How to configure cascading replication ?

Cascading Replication Steps

Streaming slave – Slave1

Cascading slave – Slave2

Streaming Replication from primary to slave1 [Steps 1- 11]

Cascading Replication from slave1 to slave2 [Steps 12- 18]

1. Modify the below parameters on primary server postgresql.conf:

archive_command= 'cp %p /home/postgres/arch/%f' [Example]

wal_level=hot_standby

max_wal_senders = 5

wal_keep_segments = 32

2. Take an online backup of primary server

select pg_start_backup('rep');

3. Create a new directory on slave1[Eg: /test/streaming] and copy

the data directory of primary to slave1 server.

Eg: cp /opt/PostgreSQL/9.2/data slave1:/test/streaming

4. After the copy is complete, stop the online backup on primary.

select pg_stop_backup();

5. Copy the required wal's from primary to slave1.

Eg: cp /home/postgres/arch/[required wals]* slave1:/home/

6. Create a recovery.conf file on slave1 server data directory:

standby_mode = 'on'

primary_conninfo = 'host=[IP address of primary] port=5432

user=postgres'

trigger_file = '/tmp/standby.trg'

restore_command = 'cp /home/postgres/arch/%f %p'

7. Modify the below parameters on slave1 postgresql.conf:

8. Add the primary server entry in pg_hba.conf of slave1:

For Eg: host replication all [IP address of primary]/32 trust

9. Write a command so that wals are being copied continuously from

Eg: rsync –a -z primary:/home/postgres/arch slave1:/home/

10. Remove the postmaster.pid on slave1 data directory.

Eg: In location /test/streaming

rm –rf postmaster.pid

11.Now, start the slave1 server and verify whether it is up and

running and verify the logs

/opt/PostgreSQL/9.2/bin/pg_ctl -D /test/streaming [data

12. Cascading replication from slave1 to slave2

Create a new directory on slave1[Eg: /test/cascading] and copy the

data directory of slave1 to slave2 and the required wals.

Eg: cp /test/streaming slave2:/test/cascading [data directory copy]

cp /home/postgres/arch/[required wals]* slave1:/home/postgres/arch [wals copy]

13. Modify the max_wal_senders = 3 in slave1 postgresql.conf for cascading replication.

- max_wal_senders value in slave1[streaming slave] should be lesser than the value of the one in primary.

14. On slave2 server add a recovery.conf file in data directory:

standby_mode = 'on'

primary_conninfo = 'host=[IP address of slave1] port=5432

user=postgres'

trigger_file = '/tmp/standby.trg'

restore_command = 'cp /home/postgres/arch/%f %p'

15. Add the slave1 server entry in pg_hba.conf of slave2:

For Eg: host replication all [IP address of slave1]/32 trust

16. Make sure that wals are being copied continuously from slave1 to

Eg: rsync -a –z slave1:/home/postgres/arch slave2:/home/

17. Remove the postmaster.pid on slave2 data directory.

Eg: In location /test/cascading

rm –rf postmaster.pid

18. Now, start the slave2 cluster.

/opt/PostgreSQL/9.2/bin /pg_ctl -D /test/cascading[data directory of slave2]

Note: - Make sure that there is a passwordless authentication from primary to slave1 and from slave1 to slave2 for the wal's to be copied. [or else save the wals in a common mount point where primary, slave1 and slave2 can also access].