pg_cron_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了pg_cron插件的背景、原理以及示例等内容。

背景信息

定时任务是一种在预定的时间点或时间间隔内自动执行的任务。定时任务通常由一个调度器负责管理，根据预定的时间规则触发任务的执行。常见的时间规则包括每天的特定时间点、每周的特定日期和时间、每月的特定日期和时间等。调度器还可以根据任务的需要设置任务的执行次数和重复间隔。

定时任务的应用场景非常广泛：

在服务器端，定时任务常用于定期执行后台任务、生成报告、清理垃圾数据等。
在客户端，定时任务可以用于更新数据、提醒用户、执行自动化操作等。

无论是在服务器端还是客户端，定时任务都可以减轻人工操作的负担，提升工作效率。

pg_cron是PolarDB PostgreSQL版（兼容Oracle）支持的一款第三方插件，提供了在PolarDB PostgreSQL版（兼容Oracle）中通过cron语法定时执行SQL的能力。用户可以通过SQL语句创建定时任务，并在指定的时间点或时间间隔内自动执行任务。

pg_cron的优势如下：

简单易用：执行SQL语句即可创建、调度和管理任务，无需额外的编程或配置。
灵活的调度选项：支持按分钟、小时、日期、星期、月份及其组合进行定时调度。
库级别的任务管理：定时任务保存在数据库中，可以跨多个数据库共享和管理任务。
并发任务执行：支持同时执行多个任务，提高任务执行效率。
可靠性和容错性：提供错误处理和容错机制，确保任务的正确执行并提供相应的日志记录。

pg_cron为数据库管理员和开发人员提供了一种简单而强大的方式来自动化执行重复性任务。例如，备份数据、生成报告、定期清理等，从而提升数据库的管理和开发效率。

注意事项

PolarDB PostgreSQL版（兼容Oracle）的内核小版本为2.0.14.16.1及更低版本时，该插件在使用过程中可能会因集群端口在重启后发生变化而导致任务失败。该问题在内核小版本为2.0.14.16.1及以上版本中得到修复。

原理介绍

定时任务信息维护

所有的定时任务将会被存储在cron.job表中，用户可以通过SQL函数接口新增或删除定时任务。

pg_cron通过维护JOB LIST和TASK LIST两个列表进行后台调度和任务执行。数据库启动时，根据cron.job表中的任务列表构造JOB LIST和TASK LIST；后续任务列表有更新时，通过触发器cron.job_cache_invalidate刷新列表保持JOB LIST和TASK LIST与cron.job表实时一致，确保任务能够得到正确调度和执行。

定时任务调度执行

WAITING（等待）：默认状态。如果条件不满足（非激活状态/计划时间还未到），则跳过该任务的调度；如果条件满足，则进入START状态。
START（启动）：构建任务的连接信息，并进行连接测试。如果连接成功，则进入CONNECTING状态；否则进入ERROR状态。
CONNECTING（连接）：检查任务是否激活，连接是否正常。如果所有条件都满足，则进入SENDING状态；否则进入ERROR状态。
SENDING（发送）：检查任务是否激活，连接是否正常。如果所有条件满足，将定时任务文本发送至服务器，进入RUNNING状态；否则进入ERROR状态。
RUNNING（运行）：检查任务是否激活，连接是否正常。如果所有条件都满足，接收传回的任务结果并进入DONE状态；否则跳出等待进入ERROR状态。
ERROR（错误）：任务失败，重置连接信息并进入DONE状态。
DONE（完成）：任务完成，重置任务信息并重新进入WAITING状态。

函数列表

说明

出于安全性考虑，只能以高权限用户通过下列函数操作cron.job表；普通用户只有cron.job表的查看权限。
所有定时任务都储存于postgres数据库中，对定时任务的操作需要连接到postgres数据库进行操作。
定时任务执行的时间为GMT时间（格林威治标准时间），请注意换算时间。

添加定时任务

语法

cron.schedule (
    job_name TEXT,
    schedule TEXT,
    command TEXT
);

参数说明

参数	说明
job_name	任务名称，可以默认为空。
schedule	任务计划周期，格式为标准`cron`格式字符串。
command	任务内容，格式为`SQL`字符串。

在指定数据库中添加定时任务

语法

cron.schedule_in_database (
    job_name TEXT,
    schedule TEXT,
    command TEXT,
    db_name TEXT
);

参数说明

参数	说明
job_name	任务名称。
schedule	任务计划周期，格式为标准`cron`格式字符串。
command	任务内容，格式为`SQL`字符串。
db_name	任务执行的目标数据库。

修改定时任务

语法

cron.alter_job (
    job_id BIGINT,
    schedule TEXT DEFAULT NULL,
    command TEXT DEFAULT NULL,
    db_name TEXT DEFAULT NULL,
    active BOOLEAN DEFAULT NULL
);

参数说明

参数	说明
job_id	待修改的任务ID。
schedule	修改后的任务计划。
command	修改后的任务内容。
db_name	修改后的任务执行数据库。
active	修改后任务是否处于激活状态（正常运行）。

删除定时任务

语法

cron.unschedule(job_id BIGINT);

cron.unschedule(job_name TEXT);

参数说明

参数	说明
job_id	待删除任务ID。
name	待删除任务名称。

使用指南

说明

出于安全性考虑，只能以高权限用户执行以下操作。

创建插件

连接数据库并执行以下命令，创建pg_cron插件。

CREATE EXTENSION pg_cron;

新增定时任务

执行以下命令，为db01数据库创建一个名为task1的任务，函数将返回任务ID。

SELECT cron.schedule_in_database('task1', '* * * * *', 'SELECT 1', 'db01');

结果显示如下：

 schedule_in_database
----------------------
                    1
(1 row)

查看定时任务列表

执行以下命令，查看定时任务列表。

SELECT * FROM cron.job;

结果显示如下：

jobid | schedule  | command  | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
     1 | * * * * * | SELECT 1 | /tmp     |    39361 | db01     | u1       | t      | task1
(1 row)

查看定时任务执行历史

执行以下命令，查看定时任务执行历史。

SELECT * FROM cron.job_run_details;

结果显示如下：

 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 | 4152316 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:55:00.020442+00 | 2023-10-19 03:55:00.021512+00
     1 |     2 | 4152438 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:56:00.006468+00 | 2023-10-19 03:56:00.006822+00
(2 rows)

删除定时任务

执行以下命令，删除定时任务。

SELECT cron.unschedule('task1');

结果显示如下：

 unschedule
------------
 t
(1 row)

删除插件

执行以下命令，删除pg_cron插件。

DROP EXTENSION pg_cron;

时间规则示例

pg_cron使用标准的cron语法，*表示每间隔指定单位时间运行，指定数字表示间隔该数字单位时间运行。

 ┌───────────── 分钟 (0 - 59)
 │ ┌────────────── 小时 (0 - 23)
 │ │ ┌─────────────── 日期 (1 - 31)
 │ │ │ ┌──────────────── 月份 (1 - 12)
 │ │ │ │ ┌───────────────── 一周中的某一天 (0 - 6) (0到6表示周日到下周六，
 │ │ │ │ │                  7 也表示周日）
 * * * * *

在每周六3:30 AM（GMT 时间）删除过期数据。

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

在每天10:00 AM（GMT 时间）执行VACUUM。
```
SELECT cron.schedule('0 10 * * *', 'VACUUM;');
```

每分钟执行SQL。

SELECT cron.schedule('* * * * *', 'SELECT 1;');

每小时的23分钟执行SQL。

SELECT cron.schedule('23 * * * *', 'SELECT 1;');

每个月的4号执行SQL。

SELECT cron.schedule('* * 4 * *', 'SELECT 1;');