The PG_CRON plug-in is a simple CRON-based job scheduler for PostgreSQL 9.5 and later, and runs as an extension in databases. The PG_CRON plug-in uses the same syntax as regular CRON expressions. However, it allows you to schedule PostgreSQL commands from databases.


  • The PG_CRON plug-in of an earlier version may risk memory leak. Therefore, before you use the PG_CRON plug-in, upgrade the kernel to V1.1.19 or later. If you avoid the PG_CRON plug-in, this risk is not incurred.
  • For security considerations, you can execute scheduled tasks only by using system functions. The system allows you to add and delete scheduled tasks. Common users can only view scheduled tasks in the cron.job table. Select a method of using the functions.
  • Scheduled tasks are stored in the default PostgreSQL database, but you can query the scheduled tasks in other databases.
  • Scheduled tasks are run based on GMT.
  • PG_CRON is enabled by default. If it cannot run normally, Submit a ticket to activate it.

Enable or disable the PG_CRON plug-in

You can enable or disable the PG_CRON plug-in by running one of the following scripts in your database:

-- Enable PG_CRON

-- Disable PG_CRON

Use the PG_CRON plug-in

Each scheduled task consists of a schedule and a task. The schedule specifies the time when the task is executed. For example, the task is executed once every minute. The task specifies the operation, such as select * from some_table. The PG_CRON plug-in provides the database parameter. This parameter is optional. If you do not specify this parameter, the system automatically uses the PostgreSQL database.

You can execute only the following functions. The execution of other functions is invalid.

-- Execute a task.
SELECT cron.schedule('schedule', 'task')

-- Select a database to execute the task. 
-- If you do not specify the third parameter, the database in the configuration file is used. By default, the PostgreSQL database is used.
SELECT cron.schedule('schedule', 'task', 'my_db')

-- Delete a task.
SELECT cron.unschedule(schedule_id)

-- View the task list.
SELECT * FROM cron.job;


  • Add scheduled tasks.
    -- Delete stale data at 3:30 in the morning every Saturday. The time is in Greenwich Mean Time (GMT). 
    SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    -- Clear disks at 10:00 in the morning every day. The time is in GMT.
    SELECT cron.schedule('0 10 * * *', 'VACUUM');
    -- Execute the specified script once every minute.
    SELECT cron.schedule('* * * * *', 'select 1;');
    -- Execute the specified script at the twenty-third minute of every hour.
    SELECT cron.schedule('23 * * * *', 'select 1;');
    -- Execute the specified script on the fourth day of every month.
    SELECT cron.schedule('* * 4 * *', 'select 1;');

    The schedules use standard CRON expressions. In the expressions, wildcards * indicate that the tasks are periodically executed and the numbers indicate the specific points in time when the tasks are executed.

     ┌───────────── Minute (0 to 59)
     │ ┌────────────── Hour (0 to 23)
     │ │ ┌─────────────── Date (1 to 31)
     │ │ │ ┌──────────────── Month (1 to 12)
     │ │ │ │ ┌───────────────── The day of the week (0 to 6) (The value 0 indicates Sunday. 
     │ │ │ │ │                   The value 7 still indicates a weekend day.)
     * * * * *
  • Delete a task.
    -- Stop and delete a task.
    SELECT cron.unschedule(42);
  • View the running task.
    SELECT * FROM cron.job;
     jobid | schedule   |  command  | nodename  | nodeport | database | username | active 
        43 | 0 10 * * * |   VACUUM; | localhost |     5433 | postgres | test     | t