Sample Header Ad - 728x90

pg_cron jobs are not being executed

0 votes
0 answers
1656 views
We have installed **pg_cron** extension on PostgreSQL 13 database. postgresql.conf file:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'pg_cron_db_name'
Also we enabled trust authentication for connections coming from localhost in pg_hba.conf:
#IPv4 local connections:
host   db_name    postgres,pg_cron_user    127.0.0.1/32         trust
host   all        all                      0.0.0.0/0            md5
We have run this query from postgres user to schedule a simple job for a database db_name (other than the one **pg_cron** was installed in):
-sql
SELECT cron.schedule_in_database('job1', '5,10,20,30 17 * * *', $$SELECT 1$$, 'db_name');
Everythings works fine on our staging database. \ But on our production DB the job is never executed. Table cron.job_run_details is empty and there are no entries in a logfile (even error ones).\ Versions of production and staging DBs are the same. We performed identical steps to set up **pg_cron**. The only difference we can think of is that staging DB is running on on-premise server while production DB is running on Azure's virtual machine. We tried to switch to unix domain socket to allow connections from localhost by running:
-sql
UPDATE cron.job SET nodename = '' WHERE jobid = ;
This helped, but temporarily. Job has been successfully executed three times and after that stopped to run again. Any ideas on how to solve this problem are appreciated.
Asked by Lesya Klachko (1 rep)
Sep 11, 2023, 03:13 PM