How To Setup Binary Replication Between 2 PostgreSQL 9.4 Hosts (Hot-Standby)


Reliable Hardware – Trained Staff
You are here:  Service / Technical Blog
By    -   Head of Technical Projects

Utilising a master/slave (hot-standby) setup to provide a resilience layer at database level can be easy. The following assumes you have 2 PgSQL hosts at 10.10.50.1 and 10.10.50.2, both running Ubuntu 14.04 LTS and PostgreSQL 9.4 (9.4.5).

  • 1. On the master 10.10.50.1, edit the following in postgresql.conf:

    listen_addresses = '*'
    wal_level = hot_standby
    max_wal_senders = 3

    listen_addresses can also be scoped down to single or multiple server bound IP addresses, for added security/best practice

    wal_level defines what type of data, and how much of it is written to/stored in the Write Ahead Log. Setting to hot_standby tells PgSQL to write all the data that would have been written with “archive” mode, plus the data needed to reconstruct the status of running transactions.

    max_wal_senders defines the number of process to use (max) to send replication data to the slave. This can be fine-tuned for your DB load and network capacity.


  • 2. On the master, 10.10.50.1, edit the following in pg_hba.conf:

    host	replication		all		10.10.50.2/32		trust

    This entry allows the slave to communicate back to the master, but only for replication based tasks.

  • 3. On the slave, 10.10.50.2, edit the following in postgresql.conf:

    hot_standby = on

  • 4. On the slave, 10.10.50.2, create a new configuration file named “recovery.conf” and add the following:

    standby_mode = 'on'
    primary_conninfo = 'host=10.10.50.1'

  • 5. We now need to sync the DB data from the master to the slave, so they can begin at the same point. Your mileage may vary with this, but the rsync command that would work in this scenario is the following, note the excludes, these are important, don’t sync those:

    rsync -av -e "ssh -p 22" --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.4/main/* root@10.10.50.2:/var/lib/postgresql/9.4/main/

  • 6. Once the sync has completed, start the Slave DB, once up, start the Master DB. Replication will now be in effect.


Got Something To Say:

Your email address will not be published. Required fields are marked *

*


Authors

Categories

Recent Posts

Review Us

Are you a VooServers customer? We would love to hear what you say!

Review us
Back to top

© VooServers Ltd 2016 - All Rights Reserved
Company No. 05598165