#
#PostgreSQL Synchronous Replication on Ubuntu 12.04
#
1.Download postgres version postgresql-9.2.2.tar.gz from http://www.postgresql.org/ftp/source/v9.2.2/
2.Install dependencies on both servers[master-192.168.0.168,slave-192.168.0.169]
apt-get install postgresql-client-common postgresql-client-9.1 gcc libreadline-dev zlib1g-dev
3.Configure and install postgres on both servers
tar -xvzf postgresql-9.2.2.tar.gz
$./configure --prefix=/opt/postgres
$make
$make install
4.Add user postgres on both master and slave server
5.On Master server initilize database as user "postgres"
NOTE:-On both servers ownership of "/opt/postgres" should be assign to user "postgres"
[drwxr-xr-x 6 postgres root 4096 Jan 15 15:19 /opt/postgres]
$su - postgres
$cd /opt/postgres;
$./bin/initdb -D master
$vim master/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
$vim master/pg_hba.conf
host all postgres 192.168.0.169/32 trust
host replication postgres 192.168.0.169/32 trust
#Now start the master and see for any errors
$./bin/postgres -D master
6.On slave server
$su - postgres
$cd /opt/postgres
$./bin/initdb -D slave
$vim slave/postgresql.conf
hot_standby = on
$ vim slave/recovery.conf
standby_mode = on
primary_conninfo = 'host=192.168.0.168 application_name=slave1'
#Sync master data with slave
On slave server
$cd /opt/postgres
$rsync -av --exclude pg_xlog --exclude *.conf postgres@192.168.0.168:/opt/postgres/master/* slave/
7.Enabling Synchronous Replaction
#On master
$vim master/postgresql.conf
synchronous_standby_names = 'slave1'
8.Then start master and slave servers
#On master
./bin/postgres -D slave/
LOG: database system was shut down in recovery at 2013-01-16 11:27:01 IST
LOG: entering standby mode
LOG: WAL file is from different database system
DETAIL: WAL file database system identifier is 5833920183356866431, pg_control database system identifier is 5833917520477060503.
LOG: streaming replication successfully connected to primary
LOG: consistent recovery state reached at 0/16D4DB8
LOG: database system is ready to accept read only connections
#On slave
./bin/postgres -D slave/
LOG: database system was shut down in recovery at 2013-01-16 11:31:19 IST
LOG: entering standby mode
LOG: consistent recovery state reached at 0/16D4DB8
LOG: record with zero length at 0/16D4DB8
LOG: database system is ready to accept read only connections
LOG: streaming replication successfully connected to primary
LOG: redo starts at 0/16D4DB8
9.Verify sync sttus
On Master
$ su - postgres
$ psql -h localhost
postgres=# SELECT usename, application_name, client_addr, client_hostname, sync_state FROM pg_stat_replication;
usename | application_name | client_addr | client_hostname | sync_state
----------+------------------+---------------+-----------------+------------
postgres | slave1 | 192.168.0.169 | | sync
REF-http://bradmontgomery.net/blog/streaming-replication-in-postgresql-91/
