(文档)第118讲:定时任务调度插件–pg_cron 使用技巧

目录

　　一、 pg_cron概述

　　二、 pg_cron安装

　　三、 pg_cron应用案例

pg_cron概述

　　pg_cron是PostgreSQL中的一个简单的基于cron的任务调度插件，支持在指定时间点执行批处理任务，如：定时更新数据、备份数据或清理过期数据等，能够确保任务按照预定计划进行，避免遗漏或延迟，节省时间和人力成本，实现自动准确高效的任务执行，提升系统的效率和可管理性。

pg_cron功能介绍

　　定时任务主要分为定时计划和任务内容两个部分，具体介绍如下：

　　1) 任务内容：用于指定该任务的具体内容，例如VACUUM。

　　2) 定时计划：用于规定任务的时间计划，例如每隔一分钟执行一次该任务。

pg_cron实现原理

　　pg_cron里维护了job list和task list来进行后台调度和任务的执行，其更新过程是在启动时根据cron.job表构造job list和task list，后面任务列表更新时通过触发器cron.job_cache_invalidate进行列表刷新，整体流程如下：

pg_cron调度过程

　　pg_cron启动后会进入一个循环，其内部不断执行任务列表获取，是否执行判断(ShouldRunTask函数)，其任务状态有以下几种，由于pg_cron是单线程的，所以在对于可能阻塞的步骤采用IO多路复用来处理，避免阻塞，使用的是Poll函数。

pg_cron语法介绍

　　定时计划使用标准的cron语法，语法如下：

pg_cron安装

　　1、下载

　　git clone https://github.com/citusdata/pg_cron.git

　　2、编译安装

　　cd pg_cron

　　make && make install

　　3、修改参数并重启数据库

　　postgres=# ALTER SYSTEM SET shared_preload_libraries TO pg_cron;

　　postgres=# \! pg_ctl restart

　　4、安装插件

　　postgres=# CREATE EXTENSION pg_cron;

　　pg_cron使用技巧

　　1) 定时任务执行的时间是GMT或UTC时间，在使用时注意换算本地时间。

　　2) 定时任务都储存于默认数据库postgres中，仅支持在数据库postgres中查询定时任务。

添加任务

　　1、周六3:30 AM(GMT)删除过期数据

　　2、每天的10:00 AM(GMT)执行函数test

　　SELECT cron.schedule(‘0 10 * * *’, ‘select test()’);：

　　3、每分钟执行指定SQL

　　SELECT cron.schedule(‘* * * * *’, ‘select 1’);

　　4、每月1号和30号以及每周六和周日的2:30 AM(GMT)执行磁盘清理：

　　SELECT cron.schedule(’30 2 1,30 * 6,0′, ‘VACUUM FULL’);

　　5、每天的11:00 PM(GMT)对Postgres库执行VACUUM：

　　SELECT cron.schedule(‘Do Vacuum’, ‘0 23 * * *’ , ‘VACUUM FULL’);

　　     1) 低于1.4版本的pg_cron插件只能在安装插件的库执行定时任务，如果需要在其他库执行任务，需要直接操作cron.job表，操作不方便且不安全。

　     　2) 1.4版本的pg_cron插件支持指定数据库和数据库账号执行定时任务。语法如下：

　　SELECT cron.schedule_in_database(‘<定时任务名称>’, ‘<定时计划>’, ‘<定时任务>’, ‘<执行数据库>’, ‘<数据库账号>’, ‘<任务是否启用>’);

　　'<执行数据库>’：默认值为空，表示在postgres库执行。

　　'<数据库账号>：默认值为空，表示使用当前账号执行。

　　'<任务是否启用>’：默认值为true，表示启用该定时任务。

　　1、每天的11:00 PM(GMT)在testdb库执行VACUUM

　　SELECT cron.schedule_in_database(‘Do Vacuum’, ‘0 23 * * *’ , ‘VACUUM FULL’, ‘testdb’);

　　2、每分钟让user1用户在testdb库中执行指定SQL

　　SELECT cron.schedule_in_database(‘Select Per Minute’, ‘* * * * *’, ‘select 1’, ‘testdb’, ‘user1’);

修改任务

　　pg_cron提供了cron.alter_job函数用于更改定时任务，语法如下：

　　SELECT cron.alter_job(<定时任务ID>, ‘<定时计划>’, ‘<定时任务>’, ‘<执行数据库>’, ‘<执行用户>’, ‘<任务是否启用>’);

　　<定时任务ID>：必填。定时任务ID为创建任务时自动生成的，可以通过查看cron.job表的jobid字段查看。

　　其他参数：非必填，默认值为空，表示不更改。

　　1、任务ID为3的定时任务更改为每天11:00 AM(GMT)执行

　　SELECT cron.alter_job(3, ‘0 11 * * *’);

　　2、任务ID为1的定时任务更改为VACUUM

　　SELECT cron.alter_job(1, null , ‘VACUUM’);

　　3、任务ID为2的定时任务的执行用户更改为user2：

　　SELECT cron.alter_job(2, null , null, null, ‘user2’);

监控任务

　　1) pg_cron新增了cron.job_run_details表记录定时任务执行信息，您可以在该表中获取执行任务的详细情况。

　　2) 定时任务较多时，可能会导致cron.job_run_details表变得非常大，建议设置一个定时任务来删除该表。如果您不需要记录定时任务的执行情况，也可以提交工单联系技术支持修改cron.log_run关闭记录信息。

　　1、 查看失败的任务详情

　　SELECT * FROM cron.job_run_details WHERE status = ‘failed’;

　　2、查看任务ID为1的定时任务执行详情

　　SELECT * FROM cron.job_run_details WHERE jobid = ‘1’;

维护任务

　　1、查看定时任务列表

　　SELECT * FROM cron.job;

　　2、通过任务名字删除定时任务

　　SELECT cron.unschedule(‘<定时任务名称>’);

　　3、通过ID名字删除定时任务

　　SELECT cron.unschedule(<定时任务ID>);

　　注意：定时任务ID为创建任务时自动生成的，可以通过查看cron.job表的jobid字段查看。