1、简介

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

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

2、安装

2.1、环境

pg_cron 最低版本为9.5

本次测试环境为:

  1. 系统:CentOS Linux release 7.6.1810 (Core)
  2. 数据库版本:PostgreSQL 11.2

2.2、安装pg_cron

github下载地址

2.2.1、编译安装

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

2.2.2、修改参数

  1. 1'参数配置'
  2. # 修改 postgresql.conf 配置:
  3. shared_preload_libraries = 'pg_cron'
  4. # .pgpass配置:
  5. 127.0.0.1:5432:postgres:postgres:postgres
  6. 2'创建扩展'
  7. # 重启一下数据库,使参数生效
  8. pg_ctl restart
  9. # 进入数据库
  10. psql
  11. # 创建扩展
  12. CREATE EXTENSION pg_cron;

3、使用

3.1、创建定时任务

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

3.2、取消定时任务

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

3.3、查询定时任务

  1. \x
  2. flying=# select * from cron.job;
  3. -[ RECORD 1 ]-----------------------
  4. jobid | 1
  5. schedule | */10 * * * *
  6. command | VACUUM
  7. nodename | localhost
  8. nodeport | 5432
  9. database | flying
  10. username | postgres
  11. active | t

4、pg_cron日志记录

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

pg_cron启动日志:

  1. LOG: pg_cron scheduler started

操作成功的日志:

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

操作失败的日志:

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

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

5、应用举例

5.1、定期删除旧数据

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

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

5.2、定期vacuum

每天晚上23点定期vacuum

  1. SELECT cron.schedule ('0 23 * * *','VACUUM');
0 评论  
添加一条新评论