Project

General

Profile

Support #698

Setup PostgreSQL Master/Slave Replication

Added by Daniel Curtis over 8 years ago. Updated almost 7 years ago.

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

50%

Estimated time:
2.00 h
Spent time:

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