Debian Administration

How to setup Postgresql 9.1 Streaming Replication Debian Squeeze

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:

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.

Add Comment Printable version

 

<<< Isolating sudo messages from syslog Virtual IP addresses with ucarp, for high-availability >>>