How to deal with 'failed' pg_cron jobs (postgres)

Question

I have some Jobs (postgres plpgsql procedures) that I am scheduling using pg_cron library/extension and cron expressions.

I have some scripts that have failed and thus logged the 'failed' status column in the cron.job_run_details table. The problem is that the Job will try to re-try, even though there it will fail again, which causes necessary load on my DB.

My main database tables are of course on a different database than the default postgres db that pg_cron uses, thus, I cannot query the cron.job_run_details table (cross db). I wanted to probably query this table is an entry for my job name existed with a 'failed' status.

What is the best way to deal with these failures? Since the whole point of pg_cron is to automate, I need a way to have these scripts running and if they fail, to somehow resolve themselves without manual intervention.

Answer 1

First check the query you setup on Cron by running it on the db where the tables reside.

If the query is OK then set it again on Cron and set the Cron timings to * * * * * so that you can check the results on real time. If it succeeded then you can change the Cron timings.

Please refer the link below for detailed explanation on Pg_Cron:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

I hope it helps!