pg_cron: Job Scheduling
The pg_cron extension is a simple cron-based job scheduler for PostgreSQL that runs inside the database.
Usage#
Enable the extension#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_cron" and enable the extension.
Syntax#
The schedule uses the standard cron syntax, in which * means "run every time period", and a specific number means "but only at this time":
┌───────────── min (0 - 59) │ ┌────────────── hour (0 - 23) │ │ ┌─────────────── day of month (1 - 31) │ │ │ ┌──────────────── month (1 - 12) │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to │ │ │ │ │ Saturday, or use names; 7 is also Sunday) │ │ │ │ │ │ │ │ │ │ * * * * *
Examples#
Delete data every week#
Delete old data on Saturday at 3:30am (GMT):
1select cron.schedule (
2 'webhook-every-minute', -- name of the cron job
3 '* * * * *', -- every minute
4 $$ delete from events where event_time < now() - interval '1 week' $$
5);
Run a vacuum every day#
Vacuum every day at 3:00am (GMT)
1SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
Unschedule a job#
Unschedules a job called 'nightly-vacuum'
1SELECT cron.unschedule('nightly-vacuum');