pgcron doc#

Pg_cron āĻāĻ•āϟāĻŋ cron-based job scheduler āϝāĻž PostgreSQL(9.5 or higher) āĻāϰ āϭ⧇āϤāϰ āĻāĻ•āϟāĻŋ Database extension āĻšāĻŋāϏ⧇āĻŦ⧇ run āĻ•āϰ⧇āĨ¤ Pg_cron āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ specific āϏāĻŽā§Ÿ āĻĒāϰ āĻĒāϰ āύāĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āϟ sql query āϕ⧇ postgresql āĻ execute āĻ•āϰāĻž āϝāĻžā§ŸāĨ¤

for an example application āĻāϰ people suggation portion āĻĨ⧇āϕ⧇ user1 āϝāĻĻāĻŋ user13 āϕ⧇ unmatch āĻ•āϰ⧇, āϤāĻŦ⧇ user1 āϤāĻžāϰ suggestion portion āĻāĻŦāĻ‚ search people portion āĻ user13 āϕ⧇ āĻĻ⧇āĻ–āϤ⧇ āĻĒāĻžāĻŦ⧇ āύāĻž āĻāĻŦāĻ‚ user13 āĻ“ āϤāĻžāϰ suggestion portion āĻāĻŦāĻ‚ search people portion āĻ user1 āϕ⧇ āĻĻ⧇āĻ–āϤ⧇ āĻĒāĻžāĻŦ⧇ āύāĻžāĨ¤

According to business case, unmatched āĻ•āϰāĻž users āϗ⧁āϞ⧋ 40 days āĻāϰ āĻŦ⧇āĻļāĻŋ unmatched āĻĨāĻžāĻ•āĻŦ⧇ āύāĻžāĨ¤ āϤāĻžāϰāĻž āφāĻŦāĻžāϰ suggestion āĻŸā§‡āĻŦāĻŋāϞ āĻāĻŦāĻ‚ search people portion āĻ seen āĻšāĻŦ⧇āĨ¤
āϝ⧇ āϏāĻŦ user āĻāϰ unmatched date ā§Ēā§Ļ āĻĻāĻŋāύ āĻšā§Ÿā§‡āϛ⧇ āϤāĻžāĻĻ⧇āϰāϕ⧇ unmatch table āĻĨ⧇āϕ⧇ remove āĻ•āϰ⧇ āĻĻā§‡ā§ŸāĻžāϰ opperation āϟāĻŋ postgreSQL āĻāϰ under āĻ āϏāĻŽā§āĻĒā§‚āĻ°ā§āĻŖ āĻšā§ŸāĨ¤ āĻĒā§āϰāϤāĻŋāĻĻāĻŋāύ āϰāĻžāϤ ā§Š āϟāĻžāϰ āϏāĻŽā§Ÿ user_unmatches table āĻ PostgreSQL āĻāĻ•āϟāĻŋ query āĻ…āĻĒāĻžāϰ⧇āĻļāĻžāύ execute āĻ•āϰāĻŦ⧇ āĻāĻŦāĻ‚ current date-time āĻāϰ āĻĨ⧇āϕ⧇ 40 days āĻĒā§‚āĻ°ā§āĻŦ⧇ create āĻšāĻ“ā§ŸāĻž row/user information āϗ⧁āϞ⧋ remove āĻ•āϰ⧇ āĻĻāĻŋāĻŦ⧇āĨ¤

āωāĻĒāϰ⧋āĻ•ā§āϤ operation āϟāĻŋ āĻāĻ•āϟāĻŋ scheduler operation āϝāĻž postgreSQL āĻāϰ under āĻ pg_cron āĻāϰ āĻĻā§āĻŦāĻžāϰāĻž execute āĻšā§ŸāĨ¤ PostgreSQL āĻ Pg_cron configure āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ āύāĻŋāĻšā§‡āϰ instruction follow āĻ•āϰāϤ⧇ āĻšāĻŦ⧇ āĨ¤

Installing Pg_cron: Red Hat, CentOS, Fedora, and Amazon Linux āĻāϰ āϏāĻ‚āĻ˜ā§‡ PostgreSQL 12 āĻ pg_cron extension install :

sudo yum install -y pg_cron_12

install on Debian, and Ubuntu with PostgreSQL 12 using apt.postgresql.org:

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

Setting up pg_cron: Pg_cron āĻāϰ āϜāĻ¨ā§āϝ postgreSQL āĻāϰ background worker enable āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ shared_preload_libraries āϤ⧇ pg_cron āϕ⧇ āωāĻ˛ā§āϞ⧇āĻ– āĻ•āϰāϤ⧇ āĻšāĻŦ⧇ āĨ¤ server āϝāĻĻāĻŋ hot standby mode āĻ āĻĨāĻžāϕ⧇ āϤāĻŦ⧇ pg_Cron run āĻšāĻŦ⧇ āύāĻžāĨ¤ āĻ•āĻŋāĻ¨ā§āϤ⧁ āϝāĻ–āύ server start āĻšāĻŦ⧇ pg_cron automatically start āĻšāĻŦ⧇āĨ¤

add to postgresql.conf#

required to load pg_cron background worker on start-up#

shared_preload_libraries = 'pg_cron'

By default, pg_cron āĻāϰ background worker āϤāĻžāϰ metadata tables āϗ⧁āϞ⧋ “Postgres” database āĻ āϤ⧈āϰāĻŋ āĻ•āϰ⧇āĨ¤ āϤāĻžāχ āϝ⧇ database āĻāϰ table āĻ scheduler operation āϟāĻŋ execute āĻšāĻŦ⧇ āϏ⧇āχ database name āϟāĻŋ postgresql.conf file āĻāϰ cron.database_name configuration parameter āĻ āωāĻ˛ā§āϞ⧇āĻ– āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤

add to postgresql.conf#

optionally, specify the database in which the pg_cron background worker should run (defaults to postgres)#

cron.database_name = 'database_name’

Configuration part āĻļ⧇āώ āĻšā§Ÿā§‡āϛ⧇āĨ¤ āĻāĻ–āύ postgreSQL restart āĻ•āϰāĻžāϰ āĻĒāϰ ‘CREATE EXTENSION pg_cron’ āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ pg_cron function āĻāĻŦāĻ‚ metadata table āϤ⧈āϰāĻŋ āĻ•āϰāϤ⧇ āĻĒāĻžāϰāĻž āϝāĻžā§ŸāĨ¤

– run as superuser: CREATE EXTENSION pg_cron;

– optionally, grant usage to regular users: GRANT USAGE ON SCHEMA cron TO postgre_user_name;

Important: by default pg_Cron, libpq āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ āĻāĻ•āϟāĻŋ āύāϤ⧁āύ connection āϕ⧇ local database āĻ āϤ⧈āϰāĻŋ āĻ•āϰ⧇āĨ¤ āϝāĻž pg_hba.conf āĻĨ⧇āϕ⧇ allowed āĻĨāĻžāĻ•āϤ⧇ āĻšā§ŸāĨ¤ database āĻāϰ user authentication āĻ trust enable āĻĨāĻžāĻ•āϤ⧇ āĻšāĻŦ⧇ āϝ⧇āύ localhost āĻ cron job running āĻĨāĻžāϕ⧇ āĨ¤ āĻāχ āϜāĻ¨ā§āϝ postgreuser āĻāϰ password āϟāĻŋ .pgpass file āĻ āϰāĻžāĻ–āϤ⧇ āĻšāĻŦ⧇, āϝāĻž libpq āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ connection open āĻ•āϰāĻŦ⧇āĨ¤

.pgpass āĻāχ file āĻ postgresql āĻāϰ credential āύāĻŋāĻŽā§āύ āϰ⧂āĻĒ⧇ āĻĨāĻžāϕ⧇,

hostname:port:database:username:password#

localhost:5432:demo_db:postgres:postgres

pg_cron āĻāϰ background workers āĻāϰ number of concurrent job āĻāϰ limit āϕ⧇ max_worker_processes āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ setting āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤

Schedule jobs via background workers instead of localhost connections#

cron.use_background_workers = on

Increase the number of available background workers from the default of 8#

max_worker_processes = 20

āĻāĻ›āĻžā§œāĻž Unix domain socket directory āĻĨ⧇āϕ⧇ hostname āϕ⧇ trust permission āĻĻāĻŋā§Ÿā§‡ pg_cron āĻāϰ libpq āĻāϰ connection established āĻ•āϰāĻž āϝāĻžā§ŸāĨ¤

Pg_cron āϕ⧇ operational āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ āύāĻŋāĻšā§‡āϰ step āϗ⧁āϞ⧋ follow āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤

Step 1: demo_db āĻāχ database āĻ pg_cron extension āύāĻŋā§Ÿā§‡ āĻ•āĻžāϜ āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ at first postgreSQL āĻ CLI terminal āĻĨ⧇āϕ⧇ connect āĻšāϤ⧇ āĻšāĻŦ⧇āĨ¤

psql -h localhost -U postgres demo_db;

Step 2: step 1 āĻ connect āĻĨāĻžāĻ•āĻž āĻ…āĻŦāĻ¸ā§āĻĨāĻžāϤ⧇ demo_db database āĻ Pg_cron āĻāϰ plug-in create āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤

CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO postgre_user_name;

Step 1 āĻ connect āĻĨāĻžāĻ•āĻž āĻ…āĻŦāĻ¸ā§āĻĨāĻžāϤ⧇ demo_db āĻĨ⧇āϕ⧇ create āĻ•āϰāĻž extension remove āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ āύāĻŋāĻšā§‡āϰ command run āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤
DROP EXTENSION pg_cron;

Step 1 āĻ connect āĻĨāĻžāĻ•āĻž āĻ…āĻŦāĻ¸ā§āĻĨāĻžāϤ⧇ pg_cron āĻĨ⧇āϕ⧇ schedule āĻ•āϰāĻž āϏāĻ•āϞ queryāϗ⧁āϞ⧋ āύāĻŋāĻšā§‡āϰ query āĻĻā§āĻŦāĻžāϰāĻž āĻĻ⧇āĻ–āĻž āϝāĻžā§ŸāĨ¤

    select * from cron.job;

    jobid |  schedule   |   command                       | nodename  | nodeport |  database   | username | active | jobname
    -------+----------------+----------------------------------+---------------+-------------+----------------+--------------+--------+---------

Step 3: Step 1 āĻ connect āĻĨāĻžāĻ•āĻž āĻ…āĻŦāĻ¸ā§āĻĨāĻžāϤ⧇ pg_cron āĻ postgreSQL āĻāϰ query āϟāĻŋ shedule āĻ•āϰāĻžāϰ āϜāĻ¨ā§āϝ āύāĻŋāĻšā§‡āϰ query execute āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤ SELECT cron.schedule(‘0 3 * * *’, $$DELETE FROM public.auth_user_app_user_unmatch WHERE user_unmatch_created_at < now() - interval ‘40 days’$$);

Schedule query āϟāĻŋāϰ structure SELECT cron.schedule(’* * * * *’, $$$$);

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

    * * * * *

Pg_cron āĻāϰ āϕ⧋āύ schedule āϕ⧇ unschedule āĻ•āϰāϤ⧇ āĻšāϞ⧇ āύāĻŋāĻšā§‡āϰ command āϟāĻŋ Step 1 āĻ connect āĻĨāĻžāĻ•āĻž āĻ…āĻŦāĻ¸ā§āĻĨāĻžāϤ⧇ run āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤
SELECT cron.unschedule(jobid); Or SELECT cron.unschedule(jobid) FROM cron.job;

SELECT \* FROM cron.job; āĻĨ⧇āϕ⧇ Jobid āύāĻŋā§Ÿā§‡ āϏ⧇āχ jobid user āĻ•āϰ⧇ unschedule āĻ•āϰāϤ⧇ āĻšāĻŦ⧇āĨ¤

    jobid | schedule | command | nodename | nodeport | database | username | active | jobname
    -------+-------------+-----------------------+-----------+----------+-------------+----------+--------+---------
    3 | 0 3 \* \* \* | DELETE FROM public.auth_user_app_user_unmatch WHERE user_unmatch_created_at < now() - interval '2 MINUTE' | localhost | 5432 | demo_db | postgres | t |
    (1 rows)

āωāĻĻāĻžāĻšāϰ āϏāϰ⧂āĻĒ SELECT cron.unschedule(3); āĻŦā§āϝāĻŦāĻšāĻžāϰ āĻ•āϰ⧇ schedule āϟāĻŋ unschedule āĻ•āϰāĻž āϝāĻžā§ŸāĨ¤