How to setup Postgresql 9.1 Streaming Replication Debian Squeeze

Posted by Jevad on Tue 10 Apr 2012 at 13:07

This is a short HOWTO on setting up a backported Postgresql 9.1 database with streaming replication on Debian Squeeze.

To get started you'll clearly need two servers, each running Debian Squeeze and with a back-ported Postgresql 9.1 package installed upon each of them.

In this example we're going to be dealing with two hosts:

Master	192.168.0.1
Slave	192.168.0.2

The Master Setup

The following are the minimum settings to get the master host ready for streaming replication:
root@master:~# nano -w /etc/postgresql/9.1/main/postgresql.conf
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
listen_addresses = '*'

#wal_level = minimal			# minimal, archive, or hot_standby
wal_level = hot_standby

#max_wal_senders = 0
max_wal_senders = 3
Once you've made those changes you'll also need to create a replication user:
root@master:~# psql -h localhost -U postgres -W -c "CREATE USER ruser WITH REPLICATION PASSWORD 'password';"
After the replication user has been created you'll need to allow it to connect:
root@master:~# nano -w /etc/postgresql/9.1/main/pg_hba.conf
#rep
host    replication     ruser        192.168.0.2/32          md5
Now stop the service:
root@master:~# /etc/init.d/postgresql stop

Slave Configuration

Again we'll be editing the configuration file:
root@slave:~# nano -w /etc/postgresql/9.1/main/postgresql.conf
You want to make similar changes to allow the service to listen on an externally-visible IP address, rather than on the loopback interface.
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
listen_addresses = '*'

#hot_standby = off
hot_standby = on
Now on the slave STOP and clean up in advance of getting the replication running NOTE this will clear all data on the slave:
#/etc/init.d/postgresql stop
#cd /var/lib/postgresql/9.1/main/
#rm -rf *
once the data has gone we need to make more tweaks:
root@slave:~# nano -w /var/lib/postgresql/9.1/main/recovery.conf
primary_conninfo = 'host=192.168.0.1 port=5432 user=ruser password=password'
standby_mode = on

Getting it working

Copy all of your data from the master to the slave host. You could do that by running this on the master:
root@master:~# rsync -av /var/lib/postgresql/9.1/main/* 192.168.0.2:/var/lib/postgresql/9.1/main/
On both the master and the slave you can now start the service:
root@slave:~# /etc/init.d/postgresql start
root@master:~# /etc/init.d/postgresql start
With both hosts running the service you can now check on the status of the replication :
root@master:~# psql -h localhost -U postgres -W -c "select * from pg_stat_replication;"

Notes

Since you've switched from running the database on the loopback adapter to running on externally visible IP addresses you should consider firewalling access appropriately.

This article can be found online at the Debian Administration website at the following bookmarkable URL (along with associated comments):

http://www.debian-administration.org/article/677/How_to_setup_Postgresql_9.1_Streaming_Replication_Debian_Squeeze