PostgreSQL Master-Slave Database Replication

Introduction

The master-slave database replication is a process of copying (syncing) data from a database on one server (the master) to a database on another server (the slaves) and distributing the read/write request load as per the system requirements. Where Master is the read/write server and slaves are read-only servers.

The master-slave replication in PostgreSQL is used to improve the reliability and scalability of database systems. The main purposes are:

1. High Availability: By having a slave database, it can take over in case the master database fails.
2. Load Balancing: The read requests can be sent to the slave database to reduce the load on the master database.
3. Data Backup: The slave database can be used as a backup in case the master database is lost or corrupted.
4. Data Analysis: The slave database can be used to run analytics or reports without affecting the performance of the master database.

Overall, the master-slave replication in PostgreSQL helps to ensure that the data is available, reliable, and scalable for a variety of use cases.

Architecture

Name	    IP Address	    Role
pg-master	192.168.1.1	    Master
pg-slave	192.168.1.2	    Slave

Setup

Install PostgreSQL

We will install PostgreSQL on both the master and slave servers. For this, I’m using PostgreSQL v16.3on Ubuntu v22.04. Follow the same installation methods as listed below for both systems (master and slave).

sudo apt update; sudo apt upgrade -y
sudo apt install nano wget net-tools -y
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt install postgresql postgresql-contrib -y

Configuring Master

Create User

create a database user “replication” who will carry out the task of replication

sudo -u postgres psql

This will start psql program where you can create new user.

CREATE USER replication WITH password 'blackIntstack' REPLICATION;

Checked the List of roles

\du

Ouput:

                              List of roles
  Role name  |                         Attributes                         
-------------+------------------------------------------------------------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS
 replication | Replication                                               +
             | 1 connection

Change in postgresql & pg_hba conf files

Now this is a stand alone database. As we want to make it master of a database cluster, we need to change some configuration. Append following line at the end of /etc/postgresql/16/main/postgresql.conf file

sudo cat << 'EOF' >> /etc/postgresql/16/main/postgresql.conf
listen_addresses = '192.168.1.1'                # Master-IP
wal_level = replica                             # minimal, replica, or logical (change requires restart)
max_wal_senders = 10                            # max number of walsender processes (change requires restart)
max_replication_slots = 10                      # max number of replication slots (change requires restart)
# For more details about parameters - https://www.postgresql.org/docs/16/runtime-config-replication.html
EOF

⚠️ replace '192.168.1.1' with your Master server ip

Slave server need authentication for replication. Now append following line to /etc/postgresql/16/main/pg_hba.conffile.

sudo cat << 'EOF' >> /etc/postgresql/16/main/pg_hba.conf
host    replication     replication     192.168.1.2/0                 md5
EOF

⚠️ replace '192.168.1.2' with your Slave server ip

Restart and Check Status Of PostgreSQL 

Restart the PostgreSQL service and verify that it has started.

sudo service postgresql restart
sudo service postgresql status

We have completed the master server configuration, and our master server is now ready for replication.

Configuring Slave

Stop the PostgreSQL service

First, we must stop the PostgreSQL service on the slave server.

sudo service postgresql stop
sudo service postgresql status

Change in postgresql & pg_hba conf files

Append following line at the end of /etc/postgresql/16/main/postgresql.conf file. 

sudo cat << 'EOF' >> /etc/postgresql/16/main/postgresql.conf
listen_addresses = '192.168.1.2'             # Slave-IP
wal_level = replica                             # minimal, replica, or logical
max_wal_senders = 10                            # max number of walsender processes
max_replication_slots = 10                      # max number of replication slots
hot_standby = on                # "off" disallows queries during recovery
# For more details about parameters - https://www.postgresql.org/docs/16/runtime-config-replication.html
EOF

⚠️ replace '192.168.1.2' with your Slave server ip

Now append following line to /etc/postgresql/16/main/pg_hba.conf file

sudo cat << 'EOF' >> /etc/postgresql/16/main/pg_hba.conf
host    replication     replication     192.168.100.75/0                 md5
EOF

⚠️ replace '192.168.1.1' with your master ip

We need to remove all files and folders of PostgreSQL data directory [/var/lib/postgresql/16/]on slave node.

cd /var/lib/postgresql/16/main

if above directory is not empty run below commands.

cd /var/lib/postgresql/16/
mv main main_backup
mkdir main
chmod 700 main
chown postgres: main

Copy Data from the Master

Now, run the following command to copy all data from the master database to the slave database.

sudo su postgres
pg_basebackup -h 192.168.1.1 -U replication -p 5432 -D /var/lib/postgresql/16/main/  -Fp -Xs -P -R

⚠️ replace '192.168.1.1' with your master ip. it will ask for password authentication [replication user password ]

Verify the Data

Now for the confirmation to check whether Initial Data Replication has been set up properly or not check the file created in the /etc/postgresql/16/main/.

make sure the following changes have been configured in the main folder:

• standby.signal has been created which indicated that this is the Replica (Slave) Server.
• Check the content of postgresql.auto.conf file, It should be configured as below

primary_conninfo = 'user=replication password=blackIntstack channel_binding=prefer host=192.168.1.1 port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

Start  and check the status of PostgreSQL 

sudo service postgresql start
sudo service postgresql status

Test the Server Setup

On Master:

In your master server run the below command to check the connection of the Slave server.

sudo -u postgres psql
\x  # expended display on
SELECT * FROM pg_stat_replication;

Output:

-[ RECORD 1 ]----+------------------------------
pid              | 7516
usesysid         | 16388
usename          | replication
application_name | 16/main
client_addr      | 192.168.1.2
client_hostname  |
client_port      | 46328
backend_start    | 2024-05-21 20:06:01.519454+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/4037908
write_lsn        | 0/4037908
flush_lsn        | 0/4037908
replay_lsn       | 0/4037908
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2024-05-21 20:15:36.196801+00

On Slave:

In Your slave server run the below command to check the connection of the Master Server.

sudo -u postgres psql
\x  # expended display on
SELECT * FROM pg_stat_wal_receiver;

Output:

-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 7555
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
written_lsn           | 0/40010C8
flushed_lsn           | 0/40010C8
received_tli          | 1
last_msg_send_time    | 2024-05-21 20:07:37.574739+00
last_msg_receipt_time | 2024-05-21 20:07:37.559436+00
latest_end_lsn        | 0/40010C8
latest_end_time       | 2024-05-21 20:07:37.574739+00
slot_name             |
sender_host           | 192.168.1.1
sender_port           | 5432
conninfo              | user=replication password=******** channel_binding=prefer dbname=replication host=192.168.1.1 port=5432 fallback_application_name=16/main sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable

Now If you will perform any CREATE/UPDATE/DELETE query in the Master Server, you will find instant replication of the same query output in the  Slave Server too.

On Master:

CREATE TABLE customers (
 id SERIAL PRIMARY KEY,
 name VARCHAR(50) NOT NULL,
 email VARCHAR(100) UNIQUE
);

INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO customers (name, email) VALUES ('Jane Smith', 'jane.smith@example.com');

SELECT * FROM customers;

On Slave:

SELECT * FROM customers;

Rebuild Slave

Stop the PostgreSQL service on the slave

sudo systemctl stop postgresql
sudo systemctl status postgresql

Clear the data directory on the slave

sudo rm -rf /var/lib/postgresql/16/main/*

Perform the base backup again from the master

sudo su postgres
pg_basebackup -h 192.168.1.1 -U replication -p 5432 -D /var/lib/postgresql/16/main/  -Fp -Xs -P -R

⚠️ replace '192.168.1.1' with your master ip. it will ask for password authentication [replication user password ]

Check the content of postgresql.auto.conf file, It should be configured as below

primary_conninfo = 'user=replication password=blackIntstack channel_binding=prefer host=192.168.1.1 port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

Start the PostgreSQL service

sudo systemctl start postgresql
sudo systemctl status postgresql

On Master:

Insert data into any table from master node

INSERT INTO customers (name, email) VALUES ('arick eidelman', 'arickeidelman@example.com');

On Slave:

sudo -u postgres psql
SELECT * FROM customers;

Promote Slave to Master

To check if hot standby serves read-only queries

On Master:

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"        # It should return false(f).

On Slave:

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"        # It should return true (t).

Output:

pg_is_in_recovery
-------------------
 t
(1 row)

Now lets promote the slave to master

sudo -u postgres pg_ctlcluster 16 main promote

verify it 

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"        # It should return false(f).

Output:

pg_is_in_recovery
-------------------
 f
(1 row)