PolarDB:pg_cron

Scheduled task storage

All jobs are stored in the cron.job table. When the database starts, pg_cron builds a JOB LIST and TASK LIST from this table. The cron.job_cache_invalidate trigger keeps both lists in sync whenever you create, modify, or delete a job.

Task execution states

Each task moves through the following states during execution:

cron.schedule — Create a job

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

cron.schedule_in_database — Create a job in a specific database

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

cron.alter_job — Modify a job

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 — Delete a job

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

Step 1: Install the extension

Connect to your database and run:

CREATE EXTENSION pg_cron;

Step 2: Create a job

The following example creates a job named task1 that runs SELECT 1 every minute against the db01 database. The returned value is the job ID.

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

Expected output:

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

Step 3: View scheduled jobs

SELECT * FROM cron.job;

Expected output:

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

Step 4: Check job execution history

SELECT * FROM cron.job_run_details;

Expected 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 filter for failed jobs only:

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

The return_message column contains the error details for each failed run.

Step 5: Delete a job

SELECT cron.unschedule('task1');

Expected output:

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

Remove the extension

DROP EXTENSION pg_cron;

Delete expired data every week

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

Runs at 03:30 GMT every Saturday.

Run VACUUM daily

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

Runs at 10:00 GMT every day.

Purge job history to prevent table bloat

The cron.job_run_details table accumulates a row for every job execution and can grow large over time. Schedule a regular cleanup job to keep it manageable:

SELECT cron.schedule(
    'purge-job-history',
    '0 0 * * *',
    $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$
);

Run a job every minute

SELECT cron.schedule('every-minute', '* * * * *', 'SELECT 1;');

Run a job at a specific minute each hour

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

Run a job on a specific day of the month

SELECT cron.schedule('monthly-on-4th', '* * 4 * *', 'SELECT 1;');