Monitor pg_cron jobs in Postgres

pg_cron records each run in a table that nobody queries until something has already gone wrong. Send a ping from inside the database when the job commits, and we email you when a run fails or doesn't happen.

You need: a Lateping account, Postgres with pg_cron and pg_net, about 10 minutes.

What can go wrong#

pg_cron runs SQL on a schedule inside the database. Its record of what happened is the cron.job_run_details table, which you only read when you already suspect a problem.

Set it up#

1. Create a check#

   Create a check in Lateping with the same cron expression as the job. Set its timezone to match cron.timezone, which is GMT (UTC) unless your server or provider changed it:

   sqlCopy

   SHOW cron.timezone;
   

2. Enable pg_net#

   pg_net sends HTTP requests from SQL. It’s available on Supabase and many other hosts.

   sqlCopy

   CREATE EXTENSION IF NOT EXISTS pg_net;
   

   pg_net only sends a request after the transaction that made it commits. If the transaction rolls back, the request is never sent. That’s what makes the pattern below work: the success ping goes out only when the job’s changes are saved.

3. Wrap the job#

   Put the job in a function that pings on success and on failure, then schedule the function.

   nightly_cleanup.sqlCopy

   CREATE OR REPLACE FUNCTION nightly_cleanup_monitored() RETURNS void
   LANGUAGE plpgsql AS $$
   DECLARE
     ping_url text := 'https://lateping.com/p/<check-id>';
   BEGIN
     BEGIN
       -- Your job goes here.
       DELETE FROM sessions WHERE expires_at < now() - interval '30 days';
     EXCEPTION WHEN OTHERS THEN
       -- The job's changes are rolled back to this point. The /fail request
       -- below still commits, so it is sent.
       PERFORM net.http_post(url := ping_url || '/fail');
       RAISE WARNING 'nightly_cleanup failed: %', SQLERRM;
       RETURN;
     END;
     PERFORM net.http_post(url := ping_url);
   END $$;
   
   SELECT cron.schedule('nightly-cleanup', '17 2 * * *', 'SELECT nightly_cleanup_monitored()');
   

   • If the job succeeds, its changes and the success ping commit together.
   • If the job errors, its changes are undone, /fail is sent and alerts straight away, and the error message goes to the Postgres log as a warning.
   • If the job doesn’t run at all, no ping arrives and the check alerts after the grace period.

4. Test it#

   Run the function by hand. The check should show a ping within a few seconds.

   sqlCopy

   SELECT nightly_cleanup_monitored();
   

   To see the request pg_net sent, and the response it got:

   sqlCopy

   SELECT status_code, error_msg, created FROM net._http_response ORDER BY created DESC LIMIT 5;
   

Notes#

• Because the exception is caught, pg_cron records a failed job as succeeded in cron.job_run_details. Lateping, and the warning in your Postgres log, are where the failure shows up. If you’d rather pg_cron recorded the failure, drop the EXCEPTION block. A failed job then rolls back its success ping with it, pg_cron records failed, and the check alerts after the grace period instead of straight away.
• There’s no /start ping in this pattern. pg_net sends requests only after the transaction commits, so a start ping would arrive at the same moment as the result.
• If your database can’t make HTTP requests (pg_net isn’t available on every host), run the job from a server’s crontab with psql instead, and follow the crontab guide: psql "$DATABASE_URL" -c 'SELECT nightly_cleanup();' && curl -fsS -m 10 --retry 3 https://lateping.com/p/<check-id>.
• Alerts go by email. On Pro and above they can also go to Slack, Discord or a webhook.