Re: pg_upgrade + streaming replication ?

On Tue, Mar 20, 2012 at 11:46 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Mon, Mar 19, 2012 at 03:07:02PM -0700, Jeff Davis wrote:
>> On Mon, 2012-03-19 at 15:30 -0400, Bruce Momjian wrote:
>> > On Thu, Mar 01, 2012 at 02:01:31PM -0800, Lonni J Friedman wrote:
>> > > I've got a 3 node cluster (1 master/2 slaves) running 9.0.x with
>> > > streaming replication.  I'm in the planning stages of upgrading to
>> > > 9.1.x, and am looking into the most efficient way to do the upgrade
>> > > with the goal of minimizing downtime & risk.  After googling, the only
>> > > discussion that I've found of using pg_upgrade with a streaming
>> > > replication setup seems to be this (nearly) year old thread:
>> > > http://web.archiveorange.com/archive/v/9FNVlDWGQtpyWVL54jlK
>> > >
>> > > In summary, there is no way to use both pg_upgrade and streaming
>> > > replication simultaneously.  I'd have to either use pg_upgrade and
>> > > then effectively rebuild/redeploy the slaves, or not use pg_upgrade,
>> > > and reimport all of the data.  Is that still the latest status, or are
>> > > there other options?
>> >
>> > You can shut down all three servers, run pg_upgrade on all of them, then
>> > restart them as 9.1 servers.
>>
>> After running pg_upgrade on each server individually, they will have
>> different system IDs, and potentially different on-disk representation
>> of the catalogs, right?
>>
>> So how can you resume streaming without rebuilding the slaves?
>
> Oh, wow, I never thought of the fact that the system tables will be
> different?   I guess you could assume the pg_dump restore is going to
> create things exactly the same on all the systems, but I never tested
> that.  Do the system id's have to match?  That would be a problem
> because you are initdb'ing on each server.  OK, crazy idea, but I
> wonder if you could initdb on the master, then copy that to the slaves,
> then run pg_upgrade on each of them.  Obviously this needs some testing.

Wouldn't it be easier to just pg_upgrade the master, then setup the
slaves from scratch (with rsync, etc)? It certainly wouldn't be any
more work to do it that way (although still a lot more work than
simply running pg_upgrade on all servers).