How to get alerts when pg_cron jobs fail

pg_cron is the standard extension for scheduling recurring tasks directly inside PostgreSQL. It runs SQL commands on a cron schedule without requiring an external scheduler, supports all standard cron expressions, and logs execution results to cron.job_run_details.

The logging is where pg_cron's observability ends.

When a pg_cron job fails, the failure is recorded in cron.job_run_details with a status of failed and an error message. No external alert fires. Nothing notifies the team. The failure sits in a database table until someone queries it — which typically happens after a downstream effect makes the failure visible.

When a pg_cron job succeeds but produces no useful output — a vacuum that finds nothing to clean, a materialised view refresh that processes zero new rows, a cleanup job whose filter matches nothing — pg_cron records the run as succeeded. There's no mechanism to evaluate whether the job accomplished anything.

Bridging this gap requires combining pg_cron's scheduling with a notification layer that fires alerts to channels your team actually monitors.

Option 1: Notify from within the pg_cron job using pg_notify

If your application is already listening on PostgreSQL's LISTEN/NOTIFY channel, you can send a notification from inside the pg_cron job:

-- A pg_cron job that notifies on completion
SELECT cron.schedule(
  'nightly-cleanup',
  '0 2 * * *',
  $$
  DO $$
  DECLARE
    deleted_count INTEGER;
  BEGIN
    DELETE FROM stale_records
    WHERE created_at < now() - INTERVAL '90 days';
    GET DIAGNOSTICS deleted_count = ROW_COUNT;

    -- Notify the application with the result
    PERFORM pg_notify(
      'cron_result',
      json_build_object(
        'job', 'nightly-cleanup',
        'status', 'success',
        'rows_deleted', deleted_count,
        'executed_at', now()
      )::text
    );
  END;
  $$ LANGUAGE plpgsql;
  $$
);

Your application listens on the cron_result channel and sends the alert to Slack or email. This works well when your application process is always running and can be trusted to relay notifications. It doesn't help with missed run detection — if pg_cron itself doesn't fire, no notification is sent.

Option 2: HTTP pings from a PostgreSQL function using http extension

If your PostgreSQL instance has the http extension installed (available on some managed providers), you can send HTTP pings directly from a pg_cron job:

-- Install the http extension (if available on your provider)
CREATE EXTENSION IF NOT EXISTS http;

-- Wrapper function that runs a job and pings Crontify
CREATE OR REPLACE FUNCTION run_and_monitor_cleanup()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  deleted_count INTEGER;
  monitor_id TEXT := 'your-monitor-id';
  api_key TEXT := current_setting('app.crontify_api_key');
  base_url TEXT := 'https://api.crontify.com/api/v1/ping/';
BEGIN
  -- Start ping
  PERFORM http_post(
    base_url || monitor_id || '/start',
    '',
    'application/json'
  );

  -- Do the actual work
  DELETE FROM stale_records
  WHERE created_at < now() - INTERVAL '90 days';
  GET DIAGNOSTICS deleted_count = ROW_COUNT;

  -- Success ping with metadata
  PERFORM http_post(
    base_url || monitor_id || '/success',
    json_build_object('meta', json_build_object('rows_deleted', deleted_count))::text,
    'application/json'
  );

EXCEPTION WHEN OTHERS THEN
  -- Fail ping with error message
  PERFORM http_post(
    base_url || monitor_id || '/fail',
    json_build_object('message', SQLERRM)::text,
    'application/json'
  );
  RAISE;
END;
$$;

-- Store the API key as a database setting (not hardcoded)
ALTER DATABASE your_database SET app.crontify_api_key = 'ck_live_your_key';

-- Schedule it
SELECT cron.schedule('nightly-cleanup', '0 2 * * *', 'SELECT run_and_monitor_cleanup()');

The http extension is available on some managed PostgreSQL providers but not all. Check your provider's documentation before relying on this approach.

Option 3: Companion monitoring job using NOTIFY and a sidecar

For environments where the http extension isn't available, a sidecar process approach works reliably. A small service subscribes to PostgreSQL LISTEN/NOTIFY and forwards job results to Crontify via HTTP:

// monitor-relay.ts — runs as a persistent sidecar process
import { Client } from 'pg';
import fetch from 'node-fetch';

const db = new Client({ connectionString: process.env.DATABASE_URL });
const CRONTIFY_API_KEY = process.env.CRONTIFY_API_KEY!;

const MONITOR_MAP: Record<string, string> = {
  'nightly-cleanup': 'mon_abc123',
  'daily-aggregation': 'mon_def456',
};

async function main() {
  await db.connect();
  await db.query('LISTEN cron_result');

  db.on('notification', async (msg) => {
    if (!msg.payload) return;

    const payload = JSON.parse(msg.payload);
    const monitorId = MONITOR_MAP[payload.job];
    if (!monitorId) return;

    const event = payload.status === 'success' ? 'success' : 'fail';

    await fetch(
      `https://api.crontify.com/api/v1/ping/${monitorId}/${event}`,
      {
        method: 'POST',
        headers: {
          'X-API-Key': CRONTIFY_API_KEY,
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({ meta: payload }),
      }
    );
  });

  console.log('Monitoring relay started, listening for pg_cron notifications');
}

main().catch(console.error);

Your pg_cron jobs emit PERFORM pg_notify('cron_result', ...) on completion. The relay forwards the notification to Crontify. The monitor holds the expected schedule and fires a missed run alert if no notification arrives within the grace period.

Querying cron.job_run_details for recent failures

While waiting for the above infrastructure to be in place, this query gives you immediate visibility into pg_cron failures:

-- Jobs that failed in the last 24 hours
SELECT
  j.jobname,
  r.start_time,
  r.end_time,
  r.status,
  r.return_message
FROM cron.job_run_details r
JOIN cron.job j ON j.jobid = r.jobid
WHERE r.status = 'failed'
  AND r.start_time > now() - INTERVAL '24 hours'
ORDER BY r.start_time DESC;

-- Jobs that haven't run in longer than expected
SELECT
  j.jobname,
  j.schedule,
  MAX(r.start_time) AS last_run,
  now() - MAX(r.start_time) AS time_since_last_run
FROM cron.job j
LEFT JOIN cron.job_run_details r ON r.jobid = j.jobid
GROUP BY j.jobname, j.schedule
ORDER BY time_since_last_run DESC NULLS FIRST;

You can schedule this query as a pg_cron job itself — running every hour and sending results to a Slack webhook via a notification function. It's not a substitute for external monitoring (if pg_cron stops running, this query also stops), but it adds a layer of visibility with no additional infrastructure.

Silent failure detection for pg_cron jobs

pg_cron records a job as succeeded when the SQL command completes without raising an exception. A DELETE that deletes zero rows is a success. A REFRESH MATERIALIZED VIEW that processes no new data is a success.

If you use Option 2 (http extension) or Option 3 (sidecar relay), you can attach row counts and evaluation results to the success ping. In Crontify's dashboard, define rules:

• rows_deleted eq 0 → fire alert (cleanup ran but deleted nothing)
• rows_aggregated eq 0 → fire alert (aggregation processed no new data)

The job is still recorded as succeeded in pg_cron. You get an external alert when the output falls outside expected ranges.

Crontify is free for up to 5 monitors — no credit card required.