« Jython and Django Trial | Main | Cast the results of a subquery as an array then join as a string »

November 26, 2011

Server replication in Postgres 9.1

Postgres slave server in five minutes

Files: Postgres_9.1_replication_standby_files.tar.gz

Binary Replication in 6 Steps

This 6-step guide, and all of the examples in this tutorial, assume that you have a master server at 192.168.0.1 and a standby server at 192.168.0.2 and that your database and its configuration files are installed at /var/lib/postgresql/data. Replace those with whatever your actual server addresses and directories are.

1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings: 

 listen_address = '*'
 wal_level = hot_standby
 max_wal_senders = 3

2. Edit pg_hba.conf on the master in order to let the standby connect. 

 host  replication   all   192.168.0.2/32      trust

3. Edit recovery.conf and postgresql.conf on the standby to start up replication and hot standby. First, in postgresql.conf, change this line: 

 hot_standby = on

Then create a file in the standby's data directory (which is often the same directory as postgresql.conf and pg_hba.conf, except on some Linux distributions such as Debian and Ubuntu), called recovery.conf, with the following lines: 

 standby_mode = 'on'
 primary_conninfo = 'host=192.168.0.1'

4. Shutdown the master and copy the files. You want to copy most but not all files between the two servers, excluding the configuration files and the pg_xlog directory. 

tar cvzhf /Users/paulhollands/Desktop/data.tgz --exclude 'pg_xlog' --exclude 'postgresql.conf' data/

5. Start the standby first, so that they can't get out of sync.

6. Start the master.

Tags: PostgreSQL

Posted by pj at November 26, 2011 12:10 PM

Comments