Movatterモバイル変換

citusdata/pg_cronPublic

NotificationsYou must be signed in to change notification settings
Fork222
Star3.4k

Run periodic jobs in PostgreSQL

License

PostgreSQL license

3.4k stars 222 forks Branches Tags Activity

Star

Notifications

You must be signed in to change notification settings

Branches Tags

Folders and files

Name		Name	Last commit message	Last commit date
Latest commit History 310 Commits
expected		expected
include		include
sql		sql
src		src
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
META.json		META.json
Makefile		Makefile
README.md		README.md
github-banner.png		github-banner.png
pg_cron--1.0--1.1.sql		pg_cron--1.0--1.1.sql
pg_cron--1.1--1.2.sql		pg_cron--1.1--1.2.sql
pg_cron--1.2--1.3.sql		pg_cron--1.2--1.3.sql
pg_cron--1.3--1.4.sql		pg_cron--1.3--1.4.sql
pg_cron--1.4--1.4-1.sql		pg_cron--1.4--1.4-1.sql
pg_cron--1.4-1--1.5.sql		pg_cron--1.4-1--1.5.sql
pg_cron--1.5--1.6.sql		pg_cron--1.5--1.6.sql
pg_cron.conf		pg_cron.conf
pg_cron.control		pg_cron.control
pg_cron.sql		pg_cron.sql

Repository files navigation

What is pg_cron?

pg_cron is a simple cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database. You can also use '[1-59] seconds' to schedule a job based on an interval.

pg_cron also allows you using '$' to indicate last day of the month.

-- Delete old data on Saturday at 3:30am (GMT)SELECTcron.schedule('30 3 * * 6', $$DELETEFROM eventsWHERE event_time< now()- interval'1 week'$$); schedule----------42-- Vacuum every day at 10:00am (GMT)SELECTcron.schedule('nightly-vacuum','0 10 * * *','VACUUM'); schedule----------43-- Change to vacuum at 3:00am (GMT)SELECTcron.schedule('nightly-vacuum','0 3 * * *','VACUUM'); schedule----------43-- Stop scheduling jobsSELECTcron.unschedule('nightly-vacuum' ); unschedule------------ tSELECTcron.unschedule(42); unschedule------------          t-- Vacuum every Sunday at 4:00am (GMT) in a database other than the one pg_cron is installed inSELECTcron.schedule_in_database('weekly-vacuum','0 4 * * 0','VACUUM','some_other_database'); schedule----------44-- Call a stored procedure every 5 secondsSELECTcron.schedule('process-updates','5 seconds','CALL process_updates()');-- Process payroll at 12:00 of the last day of each monthSELECTcron.schedule('process-payroll','0 12 $ * *','CALL process_payroll()');

pg_cron can run multiple jobs in parallel, but it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started as soon as the first run completes.

The schedule uses the standard cron syntax, in which * means "run every time period", and a specific number means "but only at this time":

 ┌───────────── min (0 - 59) │ ┌────────────── hour (0 - 23) │ │ ┌─────────────── day of month (1 - 31) or last day of the month ($) │ │ │ ┌──────────────── month (1 - 12) │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to │ │ │ │ │                  Saturday, or use names; 7 is also Sunday) │ │ │ │ │ │ │ │ │ │ * * * * *

An easy way to create a cron schedule is:crontab.guru.

The code in pg_cron that handles parsing and scheduling comes directly from the cron source code by Paul Vixie, hence the same options are supported.

Installing pg_cron

Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 16 usingPGDG:

# Install the pg_cron extensionsudo yum install -y pg_cron_16

Install on Debian, Ubuntu with PostgreSQL 16 usingapt.postgresql.org:

# Install the pg_cron extensionsudo apt-get -y install postgresql-16-cron

You can also install pg_cron by building it from source:

git clone https://github.com/citusdata/pg_cron.gitcd pg_cron# Ensure pg_config is in your path, e.g.export PATH=/usr/pgsql-16/bin:$PATHmake&& sudo PATH=$PATH make install

Setting up pg_cron

To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron toshared_preload_libraries in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is inhot standby mode, but it automatically starts when the server is promoted.

# add to postgresql.conf# required to load pg_cron background worker on start-upshared_preload_libraries = 'pg_cron'

By default, the pg_cron background worker expects its metadata tables to be created in the "postgres" database. However, you can configure this by setting thecron.database_name configuration parameter in postgresql.conf.

# add to postgresql.conf# optionally, specify the database in which the pg_cron background worker should run (defaults to postgres)cron.database_name = 'postgres'

pg_cron may only be installed to one database in a cluster. If you need to run jobs in multiple databases, usecron.schedule_in_database().

Previously pg_cron could only use GMT time, but now you can adapt your time by settingcron.timezone in postgresql.conf.

# add to postgresql.conf# optionally, specify the timezone in which the pg_cron background worker should run (defaults to GMT). E.g:cron.timezone = 'PRC'

After restarting PostgreSQL, you can create the pg_cron functions and metadata tables usingCREATE EXTENSION pg_cron.

-- run as superuser:CREATE EXTENSION pg_cron;-- optionally, grant usage to regular users:GRANT USAGEON SCHEMA cron TO marco;

Ensuring pg_cron can start jobs

Important: By default, pg_cron uses libpq to open a new connection to the local database, which needs to be allowed bypg_hba.conf.It may be necessary to enabletrust authentication for connections coming from localhost in for the user running the cron job, or you can add the password to a.pgpass file, which libpq will use when opening a connection.

You can also use a unix domain socket directory as the hostname and enabletrust authentication for local connections inpg_hba.conf, which is normally safe:

# Connect via a unix domain socket:cron.host = '/tmp'# Can also be an empty string to look for the default directory:cron.host = ''

Alternatively, pg_cron can be configured to use background workers. In that case, the number of concurrent jobs is limited by themax_worker_processes setting, so you may need to raise that.

# Schedule jobs via background workers instead of localhost connectionscron.use_background_workers = on# Increase the number of available background workers from the default of 8max_worker_processes = 20

For security, jobs are executed in the database in which thecron.schedule function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in thecron.job table.

-- View active jobsselect*fromcron.job;

Viewing job run details

You can view the status of running and recently completed job runs in thecron.job_run_details:

select*fromcron.job_run_detailsorder by start_timedesclimit5;┌───────┬───────┬─────────┬──────────┬──────────┬───────────────────┬───────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐│ jobid │ runid │ job_pid │ database │ username │      command      │  status   │  return_message  │          start_time           │           end_time            │├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤│10 │4328 │2610 │ postgres │ marco    │select process()  │ succeeded │SELECT1         │2023-02-0709:30:00.098164+01 │2023-02-0709:30:00.130729+01 ││10 │4327 │2609 │ postgres │ marco    │select process()  │ succeeded │SELECT1         │2023-02-0709:29:00.015168+01 │2023-02-0709:29:00.832308+01 ││10 │4321 │2603 │ postgres │ marco    │select process()  │ succeeded │SELECT1         │2023-02-0709:28:00.011965+01 │2023-02-0709:28:01.420901+01 ││10 │4320 │2602 │ postgres │ marco    │select process()  │ failed    │ server restarted │2023-02-0709:27:00.011833+01 │2023-02-0709:27:00.72121+01  ││9 │4320 │2602 │ postgres │ marco    │select do_stuff() │ failed    │ job canceled     │2023-02-0709:26:00.011833+01 │2023-02-0709:26:00.22121+01  │└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘(10 rows)

The records incron.job_run_details are not cleaned automatically, but every user that can schedule cron jobs also has permission to delete their owncron.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 noonSELECTcron.schedule('delete-job-run-details','0 12 * * *', $$DELETEFROMcron.job_run_detailsWHERE end_time< now()- interval'7 days'$$);

If you do not want to usecron.job_run_details at all, then you can addcron.log_run = off topostgresql.conf.

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_settingsWHERE nameLIKE'cron.%';

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

ALTER SYSTEMSET cron.<parameter> TO'<value>';

cron.log_min_messages andcron.launch_active_jobs have asetting context ofsighup. They can be finalized by executingSELECT pg_reload_conf();.

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

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	✔️
ScaleGrid	✔️
Scaleway	✔️
Supabase	✔️
Tembo	✔️
YugabyteDB	✔️