How to Set Up PostgreSQL Streaming Replication: Step-by-Step Guide

Manosh Malai
Nov 16, 2026
10
Mins to Read
All
Set Up PostgreSQL Streaming Replication
Set Up PostgreSQL Streaming Replication

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:

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:

  1. Every write operation (insert, update, delete, DDL) is logged to the Write-Ahead Log on the source before it touches data tables.
  2. The standby replica connects over a TCP connection, which causes the source to fork a dedicated walsender background process.
  3. On the replica, a walreceiver process receives incoming WAL streams and flushes them to disk.
  4. The replica’s startup process reads and applies the WAL continuously, mirroring the source's data files.
  5. The replica operates in hot standby mode, meaning client applications can connect to run read-only queries while WAL replay occurs concurrently.
PostgreSQL Physical (Streaming) Replication Architecture
SOURCE (pg-source) 172.16.20.11
1Client Writes
INSERT / UPDATE / DELETE
WAL Files (`pg_wal`) Written immediately to disk
walsender Process 1 process allocated per connected standby
Data Files (Heap) Flushed at checkpoint intervals
TCP: 5432 user: replicator
Replication Slot replica1_slot retains WAL on source until standby receives it.
REPLICA (pg-replica) 172.16.20.12
5Read-Only Queries
Hot standby mode (reports, analytical reads)
3walreceiver Process
Receives stream & writes to local disk
WAL Files (`pg_wal`) Local standby queue
4Startup Process
Continuously replays WAL into local data files

Environment and Prerequisites

For this walkthrough, we configure two nodes running on an internal subnet:

Cluster Role
Hostname
Static IPv4
State
Primary (Source)
pg-source
172.16.20.11:5432
Read/Write
Hot Standby (Replica)
pg-replica
172.16.20.12:5432
Read-Only

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.
Streaming replication setup in 5 steps
  1. 1
    Prepare the sourcelisten_addresses, replication user, pg_hba.conf
  2. 2
    Create a physical replication slotWAL is kept until the replica receives it
  3. 3
    Take the base backuppg_basebackup with -R on the replica
  4. 4
    Align settings and start the replicaSizing parameters must be equal or higher
  5. 5
    Verify and monitorpg_stat_replication on the source

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 ROLE

4. 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 connections

If 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 postgresql

2. 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_Here

Execute 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/6F0000A0

Flag 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.signal

You 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 value

6. 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 1

Step 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 1

On 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 transaction

Clean 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.

How long does the app wait for a commit?
Asynchronous (default)Source does not wait for the replica
Commit OK
Fastest, but a failover can lose the last few transactions.
SynchronousSource waits for the replica to confirm
Commit OK
Higher commit latency, and writes block if the only synchronous standby goes down.
App waitingWAL still reaching the replica

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.

What a replication slot does to WAL on the source
Replica connected Replica disconnected Slot invalidated
max_slot_wal_keep_size
WAL kept on the source disk

The slot tracks what the replica has received, so WAL is kept only until it is acknowledged.

The slot keeps holding WAL for the missing replica, so the retained WAL keeps growing.

The cap is reached. PostgreSQL invalidates the slot and frees the WAL, so the source disk is protected.

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.

  1. 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.
  2. Keep versions aligned during upgrades: upgrade replicas first, then the source, within the same major version.
  3. Alert on lag and on pg_stat_replication being empty, which means the replica is disconnected.

Troubleshooting Common Issues

Observed Error Symptom
Root Cause & Remediation
pg_basebackup: directory exists but is not empty
Root Cause: Default data cluster already initialized.
Fix: Clear destination on the replica: rm -rf /var/lib/postgresql/16/main/*
FATAL: no pg_hba.conf entry for replication connection
Root Cause: Source rejecting connection credentials.
Fix: Add host replication replicator 172.16.20.12/32 scram-sha-256 to source pg_hba.conf and reload.
password authentication failed for user "replicator"
Root Cause: Invalid password or restrictive `.pgpass` file.
Fix: Verify credentials in ~/.pgpass and verify permissions are locked to chmod 0600.
requested WAL segment ... has already been removed
Root Cause: Primary recycled WAL before standby received it.
Fix: Create and attach a replication slot (replica1_slot) to guarantee retention on source.
recovery aborted because of insufficient parameter settings
Root Cause: Standby parameter lower than source.
Fix: Increase max_connections, max_prepared_transactions, or max_locks_per_transaction on replica to be ≥ primary.
FATAL: could not connect to primary: Connection refused / timed out
Root Cause: Network listener inactive or firewall blocking 5432.
Fix: Ensure listen_addresses = '*' in source postgresql.conf and verify security groups open TCP 5432.

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/7200AA60

Promotion 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:

  1. Configure primary networking, authentication, and replication users.
  2. Create physical replication slots to protect WAL retention.
  3. Pull an online base backup using pg_basebackup -R.
  4. Align configuration parameters and start the replica instance.
  5. 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.

No items found.

About the Author

CTO of Mydbops, leading the engineering behind its managed database services and agentic platforms for databases. He has 16+ years of Linux and open-source experience, from kernel tuning to MongoDB performance and automation.

Topic covered

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.