whpg-cron v7.5

whpg-cron is WarehousePG's packaged distribution of pg_cron, a cron-based job scheduler that runs inside the database. It lets you schedule SQL commands, including calls to stored procedures, directly from the database using standard cron syntax.

The WarehousePG whpg-cron package installs the extension under its upstream name. You create it, and refer to it in SQL, as pg_cron. See pg_cron for the upstream project.

Downloading, installing, and loading the extension

Refer to Downloading and installing an extension for installation and setup instructions. The package name is edb-whpg7-whpg-cron.

1. Set the database where pg_cron creates its metadata tables. By default, it uses the postgres database. Set cron.database_name before you create the extension:

   → WrapCopy

   gpconfig -c cron.database_name -v '<db_name>' --skipvalidation

2. Load the extension as a shared library. Check for existing shared libraries:

   → WrapCopy

   gpconfig -s shared_preload_libraries

3. Use the output of the previous command to enable pg_cron, along with any other shared libraries, and restart WarehousePG:

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, pg_cron'
   gpstop -raf

4. Create the extension in the database you configured:

   → WrapCopy

   CREATE EXTENSION pg_cron;

5. Optionally, grant usage to other users so they can schedule and manage their own jobs:

   → WrapCopy

   GRANT USAGE ON SCHEMA cron TO <user_name>;

Configuring whpg-cron

After you create the extension, make sure pg_cron can connect to run your jobs, and, optionally, tune other settings.

Ensuring pg_cron can start jobs

Choose a method to allow pg_cron to connect to the database to run each job:

• Unix domain socket. Limit access to local OS users, since this option doesn't expose the database over the network. Point cron.host at the socket directory and restart the database:

  → WrapCopy

  gpconfig -c cron.host -v '/tmp'
  # Or leave cron.host as an empty string ('') to use libpq's default socket directory.
  gpstop -raf

  Then add a matching local entry to pg_hba.conf on the coordinator to grant trust authentication on that socket:

  → WrapCopy

  local   all   all   trust

• TCP connection. Restrict the pg_hba.conf entry to the loopback address, since pg_cron always connects from the coordinator itself. Leave cron.host at its default (localhost), and add a host entry to pg_hba.conf on the coordinator:

  → WrapCopy

  host   all   all   127.0.0.1/32   trust

  Alternatively, add the job user's password to a .pgpass file for libpq to use instead of trust authentication.

• Background workers. Avoid opening a connection at all by having pg_cron run jobs as background workers instead. Skip cron.host and pg_hba.conf entirely:

  → WrapCopy

  gpconfig -c cron.use_background_workers -v 'on'
  gpconfig -c max_worker_processes -v '20'
  gpstop -raf

  max_worker_processes limits the number of concurrent jobs, so raise it from its default of 8 if you schedule many jobs.

Tuning pg_cron settings

pg_cron supports the following configuration parameters, which you set with gpconfig:

View the current settings:

SELECT * FROM pg_settings WHERE name LIKE 'cron.%';

Change a setting with gpconfig, then restart:

gpconfig -c cron.<parameter> -v '<value>'
gpstop -raf

Scheduling jobs

Write a cron schedule, then create, alter, remove, or view jobs using the cron schema's functions.

pg_cron runs jobs in parallel, but runs only one instance of a given job at a time. If a job is still running when its next scheduled run comes due, pg_cron queues the new run until the current one finishes.

pg_cron stores every scheduled job as a row in cron.job, with columns for its jobid, schedule, command, and username, among others. pg_cron runs each job in the database where you created it, with the same permissions as the user who scheduled it. A row-level security (RLS) policy limits you to viewing and modifying only the jobs you created, unless you're a superuser or have the bypassrls attribute, which lets a role bypass row-level security entirely.

Understanding cron syntax

Write schedules using the same cron syntax as Vixie cron, or build one at crontab.guru.

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

pg_cron also supports:

• $ to indicate the last day of the month.
• [1-59] seconds to schedule a job on an interval measured in seconds. You can't combine seconds with the other time units.

Example schedules:

'10 seconds'  # every 10 seconds
* * * * *     # every minute
*/5 * * * *   # every 5 minutes
0 * * * *     # every hour
0 0 * * *     # daily at 12 AM
0 0 * * 1-5   # 12 AM every weekday
0 1 * * 0     # 1 AM every Sunday
0 13 2 6 *    # 1 PM on June 2

Creating a cron job

Create a job, either anonymous or named, with cron.schedule(). The function accepts (schedule, command) for an anonymous job or (job_name, schedule, command) for a named one, and returns the new job's jobid.

Examples

• Create an anonymous job that deletes old data every Saturday at 3:30 AM (GMT):

  → WrapCopy

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

• Create a named job that runs VACUUM every day at 10:00 AM (GMT):

  → WrapCopy

  SELECT cron.schedule(
      'nightly-vacuum',
      '0 10 * * *',
      'VACUUM'
  );

• Run a query every 30 seconds:

  → WrapCopy

  SELECT cron.schedule(
      'run_every_30_seconds',
      '30 seconds',
      'SELECT 1'
  );

• Call a stored procedure every 5 seconds:

  → WrapCopy

  SELECT cron.schedule(
      'process-updates',
      '5 seconds',
      'CALL process_updates()'
  );

• Process payroll at noon on the last day of each month:

  → WrapCopy

  SELECT cron.schedule(
      'process-payroll',
      '0 12 $ * *',
      'CALL process_payroll()'
  );

Creating a cron job in a different database

Run a job against a database other than the one where pg_cron is installed with cron.schedule_in_database(). The function accepts job_name, schedule, command, and database, plus the optional username and active parameters, and returns the new job's jobid.

For example, delete old data every Saturday at 3:30 AM (GMT) in another database:

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

Listing jobs

View your active jobs in cron.job:

SELECT * FROM cron.job;

Removing a cron job

Remove a job by name or by ID with cron.unschedule(). The function returns true if the job was removed.

Examples

• Remove a named job:

  → WrapCopy

  SELECT cron.unschedule('nightly-vacuum');

• Remove a job by ID:

  → WrapCopy

  SELECT cron.unschedule(42);

Altering a cron job

Change a job's schedule, command, database, username, or active status without recreating it, using cron.alter_job(). Pass the job_id plus only the parameters you want to change.

Examples

• Change a job's schedule:

  → WrapCopy

  SELECT cron.alter_job(42, '0 10 * * *');

• Change a job's schedule, command, and username together:

  → WrapCopy

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

• Deactivate a job:

  → WrapCopy

  SELECT cron.alter_job(42, active := false);

Viewing job history

View the 10 most recent job runs:

SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

cron.job_run_details isn't cleaned up automatically, but any user who can schedule jobs can also delete their own entries from it. Purge entries older than 14 days on a schedule of their own:

SELECT cron.schedule(
    '0 0 * * *',
    $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '14 days'$$
);

Set cron.log_run = off if you don't want to use cron.job_run_details at all.

Editing cron.job directly

Edit cron.job directly only when the cron schema functions can't do what you need, for example a bulk update across many jobs at once. Since WarehousePG doesn't support the TRIGGER statement pg_cron normally relies on to detect such changes automatically, use the cron.reload_job() function to update the pg_cron cache. Any role granted USAGE on the cron schema can call this function.

For example, deactivate a job by updating cron.job directly, then refresh the cache:

UPDATE cron.job SET active = false WHERE jobid = 1;
SELECT cron.reload_job();

Limitations

whpg-cron for WarehousePG has the following limitations:

• You can only install pg_cron in one database per WarehousePG cluster.
• pg_cron and whpg-anonymizer can't run in the same database. Creating the pg_cron extension in a database where whpg-anonymizer is enabled fails, because whpg-anonymizer's event trigger doesn't allow another extension to replace the masked views it owns. Schedule jobs in a separate database from the one where you use whpg-anonymizer.