pg_cron is a PostgreSQL extension that provides cron-style job scheduling directly in the database. It runs SQL commands on a schedule without external cron daemons or application code. Most managed PostgreSQL providers (AWS RDS, Supabase, Neon) support pg_cron.

What if pg_cron is not available on my provider?

Use your application scheduler (cron, systemd timer, CI/CD pipeline, or a task queue) to run the SQL commands on schedule. The important thing is that the commands run regularly and results are logged in the audit trail.

Automating PostgreSQL Compliance with pg_cron: Set It and Forget It

Why Automate Compliance

The number one compliance failure mode is not missing capabilities — it is not running them. You set up health checks, retention policies, and drift detection... and then forget to run them for three months.

One-Command Setup

SELECT pgcomply.schedule_jobs(install := true);

This creates:

| Job | Schedule | What It Does | |-----|----------|-------------| | pgcomply-retention | Daily 03:00 | Enforce all retention policies | | pgcomply-health | Monday 06:00 | Run 14 CIS security checks | | pgcomply-drift | Monday 07:00 | Scan for unregistered PII | | pgcomply-sessions | Every 15 min | Log active session snapshot |

Manual pg_cron Setup

If you want custom schedules:

-- Daily retention at 4 AM
SELECT cron.schedule('retention', '0 4 * * *',
  'SELECT pgcomply.enforce_retention()');

-- Health check every Monday and Thursday at 7 AM
SELECT cron.schedule('health', '0 7 * * 1,4',
  'SELECT pgcomply.health_check()');

-- Drift detection after deployments (run manually or schedule)
SELECT cron.schedule('drift', '0 8 * * 1',
  'SELECT pgcomply.schema_drift()');

-- Session snapshot every 10 minutes
SELECT cron.schedule('sessions', '*/10 * * * *',
  'SELECT pgcomply.log_sessions()');

Verifying Automation

Check that jobs are running:

-- pg_cron job history
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 20;

-- pgcomply audit trail shows every automated execution
SELECT * FROM pgcomply.audit_log
WHERE executed_by = 'pg_cron'
ORDER BY created_at DESC LIMIT 20;

Alerting on Failures

Combine with webhooks (Pro) to get notified when checks fail:

SELECT pgcomply.add_webhook(
  'https://hooks.slack.com/your-webhook',
  ARRAY['health_check_fail', 'drift_detected', 'breach_reported']
);

Custom Scheduling Patterns

Beyond the default four jobs, common patterns for compliance automation:

Monthly Compliance Report

SELECT cron.schedule('monthly-report', '0 8 1 * *',
  $$SELECT pgcomply.compliance_report()$$);
-- Pro: generates structured JSON on the 1st of every month

Post-Deployment Drift Check

Trigger drift detection after every deployment window:

-- If deployments happen Tuesday/Thursday evenings
SELECT cron.schedule('post-deploy-drift', '0 22 * * 2,4',
  $$SELECT pgcomply.schema_drift()$$);

Weekend Security Audit

SELECT cron.schedule('weekend-audit', '0 6 * * 6',
  $$SELECT pgcomply.health_check(); SELECT pgcomply.connection_audit()$$);

Monitoring Job Health

pg_cron maintains execution history:

-- Check last 10 job runs
SELECT jobid, command, status, return_message,
  start_time, end_time,
  end_time - start_time AS duration
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Look for:

failed status — the command threw an error
Long durations — retention enforcement taking hours means too much data accumulated
Missing runs — if the database was down during the scheduled time

-- Find jobs that haven't run recently
SELECT jobid, schedule, command,
  (SELECT max(start_time) FROM cron.job_run_details d WHERE d.jobid = j.jobid) AS last_run
FROM cron.job j;

When pg_cron Is Not Available

Some managed providers do not support pg_cron. Alternatives:

GitHub Actions Cron

name: Weekly Compliance
on:
  schedule:
    - cron: '0 6 * * 1'
jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - run: |
          psql "$DATABASE_URL" -c "SELECT pgcomply.enforce_retention();"
          psql "$DATABASE_URL" -c "SELECT * FROM pgcomply.health_check();"
          psql "$DATABASE_URL" -c "SELECT * FROM pgcomply.schema_drift();"

systemd Timer (Self-Hosted)

# /etc/systemd/system/pgcomply-health.timer
[Timer]
OnCalendar=Mon *-*-* 06:00:00
Persistent=true

[Install]
WantedBy=timers.target

Application-Level Cron

Most frameworks have built-in job schedulers (Sidekiq, Celery, Bull). Schedule a job that runs the pgcomply SQL commands.

The key requirement: whatever scheduler you use, the results must end up in the pgcomply audit trail. All pgcomply functions log their execution automatically, so any calling method works.

Summary

Automated compliance is the only reliable compliance. pgcomply.schedule_jobs() sets up four essential automation jobs in one command. Every execution is logged in the immutable audit trail, providing continuous evidence of compliance monitoring — exactly what auditors want to see.