Step-by-Step Guide to PostgreSQL Streaming Replication (Primary-Standby Setup)

Here's the properly organized step-by-step guide for setting up PostgreSQL 12/15 streaming replication between Primary (172.19.0.20) and Standby (172.19.0.30):


✅ Pre-Requisites

Two servers with PostgreSQL 12/15.3 installed
Database cluster initialized on both using initdb (service not started yet)

IP configuration:

Primary: 172.19.0.20
Standby: 172.19.0.30

🔧 Primary Server Configuration

Backup config file:

bash

cd /u01/postgres/pgdata_15/data
cp postgresql.conf postgresql.conf_bkp_$(date +%b%d_%Y)


Edit postgresql.conf:

bash

vi postgresql.conf

Set these parameters:

listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10
wal_keep_segments = 50

Start PostgreSQL:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Create replication user:

sql

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Configure pg_hba.conf:

bash

cp pg_hba.conf pg_hba.conf_bkp_$(date +%b%d_%Y)

vi pg_hba.conf

Add:
host replication repl_user 172.19.0.30/32 md5

Reload config:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

🔄 Standby Server Configuration

Clear data directory:

bash

rm -rf /u01/postgres/pgdata_15/data/*

Run pg_basebackup:

bash

pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Start PostgreSQL:

bash

pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Verify logs:

bash

tail -f /u01/postgres/pgdata_15/data/logfile

Look for:

text

started streaming WAL from primary at 0/3000000 on timeline 1

🔍 Verification Steps

On Primary:

sql

CREATE DATABASE reptest;

\c reptest

CREATE TABLE test_table (id int);

INSERT INTO test_table VALUES (1);

On Standby:

sql

\c reptest

SELECT * FROM test_table; -- Should return 1 row

Check processes:

Primary:

bash

ps -ef | grep postgres

Look for walsender process

Standby:

Look for walreceiver process

Network test:

bash

nc -zv 172.19.0.20 5432


📌 Key Notes

-Replace paths/versions according to your setup
-Ensure firewall allows port 5432 between servers
For Oracle Linux 6.8, adjust service commands if needed
-Monitor logs (logfile) on both servers for errors


Detailed steps:

✅ Pre-Requisites

Two servers with PostgreSQL 12 or PostgreSQL 15.3 installed.
Database cluster initialized on both using initdb (but do not start the service yet).

IPs configured:

Primary: 172.19.0.20
Standby: 172.19.0.30

Make some necessary changes in postgresql.conf file on the primary server.

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ cp postgresql.conf postgresql.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi postgresql.conf


listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10

After making necessary changes in the postgresql.conf file. Let’s start the database server on primary server using below command.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Once the database server is started, invoke psql as super user i.e. postgres and create a role for replication.

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Allow connection to created user from Standby Server by seting pg_hba.conf

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ ls

archive_logs pg_commit_ts pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

base pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postgresql.conf_old

global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact postmaster.opts

logfile pg_hba.conf_bkp_Aug18_2025 pg_notify pg_stat pg_twophase postgresql.auto.conf postmaster.pid


[postgres@HOST01 data]$ cp pg_hba.conf pg_hba.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi pg_hba.conf

host replication repl_user 172.19.0.30/32 md5

Reload the setting of the cluster.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile stop

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

(OR)

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

We made changes to the configuration of the primary server. Now we’ll use pg_basebackup to get the data directory i.e. /u01/postgres/pgdata_15/data to Standby Server.

Standby Server Configurations.

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

pg_basebackup: error: directory "/u01/postgres/pgdata_15/data" exists but is not empty

Navigate to data directory and empty it.

cd /u01/postgres/pgdata_15/data

rm -rf *

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

38761/38761 kB (100%), 1/1 tablespace

Now start the database server at standby side using below command.


[postgres@HOST02 ~]$ pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

waiting for server to start.... done

server started

Check the logfile to gather some more information.

[postgres@HOST02 data]$ tail -f logfile

2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv6 address "::", port 5432
2025-08-18 21:12:09.491 UTC [11349] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-08-18 21:12:09.498 UTC [11352] LOG: database system was interrupted; last known up at 2025-08-18 21:11:13 UTC
2025-08-18 21:12:09.513 UTC [11352] LOG: entering standby mode
2025-08-18 21:12:09.518 UTC [11352] LOG: redo starts at 0/2000028
2025-08-18 21:12:09.519 UTC [11352] LOG: consistent recovery state reached at 0/2000100
2025-08-18 21:12:09.520 UTC [11349] LOG: database system is ready to accept read-only connections
2025-08-18 21:12:09.557 UTC [11353] LOG: started streaming WAL from primary at 0/3000000 on timeline 1

Let’s now check the replication. Invoke psql and perform some operations.

Primary:

create database qadar_reptest;
\c qadar_reptest;
create table mqm_reptst_table (serial int);
insert into mqm_reptst_table values (1);

Standby:

\c qadar_reptest;
select count(*) from mqm_reptst_table;

Check the background process involved in streaming replication:

Primary:

[postgres@HOST01 data]$ ps -ef|grep postgres

root 11329 160 0 19:40 pts/1 00:00:00 su - postgres
postgres 11330 11329 0 19:40 pts/1 00:00:04 -bash
root 11608 11496 0 21:07 pts/2 00:00:00 su - postgres
postgres 11609 11608 0 21:07 pts/2 00:00:00 -bash
postgres 11635 11609 0 21:07 pts/2 00:00:00 /usr/bin/coreutils --coreutils-prog-shebang=tail /usr/bin/tail -f logfile
postgres 11639 1 0 21:08 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11640 11639 0 21:08 ? 00:00:00 postgres: checkpointer
postgres 11641 11639 0 21:08 ? 00:00:00 postgres: background writer
postgres 11643 11639 0 21:08 ? 00:00:00 postgres: walwriter
postgres 11644 11639 0 21:08 ? 00:00:00 postgres: autovacuum launcher
postgres 11645 11639 0 21:08 ? 00:00:00 postgres: archiver last was 000000010000000000000002.00000028.backup
postgres 11646 11639 0 21:08 ? 00:00:00 postgres: logical replication launcher
postgres 11663 11639 0 21:12 ? 00:00:00 postgres: walsender repl_user 172.19.0.30(38248) streaming 0/3000060
postgres 11671 11330 0 21:15 pts/1 00:00:00 ps -ef
postgres 11672 11330 0 21:15 pts/1 00:00:00 grep --color=auto postgres

Standby:

[postgres@HOST02 ~]$ ps -ef|grep postgres

root 11300 119 0 20:59 pts/1 00:00:00 su - postgres
postgres 11301 11300 0 20:59 pts/1 00:00:00 -bash
postgres 11349 1 0 21:12 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11350 11349 0 21:12 ? 00:00:00 postgres: checkpointer
postgres 11351 11349 0 21:12 ? 00:00:00 postgres: background writer
postgres 11352 11349 0 21:12 ? 00:00:00 postgres: startup recovering 000000010000000000000003
postgres 11353 11349 0 21:12 ? 00:00:00 postgres: walreceiver streaming 0/3000148
postgres 11356 11301 0 21:16 pts/1 00:00:00 ps -ef
postgres 11357 11301 0 21:16 pts/1 00:00:00 grep --color=auto postgres


Check the network connectivity betweeen the servers.

Standby:

[postgres@HOST02 ~] yum install nc
[postgres@HOST02 ~]$ nc -zv 172.19.0.20 5432
Ncat: Version 7.70 ( https://nmap.org/ncat )
Ncat: Connected to 172.19.0.20:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.