PgAgent, in plain words, is like a “cron” but specifically for Postgres databases and a much more powerful level, a great help for carrying out scheduled jobs.

To install pgagent, for example from Debian, just run: apt-get install pgagent

Once on the computer we proceed to the real task, its configuration. First we have to make sure that we have the plpgsql language installed:

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;

Now it’s time to create the necessary objects in the database to be able to manage these jobs. To do this, we will use our favorite Postgres interpreter, for example pgAdmin III, and we will execute the pgagent.sql file located in:

/usr/share/pgadmin3/pgagent.sql Once you have reached this step, it is now necessary to leave the pgagent running as a daemon, so that it can enhance the work at any time, for example by copying this script to /etc/init.d/pgagent

#!/bin/bash # /etc/init.d/pgagent # ### BEGIN INIT INFO # Provides: pgagent # Required-Start: $local_fs $syslog # Required-Stop: $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Run postgres agent # Description: This file should be used to construct scripts to be # placed in /etc/init.d. ### END INIT INFO #. /etc/init.d/functions . /lib/lsb/init-functions RETVAL=0 prog="PgAgent" start() { log_daemon_msg "Starting pgAgent" "pgagent" if start-stop-daemon --start --quiet --pidfile /var/run/pgagent.pid --exec /usr/bin/pgagent "hostaddr=127.0.0.1 dbname=postgres user=postgres password=XXXXXXX";then log_end_msg 0 RETVAL=$? echo else log_end_msg 1 fi } stop() { log_daemon_msg "Stopping pgAgent" "pgagent" if start-stop-daemon --stop --quiet --oknodo --pidfile /var/run/pgagent.pid; then log_end_msg 0 # RETVAL=$? # echo else log_end_msg 1 fi } # # See how we were called. # case "$1" in start) start ;; stop) stop ;; reload|restart) stop start RETVAL=$? ;; status) status /usr/bin/pgagent RETVAL=$? ;; *) echo $"Usage: $0 {start|stop|restart|reload|status}" exit 1 esac exit $RETVAL

If we do not want to write the access password in that script, it is also possible to save it in root’s .pgpass

We make sure it is executable chmod 755 /etc/rc.d/init.d/pgagent

And we place it in the boot process update-rc.d pgagent defaults

One recommendation, delete the connection that you already have created in PgAdmin III and create it again, it may be that refreshing does not show the jobs section.