PostgreSQL 9.1 Master/Slave streaming replication

Streaming Replication is one of the most significant features that made the PostgreSQL flexible and scalable. And today we will talk about.

First of all I expect you have same environment:

• Ubuntu 12.10 or later 
• PostgreSQL 9.1 Two servers, suppose: 192.168.1.110/192.168.1.155 
• PostgreSQL 9.1 is placed default to /var/lib/postgresql/9.1/ 

Let's make first server as Master (192.168.1.110) and Slave (192.168.1.155)

1.Configuring listen and pg_hba.conf.
Open the config file postgresql.conf and set follow line:

listen_addresses = '192.168.1.110' (on Master) 
listen_addresses = '192.168.1.155' (on Slave) 

In pg_hba.conf on Master, add follow line:
host replication postgres 192.168.1.155/32 trust

2. Modifying all required options for replication on the Master. 

#Setting replication to read-only mode for slave node. Instead "hot_standby" mode you can put "archive" mode then slave node will solely archiving data. 
wal_level = hot_standby 

#Maximum amount of slave nodes 
max_wal_senders = 2 

#How many parts of logs we want keep. If you have a high loaded records. 
wal_keep_segments = 32 

#For data duplication we can set follow options(not required). 
archive_mode = on 
archive_command = 'cp %p /var/lib/postgresql/9.1/main/archive/%f' 

After you should restart the database.

3. Sending the database from Master to Slave. 

You have to shutdown the database on Slave node before configuring the slave.
$ psql -c "SELECT pg_start_backup('label', true)" 
$ rsync -a /var/lib/postgresql/9.1/main/ slave:/var/lib/postgresql/9.1/main/ --exclude postmaster.pid 
$ psql -c "SELECT pg_stop_backup()" 

4. Turning on hot_standby on the slave node

Add this line in postgresql.conf:
hot_standby = on 

5. Create a replication config file on the slave.

Add these lines in recovery.conf(you should create this file at /var/lib/postgresql/9.1/main): standby_mode = 'on' 
primary_conninfo = 'host=192.168.1.110 port=5432 user=postgres' 
trigger_file = '/var/lib/postgresql/9.1/main/trigger'
restore_command = 'cp /var/lib/postgresql/9.1/main/archive/%f "%p"'

6. Done!

Running Slave. For checking if started it, launch this command:
$ ps  aux | grep receiver 
--------------------------- 
postgres 1953 0.0 0.0 101980 4156 ? Ss 19:19 0:00 postgres: wal receiver process streaming 2/B40001D0 

If you see this line, so you are lucky guy :)

7. Monitoring
Unfortunately, I don't know utilities for scanning replication status, but you can check it writing follow command on both nodes. 

psql -c "SELECT pg_current_xlog_location()" -h192.168.1.110 
-------------------------- 
0/2000000 (1 row) 

psql -c "select pg_last_xlog_replay_location()" -h192.168.1.155 
pg_last_xlog_replay_location 
------------------------------ 
0/2000000 (1 row) 

If you notice same results, then last replication was success.

8. If you faced to some trouble on Master you can recover all data from slave.

• Create a trigger file on the slave node (described in 5th section). Slave will be opened to record,  hence stopped replication act. And you can led clients to Slave node. 
• After fixing Master node, we can do same action(5th section) for recover the master. Don't forget remove trigger file after all actions.