High Availability PostgreSQL HOWTO

introduction:
this note describes ways to implement high availibility (ha) for postgresql. ha gives the ability to transparently fail-over database connections to an alternative system in the event of some sort of failure of the primary system.

overview:
the ha-postgresql system works the following way: the ha cluster consists of two machines running heartbeat that use both a serial port and a second network interface to provide the failover heartbeat. the primary system keeps the secondary system database files synchronized by periodic calls to a script that runs rsync.

the heartbeat software provides ha in the form of a hot standby 2-node cluster. in the event of a failure of the primary database system, the heartbeat software causes the secondary to take over. any database changes that may have occurred between the last synchronization and the failure will be lost, so this synchronization must be done relatively frequently. the synchronization is done with the same network interface that is also doing the heartbeat in order to reduce the volume of traffic on the primary network.

setting up ha-postgresql
install heartbeat(http://linux-ha.org/) and get it running properly on the two systems. the installation should use both a serial connection and a secondary network interface (connected via a cross-over cable) to implement the heartbeat. in what follows, i will assume that the secondary network interface is eth1 on both systems and that 10.0.0.1 (hb-castor) is the primary system (castor) and 10.0.0.2 (hb-pollux) is the secondary system (pollux).

configure and install postgresql (http://www.postgresql.org/) on both systems. you should use the same version of postgresql and the same configuration options for both systems. it will also be less confusing to use the same path, uid and gid settings for both machines. in what follows, i will assume that the postgresql datafile directory, pgdata, is /home/pgsql on both systems.
test postgresql, individually on each system. when you are satisfied that it is running properly then bring it down on both sides. on the secondary, clear out the datafile directory:

cd /home/pgsql/base; rm -rf *

next decide upon a synchronization scheme for the servers. this is tricky because postgresql may have cached some of its recent actions in memory and not written them to disk yet.
there are at least three synchronization methods that can be used:

use pg_dump/pg_dumpall. this will assure consistant replication even if the database is being used concurrently. the problem with this approach is that there is no way to may incremental backups so that there can be a needlessly large volume of traffic between the servers if the databases are large. using pg_dump will require iterating the synchronization over all the individual databases on the server but can handle large binary objects. using pg_dumpall will handle the entire collection of databases but it cannot handle large binary objects.

use rsync. this method will keep the volume of data being moved between the servers down. the problem is that since this method works with the data files, its not supposed to work. i have found that in practice, for large databases with low transaction volumes, that it can work. if you have large databases with a low insert/update rates, you might want to experiment with this approach to see if it works for you. rsync setup details.

use rserv. recent postgresql versions (after 7.0) contain a package in the contrib section called rserv. this is a toolset for replicating a master database to a slave database. using rserv will require iterating the synchronization over all the individual databases on the server. it also requires your database tables to contain a column that can be monitored for updates in order for it to detect what to synchronize (i do not yet know what it does if you add/delete a row to/from a table). i have not yet tried doing this for handling failovers, for example it has a master/slave concept, but in a failover situation the roles get reversed, how rserv will work under this situation i still need to test. i will report here once i have (or you can let me know what you did if you have done it this way).

test the transfer.
we can now test the primary-to-secondary transfer with one of the following on the appropriate system:

method command run from
pg_dump pg_dump -b -h 10.0.0.1 (dbname) > /tmp/db.tar; pg_restore -c -d (dbname) /tmp/db.tar; rm /tmp/db.tar  secondary
pg_dumpall pg_dumpall -h 10.0.0.1 > /tmp/db.dump; pg_restore -f /tmp/db.dump template1; rm /tmp/db.dump  secondary
rsync /usr/bin/rsync -auv --delete /home/pgsql/ 10.0.0.2::postgresql/ primary
rserv  

you should now see a copy of all the postgres files on the secondary system in /home/pgsql

if the primary-to-secondary transfer is working, we next need to test the secondary-to-primary transfer.
first, temporarily move the database directory out of the way (we will keep it, just in case) on the primary.

 

mv /home/pgsql/base /home/pgsql/base.tmp
mkdir /home/pgsql/base
chown postgres:postgres /home/pgsql/base
chmod 700 /home/pgsql/base

then try the transfer from the secondary by going to the opposite system from the previous test and typing:

method command run from
pg_dump pg_dump -b -h 10.0.0.2 (dbname) > /tmp/db.tar; pg_restore -c -d (dbname) /tmp/db.tar; rm /tmp/db.tar  primary
pg_dumpall pg_dumpall -h 10.0.0.2 > /tmp/db.dump; pg_restore -f /tmp/db.dump template1; rm /tmp/db.dump  primary
rsync /usr/bin/rsync -auq --delete /home/pgsql/ 10.0.0.1::postgresql/ secondary
rserv  

you should now have a copy of the files in /home/pgsql/base on the primary. if so, you can get rid of the temporary copy:
rm -rf /home/pgsql/base.tmp

the data is transferred between systems by periodically calling a script, db_sync, which does the update:

the rsync version:

#!/bin/sh
# db_sync sync the db with failover copy
# rsync version

# the hb interface of the other side
rhost="hb-pollux"
lockfile="/var/lock/subsys/db_sync"

master=`/sbin/ifconfig | grep "eth0:0" | wc -l`

if [ $master -eq "0" ]; then
# this side not the master
exit 0
fi

# this side is the master

if [ -f $lockfile ]; then
# have not finished since the last time this script was invoked
logger -p daemon.notice -t db_sync "locked, sync already active"
exit 0
fi

touch $lockfile
logger -p daemon.notice -t db_sync "syncing database with $rhost"
sync
/usr/bin/rsync -auq --delete-after /home/pgsql/ $rhost::postgresql/
rm -f $lockfile

exit 0

 

the pg_dumpall version:

#!/bin/sh
# db_sync sync the db with failover copy
# pg_dumpall version
# the pg_dump version is a simple modification of this version

# the hb interface of the other side
rhost="hb-pollux"
lockfile="/var/lock/subsys/db_sync"

master=`/sbin/ifconfig | grep "eth0:0" | wc -l`

if [ $master -eq "1" ]; then
# this side not the slave
exit 0
fi

# this side is the slave

if [ -f $lockfile ]; then
# have not finished since the last time this script was invoked
logger -p daemon.notice -t db_sync "locked, sync already active"
exit 0
fi

touch $lockfile
logger -p daemon.notice -t db_sync "syncing database with $rhost"
pg_dumpall -u postgres -h $rhost > /tmp/db.dump$$
pg_restore -u postgres -f /tmp/db.dump$$ template1
rm -f $lockfile /tmp/db.dump$$

exit 0

this script assumes that eth0:0 is the ha interface of your cluster. a copy of this script is on both systems (with rhost set appropriately). it should be called periodically by cron, e.g.
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/local/sbin/db_sync
in roots crontab file, would cause it to be invoked every 5 minutes. the frequency of this update involves a trade-off. the more often the update is done then the finer the granularity of the mirror copy of the database is, and so there will be less of a data loss when switching over to the secondary. if the synchronization is done too often, then there may be a large volume of network traffic and its possible that the synchronization has not completed when it is time do start another. the script above handles the second case (if you see locked, sync already active messages in the system logs, then you know that there is too much data to transfer in the alloted time).

edit the postgres init file so that on startup it will do an "pull" from the opposite system.
if using rsync, use the command (on castor).
rsync -auq --delete-after hb-pollux::postgresql/ /home/pgsql/
and then start postgresql like normal.
if using the other methods, invoke the db_sync script immediately after postgresql starts up.
this is done so that the primary has a fresh copy of the database in the event that it was down long enough that the secondary copy has substantially changed. for the secondary, it provides the chance to get anything that is new before it takes over. doing this makes the periodic updates less critical, since that version of the database is only going to be used if the opposite system is unreachable.

configure heartbeat to manage postgresql from the haresources file, e.g.
dbserver.mydomain.com 192.168.1.110 postgresql
and restart heartbeat.
now postgresql connections to dbserver.mydomain.com will go to castor if its up and to pollux if its not.

this setup provides basic high availability for postgresql. it cannot handle problems that will happen to connections that are active during the failover; these will just fail. one should be particularly aware of this kind of problem when doing transactions with a commit (i.e. the transaction is set up, a failover happens, and then the commit is executed).