• 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
      • PostgreSQL Cascaded Replication – POA Document

      PostgreSQL Cascaded Replication – POA Document

      • Posted by Nijamutheen J
      • Categories Blog
      • Date November 10, 2018
      • Comments 0 comment

      Prepared by
      PostgreSQL Cascade Replication

      Document Summary Information:

       Service Provider rayafeel.com
      Customer(s) XXXX
      Date Created 26st MAR 2018
      Prepared By vinith
      Reviewed By
      Sign Off Authority Prem Anand

      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

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      How to connect the postgresql database automatically without password prompt ?
      November 10, 2018

      Next post

      Installing PHP Extensions and Applications Package "Archive_Zip"
      November 11, 2018

      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?