Re: Streaming replication upgrade sanity check

Greetings,

* tsuraan (tsuraan(at)gmail(dot)com) wrote:
> > You don't need to go to 11 or 12 if you want to upgrade to 13- you can
> > just pg_upgrade directly to 13.
>
> That's cool. In that case, I assume I could also only have the old
> (10.x) and the new 13.2 postgres versions installed? The intermediate
> steps of calling pg_upgrade aren't a problem, but if I could skip
> shipping out and installing postgres 11 and 12, that would be pretty
> nice.

That's correct.

> > Exactly how are you doing this rsync..? A simplistic rsync would end up
> > just copying everything over and, as Bruce says later, if you're doing
> > that then you might as well just use pg_basebackup or a similar tool to
> > do it cleanly. In other words, I don't think that you're actually
> > getting the benefit that you think you are with this.. Note that the
> > file names are not kept the same after the pg_upgrade...
>
> The exact rsync flags are "-acz" "--delete" (so, --archive --checksum
> --compress --delete). I'm now seeing that I left off --hardlink, which
> was definitely a mistake, but I also don't know whether --checksum is
> a valid thing to do when the master is active. From the general tone
> of this discussion, it may be "fine", but it's not likely to be better
> than just doing a pg_basebackup, and of course pg_basebackup is
> definitely correct. I want to do things efficiently, but doing them
> correctly comes first, so I'll probably just go with a basebackup and
> deal with complaints as they come in :)

No, that wouldn't be more efficient than a pg_basebackup since it's just
going to end up copying over the entire cluster. Using hard-link with
checksum and doing it properly *might* end up being alright and actually
reducing the data transfer but you'd want to make 100% sure that rsync
in that mode would check the files it's creating hard links for with
checksums and transfer any that are different. Still doesn't address
the unlogged tables and temp files caveat that I previously mentioned.

> > Also- I don't think you realize how fast the rsync process to update the
> > standbys will be. Done correctly, it should be faster than the
> > pg_upgrade.. Note that you'll want to make sure you TRUNCATE and
> > unlogged tables before the pg_upgrade, et al, otherwise you'll end up
> > rsync'ing those over. Also make sure you haven't got any stray or
> > forgotten temp files or other things. Again, done properly, the rsync
> > to upgrade the standbys should only be copying the catalog tables
> > themselves and it should be quite fast.
>
> Yeah, it's not as much a matter of how long a sync will take, but just
> the way the systems fit together. The systems are all customer-owned,
> running in customer locations, and we don't actually have a guarantee
> that the standby targets are even powered up while the main database
> is upgrading. They absolutely should be, but customer installations
> can get pretty weird, so I'm going to stick with fully upgrading and
> activating the master machines as quickly as possible, and then
> dealing with getting the upgrades to the standby systems however I
> have to. It's looking like a new pg_basebackup is just the best
> approach given what I have to work with, so I'll switch to that unless
> somebody stops me :)

That's certainly a simple and safe approach. The biggest issue with
pg_basebackup is that it's single-threaded and therefore you could end
up CPU bound in rebuilding the replicas. Maybe that's fine in your
case, but there are alternative solutions which can do parallel backup
and restore if you're looking for something that won't get throttled due
to only being able to use one CPU.

Thanks,

Stephen