Jump to content

Automatic restart of pgAgent job when it fails


Recommended Posts

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.


Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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 by spazzle
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.



If you enjoy our contents, support us by Disable ads Blocker or add GIS-area to your ads blocker whitelist