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.