Share this page: Follow me to be notified of new articles:

Easy replication setup with PostgreSQL

Introduction

Replicating a PostgreSQL server to a slave is an easy process with PostgreSQL. The documentation below explains how to create a new database slave from an existing server (the master).
Replication
Basic replication diagram

Install PostgreSQL on the slave

If the slave does not have PostgreSQL installed yet, here are the steps to take to install it.

Create SSH keys for "postgres" user

On both servers, run the following command to generate SSH keys:
# On the master
root@master:~# sudo -H -u postgres ssh-keygen

# On the slave
root@slave:~# sudo -H -u postgres ssh-keygen
Validate all the steps. A key will be generated on each server and can be viewed with:
# On the master
root@master:~# cat /var/lib/postgresql/.ssh/authorized_keys

# On the slave
root@slave:~# cat /var/lib/postgresql/.ssh/authorized_keys
Run the following command on the master and the slave:
# On the master
root@master:~# echo "echo \""`cat /var/lib/postgresql/.ssh/id_rsa.pub`"\" >> /var/lib/postgresql/.ssh/authorized_keys"
echo "ssh-rsa AAABBBCCCDDDEEE[....] postgres@master" >> /var/lib/postgresql/.ssh/authorized_keys

# On the slave
root@slave:~# echo "echo \""`cat /var/lib/postgresql/.ssh/id_rsa.pub`"\" >> /var/lib/postgresql/.ssh/authorized_keys"
echo "ssh-rsa AAABBBCCCDDDEEE[....] postgres@slave" >> /var/lib/postgresql/.ssh/authorized_keys
This will output a command. Run the command outputted on the master on the slave and the one outputted on the slave on the master. This will exchange the public keys.

Configure the master

Connect to psql:
gab@master:~# sudo bash
root@master:~# su postgres
postgres@master:~# psql
And create a replication user with a strong password:
postgres=# CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'my_secret_password';
CREATE ROLE
Then edit the authentication configuration file:
root@master:~# emacs /etc/postgresql/9.6/main/pg_hba.conf
Add the following line at the end (don't forget to replace [SLAVE_ID] with the slave IP address):
host     replication     replication     [SLAVE_IP]/32          md5
Save and exit.

Edit the general configuration file:
root@master:~# emacs /etc/postgresql/9.6/main/postgresql.conf
Search and edit the following in the configuration:
listen_addresses = 'localhost,[IP_ADDRESS_OF_MASTER_ON_LAN]'              # what IP address(es) to listen on; 
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
Save and exit and restart PostgreSQL:
root@master:~# service postgresql restart
You can ensure that the database restarted properly with a quick look into its log:
root@master:~# tail /var/log/postgresql/postgresql-9.6-main.log

Configure the slave

Stop PostgreSQL on the slave:
root@slave:~# service postgresql stop
Then edit the authentication configuration file:
root@slave:~# emacs /etc/postgresql/9.6/main/pg_hba.conf
Add the following line at the end (don't forget to replace [MASTER_IP] with the master IP address):
host     replication     replication     [MASTER_IP]/32          md5
Edit the general configuration file:
root@slave:~# emacs /etc/postgresql/9.6/main/postgresql.conf
Search and edit the following in the configuration:
listen_addresses = 'localhost,[IP_ADDRESS_OF_SLAVE_ON_LAN]'              # what IP address(es) to listen on; 
max_connections = 100 # Ensure that this value is the same as the master's
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

On the slave: take a master base backup

Move the current PostgreSQL cluster to a backup folder in order to replace it with a base backup from the master:
root@slave:~# mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main_old
Run this command on the slave to take a base backup from the master:
root@slave:~# sudo -u postgres pg_basebackup -h [MASTER_IP] -D /var/lib/postgresql/9.6/main -U replication -P -v
Password: # Type the replication password defined earlier on the master
28811/28811 kB (100%), 1/1 tablespace                                         
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
pg_basebackup: base backup completed

On the slave: configure a recovery file

Create the following file:
root@slave:~# emacs /var/lib/postgresql/9.6/main/recovery.conf
Add the following lines (make sure to replace [MASTER_IP] with the master's IP address and the password with the replication password defined earlier):
standby_mode = 'on'
primary_conninfo = 'host=[MASTER_IP] port=5432 user=replication password=my_secret_password'
trigger_file = '/tmp/postgresql.trigger.5432'
Ensure that the file permissions are correct:
root@slave:~# chown postgres.postgres /var/lib/postgresql/9.6/main/recovery.conf
Start the slave:
root@slave:~# service postgresql start
You can ensure that the slave started properly with a quick look into its log:
root@slave:~# tail /var/log/postgresql/postgresql-9.6-main.log

Test the replication

Insert a row in a dummy table on the master:
big_law_db=# INSERT INTO big_law_clients (name, date) VALUES ('Gab', NOW());
INSERT 0 1
big_law_db=# 
Ensure that the row was replicated on the slave:
big_law_db=# SELECT * FROM big_law_clients;
 id |     name      |             date              
----+---------------+-------------------------------
  1 | Coca-Cola     | 2016-06-25 10:17:01.955688-04
  2 | Larry Ellison | 2016-06-25 10:17:12.069001-04
  3 | Gab           | 2016-06-25 13:49:07.521762-04
(3 rows)

big_law_db=# 
Last update of this page: July 19, 2017
Please do not copy any of the content of this website without asking me first.