Re: Using pg_upgrade on log-shipping standby servers

On Mon, Jul 16, 2012 at 05:29:26PM -0700, Jeff Davis wrote:
> On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote:
> > I don't think we can assume that because pg_upgrade was run on the
> > master and standby that they are binary identical, can we? Technically
> > the user file are identical, but the system catalogs and WAL might be
> > different, hence my suggestion to run rsync before allowing the standby
> > to rejoin the primary.
>
> Do you have plans to change that in the future?
>
> If we know that the user data files are identical between primary and
> replica, it would be nice if we could provide a robust way to avoid
> copying them.

Well, rsync --checksum would work, but both systems have to be down for
that. You could snapshot the down primary and rsync --checksum that
against the standby, but I am not sure how much that helps us.

I can't figure out how to make this work better without adding a whole
lot more code to pg_upgrade that might need adjustment for every minor
release, i.e. pg_upgrade knows nothing about the WAL file format, and I
want to keep it that way.

However, I have two ideas. First, I don't know _why_ the
primary/standby would be any different after pg_upgrade, so I added the
documentation mention because I couldn't _guarantee_ they were the same.
Actually, if people can test this, we might be able to say this is safe.

Second, the user files (large) are certainly identical, it is only the
system tables (small) that _might_ be different, so rsync'ing just those
would add the guarantee, but I know of no easy way to rsync just the
system tables.

Does that help?

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

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