使用pg_cron插件

简介

pg_cron扩展是一个使用cron语法的定时任务调度程序。与常规cron使用相同的语法，但允许直接从数据库执行PostgreSQL命令。更多信息，请参见pg_cron官方文档。

支持的版本说明

PostgreSQL 12及以上版本的最新小版本支持该插件。可通过以下SQL语句查询当前实例支持该插件的版本：

SELECT * FROM pg_available_extension_versions WHERE name = 'pg_cron';

如果不支持，可通过升级内核小版本或者使用转储与还原升级大版本使用该插件。

RDS for PostgreSQL实例支持的插件，具体请参见支持的插件列表。

插件介绍

标准cron语法，*表示每个时间段运行，特定数字表示仅在此时间。

┌───────────── min (0 - 59)  
   │ ┌────────────── 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)  
   │ │ │ │ │  
   │ │ │ │ │ 

例如每周六9:30 AM（GMT）的语法为：

30 9 * * 6

注意事项

• pg_cron需要后台守护进程，因此启动数据库前，需要将pg_cron放到shared_preload_libraries中。
• 定时任务不会在备机上运行，但当备机升主后，定时任务会自动启动。
• 定时任务会以任务创建者的权限执行。
• 定时任务使用GMT时间执行。
• 一个实例可以并行运行多个任务，但同一时间某个任务仅能运行一个。
• 某个任务，需要等待前一个定时任务结束，那么该任务会进入等待队列，且会在前一个任务结束后尽快启动。
• 使用前，需要将cron.database_name修改为创建定时任务的数据库，并且只能设为单个数据库，不支持设置多个数据库。

插件安装

1. 在实例列表，单击实例名称，进入概览页面。
2. 选择“插件管理”，将pg_cron添加到shared_preload_libraries参数中。
   图1 插件管理
   
3. 重启实例，使shared_preload_libraries参数值生效。
   图2 添加完成
   
4. 如果插件安装的数据库不是默认的postgres库，需要修改参数“cron.database_name”。
   该pg_cron插件在哪个库使用，就需要将该参数“cron.database_name”的值修改为对应的数据库名称。例如：需要在test_db库安装使用，就需要修改参数“cron.database_name”值为test_db。

   图3 修改参数值
   
   

   修改参数“cron.database_name”的值后，需要重启实例使参数值生效。

5. 安装pg_cron插件。
   • 通过界面安装pg_cron
     在插件管理页面，选择对应的数据库，搜索pg_cron，单击“安装”。

     图4 安装pg_cron
     
   • 通过SQL安装pg_cron

     登录到对应的数据库，执行如下SQL创建插件：

     CREATE EXTENSION IF NOT EXISTS pg_cron;

基本使用

创建任务：

-- 任务1：每周六上午3:30(GMT)，删除旧数据 
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);  
-- 任务2：每天上午 10:00(GMT)，执行vacuum，任务以nightly-vacuum为命名。
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');

进阶使用（对postgres以外的数据库设置定时任务）

前提：在postgres库中已经安装pg_cron插件、已提前创建test_db数据库，需要在test_db设置定时任务。

1. 登录postgres库。
2. 创建定时任务。

   SELECT cron.schedule('create', '10 * * * *', 'create table test (a int);');
   SELECT cron.schedule('insert', '15 * * * *', 'insert into test values(1);');
   SELECT cron.schedule('drop', '20 * * * *', 'drop table test;');

   

   定时任务名称不能相同，否则会被覆盖。

3. 将定时任务设置为想要执行定时任务的库test_db。

   UPDATE cron.job SET database = 'test_db' WHERE jobid = 1;
   UPDATE cron.job SET database = 'test_db' WHERE jobid = 2;
   UPDATE cron.job SET database = 'test_db' WHERE jobid = 3;

   jobid可通过如下命令查询：

   postgres=> select * from cron.job;
    jobid |  schedule  |           command           | nodename  | nodeport | database  | username | active | jobname 
   -------+------------+-----------------------------+-----------+----------+-----------+----------+--------+---------
        1 | 10 * * * * | create table test (a int);  | localhost |     5432 | postgres  | root     | t      | create
        2 | 15 * * * * | insert into test values(1); | localhost |     5432 | postgres  | root     | t      | insert
        3 | 20 * * * * | drop table test;            | localhost |     5432 | postgres  | root     | t      | drop

4. 查看修改结果，会发现database字段为test_db，即该任务执行的数据库为test_db。

   postgres=> select * from cron.job;
    jobid |  schedule  |           command           | nodename  | nodeport | database  | username | active | jobname 
   -------+------------+-----------------------------+-----------+----------+-----------+----------+--------+---------
        1 | 10 * * * * | create table test (a int);  | localhost |     5432 | test_db   | root     | t      | create
        2 | 15 * * * * | insert into test values(1); | localhost |     5432 | test_db   | root     | t      | insert
        3 | 20 * * * * | drop table test;            | localhost |     5432 | test_db   | root     | t      | drop

5. 验证定时任务是否执行成功。
   • 在日志中查看是否有对应定时任务执行成功。
     图5 查看日志
     
   • 在test_db库执行查询，查看test表已经创建成功。
     图6 查看库
     
6. 如果不想再使用该定时任务，根据在4中查询出的结果，执行以下SQL删除任务。
   • 通过定时任务jobid删除

     SELECT cron.unschedule(1);

   • 通过定时任务名称删除

     SELECT cron.unschedule('create');

pg_cron插件相关的参数