Use the pg_cron extension to configure scheduled tasks - ApsaraDB RDS

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

Prerequisites

The RDS instance runs PostgreSQL 10 or later.
The minor engine version of the RDS instance is 20230330 or later.
Important The extension is supported in minor engine versions that are earlier than 20230330. However, the extensions that are supported for ApsaraDB RDS for PostgreSQL instances are changed. Starting April 17, 2023, some extensions can no longer be created for RDS instances that run minor engine versions earlier than 20230330. For more information, see [Notice] Starting April 17, 2023, some extensions can no longer be created for ApsaraDB RDS for PostgreSQL instances that run earlier minor engine versions.
- If you have created the extension for your RDS instance that runs a minor engine version earlier than 20230330, 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 20230330 or later. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
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 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.) 
 │ │ │ │ │                  
 │ │ │ │ │
 │ │ │ │ │
 * * * * *
```
The following schedule specifies to run the task 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 the 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.

Procedure

Important The following method applies only to RDS instances that run the minor engine version of 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 are granted the permissions to execute the preceding statement.
- This statement can be executed only in the postgres database. If the statement is executed in other databases, the ERROR: can only create extension in database postgres error message is displayed.
Delete the extension.
```
DROP EXTENSION pg_cron;
```
Note Only privileged accounts are granted the permissions to execute the preceding 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>)

Examples

SELECT cron.unschedule(43);

View all scheduled tasks.
```
SELECT * FROM cron.job;
```