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

Parameter	Stops at
`recovery_target_time`	Timestamp (use `timestamptz` string)
`recovery_target_xid`	Transaction ID
`recovery_target_lsn`	Log sequence number
`recovery_target_name`	`pg_create_restore_point` label
`recovery_target`	Alias; sets generic target string
`recovery_target_inclusive`	Include (`on`) or exclude (`off`) matching record

recovery_target_action

Value	Behavior
`pause`	Pause recovery at target; wait for `pg_wal_replay_resume()`
`promote`	End recovery and accept connections
`shutdown`	Stop instance at target (inspect before promote)

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

Alternative	Use When	Don't Use When
`recovery_target_lsn`	You have exact LSN from `pg_walfile_name` tooling	Human operators pick time
pgBackRest `--target`	Unified backup tool	Manual archive dirs only
Flashback table (logical undo)	Small table, recent dump	Cluster-wide mistake

FAQs

Named vs time target?

Named is exact to team marker; time requires log correlation but needs no foresight API call.

Can I recover to latest?

Omit recovery target; recovery replays all available WAL then promote with recovery.signal removed or promote trigger.

recovery_target_xid when?

When logs show exact txid from pg_current_xact_id() snapshot before bug.

Multiple targets set?

PostgreSQL uses one; conflicting settings error or prioritize per docs. Set only one target type.

Patroni PITR?

Patroni can bootstrap new cluster from backup with recovery parameters; not in-place on running leader.

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

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

Parameter	Stops at
`recovery_target_time`	Timestamp (use `timestamptz` string)
`recovery_target_xid`	Transaction ID
`recovery_target_lsn`	Log sequence number
`recovery_target_name`	`pg_create_restore_point` label
`recovery_target`	Alias; sets generic target string
`recovery_target_inclusive`	Include (`on`) or exclude (`off`) matching record

recovery_target_action

Value	Behavior
`pause`	Pause recovery at target; wait for `pg_wal_replay_resume()`
`promote`	End recovery and accept connections
`shutdown`	Stop instance at target (inspect before promote)

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

Alternative	Use When	Don't Use When
`recovery_target_lsn`	You have exact LSN from `pg_walfile_name` tooling	Human operators pick time
pgBackRest `--target`	Unified backup tool	Manual archive dirs only
Flashback table (logical undo)	Small table, recent dump	Cluster-wide mistake

FAQs

Named vs time target?

Named is exact to team marker; time requires log correlation but needs no foresight API call.

Can I recover to latest?

Omit recovery target; recovery replays all available WAL then promote with recovery.signal removed or promote trigger.

recovery_target_xid when?

When logs show exact txid from pg_current_xact_id() snapshot before bug.

Multiple targets set?

PostgreSQL uses one; conflicting settings error or prioritize per docs. Set only one target type.

Patroni PITR?

Patroni can bootstrap new cluster from backup with recovery parameters; not in-place on running leader.

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

Recovery Target Options

Buscar en la documentación

Recovery Target Options