Schedule automated database tasks with PG_CRON - PolarDB

pg_cron is a cron-based job scheduler for PostgreSQL 9.5 and later, running directly inside your database as an extension. It uses standard cron expression syntax, so you can schedule any SQL command—from routine VACUUM runs to custom data archival queries—without an external scheduler.

Important

Before using pg_cron, upgrade your kernel to V1.1.19 or later. Earlier versions have a known memory leak risk.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL cluster running kernel V1.1.19 or later

Enable pg_cron

pg_cron is disabled by default. To enable it, apply for access through the Quota Center:

Go to Quota Center.
Find polardb_pg_pg_cron in the quota list.
Click Apply in the Actions column.

You cannot enable it manually with CREATE EXTENSION.

Usage notes

Scheduled jobs run in GMT.
All jobs are stored in the default PostgreSQL database, but you can query them from any database.
For security considerations, you can execute scheduled tasks only by using system functions. The system allows you to add and delete scheduled tasks. Common users can only view scheduled tasks in the cron.job table.

Schedule a job

Each scheduled job has two components: a cron expression that defines when it runs, and a SQL command to execute.

-- Schedule a job in the default PostgreSQL database
SELECT cron.schedule('<cron-expression>', '<sql-command>');

-- Schedule a job in a specific database
SELECT cron.schedule('<cron-expression>', '<sql-command>', '<database-name>');

If you omit the database parameter, the default PostgreSQL database is used.

cron.schedule() returns a numeric jobid that you use to manage the job later.

Cron expression syntax

A cron expression has five space-separated fields:

┌─────────────── Minute (0–59)
│ ┌───────────── Hour (0–23)
│ │ ┌─────────── Day of month (1–31)
│ │ │ ┌───────── Month (1–12)
│ │ │ │ ┌─────── Day of week (0–6, where 0 = Sunday; 7 still indicates a weekend day)
│ │ │ │ │
* * * * *

Special characters:

Character	Meaning	Example
`*`	Every value in the field	`* * * * *` runs every minute

Examples

Purge stale data every Saturday

-- Delete events older than 1 week at 3:30 AM GMT every Saturday
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

Output:

 schedule
----------
       42

Run VACUUM daily

-- Run VACUUM at 10:00 AM GMT every day
SELECT cron.schedule('0 10 * * *', 'VACUUM');

Output:

 schedule
----------
       43

Run a command every minute

SELECT cron.schedule('* * * * *', 'select 1;');

Output:

 schedule
----------
       44

Run a command at the 23rd minute of every hour

SELECT cron.schedule('23 * * * *', 'select 1;');

Output:

 schedule
----------
       45

Run a command on the 4th day of every month

SELECT cron.schedule('* * 4 * *', 'select 1;');

Output:

 schedule
----------
       46

Manage jobs

View scheduled jobs

SELECT * FROM cron.job;

Example output:

 jobid |  schedule   |  command  | nodename  | nodeport | database | username | active
-------+-------------+-----------+-----------+----------+----------+----------+--------
    43 | 0 10 * * *  |  VACUUM;  | localhost |     5433 | postgres | test     | t

Delete a job

-- Delete the job with jobid 42
SELECT cron.unschedule(42);

Output:

 unschedule
------------
          t

Disable pg_cron

To disable the extension, run:

DROP EXTENSION pg_cron;