All Products
Search

This Product

    ApsaraDB RDS:Use the pg_cron extension

    Document Center

    ApsaraDB RDS:Use the pg_cron extension

    Last Updated:Nov 11, 2024

    ApsaraDB RDS for PostgreSQL supports the pg_cron extension that adopts the same syntax and task scheduling mechanism as cron. You can use this extension to configure scheduled tasks in databases by running SQL commands.

    Overview

    pg_cron is a cron-based scheduler that uses the same syntax as cron. You can run SQL commands to configure scheduled tasks. For more information, see pg_cron.

    A scheduled task consists of a schedule and an action. In this section, a scheduled task is used as an example to describe the two parts. The scheduled task is configured by running the following command: SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);.

    • Schedule

      This part specifies the schedule based on which the system uses the pg_cron extension to run the task. You can specify a schedule by following the same syntax as standard cron expressions. In the syntax, an asterisk (*) wildcard in a field specifies that the task runs at any point in time specified by the field, while a numerical value in a field specifies that the task runs only at the point in time specified by this value. In the sample scheduled task, the schedule is 30 3 * * 6, which specifies that the task runs at 03:30:00 (UTC) every Saturday.

       ┌───────────── 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.)
 │ │ │ │ │                  
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

      You can create and resolve the schedule of a pg_cron scheduled task on crontab.guru.

    • Action

      This part specifies the action that you want to perform by running the task. In the sample scheduled task, the action is $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$, which specifies that the scheduled task runs to delete expired data from a table named events.

    Prerequisites

    • Your RDS instance runs PostgreSQL 10 or later.

      Note

      This extension is not supported by ApsaraDB RDS for PostgreSQL instances that run PostgreSQL 17.

    • Your RDS instance runs a minor engine version of 20230830 or later.

      Important

      This extension is supported in some minor engine versions earlier than 20230830. However, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version updates for standardized extension management and enhanced security. Therefore, you cannot create this extension for RDS instances that run a minor engine version earlier than 20230830. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

      • If you have already created this extension for your RDS instance that runs a minor engine version earlier than 20230830, you can continue using this extension.

      • If you are creating this extension for the first time or need to recreate the extension for your RDS instance, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.

    • Before using the extension, you must add pg_cron to the Running Value of the shared_preload_libraries parameter in the ApsaraDB RDS console. For example, you can change the Running Value of this parameter to 'pg_stat_statements,auto_explain,pg_cron'. For more information on configuring parameters for an instance, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

    Usage notes

    • The time specified in the schedule of a scheduled task follows UTC. Convert the time as required.

    • Configured scheduled tasks are stored in a default database named postgres. You can query these scheduled tasks in other databases.

    • The pg_cron extension is installed in the database specified by the cron.database_name parameter, whose default value is postgres. If you want to install the pg_cron extension in another database, modify the value of the cron.database_name parameter. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

    • The original pg_cron extension is supported for an RDS instance that runs PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12 and uses a minor engine version earlier than 20201130. However, we recommend that you update the minor engine version of your RDS instance to the latest version to use the upgraded version of pg_cron. For more information, see Update the minor engine version. If you were using pg_cron before the minor engine version update, recreate pg_cron to take advantage of its new features. Note that the scheduled tasks that you configured by using pg_cron are lost after the extension is recreated.

    • You must use a priviledged account to create or delete the pg_cron extension. For more information about how to create a priviledged account, see Create an account.

    Use the extension

    Important

    The commands supported by pg_cron can be executed only in the database specified by the cron.database_name parameter, whose default value is postgres. For more information about how to use pg_cron, see pg_cron.

    • Create the extension

      CREATE EXTENSION pg_cron;
      Note

      • Only privileged accounts have permissions to run the preceding command. For more information about how to create a privileged account, see Create an account.

      • You can execute the following statement to view the installed extensions: SELECT * FROM pg_extension;

    • Configure a scheduled task

      SELECT cron.schedule('<Scheduled Task Name>','<Schedule>', '<Action>');
      Note

      You can leave the scheduled task name unspecified. After the scheduled task is configured, the task ID is returned.

      Example:

      -- 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 the disk at 10:00 AM (GMT) every day.
SELECT cron.schedule('0 10 * * *', 'VACUUM');

    • Configure a scheduled task in a specified database

      SELECT cron.schedule_in_database('<Scheduled Task Name>', '<Schedule>', '<Action>', '<Specified Database>');
      Important

      You must specify a scheduled task name. Otherwise, the task will fail to be created.

      If your RDS for PostgreSQL instance runs a minor engine version earlier than 20230530, use the following command to run a scheduled task. If you do not specify a database, the task runs in the database specified by the cron.database_name parameter. By default, this database is postgres.

      SELECT cron.schedule('<Schedule>', '<Action>', '<Specified Database>')

      Example:

      SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');

    • View the configured scheduled tasks

      SELECT * FROM cron.job;

    • Delete a scheduled task

      SELECT cron.unschedule(<Scheduled Task ID>);

      If a name is specified for a scheduled task, you can also delete the task by specifying its name.

      SELECT cron.unschedule('<Scheduled Task Name>');

      Example:

      -- Delete a scheduled task by its ID.
SELECT cron.unschedule(43);
-- Delete a scheduled task by its name.
SELECT cron.unschedule('test01');

    • Delete the extension

      DROP EXTENSION pg_cron;
      Note

      Only privileged accounts have permission to run the preceding command.