This topic describes how to use the pg_cron plug-in to configure scheduled tasks in AnalyticDB for PostgreSQL.

Features

pg_cron is a cron-based job scheduling plug-in that uses the same syntax as standard cron expressions. The pg_cron plug-in allows you to execute PostgreSQL statements directly on AnalyticDB for PostgreSQL databases.

Each scheduled task consists of the following parts:

  • Task: the specific task to execute, such as VACUUM.
  • Schedule: the schedule based on which to execute a task. For example, you can schedule a task to execute once every minute.

    The schedule follows the same syntax as standard cron expressions. You can use the following operators in the syntax:

     ┌───────────── 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.)
     │ │ │ │ │
     │ │ │ │ │
     │ │ │ │ │
      *  *  *  *  *
    • An asterisk (*) specifies to execute the task at any time.
    • A number specifies the time period during which the task can be executed.
    • Commas (,) can be used to separate multiple specified points in time.
    • A hyphen (-) can be used to specify a time range.
    • A forward slash (/) can be used to specify a step value.
    Note
    • pg_cron uses Greenwich Mean Time (GMT) or Coordinated Universal Time (UTC). You must convert the local system time to GMT.
    • For more information about how to create and preview schedules, visit Crontab.guru.

    Examples:

    • 03:30:00 GMT every Saturday:
      30 3 * * 6
    • 01:45:00 GMT on the 1st and 30th days of every month:
      45 1 1,30 * *
    • 03:00:00 GMT from Monday to Friday every week:
      00 3 * * 1-5
    • Every two hours from 08:00:00 GMT to 20:00:00 GMT every day:
      0 8-20/2 * * *

Precautions

The latest version of pg_cron is 1.4. To use the latest version, you must update your AnalyticDB for PostgreSQL instance to V6.3.6.0 or later.

Note

Create or delete the pg_cron plug-in

  • Check whether the pg_cron plug-in is installed.

    By default, the pg_cron plug-in of V1.4 is installed in AnalyticDB for PostgreSQL instances of V6.3.6.0 or later. You can connect to the postgres database of your instance and execute the following statement to view the pg_extension table and check whether the pg_cron plug-in is installed:

    SELECT * FROM pg_extension;

    If pg_cron is displayed in the extname column, the pg_cron plug-in is installed. Otherwise, the pg_cron plug-in is not installed.

  • Create the pg_cron plug-in.

    To create the pg_cron plug-in, perform the following steps:

    1. Submit a ticket to check whether pg_cron is added to the shared_preload_libraries parameter and configure the cron.database_name parameter.
    2. Restart your instance for the configurations to take effect.
      Note To mitigate the impact of an instance restart on your business, we recommend that you manually restart your instance during off-peak hours. For more information about how to restart an instance, see Restart an AnalyticDB for PostgreSQL instance.
    3. Use an account that has administrator permissions to connect to the postgres database and execute the following statements to install and update the pg_cron plug-in:
      CREATE EXTENSION pg_cron WITH SCHEMA pg_catalog VERSION '1.0';
      ALTER EXTENSION pg_cron UPDATE;
      Note The pg_cron plug-in created by using this method is in version 1.2 and may not provide specific features described in this topic. To use all features described in this topic, we recommend that you update your instance to V6.3.6.0 or later.
  • Delete the pg_cron plug-in.

    Use an account that has administrator permissions to connect to the postgres database and execute the following statement to delete the pg_cron plug-in:

    DROP EXTENSION pg_cron;

Create a scheduled task

  • Create a scheduled task.

    Syntax:

    SELECT cron.schedule('<Schedule>', '<Task>');

    Examples:

    • Delete expired data at 03:30:00 GMT every Saturday.
      SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    • Invoke the TEST() function at 10:00:00 GMT every day.
      SELECT cron.schedule('0 10 * * *', 'select test()');
    • Execute a specified SQL statement every minute.
      SELECT cron.schedule('* * * * *', 'select 1');
    • Clear disks at 02:30:00 GMT every Saturday and Sunday and on the 1st and 30th days of every month.
      SELECT cron.schedule('30 2 1,30 * 6,0', 'VACUUM FULL');
  • Specify the name of a scheduled task.

    The pg_cron plug-in allows you to specify the name of a scheduled task. Syntax:

    SELECT cron.schedule('<Scheduled task name>', '<Schedule>', '<Task>');

    Examples:

    • Delete expired data at 10:00:00 GMT every Saturday.
      SELECT cron.schedule('Delete Expired Data','30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    • Execute a specified SQL statement every minute.
      SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1');
    • Execute VACUUM on the postgres database at 23:00:00 GMT every day.
      SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL');
  • Specify a database to execute a scheduled task.

    In earlier versions of the pg_cron plug-in, scheduled tasks can be executed only on the database where the plug-in is installed. To execute scheduled tasks on other databases, you must perform operations in the cron.job table. The latest version allows you to specify a database and an account to execute a scheduled task. Syntax:

    SELECT cron.schedule('<Scheduled task name>', '<Schedule>', '<Task>', '<Database>', '<Account>', '<Enable task>');
    • '<Database>': If you leave this field empty, the scheduled task is executed on the postgres database.
    • '<Account>: If you leave this field empty, the current account is used to execute the scheduled task.
    • '<Enable task>': If you set this field to true, the scheduled task is enabled.

    Examples:

    • Execute VACUUM on the dw database at 23:00:00 GMT every day.
      SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL', 'dw');
    • Execute the specified SQL statement on the dw database by using the gp1234 account every minute.
      SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1', 'dw', 'gp1234');
    • Invoke the TEST() function on the dw database by using the user1 account at 10:00:00 GMT every day.
      SELECT cron.schedule('DO Function', '0 10 * * *', 'select test()', 'dw', 'user1', true);

Modify a scheduled task

The pg_cron plug-in provides the CRON.ALTER_JOB() function to modify scheduled tasks. Syntax:

SELECT cron.alter_job(<Scheduled task ID>, '<Schedule>', '<Task>', '<Database>', '<Account>', '<Enable task>');
  • <Scheduled task ID>: required.
  • Other fields: optional. If you leave these fields empty, the scheduled task is not modified.

Examples:

  • Change the schedule of the scheduled task whose ID is 3 to 11:00:00 GMT every day.
    SELECT cron.alter_job(3, '0 11 * * *');
  • Change the scheduled task whose ID is 1 to VACUUM.
    SELECT cron.alter_job(1, null , 'VACUUM');
  • Change the account that is used to execute the scheduled task whose ID is 2 to gp1234.
    SELECT cron.alter_job(2, null , null, null, 'gp1234');

View execution details of scheduled tasks

The pg_cron plug-in provides the cron.job_run_details table to record execution details of scheduled tasks. You can view task execution details from this table. Examples:

  • View execution details of failed scheduled tasks.
    SELECT * FROM cron.job_run_details WHERE status = 'failed';
  • View execution details of the scheduled task whose ID is 1.
    SELECT * FROM cron.job_run_details WHERE jobid = '1';
Note If large numbers of scheduled tasks are configured, the cron.job_run_details table may contain large amounts of data. We recommend that you configure a scheduled task to delete this table. If you do not need to record execution details of scheduled tasks, you can Submit a ticket to modify the cron.log_run parameter to disable recording.

View the list of scheduled tasks

Syntax:

SELECT * FROM cron.job;

Delete a scheduled task

  • Delete a scheduled task by using the task name. Syntax:
    SELECT cron.unschedule('<Scheduled task name>');
    Note The pg_cron plug-in allows you to create scheduled tasks with the same name. When the name of the scheduled task that you want to delete has duplicates, only the task with the smaller task ID is deleted.

    Example:

    Delete the scheduled task whose name is Do Vacuum.

    SELECT cron.unschedule('Do Vacuum');
  • Delete a scheduled task by using the task ID. Syntax:
    SELECT cron.unschedule(<Task ID>);
    Note The ID of a scheduled task is automatically generated when you create the scheduled task. You can view the ID of the scheduled task from the jobid field in the cron.job table.

    Example:

    Delete the scheduled task whose ID is 21.

    SELECT cron.unschedule(21);