What Is PostgreSQL Streaming Replication?
PostgreSQL Streaming Replication is a built-in high-availability feature that keeps data in sync by streaming WAL (Write-Ahead Log) records from the Primary to Replica (Standby) servers in real time.
- Hot Standby: Allows read-only queries on the Replica
- Synchronous (sync) / asynchronous (async) replication modes
- WAL-based: Transaction-level replication with minimal data loss
Environment
| Role | IP | OS |
|---|---|---|
| Primary | 192.168.1.10 | Ubuntu 22.04 |
| Replica | 192.168.1.11 | Ubuntu 22.04 |
Both servers must run the same PostgreSQL version (17 in this guide).
#
sudo apt update && sudo apt install -y postgresql-17Primary Server Configuration
1. Create a replication user
-- psql after
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'StrongPass!23';2. Edit postgresql.conf
sudo nano /etc/postgresql/17/main/postgresql.conflisten_addresses = '*'
wal_level = replica # WAL
max_wal_senders = 5 # when Replica ( settings)
wal_keep_size = 512 # WAL (MB)
synchronous_commit = on # when 'remote_apply'3. pg_hba.conf — Allow Replica connections
sudo nano /etc/postgresql/17/main/pg_hba.conf# Replica IP
host replication replicator 192.168.1.11/32 md5sudo systemctl restart postgresqlReplica Server Configuration
1. Wipe the existing data directory and take a base backup
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/17/main/*
# Primary backup
sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/17/main -Fp -Xs -P -R
# -R: standby.signal + recovery settings2. postgresql.conf (Replica-specific options)
hot_standby = on #
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=StrongPass!23'When you use pg_basebackup -R, these settings are written automatically to postgresql.auto.conf.
3. Start the Replica
sudo systemctl start postgresqlVerify Replication Status
On the Primary
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state
FROM pg_stat_replication;client_addr | state | sync_state
--------------+-----------+-----------
192.168.1.11 | streaming | asyncOn the Replica
SELECT * FROM pg_stat_wal_receiver;
-- status: streamingSynchronous Replication (Zero Data Loss)
# Primary postgresql.conf
synchronous_standby_names = 'replica1'
synchronous_commit = remote_apply# Replica postgresql.conf
application_name = replica1In synchronous mode, Primary commits wait until the Replica acknowledges — this has a performance cost.
Manual Failover (Promote Replica to Primary)
If the Primary fails, promote the Replica to become the new Primary.
# Replica
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/17/main
# or
sudo -u postgres psql -c "SELECT pg_promote();"After promotion, point the application's database connection to the Replica IP.
Replication Slots — Prevent WAL Loss
Create a slot so WAL is not removed while the Replica is offline.
-- Primary
SELECT pg_create_physical_replication_slot('replica1_slot');# Replica postgresql.auto.conf
primary_slot_name = 'replica1_slot'Caution: If the Replica stays offline for a long time, the Primary disk can fill up — monitoring is required.
Monitoring Queries
-- (lag) check
SELECT
client_addr,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;Summary
| Step | Location | Key setting |
|---|---|---|
| Enable WAL | Primary | wal_level = replica |
| Replication user | Primary | REPLICATION privilege |
| Base backup | Replica | pg_basebackup -R |
| Allow reads | Replica | hot_standby = on |
| Failover | Replica | pg_promote() |
AI tools may assist drafting. Check the evidence in each guide for whether commands were run and which environment was used. Confirm the current docs for your version before applying instructions, and email us about errors. email us
Questions & answers
Ask a question about this guide. We'll review and reply.