xploit29

Requirements

• Ubuntu 16.04 or Ubuntu 18.04
• PostgreSQL 10
• 2 servers (a master and a slave)

In case you don’t know how to install PostgreSQL you can follow this tutorial from DigitalOcean.

Setup

It’s highly recommended to use two servers that can communicate via on a private interface.

• Master server: 192.168.123.10
• Slave server: 192.168.123.11

Master server configuration

Create a role dedicated to the replication, you can freely choose the username and password that you like.

su - postgres psql

CREATE ROLE replication WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE replication WITH REPLICATION;

Modify the /etc/postgresql/10/main/postgresql.conf

listen_addresses = 'localhost, 127.0.0.1, 192.168.123.10'
wal_level = replica
max_wal_senders = 3 # max number of walsender processes
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables

Modify the /etc/postgresql/10/main/pg_hba.conf file to allow access from the Slave server.

host    replication    replicate    192.168.123.11/24    md5

Restart PostgreSQL.

service postgresql restart

Slave server configuration

Modify the /etc/postgresql/10/main/postgresql.conf

listen_addresses = 'localhost, 127.0.0.1, 192.168.123.11'
wal_level = replica
max_wal_senders = 3 # max number of walsender processes
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
hot_standby = on

Modify the /etc/postgresql/10/main/pg_hba.conf file to allow access from the Slave server.

host    replication    replicate    192.168.123.10/24    md5

Stop PostgreSQL

service postgresql stop

Delete all the files inside the PGDATA folder. Keep in mind that this folder may change, you may wanna check your postgresql.conf file and lookup for the data_directory setting.

rm -rf /var/lib/postgresql/10/main/*

Now we will copy all the data from the master with the pg_basebackup command. You must run this command as the postgresql user.

su - postgresql
pg_basebackup -h 192.168.123.10 -D /var/lib/postgresql/10/main/ -P -U replicate --wal-method=stream

You can press CTRL + D to leave the postgresql user and create a file named recovery.conf on your PGDATA folder

nano /var/lib/postgresql/10/main/recovery.conf

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.123.10 port=5432 user=replicate password=password'
trigger_file = '/tmp/MasterNow'
#restore_command = 'cp /home/postgresql_wal/%f "%p"'

Here is an explanation for each line:

• standby_mode=on: specifies that the server must start as a standby server
• primary_conninfo: the parameters to use to connect to the master
• trigger_file: if this file exists, the server will stop the replication and act as a master
• restore_command: this command is only needed if you have used the archive_command on the master

Start PostgreSQL.

service postgresql start

Finally

You can see the replicate user on the Master server.

su - postgresql psql

SELECT * FROM pg_stat_activity WHERE usename = 'replicate';

Your database is ready.