Re: pg_upgrade and rsync

> 4 марта 2015 г., в 19:28, Bruce Momjian <bruce(at)momjian(dot)us> написал(а):
>
> On Wed, Mar 4, 2015 at 01:53:47PM +0300, Vladimir Borodin wrote:
>> After running initdb to create the new master, but before running
>> pg_upgrade, modify the new master's postgresql.conf and change wal_level
>> = hot_standby. (Don't modify pg_hba.conf at this stage.)
>>
>>
>>
>> That does not help. The reason is that pg_upgrade sets 'Current wal_level
>> setting: minimal' in control-file, and it does not depend on what is set in
>> postgresql.conf before running pg_upgrade. Details could be seen here - http://
>> pastie.org/9998671.
>
> Well, what is happening is that the pg_resetxlog commands we run inside
> pg_upgrade set the pg_controldata file's wal_level to minimal, but as
> you saw, starting the server sets the pg_controldata properly.
> pg_resetxlog is not changing the WAL files at all, just the control
> file.
>
>> The workaround for this is to start and cleanly shut down postgres on master
>> after running pg_upgrade but before running rsync. After that there would be a
>> good control-file for streaming replication and rsync to replica can be done.
>
> 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.

>
>> 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.

> Also, are you cleanly shutting down all the
> servers, or using pg_ctl -m immediate?

I use init-script, it shuts down cleanly with "-m fast".

>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +

--
May the force be with you…
https://simply.name