Standby Promotion Failure

Scenario

The primary database goes down. The DBA tries to promote the standby by running pg_ctl promote -D $PGDATA. The command returns success, but the standby log shows it’s still in recovery mode and pg_is_in_recovery() still returns true. The recovery_target_action was set to 'pause' in the configuration — the standby has paused at the recovery target and is waiting for pg_wal_replay_resume() before it will actually promote.

How to Identify

Conditions:

• pg_is_in_recovery() returns true after promotion attempt
• pg_get_wal_replay_pause_state() returns 'paused'
• recovery_target_action = 'pause' configured
• standby.signal file still present
• No postgresql.conf recovery_target_time is set but recovery_target_action = 'pause' remains from a previous PITR exercise

Analysis Steps

-- Check if the standby is still in recovery
SELECT pg_is_in_recovery();
-- true = still in recovery (not promoted yet)

-- Check WAL replay pause state
SELECT pg_get_wal_replay_pause_state();
-- 'paused'     = recovery paused, waiting for pg_wal_replay_resume()
-- 'not paused' = recovery running normally

-- Check current replay position
SELECT
    pg_last_wal_receive_lsn()                   AS received_lsn,
    pg_last_wal_replay_lsn()                    AS replayed_lsn,
    pg_last_xact_replay_timestamp()             AS last_applied_txn,
    pg_is_wal_replay_paused()                   AS replay_paused;

-- Check recovery target settings
SELECT name, setting FROM pg_settings
WHERE name LIKE 'recovery%'
ORDER BY name;

-- Check if standby.signal file exists (presence = in recovery mode):
-- ls -la $PGDATA/standby.signal

Pitfalls

• pg_ctl promote sends a signal to the PostgreSQL process but if recovery_target_action = 'pause', the process is waiting for pg_wal_replay_resume() before it will actually promote.
• Touching the promote trigger file (touch $PGDATA/promote) is equivalent to pg_ctl promote — same limitation applies.
• If recovery_target_time is set to a past timestamp, recovery may have stopped at that time and is waiting for manual intervention.
• After successful promotion: standby.signal is automatically removed and pg_is_in_recovery() returns false.
• Attempting writes on a still-in-recovery standby returns ERROR: cannot execute INSERT in a read-only transaction.

Resolution Approach

If recovery is paused at a target: call pg_wal_replay_resume() to trigger promotion. If recovery_target_action = 'pause' is set globally, remove it from postgresql.conf before the next promotion. Verify promotion with pg_is_in_recovery() = false.