Monitoring and History¶

Two functions answer the two questions pg_cron users ask of cron.job and cron.job_run_details: what is scheduled? and how did it go?

What is scheduled — `list_scheduled_jobs()`¶

SELECT * FROM pgrelay.list_scheduled_jobs();

One row per live job, ordered by name:

Column	Meaning
`id`	The job's stable id — its `pgrelay.log` history key, accepted by `cancel()`
`schedule_name`	The name you gave `schedule_job()`
`channel`	The channel each run fires
`schedule`	The spec, exactly as registered
`payload`	The payload each run passes to the action
`next_run`	When the Processor will next launch it (`infinity` while paused indefinitely)
`expire_at`	The schedule's end date, if any
`created_at`	When the job was first registered
`updated_at`	When `schedule_job()` last (re-)registered it
`first_run_at`	When its first run launched (`NULL` until then)
`run_count`	How many runs have launched, ever
`paused`	The effective pause state right now
`paused_until`	`infinity`, a timestamp, or `NULL` — see the pause chapter

Handy variations:

-- Jobs due in the next 10 minutes:
SELECT schedule_name, next_run FROM pgrelay.list_scheduled_jobs()
WHERE NOT paused AND next_run < now() + interval '10 minutes'
ORDER BY next_run;

-- Jobs that have never run (waiting on a future first_run, or just created):
SELECT schedule_name, next_run FROM pgrelay.list_scheduled_jobs()
WHERE first_run_at IS NULL;

-- Everything currently paused:
SELECT schedule_name, paused_until FROM pgrelay.list_scheduled_jobs() WHERE paused;

run_count and first_run_at survive log purges

These two live on the job's own row, maintained at each launch — they are not derived from the audit log, so pgrelay.purge() trimming old log rows never zeroes a job's lifetime counters. A launch that crashed mid-run and rolled back is not counted (it never happened, transactionally); the re-fired attempt is.

How did it go — `scheduled_job_runs()`¶

-- The last 100 runs across every job:
SELECT * FROM pgrelay.scheduled_job_runs();

-- One job's recent history:
SELECT * FROM pgrelay.scheduled_job_runs('nightly_report');

-- Just the latest run:
SELECT * FROM pgrelay.scheduled_job_runs('nightly_report', 1);

-- Everything that completed in the last hour, across every job:
SELECT * FROM pgrelay.scheduled_job_runs(p_within := '1 hour');

-- Just today's failures:
SELECT * FROM pgrelay.scheduled_job_runs(p_within := '1 day', p_status := 'error');

Newest first, one row per audited run, read straight from pgrelay.log via the job's stable id. The three filters — a job name, p_within (a trailing time window), and p_status (one outcome, case-insensitive) — combine freely, and an unknown name or status simply returns no rows:

Column	Meaning
`job_id`, `schedule_name`, `channel`	Which job
`run_log_id`	The `pgrelay.log` row id for this run
`status`	`ok`, `error`, `retry_scheduled`, `invalid`, `overlap_skipped` (a detached ad-hoc occurrence skipped because the previous one was still running)
`error`	The failure text, when there is one
`elapsed_ms`	How long the action took
`ran_at`	When it ran

The statuses are the ordinary pg_relay outcomes, and they mean the ordinary things: error — the action raised (the schedule continued regardless); retry_scheduled — the action raised a transient error and a one-off retry row was queued (a retried run shows both an error row carrying the failure detail and a retry_scheduled row naming the retry); invalid — the job fired while its channel was disabled. A withdrawn or expired job leaves the live set, so its final cancelled/expired audit rows appear only in pgrelay.log itself (queried by the job's id, below), not here.

History reaches back exactly as far as your log retention — pgrelay.purge() trims these rows like any other audit rows, which is why the lifetime counters above live on the job itself. For anything a filter can't answer, the raw audit log is always there:

SELECT * FROM pgrelay.log
WHERE queue_id = (SELECT id FROM pgrelay.list_scheduled_jobs()
                  WHERE schedule_name = 'nightly_report')
ORDER BY actioned_at DESC;

psql shortcuts¶

The queries above come packaged as psql shortcuts in the repository's deploy/pg_relay_shortcuts.sql. Reference it from your ~/.psqlrc —

\i /path/to/pg_relay_shortcuts.sql

— and monitoring becomes two keystrokes and a colon. The full set of shortcuts the file defines:

Shortcut	Shows
`:schedules`	Every live scheduled job, paused included
`:schedules5m`	Jobs whose next run is due within the coming 5 minutes
`:schedules15m`	… within the coming 15 minutes
`:schedules1h`	… within the coming hour
`:schedules4h`	… within the coming 4 hours
`:schedules8h`	… within the coming 8 hours
`:schedules1d`	… within the coming day
`:jobs`	The last 99 completions of scheduled jobs, newest first, every status
`:jobs5m`	Completions in the trailing 5 minutes
`:jobs15m`	… in the trailing 15 minutes
`:jobs1h`	… in the trailing hour
`:jobs4h`	… in the trailing 4 hours
`:jobs8h`	… in the trailing 8 hours
`:jobs1d`	… in the trailing day
`:jobs_err`	The last 99 completions that were not `ok`, newest first
`:jobs5m_err`	Not-`ok` completions in the trailing 5 minutes
`:jobs15m_err`	… in the trailing 15 minutes
`:jobs1h_err`	… in the trailing hour
`:jobs4h_err`	… in the trailing 4 hours
`:jobs8h_err`	… in the trailing 8 hours
`:jobs1d_err`	… in the trailing day
`:queue`	The pending (undispatched) event rows, in dispatch order, up to 99 — ids feed `cancel()`
`:queue_done`	The last 99 completed queue items of any kind, from the audit log

For example:

relay_dev=# :schedules1h    -- what fires within the hour?
relay_dev=# :jobs1d_err     -- what went wrong today?
relay_dev=# :queue          -- what is waiting right now?

Each shortcut is a plain query macro over the management read functions (list_scheduled_jobs(), scheduled_job_runs(), queue_pending(), log_report()), so it works for any role with the pgrelay.grant_user() grant — nothing extra to install in the database.

When a job doesn't fire — a checklist¶

Is it paused? list_scheduled_jobs() → paused.
Is next_run actually in the past? If it's infinity or far in the future, the job is doing what it was told — check the spec with SELECT pgrelay._next_run('<spec>') and remember cron runs on server time.
Is the channel active? A disabled channel makes runs log invalid — visible in scheduled_job_runs() — while the job keeps ticking.
Is a run still executing? A job never overlaps itself; a stuck run blocks the next occurrence until it finishes. elapsed_ms in the history shows how long runs normally take.
Is the Processor running — and is the fleet paused? Scheduled jobs ride the same Processor as everything else: check pgrelay.queue_stats() and the fleet's pause state like you would for any quiet queue.
Multi-node: a job on a node-restricted channel runs only on its own node; an unrestricted job created on a dead node is adopted per your mode's rules.

Housekeeping¶

A live job's row is never touched by purge_queue() — only a withdrawn job's cancelled remains are, on the same schedule as every other processed row. Run history is pgrelay.log, governed by pgrelay.purge(). Nothing new to schedule — and if you'd like the purging itself to be a scheduled job, that's now one call.

Continue to How It Works.