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

Setup a cascading PostgreSQL replication in a few easy steps

Introduction

Setting up a cascading replication means to create a downstream slave from an existing master-slave setup.
If you want to create a regular PostgreSQL replication (one master, one slave), check out our dedicated article.
Cascading replication
Cascading replication diagram

Terminology

Create a "postgres" SSH key on the downstream slave and exchange SSH keys with the upstream slave

On the downstream slave, run the following command to generate an SSH key for "postgres" user (Validate all the steps with no input):
root@downstream_slave:~# sudo -H -u postgres ssh-keygen
Run the following command on the upstream slave and the downstream slave:
# On the upstream slave
root@upstream_slave:~# echo "echo \""`cat /var/lib/postgresql/.ssh/id_rsa.pub`"\" >> /var/lib/postgresql/.ssh/authorized_keys"
echo "ssh-rsa AAABBBCCCDDDEEE[....] postgres@upstream_slave" >> /var/lib/postgresql/.ssh/authorized_keys

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

Configure the downstream slave

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

On the upstream slave: authorize downstream slave

Edit the authentication configuration file:
root@upstream_slave:~# emacs /etc/postgresql/9.5/main/pg_hba.conf
Add the following line at the end (don't forget to replace [DOWNSTREAM_SLAVE_IP] with the downstream slave IP address):
host     replication     replication     [DOWNSTREAM_SLAVE_IP]/32          md5 # Downstream slave
There should already be a line for the master.
Save and exit and reload PostgreSQL:
root@upstream_slave:~# service postgresql reload
root@upstream_slave:~#

On the downstream slave: take an upstream slave base backup

Move the current PostgreSQL cluster to a backup folder in order to replace it with a base backup from the master:
root@downstream_slave:~# mv /var/lib/postgresql/9.5/main /var/lib/postgresql/9.5/main_old
Run this command on the slave to take a base backup from the master:
root@downstream_slave:~# sudo -u postgres pg_basebackup -h [UPSTREAM_SLAVE_IP] -D /var/lib/postgresql/9.5/main -U replication -P -v
Password: # Type the replication password currently used between the master and the upstream slave
28813/28813 kB (100%), 1/1 tablespace                                         
pg_basebackup: base backup completed

On the downstream slave: configure a recovery file

Edit the following file:
root@downstream_slave:~# emacs /var/lib/postgresql/9.5/main/recovery.conf
It should contain the upstream slave recovery configuration. Replace it with the following (make sure to replace [UPSTREAM_SLAVE_IP] with the upstream slave IP address and the password with the replication password):
standby_mode = 'on'
primary_conninfo = 'host=[UPSTREAM_SLAVE_IP] port=5432 user=replication password=my_secret_password'
recovery_target_timeline = 'latest' # see http://www.postgresql.org/docs/current/static/warm-standby.html#CASCADING-REPLICATION
trigger_file = '/tmp/postgresql.trigger.5432'
Ensure that the file permissions are correct:
root@downstream_slave:~# chown postgres.postgres /var/lib/postgresql/9.5/main/recovery.conf
Start the downstream slave:
downstream_slave@slave:~# service postgresql start
You can ensure that the downstream slave started properly with a quick look into its log:
root@downstream_slave:~# tail /var/log/postgresql/postgresql-9.5-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 ('Barack Obama', NOW());
INSERT 0 1
big_law_db=# 
Ensure that the row was replicated on the upstream 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
  4 | Barack Obama  | 2016-06-25 14:20:46.956013-04
(4 rows)
big_law_db=# 
And on the downstream slave as well:
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
  4 | Barack Obama  | 2016-06-25 14:20:46.956013-04
(4 rows)
big_law_db=# 
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.