pg_cron

---

The pg_cron module is a cron-based job scheduler that runs inside the database.

Installing and Registering the Module

The pg_cron module is installed when you install WarehousePG. Before you can use it, you must perform the following steps:

You can only install the pg_cron module in one database per WarehousePG cluster. First, set the database where you want pg_cron to create its metadata tables, by default it uses the postgres database. In order to change it, run the following command and restart WarehousePG:

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

Enable the extension as a preloaded library. First, check if there are any preloaded shared libraries by running the gpconfig command. The following example shows that the auto_explain libraries are already enabled for the cluster:

gpconfig -s shared_preload_libraries
Values on all segments are consistent
GUC              : shared_preload_libraries
Coordinator value: auto_explain
Segment     value: auto_explain

Use the output of the above command to enable the pg_cron module, along any other shared libraries, and restart WarehousePG:

gpconfig -c shared_preload_libraries -v 'auto_explain,pg_cron'
gpstop -ar

Register the pg_cron extension in the desired database. You can only install pg_cron in one database per cluster.

CREATE EXTENSION pg_cron;

Optionally, grant usage to specific users:

GRANT USAGE ON SCHEMA cron TO user1;

Refer to Installing Extensions for more information.

Upgrading the Module

The pg_cron module is installed when you install or upgrade WarehousePG. A previous version of the extension will continue to work in existing databases after you upgrade WarehousePG. To upgrade to the most recent version of the extension, you must:

ALTER EXTENSION pg_cron UPDATE TO '1.6.2';

Using the pg_cron Module

Use the user-defined functions (UDFs) under the cron schema to schedule cron jobs in your default database. For example, configure a job that deletes old database data every Saturday at 3:30am (GMT):

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

Optionally, you may update the database column for the job that you just created so that it runs in another database within your WarehousePG cluster. Run the following command as a user with the superuser role:

UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
SELECT cron.reload_job();

You may use the UDF cron.schedule_in_database() to schedule jobs in multiple databases. Below are the function arguments:

cron.schedule_in_database(job_name text,
schedule text,
command text,
database text,
username text default null,
active boolean default 'true')

For example, run VACUUM every Sunday at 4:00am (GMT) in a database other than the one pg_cron is installed on:

SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');
 schedule
----------
       44

Important Since the TRIGGER statement is not supported in WarehousePG, if a user runs an UPDATE or INSERT statement, or manually deletes any of the cron jobs instead of using the UDFs in the cron schema, you must run the following UDF to update the pg_cron cache:

SELECT cron.reload_job();

Note The server configuration parameter cron.use_background_workers is not supported on WarehousePG 6.

Module Documentation

Refer to the pg_cron github documentation for more information about using the module.