Re: how to identify the timeline of specified recovery_target_timewhen do multiple PITR - Mailing list pgsql-general

magodo wrote:
> On Thu, 2018-10-11 at 06:35 +0200, Laurenz Albe wrote:
> > magodo wrote:
> > > OK... Just take another example:
> > > 
> > >          A     B
> > > BASE-----+-----+------o1 (recover to
> > > A)                              1
> > >          |     |           C
> > >          +.....|.......----+---o2 (regret, recover to
> > > B)             2
> > >                |           |    
> > >                +...........|..------o3 (regret again, recover to
> > > C)  3
> > >                            | 
> > >                            +........--
> > > --                             4
> > > 
> > > 
> > > Suppose I'm at "o3" and want to recover to point "C". Because I want to
> > > recover to the first timeline which covers this time point, it means I
> > > wish to recover to timeline 2.
> > 
> > Ah, I finally understand your question.
> > 
> > You assume tht timeline 1 and 2 have ended (that's how you drew it),
> > and that consequently timeline 3 is the "earliest existing" timeline,
> > so why doesn't PostgreSQL choose that one automatically?
> > 
> > Even though you drew it that way, timeline 1 and 2 have not ended, in
> > a way.  There may be more on them.  How should PostgreSQL know what is
> > the last WAL entry on a certain timeline?  For that, it would have to
> > restore and examine *all* WAL segments on that timeline until that fails.
> > 
> > But that is unreasonable because of the potential amount of time
> > and work involved.  Rather, PostgreSQL has to decide at the point where
> > timeline 2 branches off which one it should follow.
> 
> Thank God I finnally conveied my meaning :)
> 
> For one cluster, if , for example, it recovered to "A" at "o1", then
> the switched WAL(in case of stop-then-recover) or .partial
> corresponding WAL(in case of promote) is the last WAL of the timeline1,
> and it makes no sense to consider timeline1 will continue grow after
> "o1", because it has ended.
> 
> You meantioned: "There may be more on them.".
> 
> Would you please give me an example? I must have missed something
> out...

The timeline is determined before any WAL segments are restored, because
the timeline history (as persisted in the *.history files) determines
which WAL segments will be restored.

You seem to assume that the point A at which recovery ended is the end of the
WAL stream of timeline 1, but you cannot know that:
- it could have been a point-in-time-recovery
- it could be that during the first recovery attempt some WAL archives
  were missing, which caused a premature end of recovery,
  but they are there at the second attempt.

Indeed, your sentence

> if , for example, it recovered to "A" at "o1", then
> the switched WAL(in case of stop-then-recover) or .partial
> corresponding WAL(in case of promote) is the last WAL of the timeline1

seems to contradict your drawing, which has B after A on timeline 1.

Example:
Assume that timeline 1 reaches to 000000010000001500000030.
We recover to point A, which is in the middle of 000000010000001500000020,
and there branch to timeline 2.
After some time, we decide to recover again, starting from a
checkpoint in 000000010000001500000010.
We want to recover to 2018-10-11 12:00:00.

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?

The only way would be to restore and read them all *before* you even
decide which timeline you want to choose for recovery.

That would be forbiddingly expensive, wouldn't it?

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