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:
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
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.confprimary_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 startroot@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;"
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.