Re: Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

On Thu, Sep 14, 2017 at 09:21:25PM -0400, Stephen Frost wrote:
> Michael, all,
>
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> > On Fri, Sep 15, 2017 at 8:23 AM, Andreas Joseph Krogh
> > <andreas(at)visena(dot)com> wrote:
> > > I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade
> > > creates the new data-dir with pg_wal "in it" (just like regular initdb), so
> > > pg_upgrade seems not to care about where the old version's pg_xlog was. You
> > > have to move (by symlinking) pg_wal to a separate location manually *after*
> > > running pg_upgrade on the master.
> >
> > That's true, this should definitely be mentioned in the documentation.
>
> Uh, this seems like something that should be *fixed*, not documented.
> That initdb accepts an alternative location for pg_xlog/pg_wal now
> raises that to a first-class feature, in my view, and other tools should
> recognize the case and deal with it correctly.
>
> Of course, that having been said, there's some technical challenges
> there. One being that we don't really want to mess with the old
> cluster's WAL during pg_upgrade. Frustratingly, we have a way to deal
> with that case today for tablespaces, it was just never applied to WAL
> when we started allowing WAL to be stored elsewhere (formally). That
> seems like it was a mistake to me.
>
> Then again, the more I think about this, the more I wonder about putting
> more-or-less everything in PGDATA into per-catalog-version directories
> and making everything self-contained. Part of the ugly bit with the
> rsync is exactly that we have to go up an extra level for the data
> directories themselves, and users can actually put them anywhere so
> there might not even *be* a common parent directory to use.

I am going to need to outline where I think we are before I can suggest
a solution.

What we did with the tablespace directory is to use the catalog version
_inside_ the tablespace directory, e.g.:

/vol1/pg_tblsp/PG_9.5_201510051
/vol1/pg_tblsp/PG_9.6_201608131

We did not do this for the WAL directory because by _default_ it is in
PGDATA, which is major-version specific. Where this breaks is when the
initdb --waldir option is used. We could have created a major version
subdirectory inside the directory specified by --waldir, but that would
have made the PGDATA contents and the --waldir differ, and I think it
would have been confusing.

What we have now is the problem that perhaps people are not creating
major-version-specific names for --waldir. I am not sure how anyone is
doing an upgrade except for dumping the data, deleteing the old cluster
and the old WAL directory, recreating the new cluster and new WAL
directory, and then loading the data. Because pg_upgrade needs to have
the old and new servers running, having different external WAL
directories for each cluster is a requirement.

We have not had any complaints about this so I am confused why it is now
an issue just because of rsync. If they created separate WAL
directories on the primary, they will need separate ones on the standby,
the symlinks will be copied, and they need to use rsync to copy stuff.
There are no hard links in there, but using the link option should be
harmless.

> > An improvement could be done as well here for pg_upgrade: when using
> > --link, the new PGDATA created could consider as well the source
> > pg_wal and create a link to it, and then clean up its contents. I am
> > not completely sure if this would be worth doing as people are likely
> > used to the current flow though. The documentation needs to outline
> > the matter at least.
>
> No, one of the baseline requirements of pg_upgrade is to *not* screw
> with the existing cluster. Removing its WAL or "cleaning it up"
> definitely seems like it's violating that principle.
>
> I tend to agree that it'd be good for the documentation to address this,
> but this is all really getting to be a bit much for a manpage to be able
> to handle, I think..

Yes, I am struggling with this too.

--
Bruce Momjian <bruce(at)momjian(dot)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 +