1、简介

在使用数据库时，我们经常会需要在上面跑一些定时作业，例如转历史库，批处理，定时维护数据等。Oracle可以通过其自带的job机制来进行，PostgreSQL是没有自带的job机制，我们使用 PostgreSQL扩展来补全功能！

pg_cron是Citus Data研发的一个PostgreSQL扩展。它包含一个后台工作程序 （pg_cron scheduler），用于在服务器端执行数据库任务。它使用与常规cron相同的语法，允许直接从数据库定期执行PostgreSQL命令。

2、安装

2.1、环境

pg_cron 最低版本为9.5

本次测试环境为：

系统：CentOS Linux release 7.6.1810 (Core)
数据库版本：PostgreSQL 11.2

2.2、安装pg_cron

github下载地址

2.2.1、编译安装

1、'安装相关依赖包'
yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl tcl openssl ncurses-devel openldap pam
2、'对源码包进行解压'
cd /soft/
tar -xf pg_cron-1.3.0.tar.gz
3、'添加 sudo 权限，否则下面编译不成功'
echo 'postgres ALL=(ALL) NOPASSWD: /bin/su,/bin/*,/sbin/*,/usr/bin/*' >> /etc/sudoers
4、'进入 Postgres 用户 进行编译'
su - postgres
cd /soft/cd pg_cron-1.3.0
make && sudo PATH=$PATH make install

2.2.2、修改参数

1、'参数配置'
# 修改 postgresql.conf 配置：
shared_preload_libraries = 'pg_cron'
# .pgpass配置：
127.0.0.1:5432:postgres:postgres:postgres
2、'创建扩展'
# 重启一下数据库，使参数生效
pg_ctl restart
# 进入数据库
psql
# 创建扩展
CREATE EXTENSION pg_cron;

3、使用

3.1、创建定时任务

SELECT cron.schedule('1 1 * * *', 'VACUUM');
SELECT cron.schedule('1 1 * * *', $$insert into t1 values(2)$$);
# 也可以直接使用sql来创建定时任务：
INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('1 5 * * *', 'VACUUM', 'node', 5432, 'postgres', 'user');
or
# 每天凌晨 1 点执行
SELECT cron.schedule('1 1 * * *', 'select drop_chunks(interval ''60 days'', ''csyun_vm_stats'')');
SELECT cron.schedule('2 1 * * *', 'select drop_chunks(interval ''60 days'', ''csyun_pm_stats'')');

3.2、取消定时任务

#取消单个定时任务
SELECT cron.unschedule(1);
#取消所有定时任务
SELECT cron.unschedule (jobid) FROM cron.job ; 
#可以选择直接删除表cron.job中对应的记录来取消定时任务：
delete from cron.job where jobid=1;
'注：1为对应的jobid'

3.3、查询定时任务

\x
flying=# select * from cron.job;
-[ RECORD 1 ]-----------------------
jobid    | 1
schedule | */10 * * * *
command  | VACUUM
nodename | localhost
nodeport | 5432
database | flying
username | postgres
active   | t

4、pg_cron日志记录

pg_cron日志会打印在数据库日志中

pg_cron启动日志：

LOG:  pg_cron scheduler started

操作成功的日志：

LOG:  cron job 1 starting: SELECT public.f_pull('pg_cron import');
LOG:  cron job 1 completed: 1 row

操作失败的日志：

LOG:  cron job 1 starting: SELECT public.f_pull('pg_cron import');
LOG:  cron job 1 connection failed

注：操作失败一般是由于pg_hba.conf或者.pgpass文件配置有问题导致连接失败，请检查配置。

5、应用举例

5.1、定期删除旧数据

每周六凌晨三点半删除一周前的数据

SELECT cron.schedule ('30 3 * * 6',$$DELETE FROM events WHERE event_time < now()-interval '1 week'$$);

5.2、定期vacuum

每天晚上23点定期vacuum

SELECT cron.schedule ('0 23 * * *','VACUUM');