.avif)
.avif)
A standalone PostgreSQL server represents a single point of failure (SPOF). If the host hardware fails, the underlying storage degrades, or the operating system crashes, your application goes down with it.
Physical (streaming) replication adds one or more standby nodes that continuously receive and replay the source's Write-Ahead Log (WAL). This architecture provides:
- High availability: a replica that can be promoted if the source fails
- Read scaling: reporting and read-only queries can move off the source
- Safer maintenance: run heavy reads, backups, and analysis on the replica
- A foundation for failover tooling such as Patroni
This post takes a standalone PostgreSQL server and turns it into a source with one streaming replica.
How Physical Replication Works
Physical replication works at the lowest storage layer replaying WAL records byte-for-byte across your cluster:
- Every write operation (insert, update, delete, DDL) is logged to the Write-Ahead Log on the source before it touches data tables.
- The standby replica connects over a TCP connection, which causes the source to fork a dedicated walsender background process.
- On the replica, a walreceiver process receives incoming WAL streams and flushes them to disk.
- The replica’s startup process reads and applies the WAL continuously, mirroring the source's data files.
- The replica operates in hot standby mode, meaning client applications can connect to run read-only queries while WAL replay occurs concurrently.
Environment and Prerequisites
For this walkthrough, we configure two nodes running on an internal subnet:
Architectural Prerequisites:
- Matching Major Versions: Both instances must share the same major release (e.g., PostgreSQL 16). Replicas can run the same or newer minor versions, but never older.
- Platform Homogeneity: Both servers must run on identical CPU architectures (e.g., x86_64) and identical OS families to guarantee binary file compatibility.
- Network Connectivity: Unobstructed TCP communication over port 5432 from the replica to the source.
- Disk Capacity: Sufficient provisioned storage on the replica to house the initial base backup plus ongoing WAL generation spikes.
- Extension Binaries: Any third-party database extensions installed on the source must have their shared libraries pre-installed on the replica host.
Step 1: Configure the Source Server
1. Check Default Replication Settings
Modern PostgreSQL releases (version 10 and above) configure default values that support physical replication out of the box. Connect to your primary instance via psql to check:
SHOW wal_level;
wal_level
-----------
replica
(1 row)
SHOW max_wal_senders;
max_wal_senders
-----------------
10
(1 row)
SHOW max_replication_slots;
max_replication_slots
-----------------------
10
(1 row)
SHOW listen_addresses;
listen_addresses
------------------
localhost
(1 row)
SHOW hot_standby;
hot_standby
-------------
on
(1 row)Since PostgreSQL 10, the defaults are wal_level = replica and max_wal_senders = 10. If your values already look like this, the source needs no restart for replication. Only listen_addresses needs one if it is still localhost.
2. Edit postgresql.conf
Open your source instance’s postgresql.conf (located in /etc/postgresql/16/main/ on Debian/Ubuntu) and adjust the networking and retention parameters:
listen_addresses = '*' # or the source's specific IP (restart required)
wal_level = replica # restart required
max_wal_senders = 10 # restart required
max_replication_slots = 10 # restart required
hot_standby = on # used when this server later acts as a standby
wal_keep_size = '1GB' # PostgreSQL 13+ (reload). Older: wal_keep_segments
max_slot_wal_keep_size = '50GB' # PostgreSQL 13+, safety cap for slots (reload)
wal_compression = on # optional, reduces WAL volume (reload)3. Create a Dedicated Replication User
Avoid connecting replicas using the postgres superuser account. Instead, create a dedicated role with minimal necessary privileges:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'Use_A_Strong_Password_Here';
CREATE ROLE4. Configure Authentication in pg_hba.conf
Add a client entry allowing the replica’s static IP to request replication streams:
# TYPE DATABASE USER ADDRESS METHOD
host replication replicator 172.16.20.12/32 scram-sha-256- The entry replication in the database field is a reserved keyword; passing all will not permit physical streaming replication.
- Lock the connection scope directly to the replica IP rather than broad subnets.
- Always enforce scram-sha-256 authentication over deprecated password methods.
5. Apply Changes
If you altered listen_addresses, restart PostgreSQL via systemd:
sudo systemctl restart postgresql
# Log: /var/log/postgresql/postgresql-16-main.log (source)
2026-11-05 10:42:17.301 UTC [1204] LOG: received fast shutdown request
2026-11-05 10:42:17.305 UTC [1204] LOG: aborting any active transactions
2026-11-05 10:42:17.312 UTC [1204] LOG: background worker "logical replication launcher" (PID 1210) exited with exit code 1
2026-11-05 10:42:17.314 UTC [1205] LOG: shutting down
2026-11-05 10:42:17.320 UTC [1205] LOG: checkpoint starting: shutdown immediate
2026-11-05 10:42:17.401 UTC [1205] LOG: checkpoint complete: wrote 12 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.031 s, sync=0.012 s, total=0.088 s; sync files=9, longest=0.004 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1A/6D0003B8, redo lsn=1A/6D0003B8
2026-11-05 10:42:17.412 UTC [1204] LOG: database system is shut down
2026-11-05 10:42:17.689 UTC [2331] LOG: starting PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2026-11-05 10:42:17.690 UTC [2331] LOG: listening on IPv4 address "0.0.0.0", port 5432
2026-11-05 10:42:17.690 UTC [2331] LOG: listening on IPv6 address "::", port 5432
2026-11-05 10:42:17.695 UTC [2331] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-11-05 10:42:17.703 UTC [2336] LOG: database system was shut down at 2026-11-05 10:42:17 UTC
2026-11-05 10:42:17.714 UTC [2331] LOG: database system is ready to accept connectionsIf you changed only pg_hba.conf and reload-level parameters:
SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
# Log: /var/log/postgresql/postgresql-16-main.log (source)
2026-11-05 10:51:09.114 UTC [2331] LOG: received SIGHUP, reloading configuration files
2026-11-05 10:51:09.115 UTC [2331] LOG: parameter "wal_keep_size" changed to "1GB"
2026-11-05 10:51:09.115 UTC [2331] LOG: parameter "max_slot_wal_keep_size" changed to "50GB"
2026-11-05 10:51:09.115 UTC [2331] LOG: parameter "wal_compression" changed to "on"
4.6 Create a physical replication slot
SELECT pg_create_physical_replication_slot('replica1_slot');
pg_create_physical_replication_slot
-------------------------------------
(replica1_slot,)
(1 row)6. Create a Physical Replication Slot
Replication slots safeguard your standby cluster. A slot tracks replication progress and instructs the source engine not to delete WAL segments until the replica has acknowledged and received them:
SELECT slot_name, slot_type, active, restart_lsn FROM pg_replication_slots;slot_name | slot_type | active | restart_lsn
---------------+-----------+--------+-------------
replica1_slot | physical | f |The active column will remain f (false) until the replica connects.
Step 2: Configure the Replica Server
1. Stop the Replica Service
Install the matching PostgreSQL binaries on your standby host. Before pulling the data snapshot, stop the automatically created initial service:
sudo systemctl stop postgresql2. Clear Existing Data Files
pg_basebackup requires a completely empty destination path. Clear out the default initialized cluster directory:
sudo -u postgres bash -c 'rm -rf /var/lib/postgresql/16/main/*'Caution: Confirm you are executing this command on your replica (pg-replica) and not the source. Ensure directory permissions remain 0700 owned by postgres:postgres.
3. Run pg_basebackup
To authenticate seamlessly, store the password in the postgres user's home path under ~/.pgpass with 0600 permissions:
172.16.20.11:5432:replication:replicator:Use_A_Strong_Password_HereExecute pg_basebackup as the postgres user:
sudo -u postgres pg_basebackup -h 172.16.20.11 -p 5432 -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R -S replica1_slot --checkpoint=fast
1263548/1263548 kB (100%), 1/1 tablespace
# Log: /var/log/postgresql/postgresql-16-main.log (source), during the backup
2026-11-05 11:08:41.233 UTC [2284] LOG: checkpoint starting: immediate force wait
2026-11-05 11:08:41.298 UTC [2284] LOG: checkpoint complete: wrote 62 buffers (0.4%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.011 s, sync=0.020 s, total=0.066 s; sync files=34, longest=0.006 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=1A/6F0000D8, redo lsn=1A/6F0000A0Flag breakdown:
- -D: The target local directory on the standby node.
- -Fp: Outputs plain format (a ready-to-use directory structure).
- -Xs: Streams WAL logs while the snapshot is copied, ensuring baseline transactional consistency.
- -P: Displays live backup transfer metrics.
- -R: Writes replication parameters directly to postgresql.auto.conf and creates the standby.signal marker file.
- -S: Attaches the backup session to our pre-created replication slot (replica1_slot).
- --checkpoint=fast: Forces the source to checkpoint immediately, starting the backup without delay.
4. Review the Generated -R Files
Inspect the auto-generated standby settings:
sudo -u postgres cat /var/lib/postgresql/16/main/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=172.16.20.11 port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica1_slot'
ls -l /var/lib/postgresql/16/main/standby.signal
-rw------- 1 postgres postgres 0 Nov 5 11:08 /var/lib/postgresql/16/main/standby.signalYou should see connection parameters matching your source:
primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=172.16.20.11 port=5432 ...'
primary_slot_name = 'replica1_slot'5. Verify Sizing Parameters
On Debian and Ubuntu, configuration files (postgresql.conf and pg_hba.conf) are stored in /etc/postgresql/16/main/ and are not overwritten by pg_basebackup.
Certain configuration parameters on the replica must be set greater than or equal to the source values, or the replica will abort during startup:
- max_connections
- max_worker_processes
- max_wal_senders
- max_prepared_transactions
- max_locks_per_transaction
Ensure your replica's postgresql.conf reflects these criteria:
hot_standby = on
hot_standby_feedback = on
max_connections = 100 # Must be >= source value6. Start the Replica Engine
Start the database service:
sudo systemctl start postgresql
# Log: /var/log/postgresql/postgresql-16-main.log (replica)
2026-11-05 11:12:03.512 UTC [1888] LOG: starting PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2026-11-05 11:12:03.513 UTC [1888] LOG: listening on IPv4 address "127.0.0.1", port 5432
2026-11-05 11:12:03.513 UTC [1888] LOG: listening on IPv6 address "::1", port 5432
2026-11-05 11:12:03.518 UTC [1888] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-11-05 11:12:03.531 UTC [1892] LOG: database system was interrupted; last known up at 2026-11-05 11:08:41 UTC
2026-11-05 11:12:03.664 UTC [1892] LOG: entering standby mode
2026-11-05 11:12:03.671 UTC [1892] LOG: redo starts at 1A/6F0000A0
2026-11-05 11:12:03.674 UTC [1892] LOG: consistent recovery state reached at 1A/6F000188
2026-11-05 11:12:03.675 UTC [1888] LOG: database system is ready to accept read-only connections
2026-11-05 11:12:03.702 UTC [1931] LOG: started streaming WAL from primary at 1A/70000000 on timeline 1Step 3: Verifying Replication Health
Source Checks
On the primary server, query pg_stat_replication:
SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
client_addr | state | sync_state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag
--------------+-----------+------------+-------------+-------------+-------------+-------------+-----------------+-----------------+-----------------
172.16.20.12 | streaming | async | 1A/70000148 | 1A/70000148 | 1A/70000148 | 1A/70000148 | 00:00:00.000282 | 00:00:00.000561 | 00:00:00.000598
(1 row)Check slot consumption status:
SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;
slot_name | active | restart_lsn | wal_status
---------------+--------+-------------+------------
replica1_slot | t | 1A/70000148 | reserved
(1 row)The slot status should show active = t.
Standby Checks
On the replica, verify recovery status:
SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;
status | sender_host | slot_name
-----------+--------------+---------------
streaming | 172.16.20.11 | replica1_slot
(1 row)
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
pg_last_wal_receive_lsn | pg_last_wal_replay_lsn
-------------------------+------------------------
1A/70000148 | 1A/70000148
(1 row)End-to-End Replication Test
To test end-to-end data flow:
On Source:
CREATE TABLE repl_test (id int, created_at timestamptz DEFAULT now());
CREATE TABLE
INSERT INTO repl_test VALUES (1);
INSERT 0 1On the replica:
SELECT * FROM repl_test; -- row should appear
id | created_at
----+-------------------------------
1 | 2026-11-05 11:21:44.907312+00
(1 row)
INSERT INTO repl_test VALUES (2); -- should fail: read-only transaction
ERROR: cannot execute INSERT in a read-only transactionClean Up (Source):
DROP TABLE repl_test;Accurate Replication Lag Monitoring
While teams often run SELECT now() - pg_last_xact_replay_timestamp(); on standbys, this metric reflects elapsed time since the last written transaction. When the source is idle, this number continues to grow, causing false alerts.
For precise lag calculations, measure byte differentials directly on the source instance:
On the replica:
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
replay_delay
-----------------
00:03:18.412905
(1 row)This value keeps growing when the source is idle, even though nothing is wrong. For reliable lag monitoring, use replay_lag from pg_stat_replication on the source, or compare LSNs in bytes:
SELECT client_addr,pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes FROM pg_stat_replication;
client_addr | replay_lag_bytes
--------------+------------------
172.16.20.12 | 0
(1 row)Tuning Standby Parameters and Resolving Query Conflicts
- Query conflicts: long queries on the replica can conflict with WAL replay (for example, vacuum removing rows the query still needs). Replay waits up to max_standby_streaming_delay (default 30s) and then cancels the query.
- hot_standby_feedback = on prevents most of these cancellations, but the replica’s long queries can then hold back vacuum on the source and cause bloat. Use it consciously for reporting replicas.
- Don’t set max_standby_streaming_delay = -1 on an HA replica, because replay can fall behind indefinitely.
Example from the replica log when replay cancels a long-running query on a reporting replica:
2026-11-05 12:14:52.806 UTC [3120] ERROR: canceling statement due to conflict with recovery
2026-11-05 12:14:52.806 UTC [3120] DETAIL: User query might have needed to see row versions that must be removed.
2026-11-05 12:14:52.806 UTC [3120] STATEMENT: SELECT customer_id, sum(amount) FROM payments GROUP BY customer_id;Asynchronous vs. Synchronous Replication
By default replication is asynchronous: the source commits without waiting for the replica. This is the fastest option, but a failover can lose the last few transactions.
For synchronous replication, give the replica an application_name in primary_conninfo (for example application_name=replica1), then
On the source:
synchronous_standby_names = 'FIRST 1 (replica1)'
synchronous_commit = on
# Log: source, after SELECT pg_reload_conf(); and the replica restarts its WAL receiver with application_name=replica1
2026-11-05 11:40:12.071 UTC [2331] LOG: received SIGHUP, reloading configuration files
2026-11-05 11:40:12.072 UTC [2331] LOG: parameter "synchronous_standby_names" changed to "FIRST 1 (replica1)"
2026-11-05 11:40:12.309 UTC [2492] LOG: standby "replica1" is now a synchronous standby with priority 1
-- Verify on the source
SELECT application_name, state, sync_state FROM pg_stat_replication;
application_name | state | sync_state
------------------+-----------+------------
replica1 | streaming | sync
(1 row)Commits now wait for the replica’s confirmation. The trade-offs are higher commit latency, and writes block if the only synchronous standby goes down. Use synchronous replication only when zero data loss is a hard requirement, and ideally with two or more candidate standbys.
Managing Replication Slots and Pitfals
Replication slots prevent the source server from recycling WAL files before the replica consumes them. However, if a replica disconnects permanently, the source server will retain WAL files indefinitely, which can eventually fill the disk and crash the primary instance.
To protect against disk exhaustion:
- Define Slot Retention Caps: Always configure max_slot_wal_keep_size on the primary instance:
max_slot_wal_keep_size = '50GB'- If a replica falls behind by more than this limit, PostgreSQL invalidates the slot to protect primary disk space.
- Clean Up Inactive Slots: If a replica is decommissioned, drop its slot immediately:
SELECT pg_drop_replication_slot('replica1_slot');
pg_drop_replication_slot
--------------------------
(1 row)If a disconnected replica falls further behind than max_slot_wal_keep_size allows, the source invalidates the slot and logs:
2026-11-05 12:29:36.402 UTC [2284] LOG: invalidating obsolete replication slot "replica1_slot"
2026-11-05 12:29:36.402 UTC [2284] DETAIL: The slot's restart_lsn 1A/71000000 exceeds the limit by 33554432 bytes.
2026-11-05 12:29:36.402 UTC [2284] HINT: You might need to increase max_slot_wal_keep_size.A replica is not a backup. A DROP TABLE or bad UPDATE replicates immediately. Keep regular backups and WAL archiving for point-in-time recovery.
- Secure the connection. Use scram-sha-256, restrict pg_hba.conf to specific IPs, and enable SSL (sslmode=require or verify-full in primary_conninfo) if traffic crosses untrusted networks.
- Keep versions aligned during upgrades: upgrade replicas first, then the source, within the same major version.
- Alert on lag and on pg_stat_replication being empty, which means the replica is disconnected.
Troubleshooting Common Issues
Log lines you will typically see for the failures above (PostgreSQL 16, Ubuntu paths):
# Replica data directory is not empty (pg_basebackup output)
pg_basebackup: error: directory "/var/lib/postgresql/16/main" exists but is not empty
# Missing pg_hba.conf entry (source log; libpq tries SSL first, then retries without it)
2026-11-05 11:03:27.481 UTC [2287] FATAL: no pg_hba.conf entry for replication connection from host "172.16.20.12", user "replicator", SSL encryption
2026-11-05 11:03:27.502 UTC [2288] FATAL: no pg_hba.conf entry for replication connection from host "172.16.20.12", user "replicator", no encryption
# Wrong password (source log)
2026-11-05 11:06:14.337 UTC [2301] FATAL: password authentication failed for user "replicator"
2026-11-05 11:06:14.337 UTC [2301] DETAIL: Password does not match for user "replicator".
Connection matched file "/etc/postgresql/16/main/pg_hba.conf" line 128: "host replication replicator 172.16.20.12/32 scram-sha-256"
# Source not listening on the network (replica log)
2026-11-05 11:14:02.115 UTC [1931] FATAL: could not connect to the primary server: connection to server at "172.16.20.11", port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
# Firewall or security group blocking port 5432 (replica log)
2026-11-05 11:16:35.774 UTC [1953] FATAL: could not connect to the primary server: connection to server at "172.16.20.11", port 5432 failed: Connection timed out
Is the server running on that host and accepting TCP/IP connections?
# WAL already removed because no slot was used (replica log)
2026-11-05 12:31:09.774 UTC [1931] LOG: started streaming WAL from primary at 1A/71000000 on timeline 1
2026-11-05 12:31:09.775 UTC [1931] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000001A00000071 has already been removed
# Replica parameter lower than on the source (replica log, PostgreSQL 14 and later)
2026-11-05 11:20:48.629 UTC [2415] FATAL: recovery aborted because of insufficient parameter settings
2026-11-05 11:20:48.629 UTC [2415] DETAIL: max_connections = 50 is a lower setting than on the primary server, where its value was 100.
2026-11-05 11:20:48.629 UTC [2415] HINT: You can restart the server after making the necessary configuration changes.Standby Promotion Preview
If the primary node experiences an unrecoverable failure, promote the standby to accept read-write traffic:
Using SQL:
SELECT pg_promote();
pg_promote
------------
t
(1 row)Or
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main
waiting for server to promote.... done
server promoted
# Log: /var/log/postgresql/postgresql-16-main.log (replica)
2026-11-05 13:05:18.402 UTC [1892] LOG: received promote request
2026-11-05 13:05:18.402 UTC [1931] FATAL: terminating walreceiver process due to administrator command
2026-11-05 13:05:18.405 UTC [1892] LOG: invalid record length at 1A/7200AA30: expected at least 24, got 0
2026-11-05 13:05:18.405 UTC [1892] LOG: redo done at 1A/7200A9F8 system usage: CPU: user: 0.05 s, system: 0.03 s, elapsed: 6795.12 s
2026-11-05 13:05:18.405 UTC [1892] LOG: last completed transaction was at log time 2026-11-05 13:05:11.206318+00
2026-11-05 13:05:18.411 UTC [1892] LOG: selected new timeline ID: 2
2026-11-05 13:05:18.452 UTC [1892] LOG: archive recovery complete
2026-11-05 13:05:18.463 UTC [1890] LOG: checkpoint starting: force
2026-11-05 13:05:18.466 UTC [1888] LOG: database system is ready to accept connections
2026-11-05 13:05:18.521 UTC [1890] LOG: checkpoint complete: wrote 9 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.012 s, sync=0.008 s, total=0.058 s; sync files=7, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1A/7200AAA8, redo lsn=1A/7200AA60Promotion is one-way: the old source cannot simply rejoin as a standby and must be rebuilt (or rewound with pg_rewind). Automated failover is best handled by tools such as Patroni or repmgr, which can be the topic of a follow-up post.
Conclusion
Setting up PostgreSQL physical streaming replication comes down to five core steps:
- Configure primary networking, authentication, and replication users.
- Create physical replication slots to protect WAL retention.
- Pull an online base backup using pg_basebackup -R.
- Align configuration parameters and start the replica instance.
- Validate replication streams and monitor lag via pg_stat_replication.
Paired with routine database backups and continuous monitoring, streaming replication builds the foundation for resilient database infrastructure.
Setting up replication is the easy part. Keeping it healthy, monitored and ready for failover is where most teams struggle. With 10+ years of database expertise, 800+ clients and 4000+ servers managed, Mydbops can design, set up and monitor your PostgreSQL replication and high availability for you.
.png)

.avif)

_%20How%20MySQL%2026.7%20Fixes%20Replication%20Lag%20(1).avif)
_%20How%20MySQL%2026.7%20Fixes%20Replication%20Lag.avif)