Introduction

Storing data in one place is usually a risky thing to do. It is usually recommended that you have a plan in place to ensure you recover from a disaster as soon as it strikes. One good way to ensure an effective disaster recovery plan is to have the same data on multiple servers and to keep them synchronized. The database servers usually work together for one to take over in case the primary/master server fails hence creating high availability. Several database servers may also serve the same data at the same time, this is referred to as Load Balancing. In this guide, you’ll learn how to configure a master-slave replication where one database server will have read-write permissions granted to it while the second one will have read only permissions granted to it.

Before you start

2 Ubuntu 18.04 Virtual Private Servers (VPS’s) or Dedicated Servers with a nonroot user having sudo privileges. One will act as a master while the other as a standby/slave server.
PostgreSQL installed and configured on both servers. You can learn How To Install PostgreSQL Database Server On Ubuntu 18.04 here

Steps

You first need to change the listen address on PostgreSQL configuration page

. The server needs to be able to listen to all ports.

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

Special Note: The postgresql version might be a different one and hence your path might differ from the one above.

Search listen_addresses word on your editor or set numbers and scroll down to line 59. Uncomment the line and change the localhost name to * which enables the server listen to all IP addresses. It needs to look as the following excerpt.

...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
...

Close your editor while saving changes and reload the postgresql service to accommodate the changes made

$ sudo systemctl restart postgresql

Create a Sample Database

For replication test purposes, create a sample table called characters. Log into your postgres database

$ sudo -i -u postgres psql postgres

# CREATETABLEcharacters(fname text, character_id serial, datetimestamp, description text);

Now insert sample data in your newly created table.

# INSERTINTOcharacters (fname, date, description) VALUES ( 'Griffin', current_date, 'This is a sample character' );

INSERT01

Primary Server Configuration

PostgreSQL requires a role/user with special permissions for replication purposes.

$ sudo -u postgres createuser -U postgres replicationuser -P -c 5 --replication

Special Note: You can learn about the createuser command options here. Also make sure in a production environment, the replicationuser password is STRONG to avoid incidents such as bruteforcing or easy password guessing.

You then need to create an archive directory which will be a sub-directory of your cluster’s default main directory.

$ sudo mkdir -p /var/lib/postgresql/9.5/main/mnt/server/archivedir

Configure the pg_hba.conf file

$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf

Search the replication section, then add the following entry on replication user to look as follows. Remember to replace your slave server IP address.

# Allow replication connections
host     replication             replicationuser                192.168.56.20/24               md5

Exit while saving changes. Edit the postgresql.conffile

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

Edit the following settings. Remember to uncomment each setting that you edit.

listen_addresses= '*'
wal_level = hot_standby
archive_mode= on
Archive_commend = 'test ! -f mnt/server/archivedir/%f && cp %p mnt/server/archivedir/%f'
max_wal_senders=3

Exit while saving changes and restart the postgresql service

$ sudo systemctl restart postgresql

Slave server configuration

First, stop the postgresql service on your slave server.

$ sudo systemctl stop postgresql

Rename the main in the postgresql directory to something else since the backup won’t replace the existing files in the same folder.

$ sudo mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main_old

Run the pg_basebackup utility. Replace the ip with your master server ip address.

$ sudo -u postgres pg_basebackup -h 192.168.56.20 -D /var/lib/postgresql/9.5/main -U replicationuser -v -P --xlog-method=stream

Enter the replicationuser password that you set on your master server when prompted. Edit the postgresql.conf file

$ sudo vim /etc/postgresql/9.5/main/postgresql.conf

Search for the hot_standby setting option and change it to on. Exit while saving changes. You now need to provide the recovery settings which will be used in the event of data recovery.

$ sudo cp -avr /usr/share/postgresql/9.5/recovery.conf.sample /var/lib/postgresql/9.5/main/recovery.conf

Edit the recovery file

$ sudo vim /var/lib/postgresql/9.5/main/recovery.conf

Search for the standby_mode and change the option to on to enable the server to stay on standby mode. Search for the primary_conninfo and add the master server details

primary_conninfo= 'host=192.168.56.20 port=5432 user=replicationuser password=.Breotmas@12P12sr%'

Exit while saving changes. Start the postgresql service on your slave server

$ sudo systemctl start postgresql

Test-Replication

Go to your master server in the table you created. Add a new entry.

$ sudo -u postgres psql postgres

# INSERTINTOcharacter(fname, date, description) VALUES ( 'Sanchez', current_date, 'This is a family guy character');
INSERT01

Go back to your slave server in the Postgres database and view the data

$ sudo -u postgres psql postgres

# SELECT * FROMcharacter

 fname  | character_id |        date         |        description         
---------+--------------+---------------------+----------------------------
 Griffin |            1 | 2018-10-22 00:00:00 | This is a sample character
 Sanchez |            2 | 2018-10-2200:00:00 | A Rick and Morty character
(2 rows)

Conclusion

If you have been able to view the data on your slave server after inserting it in your master server, it means you have successfully set up your PostgreSQL master-slave replication. There are two ways to invoke a failover when the master server has suddenly stopped working; you can either use the pg_ctl command or you may set a trigger file location in your slave server’s /etc/postgresql/9.5/main/postgresql.conf file. In the file, simply search and uncomment the trigger_file = ‘/tmp/postgresql.trigger.5432’line.