Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to add extra one slave an existing PostgreSQL cascade replication without down time ?

In this Tutorial i will expained how to add one extra slave on existing PostgreSQL replication  without any downtime
if you want to know more about cascade replication just go through my previous blog post 

How to Configure the cascade replication On PostgreSQL 10.3 ?


HOSTNAME  :      Slave3                           
 IP                :    192.168.2.5

Step 1:put the 3rd slave information on slave_2's pg_hba.conf if not here recovery.conf file just create it now
cat recovery.conf
standby_mode='on'
primary_conninfo = 'host=192.168.2.3 port=5432 user=postgres password=postgres'

#restore_command = 'cp /archive/%f "%p"'
#archive_cleanup_command = '/usr/pgsql-9.4/bin/pg_archivecleanup /archive %r'
recovery_target_timeline = 'latest'
Pg_hba.conf:
host    replication     postgres            192.168.2.5/32  trust
host    replication     postgres            192.168.2.4/32  trust
Step 2. reload Or restart the slave_2 server:
Option 1: From the command-line shell
su - postgres
/usr/bin/pg_ctl -D /DATA reload

Option 2: Using SQL
SELECT pg_reload_conf();
Step 3:Pause the slave_2 server then check whether paused or not
select pg_wal_replay_pause();
select pg_is_wal_replay_paused();
Note:if your Server is testing ,after paused the slave_2 just create some table on master becouse after resume you need to check the slave_2 & slave_3 whether Data is  replicated or not 

Step 4:Take the physical backup on slave_2 and send it to slave_3:
ON SLAVE_3:
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ stop  -----stop the slave_3 cluster
rm -vfr /var/lib/pgsql/10/data/*      ---remove the old data's
FROM SLAVE_2:
rsync -cva --inplace /DATA/* postgres@192.168.2.5:/var/lib/pgsql/10/data/
slave_3 pg_hba.conf:
host    replication     postgres            192.168.2.4/32  trust
Slave_3 postgresql.conf:
listen_addresses = 'localhost,192.168.2.5'
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 .'
slave_3 recovery.conf:
cat /var/lib/pgsql/10/data/recovery.conf
standby_mode = 'on'      # to enable the standby (read-only) mode.
primary_conninfo = 'host=192.168.2.4 port=5432 user=postgres password=postgres'
                         # to specify a connection info to the master node.
Step 5:Resume slave_2 server then restart the slave_3 server:
At slave_2:
select pg_wal_replay_resume();
At Slave_3:
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ start
MONITOR THE SLAVE_3 AND MASTER SERVER :
ON SLAVE_3:
postgres=# select * from pg_stat_wal_receiver ;
  pid  |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |       last_msg_send_time       |      last_msg_receipt_time       | latest_end_lsn |         latest_end_time          | slot_name |                                                                                             conninfo                                                                                             
-------+-----------+-------------------+-------------------+--------------+--------------+--------------------------------+----------------------------------+----------------+----------------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 39970 | streaming | 0/1A000000        |                 1 | 0/1A000000   |            1 | 2018-03-15 16:39:03.4787+05:30 | 2018-03-15 16:39:01.426584+05:30 | 0/1A30B500     | 2018-03-15 16:37:03.161287+05:30 |           | user=postgres password=******** dbname=replication host=192.168.2.4 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
(1 row)
ON MASTER:
select * from pg_stat_replication ;
  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |          backend_start           | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
-------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 14640 |       10 | postgres | walreceiver      | 192.168.2.3 |                 |       57716 | 2018-03-15 09:33:48.617764+05:30 |              | streaming | 0/1A30B500 | 0/1A30B500 | 0/1A30B500 | 0/1A30B500 |           |           |            |             0 | async
(1 row)

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger