PITR Restores to Wrong Recovery Target

Scenario

A developer accidentally deleted 50,000 rows from the orders table at 14:23 UTC. The DBA runs PITR targeting recovery_target_time = '2024-01-15 14:20:00 UTC'. After restore, the database comes up but shows the delete has already happened — the table still has fewer rows. The DBA targeted 3 minutes before the delete but PITR overshot past it.

How to Identify

Conditions:

• recovery_target_time set to a time before the deletion, but deleted rows are still missing
• recovery_target_inclusive = on (default) — includes the transaction AT the target time
• Archived WAL files have gaps — some WAL segments were not archived
• Timezone mismatch between recovery_target_time and server timezone
• recovery_target_action not set — database pauses at target waiting for pg_wal_replay_resume()

Analysis Steps

-- After restoring: check if database paused at recovery target
SELECT pg_is_in_recovery();
-- true = still in recovery mode

SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-- Shows current replay position and timestamp

-- Check recovery target settings in postgresql.conf/recovery.conf
-- cat $PGDATA/postgresql.conf | grep recovery_target
-- recovery_target_time = '2024-01-15 14:20:00 UTC'  ← target time
-- recovery_target_inclusive = on                      ← includes this exact second

-- Check recovery_target_action
-- recovery_target_action = 'pause'   ← stops replay, waits for manual promotion
-- recovery_target_action = 'promote' ← promotes immediately (careful!)
-- recovery_target_action = 'shutdown' ← shuts down after reaching target

-- Verify WAL archive has no gaps
-- ls /wal_archive/ | sort | awk 'NR>1{diff=$1-prev; if(diff>1)print "GAP at " $1}{prev=$1}'

-- Check pg_wal_replay_pause status
SELECT pg_get_wal_replay_pause_state();
-- 'paused' = recovery stopped at target, waiting for pg_wal_replay_resume()
-- 'not paused' = not in paused recovery

Pitfalls

• recovery_target_inclusive = on (default) applies all transactions up to and including the target time. If the delete happened at exactly 14:20:00, it will be included.
• Timezone confusion: recovery_target_time uses the server’s log_timezone if no timezone is specified. UTC offset errors shift the target by hours.
• WAL gaps in the archive stop PITR at the gap — recovery cannot skip a missing segment.
• recovery_target_action = 'promote' immediately promotes without human review — use 'pause' in production to verify data before promoting.
• After promoting from PITR, the old timeline is abandoned. To try again at a different target, you must start from the base backup again.

Resolution Approach

Use recovery_target_inclusive = off to stop BEFORE the target transaction. Always specify timezone explicitly in recovery_target_time. Use recovery_target_action = 'pause' so you can inspect data before committing to promotion. Test PITR regularly on a non-production copy.