ApsaraDB RDS:Use the pg_cron extension

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 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.

Create the extension

• Use the extension management feature to install the extension

  1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which your RDS instance resides. Then, find the RDS instance and click the instance ID.

  2. In the left-side navigation pane, click Plug-ins.

  3. On the page that appears, click the Extension Management tab and then the Uninstalled Extensions tab. Then, search for the pg_cron extension, select the database on which you want to install the extension, and then click Install in the Actions column.

  4. In the dialog box that appears, select a privileged account and click OK to install the extension on the required database.

     During extension installation, the status of the RDS instance changes to Maintaining Instance. If the status of the RDS instance changes to Running, the extension is installed.

• Execute the required SQL statement to install the extension

  1. Modify the required parameter to 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, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

  2. Use a privileged account to connect to the required database and execute the following statement:

     CREATE EXTENSION pg_cron;

     Note

     You can execute the SELECT * FROM pg_extension statement to view the installed extensions.

Configure a scheduled task

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

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>');

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;