pg_cron - a simple cron-based job scheduler-Crunchy Data.pdf
免费下载
What is pg_cron?
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:
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule(’30 3 * * 6’, $$DELETE FROM events WHERE event_time < now() - interval ’1 week’$$);
schedule
----------
42
-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule(’nightly-vacuum’, ’0 10 * * *’, ’VACUUM’);
schedule
----------
43
-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule(’nightly-vacuum’, ’0 3 * * *’, ’VACUUM’);
schedule
----------
43
-- Stop scheduling jobs
SELECT cron.unschedule(’nightly-vacuum’ );
unschedule
------------
t
(1 row)
SELECT cron.unschedule(42);
unschedule
------------
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)
1
| +-------------- 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!
Installing pg_cron
Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 12 using
PGDG:
# Install the pg_cron extension
sudo yum install -y pg_cron_12
Install on Debian, Ubuntu with PostgreSQL 12 using apt.postgresql.org:
# Install the pg_cron extension
sudo apt-get -y install postgresql-12-cron
You can also install pg_cron by building it from source:
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-12/bin:$PATH
make && 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 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.
2
of 4
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论