This parameter specifies the transaction ID up to which recovery will proceed.
At a glance
| Property | Value |
|---|---|
| Parameter | recovery_target_xid |
| Category | Write Ahead Log |
| Default | (see documentation) |
| Value type | string |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 12, 13, 14, 15, 16, 17, 18, 19 (added in 12) |
What it does
This parameter specifies the transaction ID up to which recovery will proceed. Keep in mind that while transaction IDs are assigned sequentially at transaction start, transactions can complete in a different numeric order. The transactions that will be recovered are those that committed before (and optionally including) the specified one. The precise stopping point is also influenced by recovery_target_inclusive.
The value can be specified as either a 32-bit transaction ID or a 64-bit transaction ID (consisting of an epoch and a 32-bit ID), such as the value returned by pg_current_xact_id(). When a 64-bit transaction ID is provided, only its 32-bit transaction ID portion is used as the recovery target. For example, the values 4294968296 (epoch 1) and 8589935592 (epoch 2) both refer to the same 32-bit transaction ID, 1000.
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW recovery_target_xid; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'recovery_target_xid';.
Tuning guidance
This is a point-in-time-recovery control, used only while restoring a backup, not during normal operation. Set it in the recovery configuration to define the exact stopping point or restore action you want, perform the recovery, then remove it. It has no effect on a normally running primary.