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