Re: pg_upgrade and rsync

On Thu, Mar  5, 2015 at 10:55:28AM +0300, Vladimir Borodin wrote:
>     You are correct that a pg_controldata file is copied over that has
>     wal_level=minimal, but that should not be a problem.
>
>
> I suppose, this is the root cause of why replica does not start as hot standby.
> It it enough to start it as warm standby, but not hot standby.
> See CheckRequiredParameterValues function in xlog.c which is called inside of
> StartupXLOG function.

Yes, you are correct.  I spent all day building a test harness so I
could automate this setup and test various failures.  I was able to
reproduce your failure, and you are correct that the proper fix is to
set wal_level=hot_standby on the new master, and then start and stop the
new cluster just before rsync.

The root cause is that pg_upgrade calls pg_resetxlog -o on the new
cluster _after_ the new cluster stopped for the final time, so rsync is
copying the incorrect pg_controldata wal_level value.  Also, even if
pg_resetxlog preserved wal_level in the control file, there is no
guarantee that the user configured the new cluster's wal_level for
hot_standby anyway.

What I have done is to update the pg_upgrade instructions to add this
required step.  Updated doc patch attached.  (I also added the --delete
flag to rsync.)  Thanks so much for your detailed report.

>         But it could not be done with --size-only key, because control-file is
>         of fixed
>         size and rsync would skip it. Or may be everything should be copied
>         with
>         --size-only and control-file should be copied without this option.
>
>
>     Well, what happens is that there is no _new_ standby pg_controldata
>     file, so it is copied fully from the new master.  Are you running initdb
>     to create the new standby --- you shouldn't be doing that as the rsync
>     will do that for you.
>
>
> No, I don’t. The scenario of the problem with copying control-file was in case
> when I:
> 1. ran pg_upgrade on master and got control-file with "wal_level = minimal",
> 2. did rsync --size-only to replica (and it got this control-file with
> "wal_level = minimal"),
> 3. started and stopped postgres on master to get «good» control-file with
>  "wal_level = hot_standby»,
> 4. did rsync --size-only to replica one more time. And this time control-file
> is not copied because of the same size of control-file.
>
> Actually, if you don’t do step 2, everything works as expected. Sorry for
> bothering you.

Ah, yes, I think doing rsync twice is never a good suggestion.  It can
lead to too many failures.  Doing the start/stop before rsync seems like
the best solution.

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

  + Everyone has their own god. +