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).

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/id_rsa.pub

# On the slave
root@slave:~# cat /var/lib/postgresql/.ssh/id_rsa.pub

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 5 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_IP] 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 = 5
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 = 5
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=#