使用 PostgreSQL pg_cron 扩展计划维护 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

使用 PostgreSQL pg_cron 扩展计划维护

您可以使用 PostgreSQL pg_cron 扩展来计划 PostgreSQL 数据库中的维护命令。有关扩展的更多信息,请参阅 pg_cron 文档中的什么是 pg_cron?

Aurora PostgreSQL 引擎版本 12.6 及更高版本支持 pg_cron 扩展

要了解有关使用 pg_cron 的更多信息,请参阅在 RDS for PostgreSQL 或 Aurora PostgreSQL 兼容版数据库上使用 pg_cron 计划任务

设置 pg_cron 扩展

按如下方式设置 pg_cron 扩展:

  1. 通过向 shared_preload_libraries 参数值添加 pg_cron,修改与 PostgreSQL 数据库实例关联的自定义参数组。

    重新启动 PostgreSQL 数据库实例,以使对参数组的更改生效。要了解有关使用参数组的更多信息,请参阅Amazon Aurora PostgreSQL 参数

  2. 重新启动 PostgreSQL 数据库实例后,使用具有 rds_superuser 权限的账户运行以下命令。例如,如果在创建 Aurora PostgreSQL 数据库实例时使用默认设置,请以用户 postgres 身份进行连接,然后创建扩展。

    CREATE EXTENSION pg_cron;

    pg_cron 调度程序是在名为 postgres 的默认 PostgreSQL 数据库中设置的。这些 pg_cron 对象是在此 postgres 数据库中创建的,所有调度操作都在此数据库中运行。

  3. 您可以使用默认设置,也可以计划作业在 PostgreSQL 数据库实例的其他数据库中运行。要为 PostgreSQL 数据库实例中的其他数据库计划作业,请参阅 为 postgres 以外的数据库计划 cron 作业 中的示例。

授予数据库用户使用 pg_cron 的权限

安装 pg_cron 扩展需要 rds_superuser 权限。但是,可以(由 pg_cron 组/角色的成员)将使用 rds_superuser 的权限授予其他数据库用户,以便他们可以计划自己的任务。我们建议您仅在需要时才授予对 cron 架构的权限,前提是它可以改进生产环境中的操作。

要在 cron 架构中授予数据库用户权限,请运行以下命令:

postgres=> GRANT USAGE ON SCHEMA cron TO db-user;

这向 db-user 授予访问 cron 架构的权限,以便为他们有权限访问的对象计划 cron 任务。如果数据库用户没有权限,则在将错误消息发布到 postgresql.log 文件后,任务会失败,如下所示:

2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name 2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1

换言之,确保被授予对 cron 架构的权限的数据库用户也对他们计划安排的对象(表、架构等)拥有权限。

cron 任务的详细信息以及它的成功或失败情况也在 cron.job_run_details 表中捕获。有关更多信息,请参阅用于计划任务和捕获状态的表

计划 pg_cron 作业

以下各节介绍了如何使用 pg_cron 作业安排各种管理任务。

注意

创建 pg_cron 任务时,请检查 max_worker_processes 设置是否大于 cron.max_running_jobs 的数量。如果 pg_cron 任务耗尽后台工作进程,它将失败。原定设置的 pg_cron 任务数量为 5。有关更多信息,请参阅用于管理 pg_cron 扩展的参数

对表执行清理操作

Autovacuum 在大多数情况下处理清理维护。但是,您可能希望在选择的时间计划对特定表执行清理操作。

以下示例介绍了使用 cron.schedule 函数设置作业,以便每天 22:00 (GMT) 在特定表上使用 VACUUM FREEZE

SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts'); schedule ---------- 1 (1 row)

运行上述示例之后,您可以按如下方式检查 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)

下面的内容说明如何查询 cron.job_run_details 表以查看失败的任务。

postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed'; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time ------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------ 5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00 (1 row)

有关更多信息,请参阅用于计划任务和捕获状态的表

清除 pg_cron 历史记录表

cron.job_run_details 表包含 cron 作业的历史记录,随着时间的推移,这些历史记录可能会变得非常大。我们建议您计划清除此表的作业。例如,保留一周的条目可能足以进行故障排除。

以下示例使用 cron.schedule 函数计划每天午夜运行以清除 cron.job_run_details 表的作业。这项工作只保留了过去七天的历史记录。使用您的 rds_superuser 账户计划作业,如下所示。

SELECT cron.schedule('0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

有关更多信息,请参阅用于计划任务和捕获状态的表

仅将错误记录到 postgresql.log 文件中

要防止向 cron.job_run_details 表中进行写入,请修改与 PostgreSQL 数据库实例关联的参数组,然后将 cron.log_run 参数设置为 off。如果您执行此操作,pg_cron 扩展不再写入表,只会将错误捕获到 postgresql.log 文件中。有关更多信息,请参阅修改数据库参数组中的参数

使用以下命令检查 cron.log_run 参数的值。

postgres=> SHOW cron.log_run;

有关更多信息,请参阅“用于管理 pg_cron 扩展的参数”。

postgres 以外的数据库计划 cron 作业

pg_cron 的元数据全部保存在名为 postgres 的 PostgreSQL 默认数据库中。由于后台工件用于运行维护 cron 作业,因此您可以在 PostgreSQL 数据库实例中的任何数据库中计划作业。

  1. 在 cron 数据库中,以与平常使用 cron.schedule 相同的方式计划作业。

    postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
  2. 作为具有 rds_superuser 角色的用户,请更新刚创建的作业的数据库列,使其在 PostgreSQL 数据库实例中的另一个数据库中运行。

    postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
  3. 通过查询 cron.job 表进行验证。

    postgres=> SELECT * FROM cron.job; jobid | schedule | command | nodename | nodeport | database | username | active | jobname ------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+------------------------- 106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum 1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum (2 rows)
注意

在某些情况下,您可以添加打算在其他数据库上运行的 cron 作业。在这些情况下,在您更新正确的数据库列之前,该作业可能会尝试在默认数据库 (postgres) 中运行。如果用户名具有权限,则作业将在默认数据库中成功运行。

pg_cron 扩展的参考

您可以将以下参数、函数和表与 pg_cron 扩展搭配使用。有关更多信息,请参阅 pg_cron 文档中的什么是 pg_cron?

用于管理 pg_cron 扩展的参数

以下是用于控制 pg_cron 扩展行为的参数列表。

参数 描述

cron.database_name

保存 pg_cron 元数据的数据库。

cron.host

要连接到 PostgreSQL 的主机名。您无法修改此值。

cron.log_run

job_run_details 表中记录运行的每个任务。值为 onoff。有关更多信息,请参阅“用于计划任务和捕获状态的表 ”。

cron.log_statement

在运行所有 cron 语句之前将其记入日志。值为 onoff

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;

函数参考:cron.schedule

此函数计划 cron 作业。作业最初是在默认 postgres 数据库中计划的。该函数返回一个表示作业标识符的 bigint 值。要计划作业在 PostgreSQL 数据库实例的其他数据库中运行,请参阅 为 postgres 以外的数据库计划 cron 作业 中的示例。

该函数有两种语法格式。

语法
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)

函数参考:cron.unschedule

此函数删除 cron 作业。您可以指定 job_namejob_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)

用于计划任务和捕获状态的表

将以下各表用于计划 cron 作业和记录作业完成的方式。

描述
cron.job

包含有关每个计划作业的元数据。与此表的大多数交互应使用 cron.schedulecron.unschedule 函数完成。

重要

我们不建议直接授予对此表的更新或插入权限。这样做将允许用户更新 username 列,从而以 rds-superuser 身份运行。

cron.job_run_details

包含过去运行的计划作业的历史信息。这对于调查运行的作业的状态、返回消息以及开始和结束时间非常有用。

注意

为了防止此表无限增长,请定期清除此表。有关示例,请参阅清除 pg_cron 历史记录表