In PostgreSQL, when the WAL archive already holds a newer timeline from an earlier recovery, what does recovery_target_timeline decide, and when must you change its default?
answer
- the default follows the newest branch
- current means the backup's own timeline
- hex IDs need a 0x prefix
- the second attempt after a PITR
basics
~20 srecovery_target_timeline picks which branch of WAL history recovery replays. The default, latest, follows the newest timeline in the archive; set current or an explicit ID to stay on the original history after an earlier recovery branched it.
solid answer
~50 sEvery completed archive recovery starts a new timeline, and segment names carry the timeline ID, so one archive can hold several histories. `recovery_target_timeline` decides which one replay follows: `latest`, the default, follows the newest timeline found in the archive; `current` stays on the timeline the base backup was taken on; a number names one exactly, with a `0x` prefix for a hex ID taken from a file name. The trap is a second attempt. You recover to 14:31, promote, and that server archives timeline 2 into the same archive. Retrying from the same base backup to 14:31:40 with `latest`, recovery switches to timeline 2 at its fork and stops almost at once, so the original history's commits after 14:31 are never replayed. Set `current` instead. You cannot recover into a timeline that branched before the base backup.
code
ini · 4 lines# second attempt, same 02:00 base backup, original history only
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-10-02 14:31:40+00'
recovery_target_timeline = 'current'go deeper
Recall that each completed recovery starts a new timeline, and that recovery_target_timeline defaults to latest.
Explain latest, current and an explicit ID, how to read the timeline from a segment name, and why hex IDs need the 0x prefix.
Walk through a second recovery attempt on an archive that already holds the first attempt's timeline, and show why current is needed and how you would notice the mistake.
Set the policy for where trial restores may archive and how accepted timelines are recorded, so later recoveries are deliberate rather than lucky.
## Why PostgreSQL has timelines Every time an archive recovery or a standby promotion finishes, PostgreSQL starts a new **timeline**: a new numbered branch of WAL history. The timeline ID is the first eight hex digits of every segment name (in `0000000200000A2B00000041` it is 2), so segments from different branches never overwrite each other in one archive. A small history file such as `00000002.history` records where each branch forked; its format and the promotion mechanics are covered with promotion and timelines. This answer is about the recovery setting that chooses a branch. How does recovery find the newest timeline? It asks `restore_command` (and then `pg_wal/`) for history files with successively higher IDs (`00000002.history`, `00000003.history`, and so on) and stops at the first one the archive does not have. That is why a perfectly normal recovery log shows failed fetches for `.history` files, and why a history file a trial restore left in the archive is enough to change what `latest` means. ## What recovery_target_timeline accepts | Value | Recovery follows | Typical use | |---|---|---| | `latest` (default) | the newest timeline found in the archive | standbys that must follow failovers | | `current` | the timeline the base backup was taken on | re-running a PITR on the original history | | a timeline ID | exactly that branch | returning to a state reached after an earlier recovery | - A hex ID taken from a file name needs a `0x` prefix: for `00000011000000A10000004F`, write `0x11`, which is timeline 17. Server log messages usually print timeline IDs in decimal. - The setting is read only at server start, like the other recovery target settings. - You cannot recover into a timeline that branched off before the base backup was taken. ## The re-recovery trap 1. The legal-documents primary runs on timeline 1, and the 02:00 base backup is on timeline 1. 2. First attempt: restore, set `recovery_target_time = '2026-10-02 14:31:00+00'`, and promote. The restored server switches to timeline 2. Its `postgresql.conf` came from the backup with production's `archive_command`, so after promotion it archives `00000002.history` and timeline-2 segments into the same archive. 3. The check finds that a contract filed at 14:31:30 is missing. You restore the same base backup again, now with `recovery_target_time = '2026-10-02 14:31:40+00'`. 4. With the default `latest`, recovery fetches the history file, switches to timeline 2 at its fork point near 14:31:00, and stops almost at once, because every timeline-2 commit carries a later wall-clock time. The timeline-1 commits between 14:31:00 and 14:31:40 are never replayed, and you land on the first attempt's history again. 5. With `recovery_target_timeline = 'current'` (or `1`), recovery stays on timeline 1 and reaches 14:31:40 as intended. Nothing in the logs calls this an error: recovery reached its target. Checking the data reveals that the wrong branch was followed, and so does the server log: its `restored log file ... from archive` lines name segments beginning `00000002` where you expected only `00000001`. ## Choosing a value - **A single recovery from an archive with one history:** the default `latest` is fine. - **Retrying on the original history after an earlier attempt was promoted:** set `current`, or the ID of the backup's timeline. - **Returning to a state created by an earlier recovery:** set that timeline's ID, and restore a base backup taken before the fork on one of its ancestor timelines, or on that timeline itself. - **A standby:** keep `latest`, so it follows its primary across promotions. ## Keeping experimental restores out of the way - Decide where a restored copy archives before you start it. Giving it its own archive location, or `archive_mode = off` while you experiment, keeps trial timelines out of production's archive. - If trial timelines do land there, keep their history files, which are tiny, and pin `recovery_target_timeline` in every later recovery instead of deleting files by hand. - Record which timeline the accepted recovery created; later recoveries of the repaired database will start from it.
- How do you read which timeline a PostgreSQL WAL segment belongs to, and how do you write it in recovery_target_timeline?The first eight hex digits of a segment name are the timeline ID: `0000000200000A2B00000041` is on timeline 2. In `recovery_target_timeline`, a hex value needs a `0x` prefix, so the 11 in `00000011000000A10000004F` is written `0x11`, which is timeline 17. Log messages usually print the decimal form, so do not copy hex digits from a file name without the prefix.
- Why is latest the right default for a PostgreSQL standby but a hazard for a re-run point-in-time recovery?A standby should follow its primary through failovers, so it must switch to any new timeline that appears in the archive, and `latest` does that. A re-run recovery wants a fixed history instead. Any timeline an earlier attempt added to the archive silently becomes the newest one, and the recovery follows it from its fork point.
- recovery_target_timeline defaults to current, the base backup's own timeline
- A newer timeline's segments overwrite the older timeline's files in the archive
- Recovery can follow a timeline that branched off before the base backup
- The 11 in a WAL file name's timeline digits can be written as plain 11
- recovery_target_timeline can be changed with a reload during a recovery