PostgreSQL Synchronous Replication on Ubuntu 12.04

#
#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/