All Products
Search

This Product

    ApsaraDB RDS:Use the pg_cron extension

    Document Center

    ApsaraDB RDS:Use the pg_cron extension

    Last Updated:Mar 28, 2026

    pg_cron is a cron-based job scheduler for PostgreSQL. It runs SQL statements on a schedule—no external tooling required. Use it to automate routine database tasks such as cleanup, archival, and maintenance.

    Prerequisites

    Before you begin, make sure that you have:

    • An ApsaraDB RDS for PostgreSQL instance running PostgreSQL 10 or later

    • A minor engine version of 20250430 or later

    Important

    You cannot create the pg_cron extension on instances running a minor engine version earlier than 20250430. If your instance already has pg_cron installed on an older minor version, you can continue using it. To install pg_cron for the first time, or to reinstall it, update the minor engine version to the latest version. For version restrictions, see Limits on extension creation.

    How it works

    Each pg_cron job has two parts:

    • Schedule: A cron expression that specifies when the job runs.

    • Action: The SQL statement that runs on that schedule.

    For example, the following job deletes records older than one week, every Saturday at 03:30 UTC:

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

    The schedule 30 3 * * 6 follows standard cron syntax:

     ┌─────────────────── Minute (0–59)
 │  ┌──────────────── Hour (0–23)
 │  │  ┌─────────────── Day of month (1–31)
 │  │  │  ┌──────────── Month (1–12)
 │  │  │  │  ┌───────── Day of week (0–6, where 0 = Sunday)
 │  │  │  │  │
 30  3  *  *  6

    Special characters:

    CharacterMeaningExample
    *Every value in the field* * * * * — runs every minute
    ,List of values1,3,5 in day-of-week — runs on Monday, Wednesday, and Friday
    -Range of values10-12 in hours — runs at hours 10, 11, and 12
    /Step values*/15 in minutes — runs every 15 minutes

    Use crontab.guru to build and verify cron expressions.

    Note

    pg_cron runs all jobs in UTC by default. Convert times based on the TIMEZONE parameter of your database if needed.

    All jobs are stored in the cron.job table of the database specified by cron.database_name (default: postgres). You can query this table from other databases.

    Note

    pg_cron commands can only run in the database specified by cron.database_name. To install pg_cron in a different database, update cron.database_name first. See Set instance parameters.

    Install pg_cron

    Choose one of the following installation methods.

    Method 1: Use the console (recommended)

    1. Log in to the ApsaraDB RDS console. In the top navigation bar, select the region where your instance is located, then click the instance ID.

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

    3. On the Extension Management tab, click Uninstalled Extensions. Search for pg_cron, select the target database, and click Install in the Actions column.

    4. In the dialog box, select a privileged account and click OK.

    The instance status changes to Maintaining Instance during installation. When the status returns to Running, the extension is installed.

    Method 2: Run a SQL statement

    1. In the ApsaraDB RDS console, add pg_cron to the Running Value of the shared_preload_libraries parameter. For example, set the value to 'pg_stat_statements,auto_explain,pg_cron'. See Set instance parameters for instructions.

    2. Connect to the target database using a privileged account and run:

      CREATE EXTENSION pg_cron;

    To verify the installation, run:

    SELECT * FROM pg_extension;

    Schedule jobs

    Create a job

    Syntax:

    -- With a job name
cron.schedule(job_name, schedule, command)

-- Without a job name (returns a numeric job ID)
cron.schedule(schedule, command)

    Parameters:

    ParameterDescription
    job_nameName of the job. Optional. When omitted, pg_cron returns a numeric job ID you can use to manage the job later.
    scheduleCron expression specifying when the job runs.
    commandSQL statement to run.

    Examples:

    -- Delete records older than 1 week, every Saturday at 03:30 UTC
SELECT cron.schedule('weekly-cleanup', '30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - INTERVAL '1 week'$$);

-- Run VACUUM every day at 10:00 UTC
SELECT cron.schedule('0 10 * * *', 'VACUUM');

    Create a job in a specific database

    Syntax:

    cron.schedule_in_database(job_name, schedule, command, database)

    Parameters:

    ParameterDescription
    job_nameName of the job. Required — the job fails to be created if omitted.
    scheduleCron expression specifying when the job runs.
    commandSQL statement to run.
    databaseTarget database in which the job runs.

    Example:

    SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');
    Note

    If your instance runs a minor engine version earlier than 20230530, use the following syntax instead. If you omit the database, the job runs in the cron.database_name database (default: postgres):

    SELECT cron.schedule('<schedule>', '<sql-statement>', '<database>');

    View scheduled jobs

    SELECT * FROM cron.job;

    Delete a job

    Syntax:

    -- By job ID
cron.unschedule(job_id)

-- By job name
cron.unschedule(job_name)

    Parameters:

    ParameterDescription
    job_idNumeric ID returned when the job was created.
    job_nameName assigned to the job at creation.

    Examples:

    -- Delete by job ID
SELECT cron.unschedule(43);

-- Delete by job name
SELECT cron.unschedule('test01');

    Uninstall pg_cron

    To uninstall pg_cron via SQL, connect using a privileged account and run:

    DROP EXTENSION pg_cron;
    Note

    Only privileged accounts can run this command. Alternatively, uninstall the extension from the Installed Extensions tab under Extension Management in the console.

    Troubleshooting

    Jobs are not running

    If a scheduled job is not triggering, run the following test job to confirm the extension is working:

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

    If this job runs successfully, the issue is with the job's schedule or SQL statement. Review the job definition and check the database logs for errors.

    Jobs lost after recreating pg_cron

    If you were using pg_cron before a minor engine version update, you must recreate the pg_cron extension to take advantage of new features. Note that all scheduled tasks configured by pg_cron are lost after the extension is recreated. Re-create them after reinstalling the extension.

    Note

    The original pg_cron extension is supported for RDS instances running PostgreSQL 10, 11, or 12 with a minor engine version earlier than 20201130. We recommend updating to the latest minor engine version to use the upgraded version of pg_cron. See Update the minor engine version.

    What's next