Easy replication setup with PostgreSQL 12

Changes from older versions

• recovery.conf is not used anymore to setup the replication
• standby_mode parameter has been replaced with 2 files: standby.signal and recovery.signal

Create SSH keys for "postgres" user

On both servers, run the following command to generate SSH keys:

# On the primary
root@primary:~# sudo -H -u postgres ssh-keygen

# On the replica
root@replica:~# 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 primary
root@primary:~# cat /var/lib/postgresql/.ssh/id_rsa.pub

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

Run the following command on the primary and the replica:

# On the primary
root@primary:~# echo "echo \""`cat /var/lib/postgresql/.ssh/id_rsa.pub`"\" >> /var/lib/postgresql/.ssh/authorized_keys"
echo "ssh-rsa AAABBBCCCDDDEEE[....] postgres@primary" >> /var/lib/postgresql/.ssh/authorized_keys

# On the replica
root@replica:~# echo "echo \""`cat /var/lib/postgresql/.ssh/id_rsa.pub`"\" >> /var/lib/postgresql/.ssh/authorized_keys"
echo "ssh-rsa AAABBBCCCDDDEEE[....] postgres@replica" >> /var/lib/postgresql/.ssh/authorized_keys

This will output a command. Run the command outputted on the primary on the replica and the one outputted on the replica on the primary. This will exchange the public keys.

Configure the primary

Connect to psql:

gab@primary:~# sudo bash
root@primary:~# su postgres
postgres@primary:~# 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@primary:~# emacs /etc/postgresql/12/main/pg_hba.conf

Add the following line at the end (don't forget to replace [REPLIACA_IP] with the replica IP address):

host    replication     replication     [REPLIACA_IP]/32          md5

Save and exit.

Edit the general configuration file:

root@primary:~# emacs /etc/postgresql/12/main/postgresql.conf

Search and edit the following in the configuration:

listen_addresses = 'localhost,[IP_ADDRESS_OF_PRIMARY_ON_LAN]'              # what IP address(es) to listen on; 
wal_level = 'replica'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 5
primary_conninfo = 'host=[REPLICA_IP] port=5432 user=replication password=[REPLICATION PASSWORD]'
hot_standby = on

Save and exit and restart PostgreSQL:

root@primary:~# service postgresql restart

You can ensure that the database restarted properly with a quick look into its log:

root@primary:~# tail /var/log/postgresql/postgresql-12-main.log

Configure the replica

Stop PostgreSQL on the replica:

root@replica:~# service postgresql stop

Then edit the authentication configuration file:

root@replica:~# emacs /etc/postgresql/12/main/pg_hba.conf

Add the following line at the end (don't forget to replace [≈] with the primary IP address):

host    replication     replication     [PRIMARY_IP]/32          md5

Edit the general configuration file:

root@replica:~# emacs /etc/postgresql/12/main/postgresql.conf

Search and edit the following in the configuration:

listen_addresses = 'localhost,[IP_ADDRESS_OF_REPLIACA_ON_LAN]'              # what IP address(es) to listen on; 
max_connections = 100 # Ensure that this value is the same as the primary's
wal_level = 'replica'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 5
primary_conninfo = 'host=[PRIMARY_IP] port=5432 user=replication password=[REPLICATION PASSWORD]'
hot_standby = on

On the replica: take a primary base backup

Move the current PostgreSQL cluster to a backup folder in order to replace it with a base backup from the primary:

root@replica:~# su postgres
postgres@replica:~# mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main_old

Run this command on the replica to take a base backup from the primary:

root@replica:~# sudo -u postgres pg_basebackup -h [PRIMARY_IP] -D /var/lib/postgresql/12/main -U replication -P -v
# Password: # Type the replication password defined earlier on the primary
# 28811/28811 kB (100%), 1/1 tablespace                                         
# NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
# pg_basebackup: base backup completed

Start the replication

Create the standby.signal file:

touch /var/lib/postgresql/12/main/standby.signal

Start the replica:

root@replica:~# service postgresql start

You can ensure that the replica started properly with a quick look into its log:

root@replica:~# tail /var/log/postgresql/postgresql-12-main.log