Name: pg_cron
Owner: Citus Data
Description: Run periodic jobs in PostgreSQL
Created: 2016-09-04 23:12:31.0
Updated: 2018-05-24 11:28:29.0
Pushed: 2018-05-21 14:09:39.0
Homepage: null
Size: 106
Language: C
GitHub Committers
| User | Most Recent Commit | # Commits |
|---|
Other Committers
| User | Most Recent Commit | # Commits |
|---|
pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 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:
elete old data on Saturday at 3:30am (GMT)
CT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
edule
------
42
acuum every day at 10:00am (GMT)
CT cron.schedule('0 10 * * *', 'VACUUM');
edule
------
43
top scheduling a job
CT cron.unschedule(43);
chedule
--------
t
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)
? ????????????????? 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. Be aware that pg_cron always uses GMT!
Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 10:
d Citus Data package repository
https://install.citusdata.com/community/rpm.sh | sudo bash
stall the pg_cron extension
yum install -y pg_cron_10
Install on Debian, Ubuntu with PostgreSQL 10 using apt.postgresql.org:
stall the pg_cron extension
apt-get -y install postgresql-10-cron
You can also install pg_cron by building it from source:
clone https://github.com/citusdata/pg_cron.git
g_cron
sure pg_config is in your path, e.g.
rt PATH=/usr/pgsql-10/bin:$PATH
&& sudo PATH=$PATH make install
To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is in hot standby mode, but it automatically starts when the server is promoted.
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 the cron.database_name configuration parameter in postgresql.conf.
d to postgresql.conf:
ed_preload_libraries = 'pg_cron'
.database_name = 'postgres'
After restarting PostgreSQL, you can create the pg_cron functions and metadata tables using CREATE EXTENSION pg_cron.
un as superuser:
TE EXTENSION pg_cron;
ptionally, grant usage to regular users:
T USAGE ON SCHEMA cron TO marco;
Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.
For security, jobs are executed in the database in which the cron.schedule function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in the cron.job table.
Articles showing possible ways of using pg_cron:
Since pg_cron uses libpq, you can also run periodic jobs on other databases or other machines. If you are superuser, then you can manually modify the cron.job table and use custom values for nodename and nodeport to connect to a different machine:
RT INTO cron.job (schedule, command, nodename, nodeport, database, username)
ES ('0 4 * * *', 'VACUUM', 'node-1', 5432, 'postgres', 'marco');
You can use .pgpass to allow pg_cron to authenticate with the remote server.
The following table keeps track of which of the major managed Postgres services support pg_cron.
| Service | Supported | Version | | ————- |:————-:| ——–:| | Citus Cloud | :heavy_check_mark: | 1.0 | | Amazon RDS | :x: | | | Azure | :x: | | | Google Cloud | :x: | | | Heroku | :x: | |