Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-pitr~45 min

A point-in-time recovery restored the deleted rows and then replayed the deletion, and the second attempt had to start from the base backup again

Reported symptoms

  • ●A migration script issued an unqualified DELETE at 14:07 and removed 5000 rows from the ledger table
  • ●The team began a point-in-time recovery onto a spare host, targeting 14:06
  • ●The recovery completed, the server became writable, and the ledger table was still missing the 5000 rows
  • ●A second attempt targeting 14:05 was started against the same restored directory and failed immediately
  • ●The team restored the base backup again, which took fifty minutes, before the second attempt could begin
  • ●A third attempt targeting a time before the base backup failed with recovery ended before configured recovery target was reached
  • ●Total elapsed time from the DELETE to correct data was four hours and twenty minutes

Evidence

  • · The first recovery configuration set recovery_target_time and did not set recovery_target_action
  • · recovery_target_action has a boot value of pause, not promote, so the missing setting was not the cause: measured on 18.6, a recovery that reaches its target with the setting absent pauses and logs pausing at the end of recovery
  • · The target was never reached, so no action applied and recovery ran to the end of the archive
  • · The first recovery log shows starting point-in-time recovery, then redo done, then selected new timeline ID: 2, then archive recovery complete
  • · The recovery_target_time of 14:06 was taken from the migration job log, which records local time, while the database records UTC
  • · The second attempt failed because the restored directory had already been promoted and was no longer a recovery target
  • · The third attempt log ends with FATAL: recovery ended before configured recovery target was reached, followed by shutting down due to startup process failure
  • · In that third case pg_ctl reported could not start server and exited 1, but in an earlier attempt on a slower recovery pg_ctl printed server started and exited 0 while the startup process failed moments later
  • · A recovery run with recovery_target_action set to pause stopped at the target with pg_is_in_recovery true and pg_get_wal_replay_pause_state paused, and the data was inspectable before any decision was made
Diagnosis and resolutionclick to reveal

Root cause

The first recovery did exactly what it was configured to do, and the configuration contained no opportunity to check the result before committing to it. The obvious explanation is wrong, and it is worth naming because it is widely repeated: `recovery_target_action` does **not** default to `promote`. Measured on 18.6, its boot value is `pause`, and a recovery that reaches its target with the setting absent stops there and says so — `pausing at the end of recovery`, with `HINT: Execute pg_wal_replay_resume() to promote.` The action never applied, because **the target was never reached**. A recovery target that lies beyond every record in the archive is not a target at all: recovery replays to the end of the WAL, finds nothing more to apply, and promotes. That is what the log shows — `redo done`, then `selected new timeline ID: 2` — and there is no `recovery stopping before` line anywhere in it, which is the tell. The target itself was wrong. `recovery_target_time` was set to 14:06 from the migration job's log, which records local time, while PostgreSQL interprets the value in the session's time zone and records events in UTC. The recovery therefore replayed past the `DELETE` and reproduced it faithfully. The recovery worked; the target did not. Those two faults compound. A wrong target is an ordinary mistake and easily corrected — if the cluster is paused, you look, you see the rows are missing, you adjust and continue. Once promoted, the only way to try a different target is to restore the base backup again, which cost fifty minutes each time. The third attempt exposes a separate trap. Targeting a time before the base backup produces `FATAL: recovery ended before configured recovery target was reached`. That is PostgreSQL refusing to pretend: it replayed everything it had and never reached the target, so it will not present the result as a successful recovery to that point. This is correct and it is a hard boundary — a recovery cannot reach a moment earlier than the backup it starts from. And `pg_ctl` cannot be trusted as the signal. In the third attempt it reported `could not start server` and exited 1. In an earlier attempt on a slower recovery it printed `server started` and exited 0 while the startup process failed moments afterwards — because `pg_ctl` stops waiting once the postmaster is up, and the startup process can fail after that. A recovery is confirmed by connecting and by reading the log, never by an exit status.

Remediation

Always set `recovery_target_action = 'pause'`. This is the single most valuable setting in a point-in-time recovery, and it costs nothing: ```ini restore_command = 'cp /archive/%f %p' recovery_target_time = '2026-08-28 14:06:00+00' recovery_target_action = 'pause' ``` Note the explicit UTC offset. Take the target from the database's own record of the event — the server log, or `pg_stat_activity` if you caught it live — not from an application log in local time. If you must use a local time, convert it and state the offset in the value. Create `recovery.signal` in the restored data directory. Its presence is what makes this a recovery rather than a crash restart: ```bash touch /var/lib/postgresql/18/restore/recovery.signal ``` Start the server and read the log rather than the exit status: ```bash grep -E 'starting point-in-time recovery|recovery stopping|pausing at the end|redo done|FATAL' \ /var/lib/postgresql/18/restore/log/postgresql.log ``` A recovery that reached its target and paused says so: ```text LOG: starting point-in-time recovery to 2026-08-28 06:04:03.779666+00 LOG: consistent recovery state reached at 0/39000120 LOG: recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324+00 LOG: pausing at the end of recovery ``` Now inspect, while you still have every option. The cluster is read-only and paused, and you can look at anything: ```sql SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(), pg_last_wal_replay_lsn(); SELECT count(*) FROM ledger; ``` If the data is right, promote and accept the outcome: ```sql SELECT pg_promote(); ``` If it is not, you have two choices and both are cheap compared with restoring again. Change `recovery_target_time` to a later value and restart the server, which continues replaying from where it is; or, to go **earlier**, restore the base backup again — replay cannot run backwards. If you see `recovery ended before configured recovery target was reached`, the target is beyond the end of your archive or before the base backup. Check both ends: ```sql -- on the source cluster SELECT last_archived_wal, last_archived_time FROM pg_stat_archiver; ``` ```bash ls /archive | tail -3 # how far the archive extends cat /var/lib/postgresql/18/restore/backup_label # where the backup starts ```

Verification

The paused cluster contains the data you expected, checked before promotion. On the measured recovery the table that a `DROP` had removed was present again and the 5,000 deleted rows were back, giving 15,001 rows against the 10,000 that existed at backup time. ```sql SELECT count(*) AS total, count(*) FILTER (WHERE created_at < :backup_time) AS before_backup FROM ledger; ``` The log contains `recovery stopping before commit of transaction N, time ...`. That line names the exact transaction the recovery stopped at, which is the strongest available statement of where you landed. After promotion, `pg_is_in_recovery()` returns `false`, the log shows `selected new timeline ID` and `archive recovery complete`, and a write succeeds: ```sql SELECT pg_is_in_recovery(); INSERT INTO ledger ... ; ``` The new timeline history file exists and records the recovery target, which distinguishes this cluster's history from a plain failover: ```bash cat pg_wal/00000002.history ``` A PITR history file names the target — `before 2026-08-27 21:22:51.345091+00` — where a promotion writes `no recovery target specified`. **The recovery was timed.** Restoring the base backup took fifty minutes here, and that number belongs in the disaster-recovery plan rather than being discovered during an incident.

Prevention

**Set `recovery_target_action = 'pause'` in every recovery, without exception.** It is free, it is reversible, and its absence cost fifty minutes per wrong guess. **Take the recovery target from the database's own log**, in UTC, with an explicit offset in the value. An application log in local time is the wrong source and it is the most common way to miss a target. **Choose a target slightly before the event, then walk forward.** Continuing a paused recovery to a later target is a restart; going earlier means restoring again. Undershooting is cheap and overshooting is not. **Never trust `pg_ctl`'s exit status for a recovery.** It stops waiting once the postmaster is up, and the startup process can fail afterwards. Confirm with a connection and with the log. **Rehearse a point-in-time recovery on a schedule**, and time each phase. A team that has never done one will discover the pause setting, the time zone, and the fifty minutes during a real incident, in that order. **Keep the base backup cadence tied to the recovery time objective.** Every recovery starts from a base backup and replays forward; the older the backup, the longer every attempt takes and the more WAL must survive intact. **Verify the archive is complete before you need it.** A recovery is bounded at one end by the base backup and at the other by the last archived segment, and a gap anywhere between them stops replay at the gap. **Write the runbook with the exact configuration file contents**, including `recovery_target_action`, `restore_command`, and the `recovery.signal` step. A recovery is not the moment to reconstruct a configuration from memory.

Reported symptoms

A migration script issued an unqualified DELETE at 14:07 and removed 5,000 rows from ledger.

The team began a point-in-time recovery onto a spare host, targeting 14:06. The recovery completed, the server became writable, and the rows were still missing.

A second attempt targeting 14:05 was started against the same restored directory and failed immediately. The base backup had to be restored again — fifty minutes — before the second attempt could begin.

A third attempt, targeting a time before the base backup, failed with recovery ended before configured recovery target was reached.

Total elapsed time from the DELETE to correct data: four hours and twenty minutes.

Evidence provided

The first recovery configuration set recovery_target_time and did not set recovery_target_action. That omission is a red herring, and the log below is what proves it: there is no recovery stopping before line, so the target was never reached and no action was ever taken.

Read-only / Safewhat a recovery that promotes itself looks like in the log
$ grep -E 'timeline|archive recovery|redo done' postgresql.log
2026-08-28 06:04:46.302 UTC [3143] LOG:  redo done at 0/3A19A2B8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 23.05 s
2026-08-28 06:04:46.329 UTC [3143] LOG:  selected new timeline ID: 2
2026-08-28 06:04:46.352 UTC [3143] LOG:  archive recovery complete
2026-08-28 06:04:46.358 UTC [3137] LOG:  database system is ready to accept connections

The 14:06 target came from the migration job’s log, which records local time. PostgreSQL records UTC.

Read-only / Safethe third attempt: a target the archive cannot reach
$ pg_ctl -D /restore start && tail -3 postgresql.log
2026-08-28 06:05:08.073 UTC [3255] LOG:  redo done at 0/3A1A7E58 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
2026-08-28 06:05:08.073 UTC [3255] FATAL:  recovery ended before configured recovery target was reached
2026-08-28 06:05:08.074 UTC [3249] LOG:  shutting down due to startup process failure

And a recovery configured with recovery_target_action = 'pause':

Read-only / Safethe same recovery, paused at the target, with everything still inspectable
$ grep -E 'starting point-in-time|consistent|recovery stopping|pausing' postgresql.log
2026-08-28 06:04:23.247 UTC [3143] LOG:  starting point-in-time recovery to 2026-08-28 06:04:03.779666+00
2026-08-28 06:04:23.269 UTC [3143] LOG:  consistent recovery state reached at 0/39000120
2026-08-28 06:04:23.274 UTC [3143] LOG:  recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324+00
2026-08-28 06:04:23.274 UTC [3143] LOG:  pausing at the end of recovery
 in_recovery | pause_state | replayed_to 
-------------+-------------+-------------
 t           | paused      | 0/3A19A2B8

 total | before_backup | after_backup | marker 
-------+---------------+--------------+--------
 15001 |         10000 |         5000 |      1

Work the evidence before reading on

  1. The first recovery “completed” and the rows were missing. What did it actually do?
  2. Why could the second attempt not reuse the restored directory?
  3. recovery ended before configured recovery target was reached — what are the two ways to produce that?
  4. What would recovery_target_action = 'pause' have changed?

Root cause

A target that is never reached promotes, whatever the action says

The target was wrong, and that should have been cheap

14:06 came from a log recording local time; PostgreSQL records UTC. The recovery replayed past the DELETE and reproduced it faithfully.

The recovery worked. The target did not.

Two ways to fail to reach a target

recovery ended before configured recovery target was reached means PostgreSQL replayed everything it had and never got there. Either the target is beyond the end of the archive, or it is before the base backup — and no recovery can reach a moment earlier than the backup it starts from.

PostgreSQL refuses to present that as a successful recovery to the requested point, which is correct.

Resolution

Always set recovery_target_action = 'pause'. It is the single most valuable setting in a point-in-time recovery and it costs nothing:

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-28 14:06:00+00'
recovery_target_action = 'pause'

Note the explicit UTC offset, and take the target from the database’s record of the event — the server log — not from an application log in local time.

Create recovery.signal, whose presence is what makes this a recovery rather than a crash restart:

touch /var/lib/postgresql/18/restore/recovery.signal

Start the server and read the log:

grep -E 'starting point-in-time recovery|recovery stopping|pausing at the end|redo done|FATAL' \
  /var/lib/postgresql/18/restore/log/postgresql.log

Then inspect, while you still have every option:

SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(), pg_last_wal_replay_lsn();
SELECT count(*) FROM ledger;

If the data is right, promote:

SELECT pg_promote();

If it is not: change recovery_target_time to a later value and restart, which continues replaying from where it is. To go earlier, restore the base backup again — replay cannot run backwards.

If you see recovery ended before configured recovery target was reached, check both ends:

-- on the source cluster
SELECT last_archived_wal, last_archived_time FROM pg_stat_archiver;
ls /archive | tail -3
cat /var/lib/postgresql/18/restore/backup_label

Verification

The paused cluster contains the data you expected, checked before promotion. On the measured recovery, the table a DROP had removed was present again and the 5,000 deleted rows were back — 15,001 rows against the 10,000 that existed at backup time.

The log contains recovery stopping before commit of transaction N, time ..., which names the exact transaction you stopped at. That is the strongest available statement of where you landed.

After promotion, pg_is_in_recovery() is false, the log shows selected new timeline ID and archive recovery complete, and a write succeeds.

The history file records the target, which distinguishes a PITR from a plain failover months later:

cat pg_wal/00000002.history

A PITR names the target — before 2026-08-27 21:22:51.345091+00 — where a promotion writes no recovery target specified.

The recovery was timed. Restoring the base backup took fifty minutes, and that number belongs in the disaster-recovery plan rather than being discovered during an incident.

Prevention

Set recovery_target_action = 'pause' in every recovery, without exception. Free, reversible, and its absence cost fifty minutes per wrong guess.

Take the target from the database’s own log, in UTC, with an explicit offset. An application log in local time is the most common way to miss a target.

Choose a target slightly before the event and walk forward. Undershooting is cheap; overshooting is not.

Never trust pg_ctl’s exit status for a recovery.

Rehearse a PITR on a schedule and time each phase. A team that has never done one will discover the pause setting, the time zone, and the fifty minutes during a real incident, in that order.

Tie base backup cadence to the recovery time objective. Every recovery starts from a base backup and replays forward.

Verify the archive is complete before you need it. A recovery is bounded by the base backup at one end and the last archived segment at the other, and a gap between them stops replay at the gap.

Write the runbook with the exact configuration file contents. A recovery is not the moment to reconstruct a configuration from memory.