Step-by-Step Guide to Deploy PostgreSQL Master‑Slave Replication

1. Goal

Explain how to build a reliable PostgreSQL master‑slave deployment that improves read performance, provides failover capability, and supports hot standby queries.

2. Overview

Deployment planning

Single‑node PostgreSQL installation

Master‑slave configuration

Synchronization verification

3. Practical Steps

3.1 Introduction

PostgreSQL is a high‑performance relational database that also supports JSON/JSONB, spatial data via PostGIS, and can act as a NoSQL store. Native multi‑master clustering is limited, so replication is usually achieved with third‑party tools such as Bucardo, or by using built‑in streaming replication for a primary‑standby architecture.

3.2 Deployment Planning

Two servers are used:

Master node: 10.10.20.26 Standby node:

10.10.20.27

3.3 Single‑Node Installation (PostgreSQL 10)

Install the repository and required packages:

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Verify the packages: yum list | grep postgresql10 Install the core components (client, server, contrib, devel):

yum -y install postgresql10-client postgresql10-server postgresql10-contrib postgresql10-devel

Initialize the database cluster: /usr/pgsql-10/bin/postgresql-10-setup initdb Enable and start the service:

systemctl enable postgresql-10.service
systemctl start postgresql-10

3.4 Master Configuration

Create a replication user:

su - postgres
psql
CREATE ROLE replica LOGIN REPLICATION ENCRYPTED PASSWORD 'replica';

Allow the standby IP to connect (add to pg_hba.conf):

#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            ident
#host    replication     all             ::1/128                 ident
host    all             all         0.0.0.0/0               md5
host    replication     replica       10.10.20.27/32          md5

Adjust postgresql.conf for streaming replication:

listen_addresses = '*'
archive_mode = on
archive_command = 'cp %p /opt/pgsql/pg_archive/%f'
wal_level = logical
max_wal_senders = 32
wal_keep_segments = 256
wal_sender_timeout = 60s
max_connections = 1000

Reload or restart the service to apply changes:

systemctl restart postgresql-10   # or systemctl reload postgresql-10

3.5 Standby Configuration

Clear any existing data on the standby:

su - postgres
cd /var/lib/pgsql/10/data
rm -rf *

Copy the base backup from the master:

pg_basebackup -D $PGDATA -Fp -Xstream -R -c fast -v -P -h 10.10.20.26 -U replica -W

Modify postgresql.conf on the standby (enable hot standby and adjust WAL settings):

wal_level = logical
max_connections = 1000
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

Create recovery.conf (or its modern equivalent) to point to the master:

standby_mode = 'on'
primary_conninfo = 'user=replica password=replica host=10.10.20.26 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
recovery_target_timeline = 'latest'

Start the standby service:

systemctl restart postgresql-10

3.6 Synchronization Verification

On the master, query replication status:

su - postgres
psql
\x
SELECT * FROM pg_stat_replication;

The output shows the standby’s IP, WAL positions, and state (e.g., streaming).

3.7 Application Setup

Create a business user and database on the master:

CREATE USER office WITH PASSWORD '654321';
ALTER USER office SUPERUSER CREATEROLE CREATEDB REPLICATION;
CREATE DATABASE mirson OWNER office;

Export the database (using pgAdmin or command line):

pg_dump -h 127.0.0.1 -p 5432 -U office mirson > /usr/local/mirson.sql

Import the dump on the standby (or on the master after failover):

psql -h 127.0.0.1 -p 5432 -U office mirson < /usr/local/mirson.sql

If authentication fails, ensure pg_hba.conf contains a trust entry for the local host:

host all all 127.0.0.1/32 trust

3.8 Result

Both nodes now contain identical data; read queries can be directed to the standby, reducing load on the master. The setup also provides a quick recovery path if the primary fails.

Conclusion

Deploying PostgreSQL master‑slave replication with streaming replication and hot standby delivers high availability, load balancing for read‑heavy workloads, and a solid foundation for scaling out with additional standby nodes.