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

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