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

Prerequisites

  • The major and minor engine versions of the RDS instance must meet following requirements.
    Major engine version Minor engine version
    • PostgreSQL 10
    • PostgreSQL 11
    • PostgreSQL 12
    20201130 or later
    Note 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 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.
    • PostgreSQL 13
    • PostgreSQL 14
    20220130 or later
  • You must add pg_cron to the value of the shared_preload_libraries parameter of your RDS instance.

    For more information about how to add pg_cron to the value of the shared_preload_libraries parameter, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance.

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.) 
     │ │ │ │ │                  
     │ │ │ │ │
     │ │ │ │ │
     * * * * *
    Example:

    The following schedule specifies that the task is run 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 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.

Use the pg_cron extension

  • Create the pg_cron extension.
    CREATE EXTENSION pg_cron;
    Note Only privileged accounts have the permissions to execute the statement.
  • Delete the pg_cron extension.
    DROP EXTENSION pg_cron;
    Note Only privileged accounts have the permissions to execute the 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>)

    Example:

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