Re: pg_upgrade (and recovery) pitfalls

Greetings,

* PO (gunnar(dot)bluth(at)pro-open(dot)de) wrote:
> Stephen Frost – Thu, 16. August 2018 19:00
> > * PO (gunnar(dot)bluth(at)pro-open(dot)de) wrote:
> > > - why does a recovery, based on a recovery.conf that points to a reachable
> > primary (which obviously communicates its own timeline), still look for higher
> > timelines' history-files in the archive and tries to jump onto these
> > timelines? This doesn't seem reasoable to me at all...
> >
> > PG is going to start from the current timeline and try to find all the
> > timelines that it could possibly play forward to and at what point the
> > timeline changes were done and then it's going to figure out which
> > timeline to go to (by default we try to stick with the currnet timeline,
> > but you can configure recovery.conf to specify a different timeline or
> > 'latest') and then it's going to request the WAL to get from where PG is
> > to the end of whichever timeline it thinks you want. That's all
> > entirely reasonable and how things are supposed to work.
> >
> > Only once PG reaches the end up what's available through the restore
> > command does it start trying to talk to the primary. There's been some
> > discussion about how it might be nice to be able to configure PG to
> > prefer going to the primary instead, though, really, it should typically
> > be faster to replay WAL from a restore_command than to get it from the
> > primary over the network, not to mention that getting it from the
> > primary will introduce some additional load on the system.
>
> Fair enough, and I onlöy just realised I've been carrying a
> recovery_target_timeline = 'latest'
> around from my predecessors. :facepalm:

That isn't necessairly a bad thing to have, especially in environments
where you're promoting replicas to be primaries and flipping things
around.

> I owe you a beer or X (in Lisbon?)!

Yes, I'll be in Lisbon, would be happy to chat over a beer.

Thanks!

Stephen