Recovery Target Options

Recovery targets tell PostgreSQL when to stop WAL replay during PITR. Choose time, transaction ID (XID), LSN, or a named restore point created before risky work.

Recipe

Recover to one second before accidental DROP using timestamp.

# postgresql.auto.conf on restore instance
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-07-09 14:31:58+00'
recovery_target_action = 'promote'

-- Before risky migration on primary
SELECT pg_create_restore_point('before_migration_20260709');

When to reach for this: Precision recovery when you know approximately when the mistake happened or you tagged the moment in advance.

Working Example

Compare target types and inclusive/exclusive boundaries:

-- Named restore point (primary, before change)
SELECT pg_create_restore_point('pre_deploy_v42');

# Option A: named target
recovery_target_name = 'pre_deploy_v42'
recovery_target_action = 'promote'
 
# Option B: timestamp (inclusive by default in PG 18)
recovery_target_time = '2026-07-09 14:31:58+00'
recovery_target_inclusive = on
 
# Option C: transaction ID (advanced)
# recovery_target_xid = '1234567'

-- After recovery completes
SELECT pg_is_in_recovery();  -- false after promote
-- Verify bad change absent
SELECT to_regclass('public.dropped_table');

What this demonstrates:

• pg_create_restore_point records a label in WAL for exact team-known moments.
• recovery_target_time needs timezone-aware literal.
• recovery_target_action = 'promote' opens database for connections after target reached.

Deep Dive

Target Parameters

recovery_target_action

Finding the Right Time

-- On primary (if still up): audit log or application timestamps
SELECT now();
 
-- pg_audit or application logs narrow the DROP second

Use recovery_target_action = 'pause' first if unsure; inspect data, then promote.

Gotchas

• Local time without offset - DST bugs move recovery point. Fix: Always use +00 or explicit zone.
• Target after last archived WAL - Recovery stops at end of available WAL, not target. Fix: Ensure archive chain complete to desired moment.
• Inclusive off by one transaction - Bad row still visible. Fix: Trial restore to scratch; adjust recovery_target_inclusive.
• No restore point before deploy - Fuzzy time recovery only. Fix: Mandatory pg_create_restore_point in change checklist.
• Promote before validation - Production opened with wrong data. Fix: pause + verify on recovery instance first.

Alternatives

FAQs

Related

• PITR Basics - overall flow
• archive_mode & archive_command - WAL supply
• PITR Drill Runbook - practice targets
• Change Management Basics - restore points in changes

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.