AnalyticDB:pg_cron

Introduction

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

Each scheduled job consists of the following parts:

• Job content: the content of the scheduled job. Example: VACUUM.

• Schedule: the schedule based on which you want to run the job. For example, you can schedule the job to run 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 (A value of 0 specifies Sunday.)
   │ │ │ │ │
   │ │ │ │ │
   │ │ │ │ │
    *  *  *  *  *

  • An asterisk (*) specifies that the job can be run at any time.

  • A number specifies the point in time at which you want to run the job.

  • Commas (,) can be used to separate multiple points in time.

  • A hyphen (-) can be used to specify a time range.

  • A forward slash (/) can be used to specify step values.

  For information about how to create and preview a schedule, visit crontab.guru.

  Examples:

  • 03:30:00 UTC every Saturday:

    30 3 * * 6

  • 01:45:00 UTC on the first and thirtieth days of every month:

    45 1 1,30 * *

  • 03:00:00 UTC from Monday to Friday every week:

    00 3 * * 1-5

  • Every 2 hours from 08:00:00 UTC to 20:00:00 UTC every day:

    0 8-20/2 * * *

Usage notes

• Scheduled jobs use UTC. You must convert your local system time into UTC.

• By default, scheduled jobs are stored in the postgres database. You can query scheduled jobs only from the postgres database.

• If the minor version of your instance is earlier than V6.3.6.0 (AnalyticDB for PostgreSQL V6.0), V7.0.3.0 (AnalyticDB for PostgreSQL V7.0), or V2.0.0.1 (AnalyticDB for PostgreSQL in Serverless mode), specific features that are described in this topic may be unavailable. If you want to use all features that are described in this topic, we recommend that you update the minor version of your instance. For information about how to update the minor version of an AnalyticDB for PostgreSQL instance, see Update the minor version of an instance.

Installation and uninstallation

Installation

The pg_cron extension is automatically installed after you create an AnalyticDB for PostgreSQL instance. You do not need to manually install the extension.

Uninstallation

The pg_cron extension provides engine feature dependencies and cannot be uninstalled.

Create a scheduled job

Create a scheduled job

Syntax:

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

Examples:

• Delete expired data at 03:30:00 UTC every Saturday.

  SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

• Call the TEST() function at 10:00:00 UTC every day.

  SELECT cron.schedule('0 10 * * *', 'select test()');

• Execute an SQL statement every minute.

  SELECT cron.schedule('* * * * *', 'select 1');

• Clear disk data at 02:30:00 UTC every Saturday and Sunday and on the first and thirtieth days of every month.

  SELECT cron.schedule('30 2 1,30 * 6,0', 'VACUUM FULL');

Specify a name for a scheduled job

The pg_cron extension allows you to specify a name for a scheduled job. Syntax:

SELECT cron.schedule('<Name of the scheduled job>', '<Schedule>', '<Job content>');

Examples:

• Delete expired data at 10:00:00 UTC every Saturday.

  SELECT cron.schedule('Delete Expired Data','30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

• Execute an SQL statement every minute.

  SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1');

• Execute the VACUUM statement on the postgres database at 23:00:00 UTC every day.

  SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL');

Specify a database to run a scheduled job

For pg_cron extensions earlier than V1.4, scheduled jobs can be run only on the database where the extension is installed. To run scheduled jobs on other databases, you must perform operations on the cron.job table.

The pg_cron extension of V1.4 allows you to specify a database and a database account to run a scheduled job. Syntax:

SELECT cron.schedule('<Name of the scheduled job>', '<Schedule>', '<Job content>', '<Database>', '<Database account>', '<Enable the job>');

• '<Database>': If you leave this field empty, the scheduled job is run on the postgres database.

• '<Database account>: If you leave this field empty, the current database account is used to run the scheduled job.

• '<Enable the job>': If you set this field to true, the scheduled job is enabled.

Examples:

• Execute the VACUUM statement on the dw database at 23:00:00 UTC every day.

  SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL', 'dw');

• Execute an SQL statement on the dw database every minute by using the gp1234 account.

  SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1', 'dw', 'gp1234');

• Call the TEST() function on the dw database by using the user1 account at 10:00:00 UTC every day.

  SELECT cron.schedule('DO Function', '0 10 * * *', 'select test()', 'dw', 'user1', true);

Modify a scheduled job

The pg_cron extension provides the CRON.ALTER_JOB() function that can be used to modify scheduled jobs. Syntax:

SELECT cron.alter_job(<ID of the scheduled job>, '<Schedule>', '<Job content>', '<Database>', '<Database account>', '<Enable the job>');

• <ID of the scheduled job>: required. When you create a scheduled job, the ID of the scheduled job is automatically generated. You can query the ID of the scheduled job from the jobid field in the cron.job table.

• Other fields: optional. If you leave these fields empty, the scheduled job is not modified.

Examples:

• Change the schedule of the scheduled job whose ID is 3 to 11:00:00 UTC every day.

  SELECT cron.alter_job(3, '0 11 * * *');

• Change the content of the scheduled job whose ID is 1 to VACUUM.

  SELECT cron.alter_job(1, null , 'VACUUM');

• Change the database account that is used to run the scheduled job whose ID is 2 to gp1234.

  SELECT cron.alter_job(2, null , null, null, 'gp1234');

Query the execution details of scheduled jobs

The pg_cron extension provides the cron.job_run_details table to record the execution details of scheduled jobs. You can query job execution details from the table.

Examples:

• Query the execution details of failed scheduled jobs.

  SELECT * FROM cron.job_run_details WHERE status = 'failed';

• Query the execution details of the scheduled job whose ID is 1.

  SELECT * FROM cron.job_run_details WHERE jobid = '1';

Query a list of scheduled jobs

Example:

SELECT * FROM cron.job;

Delete a scheduled job

• Delete a scheduled job by job name. Syntax:

  SELECT cron.unschedule('<Name of the scheduled job>');

  Important

  The pg_cron extension allows you to create scheduled jobs that have the same name. When the name of the scheduled job that you want to delete has duplicates, only the job whose ID is smaller is deleted.

  Example:

  Delete the scheduled job whose name is Do Vacuum.

  SELECT cron.unschedule('Do Vacuum');

• Delete a scheduled job by job ID. Syntax:

  SELECT cron.unschedule(<ID of the scheduled job>);

  When you create a scheduled job, the ID of the scheduled job is automatically generated. You can query the ID of the scheduled job from the jobid field in the cron.job table.

  Example:

  Delete the scheduled job whose ID is 21.

  SELECT cron.unschedule(21);

References

• For more information about the pg_cron extension, visit GitHub.

• For information about how to use the pg_cron extension on an ApsaraDB RDS for PostgreSQL instance, see Use the pg_cron extension.