AnalyticDB:pg_cron

How it works

pg_cron is a cron-based job scheduler that uses the standard cron syntax and lets you run PostgreSQL commands directly from the database.

A scheduled job consists of two main parts:

• Command: The task to run, such as VACUUM.

• Schedule: The time when the task runs, for example, once every minute.

  The schedule uses standard cron syntax:

   ┌───────────── minute (0 - 59)
   │ ┌────────────── hour (0 - 23)
   │ │ ┌─────────────── day of month (1 - 31)
   │ │ │ ┌──────────────── month (1 - 12)
   │ │ │ │ ┌───────────────── day of week (0 - 6, 0 is Sunday)
   │ │ │ │ │
   │ │ │ │ │
   │ │ │ │ │
   * * * * *

  • *: Represents any value.

  • A specific number: Runs only at this time.

  • ,: Separates multiple values.

  • -: Defines a range of values.

  • /: Specifies a step value.

  To create or preview a schedule, see Crontab.guru.

  Examples:

  • Every Saturday at 3:30 AM (GMT):

    30 3 * * 6

  • At 1:45 AM (GMT) on the 1st and 30th of every month:

    45 1 1,30 * *

  • Every weekday (Monday to Friday) at 3:00 AM (GMT):

    00 3 * * 1-5

  • Every two hours on the hour, from 8:00 AM to 8:00 PM (GMT):

    0 8-20/2 * * *

Usage notes

• Scheduled jobs run in Greenwich Mean Time (GMT), which is equivalent to Coordinated Universal Time (UTC). When scheduling a job, convert your local time to GMT.

• All scheduled jobs are stored in and must be queried from the default postgres database.

• If your instance version is earlier than v6.3.6.0 (for AnalyticDB for PostgreSQL V6.0), v7.0.3.0 (for AnalyticDB for PostgreSQL V7.0), or v2.0.0.1 (for AnalyticDB for PostgreSQL Serverless mode), some features described in this topic may not be available. To use all features, update the minor version of your instance. For instructions, see Upgrade the minor engine version.

Install or uninstall the extension

Install the extension

AnalyticDB for PostgreSQL installs the pg_cron extension by default when you create an instance. You do not need to install it manually.

Uninstall the extension

The pg_cron extension has kernel dependencies and cannot be deleted.

Schedule a job

Schedule a job

Syntax:

SELECT cron.schedule('<schedule>', '<command>');

Examples:

• Delete expired data every Saturday at 3:30 AM (GMT):

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

• Run the test() function every day at 10:00 AM (GMT):

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

• Run a specified SQL statement every minute:

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

• Run VACUUM FULL at 2:30 AM (GMT) on the 1st and 30th of each month, and also every Saturday and Sunday:

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

Specify a job name

You can assign a name to a scheduled job. The syntax is as follows:

SELECT cron.schedule('<job_name>', '<schedule>', '<command>');

Examples:

• Set the scheduled job name to "Delete Expired Data":

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

• Set the scheduled job name to "Select Per Minute":

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

• Set the scheduled job name to "Do Vacuum":

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

Specify a database for the job

In pg_cron versions earlier than 1.4, jobs could only run in the database where the extension was installed. To run jobs in other databases, you had to directly manipulate the cron.job table, which was inconvenient and insecure.

pg_cron version 1.4 lets you specify a database and a database account to run a scheduled job. The syntax is as follows:

SELECT cron.schedule('<job_name>', '<schedule>', '<command>', '<database>', '<username>', '<active>');

• '<database>': The database where the job runs. If not specified, defaults to the postgres database.

• '<username>: The database account that runs the job. If not specified, defaults to the current account.

• '<active>': Specifies whether the scheduled job is enabled. The default value is true.

Examples:

• Run VACUUM on the dw database every day at 11:00 PM (GMT):

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

• Run a specified SQL statement in the dw database every minute as the user gp1234:

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

• Run the test() function in the dw database every day at 10:00 AM (GMT) as the user user1:

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

Update a scheduled job

Use the cron.alter_job function to update a scheduled job. The syntax is as follows:

SELECT cron.alter_job(<job_id>, '<schedule>', '<command>', '<database>', '<username>', '<active>');

• <job_id>: Required. This is the job's automatically generated ID, which you can find in the cron.job table.

• Other parameters: Optional. If you do not specify a value for an optional parameter, it remains unchanged.

Examples:

• Change the schedule for the job with ID 3 to run every day at 11:00 AM (GMT):

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

• Change the command for the job with ID 1 to VACUUM:

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

• Change the user for the job with ID 2 to gp1234:

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

View job execution details

The cron.job_run_details table logs the details of each job execution.

Examples:

• View details for all failed jobs:

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

• View execution details for the job with ID 1:

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

View the list of scheduled jobs

Example:

SELECT * FROM cron.job;

Unschedule a job

• Unschedule a job by its name. The syntax is as follows:

  SELECT cron.unschedule('<job_name>');

  Important

  pg_cron lets you create jobs with duplicate names. If you unschedule a job by a name that applies to multiple jobs, pg_cron deletes only the job with the smallest jobid.

  Example:

  Unschedule the job named 'Do Vacuum':

  SELECT cron.unschedule('Do Vacuum');

• Unschedule a job by its ID. The syntax is as follows:

  SELECT cron.unschedule(<job_id>);

  pg_cron automatically generates the job ID when you create the job. You can find the jobid in the cron.job table.

  Example:

  Unschedule the job with ID 21:

  SELECT cron.unschedule(21);

Related documents

• For more information about pg_cron, see the pg_cron repository on GitHub.

• If your instance is an RDS for PostgreSQL instance, see Scheduled jobs (pg_cron).