Database Reference
In-Depth Information
Helpful pgAgent Queries
With your finely honed SQL skills, you can easily replicate jobs, delete jobs, and edit
jobs directly by messing with pgAgent metatables. Just be careful! For example, to get
a glimpse inside the tables controlling all of your agents and jobs, connect to the post
gres database and execute the query in Example 4-3 .
Example 4-3. Description of pgAgent tables
SELECT c . relname As table_name , d . description
FROM
pg_class As c INNER JOIN
pg_namespace n ON n . oid = c . relnamespace INNER JOIN
pg_description As d ON d . objoid = c . oid AND d . objsubid = 0
WHERE n . nspname = 'pgagent'
ORDER BY c . relname ;
table_name | description
---------------+-------------------------
pga_job | Job main entry
pga_jobagent | Active job agents
pga_jobclass | Job classification
pga_joblog | Job run logs.
pga_jobstep | Job step to be executed
pga_jobsteplog | Job step run logs.
pga_schedule | Job schedule exceptions
Although pgAdmin already provides an intuitive interface to pgAgent scheduling and
logging, you may find the need to generate your own jobs reports. This is especially true
if you have many jobs or you want to compile stats from your job results. Example 4-4
demonstrates the one query we use often.
Example 4-4. List log step results from today
SELECT j . jobname , s . jstname , l . jslstart , l . jslduration , l . jsloutput
FROM
pgagent . pga_jobsteplog As l INNER JOIN
pgagent . pga_jobstep As s ON s . jstid = l . jsljstid INNER JOIN
pgagent . pga_job As j ON j . jobid = s . jstjobid
WHERE jslstart > CURRENT_DATE
ORDER BY j . jobname , s . jstname , l . jslstart DESC ;
We find this query essential for monitoring batch jobs because sometimes a job will
report success even though it failed. pgAgent can't always discern the success or failure
of a shell script on the OS. The jsloutput field in the logs provides the shell output,
which usually details what went wrong.
Search WWH ::




Custom Search