Sunday, April 28, 2013

Installing pgAgent 3.3 on CentOS 5.x (x86_64)

pgAgent is PostgreSQL's job scheduler that provides the ability to run batch (SQL or shell script) jobs on a PostgreSQL database with a set schedule.  Once installed, a "Jobs" section will be visible to the postgres user in pgAdmin.  In the past pgAgent was distributed as part of pgAdmin, but now it's distributed as a separate package.

I had a terrible time trying to follow the (oversimplified) README instructions to install pgAgent 3.3 on our CentOS servers.  The biggest issue I had was with pgAgent's cmake/ccmake not finding the correct wxWidgets configuration.  After so much trial and error, I decided the process is involved enough to share with everyone else having the same issues.

If you're an expert, these are the basic steps needed:

  1. Install epel-release-5-4.noarch.rpm
  2. Install wxGTK.x86_64 and wxGTK-devel.x86_64 and cmake
  3. Download, build and install wxWidgets from source
  4. Build wxWidgets/contrib
  5. Download, build, and install pgAgent from source
  6. Install pgagent schema
  7. Make sure Postgres's lib directory is in LD_LIBRARY_PATH
  8. Install the pgagent SysV service
  9. Start the pgagent service
  10. Enable pgagent service to start at boot time
If you're not, read on...

Before you follow these steps, please note my environment:

  • OS Release: CentOS 5.4 64bit
  • PostgreSQL Release: PostgreSQL 9.1
  • PostgreSQL Location: /usr/local/pgsql/

Install epel-release-5-4.noarch.rpm

The epel-release package provides "Extra Packages for Enterprise Linux".  This includes the required wxGTK and all of its dependencies, as well as cmake.

# rpm -Uvh http://mirrors.servercentral.net/fedora/epel/5/i386/epel-release-5-4.noarch.rpm

Install wxGTK.x86_64 and wxGTK-devel.x86_64 and cmake

wxWidgets is a C++ library for developing cross-platform GUI applications.  wxGTK is the GTK implementation of the wxWidgets API.  Why is it needed for pgagent?  I don't know, but I'm guessing it might be because it was previously part of pgAdmin, which is a GUI application.  The cmake package is a cross-platform build system that's used to build  pgAgent from source.

# yum install wxGTK.x86_64 wxGTK-devel.x86_64 cmake

Download, build and install wxWidgets from source

Download the latest wxWidgets source from http://sourceforge.net/projects/wxwindows/files/2.8.12/wxWidgets-2.8.12.tar.gz/
$ tar -zxvf wxWidgets-2.8.12.tar.gz
$ cd wxWidgets*
$ ./configure --with-gtk --enable-gtk2 --enable-unicode
$ make
$ su -c "make install; /sbin/ldconfig"

Build wxWidgets/contrib

$ cd contrib
$ make
$ su -c "make install"

Download, build, and install pgAgent from source

One thing to note in building pgAgent from source is that you'll have to tell cmake where your pg_config is located, as well as enabling dynamic linking to the wxWidgets library by turning off static linking.  Without dynamic linking, I would get this error when running cmake:

The selected wxWidgets configuration (version: 2.8, debug: no, static: yes, unicode: yes, modules: base) is not available.

Download the latest pgAgent source from http://www.postgresql.org/ftp/pgadmin3/release/pgagent/.  The current release is 3.3.0.
$ tar -zxvf pgAgent-3.3.0-Source.tar.gz
$ cd pgAgent*
$ cmake -D PG_CONFIG_PATH:FILEPATH=/usr/local/pgsql/bin/pg_config -D STATIC_BUILD:BOOL=OFF .
$ make
$ su -c "make install"

Install schema changes

pgAgent requires the pgagent schema to be installed.  It contains the tables necessary for the agent to store jobs, schedules, and logs.  This schema is what pgAdmin looks for in order to display the "Jobs" section.

$ psql -U postgres -d postgres -f pgagent.sql


Make sure PostgreSQL's lib directory is in LD_LIBRARY_PATH

You can, of course, add the lib path (in my case /usr/local/pgsql/lib) to LD_LIBRARY_PATH.  However, the easiest way to manage this is to add it to the /etc/ld.so.conf.d/ directory.

$ su -c "echo '/usr/local/pgsql/lib' >> /etc/ld.so.conf.d/postgres.conf"

Install the pgagent SysV service

To be able to use the pgagent binary as a service, a script named /etc/rc.d/init.d/pgagent needs to exist, with permissions 755.  The script is a simple bash script that responds to commands like start, stop, restart, etc.  I took the one from http://svn.pgrpms.org/browser/rpm/redhat/9.0/pgagent/EL-6/pgagent.init?rev=3 and modified the path to match my installation.  Here's my code:

#!/bin/bash
#
# /etc/rc.d/init.d/pgagent
#
# Manages the pgagent daemon
#
# chkconfig: - 65 35
# description: PgAgent PostgreSQL Job Service
# processname: pgagent
. /etc/init.d/functions


RETVAL=0
prog="PgAgent"

start() {
    echo -n $"Starting $prog: "
    daemon "/usr/local/bin/pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres"
    RETVAL=$?
    echo
}
stop() {
    echo -n $"Stopping $prog: "
    killproc /usr/local/bin/pgagent
    RETVAL=$?
    echo
}

case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  reload|restart)
    stop
    start
    RETVAL=$?
    ;;
  status)
    status /usr/local/bin/pgagent
    RETVAL=$?
    ;;
  *)
    echo $"Usage: $0 {start|stop|restart|reload|status}"
    exit 1
esac

exit $RETVAL

Start pgagent service

$ su -c "/sbin/service pgagent start"

After running this, you should see an [OK] message.  You may get this error when starting the service:

pgagent: unrecognized service

This may mean you have to make the service executable by setting permissions to 755.  Another error you may get is this:

Starting PgAgent: /usr/local/bin/pgagent: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

This means that pgagent can't find the PostgreSQL library libpq.  Running "ldconfig" should fix it.

To make sure the job is running, run "service pgagent status".  It should show 1) that it's running, and 2) what the PID is.

Enable pgagent service to start at boot time

Now that everything is working, enable the pgagent service to start at boot time for run levels 3, 4, and 5.
$ su -c "/sbin/chkconfig --level 345 pgagent on"

Celebrate.  Create Jobs!

http://www.pgadmin.org/docs/1.6/pgagent.html

Feedback and corrections welcomed.

Installing pgAgent 3.3 on CentOS 5.x (x86_64)

pgAgent is PostgreSQL's job scheduler that provides the ability to run batch (SQL or shell script) jobs on a PostgreSQL database with a...