Friday, August 16, 2013

Postgresql 9.2 Streaming Replication Setup

0 comments
Node 1 : 10.0.1.46
Node 2: 10.0.1.56

i) Prepare Node 1:
 
1. Check postgresqls running with the following parameters:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = on

2. Modify pg_hba.conf for replication

host all replicator 10.0.1.56/24 trust
host replication replicator 10.0.1.56/24 trust


3. Create required user for replication

CREATE USER replicator WITH PASSWORD 'replicator' REPLICATION LOGIN;

4. Reload pg_hba

pg_ctl reload

 
ii) Prepare Node 2:

1. Install Postgresql 9.2 at Node2

2. Modfiy postgresql.conf for replication

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = on

3. Modify pg_hba.conf for replication

host all replicator 10.0.1.46/24 trust
host replication replicator 10.0.1.46/24 trust

4. Restart database

pg_ctl -mf restart

5. Create script to sync with other node and act as slave

vi sync_with_master.sh
#! /bin/sh
MASTER=10.0.1.46
MASTER_PGPORT=5432
MASTER_PGDATA=/var/lib/pgsql/9.2/data
MASTER_PGENGINE=/usr/pgsql-9.2/bin
SLAVE=10.0.0.37
SLAVE_PGDATA=$MASTER_PGDATA
SLAVE_PGPORT=$MASTER_PGPORT
SLAVE_PGENGINE=$MASTER_PGENGINE
REPL_USER=repl
$SLAVE_PGENGINE/pg_ctl stop -D $SLAVE_PGDATA -mf
psql -h $MASTER -p $MASTER_PGPORT -U $REPL_USER -d postgres -c "select pg_start_backup('replication_backup')"
rsync -azv "$MASTER":"$MASTER_PGDATA"/* "$SLAVE_PGDATA"/ --exclude="postmaster.pid" --exclude="*.conf*"
psql -h $MASTER -p $MASTER_PGPORT -U $REPL_USER -d postgres -c "select pg_stop_backup()"
echo "standby_mode = 'on' # enables stand-by (readonly) mode" > $SLAVE_PGDATA/recovery.conf
echo "primary_conninfo = 'host= $MASTER port= $MASTER_PGPORT user= $REPL_USER'" >> $SLAVE_PGDATA/recovery.conf
echo "trigger_file = '/tmp/pg_failover_trigger'" >> $SLAVE_PGDATA/recovery.conf
$SLAVE_PGENGINE/pg_ctl start -D $SLAVE_PGDATA -w

iii) Ensure password less ssh access for "postgres" user between the nodes

iv) Make Node 2 slave:

 sh sync_with_master.sh

v) Check replication:

At master:

SELECT pg_xlog_location_diff(pg_current_xlog_location(), '0/0') AS offset;

At Slave:

SELECT pg_xlog_location_diff(pg_last_xlog_receive_location(), '0/0') AS receive, pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/0') AS replay;

If we get the same number in above two queries then we can assume that the replication is working.

Even, we may use "CREATE DATABASE db1" at Master and see if we get it at Slave or not.

v) Make Node 2 Master:

touch /tmp/pg_failover_trigger

At this stage, Node 2 starts act as Master while Node 1 is still alive as a stand-alone server.
Modify some data in Node 2. Now we can make Node 1 as a slave for Node 2.


Leave a Reply

Labels