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 diagram
Terminology
- Master: existing server receiving write queries
- Upstream slave: existing slave setup with a basic PostgreSQL replication
- Downstream slave: new server we are installing below the current slave
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.6/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.6/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.6/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.
If you plan to have more than one downstream slave, you need to update the PostgreSQL configuration as well:
Edit the configuration file:
root@upstream_slave:~# emacs /etc/postgresql/9.6/main/postgresql.conf
Search and edit the following in the configuration:
max_wal_senders = 1 # Adjust the number to match the number of downstream slaves
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.6/main /var/lib/postgresql/9.6/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.6/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.6/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.6/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.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 ('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=#