Scheduled Jobs with pg_cron

DB9 includes pg_cron for running SQL on a schedule. You can automate cleanup tasks, periodic data syncs, API polling, health checks, and any other SQL workflow that needs to run at regular intervals.

This guide walks through creating jobs, monitoring their execution, handling failures, and combining scheduled jobs with other DB9 extensions.

Prerequisites

• A DB9 database (see Quick Start)
• The pg_cron extension is enabled by default — no CREATE EXTENSION needed

Running CREATE EXTENSION pg_cron is harmless but unnecessary. All functions live in the cron schema.

1. Schedule a Job

Use cron.schedule() with a name, cron expression, and SQL command:

SQL

SELECT cron.schedule('cleanup-old-logs', '0 3 * * *',
  'DELETE FROM logs WHERE created_at < now() - INTERVAL ''30 days''');

This creates a job named cleanup-old-logs that runs daily at 3:00 AM UTC. The function returns the job ID.

Cron expression format

Standard 5-field syntax — minute, hour, day of month, month, day of week:

Output

┌───────────── minute (0-59)
│ ┌───────────── hour (0-23)
│ │ ┌───────────── day of month (1-31)
│ │ │ ┌───────────── month (1-12)
│ │ │ │ ┌───────────── day of week (0-6, Sunday=0)
│ │ │ │ │
* * * * *

Common patterns:

Expression	Meaning
* * * * *	Every minute
*/5 * * * *	Every 5 minutes
0 * * * *	Every hour
0 3 * * *	Daily at 3:00 AM
0 0 * * 0	Weekly on Sunday at midnight
0 12 1 * *	Monthly on the 1st at noon
30 9 * * 1-5	Weekdays at 9:30 AM

Wildcards (*), ranges (1-5), steps (*/5), and lists (1,3,5) are all supported. Shorthand like @daily or @hourly is not supported — use the explicit 5-field form.

Upsert semantics

If you call cron.schedule() with the same job name, it updates the existing job instead of creating a duplicate:

SQL

-- First call creates the job
SELECT cron.schedule('sync', '*/15 * * * *', 'SELECT sync_data()');

-- Second call with same name updates schedule and command
SELECT cron.schedule('sync', '*/5 * * * *', 'SELECT sync_data_v2()');
-- Returns the same job ID

This makes it safe to put cron.schedule() calls in migration scripts or seed files — they are idempotent.

2. List and Inspect Jobs

View all scheduled jobs:

SQL

SELECT jobid, jobname, schedule, active, next_run_at
FROM cron.job;

Column	Description
jobid	Auto-assigned job ID
jobname	Name you provided (or NULL for anonymous jobs)
schedule	Cron expression
command	SQL to execute
active	Whether the job is enabled
next_run_at	Computed next execution time (ISO 8601, UTC)

You can also use the CLI:

Terminal

db9 db cron <database> list

3. Monitor Execution History

Every job run is recorded in cron.job_run_details:

SQL

SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY runid DESC
LIMIT 10;

Status values:

Status	Meaning
starting	Queued, not yet executing
running	Currently executing
succeeded	Completed without error
failed	Execution raised an error
cancelled	Stopped by cron.cancel()

Find failed runs

SQL

SELECT jobid, command, return_message, start_time
FROM cron.job_run_details
WHERE status = 'failed'
ORDER BY runid DESC
LIMIT 20;

The return_message column contains the error text, which helps diagnose failures.

Check run duration

SQL

SELECT
  jobid,
  status,
  start_time,
  end_time
FROM cron.job_run_details
WHERE jobid = 1
ORDER BY runid DESC
LIMIT 5;

Run history is retained for 7 days and then automatically cleaned up.

CLI shortcut

Terminal

db9 db cron <database> history
db9 db cron <database> history --limit 50
db9 db cron <database> history --job cleanup-old-logs

4. Modify and Manage Jobs

Disable a job (pause without deleting)

SQL

SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, false);

The job remains in cron.job but stops executing until re-enabled.

Re-enable a job

SQL

SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, true);

Change the schedule

SQL

SELECT cron.alter_job(1, '0 4 * * *', NULL, NULL, NULL, NULL);

Set a per-job timeout

SQL

-- Allow this job up to 30 minutes before it is marked failed
SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, NULL, '30min');

Supported formats: 30min, 2h, 60s, 5000ms, or a plain integer (milliseconds). The default timeout is 5 minutes.

Delete a job

SQL

SELECT cron.unschedule('cleanup-old-logs');  -- by name
SELECT cron.unschedule(1);                   -- by ID

Deleting a job also removes its run history.

CLI equivalents:

Terminal

db9 db cron <database> enable <name_or_id>
db9 db cron <database> disable <name_or_id>
db9 db cron <database> delete <name_or_id>

5. Practical Patterns

Periodic cleanup

SQL

SELECT cron.schedule('expire-sessions', '*/15 * * * *',
  'DELETE FROM sessions WHERE expires_at < now()');

Aggregate metrics on a schedule

SQL

SELECT cron.schedule('hourly-metrics', '0 * * * *',
  $$INSERT INTO hourly_stats (hour, total_events, unique_users)
    SELECT date_trunc('hour', now() - INTERVAL '1 hour'),
           count(*), count(DISTINCT user_id)
    FROM events
    WHERE created_at >= now() - INTERVAL '1 hour'$$);

Use dollar quoting ($$...$$) for multi-line or quote-heavy commands.

Poll an external API with http

Combine pg_cron with the http extension to fetch data on a schedule:

SQL

SELECT cron.schedule('poll-status', '*/5 * * * *',
  $$INSERT INTO api_snapshots (fetched_at, status, body)
    SELECT now(), status, content::jsonb
    FROM extensions.http_get('https://api.example.com/status')$$);

Write periodic reports to the filesystem

Combine with fs9 to write scheduled outputs:

SQL

SELECT cron.schedule('daily-report', '0 6 * * *',
  $$SELECT fs9_write('/reports/' || to_char(now(), 'YYYY-MM-DD') || '.csv',
    (SELECT string_agg(id || ',' || name || ',' || total, E'\n')
     FROM daily_summary))$$);

6. Cancel a Running Job

If a job is running too long, a superuser can cancel it:

SQL

SELECT cron.cancel(1);  -- by job ID

To see what is currently running:

SQL

SELECT run_id, job_id, command, elapsed_ms
FROM cron.running_jobs;

Both cron.cancel() and cron.running_jobs require superuser privileges.

Limits and Caveats

• Max 50 jobs per database — additional cron.schedule() calls fail once this limit is reached.
• Max 32 concurrent executions — across all databases on the same server.
• Poll interval — the scheduler checks for due jobs every 60 seconds. Jobs cannot execute more frequently than once per minute.
• No sub-minute scheduling — the minimum granularity is one minute.
• No shorthand expressions — @daily, @hourly, @reboot are not supported. Use the 5-field form.
• No cross-database scheduling — jobs always execute in the database where they were created.
• Run history retention — 7 days by default. Older records are automatically cleaned up.
• Superuser required — cron.cancel() and cron.running_jobs require the admin role. cron.schedule(), cron.unschedule(), and cron.alter_job() work for any user but are scoped to their own jobs.

Next Pages

• pg_cron Extension Reference — function signatures and cron expression details
• HTTP from SQL — call external APIs that you can combine with scheduled jobs
• Analyze Agent Logs with fs9 — write and query files from SQL
• Extensions Overview — all 9 built-in extensions
• CLI Reference — db9 db cron commands for job management