postgres pg_cron - ghdrako/doc_snipets GitHub Wiki

• https://github.com/citusdata/pg_cron

Instal

sudo apt-get -y install postgresql-14-cron

The pg_cron extension requires to be pre-loaded at server startup.

# edit postgresql.conf
shared_preload_libraries = ‘pg_cron’

By default, the extension expects to be added to the postgres database and to schedule the jobs based on the GMT timezone. You can modify these defaults to use a different database and another timezone, for example, database prod1 and the timezone CST.

# edit postgresql.conf
cron.database_name = 'prod1'
cron.timezone = 'CST'

CREATE EXTENSION pg_cron;

Using

-- Execute cleaning data custom function on Sunday at 3:00 AM GMT
SELECT cron.schedule(‘0 3 * * 7’, $$SELECT f_clean_data()$$);
schedule
----------
8
-- Runs vacuum daily at 5:30 AM GMT
SELECT cron.schedule(‘daily-vacuum’, ‘30 5 * * *’, ‘VACUUM’);
schedule
----------
9
-- Stop scheduling daily-vacuum job
SELECT cron.unschedule(‘daily-vacuum’ );
unschedule
------------
t