PostgreSQL standby stops applying WAL during long-running transactions on primary after pg_basebackup restore

Environment

Primary: PostgreSQL 14.5 on Debian 11, heavy OLTP workload with frequent long-running reporting transactions; Standby: streaming replication via walreceiver using primary_conninfo (replication user), recovery.signal present after pg_basebackup; wal_level = replica, synchronous_commit = on, archive_mode = on with archive_command set to copy WALs to shared NFS.

Symptoms

Standby initially replicates WAL and is caught up. When primary executes long-running SELECTs with REPEATABLE READ or application-level idle-in-transaction lasting >30 min, WAL shipping to standby continues but standby's replay_lag increases and WAL application halts until primary transaction commits. During stall, queries on standby show waiting on 'replay' and pg_stat_replication on primary shows 'write' and 'flush' positions ahead of 'replay'.

Observations

On standby, pg_stat_activity shows walreceiver active but walreceiver process reports 'waiting for new data' after a particular WAL location. primary logs show no WAL flush errors. Archive of WAL segments succeeds to NFS. There are no network interruptions; bandwidth underutilized. When the long-running transaction on primary commits, standby quickly applies backlog. Prometheus metrics show wal_receive_lag spikes in lockstep with application transactions.

Already attempted

Increased wal_receiver_timeout and work_mem on standby — no effect. Tested with synchronous_commit = local on primary to reduce flush waits — behavior unchanged. Performed a base backup again and rebuilt standby — same pattern. Stopped the long-running query on primary and standby catches up immediately. Temporarily reduced max_wal_senders — no change.

Question

Why does the standby defer WAL application while a long-running transaction remains open on the primary, and what safe configuration adjustments or operational practices will allow near-real-time replay without risking data consistency?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Application holds long-running transactions open, delaying commit and therefore preventing standby from applying subsequent WAL that depends on commit ordering. 95%

Replication slot or resource exhaustion on standby preventing continuous application (e.g., walreceiver blocked by synchronous replication wait or checkpoint saturation). 20%

Hot standby feedback disabled causing primary to vacuum aggressively and create conflict that stalls replay until safe, or conversely feedback enabled causing bloat — configuration mismatch. 10%

Summary

Standby deferring replay while primary has an open long-running transaction suggests WAL generation or commit ordering interactions, not network issues. PostgreSQL ensures read consistency: standby cannot apply WAL corresponding to changes that are part of an in-progress transaction until commit is observed. However, WAL is sent; replay waits if there are dependencies (e.g., xid mapping, hint bits) or if standby is prevented from applying WAL due to hot_standby_feedback being off and vacuum-related conflicts. The immediate catch-up after commit indicates expected behavior but long stalls imply application design holding transactions open too long.

Suggested Fixes

Diagnostic step 1

Confirm if the standby is blocked waiting for commit-related WAL by inspecting pg_stat_activity and the WAL LSN positions on both servers (pg_current_wal_lsn, replay_lsn). Capture timeline and events around the start and commit of long transactions.

Diagnostic step 2

As an operational mitigation, avoid application patterns that hold transactions open for long durations; convert long reporting SELECTs to snapshot exports or use secondary-safe reporting transactions that acquire fewer resources.

Diagnostic step 3

Consider enabling hot_standby_feedback=true temporarily to reduce query conflict-induced cancellations but be aware this can prevent vacuum on primary leading to bloat; monitor autovacuum and disk usage if enabled.