Re: how to identify the timeline of specified recovery_target_time when do multiple PITR

magodo wrote:
> > How can you know how many WAL segments there are on timeline 1, and if
> > there is one that extends past 2018-10-11 12:00:00 or not?
>
> This is the exact problem I want to figure out. My approach is as you said,
> I will parse each archived WAL segment via `pg_xlogdump -r Transaction`,
> and try to find the first least earliest WAL against the specified time.
> This is a linear search, which has complexity of O(n).

That means that the time spent grows linearly, but it is still a lot of time
if there are a lot of WAL archives.

> So if you want to recover to that point of time, how do you choose the timeline?

PostgreSQL doesn't provide auch a functionality, and I can think of three reasons:
1) the high cost as mentioned above
2) during archive recovery, it does not happen very often that you have to
choose between timelines at all. It is more typical for streaming replication.
3) nobody saw a use case for such a functionality

If you really need this functionality, you'll have to write it yourself.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com