Support #698: Setup PostgreSQL Master/Slave Replication - GNU/Linux Administration - ALT Project Management

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

Table of contents
Prepare Master Server
Prepare Slave Server
Start the Replication
- Master Server
- Slave Server
Resources

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

NOTE: The example servers will be

pg1.example.com as the master at 192.168.0.10
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