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