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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match