All Products
Search
Document Center

AnalyticDB for PostgreSQL:Use the pg_cron extension to configure scheduled jobs

Last Updated:Feb 23, 2024

AnalyticDB for PostgreSQL allows you to run batch processing jobs based on a specific schedule. For example, you can update data, back up data, or clear expired data at a specific point in time. This ensures that jobs are run as scheduled, saves time, reduces labor costs, and improves system efficiency and manageability.

Overview

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 a job. For example, you can schedule a 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 a job can be run at any time.

    • A number specifies the point in time at which you want to run a 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 a step value.

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

Install or delete the pg_cron extension

Install the pg_cron extension

  • Check whether the pg_cron extension is installed.

    You can connect to the postgres database of your AnalyticDB for PostgreSQL instance and execute the following statement to query the pg_extension table and check whether the pg_cron extension is installed:

    SELECT * FROM pg_extension;

    If pg_cron is displayed in the extname column, the pg_cron extension is installed. Otherwise, the pg_cron extension is not installed.

    Note

    By default, the pg_cron extension of V1.4 is installed on AnalyticDB for PostgreSQL V6.0 instances of V6.3.6.0 or later, AnalyticDB for PostgreSQL V7.0 instances of V7.0.3.0 or later, and AnalyticDB for PostgreSQL instances in Serverless mode of V2.0.0.1 or later. For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor engine version.

  • If the pg_cron extension is not installed, perform the following operations to install the extension:

    1. Submit a ticket to ask technical support to add pg_cron to the shared_preload_libraries parameter and configure the cron.database_name parameter.

    2. Restart your AnalyticDB for PostgreSQL instance to allow the configurations to take effect.

      Note

      To mitigate the impact of an instance restart on your business, we recommend that you manually restart your instance during off-peak hours. For information about how to restart an instance, see Restart an instance.

    3. Use a database account that has administrator permissions to connect to the postgres database and execute the following statements to install and update the pg_cron extension:

    CREATE EXTENSION pg_cron WITH SCHEMA pg_catalog VERSION '1.0';
    ALTER EXTENSION pg_cron UPDATE;

Delete the pg_cron extension

Use a database account that has administrator permissions to connect to the postgres database and execute the following statement to delete the pg_cron extension:

DROP EXTENSION pg_cron;

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 the pg_cron extension 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 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.

Note

If large numbers of scheduled jobs are configured, the cron.job_run_details table may contain large amounts of data. We recommend that you configure a scheduled job to delete the table. If you do not want to record the execution details of scheduled jobs, you can Submit a ticket to ask technical support to set the cron.log_run parameter to off.

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