How do I troubleshoot common issues with pg_cron extensions in Amazon RDS for PostgreSQL?

Short description

Amazon RDS for PostgreSQL supports pg_cron extensions that run inside the database and allow you to schedule PostgreSQL commands. When you run pg_cron extensions you might experience issues with jobs that don't run at the scheduled time and updates that don't take effect.

Resolution

To troubleshoot issues with pg_cron extensions in Amazon RDS for PostgreSQL, complete the following steps:

Note: Before you begin, make sure that the pg_cron extension is set up correctly. For more information, see Setting up the pg_cron extension.

Scheduled pg_cron jobs that don't run at the scheduled time

To troubleshoot issues with scheduled pg_cron jobs that don't run at the scheduled time, complete the following steps:

1. Confirm that the pg_cron launcher runs in the database that initiates cron job workers:

   Example:

   postgres=> select application_name,usename,backend_type,query,state,wait_event_type,age(now(),backend_start) as backend_start_age,age(now(),query_start) as query_start_age,age(now(),state_change) state_change_age from pg_stat_activity where backend_type = 'pg_cron launcher';

   Example output:

    application_name  | usename  |   backend_type   | query | state  | wait_event_type |      backend_start_age       | query_start_age | state_change_age 
   -------------------+----------+------------------+-------+--------+-----------------+------------------------------+-----------------+------------------
    pg_cron scheduler | rdsadmin | pg_cron launcher |       | [NULL] | Extension       | 2 mons 28 days 15:16:41.6642 | [NULL]          | [NULL]
   (1 row)

   Note: If the previous query returns 0 rows, then the pg_cron launcher isn't running. To resolve this, restart the Amazon RDS instance to initiate the launcher and allow scheduled jobs to run.

2. If the previous query returns rows, then check if the previous job is in a Running state.
   Note: Pg_cron can run multiple jobs in parallel, but runs one instance of a job at a time. If a second job run is scheduled to start while the first job is running, then the second job is queued. The second job starts after the first job run completes.

   Example:

   postgres=> select *  FROM cron.job_run_details where status ='running';
    jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time 
   -------+-------+---------+----------+----------+---------+--------+----------------+------------+----------  1 |     2 |   15712 | postgres | postgres | select pg_sleep(5) | running   | [NULL]         | 2024-07-10 16:40:00.025228+00 | [NULL]  (1 row)

3. Make sure that the job schedule is configured to align with the UTC time zone. The pg_cron schedule time is based on the UTC time zone in Amazon RDS for PostgreSQL databases.

Updates to cron.max_running_jobs that don't take effect

The cron.max_running_jobs parameter determines the maximum number of jobs that can run concurrently. If you want to increase cron.max_running_jobs, then update max_worker_processes to be equal to or higher than cron.max_running_jobs. Cron.max_running_jobs can't be higher than max_worker_processes. If you increase cron.max_running_jobs higher than max_worker_processes, then no updates take effect. Make sure that you reboot the Amazon RDS instance when you update the cron.max_running_jobs parameter for the changes to take effect.

Related information

Tables for scheduling jobs and capturing status