pg_cron

什么是 pg_cron？

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

pg_cron 还允许使用"$"来表示每月的最后一天。

-- 在星期六凌晨 3:30（GMT）删除旧数据
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
       42

-- 每天上午 10:00（GMT）进行 VACUUM
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- 更改为凌晨 3:00（GMT）进行 VACUUM
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- 停止调度作业
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t

SELECT cron.unschedule(42);
 unschedule
------------
          t

-- 每周日凌晨 4:00（GMT）在安装了 pg_cron 的数据库以外的数据库中进行 VACUUM
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');
 schedule
----------
       44

-- 每 5 秒调用一次存储过程
SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');

-- 每月最后一天的 12:00 处理工资
SELECT cron.schedule('process-payroll', '0 12 $ * *', 'CALL process_payroll()');

pg_cron 可以并行运行多个作业，但一次最多运行一个作业的实例。如果第二次运行应该在第一次完成之前开始，则第二次运行将排队，并在第一次运行完成后立即开始。

调度使用标准的 cron 语法，其中 * 表示"每个时间段运行"，特定数字表示"仅在此时间运行"：

 ┌───────────── 分钟 (0 - 59)
 │ ┌────────────── 小时 (0 - 23)
 │ │ ┌─────────────── 月份中的日期 (1 - 31) 或月份的最后一天 ($)
 │ │ │ ┌──────────────── 月份 (1 - 12)
 │ │ │ │ ┌───────────────── 星期几 (0 - 6) (0 到 6 分别是周日到
 │ │ │ │ │                  周六，或使用名称；7 也是周日)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

创建 cron 调度的简便方法是：crontab.guru。

pg_cron 中处理解析和调度的代码直接来自 Paul Vixie 的 cron 源代码，因此支持相同的选项。

安装 pg_cron

在 Red Hat、CentOS、Fedora、Amazon Linux 上使用 PostgreSQL 16 通过 PGDG 安装：

# 安装 pg_cron 扩展
sudo yum install -y pg_cron_16

在 Debian、Ubuntu 上使用 PostgreSQL 16 通过 apt.postgresql.org 安装：

# 安装 pg_cron 扩展
sudo apt-get -y install postgresql-16-cron

您也可以通过从源代码构建来安装 pg_cron：

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# 确保 pg_config 在您的路径中，例如
export PATH=/usr/pgsql-16/bin:$PATH
make && sudo PATH=$PATH make install

设置 pg_cron

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

# 添加到 postgresql.conf

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

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

# 添加到 postgresql.conf

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

pg_cron 只能安装在集群中的一个数据库中。如果您需要在多个数据库中运行作业，请使用 cron.schedule_in_database()。

以前 pg_cron 只能使用 GMT 时间，但现在您可以通过在 postgresql.conf 中设置 cron.timezone 来调整您的时间。

# 添加到 postgresql.conf

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

重启 PostgreSQL 后，您可以使用 CREATE EXTENSION pg_cron 创建 pg_cron 函数和元数据表。

-- 以超级用户身份运行：
CREATE EXTENSION pg_cron;

-- 可选，向普通用户授予使用权限：
GRANT USAGE ON SCHEMA cron TO marco;

确保 pg_cron 可以启动作业

重要：默认情况下，pg_cron 使用 libpq 打开到本地数据库的新连接，这需要得到 pg_hba.conf 的允许。可能需要为运行 cron 作业的用户启用来自 localhost 的连接的 trust 身份验证，或者您可以将密码添加到 .pgpass 文件中，libpq 在打开连接时会使用该文件。

您还可以使用 unix 域套接字目录作为主机名，并在 pg_hba.conf 中为本地连接启用 trust 身份验证，这通常是安全的：

# 通过 unix 域套接字连接：
cron.host = '/tmp'

# 也可以是空字符串，以查找默认目录：
cron.host = ''

或者，pg_cron 可以配置为使用后台工作进程。在这种情况下，并发作业的数量受 max_worker_processes 设置的限制，因此您可能需要提高该值。

# 通过后台工作进程而不是 localhost 连接调度作业
cron.use_background_workers = on
# 将可用后台工作进程的数量从默认的 8 增加
max_worker_processes = 20

出于安全考虑，作业在调用 cron.schedule 函数的数据库中执行，具有与当前用户相同的权限。此外，用户只能在 cron.job 表中看到自己的作业。

-- 查看活动作业
select * from cron.job;

查看作业运行详情

您可以在 cron.job_run_details 中查看正在运行和最近完成的作业运行的状态：

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            │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│    10 │  4328 │    2610 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:30:00.098164+01 │ 2023-02-07 09:30:00.130729+01 │
│    10 │  4327 │    2609 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:29:00.015168+01 │ 2023-02-07 09:29:00.832308+01 │
│    10 │  4321 │    2603 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:28:00.011965+01 │ 2023-02-07 09:28:01.420901+01 │
│    10 │  4320 │    2602 │ postgres │ marco    │ select process()  │ failed    │ server restarted │ 2023-02-07 09:27:00.011833+01 │ 2023-02-07 09:27:00.72121+01  │
│     9 │  4320 │    2602 │ postgres │ marco    │ select do_stuff() │ failed    │ job canceled     │ 2023-02-07 09:26:00.011833+01 │ 2023-02-07 09:26:00.22121+01  │
└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘
(10 rows)

cron.job_run_details 中的记录不会自动清理，但是每个可以安排定时任务的用户都有权限删除自己的 cron.job_run_details 记录。

特别是当你有每隔几秒就运行一次的任务时，定期清理是个好主意，这可以通过 pg_cron 本身轻松完成：

-- 每天中午删除当前用户7天前的 cron.job_run_details 记录
SELECT  cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

如果你完全不想使用 cron.job_run_details，那么你可以在 postgresql.conf 中添加 cron.log_run = off。

使用案例示例

展示 pg_cron 可能用途的文章：

托管服务

下表跟踪了主要的托管 Postgres 服务中哪些支持 pg_cron。

服务	是否支持
Aiven	:heavy_check_mark:
阿里云	:heavy_check_mark:
Amazon RDS	:heavy_check_mark:
Azure	:heavy_check_mark:
Crunchy Bridge	:heavy_check_mark:
DigitalOcean	:heavy_check_mark:
Google Cloud	:heavy_check_mark:
Heroku	:x:
ScaleGrid	:heavy_check_mark:
Scaleway	:heavy_check_mark:
Supabase	:heavy_check_mark:
Tembo	:heavy_check_mark: