定时任务 pg_cron 插件

最近更新时间:2023-12-12 16:32:11

我的收藏
pg_cron 是一个简单的基于 cron 的 PostgreSQL(10或更高版本)任务调度器,作为扩展在数据库中运行。它使用与常规 cron 相同的语法,允许您直接从数据库定时调度并执行数据库命令。
本文为您介绍 PostgreSQL pg_cron 插件的使用方法。

启用 pg_cron 扩展

1. 若需使用 pg_cron,请先 提交工单,将 pg_cron 添加至数据库 shared_preload_libraries 参数中。修改此参数,需要重启实例,请选择一个业务空闲期去进行操作。
2. 完成参数修改后,请进入 postgres 库中执行,使用管理员账户运行以下命令:
CREATE EXTENSION pg_cron;
3. 当前 pg_cron 配置仅能在 postgres 库中执行计划任务,若需要在其他 database 中运行计划任务,请参见 对 postgres 以外的数据库设置定时任务
4. 默认 pg_cron 创建完成后,其配置数据以及任务执行只能由管理员用户进行设置。若需要其他用户进行 pg_cron 设置或者执行,则需要向其他用户授予 cron 元数据库的权限,请运行以下命令。
postgres=> GRANT USAGE ON SCHEMA cron TO other-user;
此权限为 other-user 提供了对 cron 元数据的访问权限以计划和取消计划 cron 任务。但是,为了成功运行 cron 任务,还需要具有对 cron 任务中对象的访问权限。如果用户没有权限,则任务将失败,postgresql.log 中将显示错误。 如下实例,用户没有访问 pgbench_accounts 表的权限。
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table pgbench_accounts
2020-12-08 16:41:00 UTC::@:[30647]:STATEMENT: update pgbench_accounts set abalance = abalance + 1
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
cron.job_run_details 表中的其他消息如下所示:
postgres=> select jobid, username, status, return_message, start_time from cron.job_run_details where status = 'failed';
jobid | username | status | return_message | start_time
-------+------------+--------+-----------------------------------------------------+-------------------------------
143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:41:00.036268+00
143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:40:00.050844+00
143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:42:00.175644+00
143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:43:00.069174+00
143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:44:00.059466+00
(5 rows)

pg_cron 计划任务配置描述

pg_cron 提供三个主要操作:增加任务项、删除任务项、查看任务信息。

cron.schedule() 函数

此函数计划 cron 任务。任务最初是在默认 postgres 数据库中计划的。该函数返回一个表示任务标识符的 bigint 值。要计划任务在 PostgreSQL 数据库实例的其他数据库中运行,请参阅 对 postgres 以外的数据库设置定时任务 中的示例。 该函数有两种语法格式。
语法
cron.schedule (job_name,
schedule,
command
);

cron.schedule (schedule,
command
);
参数
参数
描述
job_name
cron 任务的名字,可为空不设置。
schedule
表示 cron 任务时间表的文本。格式是标准 cron 格式。
command
要运行的命令的文本。
示例
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)

postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
schedule 使用标准的 cron 语法,其中 * 表示“每个该时间运行”,特定数字表示“仅在这个数字时运行”。
# 格式是:分 时 日 月 星期
# week (0 - 6) = sun,mon,tue,wed,thu,fri,sat
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,...,sat
# | | | | |
# * * * * *

cron.unschedule() 函数

此函数删除 cron 任务。您可以传入 job_name 或 job_id。请确保您是当前 job_id 所对应的策略的拥有者。该函数返回一个布尔值,指示成功或失败。
该函数使用以下语法格式。 语法
cron.unschedule (job_id);

cron.unschedule (job_name);
参数
参数
描述
job_id
计划 cron 任务时从 cron.schedule 函数返回的任务标识符。
job_name
使用该 cron.schedule 函数计划的 cron 任务的名称。
示例
postgres=> select cron.unschedule(108);
unschedule
------------
t
(1 row)

postgres=> select cron.unschedule('test');
unschedule
------------
t
(1 row)

pg_cron 表

将以下各表用于计划任务和记录任务完成的方式。
描述
cron.job
包含有关每个计划任务的元数据。与此表的大多数交互应使用 cron.schedule 和 cron.unschedule 函数完成。
注意:不建议直接向此表授予更新或插入权限。
cron.job_run_details
包含过去运行的计划任务的历史信息。这对于调查运行的任务的状态、返回消息以及开始和结束时间非常有用。
为了防止此表无限增长,请定期清除此表。

设置 pg_cron 定时任务

1. 如果您可能希望在选择的时间计划对特定表执行 vacuum 操作,则可以使用 cron.schedule 函数设置定时任务,如每天22:00 (GMT) 在特定表上使用 VACUUM FREEZE。执行设置语句所返回的数字是指当前任务的任务编号。
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
2. 其中函数存在3个入参,第一个入参为任务名(字符型),第二个为 cron 定时语法,第三个为具体的执行 SQL。

查看 pg_cron 定时任务

在设置了定时任务后,可通过 cron.job 表查看已经配置了的定时任务,如执行以下语句。
SELECT * FROM cron.job;

jobid | schedule | command | nodename | nodeport | database | username | active
-------+------------+-----------+-----------+----------+----------+----------+--------
1 | 0 22 * * * | VACUUM ... | localhost | 5432 | postgres | test | t

删除 pg_cron 定时任务

如果某一条定时任务无需使用,则需要将此定时任务删除。执行下列语句可将定时任务删除。
SELECT cron.unschedule(1);

unschedule
------------
t

查看定时任务执行历史

运行上述示例之后,您可以按如下方式检查 cron.job_run_details 表中的任务状态以及执行情况。
postgres=> select * from cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+----------------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)

清除 pg_cron 历史记录表

1. cron.job_run_details 表包含 cron 任务的历史记录,随着时间的推移,这些历史记录可能会变得非常大。我们建议您计划清除此表的记录。例如,保留一周的条目可能足以进行故障排除。
2. 以下示例使用 cron.schedule 函数计划每天午夜运行以清除 cron.job_run_details 表的记录。如只保留了过去七天的历史记录,如下所示。
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() – interval '7 days'$$);

禁用 pg_cron 历史记录

- 如果需要完全禁用向 cron.job_run_details 表中写入任何内容,请在控制台修改将 cron.log_run 参数设置为 off。
如果您执行此操作,pg_cron 扩展不再写入表,只会在 postgresql.log 文件中生成错误。所有的错误信息将都可以在控制台中的错误日志中查看到。
- 使用以下命令检查 cron.log_run 参数的值。
postgres=> SHOW cron.log_run;

对 postgres 以外的数据库设置定时任务

默认场景下 pg_cron 的元数据全部保存在 postgres 数据库中。若需要对其他 database 中的对象执行定时任务,则需要执行以下操作。
1. 如需要对 test 数据库执行某一个表的 vacuum 操作,则首先需要使用 pg_cron 的管理员用户在 postgres 库中执行 cron.schedule 函数,设置定时任务。
postgres=> SELECT cron.schedule('test manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
2. 使用管理员用户,执行数据库更改,使定时任务执行 database 设置为想要设置的库。请注意 jobid 一定要为第一步中执行返回的 jobid。如下命令
postgres=> UPDATE cron.job SET database = 'test' WHERE jobid = 106;
3. 通过查询 cron.job 表进行验证。
postgres=> select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-------------+----------------------------------------+-----------+----------+-----------+-----------+--------+-------------------------
2 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | test | adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)

pg_cron 参数

以下是用于控制 pg_cron 扩展行为的参数列表。
参数
描述
cron.database_name
保存 pg_cron 元数据的数据库。
cron.host
要连接到 PostgreSQL 的主机名。您无法修改此值。
cron.log_run
将运行的所有任务记录到 job_run_details 表中。值为 on 或 off。
cron.log_statement
在运行所有 cron 语句之前将其记入日志。值为 on 或 off。
cron.max_running_jobs
可以同时运行的最大任务数,若需要支持更多的任务,请 提交工单 支持。
cron.use_background_workers
使用后台工作程序而不是客户端会话。您无法修改此值。
使用以下 SQL 命令来显示这些参数及其值。
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;