PostgreSQL运维工具之任务调度插件pg_cron

原创小白学信创²⁰²² ᯤ⁶ᴳ All China Database Union 2024-09-08

1503

说明：定时计划在工作中经常会用到，在PostgreSQL系列的数据库系统中，pg_cron或许可以作为您的备选方案之一，用于定时执行某个计划，如：vacuum、收集统计信息，定时执行函数等，本文整理了一些常用定时脚本供您参考。

一、pg_cron功能概述

1.1、pg_cron是什么？

pg_cron是一个简单的基于cron的任务调度插件，适用于PostgreSQL（10版或更高版本），它作为扩展运行在数据库内部。它使用与常规cron相同的语法，但它允许您直接从数据库安排PostgreSQL命令。您还可以使用“[1-59]秒”根据间隔来安排任务。

1.2、pg_cron主要功能

pg_cron是一个简单的基于cron的任务调度插件，适用于PostgreSQL（10版或更高版本），它作为扩展运行在数据库内部。它使用与常规cron相同的语法，但它允许您直接从数据库安排PostgreSQL命令。您还可以使用“[1-59]秒”根据间隔来安排任务。

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

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

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

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

┌───────────── 分钟 (取值为0 - 59)
│ ┌────────────── 小时 (取值为0 - 23)
│ │ ┌─────────────── 日期 (取值为1 - 31)
│ │ │ ┌──────────────── 月份 (取值为1 - 12)
│ │ │ │ ┌───────────────── 一周中的一天 (取值为0 - 6，0表示周日)
│ │ │ │ │
│ │ │ │ │
│ │ │ │ │

*表示任意时间都可以运行。

特定数字表示仅在这个时间运行。

,表示分隔多个指定时间。

-表示时间范围。

/表示范围间隔。

创建或预览定时计划，请参见Crontab.guru。

示例如下：

每周六3:30 AM（GMT）：

30 3 * * 6
每月1号和30号1:45 AM（GMT）：

45 1 1,30 * *
每周一至周五的3:00 AM （GMT）：

00 3 * * 1-5
从8点（GMT）到20点（GMT），每两小时整点：

0 8-20/2 * * *

注意事项

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

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

二、pg_cron安装

2.1、安装说明

安装要求：

1、支持在Red Hat, CentOS, Fedora, Amazon Linux上安装使用PGDG的PostgreSQL 16 。

2、本例通过源码安装pg_cron。

2.2、下载源码

[postgres@Node1 ~]$ cd /install/
[postgres@Node1 install]$ git clone https://github.com/citusdata/pg_cron.git
Cloning into 'pg_cron'...
remote: Enumerating objects: 1169, done.
remote: Counting objects: 100% (471/471), done.
remote: Compressing objects: 100% (125/125), done.
remote: Total 1169 (delta 389), reused 376 (delta 338), pack-reused 698 (from 1)
Receiving objects: 100% (1169/1169), 370.52 KiB | 0 bytes/s, done.
Resolving deltas: 100% (707/707), done.
[postgres@Node1 install]$

2.3、源码安装

cd pg_cron/
make && sudo PATH=$PGHOME/bin:$PATH make install

[postgres@Node1 install]$ cd pg_cron/
[postgres@Node1 pg_cron]$
[postgres@Node1 pg_cron]$ make && sudo PATH=$PGHOME/bin:$PATH make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/postgres/server/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o src/pg_cron.o src/pg_cron.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/postgres/server/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o src/job_metadata.o src/job_metadata.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/postgres/server/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o src/misc.o src/misc.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/postgres/server/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o src/task_states.o src/task_states.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/postgres/server/include -I. -I./ -I/postgres/server/include/server -I/postgres/server/include/internal  -D_GNU_SOURCE   -c -o src/entry.o src/entry.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -shared -o pg_cron.so src/pg_cron.o src/job_metadata.o src/misc.o src/task_states.o src/entry.o -L/postgres/server/lib    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden -L/postgres/server/lib -lpq
cat pg_cron.sql > pg_cron--1.0.sql
/usr/bin/mkdir -p '/postgres/server/lib'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/install -c -m 755  pg_cron.so '/postgres/server/lib/pg_cron.so'
/usr/bin/install -c -m 644 .//pg_cron.control '/postgres/server/share/extension/'
/usr/bin/install -c -m 644 .//pg_cron--1.4-1--1.5.sql .//pg_cron--1.3--1.4.sql .//pg_cron--1.5--1.6.sql .//pg_cron--1.2--1.3.sql .//pg_cron--1.4--1.4-1.sql .//pg_cron--1.1--1.2.sql .//pg_cron--1.0--1.1.sql pg_cron--1.0.sql '/postgres/server/share/extension/'
[postgres@Node1 pg_cron]$

2.4、设置pg_cron

说明：如果需要在PostgreSQL启动时启动pg_cron后台进程，您需要在postgresql.conf中将pg_cron添加到shared_preload_libraries。请注意，只要服务器处于热备用模式，pg_cron就不会运行任何作业，但当服务器被提升时，它会自动启动。

-#### 修改方法：

vi $PGDATA/postgresql.conf

# 需要在启动时加载 pg_cron 后台工作者 
shared_preload_libraries = 'pg_cron'

默认情况下，pg_cron后台工作者期望其元数据表在“postgres”数据库中创建。但是，您可以通过在postgresql.conf中设置cron.database_name配置参数来配置此参数。

可选地，指定 pg_cron 后台工作者应该运行的数据库（默认为 postgres） cron.database_name = 'postgres'

pg_cron只能在一个集群中的一个数据库中安装。如果您需要在多个数据库中运行作业，请使用cron.schedule_in_database()。
之前，pg_cron 只能使用格林威治标准时间（GMT），但现在您可以通过在 postgresql.conf 中设置 cron.timezone 来调整您的时间。

可选地，指定 pg_cron 后台工作进程应该运行的时区（默认为 GMT）。例如： cron.timezone = 'PRC'

2.5、重启PostgreSQL服务

pg_ctl restart -D $PGDATA

##2.6、添加扩展pg_cron

说明：创建管理账户admin，密码为Admin#123.，授权访问模式 cron。

[postgres@Node1 ~]$ psql
psql (16.4)
Type "help" for help.

postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION
postgres=# create role admin with superuser;
CREATE ROLE
postgres=# alter user admin password 'Admin#123.';
ALTER ROLE
postgres=#
postgres=# GRANT USAGE ON SCHEMA cron TO admin;
GRANT
postgres=#

三、使用方法

3.1、添加定时任务

语法如下：

SELECT cron.schedule(’<定时计划>’, ‘<定时任务>’);

3.1.1、每天11:00 PM（GMT）对Postgres库执行VACUUM

postgres=# SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL');
schedule
----------
       3
(1 row)

postgres=#

3.1.2、每分钟执行指定SQL

postgres=# SELECT cron.schedule('* * * * *', 'select * from pg_stat_user_tables;');
 schedule
----------
        4
(1 row)

postgres=#

3.1.3、每月1号和30号以及每周六和周日的2:30 AM（GMT）执行磁盘清理

postgres=#
postgres=# SELECT cron.schedule('30 2 1,30 * 6,0', 'VACUUM FULL');
 schedule
----------
        5
(1 row)

postgres=# SELECT cron.schedule('* * * * *', 'VACUUM FULL');
 schedule
----------
        6
(1 row)

postgres=#

3.2、指定定时任务名称

语法：

SELECT cron.schedule(’<定时任务名称>’, ‘<定时计划>’, ‘<定时任务>’);

3.2.1、每周六的10:00 AM（GMT）删除过期数据

postgres=# SELECT cron.schedule('Delete Expired Data','30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
        7
(1 row)

postgres=#

3.2.2、每天的11:00 PM（GMT）对Postgres库执行VACUUM

postgres=# SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL');
 schedule
----------
        3
(1 row)

postgres=#

3.2.3、每分钟执行指定SQL

postgres=# SELECT cron.schedule('Select Per Minute', '* * * * *', 'select * from city');
 schedule
----------
        9
(1 row)

postgres=#

2024-09-06 22:25:00.003 CST [22291] LOG:  cron job 4 starting: VACUUM FULL
2024-09-06 22:25:00.030 CST [22291] LOG:  cron job 2 completed: 23 rows
2024-09-06 22:25:00.680 CST [22291] LOG:  cron job 4 COMMAND completed: VACUUM

3.3、指定数据库执行定时任务

说明：

低于1.4版本的pg_cron插件只能在安装插件的库执行定时任务，如果需要在其他库执行任务，需要直接操作cron.job表，操作不方便且不安全。
1.4版本的pg_cron插件支持指定数据库和数据库账号执行定时任务。语法如下：
SELECT cron.schedule_in_database(’<定时任务名称>’, ‘<定时计划>’, ‘<定时任务>’, ‘<执行数据库>’, ‘<数据库账号>’, ‘<任务是否启用>’);
‘<执行数据库>’：默认值为空，表示在postgres库执行。
'<数据库账号>：默认值为空，表示使用当前账号执行。
‘<任务是否启用>’：默认值为true，表示启用该定时任务。

####3.3.1、每周四凌晨4点，对数据库postgres进行VACUUM操作。

postgres=# SELECT cron.schedule_in_database('vacuum', '0 4 * * 0', 'VACUUM', 'postgres');
 schedule_in_database
----------------------
                    9
(1 row)

postgres=#

3.3.2、每5分钟执行一次函数。

postgres=# SELECT cron.schedule('process-select_Fun', '5 seconds', 'select public._group_concat(1,5);');
 schedule
----------
       10
(1 row)

postgres=#

3.3.3、每天的10:00 AM（GMT）让sy用户在postgres库查询city表：

postgres=# SELECT cron.schedule_in_database('select_city', '0 10 * * *', 'select * from city', 'postgres','sy');
 schedule_in_database
----------------------
                   13
(1 row)

postgres=#

3.4、更改定时任务

pg_cron提供了cron.alter_job函数用于更改定时任务，语法如下：
SELECT cron.alter_job(<定时任务ID>, ‘<定时计划>’, ‘<定时任务>’, ‘<执行数据库>’, ‘<执行用户>’, ‘<任务是否启用>’);
<定时任务ID>：必填。定时任务ID为创建任务时自动生成的，可以通过查看cron.job表的jobid字段查看。
其他参数：非必填，默认值为空，表示不更改。

示例如下：

3.4.1、修改jobid=3的执行计划

postgres=# select * from cron.job;
 jobid |    schedule     |                             command                             | nodename  | nodeport | database | username | act
ive |       jobname
-------+-----------------+-----------------------------------------------------------------+-----------+----------+----------+----------+----
----+---------------------
     2 | * * * * *       | select * from pg_stat_user_tables;                              | localhost |     5432 | postgres | postgres | t
    |
     3 | 30 2 1,30 * 6,0 | VACUUM FULL                                                     | localhost |     5432 | postgres | postgres | t
    |
     4 | * * * * *       | VACUUM FULL                                                     | localhost |     5432 | postgres | postgres | t
    |
     5 | 30 3 * * 6      | DELETE FROM events WHERE event_time < now() - interval '1 week' | localhost |     5432 | postgres | postgres | t
    | Delete Expired Data
     7 | 0 23 * * *      | analyze city ;                                                  | localhost |     5432 | postgres | postgres | t
    | analyze city
     1 | 0 23 * * *      | VACUUM FULL                                                     | localhost |     5432 | postgres | postgres | t
    | Do Vacuum
    10 | 5 seconds       | select public._group_concat(1,5);                               | localhost |     5432 | postgres | postgres | t
    | process-select_Fun
     9 | 0 4 * * 0       | select * from city                                              | localhost |     5432 | postgres | postgres | t
    | vacuum
    13 | 0 4 * * 0       | select * from city                                              | localhost |     5432 | postgres | sy       | t
    | select_city
(9 rows)

postgres=#
postgres=# SELECT cron.alter_job(3, '0 11 * * *','vacuum city;','postgres');
 alter_job
-----------

(1 row)

postgres=#
 
SELECT cron.unschedule(21);

3.4.2、任务ID为9的定时任务的执行用户更改为sy：

postgres=# SELECT cron.alter_job(9, null , null, null, 'sy');
 alter_job
-----------

(1 row)

postgres=#

3.5、查看作业调度情况

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

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

示例如下：

查看失败的任务详情：

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

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

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

3.5.1、查看定时任务列表

postgres=# select * from cron.job_run_details order by start_time desc limit 5;
 jobid | runid | job_pid | database | username |                                               command
                 |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+-------------------------------------------------------------------------------------
-----------------+-----------+----------------+-------------------------------+-------------------------------
     2 |   646 |   20097 | postgres | postgres | VACUUM FULL
                 | succeeded | VACUUM         | 2024-09-05 23:23:00.033656+08 | 2024-09-05 23:23:05.834672+08
     1 |   647 |   20125 | postgres | postgres | SELECT pid, usename, application_name, client_addr, query_start, state, query FROM p
g_stat_activity; | succeeded | 9 rows         | 2024-09-05 23:23:00.030249+08 | 2024-09-05 23:23:00.042551+08
     1 |   645 |   25690 | postgres | postgres | SELECT pid, usename, application_name, client_addr, query_start, state, query FROM p
g_stat_activity; | succeeded | 9 rows         | 2024-09-05 23:22:00.036856+08 | 2024-09-05 23:22:00.040284+08
     2 |   644 |   25685 | postgres | postgres | VACUUM FULL
                 | succeeded | VACUUM         | 2024-09-05 23:22:00.031437+08 | 2024-09-05 23:22:04.904608+08
     2 |   642 |     696 | postgres | postgres | VACUUM FULL
                 | succeeded | VACUUM         | 2024-09-05 23:21:00.448666+08 | 2024-09-05 23:21:04.599976+08
(5 rows)

postgres=#

3.6、删除定时任务

通过任务名称删除定时任务，语法如下：
SELECT cron.unschedule(’<定时任务名称>’);
pg_cron允许创建重复的定时任务名称，当删除名称重复的定时任务时，只会删除任务ID较小的定时任务。

示例如下：

删除名为Do Vacuum的定时任务：

postgres=# SELECT cron.unschedule('process-select_Fun');
 ######  unschedule
------------
 t
(1 row)

postgres=#

根据ID删除定时任务

postgres=# SELECT cron.unschedule(4);
 ###### unschedule
------------
 t
(1 row)

postgres=#

四、注意事项

注意事项

确保libpq连接是安全的，可能需要配置pg_hba.conf。

注意max_worker_processes和cron.max_running_jobs的设定，避免资源耗尽。

监控pg_cron的日志，确保作业按计划执行

五、总结

pg_cron是一个为PostgreSQL数据库设计的扩展，它提供了基于cron的作业调度功能。以下是pg_cron的一些优势：

简单易用：用户可以通过执行SQL语句来创建、调度和管理定时任务，无需额外的编程或配置。
灵活的调度选项：支持按照分钟、小时、日期、星期、月份及其组合进行定时调度，与Unix Cron的语法兼容。
库级别的任务管理：定时任务保存在数据库中，可以跨多个数据库共享和管理任务。
并发任务执行：支持同时执行多个任务，提高任务执行效率。
可靠性和容错性：提供错误处理和容错机制，确保任务的正确执行并提供相应的日志记录。优化的并发执行机制：确保每个任务实例在任何时刻至多只有一个在执行，后续的任务会挂起，直到前一个执行完成。
安全性：作业以执行cron.schedule函数的用户的权限执行，用户只能看到自己的作业。
兼容性：支持PostgreSQL 9.5或更高版本，可以在数据库内部运行，不依赖外部调度服务。 pg_cron为数据库管理员和开发人员提供了一种在数据库层面自动化- 执行维护任务、数据处理作业或定期执行SQL逻辑的强大工具。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

信创数据库学习中心（共80篇）

信创数据库学习中心

PostgreSQL运维工具之任务调度插件pg_cron

说明：定时计划在工作中经常会用到，在PostgreSQL系列的数据库系统中，pg_cron或许可以作为您的备选方案之一，用于定时执行某个计划，如：vacuum、收集统计信息，定时执行函数等，本文整理了一些常用定时脚本供您参考。

一、pg_cron功能概述

1.1、pg_cron是什么？

1.2、pg_cron主要功能

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

示例如下：

注意事项

二、pg_cron安装

2.1、安装说明

安装要求：

2.2、下载源码

2.3、源码安装

2.4、设置pg_cron

2.5、重启PostgreSQL服务

说明：创建管理账户admin，密码为Admin#123.，授权访问模式 cron。

三、使用方法

3.1、添加定时任务

3.1.1、每天11:00 PM（GMT）对Postgres库执行VACUUM

3.1.2、每分钟执行指定SQL

3.1.3、每月1号和30号以及每周六和周日的2:30 AM（GMT）执行磁盘清理

3.2、指定定时任务名称

语法：

3.2.1、每周六的10:00 AM（GMT）删除过期数据

3.2.2、每天的11:00 PM（GMT）对Postgres库执行VACUUM

3.2.3、每分钟执行指定SQL

3.3、指定数据库执行定时任务

说明：

3.3.2、每5分钟执行一次函数。

3.3.3、每天的10:00 AM（GMT）让sy用户在postgres库查询city表：

3.4、更改定时任务

示例如下：

3.4.1、修改jobid=3的执行计划

3.4.2、任务ID为9的定时任务的执行用户更改为sy：

3.5、查看作业调度情况

示例如下：

查看失败的任务详情：

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

3.5.1、查看定时任务列表

3.6、删除定时任务

示例如下：

删除名为Do Vacuum的定时任务：

根据ID删除定时任务

四、注意事项

注意事项

五、总结

pg_cron是一个为PostgreSQL数据库设计的扩展，它提供了基于cron的作业调度功能。以下是pg_cron的一些优势：

文章被以下合辑收录

评论