This topic describes how to use the pg_cron plug-in provided by RDS PostgreSQL to configure a scheduled task.

Prerequisites

Your RDS instance runs PostgreSQL 11.
Note The pg_cron plug-in is only available to new RDS instances. If you want to use it in an existing RDS instance, you must submit a ticket.

Background information

pg_cron is a CRON-based job scheduling plug-in. It uses the same syntax as standard CRON expressions, but can initiate PostgreSQL commands from databases.

Each scheduled task consists of the following two parts:

  • Schedule

    The schedule to run the pg_cron plug-in. For example, the schedule specifies to run the pg_cron plug-in once every minute.

  • Task

    The jobs to execute. Example: select * from some_table.

Syntax

The pg_cron plug-in follows the syntax used by standard CRON expressions. In this syntax, the wildcard * specifies to run the pg_cron plug-in at any time and a specific number specifies to only run the pg_cron plug-in during the period specified by this number.

 ┌───────────── Minute (0 to 59)
 │ ┌────────────── Hour (0 to 23)
 │ │ ┌─────────────── Date (1 to 31)
 │ │ │ ┌──────────────── Month (1 to 12)
 │ │ │ │ ┌───────────────── Day within a week (0 to 6) (The value 0 indicates Sunday.)
 │ │ │ │ │                  
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

Examples

  • Create the pg_cron plug-in.
    CREATE EXTENSION pg_cron;
  • Add jobs to the scheduled task.
    -- Delete expired data at 3:30 am (GMT) every Saturday. 
    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    
    ----------
    
    -- Clear disks at 10:00 am (GMT) every day.
    SELECT cron.schedule('0 10 * * *', 'VACUUM');
    
    ----------
    
    -- Execute the specified script once every minute.
    SELECT cron.schedule('* * * * *', 'select 1;');
    
    ----------
    
    -- Execute the specified script at the 23th minute of every hour.
    SELECT cron.schedule('23 * * * *', 'select 1;');
    
    ----------
    
    -- Execute the specified script on the 4th day of every month.
    SELECT cron.schedule('* * 4 * *', 'select 1;');
  • View the current scheduled task.
    SELECT * FROM cron.job;
    
     jobid | schedule   |  command  | nodename  | nodeport | database | username | active 
    -------+------------+-----------+-----------+----------+----------+----------+--------
        43 | 0 10 * * * |   VACUUM; | localhost |     5433 | postgres | test     | t
  • Delete a job from the scheduled task.
    SELECT cron.unschedule(43);
    Note The number 43 indicates the ID of the job you want to delete.