Installing PostgreSQL Standby Server

Assumptions:

1. password less authentication is enabled between Primary and Standby database servers.
2. rsync is installed on both servers
3. vhosts file (i.e. /etc/vhosts) contain entry for ‘standby’ server
4. pg_standby script is installed onto standby server
5. This path exists on 'standby' server i.e. /backup/psql_wal. And postgres user has permissions to read/write
6. PostgreSQL 8.3.8 is installed on both primary and standby servers.

Primary Database Setup:

--Edit postgresql.config file. Set the following parameters

archive_command = '/usr/bin/rsync -a %p standby:/backup/psql_wal/%f </dev/null'
archive_timeout = 120

--Restart postgres to apply chnages:

su postgres
pg_ctl -D /data/pgdata restart

--Start hot backup:
echo "SELECT pg_start_backup('mybackup');" | psql -U postgres

--Backup data directory using tar

tar -cvf /tmp/backup.tar /data/pgdata

--Copy backup onto standby server:

rsync /tmp/backup.tar standby:/backup

--Stop hotbackup, clear the flag
echo "SELECT pg_stop_backup();" | psql -U postgres

Standby Database Setup:

--Stop postgres:
su postgres
pg_ctl -D /data/pgdata stop

--Restore the backup:
rm -fR /data/pgdata/*
tar -xvf /tmp/backup.tar /data/pgdata
rm -fR /data/pgdata/pg_xlog/*
mkdir /data/pgdata/pg_xlog/archive_dir

--Create recovery file: /data/pgdata/recovery.conf
--add following line:
restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5432 /backup/psql_wal/ %f %p %r 2>>standby.log'

--Correct permissions:
chown -R postgres:postgres /data/pgdata

--Start postgres on standby server:
su postgres
pg_ctl -D /data/pgdata -l /data/pgdata/pg.log start

Monitor Standby Server:
tail –f /data/pgdata/standby.log