How to Install and Configure PostgreSQL Replication Set

Hello everyone, welcome to the PostgreSQL Tutorial. In this article, I am going to install and configure a PostgreSQL Replication set from two different servers. My goal is to have a continuous replication between my master PostgreSQL Server with the slave. So, it will sync in real-time. For more information about the PostgreSQL Replication features, please visit the official page. Please note that this tutorial will only enable the streaming or real-time replication feature. Next time, I will write how to create a High Availability PostgreSQL servers.

In this article, I have two PostgreSQL servers installed on two different Ubuntu servers. One act as a master server and the other one is replica server.

• Master server IP address: 192.168.100.50
• Replica server IP address: 192.168.100.250

Steps to Install and Configure PostgreSQL Replication

Step 1. Set the Hostname

This step is not mandatory but for ease of use, I would set the hostname of the two Ubuntu servers so it is easier to remember and refer. Connect to the server via SSH and then do the following.

On the Master Server

sudo hostnamectl set-hostname master-server

Then, edit the hosts file

sudo nano /etc/hosts

Add then I add the following line

192.168.1.50     master-server

On the Replica Server

sudo hostnamectl set-hostname replica-server

Same as above, add this line to the /etc/hosts file in the backup-server

192.168.100.250   replica-server

Done.

Step 2. Install PostgreSQL Server on both servers

On both servers, let’s install PostgreSQL server.

sudo apt update
sudo apt-get install postgresql postgresql-client postgresql-contrib

I think, it’s pretty straight-forward. Make sure there are no errors thrown during this installation process.

Set the postgres user password with this command

sudo passwd postgres

Step 3. Configure Master Server

Configure the Master Server

This section needs to be done on the Master server. We will configure the replication for the PostgreSQL Server. Log in to the Master server, in my case would be 192.168.100.50.

su - postgres
psql

Now let’s create a new replica user. You can use any name you want.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'YourPasswordHere';

Now let’s check

\du

Edit postgresql.conf

By default, PostgreSQL only listen to localhost (127.0.0.1). For the replication to work, we need to edit this so the PostgreSQL server will allow connection from the network. We need to edit the listen_addresses parameter

sudo nano /etc/postgresql/12/main/postgresql.conf

Find the following line and change it. 192.168.100.50 is my Master server IP address. Change it with your master server ip address.

listen_addresses = '192.168.100.50'

Edit pg_hba.conf

sudo nano /etc/postgresql/12/main/pg_hba.conf

Add this line to the end of the file

host replication replica 192.168.100.250/24 md5

As you can see, the IP address here is the replica server IP address.

Step 4. Synchronize Data from Master Server to Replica Server

The following needs to be done on the Replica server. The default data directory for PostgreSQL server in Ubuntu is in /var/lib/postgresql/12/main/. Now we need to remove the content of this folder as postgres user.

su - postgres
rm -r /var/lib/postgresql/12/main/*

Next, execute this command to perform physical backup of the master server data file (as postgres user).

pg_basebackup -h 192.168.100.50 -p 5432 -U replica -D /var/lib/postgresql/12/main/ -Fp -Xs -R

Now exit from postgres user and then restart the PostgreSQL service

sudo systemctl restart postgresql@12-main

Step 5. Check and Verify the Replication Clusters

From the Master server, run psql

su - postgres
psql

and then run this command

SELECT client_addr, state FROM pg_stat_replication;

Output

If you see the similar output, then you have successfully created the PostgreSQL replication cluster.

Now try to create a new database on the Master server.

su - postgres
psql
CREATE DATABASE manjarodb;
\list

Now let’s check the Replica server

su - postgres
psql
\list

Conclusion

Creating the PostgreSQL Replication on Ubuntu is very simple and easy. It is very convenient to have a replication server. Please let us know what you think on the comment below.