Project

General

Profile

Support #698

Setup PostgreSQL Master/Slave Replication

Added by Daniel Curtis about 9 years ago. Updated over 7 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Daniel Curtis
Category:
Database Server
Target version:
Debian
Start date:
11/22/2015
Due date:
% Done:

50%

Estimated time:
2.00 h
Spent time:
4.00 h

Description

This is a guide for setting up master-slave replication with PostgreSQL 9.4 on Debian 8.

NOTE: The example servers will be
  1. pg1.example.com as the master at 192.168.0.10
  2. pg2.example.com as the slave at 192.168.0.20

Prepare Master Server

  • Enable the contrib apt repositories:
    sudo sed -i '' -e 's/main/main\ contrib/' /etc/apt/sources.list
  • Make sure the system is up to date:
    sudo apt-get update && sudo apt-get upgrade
  • Install postgresql:
    sudo apt-get install postgresql postgresql-contrib postgresql-client rsync
  • Set a password for the postgresql admin user:
    sudo passwd postgres
  • Switch to Postgres account:
    sudo su - postgres
  • Generate ssh key:
    ssh-keygen
  • Copy SSH key to opposite server:
    ssh-copy-id pg2.example.com

    NOTE: Make sure to see the postgres password on pg2.example.com before copying the SSH id
  • Create an account for replication
    psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'SuperSecretReplicationPassword';"
  • Exit from the postgres user:
    exit
  • Modify postgres access config file:
    sudo nano /etc/postgresql/9.4/main/pg_hba.conf
    • Add content below:
      host    replication     replicator     192.168.0.20/32   md5
  • Then modify postgres config file:
    sudo nano /etc/postgresql/9.4/main/postgresql.conf
    • The config value should be:
      listen_addresses = 'localhost,192.168.0.10'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
  • Restart Master server:
    sudo service postgresql restart

Prepare Slave Server

  • Enable the contrib apt repositories:
    sudo sed -i '' -e 's/main/main\ contrib/' /etc/apt/sources.list
  • Make sure the system is up to date:
    sudo apt-get update && sudo apt-get upgrade
  • Install postgresql:
    sudo apt-get install postgresql postgresql-contrib postgresql-client rsync
  • Set a password for the postgresql admin user:
    sudo passwd postgres
  • Switch to Postgres account:
    sudo su - postgres
  • Generate ssh key:
    ssh-keygen
  • Copy SSH key to opposite server:
    ssh-copy-id pg1.example.com

    NOTE: Make sure to see the postgres password on pg1.example.com before copying the SSH id
  • Create an account for replication
    psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'SuperSecretReplicationPassword';"
  • Exit from the postgres user:
    exit
  • Stop PostgreSQL:
    sudo service postgresql stop
  • Modify postgres access config file:
    sudo nano /etc/postgresql/9.4/main/pg_hba.conf
    • Add content below:
      host    replication     rep     192.168.0.10/32  md5
  • And modify postgres config file:
    sudo nano /etc/postgresql/9.4/main/postgresql.conf
    • The config value should be:
      listen_addresses = 'localhost,192.168.0.20'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

Start the Replication

Master Server

  • Begin the replication
    sudo su - postgres

psql -c "select pg_start_backup('initial_backup');" 

rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.4/main/ -e ssh pg2.example.com:/var/lib/postgresql/9.4/main/

psql -c "select pg_stop_backup();" 

exit

Slave Server

  • Create recovery.conf file:
    sudo nano /var/lib/postgresql/9.4/main/recovery.conf
    • And add the content below to that file:
      standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=replicator password=SuperSecretReplicationPassword'
trigger_file = '/tmp/postgresql.trigger.5432'
  • And restart Slave Server to complete the setup:
    sudo service postgresql start
  • Check the postgresql log file:
    tail -n 20 /var/log/postgresql/postgresql-9.4-main.log
    • Example output:
      2015-11-21 14:47:31 PST [3237-1] LOG:  started streaming WAL from primary at 0/19000000 on timeline 1
2015-11-21 14:47:31 PST [3238-1] [unknown]@[unknown] LOG:  incomplete startup packet
2015-11-21 14:47:32 PST [3241-1] postgres@postgres FATAL:  the database system is starting up
2015-11-21 14:47:32 PST [3236-3] LOG:  redo starts at 0/19000028
2015-11-21 14:47:32 PST [3236-4] LOG:  consistent recovery state reached at 0/19000128
2015-11-21 14:47:32 PST [3235-1] LOG:  database system is ready to accept read only connections

Resources

Also available in: Atom PDF