Prepared by
PostgreSQL Cascade Replication

Document Summary Information:

Service Provider	2ndQuadrant.in
Customer(s)	XXXX
Date Created	26st MAR 2018
Prepared By	vinith
Reviewed By
Sign Off Authority	Nijamutheen J

Cascading Replication:
The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay. This can be used to reduce the number of direct connections to the master and also to minimize inter-site bandwidth overheads.

Terminology

• Master: existing server receiving write queries
• Upstream slave: existing slave setup with a basic PostgreSQL replication
• Downstream slave: new server we are installing below the current slave

1. Edit pg_hba.conf to enable replication connections.
2. Edit postgresql.conf to turn on the various replication options.
3. Restart the master.
4. Set up passwordless access to the master from the first replica and second replica.
5. Use rsync backup and send it to slave server
6. Create a recovery.conf pointing at the master.
7. Edit second slave recovery.conf to point at the first replica.
8. Start the first replica then.
9. Start the second replica.

At master:
Step 1: Create one replication user on master server

psql -c "CREATE USER rep REPLICATION LOGIN ENCRYPTED PASSWORD 'redhat';"
psql -c "CREATE USER rep REPLICATION LOGIN ENCRYPTED PASSWORD 'redhat';"

Step 2: change the PostgreSQL Replication parameter as per requirments:
AT master postgresql.conf:

listen_addresses = 'localhost, 172.30.2.187'
port = 5432
wal_level = 'hot_standby' # minimal, archive, hot_standby, or logical
max_wal_senders = 5 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
#hot_standby = on #only for slave
archive_mode = on # allows archiving to be done
archive_command = 'rsync -av %p /mnt/archive/%f && rsync -av %p postgres@192.168.2.3:/ARCHIVE/%f' # or 'cd .'

Step 3: At master pg_haba.conf file put the slave_1 and slave_2 information :
master pg_hba.conf:

host replication postgres 172.30.2.187/32 trust

Step 4: Restart the master server

$ /usr/pgsql-10/bin/pg_ctl -D /mnt/DATA/ restart -m fast

Step 5: Stop the both Slave server:
At Slave_1:

$ /usr/pgsql-10/bin/pg_ctl -D /DATA stop

At Slave_2:

/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/9.4/data stop

Step 6: Remove or move the Existing data directories of slave_1 & slave_2 server :
At Slave_1:

rm -rf /var/lib/pgsql/10/data/*

At Slave_2:

rm -rf /var/lib/pgsql/10/data/*

Step 7: Take the master server as backup mode and send the master’s physical backup from master to slave_1 and slave_2 :
At master:

psql -c "select pg_start_backup('initial_backup');"
$ rsync -cva --inplace  /mnt/DATA/* postgres@172.30.2.205:/DATA/
$ rsync -cva --inplace  /mnt/DATA/* postgres@172.18.2.190:/DATA/
psql -c "select pg_stop_backup();"

AT SLAVE_1
Step 8: Now open postgresql.conf in Slave_1, and make below changes
AT Slave_1 postgresql.conf:

listen_addresses = 'localhost, 172.30.2.205'
port = 5432
wal_level = 'hot_standby' # minimal, archive, hot_standby, or logical
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
hot_standby = on
archive_mode = on # allows archiving to be done
archive_command = 'rsync -av %p /archive/%f && rsync -av %p postgres@172.18.2.190:/archive/%f' # or 'cd .'

Step 9: Open pg_hba.conf and make below changes At Slave_1:

host replication postgres 172.30.2.205/32 trust
host replication postgres 172.18.2.190/32 trust

Step 10: And finally create a recovery.conf file under data folder. recovery.conf should be like this:

$ cat /DATA/recovery.conf
standby_mode = 'on' # to enable the standby (read-only) mode.
primary_conninfo = 'host=172.30.2.187port=5432 user=postgres password=postgres'
# to specify a connection info to the master node.
recovery_target_timeline = 'latest'
trigger_file = '/tmp/pg_failover_trigger'
# to specify a trigger file to recognize a fail over.
restore_command = 'cp /archive/%f "%p"'
archive_cleanup_command = '/usr/pgsql-9.4/bin/pg_archivecleanup /archive %r'

AT SLAVE_2
Step 11. Now open postgresql.conf in Slave_2, and make below changes
AT Slave_2 postgresql.conf:

listen_addresses = 'localhost,172.18.2.190
port = 5432
wal_level = 'hot_standby' # minimal, archive, hot_standby, or logical
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
hot_standby = on
archive_mode = on # allows archiving to be done
archive_command = 'rsync -av %p /archive/%f' # or 'cd .'

Step 12. recovery.conf at Slave_2:

cat recovery.conf
standby_mode='on'
primary_conninfo = 'host=172.30.2.187port=5432 user=rep password=redhat'

restore_command = 'cp /archive/%f "%p"'
archive_cleanup_command = '/usr/pgsql-9.4/bin/pg_archivecleanup /archive %r'
recovery_target_timeline = 'latest'

Step 13. Open pg_hba.conf and make below changes At Slave_2:

host replication postgres 172.30.2.187/32 trust

step 14. start the master(already started no need t start),slave_1 & slave_2 using pg_ctl
MASTER:

$ /usr/pgsql-10/bin/pg_ctl -D /mnt/DATA/ start

SLAVE_1:

$ /usr/pgsql-10/bin/pg_ctl -D /DATA/ start

SLAVE_2:

/usr/pgsql-10/bin/pg_ctl -D /DATA/ start

Download Our Sample plan of action Document

Cascaded Replication 2ndQuadrant.in