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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +