About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.
Some database recoveries follow human or application mistakes: a table dropped in the wrong session, an update without a WHERE clause, a migration that ran against production. For these, a nightly backup is often too coarse. Restoring last night's copy loses everything since.
PostgreSQL's point-in-time recovery (PITR) solves this by combining a physical base backup with the archived write-ahead log (WAL), PostgreSQL's ordered record of every change. Recovery replays the log from the backup forward and stops where you tell it to.
The mechanics are well documented. The decisions are harder: where exactly to stop, how to check that point before the database goes live, and which branch of history you are on if someone has already recovered once. This guide covers those decisions.
Why it matters
Stopping one transaction too late brings the mistake back. Stopping too early loses legitimate work. Promoting a recovered server before checking means new writes land on top of a possibly wrong state, and correcting that later is harder.
Timelines add a second dimension. Completing archive recovery creates a new timeline for subsequent WAL, preserving the identity of the earlier history. If a first recovery attempt was wrong, the WAL from the original history may still be needed, and a careless archive clean-up can remove it.
How it works
The recovery procedure in outline
The continuous archiving chapter gives the full procedure. Its key elements: restore the base backup files, configure how archived WAL is fetched, create recovery.signal in the data directory, and start the server. "The server will go into recovery mode and proceed to read through the archived WAL files it needs." When it finishes, "the server will remove recovery.signal (to prevent accidentally re-entering recovery mode later) and then commence normal database operations."
The documentation also suggests that "you might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful", and closes the procedure with: "Inspect the contents of the database to ensure you have recovered to the desired state. If not, return to step 1."
The WAL is fetched by restore_command, which the Write Ahead Log configuration documentation describes as "the local shell command to execute to retrieve an archived segment of the WAL file series. This parameter is required for archive recovery".
By default, recovery replays everything it can find: "Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time". A normal end shows a "file not found" message, which the documentation calls expected.
Recovery targets
To stop earlier, you set one recovery target. The documentation is strict: "At most one of recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, or recovery_target_xid can be used; if more than one of these is specified in the configuration file, an error will be raised. These parameters can only be set at server start."
The options:
- recovery_target = 'immediate': "recovery should end as soon as a consistent state is reached, i.e., as early as possible. When restoring from an online backup, this means the point where taking the backup ended."
- recovery_target_time: "the time stamp up to which recovery will proceed." The documentation warns that "you cannot use a time zone abbreviation" unless configured, and that the "preferred style is to use a numeric offset from UTC, or you can write a full time zone name".
- recovery_target_name: "the named restore point (created with pg_create_restore_point()) to which recovery will proceed."
- recovery_target_xid: "the transaction ID up to which recovery will proceed". It notes that "while transaction IDs are assigned sequentially at transaction start, transactions can complete in a different numeric order."
- recovery_target_lsn: "the LSN of the write-ahead log location up to which recovery will proceed."
The continuous archiving chapter gives a frank view of which are practical: "As of this writing only the date/time and named restore point options are very usable, since there are no tools to help you identify with any accuracy which transaction ID to use."
Time zones deserve care
For Australian teams, the time zone warning matters. Abbreviations such as AEST and AEDT are exactly what the documentation says not to use without configuration. Use a timestamp with an explicit numeric UTC offset for an unambiguous instant. A full zone name such as Australia/Sydney is accepted, but repeated local times during a daylight-saving transition still need careful interpretation. Check which zone the incident report uses, and whether the application logs record server time or local time.
Inclusive or exclusive
recovery_target_inclusive decides "whether to stop just after the specified recovery target (on), or just before the recovery target (off)". It "controls whether transactions having exactly the target WAL location (LSN), commit time, or transaction ID, respectively, will be included in the recovery. Default is on."
For a mistake, the usual intent is to stop just before it. If the target is the time or transaction of the mistake itself, the default of on includes it. Choose the target and inclusive setting from evidence about the relevant commit or log position, then inspect the recovered state. An approximate incident time can include unrelated transactions and is not proof of the right stopping point.
Pausing to check
recovery_target_action "specifies what action the server should take once the recovery target is reached. The default is pause". The other options are promote, where "the recovery process will finish and the server will start to accept connections", and shutdown, which "will stop the server after reaching the recovery target."
The documentation explains the purpose of pausing: "to allow queries to be executed against the database to check if this recovery target is the most desirable point for recovery." If it is, pg_wal_replay_resume() ends recovery. If not, "shut down the server, change the recovery target settings to a later target and restart to continue recovery."
Two details from the same page:
- "If hot_standby is not enabled, a setting of pause will act the same as shutdown." Without hot standby, you cannot query the paused server.
- "If a recovery target is configured but the archive recovery ends before the target is reached, the server will shut down with a fatal error." A target beyond the available WAL is an error, not a silent partial recovery.
Pausing is the most important safety net in PITR. It lets you confirm the dropped table exists, or the bad update has not happened, before any new transaction is written.
Note the direction: a paused recovery can move forward to a later target, but it cannot move backwards. If the stop point is too late, you start again from the base backup. (our reading, based on the documentation's instruction to choose a "later target" when continuing)
Limits on the stop point
The documentation sets a firm lower bound: "The stop point must be after the ending time of the base backup, i.e., the end time of pg_backup_stop. You cannot use a base backup to recover to a time when that backup was in progress. (To recover to such a time, you must go back to your previous base backup and roll forward from there.)"
And on damaged WAL: "If recovery finds corrupted WAL data, recovery will halt at that point and the server will not start. In such a case the recovery process could be re-run from the beginning, specifying a 'recovery target' before the point of corruption so that recovery can complete normally."
Restore points
pg_create_restore_point(), described in the administration functions documentation, "creates a named marker record in the write-ahead log that can later be used as a recovery target". It warns: "Avoid creating multiple restore points with the same name, since recovery will stop at the first one whose name matches the recovery target."
Creating a uniquely named restore point before risky changes, such as schema migrations, bulk data fixes or application upgrades, gives a precise stop point if the change goes wrong. It creates a WAL marker, not a backup; the base backup and WAL needed to reach that marker must still be retained.
Timelines
The documentation explains timelines with a story worth quoting. Suppose "you dropped a critical table at 5:15PM on Tuesday evening, but didn't realize your mistake until Wednesday noon." You recover to 5:14 PM Tuesday. Later you decide you want Wednesday morning from the original history instead. "You won't be able to if, while your database was up-and-running, it overwrote some of the WAL segment files that led up to the time you now wish you could get back to."
PostgreSQL's answer: "Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery. The timeline ID number is part of WAL segment file names so a new timeline does not overwrite the WAL data generated by previous timelines." In a WAL file name such as 0000000100001234000055CD, "the leading 00000001 is the timeline ID in hexadecimal."
Each new timeline gets a history file showing "which timeline it branched off from and when." These "are necessary to allow the system to pick the right WAL segment files when recovering from an archive that contains multiple timelines", and because they are small, "it's cheap and appropriate to keep them around indefinitely".
The documentation calls the feature "often a lifesaver" when you have to "do several point-in-time recoveries by trial and error until you find the best place to branch off from the old history."
Choosing the timeline
recovery_target_timeline "specifies recovering into a particular timeline." current "recovers along the same timeline that was current when the base backup was taken." latest "recovers to the latest timeline found in the archive" and "is the default." A numeric ID selects a specific branch; hexadecimal IDs taken from WAL file names need a 0x prefix.
The default matters in practice. If an earlier recovery attempt created a newer timeline in the same archive, a new recovery may follow that newer branch if it is reachable from the base backup. If you want the original history, specify current or the original timeline ID. The documentation adds a limit: "You cannot recover into timelines that branched off earlier than the base backup." (our reading on the practical effect of the default)
What to choose and why
These are our recommendations, based on the documentation above.
Before an incident
- Archive WAL continuously and monitor it, so the archive covers the period you may need.
- Keep history files indefinitely and do not let archive clean-up remove WAL from older timelines while a recovery question is still open.
- Create named restore points before planned risky changes, with unique names.
- Enable hot standby on recovery servers, so a paused recovery can be queried.
- Rehearse a time-based recovery on a separate server, including choosing the target and checking it while paused.
During an incident
- Establish the time of the mistake precisely, with its time zone, from application logs, the database log or the user's report. Note how certain the estimate is.
- Recover onto a separate server or directory, not over the production data directory, so the original remains available.
- Choose one evidence-based target and use pause behaviour where hot standby supports inspection.
- Query the paused server. Confirm the lost data is present and the mistake has not happened.
- Move forward if needed, to a later target, in small steps. If you overshoot, start again from the base backup.
- Decide how to use the result: promote the recovered server, or extract the lost data and load it into production. For a single dropped table, extracting it is often less disruptive than replacing the whole cluster.
- Record the timeline the recovered server is on, and keep the archive intact until the incident is closed.
Risks and limits
- Overshooting the target includes the mistake; the only way back is to start again.
- Time zone mistakes move the stop point by hours.
- Promoting without checking lets new writes build on a wrong state.
- Archive clean-up during an incident can remove WAL that a different branch choice would need.
- Transaction ID targets are hard to identify without tooling, as the documentation notes.
- Nothing here was run. Settings are quoted from the documentation for planning.
Sources
- PostgreSQL 18, Continuous Archiving and Point-in-Time Recovery (incl. 25.3.6 Timelines): https://www.postgresql.org/docs/18/continuous-archiving.html
- PostgreSQL 18, Write Ahead Log configuration (archive recovery and recovery target settings): https://www.postgresql.org/docs/18/runtime-config-wal.html
- PostgreSQL 18, System Administration Functions: https://www.postgresql.org/docs/18/functions-admin.html
This article was originally published on .