Re: Using pg_upgrade on log-shipping standby servers

On Thu, Jul 26, 2012 at 9:59 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> I think I could create a list and pass that into a loop so only
>> the command has to be modified, but again, how do we do that on Windows?
>> Can we create a shell function in Windows and pass the file name as an
>> argument?
>
> I don't know, but I assume that somewhere in the known universe there is
> a way on Windows to say, here is a list of files, copy them to that
> host.
>
>> Another problem is that the standby cluster might create _new_ files
>> that don't exist on the master, e.g. WAL files, and those have to be
>> removed. I am not clear how to do that either, except by removing all
>> files with a hard link count of 1, and again, this is difficult on
>> Windows.
>
> Well, then that would call for another list of files.

I cannot escape the feeling that if we go down this route in any form
we're going to spend years tracking down data-loss-inducing bugs. The
ones we have on the master are bad enough, but doing it on the standby
is almost worse because (1) few enough people will use this
functionality that we won't get many bug reports even if it's badly
broken and (2) people who are affected may not discover it until
something bad has already happened on the master. I don't hear anyone
thinking very hard about ways that the master could be different from
the standby, and without a lot of careful thought on that topic I
think this is all kinds of bad news. Just to take one example, how
are you going to ensure that the standby has replayed all the WAL that
the master generated prior to the upgrade? If the answer is "shut
everything down cleanly and hope for the best", color me unimpressed.

IMV, pg_upgrade is not yet sufficiently reliable that we should be
looking for new projects that seem certain to make it less reliable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company