Recovery targets and the action taken on reaching one

The previous lesson used one target and one action. There are five kinds of target and three actions, and the choice among them is usually made under pressure with incomplete information.

The five targets

Only one may be set. Setting two is a startup error, not a precedence rule.

recovery_target = 'immediate' deserves attention: it stops as soon as the backup is internally consistent and replays no further. That is how you restore “the base backup, as taken” — useful for verifying a backup without needing the archive to be complete.

recovery_target_name requires forethought:

-- run this before a risky migration
SELECT pg_create_restore_point('before_v42_migration');

The name is written into WAL. Recovering to it is exact, needs no timestamp arithmetic, and is immune to clock questions. If your change process includes anything irreversible, this one line makes the recovery target unambiguous.

recovery_target_inclusive

The default is on, meaning “stop after the target”. It applies to recovery_target_time, _xid and _lsn.

The same transaction, two settings, opposite outcomes:

$ recovery_target_xid = '756'
recovery_target_inclusive = on

LOG:  recovery stopping AFTER commit of transaction 756, time 2026-08-27 21:22:51.345091+00

SELECT count(*) FROM pg_class WHERE relname = 'ledger';
0                                  <- the DROP was INCLUDED

$ recovery_target_time = '2026-08-27 21:22:48.247655+00'

LOG:  recovery stopping BEFORE commit of transaction 756, time 2026-08-27 21:22:51.345091+00

SELECT count(*) FROM ledger;
1500                               <- the table survived

One word in the log — AFTER or BEFORE — reports which side of the boundary you landed on, and it is the fastest way to confirm the recovery did what you intended before anyone looks at the data.

The three actions

recovery_target_action controls what happens on arrival.

pause (the default)

Stop at the target and hold. The cluster is read-only, pg_is_in_recovery() stays true, and the server says so:

LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

Measured on 18.6: boot_val is pause, and a recovery that reaches its target with the setting absent pauses there. It is the right default and the right choice — it is the only action that lets you look at the data before the decision becomes permanent.

pause requires hot_standby to be on, which it is by default. With hot standby off it degrades to shutdown.

promote

End recovery, select a new timeline, open for writes. Correct when you are confident in the target — and it is the behaviour you get without reaching a target at all, because recovery that runs to the end of the archive promotes regardless of this setting.

shutdown

$ pg_ctl -D /restore4 start

waiting for server to start..... done
server shut down because of recovery target settings

LOG:  recovery stopping before commit of transaction 756
LOG:  shutdown at recovery target
LOG:  database system is shut down

$ pg_ctl -D /restore4 status
pg_ctl: no server running

$ ls /restore4/recovery.signal
/restore4/recovery.signal            <- STILL PRESENT

The cluster reaches the target and stops without accepting a single connection. recovery.signal is retained, so the configuration can be edited and the cluster restarted to try a different target.

This is the right action when the data directory is going to be copied or snapshotted at the target rather than queried, and when you want a guarantee that nothing connects before you have decided.

pause

Recovery stops and the cluster stays in recovery, accepting read-only connections.

$ SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();

LOG:  recovery stopping before commit of transaction 756
HINT:  Execute pg_wal_replay_resume() to promote.

pg_is_in_recovery | pg_get_wal_replay_pause_state
-------------------+-------------------------------
t                 | paused

From a pause you can inspect, then decide:

• SELECT pg_wal_replay_resume(); — continue to the end, or promote if the target was reached.
• SELECT pg_promote(); — end recovery here.
• Shut down, change the target, start again.

Choosing a target under pressure

You rarely know the exact instant. A workable order:

1. Find the transaction if you can. Application logs, audit tables, pg_stat_statements. An xid is exact where a timestamp is a guess.
2. Otherwise bracket the time. Choose a target you are confident is before the damage, even if it loses more than necessary.
3. Restore with pause or shutdown. Look before promoting.
4. If you overshot or undershot, do it again with a different target. The failed attempt lives on its own timeline and destroys nothing, as lesson XIII-06 established.

What to take from this

• Five target kinds; only one may be set. immediate restores the backup as taken.
• pg_create_restore_point() before risky work makes the target exact.
• recovery_target_inclusive defaults to on — after the target. Measured: the same transaction included or excluded, reported by one word in the log.
• promote opens; shutdown stops before any connection; pause allows inspection.
• A paused recovery can hold AccessExclusiveLock on the object you came to inspect. Measured on the startup process.
• A target beyond the end of the archive does not promote silently: recovery raises FATAL: recovery ended before configured recovery target was reached and the cluster does not come up. pg_ctl may still print “server started”.

Cross-course references

• Linux for Production Sysadmins — Part XLIX (Restore) covers verifying a restore before it is promoted to being the system of record, which is exactly what pause exists to allow.
• Git, CI/CD & GitOps — Part LX (Forward fix versus rollback) covers the same decision in delivery terms: recovering past the mistake is a rollback that also discards everything good after it.

Quiz