pg_cron debugging guide
Last edited: 1/15/2025
This is a general guide for debugging pg_cron. Below lists issues and how to debug them
Cannot create/edit/delete cron jobs
Cron jobs can only be modified with the respective SQL functions:
- Schedule: cron.schedule
- Alter: cron.alter_job
- Delete: cron.unschedule
If you are trying to make changes, please use the cron functions. If the cron functions are inaccessible, please contact Support
Cron Jobs are not running
You should consider initiating a software upgrade in the Infrastructure Settings if your Postgres version is below v15.6.1.122. Upgrading will give you access to pg_cron v1.6.4+, which has many bug fixes and auto-revive capabilities.
Debugging steps:
Check to see if "pg_cron scheduler" is active
pg_cron operates as the pg_cron scheduler process within Postgres. Use the below query to check if the worker is active
_10SELECT_10 pid as process_id,_10 usename as database_user,_10 application_name,_10 backend_start as when_process_began,_10 wait_event_type,_10 state,_10 query,_10 backend_type_10FROM 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:
Check the cron.job_run_details table for more information
pg_cron creates logs in its own table cron.job_run_details". The below query checks for issues from the past 5 days :
_10SELECT *_10FROM cron.job_run_details_10WHERE_10 (status <> 'succeeded' AND status <> 'running')_10 AND_10 start_time > NOW() - INTERVAL '5 days'_10ORDER BY start_time DESC_10LIMIT 10;
Respond to the errors exposed appropriately.
Long running jobs may show timeout errors. For jobs that are intended to execute for extended periods, consider wrapping their queries in functions with custom timeouts (guide).
Check if there are too many cron jobs running concurrently
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.
The below queries shows all pg_cron jobs:
_10-- All jobs_10select schedule, jobname, command from cron.job;_10_10-- Count jobs_10select COUNT(*) from cron.job;
The below query can be used to find actively querying jobs:
_10SELECT_10 pid as process_id,_10 usename as database_user,_10 application_name,_10 backend_start as when_process_began,_10 wait_event_type,_10 state,_10 query,_10 backend_type_10FROM pg_stat_activity where application_name ilike 'pg_cron';
You can view your peak connection usage throughout the day at the bottom of the Reports Dashboard
Check for database strain
Unfortunately, excessive resource strain can slow down or disrupt jobs.
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:
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
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
_20select_20 cast(postgres_logs.timestamp as datetime) as timestamp,_20 event_message,_20 parsed.error_severity,_20 parsed.user_name,_20 parsed.query,_20 parsed.detail,_20 parsed.hint,_20 parsed.sql_state_code,_20 parsed.backend_type,_20 parsed.application_name_20from_20 postgres_logs_20 cross join unnest(metadata) as metadata_20 cross join unnest(metadata.parsed) as parsed_20where_20 regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')_20 and regexp_contains(parsed.application_name, 'pg_cron')_20order by timestamp desc_20limit 100;
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.
Create custom logs within cron jobs
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:
_21create or replace function log_example()_21returns void_21language plpgsql_21as $$_21begin_21_21 -- Logging start of function_21 raise log 'logging start of cron function call: (%)', (select now());_21_21 -- INSERT LOGIC HERE_21_21 -- Logging end of function_21 raise log 'logging end of cron function call: (%)', (select now());_21_21 exception_21 -- Handle exceptions here if needed_21 when others then_21 raise exception 'An error occurred in cron function <insert name here>. ERROR MESSAGE: %', sqlerrm;_21_21end;_21$$;
You can then search for your custom messages in the Logs Interface
Upgrading pg_cron version
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
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.