Yum addendum for 8.3.5 and PgAgent

We had the pleasure of doing a fresh install of PostgreSQL 8.3.5 on RedHat EL4 box and when using the Yum repository, we noticed a couple of changes from last time we did this. This could have been an oversight in our documentation before.

Changes to Yum Install for 8.3.5?

In our April 2008 issue we had An Almost Idiot's Guide to PostgreSQL YUM and that article still seems to be surprisingly popular.

In the first step we had:
yum install postgresql
and that as I recall installed the postgresql server in addition to some client libraries.

For 8.3.5 fresh install it seems they are separated and to get the postgresql server you need to do:

yum install postgresql
yum install postgresql-server

We also throw in to get the development headers:
yum install postgresql-devel

PgAgent is now separate from PgAdmin III for Linux

We also described PgAgent in our January 2008/February 2008 issue, which is a task scheduling agent for PostgreSQL, which is similar in concept to Microsoft SQL Server Agent, but cross-platform so should fill Microsofties with a warm and fuzzy feeling.

So to get PgAgent

1. Download from http://www.postgresql.org/ftp/pgadmin3/release/pgagent/
2. Extract - Copy bin/pgagent file to /usr/bin/
3. Make sure to mark as executable with chmod 775 /usr/bin/pgagent
4. Run psql -U postgres -d postgres -f pgagent.sql
5. Install PgAgent as a service. See next section.

PgAgent on CentOS/Red Hat EL as a service

Below is a sample script we hacked together from some samples we have seen. Works for us at anyrate, but we are predominantly microsofties though less so than we were before, so more professional Linux users, feel free to butt in.

#!/bin/bash
#
#   /etc/rc.d/init.d/pgagent
#
# Starts the pgagent daemon
#
# chkconfig: - 65 35
# description: PgAgent Postgresql Job Service
# processname: pgagent
# Source function library.
. /etc/init.d/functions


RETVAL=0
prog="PgAgent"

start() {
    echo -n $"Starting $prog: "
    daemon "pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres"
    RETVAL=$?
    echo
}

stop() {
    echo -n $"Stopping $prog: "
    killproc /usr/bin/pgagent
    RETVAL=$?
    echo
}

#
#   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

1. Then copy the above script to a text file called pgagent -- no extension
2. Upload to folder on server /etc/rc.d/init.d. The path may vary slightly
3. On server do a chmod 755 /etc/rc.d/init.d/pgagent to make it executable.
4. to Add to start up services: chkconfig pgagent on
5. To test you can do a service pgagent start

It should automatically start on its own during server boot ups.