PostgreSQL HA with pgpool-II - Part 4

This is the fourth part of the tutorial. Other parts are:

• PostgreSQL HA with pgpool-II - Part 1
• PostgreSQL HA with pgpool-II - Part 2
• PostgreSQL HA with pgpool-II - Part 3
• PostgreSQL HA with pgpool-II - Part 4
• PostgreSQL HA with pgpool-II - Part 5
• PostgreSQL HA with pgpool-II - Part 6

Start from the beginning (PostgreSQL HA with pgpool-II - Part 1)!

This part deals with another painful thing: installing pgpool-II. The ultimate resource for this is pgpool-II manual. Here I'll extract the essence, and present it in easier step-by-step way. Let's start.

4.1 Installing pgpool-II

For Ubuntu users (like myself), problems start immediately. The official Ubuntu repository still offers pgpool-II version 3.3, and again we want the latest (3.5 at the moment of this writing). Again we have a good reason to insist on the newest since pgpool-II versions 3.4 and 3.5 have introduced significant improvements in performances (as you can read here). How to obtain the latest version? Well, if you are using CentOS - you're lucky - pgpool maintains yum repository. But if you are Ubuntu guy, you need to compile from source. I'm not sure why pgpool ignores Debian family and apt repository, but they do. Luckily, the installation from source isn't too hard. Pain comes later, with configuration, so CentOS guys - don't celebrate too much. Also, even if you are using CentOS, read through the next section in order to sync with us, and to have understanding about files included in the install.

4.1.1 Installing from Source

Let's start with installing packages that'll be needed for compilation:

$ apt-get update
$ apt-get install libpq-dev make

Besides mentioned packages this will install all dependencies, of course. We'll also need postgresql-9.5-pgpool2 package, but if you've followed previous parts of this tutorial - it is already installed.

The next thing to do is to download and extract source tarball:

$ # Download the tarball:
$ wget http://www.pgpool.net/download.php?f=pgpool-II-3.5.2.tar.gz -O pgpool-II-3.5.2.tar.gz
$ # Extract the tarball:
$ tar -xzf pgpool-II-3.5.2.tar.gz
$ # Delete the tarball once extracted:
$ rm pgpool-II-3.5.2.tar.gz
$ # cd to source directory:
$ cd pgpool-II-3.5.2

After that we'll configure and compile the package. There are more configuration options, but I'll use only one that defines installation directory, and I'll set it to /usr/share/pgpool2/3.5.2. You can change the installation directory to some other location if you want (i.e. another good pick would be /opt/pgpool2/3.5.2). Still in source directory, execute the following:

$ ./configure --prefix=/usr/share/pgpool2/3.5.2
 
$ make
$ make install

This will compile and install binaries (and some other things) in the specified location. Next thing to do is to create and define configuration directory. Do the following:

• Pick and create configuration directory. To be consistent with PostgreSQL, I pick my configuration directory to be /etc/pgpool2/3.5.2, so I'll create it there.

• Move content of /usr/share/pgpool2/3.5.2/etc directory to your new configuration directory. (Note that /usr/share/pgpool2/3.5.2 used here is actually installation directory you've selected in compilation process.) Content that will be moved are actually example configuration files (pcp.conf.sample and few others). Example move command (do the same for all other files from the directory):

  $ mv /usr/share/pgpool2/3.5.2/etc/pcp.conf.sample /etc/pgpool2/3.5.2/

  The files that need to be moved this way are:

  • pcp.conf.sample
  • pgpool.conf.sample
  • pgpool.conf.sample-master-slave
  • pgpool.conf.sample-replication
  • pgpool.conf.sample-stream
  • pool_hba.conf.sample

• Copy, move, or link binary files from your installation directory to /usr/sbin. I prefer using symbolic links, but you can also move or copy the files. Here's an example for file pcp_attach_node (you should do the same for all other files):

  $ # Create symbolic link:
  $ ln -s /usr/share/pgpool2/3.5.2/bin/pcp_attach_node /usr/sbin/pcp_attach_node
  $ # OR move:
  $ mv /usr/share/pgpool2/3.5.2/bin/pcp_attach_node /usr/sbin/
  $ # OR copy:
  $ cp /usr/share/pgpool2/3.5.2b/bin/pcp_attach_node /usr/sbin/

  The files that need to be linked / moved / copied this way are:

  • pcp_attach_node
  • pcp_detach_node
  • pcp_node_count
  • pcp_node_info
  • pcp_pool_status
  • pcp_proc_count
  • pcp_proc_info
  • pcp_promote_node
  • pcp_recovery_node
  • pcp_stop_pgpool
  • pcp_watchdog_info
  • pg_md5
  • pgpool

4.1.1.1 Extensions and SQL Scripts

There's one thing I must admit before starting this section: not all scripts that we'll prepare here are needed in our scenario. You can check original documentation to see when these scripts and extensions are used. Nevertheless, for the sake of completeness of pgpool-II installation, I will cover all the scripts here, and it won't hurt too much if you also do so.

pgpool-II comes with some PostgreSQL extensions and SQL scripts. You can find these in source directory (extracted from tarball), in src/sql subdirectory. Original pgpool-II documentation says that they should be compiled, but you shouldn't do this - it is already covered by installed package postgresql-9.5-pgpool2. You only need to confirm that needed binaries (pgpool_adm, pgpool-recovery, and pgpool-regclass) are already available in PostgreSQL library (/usr/lib/postgresql/9.5/lib). But extensions and SQL scripts should be copied anyway.

Extensions should be copied to extension subdirectory of PostgreSQL installation directory (/usr/share/postgresql/9.5/extension). Each extension consists of two files: *.control and *.sql, and to copy an extension we need to copy them both. Besides extensions there are some SQL script files we would also want to copy, but we will copy them to sql subdirectory of PostgreSQL configuration directory (/etc/postgresql/9.5/main/sql). Again, both extensions and SQL script files are at the moment in src/sql subdirectory of source directory (extracted from tarball). So let's start copying:

$ # Create SQL scripts directory:
$ mkdir /etc/postgresql/9.5/main/sql
$ # Navigate to source directory:
$ cd ~/pgpool-II-3.5.2
$ # Navigate to src/sql subdirectory:
$ cd src/sql
$ # While there let's copy the first script file:
$ cp insert_lock.sql /etc/postgresql/9.5/main/sql/
$ # Navigate to pgpool_adm (the first extension) subdirectory:
$ cd pgpool_adm
$ # Let's copy pgpool_adm extension:
$ cp pgpool_adm.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_adm--1.0.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script file also (note that I'm changing extension of the file also):
$ cp pgpool_adm.sql.in /etc/postgresql/9.5/main/sql/pgpool_adm.sql
$ # Navigate up in order to select another extension:
$ cd ..
$ # Navigate to pgpool-recovery (the next extension) subdirectory:
$ cd pgpool-recovery
$ # Let's copy pgpool-recovery extension:
$ cp pgpool_recovery.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_recovery--1.1.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script files also (note that I'm changing extension of the file also):
$ cp pgpool-recovery.sql.in /etc/postgresql/9.5/main/sql/pgpool-recovery.sql
$ cp uninstall_pgpool-recovery.sql /etc/postgresql/9.5/main/sql/
$ # Navigate up in order to select another extension:
$ cd ..
$ # Navigate to pgpool-regclass (the next extension) subdirectory:
$ cd pgpool-regclass
$ # Let's copy pgpool-regclass extension:
$ cp pgpool_regclass.control /usr/share/postgresql/9.5/extension/
$ cp pgpool_regclass--1.0.sql /usr/share/postgresql/9.5/extension/
$ # While there let's copy SQL script files also (note that I'm changing extension of the file also):
$ cp pgpool-regclass.sql.in /etc/postgresql/9.5/main/sql/pgpool-regclass.sql
$ cp uninstall_pgpool-regclass.sql /etc/postgresql/9.5/main/sql/
 
$ # Navigate to your home directory and you can delete pgpool source directory since we've copied everything we need:
$ cd ~
$ rm -r pgpool-II-3.5.2

Now we need to change copied SQL script files. Basically, we need to change MODULE_PATHNAME to the appropriate module path. Also we need to change $libdir with actual PostgreSQL library directory (/usr/lib/postgresql/9.5/lib). MODULE_PATHNAME is actually mentioned library path plus actual module name. As an example here I'll provide pgpool-recovery.sql before and after the change:

CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text, text)
RETURNS bool
AS 'MODULE_PATHNAME', 'pgpool_recovery'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS 'MODULE_PATHNAME', 'pgpool_remote_start'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '$libdir/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS 'MODULE_PATHNAME', 'pgpool_switch_xlog'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_recovery'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_remote_start'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
 
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS '/usr/lib/postgresql/9.5/lib/pgpool-recovery', 'pgpool_switch_xlog'
LANGUAGE C STRICT;

You need to do the same for all other SQL script files from /etc/postgresql/9.5/main/sql. As a hint I'll tell you that module name that is used as a replacement always corresponds to name of the file you are changing (i.e. while changing file pgpool-recovery.sql you'll use module name pgpool-recovery). Another important thing to check is if actual module exists in PostgreSQL library (/usr/lib/postgresql/9.5/lib).

Here I need to admit something: in some circumstances PostgreSQL is smart enough to automatically change MODULE_PATHNAME before executing the script. For example, it happens while we are installing extensions, and thanks to that we don't need to change extension files also, although they also contain MODULE_PATHNAME. For example, pgpool_recovery--1.1.sql file we've previously copied to extension directory also contains MODULE_PATHNAME, but we don't need to change it because PostgreSQL will do this automatically on execution. But with regular SQL script files I don't know how to accomplish this, and for this reason I've changed everything manually. Except for a bit more work, there's no harm in doing that.

It's probably not necessary, but I like to have all directories and files in /etc/postgresql owned by postgres user, so I'll execute:

$ chown postgres:postgres -R /etc/postgresql/9.5/main/sql

4.1.1.2 Service Script

We would like pgpool-II to run as service, of course, so we need to create and register service script. I've created one by modifying one that gets installed when you install pgpool-II v3.3 package from the official Ubuntu repository. Here I'll provide this script, but you can change it if you want. Actually there are two parts of this script: defaults file which contains default configuration options, and the script itself. I'll provide both files here. Note that if you've used different directories, you need to adjust the files appropriately.

Defaults file:

# Defaults for pgpool initscript
# sourced by /etc/init.d/pgpool2
 
# set to "yes" if you want to enable debugging messages to the log
PGPOOL_LOG_DEBUG=no
 
# config file
PGPOOL_CONFIG_FILE=/etc/pgpool2/3.5.2/pgpool.conf
 
# hba file
PGPOOL_HBA_CONFIG_FILE=/etc/pgpool2/3.5.2/pool_hba.conf
 
# pcp config file
PGPOOL_PCP_CONFIG_FILE=/etc/pgpool2/3.5.2/pcp.conf
 
# PID file. Must be the same as defined in pgpool.conf (pid_file_name)
PGPOOL_PID_FILE=/var/run/postgresql/pgpool.pid

Service script:

#! /bin/sh
 
### BEGIN INIT INFO
# Provides:          pgpool2
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Should-Start:      postgresql
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: start pgpool-II
# Description: pgpool-II is a connection pool server and replication
#              proxy for PostgreSQL.
### END INIT INFO
 
 
PATH=/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/sbin/pgpool
 
test -x $DAEMON || exit 5
 
# Include pgpool defaults if available
if [ -f /etc/default/pgpool2 ] ; then
    . /etc/default/pgpool2
fi
 
PIDFILE=${PGPOOL_PID_FILE:-/var/run/postgresql/pgpool.pid}
 
PGPOOL_CONFIG_FILE=${PGPOOL_CONFIG_FILE:-/etc/pgpool2/3.5.2/pgpool.conf}
if [ ! -f $PGPOOL_CONFIG_FILE ]; then
    echo "Config file not found."
    log_failure_msg "Config file not found."
    exit 1
fi
 
if [ x"$PGPOOL_LOG_DEBUG" = x"yes" ]; then
    OPTS="-d -f $PGPOOL_CONFIG_FILE"
else
    OPTS="-f $PGPOOL_CONFIG_FILE"
fi
 
PGPOOL_PCP_CONFIG_FILE=${PGPOOL_PCP_CONFIG_FILE:-/etc/pgpool2/3.5.2/pcp.conf}
if [ -f $PGPOOL_PCP_CONFIG_FILE ]; then
    OPTS="$OPTS -F $PGPOOL_PCP_CONFIG_FILE"
fi
 
STOPOPTS=$OPTS
 
PGPOOL_HBA_CONFIG_FILE=${PGPOOL_HBA_CONFIG_FILE:-/etc/pgpool2/3.5.2/pool_hba.conf}
if [ -f $PGPOOL_HBA_CONFIG_FILE ]; then
    OPTS="$OPTS -a $PGPOOL_HBA_CONFIG_FILE"
fi
 
. /lib/lsb/init-functions
 
 
is_running() {
    pidofproc -p $PIDFILE $DAEMON >/dev/null
}
 
 
d_start() {
 
    if ! test -d /var/run/postgresql; then
        install -d -m 2775 -o postgres -g postgres /var/run/postgresql
    fi
 
    if ! test -d /var/log/pgpool; then
        install -d -m 2775 -o postgres -g postgres /var/log/pgpool
    fi
 
    if is_running; then
        :
    else
        echo "FD - Starting pgpool-II by executing:"
        echo "$DAEMON -n $OPTS >> /var/log/pgpool/pgpool.log 2>&1 &"
        su -c "$DAEMON -n $OPTS >> /var/log/pgpool/pgpool.log 2>&1 &" - postgres
    fi
}
 
 
d_stop() {
    echo "FD - Starting pgpool-II by executing:"
    echo "$DAEMON $STOPOPTS -m fast stop"
    su -c "$DAEMON $STOPOPTS -m fast stop" - postgres
}
 
d_reload() {
    echo "FD - Reloading pgpool-II by executing:"
    echo "$DAEMON $OPTS reload"
    su -c "$DAEMON $OPTS reload" - postgres
}
 
case "$1" in
    start)
        log_daemon_msg "Starting pgpool-II" pgpool
        d_start
        log_end_msg $?
        ;;
    stop)
        log_daemon_msg "Stopping pgpool-II" pgpool
        d_stop
        log_end_msg $?
        ;;
    status)
        is_running
        status=$?
        if [ $status -eq 0 ]; then
            log_success_msg "pgpool-II is running."
        else
            log_failure_msg "pgpool-II is not running."
        fi
        exit $status
        ;;
    restart|force-reload)
        log_daemon_msg "Restarting pgpool-II" pgpool
        d_stop && sleep 1 && d_start
        log_end_msg $?
        ;;
    try-restart)
        if $0 status >/dev/null; then
            $0 restart
        else
            exit 0
        fi
        ;;
    reload)
        log_daemon_msg "Reloading pgpool-II" pgpool
        d_reload
        log_end_msg $?
        ;;
    *)
        log_failure_msg "Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}"
        exit 2
        ;;
esac

Now we can register the service by executing:

$ update-rc.d pgpool2 defaults

But since we haven't configured pgpool-II yet, let's the service for now:

$ update-rc.d pgpool2 disable

4.2 Where to Go Next?

I'm getting tired of this. Hopefully we'll get HA in the next part - PostgreSQL HA with pgpool-II - Part 5. (I've already mentioned that I'm going with this tutorial and my implementation in parallel, so I still don't have HA up and running.)