Re: [HACKERS] Clarification in pg10'spgupgrade.html step 10 (upgrading standby servers) - Mailing list pgsql-hackers

Bruce, all,
[snip]

Further, really, I think we should provide a utility to do all of the
above instead of using rsync- and that utility should do some additional
things, such as:

- Check that the control file on the primary and replica show that they
  reached the same point prior to the pg_upgrade.  If they didn't, then
  things could go badly as there's unplayed WAL that the primary got
  through and the replica didn't.

- Not copy over unlogged data, or any other information that shouldn't
  be copied across.

- Allow the directory structures to be more different between the
  primary and the replica than rsync allows (wouldn't have to have a
  common subdirectory on the replica).

- Perhaps other validation checks or similar.

Unfortunately, this is a bit annoying as it necessairly involves running
things on both the primary and the replica from the same tool, without
access to PG, meaning we'd have to work through something else (such as
SSH, like rsync does, but then what would we do for Windows...?).

> > 3. What if the directory-layout isn't the same on primary and standby, ie.
> > tablespaces are located differently?
>
> The way we reconfigured the location of tablespaces in PG 9.0 is that
> each major version of Postgres places its tablespace in a subdirectory
> of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.  If
> your tbldir is different on the primary and standby, rsync will  still
> work.  Everything _under_ the standby dir must be laid out the same, but
> the directories above it can be different.

That's correct, the directory to use for the tablespace actually *is*
the tablespace directory (unlike the base directories, it doesn't need
to be a directory above the tablespace directory, the documentation
could probably be clearer on this point).

As for all of the people raising concerns about if this process is
correct or valid- I contend that the method used above, if done
properly, isn't materially different from what pg_upgrade itself does.
If we can't consider this safe then I'm not sure how we consider
pg_upgrade safe.  (yes, I know there are some who don't, and that's a
fair position to take also, but I consider the process above, when
implemented correctly, is essentially the same).

All that said, I honestly hadn't expected this method to end up in the
documentation.  Not because I don't trust it or because I wanted to
hoard the process, but because it takes a great deal of care and there's
really additional validation that should be done (as discussed above)
and those are things that I feel reasonable confident I'd remember to do
when using such a procedure but which I wouldn't expect someone new to
PG to realize they should do.

Thanks!

Stephen

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com