How do I troubleshoot common issues with pg_cron extensions in Amazon RDS for PostgreSQL?
I want to troubleshoot common issues with pg_cron extensions in Amazon Relational Database Service (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:
-
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.
-
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)
-
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
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren