pg_cron

Introduction

The pg_cron extension is a cron-based job scheduler. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database. For more information, see the official pg_cron documentation.

Supported Versions

This extension is available to the latest minor versions of RDS for PostgreSQL 12 and later versions. You can run the following SQL statement to check whether your DB instance supports this extension:

SELECT * FROM pg_available_extension_versions WHERE name = 'pg_cron';

If this extension is not supported, upgrade the minor version of your DB instance or upgrade the major version using dump and restore.

To see more extensions supported by RDS for PostgreSQL, go to Supported Extensions.

Features

The standard cron syntax is used, in which the asterisk (*) means "run every time period", and a specific number means "but only at this time".

┌───────────── min (0 - 59)  
   │ ┌────────────── hour (0 - 23)  
   │ │ ┌─────────────── day of month (1 - 31)  
   │ │ │ ┌──────────────── month (1 - 12)  
   │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to  
   │ │ │ │ │                                   Saturday, or use names; 7 is also Sunday)  
   │ │ │ │ │  
   │ │ │ │ │ 

For example, the syntax for 9:30 a.m. (GMT) every Saturday is as follows:

30 9 * * 6

Precautions

1. pg_cron requires a daemon process. Therefore, before starting a database, you need to add pg_cron to the shared_preload_libraries parameter value.
2. Scheduled jobs do not run on the standby instance. However, if the standby instance is promoted to primary, scheduled jobs automatically start.
3. Scheduled jobs are executed with the permissions of the job creator.
4. Scheduled jobs are executed using the GMT time.
5. An instance can run multiple jobs concurrently, but one job can only run once at a given time.
6. If a job needs to wait for the completion of the previous scheduled job, it will enter the wait queue and will be started as soon as possible after the previous job completes.
7. Before using this extension, you need to change the value of cron.database_name to the name of the database where scheduled jobs are created. This parameter can only be set to one database name instead of multiple names.

How to Use

You can create or delete the extension.

-- Create the extension.
CREATE EXTENSION IF NOT EXISTS pg_cron; 
-- Delete the extension.
DROP EXTENSION IF EXISTS pg_cron; 

Create a job.

-- Job 1: Delete old data at 03:30 a.m. (GMT) every Saturday.
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);  
-- Job 2: Run the VACUUM command at 10:00 a.m. (GMT) every day. The job is named nightly-vacuum.
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');