Re: [PG_UPGRADE] 9.6 to 10.5 - Mailing list pgsql-bugs

On Thu, Aug 16, 2018 at 08:15:43AM -0300, Martín Marqués wrote:
> 2018-08-14 18:23 GMT-03:00 Bruce Momjian <bruce@momjian.us>:
> >
> > I have not seen any report from the original reporter so I have gone
> > ahead and committed the fix suggested by Michael Paquier.
> >
> > This means that standby upgrades will fail in 10.5 until 10.6 is
> > released.  Ugh!  I guess users can upgrade to 10.4 and then do a minor
> > upgrade to 10.5 as a workaround.
> 
> I didn't want to jump in earlier as I'm not a pg_upgrade expert, and
> to be honest, haven't done any code reading around this tool, but I
> was puzzled with Michael's comment on pg_upgrade failing on a standby
> node. I have always been under the impression that pg_upgrade had to
> be executed on a primary server, and standbys had to be recloned or
> rsynced (which is just the old way of recloning).
> 
> Maybe there's something I wasn't aware of. But if that's the case,
> then the docs don't reflect this missing knowledge and we should amend
> it.

Uhgh, what is the matter with me!  I specifically wrote down the
instructions on upgrading a standby in step 10, and even added details
so I could remember how rsync worked, and then forgot all about it when
dealing with this bug report:

    https://www.postgresql.org/docs/11/static/pgupgrade.html

    What this does is to record the links created by pg_upgrade's link mode
    that connect files in the old and new clusters on the primary server. It
    then finds matching files in the standby's old cluster and creates links
    for them in the standby's new cluster. Files that were not linked on the
    primary are copied from the primary to the standby. (They are usually
    small.) This provides rapid standby upgrades. Unfortunately, rsync
    needlessly copies files associated with temporary and unlogged tables
    because these files don't normally exist on standby servers.

My apologies.  I am getting old!

Anyway, let me outline what each PG version does.  For primary server
upgrades, 10.4 (and other minor releases) do not detect if you did a
pg_ctl -m immediate for the shut down, and does the upgrade, but the
upgrade could be corrupt.  10.5 fixes that by reporting improper
shut down.

For standby server upgrades, 10.4 allows a standby that is shut down to
be upgraded, even though after the upgrade it cannot be reconnected as a
standby.  For 10.5, because of the missing shutdown message test, it
will report the server was improperly shut down.  This is fixed in the
git trees for 10.6.

So, I think 10.5 and the current source trees are fine for primary
upgrades.  How do we want to handle upgrading a shut down standby?  I
think having a server go from standby to primary while pg_upgrade is
running is just too dangerous.  Specifically, if you shut down a
standby, then remove recovery.conf, the first start of the server will
make it become a primary, but at the time pg_upgrade is checking it, it
still sees the standby shut down.

I think we just need to add an error message to say the server must be
shut down as a _primary_ server, not as a standby server, and we can
recommend to read the documentation on the use of rsync.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +