This topic describes how to use the pg_cron extension to configure scheduled tasks for an ApsaraDB RDS for PostgreSQL instance.

Prerequisites

Background information

The pg_cron extension allows you to schedule tasks by using CRON. The pg_cron extension uses the same syntax as standard CRON expressions but can initiate PostgreSQL commands from databases.

Each scheduled task consists of the following parts:

  • Schedule

    The schedule based on which ApsaraDB RDS uses the pg_cron extension to run the task that you configure. For example, the schedule specifies that the task is run at an interval of 1 minute.

    The schedule follows the same syntax as standard CRON expressions. In the syntax, a wildcard (*) specifies that the task is run at any point in time, and a numerical value specifies that the task is run only at the point in time that is specified by this value.

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

    The following schedule specifies to run the task at Greenwich Mean Time (GMT) 03:30 each Saturday:

    30 3 * * 6
  • Task

    The task that you configured. Example: select * from some_table.

Usage notes

  • Scheduled tasks are run based on GMT.
  • Scheduled tasks are stored in a default database named postgres. You can query scheduled tasks in other databases.
  • The pg_cron extension is supported for your ApsaraDB RDS instance that runs PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12 and uses a minor engine version earlier than 20201130. The pg_cron extension is updated in the minor engine version 20201130. Therefore, we recommend that you update the minor engine version of the RDS instance to use the features of the extension. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance. If you use the pg_cron extension before the extension is updated and you want to use the new features of the extension, you must re-create the extension. After you re-create the pg_cron extension, the scheduled tasks that are created by using the original version of the pg_cron extension are lost.

Procedure

Important The following method applies only to RDS instances that run the minor engine version of earlier than 20230530. If your RDS instance runs the minor engine version of 20230530 or later, you can check how to use the pg_cron extension in official documentation.
  • Create the extension.
    CREATE EXTENSION pg_cron;
    Note
    • Only privileged accounts are granted the permissions to execute the preceding statement.
    • This statement can be executed only in the postgres database. If the statement is executed in other databases, the ERROR: can only create extension in database postgres error message is displayed.
  • Delete the extension.
    DROP EXTENSION pg_cron;
    Note Only privileged accounts are granted the permissions to execute the preceding statement.
  • Run a scheduled task.
    SELECT cron.schedule('<Schedule>', '<Task>')

    Examples

    -- Delete outdated data at GMT 03:30 every Saturday.  
    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    
    ----------
    
    -- Clear the disk at GMT 10:00 every day. 
    SELECT cron.schedule('0 10 * * *', 'VACUUM');
    
    ----------
    
    -- Execute a specified script at an interval of 1 minute. 
    SELECT cron.schedule('* * * * *', 'select 1;');
    
    ----------
    
    -- Execute a specified script at the twenty-third minute of every hour. 
    SELECT cron.schedule('23 * * * *', 'select 1;');
    
    ----------
    
    -- Execute a specified script on the fourth day of every month. 
    SELECT cron.schedule('* * 4 * *', 'select 1;');
  • Run a scheduled task on a specified database.
    SELECT cron.schedule('<Schedule>', '<Task>', '<Database>')
    Note If you do not specify a database, the scheduled task is run on the default database named postgres that is specified in the configuration file.
  • Delete a scheduled task.
    SELECT cron.unschedule(<ID of the scheduled task>)

    Examples

    SELECT cron.unschedule(43);
  • View all scheduled tasks.
    SELECT * FROM cron.job;