Re: pg_upgrade (was: 8.2 features status)

Rick,

> The objective is to smoothly upgrade to the new version with minimal
> downtime.

Thanks for jumping in.

> The different proposals as far as I can see are as follows:
>
> Proposal A - the big one time reformatting
> 1) shutdown the db
> 2) run a command that upgrades the data directory to the new format
> 3) start up the new postgres version with the new data dir
>
> Pros: only pg_upgrade (or whatever it's called) needs to know about
> the old and new formats, each version of postgres knows about "it's"
> format and that's it. The postgres code stays clean
> cons: your database is down while the upgrade takes place. This
> sucks because the people who need this are the same people who are
> trying to avoid downtime. It's faster than a dump/reload but it
> doesn't completely solve the problem, it just mitigates it.

Yes, but it mitigates it *considerably.* The general idea is that doing an
in-place binary upgrade should take 80% less time, and require only 10% (or
so) extra disk space. This would be sufficient for most users with large
databases; they can afford to be down from midnight to 3 am but not to be
down for the whole weekend (as dump/reload requires for a 3tb database) nor
do they have 100% + extra disk space and 20% extra CPU on the machine (as
upgrade-by-replication requires).

Plus, there are versions (like 8.2 looks to be) where there is *no* change in
the file format. For these, pg_upgrade would need just to bootstrap the
system, swap the system tables and header files, and restart. Seconds
instead of hours. As PostgreSQL matures further, I predict that there will
be more releases with no change in the file format, making this even more
useful.

> Proposal B - the gradual upgrade
> 1) shutdown the db
> 2) start it back up with the new version of postgres
> 3) the new postgres version upgrades things in place as needed

This is impractical, because the number of version-to-version compatibility
libraries required will grow geometrically with time. We don't have to just
accomodate the last version, but the last 4+ versions.

> Proposal C - PITR with in on the fly disk upgrades
> 1) setup PITR
> 2) run pg_upgrade on your latest backed up data directories
> 3) start up the new pg on that data directory in restartable
> recovery / read-only / hot-standby mode
> 4) update the recovery log importer so that it can update the log
> files on the fly as it applies them
> 5) failover to the hot standby as you normally would

I don't think this is practical. Simon?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco