This topic describes how to configure scheduled tasks for an ApsaraDB RDS for PostgreSQL instance by using the pg_cron plug-in.

Background information

The pg_cron plug-in schedules tasks based on CRON and uses the same syntax as standard CRON expressions. However, the pg_cron plug-in can initiate PostgreSQL commands from databases.

Each scheduled task consists of the following two parts:

  • Schedule

    The schedule based on which ApsaraDB RDS runs the pg_cron plug-in to perform the task that you configure. For example, the schedule specifies to run the task once every minute.

    The schedule follows the same syntax as standard CRON expressions. In the syntax, a wildcard (*) specifies to run the task at all times, and a specific number specifies to run the task only during the time range that is specified by this number.

     ┌───────────── 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) 3:30 in the morning every Saturday:

    30 3 * * 6
  • Task

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

Precautions

  • The pg_cron plug-in is supported only for RDS instances that run PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12.
  • Scheduled tasks are run based on GMT.
  • Scheduled tasks are stored in a default database named postgres. However, you can query scheduled tasks in other databases.
  • An updated version of the pg_cron plug-in is provided. If you started to use the pg_cron plug-in before you update the minor AliPG version to 20201130, you must re-create the pg_cron plug-in to use the new features that are provided in the updated version. However, after the pg_cron plug-in is re-created, the scheduled tasks that are created in the original version of the pg_cron plug-in are lost. For more information, see Release notes for AliPG.

Use the pg_cron plug-in

  • Create the pg_cron plug-in.
    CREATE EXTENSION pg_cron;
    Note Only privileged accounts are authorized to run the preceding command.
  • Delete the pg_cron plug-in.
    DROP EXTENSION pg_cron;
    Note Only privileged accounts are authorized to run the preceding command.
  • Run a scheduled task.
    SELECT cron.schedule('<Schedule>', '<Task>')

    Examples:

    -- Delete stale data at GMT 3:30 in the morning every Saturday.  
    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    
    ----------
    
    -- Clear disks at GMT 10:00 in the morning every day. 
    SELECT cron.schedule('0 10 * * *', 'VACUUM');
    
    ----------
    
    -- Execute a specified script once every 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(<The ID of the scheduled task>)

    Example:

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