Skip to content

pg_cron debugging guide

这是一个调试 pg_cron 的一般指南。下面列出了问题以及如何调试它们

🌐 This is a general guide for debugging pg_cron. Below lists issues and how to debug them

无法创建/编辑/删除定时任务 #

🌐 Cannot create/edit/delete cron jobs

Cron 作业只能通过相应的 SQL 函数进行修改：

🌐 Cron jobs can only be modified with the respective SQL functions:

如果你想进行更改，请使用 cron 功能。如果无法访问 cron 功能，请联系 支持

🌐 If you are trying to make changes, use the cron functions. If the cron functions are inaccessible, contact Support


定时任务没有运行 #

🌐 Cron Jobs are not running

如果你的 Postgres 版本低于 v15.6.1.122，你应该考虑在基础设施设置中启动软件升级。升级后，你将可以使用 pg_cron v1.6.4+，它有很多错误修复和自动恢复功能。

调试步骤： #

🌐 Debugging steps:

检查一下 pg_cron scheduler#

🌐 Check to see if pg_cron scheduler is active

pg_cron 在 Postgres 中作为 pg_cron scheduler 进程运行。使用下面的查询来检查工作进程是否活跃

🌐 pg_cron operates as the pg_cron scheduler process within Postgres. Use the below query to check if the worker is active

1
SELECT
2
  pid as process_id,
3
  usename as database_user,
4
  application_name,
5
  backend_start as when_process_began,
6
  wait_event_type,
7
  state,
8
  query,
9
  backend_type
10
FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';

如果查询没有返回结果行，说明工作进程已死亡。要让它恢复，你需要去 常规设置 并启动快速重启：

🌐 If the query does not return a row, the worker has died. To revive it, you must go to the General Settings and initiate a fast reboot:

Screenshot 2024-10-29 at 12 27 24 AM

查看 cron.job_run_details#

🌐 Check the cron.job_run_details table for more information

pg_cron 会在它自己的表 cron.job_run_details 中创建日志。下面的查询用来检查过去 5 天的问题：

🌐 pg_cron creates logs in its own table cron.job_run_details. The below query checks for issues from the past 5 days :

1
SELECT *
2
FROM cron.job_run_details
3
WHERE
4
    (status <> 'succeeded' AND status <> 'running')
5
        AND
6
     start_time > NOW() - INTERVAL '5 days'
7
ORDER BY start_time DESC
8
LIMIT 10;

适当地回应暴露出的错误。

🌐 Respond to the errors exposed appropriately.

长时间运行的作业可能会显示超时错误。对于那些打算长时间执行的作业，可以考虑将它们的查询封装在具有自定义超时的函数中（指南）。


检查是否同时运行的定时任务太多 #

🌐 Check if there are too many cron jobs running concurrently

pg_cron 支持最多 32 个并发作业，每个作业都会使用一个数据库连接。如果同时运行的作业太多，可以将它们错开，以防止连接过载和作业失败。

🌐 pg_cron supports up to 32 concurrent jobs, each using a database connection. If too many jobs are running simultaneously, space them out to prevent connection overload and job failure.

下面的查询显示所有 pg_cron 任务：

🌐 The below queries shows all pg_cron jobs:

1
-- All jobs
2
select schedule, jobname, command from cron.job;
3
4
-- Count jobs
5
select COUNT(*) from cron.job;

下面的查询可以用来查找正在查询的作业：

🌐 The below query can be used to find actively querying jobs:

1
SELECT
2
  pid as process_id,
3
  usename as database_user,
4
  application_name,
5
  backend_start as when_process_began,
6
  wait_event_type,
7
  state,
8
  query,
9
  backend_type
10
FROM pg_stat_activity where application_name ilike 'pg_cron';

你可以在可观察性仪表板底部查看你全天的并发峰值连接使用情况

🌐 You can view your concurrent peak connection usage throughout the day at the bottom of the Observability Dashboard


检查数据库负载 #

🌐 Check for database strain

不幸的是，过度的资源压力可能会减慢或中断工作。

🌐 Unfortunately, excessive resource strain can slow down or disrupt jobs.

前往报告页面（或者如果你已经设置好了，Supabase Grafana），检查是否有资源耗尽的迹象。如果很明显你的数据库正处于压力之下，可以考虑升级你的计算附加组件，或者参考其中一份优化指南的建议：

🌐 Go to the reports page (or Supabase Grafana if you have it setup), and check for signs of resource exhaustion. If it's clear your database is under pressure, consider upgrading your compute add-on or following the advice from one of the optimization guides:

如果你觉得有压力，确保你运行的是最新版本的 pg_cron（1.6.4）很重要。它是最稳定的。

🌐 It is important to make sure you are running the latest release of pg_cron (1.6.4) if you're noticing strain. It is the most robust.


查看日志浏览器以获取更多信息 #

🌐 Check the log explorer for more information

虽然 pg*cron 会在 cron.job_run_details 表中记录错误，但在少数情况下，可以在通用 Postgres 日志中找到更多信息。你可以通过以下查询在 日志浏览器 中查看失败事件

🌐 Although pg*cron records errors in the cron.job_run_details table, in rare cases, more information can be found in the general Postgres logs. You can check the Log Explorer for failure events with the following query

1
select
2
  cast(postgres_logs.timestamp as datetime) as timestamp,
3
  event_message,
4
  parsed.error_severity,
5
  parsed.user_name,
6
  parsed.query,
7
  parsed.detail,
8
  parsed.hint,
9
  parsed.sql_state_code,
10
  parsed.backend_type,
11
  parsed.application_name
12
from
13
  postgres_logs
14
  cross join unnest(metadata) as metadata
15
  cross join unnest(metadata.parsed) as parsed
16
where
17
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
18
  and regexp_contains(parsed.application_name, 'pg_cron')
19
order by timestamp desc
20
limit 100;

如果你有兴趣修改查询，这里有一个用于浏览 Postgres 日志的高级指南，以及一个用于应用过滤器的通用指南。

🌐 If you're interested in modifying the query, there is an advanced guide for navigating the Postgres logs and a general purpose one for applying filters.


在 cron 任务中创建自定义日志 #

🌐 Create custom logs within cron jobs

如果仍然不清楚发生了什么，你可以尝试在数据库函数中运行 pg_cron 查询来捕获更多日志：

🌐 If it's still not clear what is occurring you may be able to capture more logs by running the pg_cron query inside a database function:

1
create or replace function log_example()
2
returns void
3
language plpgsql
4
as $$
5
begin
6
7
     -- Logging start of function
8
     raise log 'logging start of cron function call: (%)', (select now());
9
10
    -- INSERT LOGIC HERE
11
12
     -- Logging end of function
13
     raise log 'logging end of cron function call: (%)', (select now());
14
15
    exception
16
     -- Handle exceptions here if needed
17
     when others then
18
         raise exception 'An error occurred in cron function <insert name here>. ERROR MESSAGE: %', sqlerrm;
19
20
end;
21
$$;

然后你就可以在日志界面搜索你的自定义消息了

🌐 You can then search for your custom messages in the Logs Interface


升级 pg_cron 版本 #

🌐 Upgrading pg_cron version

Supabase 上当前版本的 pg*cron 是 1.6.4。它包含一些 错误修复。你可以考虑在 基础设施设置 中升级到 Postgres v15.6.1.122+ 来获取最新的扩展。

🌐 The current version of pg*cron on Supabase is 1.6.4. It comes with a few bug fixes. You should consider upgrading to Postgres v15.6.1.122+ in the Infrastructure Settings to get the latest extension.


联系支持和维护人员 #

🌐 Contacting support and the maintainers

虽然 Supabase 包含了这个扩展，但它是由 Citus（微软的子公司）维护的。你可以联系支持寻求更多帮助，但你也应该考虑在 pg_cron 仓库 创建一个问题。

🌐 Although Supabase includes the extension, it is maintained by Citus (a Microsoft subsidiary). You can contact Support for more help, but you should also consider creating an issue in the pg_cron repo.