Version:

This documentation is sourced from a third-party project and is not maintained by pgEdge.

Extension settings

The pg_cron extension supports the following configuration parameters:

Setting	Default	Description
`cron.database_name`	`postgres`	Database in which the pg_cron background worker should run.
`cron.enable_superuser_jobs`	`on`	Allow jobs to be scheduled as superusers.
`cron.host`	`localhost`	Hostname to connect to postgres.
`cron.launch_active_jobs`	`on`	When off, disables all active jobs without requiring a server restart
`cron.log_min_messages`	`WARNING`	log_min_messages for the launcher bgworker.
`cron.log_run`	`on`	Log all run details in the`cron.job_run_details` table.
`cron.log_statement`	`on`	Log all cron statements prior to execution.
`cron.max_running_jobs`	`32`	Maximum number of jobs that can be running at the same time.
`cron.timezone`	`GMT`	Timezone in which the pg_cron background worker should run.
`cron.use_background_workers`	`off`	Use background workers instead of client connections.

Changing settings

To view setting configurations, run:

SELECT * FROM pg_settings WHERE name LIKE 'cron.%';

Setting can be changed in the postgresql.conf file or with the below command:

ALTER SYSTEM SET cron.<parameter> TO '<value>';

cron.log_min_messages and cron.launch_active_jobs have a setting context of sighup. They can be finalized by executing SELECT pg_reload_conf();.

All the other settings have a postmaster context and only take effect after a server restart.

Monitoring jobs

Reviewing the `cron.job_run_details` table

You can view job activity in the cron.job_run_details table:

select * from cron.job_run_details order by start_time desc limit 5;
┌───────┬───────┬─────────┬──────────┬──────────┬───────────────────┬───────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ jobid │ runid │ job_pid │ database │ username │      command      │  status   │  return_message  │          start_time           │           end_time            │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│    11 │  4328 │    2610 │ postgres │ marco    │ select pg_sleep(3)│ running   │ NULL             │ 2023-02-07 09:30:00.098164+01 │ NULL                          │
│    10 │  4327 │    2609 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:29:00.015168+01 │ 2023-02-07 09:29:00.832308+01 │
│    10 │  4321 │    2603 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:28:00.011965+01 │ 2023-02-07 09:28:01.420901+01 │
│    10 │  4320 │    2602 │ postgres │ marco    │ select process()  │ failed    │ server restarted │ 2023-02-07 09:27:00.011833+01 │ 2023-02-07 09:27:00.72121+01  │
│     9 │  4320 │    2602 │ postgres │ marco    │ select do_stuff() │ failed    │ job canceled     │ 2023-02-07 09:26:00.011833+01 │ 2023-02-07 09:26:00.22121+01  │
└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘
(10 rows)

The records in the table are not cleaned automatically, but every user that can schedule cron jobs also has permission to delete their own cron.job_run_details records.

Especially when you have jobs that run every few seconds, it can be a good idea to clean up regularly, which can easily be done using pg_cron itself:

-- Delete old cron.job_run_details records of the current user every day at noon
SELECT  cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

If you do not want to use cron.job_run_details at all, then you can add cron.log_run = off to postgresql.conf.

Other cron logging settings

If the cron.log_statement setting is configured, jobs will be logged before execution. The cron.log_min_messages setting controls the minimum level of messages that will be recorded.

Example use cases

Articles showing possible ways of using pg_cron:

Managed services

The following table keeps track of which of the major managed Postgres services support pg_cron.

Service	Supported
Aiven	✔️
Alibaba Cloud	✔️
Amazon RDS	✔️
Azure	✔️
Crunchy Bridge	✔️
DigitalOcean	✔️
Google Cloud	✔️
Heroku	❌
Instaclustr	✔️
Neon	✔️
PlanetScale	✔️
ScaleGrid	✔️
Scaleway	✔️
Supabase	✔️
YugabyteDB	✔️

Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.