All Products
Search

This Product

    AnalyticDB:pg_cron

    Document Center

    AnalyticDB:pg_cron

    Last Updated:Mar 28, 2026

    pg_cron is a cron-based job scheduler built into AnalyticDB for PostgreSQL. It runs PostgreSQL commands on a schedule directly from within the database — no external scheduler needed.

    Use pg_cron to automate recurring database tasks:

    • Delete or archive expired data on a schedule

    • Run VACUUM or VACUUM FULL during off-peak hours

    • Call stored functions periodically

    • Execute maintenance SQL statements automatically

    Usage notes

    Important

    All scheduled jobs run in GMT (Greenwich Mean Time), equivalent to UTC. Convert your local time to GMT before setting a schedule.

    Important

    All jobs are stored in and must be queried from the default postgres database, regardless of which database the job targets.

    Important

    Some features described in this topic require a minimum instance version:

    • AnalyticDB for PostgreSQL V6.0: v6.3.6.0 or later

    • AnalyticDB for PostgreSQL V7.0: v7.0.3.0 or later

    • AnalyticDB for PostgreSQL Serverless mode: v2.0.0.1 or later To use all features, upgrade the minor engine version. For instructions, see Upgrade the minor engine version.

    How it works

    A scheduled job has two parts:

    • Command: The SQL statement or function to run, such as VACUUM.

    • Schedule: When to run the command, expressed in standard cron syntax.

    The schedule uses five space-separated fields:

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

    Field operators:

    OperatorMeaningExample
    *Any value* * * * * — every minute
    NumberExact match30 3 * * * — 3:30 AM daily
    ,Multiple values1,15 * * * * — minute 1 and 15
    -Range1-5 — Monday through Friday
    /Step value0/2 — every 2 hours starting at 0

    Common schedule examples:

    ScheduleExpression
    Every Saturday at 3:30 AM GMT30 3 * * 6
    1:45 AM GMT on the 1st and 30th of each month45 1 1,30 * *
    Every weekday (Monday–Friday) at 3:00 AM GMT00 3 * * 1-5
    Every two hours from 8:00 AM to 8:00 PM GMT0 8-20/2 * * *

    Use Crontab.guru to build and preview cron expressions interactively.

    Install or uninstall the extension

    pg_cron is installed by default on all AnalyticDB for PostgreSQL instances. No manual installation is required.

    The extension has kernel dependencies and cannot be uninstalled.

    Schedule a job

    Basic schedule

    SELECT cron.schedule('<schedule>', '<command>');
    ParameterRequiredDescription
    <schedule>YesCron expression specifying when the job runs
    <command>YesSQL statement or function call to execute

    Examples:

    -- Delete events older than 1 week every Saturday at 3:30 AM GMT
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

-- Call the test() function daily at 10:00 AM GMT
SELECT cron.schedule('0 10 * * *', 'select test()');

-- Run a SQL statement every minute
SELECT cron.schedule('* * * * *', 'select 1');

-- Run VACUUM FULL on the 1st and 30th of each month, and every Saturday and Sunday, at 2:30 AM GMT
SELECT cron.schedule('30 2 1,30 * 6,0', 'VACUUM FULL');

    Schedule a job with a name

    Assign a name to make it easier to identify and manage jobs later.

    SELECT cron.schedule('<job_name>', '<schedule>', '<command>');
    ParameterRequiredDescription
    <job_name>YesA label for the job
    <schedule>YesCron expression
    <command>YesSQL statement or function call

    Examples:

    SELECT cron.schedule('Delete Expired Data', '30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1');
SELECT cron.schedule('Do Vacuum', '0 23 * * *', 'VACUUM FULL');
    Important

    pg_cron allows duplicate job names. If you call cron.unschedule('<job_name>') and multiple jobs share that name, only the job with the smallest job ID is removed. Use job IDs to avoid ambiguity.

    Schedule a job on a specific database

    In pg_cron versions earlier than 1.4, jobs could only run in the database where the extension was installed. Running jobs in other databases required directly manipulating the cron.job table, which was inconvenient and insecure. Starting from pg_cron version 1.4, you can specify a target database and account directly in cron.schedule().

    SELECT cron.schedule('<job_name>', '<schedule>', '<command>', '<database>', '<username>', '<active>');
    ParameterRequiredDefaultDescription
    <job_name>Yes—A label for the job
    <schedule>Yes—Cron expression
    <command>Yes—SQL statement or function call
    <database>NopostgresThe database where the job runs
    <username>NoCurrent accountThe database account that runs the job
    <active>NotrueWhether the job is enabled

    Examples:

    -- Run VACUUM FULL on the dw database daily at 11:00 PM GMT
SELECT cron.schedule('Do Vacuum', '0 23 * * *', 'VACUUM FULL', 'dw');

-- Run a SQL statement in the dw database every minute as gp1234
SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1', 'dw', 'gp1234');

-- Call test() in the dw database daily at 10:00 AM GMT as user1
SELECT cron.schedule('DO Function', '0 10 * * *', 'select test()', 'dw', 'user1', true);

    Update a scheduled job

    Use cron.alter_job to modify an existing job.

    SELECT cron.alter_job(<job_id>, '<schedule>', '<command>', '<database>', '<username>', '<active>');
    ParameterRequiredDescription
    <job_id>YesThe job ID from the cron.job table
    <schedule>NoNew cron expression. Pass null to leave unchanged.
    <command>NoNew SQL statement. Pass null to leave unchanged.
    <database>NoNew target database. Pass null to leave unchanged.
    <username>NoNew database account. Pass null to leave unchanged.
    <active>Notrue to enable, false to disable. Pass null to leave unchanged.

    Examples:

    -- Change the schedule for job 3 to daily at 11:00 AM GMT
SELECT cron.alter_job(3, '0 11 * * *');

-- Change the command for job 1 to VACUUM
SELECT cron.alter_job(1, null, 'VACUUM');

-- Change the database account for job 2 to gp1234
SELECT cron.alter_job(2, null, null, null, 'gp1234');

    Remove a scheduled job

    Remove by job name

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

    If multiple jobs share the same name, only the job with the smallest job ID is removed. Use the job ID form to be precise.

    Example:

    SELECT cron.unschedule('Do Vacuum');

    Remove by job ID

    SELECT cron.unschedule(<job_id>);

    Example:

    SELECT cron.unschedule(21);

    Find the job ID in the cron.job table.

    View scheduled jobs

    List all scheduled jobs:

    SELECT * FROM cron.job;

    View job execution history

    The cron.job_run_detailsSubmit a ticket table logs the details of each job execution.

    -- View all failed jobs
SELECT * FROM cron.job_run_details WHERE status = 'failed';

-- View execution history for job 1
SELECT * FROM cron.job_run_details WHERE jobid = '1';
    Note

    With many scheduled jobs, cron.job_run_details can grow large over time. Schedule a periodic cleanup job to keep the table size manageable. To disable execution logging entirely, submit a Submit a ticket to request that technical support set cron.log_run to false.

    What's next