PolarDB:pg_cron

Scheduled task information maintenance

All scheduled tasks are stored in the cron.job table. When the database starts, pg_cron builds two internal lists — JOB LIST and TASK LIST — from the cron.job table. The cron.job_cache_invalidate trigger keeps these lists in sync with cron.job in real time whenever tasks are added, modified, or deleted.

Scheduled task execution

Each scheduled task moves through the following states:

cron.schedule

Creates a scheduled task.

cron.schedule(
    job_name TEXT,
    schedule TEXT,
    command  TEXT
)

cron.schedule_in_database

Creates a scheduled task in a specified database.

cron.schedule_in_database(
    job_name TEXT,
    schedule TEXT,
    command  TEXT,
    db_name  TEXT
)

cron.alter_job

Modifies an existing scheduled task. Pass NULL for any parameter you do not want to change.

cron.alter_job(
    job_id   BIGINT,
    schedule TEXT    DEFAULT NULL,
    command  TEXT    DEFAULT NULL,
    db_name  TEXT    DEFAULT NULL,
    active   BOOLEAN DEFAULT NULL
)

cron.unschedule

Deletes a scheduled task by ID or name.

cron.unschedule(job_id   BIGINT);
cron.unschedule(job_name TEXT);

Create the pg_cron extension

Connect to the database and run:

CREATE EXTENSION pg_cron;

Create a scheduled task

The following example creates a task named task1 that runs SELECT 1 every minute in the db01 database. The function returns the task ID.

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

Output:

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

View scheduled tasks

SELECT * FROM cron.job;

Output:

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

View task execution history

To view all execution records:

SELECT * FROM cron.job_run_details;

Output:

 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
     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
     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
(2 rows)

To view only failed runs:

SELECT * FROM cron.job_run_details WHERE status = 'failed';

Delete a scheduled task

SELECT cron.unschedule('task1');

Output:

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

Delete the pg_cron extension

DROP EXTENSION pg_cron;

Special characters

Schedule examples

The following examples cover common DBA tasks. All times are in GMT.

Delete expired data every Saturday at 03:30:

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

Run a job every day at 10:00:

SELECT cron.schedule('0 10 * * *', 'VACUUM;');

Run a job every minute:

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

Run a job at minute 23 of every hour:

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

Run a job on the fourth day of each month:

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