DOCSEARCHbyAlgolia
Submit Docs

pg_cron Documentation

repository·main·Indexed 26 days ago

https://github.com/citusdata/pg_cron

A lightweight, cron-based job scheduler that runs as a PostgreSQL extension. It allows users to schedule SQL commands to run at specific intervals directly within the database using standard Vixie cron syntax and special interval features. Includes functions for scheduling (cron.schedule), altering (cron.alter_job), and removing (cron.unschedule) jobs, as well as monitoring activity via the cron.job_run_details table.

Tokens
2.9K
Snippets
6
Records
11
Agent score
37%

What's inside pg_cron

  1. Monitor job activity using cron.job_run_details

    main

    You can monitor the execution and status of scheduled jobs by querying the cron.job_run_details table. This table provides information such as jobid, status (e.g., running, succeeded, failed), command, and timing details.

    Note: Records in this table are not automatically cleaned up. Users with permission to schedule jobs can delete their own records. For high-frequency jobs, it is recommended to schedule a cleanup task using pg_cron itself.

  2. Install pg_cron

    main

    You can install pg_cron using package managers depending on your operating system, or by building from source.

    Red Hat, CentOS, Fedora, Amazon Linux (PostgreSQL 18)

    Use yum with PGDG repositories.

    Debian, Ubuntu (PostgreSQL 18)

    Use apt via apt.postgresql.org.

    Build from Source

    Ensure pg_config is in your PATH before running make.

    Windows

    Requires Visual Studio C++ support. Use the x64 Native Tools Command Prompt for VS as an administrator and use nmake to build and install.

    # Red Hat / CentOS / Fedora / Amazon Linux
sudo yum install -y pg_cron_18

# Debian / Ubuntu
sudo apt-get -y install postgresql-18-cron

# Build from source
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
export PATH=/usr/pgsql-18/bin:$PATH
make && sudo PATH=$PATH make install

  3. Configure pg_cron extension settings

    main

    The pg_cron extension can be configured using several parameters. You can view current configurations by running SELECT * FROM pg_settings WHERE name LIKE 'cron.%';.

    Settings can be modified in the postgresql.conf file or via the ALTER SYSTEM command.

    Note on applying changes:

    • cron.log_min_messages and cron.launch_active_jobs have a sighup context; they can be applied without a restart by executing SELECT pg_reload_conf();.
    • All other settings have a postmaster context and require a server restart to take effect.

  4. Configure pg_cron in postgresql.conf

    main

    To enable the pg_cron background worker, you must add it to shared_preload_libraries. You can also configure the metadata database, the timezone, and the connection method.

    Note: After modifying postgresql.conf, you must restart PostgreSQL. After restarting, run CREATE EXTENSION pg_cron; as a superuser.

    Configuration Parameters

    • shared_preload_libraries: Must include 'pg_cron'.
    • cron.database_name: The database where metadata tables reside (defaults to postgres).
    • cron.timezone: The timezone for job scheduling (defaults to GMT).
    • cron.host: The hostname for connections (e.g., a unix domain socket directory like '/tmp').
    • cron.use_background_workers: Set to on to use background workers instead of libpq connections.
    • max_worker_processes: If using background workers, increase this to allow more concurrent jobs.
    # postgresql.conf

# Required to load the background worker
shared_preload_libraries = 'pg_cron'

# Optional configurations
cron.database_name = 'postgres'
cron.timezone = 'PRC'
cron.host = '/tmp'
cron.use_background_workers = on
max_worker_processes = 20

  5. Create a cron job with cron.schedule

    main

    Use cron.schedule to create a new job. You can provide a job name to make it easier to manage later. The function returns the jobid (bigint).

    Signatures

    • cron.schedule(schedule text, command text): Creates a job without a name.
    • cron.schedule(job_name text, schedule text, command text): Creates a named job.

  6. Create a cron job in a different database

    main

    Use cron.schedule_in_database to schedule a command to run in a database other than the one where the function is called.

    -- Signature:
-- cron.schedule_in_database(job_name text, schedule text, command text, database text, username text DEFAULT NULL, active boolean DEFAULT true)

SELECT cron.schedule_in_database(
       'delete_old_data', 
       '30 3 * * 6', 
       $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$,
       'some_other_database'
);

  8. Alter an existing cron job

    main

    Use cron.alter_job to modify the schedule, command, database, username, or active status of an existing job using its job_id.

    -- Change only the schedule
SELECT cron.alter_job(42, '0 10 * * *');

-- Change command and username
SELECT cron.alter_job(
       42,
       '0 10 * * *',
       'VACUUM',
       username := 'some_other_user'
);

-- Deactivate a job
SELECT cron.alter_job(42, active := false);

  9. Reference pg_cron configuration parameters

    main

    The following configuration parameters are available for pg_cron:

    | Setting                          | Default     | Description                                                                                             |
| ---------------------------------| ----------- | --------------------------------------------------------------------------------------------------------|
| `cron.database_name`             | `postgres`  | Database in which the pg_cron background worker should run.                                              |
| `cron.enable_superuser_jobs`     | `on`        | Allow jobs to be scheduled as superusers.                                                                |
| `cron.host`                      | `localhost` | Hostname to connect to postgres.                                                                         |
| `cron.launch_active_jobs`        | `on`        | When off, disables all active jobs without requiring a server restart                                    |
| `cron.log_min_messages`          | `WARNING`   | log_min_messages for the launcher bgworker.                                                              |
| `cron.log_run`                   | `on`        | Log all run details in the `cron.job_run_details` table.                                                  |
| `cron.log_statement`             | `on`        | Log all cron statements prior to execution.                                                             |
| `cron.max_running_jobs`          | `32`        | Maximum number of jobs that can be running at the same time.                                              |
| `cron.timezone`                  | `GMT`       | Timezone in which the pg_cron background worker should run.                                              |
| `cron.use_background_workers`    | `off`       | Use background workers instead of client connections.                                                     |

  10. Cron syntax for pg_cron

    main

    pg_cron uses the standard Vixie cron syntax.

    Standard Format

    * * * * *

    1. min (0-59)
    2. hour (0-23)
    3. day of month (1-31) or $ for the last day of the month
    4. month (1-12)
    5. day of week (0-6, where 0 is Sunday, or use names; 7 is also Sunday)

    Special pg_cron Features

    • Last day of month: Use $ in the day of month field.
    • Intervals: Use [N] seconds (e.g., 30 seconds) to schedule based on an interval. Note: seconds can only be used with this specific syntax, not with other time units.
    '10 seconds'  # every 10 seconds
* * * * *     # every minute
*/5 * * * *   # every 5 minutes
0 * * * *     # every hour
0 0 * * *     # daily at 12AM
0 0 * * 1-5   # 12AM every weekday
0 1 * * 0     # 1AM every Sunday
0 13 2 6 *    # 1PM on the 2nd of June

  11. Parse cron schedule entries with parse_cron_entry()

    main

    The parse_cron_entry() function parses a cron schedule string into an entry structure. It supports standard cron syntax (minutes, hours, day of month, month, day of week) as well as special @ syntax.

    Supported Syntax

    Standard Cron

    minutes hours doms months dows cmd

    Special @ commands

    • @reboot or @restart: Runs at startup.
    • @yearly or @annually: Runs once a year.
    • @monthly: Runs once a month.
    • @weekly: Runs once a week.
    • @daily or @midnight: Runs once a day.
    • @hourly: Runs once an hour.

    Syntax Features

    • Wildcards: * matches all values in the range.
    • Ranges: num1-num2 (e.g., 1-5).
    • Steps: num1-num2/step (e.g., */15 or 1-10/2).
    • Lists: val1,val2,val3.
    • Day of Month (DOM) special: $ prefix (e.g., 5$) indicates the last day of the month.
    • Month/Day Names: Supports abbreviated names (e.g., Jan, Feb, Sun, Mon).