Automatic restart of pgAgent job when it fails

[darksabersan]

Hi Guys,

I have several Agents setup on various PostgreSQL 10 databases to run tasks but I would like to know if someone of you knows a tool that can restart an Agent that didn't run properly.

I am used to use a query to see the status of every tasks when it fails or success as below, launched from postgres db:

SELECT jobid, jobname, jobenabled, lastjob.jlgstatus last_job_status,
jstid, jstname, (jstkind, jstonerror) "type->error",lastjobstep.jslstatus last_step_status, jsloutput,jslduration, jstcode jobstepcode
FROM pgagent.pga_job 
LEFT JOIN pgagent.pga_jobstep on jobid = jstjobid
LEFT JOIN (SELECT _last.jlgjobid, _last.max_jlgid, jlgstatus, jlgstart
		   FROM  pgagent.pga_joblog, (select jlgjobid, max(jlgid) max_jlgid FROM pgagent.pga_joblog group by jlgjobid) _last
		   WHERE jlgid = max_jlgid) lastjob                              
ON jobid = jlgjobid 
LEFT JOIN (SELECT _last.jsljstid, jslstatus, jslstart, jsloutput, jslduration
			FROM pgagent.pga_jobsteplog, (select jsljstid, max(jslid) max_jstid from pgagent.pga_jobsteplog group by jsljstid) _last
			where jslid =  max_jstid) lastjobstep
ON jstid = jsljstid

ORDER BY jobid,jstid

Unfortunately this is only possible when I am behind my screen and if something happen after my working hour, I would love also to receive an email alert to be informed.

Thanks in advance for your advice.

darksabersan

[rahmansunbeam]

Have you tried any cron job or scheduler? I tried on websites but haven't tried on databases. Sending email are easy but triggering certain events tricky, try this. 

[Lurker]

by saying "didnt run properly" is it the agent crash or the agent running but cannot run the task correctly?

[darksabersan]

Thanks @rahmansunbeam, I will have look closer.

@Lurker, when I said "didn't run properly" I mean that usually I use *.bat file to run Python Script for downloading, converting data and upload them to PostgreSQL.

Sometime the process is stopped due to disconnected distant server and obviously it fails.

 

[spazzle]

Hi darksabersan

I was wondering if you have considered using a Software robot(s) to automate the tasks and ensure the jobs are completed.

Two RPA's I know are in the wild are:

WinAutomation (Microsoft have just aquired it) and Uipath,  RPA - Robot Process Automation

Also there are Macro programms such as Pitrinec Macro Toolworks or Macro Expert may help or

System Scheduler (Splinterware) or CronVisual (Automation, Intergration and Task Scheduling)

Edited June 21, 2020 by spazzle