PolarDB:pg_cron

Prerequisites

pg_cron is supported on PolarDB for PostgreSQL clusters running:

• PostgreSQL 14, revision version 14.9.14.0 or later

• PostgreSQL 11, revision version 1.1.1 or later

To check your revision version, run one of the following:

-- PostgreSQL 14
SELECT version();

-- PostgreSQL 11
SHOW polar_version;

Known issue: port change on cluster restart

On clusters running minor engine version 14.10.16.0 or earlier, restarting the cluster changes the cluster port, which causes pg_cron tasks to fail. This issue is fixed in minor engine version 14.10.16.1 and later.

How it works

Scheduled task storage

All scheduled tasks are stored in the cron.job table. When the database starts, pg_cron builds an in-memory JOB LIST and TASK LIST from this table. The cron.job_cache_invalidate trigger keeps these lists synchronized with cron.job in real time whenever tasks are added, modified, or removed.

Task execution lifecycle

Each task moves through the following states:

State	Description
WAITING	Default state. The task waits until its scheduled time and activation conditions are met.
START	Establishes connection information and runs a connection test. On success, moves to CONNECTING. On failure, moves to ERROR.
CONNECTING	Verifies activation status and connection health. On success, moves to SENDING. On failure, moves to ERROR.
SENDING	Sends the task to the PolarDB for PostgreSQL server. On success, moves to RUNNING. On failure, moves to ERROR.
RUNNING	Waits for the task result. On success, moves to DONE. On failure, moves to ERROR.
ERROR	Task failed. Resets connection information, then moves to DONE.
DONE	Task complete. Resets task information, then returns to WAITING.

Permissions

Only privileged accounts can create, modify, and delete scheduled tasks. Standard accounts have read-only access to the cron.job table.

All scheduled tasks are stored in the postgres database. Connect to postgres to manage tasks.

All tasks run on Greenwich Mean Time (GMT). Convert your local time to GMT before setting a schedule.

Enable pg_cron

Connect to the postgres database and run:

CREATE EXTENSION pg_cron;

To remove the extension:

DROP EXTENSION pg_cron;

Manage scheduled tasks

SELECT cron.schedule(
    'job_name',   -- Task name (optional)
    'schedule',   -- Cron expression
    'command'     -- SQL statement to run
);
-- Returns: jobid (BIGINT)

SELECT cron.schedule_in_database(
    'job_name',   -- Task name
    'schedule',   -- Cron expression
    'command',    -- SQL statement to run
    'db_name'     -- Target database
);
-- Returns: jobid (BIGINT)

SELECT cron.schedule_in_database('task1', '* * * * *', 'SELECT 1', 'db01');

 schedule_in_database
----------------------
                    1
(1 row)

SELECT * FROM cron.job;

 jobid | schedule  | command  | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
     1 | * * * * * | SELECT 1 | /tmp     |    39361 | db01     | u1       | t      | task1
(1 row)

SELECT cron.alter_job(
    job_id   => 1,              -- Task ID (required)
    schedule => '0 10 * * *',  -- New schedule (optional)
    command  => 'VACUUM;',     -- New SQL statement (optional)
    db_name  => 'db01',        -- New target database (optional)
    active   => true           -- Enable or disable the task (optional)
);

-- Delete by task ID
SELECT cron.unschedule(1);

-- Delete by task name
SELECT cron.unschedule('task1');

 unschedule
------------
 t
(1 row)

View execution history

SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
     1 |     2 | 4152438 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:56:00.006468+00 | 2023-10-19 03:56:00.006822+00
     1 |     1 | 4152316 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:55:00.020442+00 | 2023-10-19 03:55:00.021512+00
(2 rows)

The status column has three possible values:

To filter for failed tasks:

SELECT * FROM cron.job_run_details WHERE status = 'failed' ORDER BY start_time DESC;

Cron expression reference

pg_cron uses the standard cron expression format:

┌───────────── Minute (0–59)
│ ┌────────────── Hour (0–23)
│ │ ┌─────────────── Day of month (1–31)
│ │ │ ┌──────────────── Month (1–12)
│ │ │ │ ┌───────────────── Day of week (0–6, where 0 and 7 = Sunday)
│ │ │ │ │
* * * * *

Common schedule patterns:

Examples:

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

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

-- Run a query every minute
SELECT cron.schedule('heartbeat', '* * * * *', 'SELECT 1;');