Re: Using pg_upgrade on log-shipping standby servers

On Thu, Jul 26, 2012 at 7:24 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Jul 26, 2012 at 02:17:22PM -0400, Bruce Momjian wrote:
>> > > Is that sufficient?
>> >
>> > Well, at the very least, you need to guarantee that the standby is
>> > caught up - i.e. that it replayed all the WAL records that were
>> > generated on the master before it was shut down for the final time. I
>> > don't think that telling the user that they must be sure to do that is
>> > sufficient - you need some kind of built-in safeguard that will
>> > complain loudly if it's not the case.
>>
>> Yes, that would be a problem because the WAL records are deleted by
>> pg_upgrade. Does a shutdown of the standby not already replay all WAL
>> logs? We could also just require them to just start the standby in
>> master mode and shut it down. The problem with that is it might run
>> things like autovacuum.
>>
>> I was originally thinking that we would require users to run pg_upgrade
>> on the standby, where you need to first switch into master mode.
>
> OK, sorry, I was confused. You _have_ to run pg_upgrade on the standby
> --- there are many things we don't preserve, and we need pg_upgrade to
> move those user file to the right place --- a obvious example is
> tablespace files. Database oids aren't even preserved, so the data
> directory changes.

These are reasons why you CANNOT run pg_upgrade on the standby, not
why you HAVE to. If you run pg_upgrade on the standby and separately
on the master, you will end up with divergence precisely because of
those things that aren't preserved.

Any approach that calls for pg_upgrade to run on the master and
standby separately is broken.

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