Lesson 4 - PostgreSQL 16 Replication Setup

What is PostgreSQL Replication

PostgreSQL is a flexible open-source relational database management system, known for its performance and robustness. Setting up replication between a primary (master) and a secondary (slave) server is an important step in ensuring high availability and data redundancy in a PostgreSQL environment. In this guide, we will walk you through the process of installing PostgreSQL on the secondary server and configuring replication to replicate data from the primary server.

Installation Architecture

Environment Preparation

Before we start, we need to prepare 3 servers

Installing PostgreSQL 16

Install and secure PostgreSQL 16 on Ubuntu 22.04 on 3 servers postgresql-master, postgresql-slave-01, and postgresql-slave-02.

Check the PostgreSQL version

psql --version

Configuring PostgreSQL 16 Replication

Step 1: Configure PostgreSQL Master

First, we will configure the master server to allow data replication from the master server to the slave server. To start, open the PostgreSQL configuration file on the master server with your favorite text editor. In this guide, we will use the nano editor.

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

Find and edit the following lines:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

max_connections = 1000

• wal_level is the level of the WAL (Write-Ahead Log) records that the master server will generate. This level needs to be set to replica to allow the master server to send WAL records to the slave server.

• max_replication_slots is the maximum number of replication slots that the master server can create. Replication slots are a mechanism to keep copies of WAL records for the slave server. This level needs to be set to 10 to allow the master server to create up to 10 replication slots.

• max_connections is the maximum number of client connections that the master server can accept. This level needs to be set to 1000 to allow the master server to accept up to 1000 client connections.

After editing, save and exit the configuration file.

Next, we will configure the pg_hba.conf file to allow the slave server to connect to the master server and receive replication data. Open the pg_hba.conf file with your favorite text editor on the master server.

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

Add the following lines to the end of the file:

host    replication    replicator              192.168.56.12/32         md5
host    replication    replicator              192.168.56.13/32         md5

After editing, save and exit the configuration file.

Next, we will create a user with the right to replicate data from the postgresql-master server to the postgresql-slave server. First, log in to the master server and open the psql console.

sudo -u postgres psql
CREATE USER replicator

 WITH

 REPLICATION LOGIN ENCRYPTED PASSWORD '71e1b930e5d53ea4c8f02ccfd255d7cc';

71e1b930e5d53ea4c8f02ccfd255d7cc is the strong password you want to use for the replicator user. Here I set it to 71e1b930e5d53ea4c8f02ccfd255d7cc, please use md5 to create a stronger password.

After creating, exit the psql console.

Restart the PostgreSQL service

sudo systemctl restart postgresql

Step 2: Configure PostgreSQL Slave

Next, we will configure the slave server to receive data from the master server. Open the PostgreSQL configuration file on the slave server with your favorite text editor.

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

Find and edit the following lines:

listen_addresses = '*'
hot_standby = on

After editing, save and exit the configuration file.

2.1 : Stop PostgreSQL service

sudo systemctl stop postgresql

2.2 : Delete current PostgreSQL data

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

2.2 : Backup data from the master server to the slave server

On postgresql-slave-01, run the following command:

export PGPASSWORD=71e1b930e5d53ea4c8f02ccfd255d7cc

pg_basebackup -h 192.168.56.11 -U replicator -R -X stream -C -S replica_1 -v -R -W -D/var/lib/postgresql/16/main -w

# Set permission
chown -R postgres:postgres /var/lib/postgresql/16/main

postgresql-slave-01 enter the following command:

export PGPASSWORD=71e1b930e5d53ea4c8f02ccfd255d7cc

pg_basebackup -h 192.168.56.11 -U replicator -R -X stream -C -S replica_2 -v -R -W -D/var/lib/postgresql/16/main -w

# Set permission
chown -R postgres:postgres /var/lib/postgresql/16/main

2.3 : Configure recovery.conf file on postgresql-slave-01 and postgresql-slave-02

sudo nano /var/lib/postgresql/16/main/recovery.conf

Add the following content to the recovery.conf file:

standby_mode = 'on'
primary_conninfo = 'host=192.168.56.11 port=5432 user=replicator password=71e1b930e5d53ea4c8f02ccfd255d7cc'

After editing, save and exit the configuration file.

71e1b930e5d53ea4c8f02ccfd255d7cc is the password you created in step 1. 192.168.56.11 is the IP address of the postgresql-master machine.

2.4 : Start PostgreSQL service

sudo systemctl start postgresql

Check PostgreSQL Replication

Step 1: Check the status of PostgreSQL Master

First, log in to the postgresql-master server and open the psql console.

sudo -u postgres psql

Check the status of the postgresql-master server:

SELECT client_addr, state
FROM pg_stat_replication;

Thus, we have successfully configured 2 postgresql-slave-01 and postgresql-slave-02 machines to receive data from the postgresql-master machine.

Step 2: Check the status of PostgreSQL Slave

Log in to the postgresql-slave server and open the psql console.

sudo -u postgres psql

Check the status of the postgresql-slave server:

SELECT status, receive_start_lsn, sender_host  FROM pg_stat_wal_receiver;

Check data replication setup

To check whether data replication is working or not, add some data to the main server and check if the data is copied to the secondary server.

Step 1: Add data to the postgresql-master server

sudo -u postgres psql

CREATE DATABASE test;

\c test

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));

INSERT INTO users (name) VALUES ('DUY TRAN - AKITECT.IO');

Step 2: Check data on the postgresql-slave server

sudo -u postgres psql

\c test

SELECT * FROM users;

Thus, we have successfully configured PostgreSQL 16 Replication between the postgresql-master server and postgresql-slave-01, postgresql-slave-02.

Conclusion

In this guide, we have shown you how to install and configure PostgreSQL 16 Replication between the main server and the secondary server. By configuring data replication, you can clone data from the main server to the secondary server, helping to distribute data, ensure the latest data, and support replacing the main server. We hope this guide has helped you understand how to set up PostgreSQL 16 Replication. If you have any questions, please leave a comment below. We are happy to help you. Thank you for reading!