Re: State of support for back PG branches

steve(at)blighty(dot)com (Steve Atkins) writes:
> We started our upgrade from 7.2 to 7.4 about 20 months ago and finished it
> about 10 months ago, skipping 7.3 entirely.

We did similar; there was only one system deployed in a timeframe
where 7.3 was relevant, and the "big systems" skipped over 7.3 much as
you did.

> We've only just today hit our first problem in 7.4, and it's fixed by
> upgrading to 7.4.current, rather than the 7.4.something we originally
> upgraded to from 7.2.something.

Ditto, to a degree; we hit a pretty funky index update thing that was
resolved in 7.4.8.

> We'll be skipping 8.0 completely and the next step will probably be to
> 8.1.something (or possibly 8.2.something, depending on how bizgres
> looks in 3 months time). We'd probably consider upgrading our
> customers more often, but a dump and restore is extremely painful.

We're strategizing somewhat similarly, save for bizgres not being on
our roadmap.

Dump and restore isn't forcibly necessary; we did the 7.2 to 7.4
upgrade via eRServer, and made sure that Slony-I was designed to
support upgrades.

I recently did an application upgrade (not a PG version change) using
Slony-I; replicated to a node that wasn't otherwise busy, and used
that node as the "base" where various tables were transformed into
their new forms. Replicated the "new forms" on everywhere. On the
"flag day," a MOVE SET shifted mastery, MERGE SET pasted things
together, and EXECUTE SCRIPT finished the transformation.

We're starting to look at 8.1, and would *certainly* use Slony-I to
perform that upgrade. The "on the cheap" method would involve
replacing nodes one at a time with 8.1 versions, though we'd more
likely have a bunch of 7.4 nodes running parallel with a corresponding
set of 8.1 nodes, and, once done, drop all the 7.4 ones at once...

> Just a view from the pg-based-enterprise-application world.
>
> A nice pg_upgrade utility would make a big difference. Clearly an
> in-place upgrade is possible, but maintaining is hard. There are two
> broad ways of running a pg_upgrade project - one that is entirely
> independent of the main codebase and one that puts requirements on
> the main codebase developers ("if you change $foo you provide code
> to translate old $foo to new $foo"). Any feel for the relative
> difficulty of the two approaches? And how much push-back there'd be
> on the latter?

This strikes me as being only marginally easier than the proverbial
desires for tools to convert ext2 to XFS or ReiserFS.

The conversion tool would have to encode a lot of hairy details, and
would require that the likes of Tom Lane and Bruce Momjian spend a lot
of their time writing the conversion tool instead of working on new
features.

With filesystems, it seems easier and cheaper to buy an extra disk
drive (what, $200?) and use something like rsync/unison to relatively
efficiently replicate the filesystem.

Slony-I is the PostgreSQL equivalent to rsync/unison, in this case.

Or you could look at Mammoth Replicator, if you prefer...

The replication approach allows Tom and Bruce to work on sexy new
features instead of forcing them into the data conversion mould...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/slony.html
It's always darkest just before it gets pitch black.