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

Considerations

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 as needed.

Scheduled tasks are stored in the default PostgreSQL database, but you can query the scheduled tasks in other databases.

By default, pg_cron is enabled. If you cannot use pg_cron, submit a ticket.

Enable or disable pg_cron

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

-- Enable pg_cron
CREATE EXTENSION pg_cron;

-- Disable pg_cron
DROP EXTENSION pg_cron;

Use pg_cron

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. pg_cron 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;

Examples

  • 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'$$);
     schedule
    ----------
           42
    
    -- Clear disks at 10:00 in the morning every day. The time is in GMT.
    SELECT cron.schedule('0 10 * * *', 'VACUUM');
     schedule
    ----------
           43
     
    -- Execute the specified script once every minute.
    SELECT cron.schedule('* * * * *', 'select 1;');
     schedule
    ----------
           44
    
    -- Execute the specified script at the twenty-third minute of every hour.
    SELECT cron.schedule('23 * * * *', 'select 1;');
     schedule
    ----------
           45
           
    -- Execute the specified script on the fourth day of every month.
    SELECT cron.schedule('* * 4 * *', 'select 1;');
     schedule
    ----------
           46

    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);
     unschedule
    ------------
              t
  • 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