Use the pg_cron extension of AparaDB RDS for PostgreSQL to configure scheduled tasks - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL supports the pg_cron extension. You can use the extension to configure scheduled tasks.

Prerequisites

The RDS instance runs PostgreSQL 10 or later.
The RDS instance runs a minor engine version of 20230830 or later.
Important
The extension is supported in minor engine versions that are earlier than 20230830. To standardize extension management and enhance extension security for ApsaraDB RDS for PostgreSQL, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version iterations. As a result, some extensions can no longer be created for RDS instances that run earlier minor engine versions. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.
- If you have created the extension for your RDS instance that runs a minor engine version earlier than 20230830, the extension is not affected.
- If this is the first time you create the extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.
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 Modify 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 the system 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 that the task is run at UTC 03:30 each Saturday:
```
30 3 * * 6
```
Task
The task that you configure. Example: select * from some_table.

Usage notes

Scheduled tasks are run based on UTC.
Scheduled tasks are stored in a default database named postgres. You can query scheduled tasks in other databases.
The pg_cron extension is installed in the database specified by the cron.database_name parameter. The default value of the cron.database_name parameter is postgres. If you want to install the pg_cron extension in another database, modify the value of cron.database_name parameter. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
The 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. The pg_cron extension is updated. 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. 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 extension

Important

The following method applies only to RDS instances that run the minor engine version 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 have the permissions to execute the statement.
Delete the 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 UTC 03:30 each Saturday.  
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

----------

-- Clear the disk at UTC 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 database.
```
SELECT cron.schedule('<Schedule>', '<Task>', '<Database>')
```
Note
If you do not specify the 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;
```