FULL AI ANALYSIS

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

Generated Oct 3, 2026, 12:35:03 AM

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

Possible 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%

WAL archiving to shared NFS and restore_command interaction causing standby to wait for missing WAL segments when streaming falls behind (less likely here since WAL is present).

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.

Diagnostic step 4

If missing WAL segments are observed, verify archive_command and restore_command on standby, and ensure permissions and latency to NFS are acceptable. Rebuild standby only if you suspect base backup corruption. If analysis suggests complex replication slot/resource issues or you cannot change application behavior, engage DBA escalation. Provide WAL LSN logs, pg_stat_replication snapshots, and timeline history for specialist review.

Contact • Help • API Docs • Careers • Terms • Privacy • Cookies • Ads