TutorialDBA - Support | Training | Consultant

Introduced in pgAdmin III v1.4, pgAgent is a job scheduling agent for PostgreSQL, capable of running multi-step batch/shell and SQL tasks on complex schedules. From pgAdmin 1.9 onwards, pgAgent is distributed independently of pgAdmin. It may be downloaded from the  download area . Contents: pgAgent Installation pgAgent Jobs pgAgent Schedules pgAgent Steps

Taking regular SQL database backup is one of the most important responsibilities of a database administrator. By setting up automatic database backup we can get rid of the additional overhead of doing it manually, every day, week or month. When it comes to PostgreSQL running in any Unix distributions or in Windows, there are at least two ways to do it. Using the built in CronJobs/ CronTabs in case of  Linux, and Windows scheduled tasks in Windows . Using PostgreSQL’s own scheduling agent,  pgAgent . Often DBAs tends to rely on CronTabs or Scheduled tasked because of the ease of use it offers. But there are several advantages if you use pgAgent instead. Though it takes a little effort to install pgAgent, it is much better than Cron jobs or Scheduled tasks. Why use pgAgent over CronJob: Compared to CronTab, PgAgent has the following advantages:  You can have multiple steps for a job without having to resort to a batch script. You can have multiple sched...

Taking regular SQL database backup is one of the most important responsibilities of a database administrator. By setting up automatic database backup we can get rid of the additional overhead of doing it manually, every day, week or month. When it comes to PostgreSQL running in any Unix distributions or in Windows, there are at least two ways to do it. Using the built in CronJobs/ CronTabs in case of  Linux, and Windows scheduled tasks in Windows . Using PostgreSQL’s own scheduling agent,  pgAgent . Often DBAs tends to rely on CronTabs or Scheduled tasked because of the ease of use it offers. But there are several advantages if you use pgAgent instead. Though it takes a little effort to install pgAgent, it is much better than Cron jobs or Scheduled tasks. Why use pgAgent over CronJob: Compared to CronTab, PgAgent has the following advantages:  You can have multiple steps for a job without having to resort to a batch script. You can have multiple schedu...

We    wrote an init script for PGAgent. It may need some tweaking but it gets the job done in our production environment. #!/bin/bash # # /etc/init.d/pgagent # test -f /lib/lsb/init-functions || exit 1 . /lib/lsb/init-functions PIDFILE=/var/run/pgagent.pid prog=PGAgent PGAGENTDIR=/usr/bin PGAGENTOPTIONS="hostaddr=127.0.0.1 dbname=postgres user=postgres" start() { log_begin_msg "Starting PGAgent" start-stop-daemon -b --start --quiet --exec "$PGAGENTDIR/pgagent" --name pgagent --startas "$PGAGENTDIR/pgagent" -- $PGAGENTOPTIONS || log_end_msg 1 log_end_msg 0 } stop() { log_begin_msg "Stopping PGAgent" start-stop-daemon --stop --quiet -n pgagent || log_end_msg 1 log_end_msg 0 } # # See how we were called. # case "$1" in start) start ;; stop) stop ;; reload|restart) stop start RETVAL=$? ;; status) status /usr/bin/pgagent RETVAL=$? ;; *) log_suc...

pgAgent is a scheduling agent for PostgreSQL which is used to run scheduled batch/shell or SQL tasks. pgAgent doesn’t come bundled with PostgreSQL, it needs to be installed( installation )and set-up separately as it run as a stand alone daemon. pgAgent being a powerful tool has some security concerns to be aware of. This post describes, how to set up pgAgent in a way that security threats are minimized. Security concerns: Database password  -  DO NOT  be tempted to include a password in the pgAgent connection string - on Unix systems it may be visible to all users in ‘ps’ output, and on Windows systems it will be stored in the registry in plain text. Instead, use a libpq  ~/.pgpass  file to store the passwords for every database that pgAgent must access. Details of this technique may be found in the  PostgreSQL documentation on .pgpass file . System/database access  - all jobs run by pgAgent will run with the security privileges of the pgA...