PostgreSQL不自带定时任务功能，但是可以安装第三方的扩展或者使用操作系统的cron，第三方扩展使用比较多的有pgAgent和pg_cron，本文主要介绍下pg_cron。pg_cron是一个用于PostgreSQL（9.5及以上版本）的基于cron的简单作业调度程序，它作为扩展运行在数据库内部。它使用与常规cron相同的语法，但是它允许你直接从数据库调度PostgreSQL命令。pg_cron可以并行运行多个作业，但是一次最多只能运行一个作业实例。如果第二轮运行在第一轮运行完成之前开始，则第二轮运行将排队，并在第一轮运行完成后立即开始。这里有一个需要特别注意的点，pg_cron始终使用GMT时区！！如果你的数据库使用的是CST时区，定时任务的时间需要减去8小时。本文介绍的是目前最新版本v1.3.0。
1、安装pg_cron
#下载pg_cron
wget https://github.com/citusdata/pg_cron/archive/v1.3.0.tar.gz
tar zxf v1.3.0.tar.gz
cd pg_cron-1.3.0
make
make install
复制2、配置pg_cron
要在PostgreSQL启动时启动pg_cron后台工作程序，你需要把pg_cron添加到shared_preload_libraries参数中。如果是备库，pg_cron不会运行任何作业，但是备库提升为主库后它将自动启动。默认情况下，pg_cron的元数据会在postgres库中创建。不过可以通过cron.database_name参数进行灵活配置。在内部，pg_cron使用libpq打开到本地数据库的新连接。对于运行cron作业的用户，需要在pg_hba.conf中为来自本地主机的连接启用trust身份验证，或者使用.pgpass文件，但是在1.3版本简化了这个配置，你可以通过cron.use_background_workers=on在postgresql.conf中进行设置来选择使用动态后台工作程序代替连接。这样，你不需要任何pg_hba.conf更改。后台工作程序的一个缺点是并发作业的数量限制为max_worker_processes（默认为8个）。连接到localhost的标准方式仅受限制max_connections（默认情况下为100，通常更高）。如果选择使用动态后台工作程序建议调大max_worker_processes的参数值。
#vi postgresql.conf:
shared_preload_libraries = ‘pg_cron’
cron.database_name = ‘test’
cron.use_background_workers = on
max_worker_processes = 16
#重启数据库
service postgresql-10 restart
#连接数据库
psql -d test
#创建扩展，需超级用户
postgres=# CREATE EXTENSION pg_cron;
#授权
postgres=# GRANT USAGE ON SCHEMA cron TO test;
3、使用pg_cron
- 创建定时任务
#连接数据库
psql -d test -U test
--每天6点30分(GMT) 运行vacuum test
test=> SELECT cron.schedule('30 6 * * *', 'VACUUM test');
schedule
----------
7
(1 row)
--每分钟调用存储过程test()
test=> SELECT cron.schedule('process-new-events', '* * * * *', 'CALL test()');
schedule
----------
8
(1 row)
--PostgreSQL重启更新pg_cron扩展
test=> SELECT cron.schedule('upgrade-pgcron', '@reboot', 'ALTER EXTENSION pg_cron UPDATE');
schedule
----------
9
(1 row)
--每天0点0分(GMT)删除1年前的历史数据
test=> SELECT cron.schedule('delete-old-events','0 0 * * *', $$DELETE FROM test WHERE createtime < now() - interval '1 year'$$);
schedule
----------
10
(1 row)
复制- 查看定时任务
--查看定时任务
test=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+--------------------
7 | 30 6 * * * | VACUUM test | localhost | 5432 | test | test | t |
8 | * * * * * | CALL test() | localhost | 5432 | test | test | t | process-new-events
9 | @reboot | ALTER EXTENSION pg_cron UPDATE | localhost | 5432 | test | test | t | upgrade-pgcron
10 | 0 0 * * * | DELETE FROM test WHERE createtime < now() - interval '1 year' | localhost | 5432 | test | test | t | delete-old-events
(4 rows)
复制- 查看定时任务运行情况
--查看定时任务
test=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------------------------+-----------+----------------------------------------+-------------------------------+-------------------------------
8 | 7 | 28802 | test | test | CALL test() | failed | ERROR: syntax error at or near "CALL"+| 2020-11-12 16:48:00.005959+08 | 2020-11-12 16:48:00.006315+08
(1 rows)
--清楚历史数据
--每天0点0分(GMT)删除7天前的数据
test=> SELECT cron.schedule('clean audit log', '0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() – interval '7 days'$$);
schedule
----------
11
(1 row)
复制- 更新定时任务
--每周0点0分(GMT)调用存储过程test()，通过jobname名称执行upsert
test=> SELECT cron.schedule('process-new-events', '0 0 * * 0', 'CALL test()');
schedule
----------
8
(1 row)
复制- 删除定时任务
--通过jobname名称删除
test=> SELECT cron.unschedule('process-new-events');
unschedule
------------
t
(1 row)
--通过jobid删除
test=> SELECT cron.unschedule(7);
unschedule
------------
t
(1 row)
--查看job
test=> TABLE cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-----------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+-------------------
9 | @reboot | ALTER EXTENSION pg_cron UPDATE | localhost | 5432 | test | test | t | upgrade-pgcron
10 | 0 0 * * * | DELETE FROM test WHERE createtime < now() - interval '1 year' | localhost | 5432 | test | test | t | delete-old-events
(2 rows)
复制