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):
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:
UPDATE cron.job SET nodename = '' WHERE jobid = <job_id>;
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.
