Re: Clarification on using pg_upgrade

----- Original Message -----

> From: Tory M Blue <tmblue(at)gmail(dot)com>
> To: Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
> Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
> Sent: Tuesday, 14 June 2016, 22:08

> Subject: Re: [PERFORM] Clarification on using pg_upgrade
>
> Right, that's what we do, but then to upgrade, we have to drop/add the
> node, because it's being upgraded. If I'm updating the underlying OS,
> I have to kill it all. If I'm doing a postgres upgrade, using an old
> version of slon, without using pg_upgrade, I have to drop the db,
> recreate it, which requires a drop/add.
>
> I'm trying to figure out how to best do it using pg_upgrade instead
> of the entire drop/add for postgres upgrades (which are needed if you
> are using slon as an upgrade engine for your db).
>

I've just skimmed through this thread, but I can't quite gather what it is you're trying to achieve. Are you looking to move away from Slony? Upgrade by any means with or without Slony? Or just find a "fast" way of doing a major upgrade whilst keeping Slony in-place as your replication method?

If it's the latter, the easiest way is to have 2 or more subscribers subscribed to the same sets and one at a time; drop a subscriber node, upgrade and re-initdb, then use clone node to recreate it from another subscriber. If you're intent on using pg_upgrade you might be able to fudge it as long as you can bump up current txid to be greater than what it was before the upgrade; in fact I've done similar before with a slony subscriber, but only as a test on a small database.