Streaming replication in postgresql 9.1

Following document will tell you how to steup streaming replication for postgresql server 9.1.

We are using it for failover and high avilability of database for rails application.

Note : If you need an installation procedure for postgresql server 9.1, please refer this link. 

A. Set up primary ( master ) server as per postgresql server installation document

1. Open postgres.conf from data folder and made changes as per this,

listen_addresses = '10.50.4.91'  # IP address        

wal_level = hot_standby  # Option are ( host_standby, warm standby etc...)
#Need to study the other option and their implications

max_wal_senders = 5

wal_keep_segments = 32
# Need to explore
# Follwing commands are used earlier than 9.1 where replication works via xlog and    
# archive-restore command.
#archive_mode    = on
#archive_command = 'cp %p /path_to/archive/%f'

2. Open pg_hba.conf and add authentication for self machine and for stand by machine

host    all             all             10.50.4.91/22           trust
host    replication     postgres        10.50.4.58/22           trust
Note : Replication signifies the slave will replicate for all DB on master

B. Set up stand-by machine(for e.g here 10.50.4.58) as per postgres installation document till step 10.1 (Don't do 10.2 as we are copying data folder from primary)

C. On Primary log in as postgres
Start the postgresql server and then do the following

psql -Upostgres -h10.50.4.91 -c "SELECT pg_start_backup('label', true)"
rsync -a /usr/local/pgsql/data/ 10.50.4.58:/usr/local/pgsql/data/ --exclude postmaster.pid
psql -Upostgres -h10.50.4.91 -c "SELECT pg_stop_backup()"

D. Step 3 will create a data folder inside /usr/local/pgsql

on standby
1. Edit postgresql.conf of standby
a. Change listen_address as per standby
listen_addresses = '10.50.4.58'

b. comment out the rest of part in postgresql.conf (which we have done on primary server)

E. Enable read-only queries on the standby server. But if wal_level is archive on the primary, leave hot_standby unchanged (i.e., off).

hot_standby = on  #In postgres.conf
   
F. Edit nano /usr/local/pgsql/data/pg_hba.conf for your connection if required

for general setting

modify last two line(as we copied from primary)

host    all             all             10.50.4.58/22           trust
#host    replication     postgres        10.50.4.58/22           trust   

G. Add /usr/local/pgsql/data/recovery.conf on standby with following content

standby_mode          = 'on'
primary_conninfo      = 'host=10.50.4.91 port=5432 user=postgres'
trigger_file = '/tmp/trigger'   #Optional but need to be explore
#restore_command = 'cp /path_to/archive/%f "%p"' #Optional but need to be explore

H. Start the postgresql server of standby

Troubleshoot and verification:
You can see postgresql log for any issue or to see wether streaming replication is working or not.

Reference:
http://wiki.postgresql.org/wiki/Streaming_Replication

Primary Observations:
1. Even those standby goes down it will pickup a changes from primary at the time of startup.
2. You can not do a any modification on standby.
3. On restart any machine aromatically postgresql services get loaded

Important reference:
    http://wiki.postgresql.org/wiki/Main_Page
    Go to section Database Administration and Maintenance