Introduction
PostgreSQL Streaming Replication is one of the fundamental technologies used to build high-availability and disaster-recovery solutions.
In this hands-on lab, I built a three-node PostgreSQL 16 environment with:
One Primary server
Two physical Standby servers
Dedicated replication network
Physical replication slots
pg_basebackupWAL streaming
Replication monitoring
End-to-end data validation
The objective was not simply to make replication work, but to build the environment using production-oriented practices and understand what happens at every stage.
1. Lab Architecture
The final architecture is:
PostgreSQL 16.14
PRIMARY
pg-primary / RHEL 8
192.168.56.101
|
+----------+----------+
| |
WAL Stream WAL Stream
Slot: 102 Slot: 103
| |
v v
pg-standby pg-stby2
192.168.56.102 192.168.56.103
Rocky Linux 9 Rocky Linux 9
PostgreSQL 16 PostgreSQL 16
Server details
| Server | IP Address | OS | PostgreSQL | Role |
|---|---|---|---|---|
| pg-primary | 192.168.56.101 | RHEL 8.10 | 16.14 | Primary |
| pg-standby | 192.168.56.102 | Rocky Linux 9.5 | 16.14 | Physical Standby |
| pg-stby2 | 192.168.56.103 | Rocky Linux 9.5 | 16.14 | Physical Standby |
The PostgreSQL database used for validation was salesdb, which was approximately 2.3 GB in size.
2. Network Preparation
Before configuring PostgreSQL replication, the servers must be able to communicate reliably.
The /etc/hosts configuration was:
192.168.56.101 pg-primary rhel8
192.168.56.102 pg-standby rocky9
192.168.56.103 pg-stby2 rocky9
Connectivity was validated between the servers.
For example:
ping -c 3 192.168.56.101
From the standby servers, the primary responded successfully.
PostgreSQL connectivity was also checked:
pg_isready -h 192.168.56.101 -p 5432
Expected:
192.168.56.101:5432 - accepting connections
3. Configure Meaningful Hostnames
Using meaningful hostnames makes administration and troubleshooting much easier.
Primary
hostnamectl set-hostname pg-primary
Verify:
hostname
Expected:
pg-primary
Standby 1
hostnamectl set-hostname pg-standby
Standby 2
hostnamectl set-hostname pg-stby2
4. PostgreSQL Version Validation
All three servers must use the same PostgreSQL major version for physical streaming replication.
On the primary:
psql --version
Result:
psql (PostgreSQL) 16.14
The PostgreSQL server on both standby servers was also confirmed to be:
PostgreSQL 16.14
One issue encountered during the setup was that the standby operating systems had PostgreSQL 13 client binaries earlier in the system PATH.
For example:
/usr/bin/psql
/usr/bin/pg_basebackup
/usr/bin/postgres
were PostgreSQL 13 binaries, while the actual PostgreSQL 16 installation was under:
/usr/pgsql-16/bin/
The PATH was corrected:
export PATH=/usr/pgsql-16/bin:$PATH
Verification:
which psql
which pg_basebackup
which postgres
Expected:
/usr/pgsql-16/bin/psql
/usr/pgsql-16/bin/pg_basebackup
/usr/pgsql-16/bin/postgres
And:
psql --version
pg_basebackup --version
postgres --version
Expected:
PostgreSQL 16.14
This is an important validation step before running pg_basebackup.
5. Verify the Primary
On pg-primary:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
Result:
f
f confirms that the server is currently a Primary.
The PostgreSQL data directory was:
/var/lib/pgsql/16/data
Configuration file:
/var/lib/pgsql/16/data/postgresql.conf
Authentication file:
/var/lib/pgsql/16/data/pg_hba.conf
6. Check Database Size
The lab used an existing salesdb database.
Database size was checked using:
SELECT pg_size_pretty(pg_database_size('salesdb'));
Result:
2376 MB
Approximately:
2.3 GB
This made the replication exercise more realistic than using an empty demonstration database.
7. Configure WAL for Streaming Replication
Streaming replication depends on PostgreSQL Write-Ahead Logging (WAL).
The following parameters were configured on the primary:
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 2GB
wal_level
wal_level = replica
This enables the WAL information required for physical replication.
max_wal_senders
max_wal_senders = 10
This controls how many WAL sender processes can be used to send WAL data to replicas.
Two standbys are being configured, so 10 provides reasonable headroom for the lab.
max_replication_slots
max_replication_slots = 10
Replication slots allow PostgreSQL to retain WAL required by a standby that has not yet consumed it.
wal_keep_size
wal_keep_size = 2GB
This provides a minimum amount of WAL retention on the primary.
In a real production environment, WAL retention should be sized based on WAL generation rate, outage duration, storage capacity and WAL archiving strategy.
8. Configure PostgreSQL Listening Address
The primary was configured to listen on the dedicated replication IP:
192.168.56.101
Configuration:
ALTER SYSTEM SET listen_addresses = '192.168.56.101,localhost';
After changing restart-required parameters, PostgreSQL was restarted:
systemctl restart postgresql-16
Validation:
sudo -u postgres psql -c "SHOW listen_addresses;"
Result:
192.168.56.101,localhost
The listener was verified:
ss -lntp | grep 5432
The primary was listening on:
192.168.56.101:5432
9. Create a Dedicated Replication User
A separate replication account is preferable to using the PostgreSQL superuser.
Create the user:
CREATE ROLE replicator
WITH REPLICATION
LOGIN;
Set its password securely:
\password replicator
Verify:
\du replicator
Expected attributes include:
Replication
Login
10. Configure pg_hba.conf
The primary must explicitly allow the standby servers to connect for replication.
The following entries were added to:
/var/lib/pgsql/16/data/pg_hba.conf
host replication replicator 192.168.56.102/32 scram-sha-256
host replication replicator 192.168.56.103/32 scram-sha-256
The /32 is intentional.
It means only the specified IP address is allowed.
Instead of:
192.168.56.0/24
we allow:
192.168.56.102
192.168.56.103
individually.
After modifying pg_hba.conf, reload PostgreSQL:
systemctl reload postgresql-16
11. Test Primary Connectivity
From pg-standby:
pg_isready -h 192.168.56.101 -p 5432
From pg-stby2:
pg_isready -h 192.168.56.101 -p 5432
Both returned:
192.168.56.101:5432 - accepting connections
This confirmed network connectivity before performing the base backup.
12. Create Physical Replication Slots
Two standbys require two separate replication slots.
On the primary:
SELECT pg_create_physical_replication_slot('pg_standby_102');
SELECT pg_create_physical_replication_slot('pg_standby_103');
Verify:
SELECT
slot_name,
slot_type,
active,
restart_lsn
FROM pg_replication_slots;
Initially:
pg_standby_102 | physical | f
pg_standby_103 | physical | f
active = f is expected because the standbys had not connected yet.
The slots were deliberately separated:
pg_standby_102 → 192.168.56.102
pg_standby_103 → 192.168.56.103
This prevents both standby servers from sharing a single replication slot.
13. Preserve the Existing Standby Data
Both standby servers already contained independent PostgreSQL clusters.
Instead of deleting the existing data directory, it was preserved.
On pg-standby:
systemctl stop postgresql-16
Then:
mv /var/lib/pgsql/16/data \
/var/lib/pgsql/16/data_before_replication
A fresh directory was created:
mkdir -p /var/lib/pgsql/16/data
Ownership:
chown postgres:postgres /var/lib/pgsql/16/data
Permissions:
chmod 700 /var/lib/pgsql/16/data
This is safer than immediately executing:
rm -rf /var/lib/pgsql/16/data/*
because the original cluster remains available for recovery or investigation.
The same approach was used for pg-stby2.
14. Configure .pgpass
A .pgpass file was used so the replication password does not need to be supplied interactively during pg_basebackup.
Example:
192.168.56.101:5432:*:replicator:YOUR_PASSWORD
The file was secured:
chown postgres:postgres /var/lib/pgsql/.pgpass
chmod 600 /var/lib/pgsql/.pgpass
The permissions are important because PostgreSQL will reject an insecure .pgpass file.
15. Test Replication Authentication
Before taking the base backup, authentication was tested.
From the standby:
sudo -u postgres psql \
-h 192.168.56.101 \
-p 5432 \
-U replicator \
-d postgres \
-c "SELECT current_user;"
Expected:
current_user
-------------
replicator
This confirmed:
Standby
|
| TCP 5432
v
Primary
|
| pg_hba.conf
v
replicator
16. Run pg_basebackup for Standby 1
On pg-standby, PostgreSQL 16 pg_basebackup was used:
sudo -u postgres /usr/pgsql-16/bin/pg_basebackup \
-h 192.168.56.101 \
-p 5432 \
-D /var/lib/pgsql/16/data \
-U replicator \
-S pg_standby_102 \
-X stream \
-R \
-P
Explanation
-h
-h 192.168.56.101
Connect to the primary.
-p
-p 5432
PostgreSQL port.
-D
-D /var/lib/pgsql/16/data
Destination data directory.
-U
-U replicator
Replication account.
-S
-S pg_standby_102
Use the dedicated physical replication slot for standby 1.
-X stream
-X stream
Streams WAL while the base backup is running.
This is important because the primary remains online and can continue generating WAL while the backup is being taken.
-R
-R
Automatically creates the required standby configuration, including standby.signal and connection information.
-P
-P
Displays progress during the backup.
17. Start Standby 1
After pg_basebackup completed:
systemctl start postgresql-16
Verify:
systemctl status postgresql-16
Then:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
Expected:
t
This confirms that the server is now operating as a physical standby.
18. Verify Replication from the Primary
On pg-primary:
SELECT
client_addr,
usename,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication;
The result showed:
client_addr | usename | state | sync_state
---------------+-----------+-----------+------------
192.168.56.102 | replicator| streaming | async
This was the first definitive confirmation that streaming replication was working.
19. Verify Replication Slot
On the primary:
SELECT
slot_name,
slot_type,
active,
restart_lsn
FROM pg_replication_slots;
The slot changed from:
active = f
to:
active = t
for:
pg_standby_102
This means the standby is actively using its physical replication slot.
20. Understand the LSN Values
The replication monitoring query returned values such as:
sent_lsn
write_lsn
flush_lsn
replay_lsn
These represent different stages of WAL movement.
Conceptually:
Primary
|
| sent_lsn
v
Standby receives WAL
|
| write_lsn
v
WAL written
|
| flush_lsn
v
WAL flushed to disk
|
| replay_lsn
v
WAL replayed
During the validation test, the values matched:
sent_lsn = 1/3E000148
write_lsn = 1/3E000148
flush_lsn = 1/3E000148
replay_lsn = 1/3E000148
This indicated that the standby had caught up with the primary at that moment.
21. Perform Actual Data Validation
Monitoring pg_stat_replication isn't enough.
A DBA should validate that actual business data is being replicated.
A test record was inserted into salesdb on the primary:
INSERT INTO sales_data
(
customer_name,
product_name,
city,
state,
country,
quantity,
price,
sale_date,
remarks
)
VALUES
(
'Replication Test',
'PostgreSQL HA Test',
'Chennai',
'Tamil Nadu',
'India',
10,
9999.99,
CURRENT_TIMESTAMP,
'Streaming replication validation'
);
The same query was then executed on the standby:
SELECT *
FROM sales_data
WHERE customer_name = 'Replication Test';
The standby returned the replicated rows:
sale_id | customer_name | product_name | city
--------+-----------------+--------------------+---------
3900001 | Replication Test| PostgreSQL HA Test | Chennai
3900002 | Replication Test| PostgreSQL HA Test | Chennai
This proved that actual database changes were being replicated.
22. Verify Standby Read-Only Behavior
A physical standby is normally read-only while it is in recovery.
Check:
SELECT pg_is_in_recovery();
Expected:
t
The standby should not be used for normal application writes.
The expected model is:
Primary
|
+---- READ / WRITE
|
+---- WAL
|
+---- Standby
READ ONLY
23. Collation Version Warning
During validation, a warning appeared:
WARNING: database "salesdb" has a collation version mismatch
DETAIL: The database was created using collation version 2.28,
but the operating system provides version 2.34.
This happened because the environment uses different operating-system generations:
Primary:
RHEL 8.10
and:
Standby:
Rocky Linux 9.5
The database metadata recorded:
datcollversion = 2.28
while the Rocky Linux environment provides a newer collation library version.
This does not mean that streaming replication failed.
Replication was successfully streaming and the test data was successfully replicated.
However, it is an important production consideration.
Collation rules can affect sorting and indexes involving text data. Therefore, production HA environments should carefully consider OS and collation-library consistency when designing primary/standby systems or performing OS upgrades.
Do not simply run:
ALTER DATABASE salesdb REFRESH COLLATION VERSION;
on a physical standby.
Any remediation should be planned carefully, including rebuilding affected collation-dependent objects where necessary.
24. Configure Standby 2
The second standby follows the same process.
Server:
pg-stby2
192.168.56.103
Replication slot:
pg_standby_103
Stop PostgreSQL:
systemctl stop postgresql-16
Preserve the existing cluster:
mv /var/lib/pgsql/16/data \
/var/lib/pgsql/16/data_before_replication
Create the new data directory:
mkdir -p /var/lib/pgsql/16/data
chown postgres:postgres /var/lib/pgsql/16/data
chmod 700 /var/lib/pgsql/16/data
Configure .pgpass:
192.168.56.101:5432:*:replicator:YOUR_PASSWORD
Then run:
sudo -u postgres /usr/pgsql-16/bin/pg_basebackup \
-h 192.168.56.101 \
-p 5432 \
-D /var/lib/pgsql/16/data \
-U replicator \
-S pg_standby_103 \
-X stream \
-R \
-P
Start PostgreSQL:
systemctl start postgresql-16
Verify:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
Expected:
t
25. Final Replication Validation
On the primary:
SELECT
client_addr,
usename,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication
ORDER BY client_addr;
Expected architecture:
192.168.56.102 | replicator | streaming | async
192.168.56.103 | replicator | streaming | async
Check the slots:
SELECT
slot_name,
slot_type,
active,
restart_lsn
FROM pg_replication_slots
ORDER BY slot_name;
Expected:
pg_standby_102 | physical | t
pg_standby_103 | physical | t
At this point both standbys are actively consuming WAL.
26. Final Architecture
The completed environment is:
PostgreSQL Primary
pg-primary
192.168.56.101
RHEL 8.10
PostgreSQL 16.14
|
+-----------+-----------+
| |
| WAL Streaming | WAL Streaming
| |
v v
pg-standby pg-stby2
192.168.56.102 192.168.56.103
Rocky Linux 9 Rocky Linux 9
PostgreSQL 16.14 PostgreSQL 16.14
| |
Slot 102 Slot 103
| |
Streaming Streaming
| |
Async Async
27. Production Considerations
This lab establishes the foundation for a production-grade PostgreSQL HA design, but replication alone does not equal automatic high availability.
Additional production components should be considered:
WAL archiving
Point-in-Time Recovery (PITR)
Backup validation
Replication lag monitoring
Replication slot monitoring
Disk-space monitoring
WAL retention monitoring
Failover procedures
Switchover procedures
Synchronous replication where appropriate
Connection pooler / application routing
Automated failover tooling
Monitoring and alerting
Regular disaster-recovery testing
A particularly important point is that replication is not a replacement for backups.
If a user accidentally deletes data on the primary, that change can also be replicated to the standby. PITR and independent backups are therefore still required.
28. Useful Monitoring Queries
Check replication status
SELECT
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication;
Check replication slots
SELECT
slot_name,
slot_type,
active,
restart_lsn
FROM pg_replication_slots;
Calculate replay lag
SELECT
client_addr,
state,
sync_state,
pg_size_pretty(
pg_wal_lsn_diff(sent_lsn, replay_lsn)
) AS replay_lag
FROM pg_stat_replication;
Check whether a server is a standby
SELECT pg_is_in_recovery();
Primary:
f
Standby:
t
Conclusion
This lab demonstrated a complete PostgreSQL 16 physical streaming replication setup with one primary and two standby servers.
The implementation covered:
Server hostname configuration
Static networking
PostgreSQL 16 validation
WAL configuration
Dedicated replication user
pg_hba.confPhysical replication slots
pg_basebackupstandby.signalWAL streaming
Replication monitoring
Replication slot monitoring
LSN validation
End-to-end business data validation
Collation version mismatch analysis
Most importantly, the setup was validated using actual salesdb data rather than relying only on PostgreSQL status views.
The next logical phase is to move beyond basic streaming replication and implement monitoring, replication-lag analysis, WAL archiving, PITR, controlled switchover, failover, and recovery testing.
Comments
Post a Comment