postgres pg_cron scheduler - ghdrako/doc_snipets GitHub Wiki

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 IF NOT EXIST 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

It isn’t possible for the job owner to alter a job they owned. To do that, use a workaround of altering the job id in a separate session while connected as the postgres superuser.

Schedule a job

SELECT cron.schedule_in_database(
  job_name:='Manual rideshare.trips Vacuum',
  schedule:='0 10 * * *',
  command:='VACUUM (ANALYZE) rideshare.trips',
  database:='rideshare_development',
  username:='postgres'
);

View scheduled jobs

SELECT * FROM cron.job ORDER BY jobid;

-- See jobs that have ran
SELECT * FROM cron.job_run_details;