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 āĻāϰāĻž āϝāĻžā§āĨ¤