使用 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
扩展:
-
通过向
shared_preload_libraries
参数值添加pg_cron
,修改与 PostgreSQL 数据库实例关联的自定义参数组。重新启动 PostgreSQL 数据库实例,以使对参数组的更改生效。要了解有关使用参数组的更多信息,请参阅Amazon Aurora PostgreSQL 参数。
-
重新启动 PostgreSQL 数据库实例后,使用具有
rds_superuser
权限的账户运行以下命令。例如,如果在创建 Aurora PostgreSQL 数据库实例时使用默认设置,请以用户postgres
身份进行连接,然后创建扩展。CREATE EXTENSION pg_cron;
pg_cron
调度程序是在名为postgres
的默认 PostgreSQL 数据库中设置的。这些pg_cron
对象是在此postgres
数据库中创建的,所有调度操作都在此数据库中运行。 -
您可以使用默认设置,也可以计划作业在 PostgreSQL 数据库实例的其他数据库中运行。要为 PostgreSQL 数据库实例中的其他数据库计划作业,请参阅 为原定设置数据库以外的数据库计划 cron 任务 中的示例。
授予数据库用户使用 pg_cron 的权限
安装 pg_cron
扩展需要 rds_superuser
权限。但是,可以(由 pg_cron
组/角色的成员)将使用 rds_superuser
的权限授予其他数据库用户,以便他们可以计划自己的任务。我们建议您仅在需要时才授予对 cron
架构的权限,前提是它可以改进生产环境中的操作。
要在 cron
架构中授予数据库用户权限,请运行以下命令:
postgres=>
GRANT USAGE ON SCHEMA cron TOdb-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
文件中。有关更多信息,请参阅在 Amazon Aurora 中修改数据库参数组中的参数。
使用以下命令检查 cron.log_run
参数的值。
postgres=>
SHOW cron.log_run;
有关更多信息,请参阅用于管理 pg_cron 扩展的参数。
为原定设置数据库以外的数据库计划 cron 任务
pg_cron
的元数据全部保存在名为 postgres
的 PostgreSQL 默认数据库中。由于后台工件用于运行维护 cron 作业,因此您可以在 PostgreSQL 数据库实例中的任何数据库中计划作业。
-
在 cron 数据库中,以与平常使用 cron.schedule 相同的方式计划作业。
postgres=>
SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
-
作为具有
rds_superuser
角色的用户,请更新刚创建的作业的数据库列,使其在 PostgreSQL 数据库实例中的另一个数据库中运行。postgres=>
UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
-
通过查询
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 |
保存 |
cron.host |
要连接到 PostgreSQL 的主机名。您无法修改此值。 |
cron.log_run |
在 |
cron.log_statement |
在运行所有 cron 语句之前将其记入日志。值为 |
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 数据库实例的其他数据库中运行,请参阅 为原定设置数据库以外的数据库计划 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_name
或 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)
用于计划任务和捕获状态的表
将以下各表用于计划 cron 作业和记录作业完成的方式。
表 | 描述 |
---|---|
cron.job |
包含有关每个计划作业的元数据。与此表的大多数交互应使用 重要我们不建议直接授予对此表的更新或插入权限。这样做将允许用户更新 |
cron.job_run_details |
包含过去运行的计划作业的历史信息。这对于调查运行的作业的状态、返回消息以及开始和结束时间非常有用。 注意为了防止此表无限增长,请定期清除此表。有关示例,请参阅清除 pg_cron 历史记录表。 |