citusdata/pg_cron
GitHub: citusdata/pg_cron
pg_cron 是一个运行在 PostgreSQL 内部的定时任务调度扩展，让用户可以用标准 cron 语法直接在数据库中创建和管理周期性 SQL 作业。
Stars: 3775 | Forks: 249
# 什么是 pg_cron？
pg_cron 是一个基于 cron 的简单作业调度器，适用于 PostgreSQL（10 或更高版本），它作为扩展在数据库内部运行。
# 目录
- [pg_cron 的工作原理](#how-pg_cron-works)
- [Cron 语法](#cron-syntax)
- [管理和创建作业](#managing-and-creating-jobs)
- [创建 cron 作业](#creating-a-cron-job)
- [在不同数据库中创建 cron 作业](#creating-a-cron-job-in-a-different-database)
- [删除 cron 作业](#removing-a-cron-job)
- [修改 cron 作业](#altering-a-cron-job)
- [安装 pg_cron](#installing-pg_cron)
- [设置 pg_cron](#setting-up-pg_cron)
- [监控作业](#monitoring-jobs)
- [示例用例](#example-use-cases)
- [托管服务](#managed-services)
- [行为准则](#code-of-conduct)
# pg_cron 的工作原理
该扩展会创建一个后台工作进程，用于跟踪 `cron.job` 表中的作业。
```
CREATE TABLE cron.job (
jobid bigint primary key default pg_catalog.nextval('cron.jobid_seq'),
schedule text not null,
command text not null,
nodename text not null default 'localhost',
nodeport int not null default pg_catalog.inet_server_port(),
database text not null default pg_catalog.current_database(),
username text not null default current_user
);
```
根据您的配置，为了执行作业，该扩展会建立一个 Postgres 连接或生成一个数据库工作进程。
pg_cron 可以并行运行多个作业，但每个特定作业一次只能运行一个实例。如果在第一个实例完成之前触发了第二个实例，它将被排队，并在第一个实例完成后立即启动。
# Cron 语法
pg_cron 中处理解析和调度的代码直接来源于 [Paul Vixie 的 cron 源码](https://github.com/vixie/cron)，因此支持相同的选项。
```
┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *
```
创建 cron 调度的一种简单方法是使用：[crontab.guru](http://crontab.guru/)。
pg_cron 还允许您：
- 使用 `$` 表示当月的最后一天。
- 使用 `[1-59] seconds` 根据时间间隔来调度作业。请注意，不能将秒数与其他时间单位结合使用。
cron 调度示例：
```
'10 seconds' # every 10 seconds
* * * * * # every minute
*/5 * * * * # every 5 minutes
0 * * * * # every hour
0 0 * * * # daily at 12AM
0 0 * * 1-5 # 12AM every weekday
0 1 * * 0 # 1AM every Sunday
0 13 2 6 * # 1PM on the 2nd of June
```
# 管理和创建作业
如果您拥有所需的权限，可以通过直接与 `cron.job` 表交互来管理 cron 作业。但是，建议使用 cron 函数：
- [`cron.schedule`](#creating-a-cron-job)
- [`cron.schedule_in_database`](#creating-a-cron-job-in-a-different-database)
- [`cron.unschedule`](#removing-a-cron-job)
- [`cron.alter_job`](#altering-a-cron-job)
### 创建 cron 作业
#### `cron.schedule` 签名
```
-- create job, return jobid
CREATE OR REPLACE FUNCTION cron.schedule(schedule text, command text)
RETURNS bigint;
-- create named job, return jobid
CREATE OR REPLACE FUNCTION cron.schedule(job_name text, schedule text, command text)
RETURNS bigint
```
#### 示例
##### 创建一个 cron 作业
```
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule(
'30 3 * * 6',
$$DELETE FROM events WHERE event_time < now() - interval '1 week'$$
);
-- returns cron id
```
##### 创建一个命名的 cron 作业
```
-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule(
'nightly-vacuum',
'0 10 * * *',
'VACUUM'
);
-- returns cron id
```
##### 创建一个每 30 秒运行一次的作业
```
-- run SELECT 1 every 30 seconds
SELECT cron.schedule(
'run_every_30_seconds',
'30 seconds',
'SELECT 1'
);
-- returns cron id
```
##### 创建一个每 5 秒调用一次存储过程的作业
```
-- Call a stored procedure every 5 seconds
SELECT cron.schedule(
'process-updates',
'5 seconds',
'CALL process_updates()'
);
-- returns cron id
```
##### 创建一个在每月最后一天 12:00 处理工资单的作业
```
-- Process payroll at 12:00 of the last day of each month
SELECT cron.schedule(
'process-payroll',
'0 12 $ * *',
'CALL process_payroll()'
);
-- returns cron id
```
### 在不同数据库中创建 cron 作业
#### `cron.schedule_in_database` 签名
```
-- create job, return jobid
CREATE OR REPLACE FUNCTION cron.schedule_in_database(
job_name text,
schedule text,
command text,
database text,
username text DEFAULT NULL::text,
active boolean DEFAULT true
)
RETURNS bigint
```
#### 示例
##### 在不同数据库中创建 cron 作业
```
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule_in_database(
'delete_old_data',
'30 3 * * 6',
$$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,
'some_other_database'
);
-- returns cron id
```
### 删除 cron 作业
#### `cron.unschedule` 签名
```
-- remove job by name, return true if job was removed
CREATE OR REPLACE FUNCTION cron.unschedule(job_name text)
RETURNS boolean
-- remove job by id, return true if job was removed
CREATE OR REPLACE FUNCTION cron.unschedule(job_id bigint)
RETURNS boolean
```
#### 示例
##### 删除一个命名的 cron 作业
```
-- delete job by name
SELECT cron.unschedule('nightly-vacuum');
-- returns true if job was removed
```
##### 通过 id 删除 cron 作业
```
-- delete job by id
SELECT cron.unschedule(42);
-- returns true if job was removed
```
### 修改 cron 作业
#### `cron.alter_job` 签名
```
CREATE OR REPLACE FUNCTION cron.alter_job(
job_id bigint,
schedule text DEFAULT NULL::text,
command text DEFAULT NULL::text,
database text DEFAULT NULL::text,
username text DEFAULT NULL::text,
active boolean DEFAULT NULL::boolean
)
RETURNS void
```
#### 示例
##### 更改作业的调度计划
```
-- change job's schedule
SELECT cron.alter_job(42, '0 10 * * *');
-- returns void
```
##### 更改作业的调度计划、命令和用户名
```
-- change job's command
SELECT cron.alter_job(
42,
'0 10 * * *',
'VACUUM',
username := 'some_other_user'
);
-- returns void
```
##### 停用一个作业
```
-- deactivate job
SELECT cron.alter_job(42, active := false);
-- returns void
```
# 安装 pg_cron
在 Red Hat、CentOS、Fedora、Amazon Linux 上使用 [PGDG](https://yum.postgresql.org/repopackages/) 安装适用于 PostgreSQL 18 的版本：
```
# 安装 pg_cron 扩展
sudo yum install -y pg_cron_18
```
在 Debian、Ubuntu 上使用 [apt.postgresql.org](https://wiki.postgresql.org/wiki/Apt) 安装适用于 PostgreSQL 18 的版本：
```
# 安装 pg_cron 扩展
sudo apt-get -y install postgresql-18-cron
```
您也可以通过从源码构建来安装 pg_cron：
```
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# 确保 pg_config 位于您的路径中，例如
export PATH=/usr/pgsql-18/bin:$PATH
make && sudo PATH=$PATH make install
```
# 设置 pg_cron
要启动 pg_cron 后台工作进程，您需要在 postgresql.conf 的 `shared_preload_libraries` 中添加 pg_cron。请注意，只要服务器处于[热备 (hot standby)](https://www.postgresql.org/docs/current/static/hot-standby.html) 模式，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 函数和元数据表。
```
-- run as superuser:
CREATE EXTENSION pg_cron;
-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO marco;
```
### 确保 pg_cron 能够启动作业
**重要提示**：默认情况下，pg_cron 使用 libpq 打开一个到本地数据库的新连接，这需要得到 [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) 的允许。
对于运行 cron 作业的用户，可能需要为来自 localhost 的连接在 [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) 中启用 `trust` 身份验证，或者您可以将密码添加到 [.pgpass 文件](https://www.postgresql.org/docs/current/static/libpq-pgpass.html)中，libpq 在打开连接时将使用该文件。
您还可以使用 unix 域套接字目录作为主机名，并在 [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) 中为本地连接启用 `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` 表中属于自己的作业。
```
-- View active jobs
select * from cron.job;
```
## 扩展设置
pg_cron 扩展支持以下配置参数：
| 设置 | 默认值 | 描述 |
| ---------------------------------| ----------- | ---------------------------------------------------------------------------------------- |
| `cron.database_name` | `postgres` | pg_cron 后台工作进程应在其中运行的数据库。 |
| `cron.enable_superuser_jobs` | `on` | 允许以超级用户身份调度作业。 |
| `cron.host` | `localhost` | 要连接的 postgres 主机名。 |
| `cron.launch_active_jobs` | `on` | 关闭时，停用所有活动作业，而无需重启服务器 |
| `cron.log_min_messages` | `WARNING` | 启动器后台工作进程的 log_min_messages。 |
| `cron.log_run` | `on` | 在 `cron.job_run_details` 表中记录所有运行的详细信息。 |
| `cron.log_statement` | `on` | 在执行前记录所有 cron 语句。 |
| `cron.max_running_jobs` | `32` | 可同时运行的最大作业数。 |
| `cron.timezone` | `GMT` | pg_cron 后台工作进程运行的时区。 |
| `cron.use_background_workers` | `off` | 使用后台工作进程而不是客户端连接。 |
### 更改设置
要查看设置配置，请运行：
```
SELECT * FROM pg_settings WHERE name LIKE 'cron.%';
```
可以在 postgresql.conf 文件中或使用以下命令更改设置：
```
ALTER SYSTEM SET cron. TO '';
```
`cron.log_min_messages` 和 `cron.launch_active_jobs` 具有 `sighup` 的[设置上下文 (setting context)](https://www.postgresql.org/docs/current/view-pg-settings.html#VIEW-PG-SETTINGS)。可以通过执行 `SELECT pg_reload_conf();` 来使它们生效。
所有其他设置都具有 postmaster 上下文，只有在服务器重启后才会生效。
# 监控作业
### 查看 `cron.job_run_details` 表
您可以在 `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 │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│ 11 │ 4328 │ 2610 │ postgres │ marco │ select pg_sleep(3)│ running │ NULL │ 2023-02-07 09:30:00.098164+01 │ NULL │
│ 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 作业的用户也有权删除自己的 `cron.job_run_details` 记录。
特别是当您有每隔几秒运行一次的作业时，定期清理是个好习惯，这可以使用 pg_cron 本身轻松完成：
```
-- Delete old cron.job_run_details records of the current user every day at noon
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`。
### 其他 cron 日志设置
如果配置了 `cron.log_statement` 设置，作业将在执行前被记录。`cron.log_min_messages` 设置控制将被记录的[最低消息级别](https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS)。
# 示例用例
展示 pg_cron 可能的用法的文章：
* [使用 pg_partman 进行自动分区](https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-postgresql/)
* [在分析仪表板中计算汇总](https://www.citusdata.com/blog/2017/12/27/real-time-analytics-dashboards-with-citus/)
* [删除旧数据，执行 vacuum](https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/)
* [喂猫](http://bonesmoses.org/2016/09/09/pg-phriday-irrelevant-inclinations/)
* [定期调用函数](https://fluca1978.github.io/2019/05/21/pgcron.html)
* [将 Postgres 作为 cron 服务器](https://supabase.io/blog/2021/03/05/postgres-as-a-cron-server)
# 托管服务
下表跟踪了主要的 Postgres 托管服务中哪些支持 pg_cron。
| 服务 | 是否支持 |
| ------------- |:-------------:|
| [Aiven](https://aiven.io/postgresql) | :heavy_check_mark: |
| [Alibaba Cloud](https://www.alibabacloud.com/help/doc-detail/150355.htm) | :heavy_check_mark: |
| [Amazon RDS](https://aws.amazon.com/rds/postgresql/) | :heavy_check_mark: | |
| [Azure](https://azure.microsoft.com/en-us/services/postgresql/) | :heavy_check_mark: |
| [Crunchy Bridge](https://www.crunchydata.com/products/crunchy-bridge/?ref=producthunt) | :heavy_check_mark: |
| [DigitalOcean](https://www.digitalocean.com/products/managed-databases/) | :heavy_check_mark: |
| [Google Cloud](https://cloud.google.com/sql/postgresql/) | :heavy_check_mark: |
| [Heroku](https://elements.heroku.com/addons/heroku-postgresql) | :x: |
| [Instaclustr](https://instaclustr.com) | :heavy_check_mark: |
| [Neon](https://neon.tech/docs/extensions/extensions-intro#tooling-admin) | :heavy_check_mark: |
| [PlanetScale](https://planetscale.com/docs/postgres/extensions) | :heavy_check_mark: |
| [ScaleGrid](https://scalegrid.io/postgresql.html) | :heavy_check_mark: |
| [Scaleway](https://www.scaleway.com/en/database/) | :heavy_check_mark: |
| [Supabase](https://supabase.io/docs/guides/database) | :heavy_check_mark: |
| [YugabyteDB](https://www.yugabyte.com/) | :heavy_check_mark: |
# 行为准则
本项目已采用 [Microsoft 开源行为准则](https://opensource.microsoft.com/codeofconduct/)。有关更多信息，请参阅[行为准则常见问题解答](https://opensource.microsoft.com/codeofconduct/faq/)，或联系 [opencode@microsoft.com](mailto:opencode@microsoft.com) 提出任何其他问题或意见。
标签：cron, Cron语法, DNS解析, pg_cron, PostgreSQL, RDBMS, SQL, 任务调度, 作业调度, 后台任务, 定时任务, 定时脚本, 客户端加密, 并行执行, 开源项目, 插件, 数据库扩展, 数据库管理, 数据库维护, 数据清理, 测试用例, 系统审计, 网络调试, 自动化, 计划任务