Re: How to upgrade from 9.1 to 9.2 with replication?

On 10/19/2012 10:02 AM, Claudio Freire wrote:
> On Fri, Oct 19, 2012 at 11:44 AM, Karl Denninger <karl(at)denninger(dot)net> wrote:
>> On 10/18/2012 5:21 PM, delongboy wrote:
>>
>> I have replication set up on servers with 9.1 and want to upgrade to 9.2
>> I was hoping I could just bring them both down, upgrade them both and bring
>> them both up and continue replication, but that doesn't seem to work, the
>> replication server won't come up.
>> Is there anyway to do this upgrade with out taking a new base backup and
>> rebuilding the replication drive?
>>
>> Not that I know of.
>>
>> I tried this as well when the development branches were out in a "sandbox"
>> and it failed as it did for you.
>>
>> For 9.1 -> 9.2 what I did was bring down the cluster, upgrade the master,
>> then initdb the slave and run the script that brings over a new basebackup
>> with the WAL archives ("-x" switch), and when complete just started the
>> slave back up in slave mode.
>>
>> This unfortunately does require a new data copy to be pulled across to the
>> slave. For the local copies this isn't so bad as wire speed is fast enough
>> to make it reasonable; for the actual backup units at a remove it takes a
>> while as the copy has to go across a WAN link. I cheat on that by using a
>> SSH tunnel with compression turned on (which, incidentally, it would be
>> really nice if Postgres supported internally, and it could quite easily --
>> I've considered working up a patch set for this and submitting it.)
>>
>> For really BIG databases (as opposed to moderately-big) this could be a
>> much-more material problem than it is for me.
> Did you try?
>
> Bring both down.
> pg_upgrade master
> Bring master up
> pg_upgrade slave
> rsync master->slave (differential update, much faster than basebackup)
> Bring slave up
That's an interesting idea that might work; are replicated servers in a
consistent state guaranteed to have byte-identical filespaces? (other
than the config file(s), of course) I have not checked that assumption.

Surprises in that regard could manifest in very unfortunate results that
only become apparent a significant distance down the road.

--
-- Karl Denninger
/The Market Ticker ®/ <http://market-ticker.org>
Cuda Systems LLC