Streaming replication keeps a real-time copy of your PostgreSQL database on a separate server — providing both read scaling capability and a foundation for high availability failover.
How Streaming Replication Works
The primary server continuously streams its write-ahead log (WAL) to one or more replica servers, which apply these changes to stay synchronized in near real-time — replicas can serve read queries, reducing load on the primary.
Prerequisites
- Two PostgreSQL servers (primary and replica), network connectivity between them
- PostgreSQL already installed on both (see How to Install PostgreSQL on Ubuntu & Debian)
Step 1 — Configure the Primary Server
sudo nano /etc/postgresql/16/main/postgresql.conflisten_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024Step 2 — Create a Replication User
sudo -u postgres psqlCREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'CHANGE_ME_STRONG_PASSWORD';Step 3 — Allow Replication Connections
sudo nano /etc/postgresql/16/main/pg_hba.confhost replication replicator REPLICA_SERVER_IP/32 scram-sha-256Step 4 — Restart the Primary
sudo systemctl restart postgresqlStep 5 — Take a Base Backup on the Replica
sudo systemctl stop postgresql
sudo -u postgres pg_basebackup -h PRIMARY_SERVER_IP -D /var/lib/postgresql/16/main -U replicator -P -R-R automatically creates the standby configuration file needed for the replica to know how to connect to the primary.
Step 6 — Start the Replica
sudo systemctl start postgresqlStep 7 — Verify Replication Is Working
On the primary:
SELECT * FROM pg_stat_replication;On the replica:
SELECT pg_is_in_recovery();Should return t (true), confirming it's operating as a replica.
Testing Replication
# On primary
CREATE TABLE replication_test (id INT);
INSERT INTO replication_test VALUES (1);
# On replica (shortly after)
SELECT * FROM replication_test;Using the Replica for Read Queries
Direct read-only application traffic to the replica's connection string, reducing load on the primary — particularly valuable for read-heavy workloads (reporting, analytics queries) that shouldn't compete with primary write traffic.
Understanding Replication Lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;Monitor this — under normal conditions lag should be minimal (milliseconds to low seconds); sustained high lag indicates a problem worth investigating (network issues, replica under-resourced for the write volume).
Common Errors
Replica fails to connect to primary — verify pg_hba.conf on the primary allows the replica's specific IP, and that network/firewall rules permit PostgreSQL's port (5432) between the servers.
Continue Reading
- How to Install PostgreSQL on Ubuntu & Debian
- How to Set Up a Read Replica for Scaling Database Reads
- MySQL/MariaDB Replication Basics: Setting Up a Primary-Replica Setup
Browse more articles in Databases.
