Monitoring and debugging cron jobs

pg_cron records every run of every job: when it started and ended, whether it succeeded, and the message the SQL returned. On SupaHost you can read that history in the dashboard or with SQL. This page shows where to look, the queries that answer the usual questions, and how to debug jobs that call HTTP endpoints.

Recent runs in the dashboard

Dashboard → your project → Cron tab → Recent runs lists the last 50 runs across the project's jobs, with the job, status, start time and result. The page refreshes every 30 seconds.

The job list above it shows each job's schedule, whether it's Active, and its last run. If a job shows "Not yet", it hasn't run since it was created.

Run history with SQL

In Studio's SQL editor, cron.job_run_details holds the last 1,000 runs of your project's jobs. Join it to cron.job to see names:

select
  j.jobname,
  d.status,
  d.start_time,
  d.end_time - d.start_time as duration,
  left(d.return_message, 200) as message
from cron.job_run_details d
join cron.job j using (jobid)
order by d.start_time desc
limit 25;

Useful columns:

Failures in the last day

select
  j.jobname,
  count(*) filter (where d.status = 'failed') as failed,
  count(*) as runs,
  max(d.start_time) filter (where d.status = 'failed') as last_failure
from cron.job_run_details d
join cron.job j using (jobid)
where d.start_time > now() - interval '1 day'
group by j.jobname
order by failed desc;

Slow jobs

select
  j.jobname,
  round(avg(extract(epoch from d.end_time - d.start_time))::numeric, 2) as avg_seconds,
  round(max(extract(epoch from d.end_time - d.start_time))::numeric, 2) as max_seconds
from cron.job_run_details d
join cron.job j using (jobid)
where d.end_time is not null
group by j.jobname
order by max_seconds desc;

A job whose runs take longer than its interval falls behind, because pg_cron won't start a new run of a job while the previous one is still going.

How long history is kept

Two limits apply:

• cron.job_run_details shows at most your project's last 1,000 runs. A job that runs every 30 seconds fills that in about 8 hours.
• Runs older than 14 days are deleted every day at 03:15 UTC by a SupaHost maintenance job.

If you need a longer record, for example for an audit, have the job write its own row to a table you keep. See Cleaning up cron run history.

Debugging a job that fails

1. Read the error. The return_message of a failed run is the Postgres error, such as relation "public.booking" does not exist.
2. Run the SQL yourself. Copy the job's command from cron.job into the SQL editor. Studio connects as postgres, the same role jobs run as, so permission errors show up the same way.
3. Check names and schemas. Jobs run in your project's database, but don't assume a search path. Write public.bookings, not bookings.
4. Check the job is active. select jobname, active from cron.job; A paused job doesn't run.
5. Check the schedule. Times are UTC. A job at 0 9 * * * runs at 09:00 UTC, which may not be 09:00 for you. See Cron syntax and examples.

If a job never appears in the history at all, see Cron job not running.

Debugging HTTP calls from jobs

A job that calls net.http_post succeeds as soon as the request is queued, so cron.job_run_details says succeeded even if the endpoint returned an error. The response is stored separately by pg_net:

select id, status_code, timed_out, error_msg, left(content, 300) as body, created
from net._http_response
order by created desc
limit 20;

What to look for:

For Edge Functions, the request also appears in Dashboard → your project → Functions tab → Logs, with the method, path, status and duration. See Logs and debugging and Debugging database webhooks and pg_net.

Alert yourself when jobs fail

SupaHost's dashboard doesn't send alerts for failed cron jobs. A small watchdog job can do it: every 15 minutes it looks for new failures and posts them to a chat webhook.

-- Store the webhook URL once
select vault.create_secret('https://hooks.slack.com/services/T000/B000/XXXX', 'alerts_webhook');

select cron.schedule(
  'cron-failure-alert',
  '*/15 * * * *',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'alerts_webhook'),
    body := jsonb_build_object(
      'text',
      'Cron failures in the last 15 minutes: ' || string_agg(distinct j.jobname, ', ')
    )
  )
  from cron.job_run_details d
  join cron.job j using (jobid)
  where d.status = 'failed'
    and d.start_time > now() - interval '15 minutes'
    and j.jobname <> 'cron-failure-alert'
  having count(*) > 0;
  $$
);

When there are no failures, the having clause returns no rows and nothing is sent.

Related

• Scheduled jobs with pg_cron
• Cron job not running
• Stopping cron jobs on a restored project or branch
• Monitoring, health checks and alerts