In a PostgreSQL point-in-time recovery, what do recovery_target_action's pause, promote and shutdown do at the target, and why is pause the default?
answer
- look before you open
- pause leans on hot_standby
- replay moves forward, never back
- pg_wal_replay_resume ends recovery here
- recovery_target_inclusive decides the edge
basics
~20 spause holds the server read-only at the target for inspection; promote ends recovery and opens for writes on a new timeline; shutdown stops the server there. Pause is the default because replay can move forward but never back.
solid answer
~50 sWith the default `pause`, replay stops at the target and, because `hot_standby` is on by default, the server accepts read-only queries, so you can check that the deleted rows are back and nothing after the mistake is present. If the point is right, `pg_wal_replay_resume()` ends recovery there, or you call `pg_promote()`. If it is too early, shut down, set a later target and restart; replay continues forward. If it is too late, restore the base backup again, because WAL cannot be un-applied. `promote` opens for writes immediately on a new timeline, which suits proven, scripted restores. `shutdown` stops at the target and keeps `recovery.signal`, so the next start shuts down again until the configuration changes. With `hot_standby = off`, `pause` acts like `shutdown`. If archived WAL runs out before the target, the server stops with a FATAL error.
code
sql · 6 linesSELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();
SELECT count(*) FROM legal_documents WHERE matter_id = 4471;
-- the point is right: end recovery and open for writes
SELECT pg_wal_replay_resume();go deeper
Recall the three values of recovery_target_action and that pause is the default.
Explain what pause allows, why it depends on hot_standby, and what pg_wal_replay_resume() does once replay has stopped at the target.
Show the decision loop: verify while paused, move a too-early target forward, rebuild from the base backup when too late, and use an xid target with inclusive off for an exact stop.
Decide when an automated restore may use promote, and which checks must pass before a recovered copy replaces or feeds production.
## Where the action fits A PostgreSQL point-in-time recovery makes two decisions: **where** to stop (`recovery_target_time`, `recovery_target_xid`, `recovery_target_lsn`, `recovery_target_name` or `recovery_target = 'immediate'`) and **what to do** on arrival, set by `recovery_target_action`. Like the target settings, the action is read only at server start, and it has no effect when no target is set. ## The three actions | Value | At the target | `recovery.signal` | Typical use | |---|---|---|---| | `pause` (default) | replay stops; read-only queries allowed | kept while paused | a person verifies the point | | `promote` | recovery ends; server opens for writes on a new timeline | removed | a scripted restore to a proven target | | `shutdown` | the server stops at the target | kept | hand the stopped instance to a later step | Three rules qualify the table: - `pause` relies on `hot_standby`, which is `on` by default. With `hot_standby = off`, `pause` behaves like `shutdown`. - If a promotion is already under way when the target is reached, `pause` behaves like `promote`. - After `shutdown`, `recovery.signal` is not removed, so the next start ends in an immediate shutdown again unless you remove the file or change the configuration. The instance can still replay more WAL later, and on its next start it must replay at least the WAL written since its last restartpoint. ## Why pause is the safe default Replay only moves forward. A server paused at the target can still go further, but nothing can un-apply WAL. 1. Recovery pauses at `2026-10-02 14:31:00+00`. You query the legal-documents data: the documents the accidental `DELETE` removed are present, and nothing written after it is. 2. If the point is right, call `pg_wal_replay_resume()`, which at the target ends recovery and opens the server for writes, or call `pg_promote()`; a promotion requested while paused ends the pause and continues. 3. If the point is too **early**, shut down, set a later target and start again: replay continues forward from where it stopped. 4. If the point is too **late**, the only way back is to restore the base backup again and recover to an earlier target. With `promote`, the check in step 1 happens after the database is already writable and applications may have reconnected. That is why `promote` belongs in scripted restores whose target has already been proven. ## Precision at the edge `recovery_target_inclusive` (default `on`) decides what happens to the transaction exactly at the target. It applies to the time, xid and LSN targets, not to a named restore point. - `on` stops **just after** the target: a commit at exactly the target time, the target transaction itself, or the record at the target LSN is replayed. - `off` stops **just before** it. - For the legal-documents incident the cleanest target is the `DELETE` itself. Find its transaction ID, for example with `pg_waldump --rmgr=Transaction` over the archived segments, then set `recovery_target_xid` to it with `recovery_target_inclusive = off`. Every transaction that committed before it is kept, even one with a higher ID, because transactions can commit in a different order from the one in which their IDs were assigned. ## Choosing the action for the job - **Incident recovery driven by a person**, such as the legal-documents `DELETE`: keep `pause`. Someone has to confirm the point before anything is written. - **Copying lost rows back into a live database:** a paused copy may never need promoting. Query it read-only, export the rows you need, and discard it. - **A scripted restore test** whose target is fixed in advance: `promote` lets the script run its checks against an open server without waiting for a person. - **A pipeline that hands the stopped data directory to another step**, for example a storage snapshot: `shutdown`, remembering that `recovery.signal` stays behind. ## When the target is never reached - If archived WAL runs out before the target, the server stops with FATAL *recovery ended before configured recovery target was reached*. That usually means a missing segment or a mistyped target, and it is much safer than silently opening at the end of the archive. - If the target lies before the restored backup became consistent, recovery fails with *requested recovery stop point is before consistent recovery point*. - From SQL, `pg_is_in_recovery()` returns true while the server is recovering, and `pg_get_wal_replay_pause_state()` returns `paused` once replay has actually stopped at the target.
- Why would you target the accidental DELETE with recovery_target_xid and recovery_target_inclusive = off rather than a timestamp?A time target stops on commit-record times, so another transaction committing in the same instant is hard to separate from the DELETE. If you can find the DELETE's transaction ID, for example with `pg_waldump` filtered to the Transaction resource manager, set `recovery_target_xid` to it and `recovery_target_inclusive = off`: replay stops just before its commit and keeps everything that committed earlier.
- What does PostgreSQL's recovery_target = 'immediate' do, and when is it the right choice?It ends recovery as soon as the restored backup reaches a consistent state, which for an online base backup is the point where the backup finished. It is the right choice when you want a working copy of the backup itself, for example to check it restores, without replaying any later archived WAL.
- What happens if someone runs pg_promote() while a PostgreSQL recovery is paused at its target?The pause ends and promotion continues: the server stops replay where it is, switches to a new timeline and opens for writes. That is equivalent to accepting the current point, so do it only after the data has been checked.
- pg_wal_replay_resume() at a paused target keeps replaying to the end of the archive
- If the paused point is too late, set an earlier target and restart to rewind
- pause behaves the same whether or not hot_standby is enabled
- If the archive runs out before the target, the server just opens at the last WAL
- recovery_target_inclusive = on stops just before the target transaction