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.
Leave a Reply