Database Reference
In-Depth Information
Job Scheduling with pgAgent
pgAgent is a handy utility for scheduling PostgreSQL jobs. But it can also execute batch
scripts in the OS, replacing
crontab
on Linux/Unix and the task scheduler on Windows.
pgAgent goes even further: you can schedule jobs to run on any other host regardless
of OS. All you have to do is install the pgAgent service on the host and point it to use a
specific PostgreSQL database with pgAgent tables and functions installed. The Post‐
greSQL server itself is not required, but the client connection libraries are. Because
pgAgent is built atop PostgreSQL, you are blessed with the added advantage of having
access to all the tables controlling the agent. If you ever need to replicate a complicated
job multiple times, you can go straight into the database tables directly and insert the
records for new jobs, skipping the pgAdmin interface.
We'll get you started with pgAgent in this section. Visit
Setting Up pgAgent and Doing
Scheduled Backups
to see more working examples and details of how to set it up.
Installing pgAgent
You can download pgAgent from
pgAgent Download
.
It is also available via the EDB
Application Stackbuilder commonly used to install PostgreSQL on Windows. The
packaged SQL installation script creates a new schema named pgAgent in the
post
gres
database. When you connect to your server via pgAdmin, you will see a new section
called Jobs, as shown in
Figure 4-17
.
Figure 4-17. pgAdmin with pgAgent installed
If you want pgAgent to run batch jobs on additional servers, follow the same steps,
except you don't have to reinstall the SQL script packaged with pgAgent. Pay particular
attention to the OS permission settings of the pgAgent service/daemon account. Make
sure each agent has sufficient privileges to execute the batch jobs that you will be sched‐
uling.
Batch jobs often fail in pgAgent even when they might run fine from
the command line. This is often due to permission issues. pgAgent
always runs under the same account as the pgAgent service/
daemon. If this account doesn't have sufficient privileges or the nec‐
essary network path mappings, jobs fail.
