Re: Using pg_upgrade on log-shipping standby servers

On Thu, Jul 19, 2012 at 12:02 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Jul 19, 2012 at 09:41:29AM -0400, Robert Haas wrote:
>> On Thu, Jul 19, 2012 at 2:38 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > No, the point is they run pg_upgrade on the stopped primary and stopped
>> > standbys. Are those the same? I am not really sure.
>>
>> Of course not.
>
> OK, but why? When the clusters are stopped they are the same, you are
> running the same initdb on both matchines, and running the same
> pg_upgrade. What would cause the difference, other than the Database
> System Identifier, which we can deal with? I don't think we can
> guarantee they are the same, but what would guarantee they are
> different?

There isn't any guarantee that they are different. There's just no
guarantee that they are the same, which is enough to make this idea a
non-starter.

In general, it's pretty easy to understand that if you perform the
same series of inserts, updates, and deletes on two systems, you might
not end up with the exact same binary contents. There are a lot of
reasons for this: any concurrent activity whatsoever - even the exact
timing of autovacuum - can cause the same tuples can end up in
different places in the two systems. Now, admittedly, in the case of
pg_upgrade, you're restoring the dump using a single process with
absolutely no concurrent activity and even autovacuum disabled, so the
chances of ending up with entirely identical binary contents are
probably higher than average. But even there you could have
checkpoints trigger at slightly different times while restoring the
dumps, and of course checkpoints take buffer locks, and so now a HOT
prune might happen on one machine but get skipped on the other one
because the checkpointer has dropped the lock but not the pin, and now
you're hosed.

Even if you could control for that particular possibility, there are
surely others now and there will be more in the future.

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