• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • How to add extra one slave without any downtime in postgresql ?

      How to add extra one slave without any downtime in postgresql ?

      • Posted by 2ndnijam
      • Categories Blog
      • Date February 3, 2019
      • Comments 0 comment

      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)

      • Share:
      2ndnijam

      Previous post

      How do I fix a PostgreSQL 9.3 Slave that Cannot Keep Up with the Master?
      February 3, 2019

      Next post

      How to Configure the cascade replication On PostgreSQL ?
      February 3, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?