Postgres as a CRON Server

Summary

Postgres databases can schedule and execute external webhook calls periodically by combining the pg_cron and pgsql-http extensions. When configured, pg_cron schedules recurring tasks using standard cron syntax directly inside the database engine. In conjunction, the pgsql-http extension dispatches HTTP requests such as GET, POST, PATCH, and DELETE to external endpoints within SQL queries. Because pg_cron relies on background workers that start one process per server loop iteration, scheduled jobs do not interfere with core postmaster tasks or external client connections. Administrators can monitor job status, review execution logs, and unschedule active tasks directly through dedicated cron schema tables and helper functions.

Context

A Supabase user needed to trigger webhooks periodically before Supabase released its Functions feature, prompting an investigation into whether standard Postgres could manage scheduled external HTTP requests.

Approach / What changed

Enable the pg_cron and pgsql-http extensions in Postgres, grant cron schema permissions to non-superuser roles, and schedule periodic queries containing http_post calls with cron.schedule.

Takeaways

• Non-superuser roles require explicit USAGE on schema cron and privileges on its underlying tables, or scheduled jobs will fail to run.
• pg_cron executes tasks via background workers that start one process per ServerLoop iteration, preventing slow HTTP requests from blocking core postmaster connection handling.
• Cron jobs and execution histories can be inspected using cron.job and cron.job_run_details, and active tasks can be cancelled using cron.unschedule.

Related reading

• Supabase · Jul 7, 2020

  Physical vs Logical Backups in PostgreSQL

  PostgreSQL backups fall into two distinct categories: logical backups, which convert data into SQL command files, and physical backups, which copy the underlying file system storage. Logical backups generated with pg_dump or pg_dumpall allow single-database targeting and provide the only practical route for migrating across major Postgres versions with differing internal storage formats. Conversely, physical backups suit larger databases where long-running logical exports can degrade concurrent query performance and risk failure. Physical backups also integrate with Write Ahead Log files through tools like WAL-G to support Point in Time Recovery and minimize Recovery Point Objectives. Selecting between these two strategies depends on whether administrative simplicity, version portability, cluster size, or precise disaster recovery takes precedence.

  Angelico de los Reyes

• Supabase · Jul 2, 2021

  Protecting reserved roles with PostgreSQL Hooks