I had a few thoughts on this issue:

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

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.

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

Pros: very short downtime. only the time to shutdown the postgres version and start up the new one cons: postgres code gets filled with cruft. each version has to know about the old versions on disk data format and how to upgrade it. Until it is finished you will be left with a database that is part old format, part new format. This could introduce bugs for people who never needed the feature in the first place.

Here is another proposal that I haven't heard anyone else suggest. My apologies in advance if it's obviously not workable or has already be discussed.

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

Pros: essentially no downtime, just any incidental time needed for the failover to occur. cruft in postgres main codebase is mimimized. It's limited to the log importer. All other parts of postgres are unaffected Cons: requires another server or double the disk space on the original server. Is this a problem for people with databases so large that a dump reload is unacceptable? Perhaps there are technical issues with postgres that I don't understand that would make this too hard. Maybe it would take to long to update each log file as it's applied so it wouldn't be able to catch up.

Oh yeah there's another way
Proposal D - Use slony
But I figured since that's been working for a long time, if slony solved their problem then they wouldn't be looking for something else.

I have no need for this feature as a dump reload is not a problem for me. I've always wondered though if that was a feasible answer to this problem. Each time it crops up people propose solutions A and B but never C.

On Aug 4, 2006, at 1:30 PM, Jim C. Nasby wrote:

On Fri, Aug 04, 2006 at 02:12:16PM -0400, Stephen Frost wrote:
* Jim C. Nasby ([EMAIL PROTECTED]) wrote:
On Thu, Aug 03, 2006 at 11:20:48PM -0700, Josh Berkus wrote:
* In-place upgrades (pg_upgrade)

BTW, I may get Sun to contribute an engineer for this; will get you posted.

How would such a thing handle changes to page formats?

Couldn't this be done by converting a table/partial-table at a time?
It wouldn't be something which could run while the system is live, but
it'd probably take less time than dump/restore and wouldn't require
double the disk space of the whole database... no?

True, but if you're going to go about creating code that can deal with 2 different versions of on-disk data, why not go one better: put that code
into the database itself, so that pages are converted on-the-fly as
they're dirtied. That way you have *no* downtime (or almost no, anyway).
Jim C. Nasby, Sr. Engineering Consultant      [EMAIL PROTECTED]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to