Scheduling maintenance jobs with pg_cron

Setting up pg_cron

1. Log in to the ibmclouddb database as admin user.

    \c ibmclouddb
   

2. Enable the pg_cron extension.

    create extension pg_cron;
   

3. Verify whether pg_cron is installed.

    \dx
   

   pg_cron can be installed on only ibmclouddb databases and can be used only by admin user due to current security reasons.

4. Run the following command to grant privileges for pg_cron.

    select public.grant_pgcron_privileges();
   

Scheduling jobs

Use cron.schedule_in_database() to schedule your jobs.

SELECT cron.schedule_in_database(
    job_name text,
    schedule text,          -- cron expression
    command text,           -- SQL command to run
    database_name text      -- target database
);

• To view schedule jobs:

select * from cron.job;

• To view the status schedule jobs:

 select * from cron.job_run_details;

• To unschedule jobs:

ibmclouddb=> SELECT cron.unschedule(jobid);
 unschedule
------------
 t
(1 row)

Example for using pg_cron

• Log into ibmclouddb database:

\c ibmclouddb

• Enable the pg_cron extension:

ibmclouddb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

ibmclouddb=> create extension pg_cron;
CREATE EXTENSION
ibmclouddb=>
ibmclouddb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 pg_cron | 1.6     | pg_catalog | Job scheduler for PostgreSQL
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

• Grant privileges for pg_cron:

ibmclouddb=> select public.grant_pgcron_privileges();
    grant_pgcron_privileges
--------------------------------
 Granted permission on pg_cron
(1 row)

• Scheduling a VACUUM job to run every Sunday at 4:00 AM.

SELECT cron.schedule_in_database(
    job_name text,
    schedule text,          -- cron expression
    command text,           -- SQL command to run
    database_name text      -- target database
);

 ibmclouddb=> SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'test');
 schedule_in_database
----------------------
                   35
(1 row)

• To view scheduled jobs:

ibmclouddb=> select * from cron.job;
 jobid | schedule  | command | nodename  | nodeport | database | username | active |    jobname
-------+-----------+---------+-----------+----------+----------+----------+--------+---------------
    35 | 0 4 * * 0 | VACUUM  | localhost |     5432 | test     | admin    | t      | weekly-vacuum
(1 row)

• To view job execution details:

ibmclouddb=> select * from cron.job_run_details;
 jobid | runid | job_pid | database | username | command |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+---------+-----------+----------------+-------------------------------+-------------------------------
    35 |    85 |   33810 | test     | admin    | VACUUM  | succeeded | VACUUM         | 2025-09-23 15:48:00.013814+00 | 2025-09-23 15:48:01.29763+00

• To unschedule jobs:

ibmclouddb=> SELECT cron.unschedule(35);
 unschedule
------------
 t
(1 row)

• The records in cron.job_run_details are not cleaned automatically, but every user that can schedule cron jobs also has permission to delete their own cron.job_run_details records.

ibmclouddb=> SELECT  cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

• pg_cron jobs are terminated whenever the database restarts as a result of activities such as scaling, failover, or switchover. Since there is no retry mechanism, the job will not resume automatically. You must either wait for the next scheduled run or adjust the schedule as needed.

• Up to 5 pg_cron jobs run concurrently; extra jobs wait in a queue.