Postgresql pg_cron job never executed

postgresql

I added pg_cron extension to my database (postgresql 9.6) to schedule a job.
I created a job to be executed every day at 3:30 am:

SELECT cron.schedule('30 3 * * *', $$create table  tbl_new as select  * from temp$$);

I can see that this job stored in the database:

mydb=# select * from cron.job;
 jobid |  schedule  |                        command                         | nodename  | nodeport |    database    | username 
-------+------------+--------------------------------------------------------+-----------+----------+----------------+----------
     7 | 5 17 * * * | create table  tbl_new as select  * from temp | localhost |     5432 | mydb           | postgres
(1 row)

The problem is that the job is never executed and I there is no error in the error log.

Please advice how to fix this problem.

Best Answer

The tasks initiated by pg_cron must be able to connect with the parameters mentioned in cron.job, without a password.

If steps have not been taken in pg_hba.conf or through a .pgpass file in the server's environment to allow for that, the task won't be able to run.

Concerning the log file, error reports would be expected there, but pg_cron logs at the LOG level. Maybe it's below your current level (see log_min_messages) or there's another problem.

Related Question