Re: Using pg_upgrade on log-shipping standby servers

On Thu, Jul 26, 2012 at 11:03:15AM -0400, Robert Haas wrote:
> >> IMV, pg_upgrade is not yet sufficiently reliable that we should be
> >> looking for new projects that seem certain to make it less reliable.
> >
> > The script has to make the primary/standby identical, and guarantee
> > that. That is why one list and removing new standby files is necessary.
> > Are you saying having the primary/standby identical is
> > impossible/unreliable, or that having them the same is insufficient?
>
> Having them the same is clearly sufficient, but only if ALL the files
> are the same. You seem to be taking it on faith that the data files
> backing non-system tables will be the same, or close enough to the
> same, that we can just not worry about those. I find that when I
> don't worry about things, they break.

Good point, and an interesting question. The way pg_upgrade works is
that it moves the user table/index files to the new cluster at the end,
and does not start the server after that. This is why you can always
revert to the old cluster in link mode after it completes. If you start
the new cluster, you are committed and can't revert in link mode. I
guess you could ask if the old cluster operations could have modified
these files in some non-standard way, but I can't think of how that
could happen.

What you would probably want is for the old primary cluster to continue
streaming its WAL to the standby cluster _until_ pg_upgrade completes,
and then do the pg_upgrade on the old standby, but I can't think of a
simple way to do that.

What would be really interesting would be to somehow have the old and
new clusters both on the standby and have the master stream to the
appropriate server, but that seems complicated, and I am not sure if it
would help because of the interactions behind-the-scenes in pg_upgrade.

I don't see the "don't modify the user files" behavior changing anytime
soon, and it is documented, so I feel pretty confident that those files
were not modified on the primary or standby cluster, and are hence the
same, or at least as "the same" as they were when they were running the
older major version of Postgres.

Is that sufficient?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +