_我最近将我们在 The Org 的分析数据库升级到 PostgreSQL 13.1,以支持使用 pg_cron 定期刷新物化视图以直接在数据库中运行作业。我们之前的设置涉及触发 Lambda 以运行刷新命令的 CloudWatch 事件。

升级到 PostgreSQL 12.5+

Amazon RDS 支持 pg_cron 版本 12.5 及更高版本(来源)。如果您运行的是旧版本,则需要对其进行更新。

您可以在 AWS 控制面板中或运行以下命令检查版本:

SELECT version();

进入全屏模式 退出全屏模式

如果您运行的是 12.5 或更高版本,请直接跳到下一部分。

否则,您需要更新数据库。导航到您的数据库实例并将数据库引擎版本设置为 PostgreSQL 13.1-R1 以对其进行更新。

下一步将要求您更新参数组,这也需要重新启动。同时执行这两个步骤可以减少停机时间。

更新主要版本将需要 10-20 分钟,并使您的数据库暂时不可用。我不会在这里介绍避免停机的策略。

更新参数组

转到参数组并更改 shared_preload_libraries 的值以包含 pg_cron。您不能更新默认参数组。

如果您使用的是 default.postgres13 参数组,则创建一个新参数组,并将参数组系列设置为 postgres13。这会创建模板的副本,然后您可以对其进行修改。

[](https://res.cloudinary.com/practicaldev/image/fetch/s--MAwZvgfX--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to-uploads .s3.amazonaws.com/uploads/articles/wblz4sda82ezgcpom5a0.png)

导航到 RDS 实例并单击修改。在其他配置下设置数据库参数组以使用您刚刚创建的组。如果您只是修改了现有参数组,请跳过此步骤。

[](https://res.cloudinary.com/practicaldev/image/fetch/s--Yyy9Xl3B--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://dev-to-uploads .s3.amazonaws.com/uploads/articles/2y99e1b6sey2djcvsp1u.png)

应用更改。这需要快速重启数据库。

安装pg_cron

以 rds_superuser 身份连接到您的数据库。默认管理员帐户(通常称为 postgres)默认是此用户组的一部分。连接后,在 postgres 数据库中运行以下 SQL:

CREATE EXTENSION pg_cron;

进入全屏模式 退出全屏模式

如果成功,您应该能够在 postgres 数据库中看到一个名为 cron 的模式,其中包含一个作业和 job_run_details 表。失败时的常见错误包括:

错误:pg_cron 只能通过 shared_preload_libraries 加载

参数组未正确更新。确保您更新了正确的参数组并将更改应用到数据库。数据库重新启动可能处于挂起状态。

错误:创建扩展“pg_cron”的权限被拒绝

您作为身份验证的用户没有安装 pg_cron 的权限。确保您使用的是具有管理员权限的帐户。 postgres 和 rdsadmin 都应该这样做。

错误:只能在数据库 postgres 中创建扩展

您连接到错误的数据库。确保您正在对 postgres 数据库运行命令。

使用 pg_cron 添加 Cron 作业

安装 pg_cron 后,您可以使用以下语法开始调度 SQL 命令:

SELECT cron.schedule(SCHEDULE, COMMAND);
SELECT cron.schedule(JOB_NAME, SCHEDULE, COMMAND);

进入全屏模式 退出全屏模式

默认情况下,这些 cron 作业将在 postgres 数据库上下文中运行。我们使用 pg_cron 的用例是定期更新物化视图,因此我们需要更新执行命令的数据库。这是通过首先调度作业然后更新 cron.job 表中的数据库列来完成的。

SELECT cron.schedule(
    'Refresh weekly analytics',
    '5 0 * * 1',
    $$REFRESH MATERIALIZED VIEW web_client.company_weekly_analytics$$
)

UPDATE cron.job SET database = 'theorg' WHERE jobid = 1

进入全屏模式 退出全屏模式

这将在我们的主数据库中每周一 0:05 运行该命令。

pg_cron 中的速成课程

pg_cron 背后的概念相当简单。计划作业存储在 cron.job 表中,其中包含要执行的命令和执行时间。计划以常规 cron 表达式表示 - 您可以使用 crontab.guru 生成正确的 cron 表达式。

/* See all scheduled jobs. */
SELECT * FROM cron.job

/* Query to confirm that is was successful and check running times. */
SELECT * FROM cron.job_run_details

/* Schedule a job */
SELECT cron.schedule(JOB_NAME, SCHEDULE, COMMAND);

/* Unschedule a job */
SELECT cron.unschedule(JOB_ID);

/* Update cron expression - this would run daily at 3:14 */
UPDATE cron.job SET schedule = '14 3 * * *' WHERE jobid = 1

进入全屏模式 退出全屏模式

每次运行都会在 cron.job\run\details 表中添加一个条目。查询它以确认您的 cron 作业已成功执行。

免责声明

考虑在您的用例中直接在数据库中运行 cron 作业是否是正确的解决方案。它可能会使您的数据库变得混乱,耗尽资源并从您的代码库中隐藏业务关键信息 — 可能会给您的同事带来一些非常讨厌的调试会话。

我个人不建议直接在数据库中运行清理或数据修改作业。我们的用例是确保我们的物化视图(出于性能原因必须物化)保持更新。我们的替代方法是使用 CloudWatch 事件定期触发连接到数据库并刷新视图的 AWS lambda。

运行 Lambda 来刷新视图会增加成本、复杂性并涉及更多可能失败的移动部件。这种方法也不是很明显,因为您必须知道 lambda 存在并且会定期触发。在我们的例子中,使用 pg_cron 是较小的邪恶。