Submitted by Ray (not verified) on Fri, 2008-02-15 08:59.
Hi Todd,
The reason for the dump/restore cycle is that there are changes in the disk file structures between major versions. I'm not involved in the development of PG so I can't give you any more detail than that, but subjectively I'm more reassured by a product that moves on from version to version! :-) Yes, it can be time consuming for a large database (and I'll point out that by using --column-inserts you opted for the slowest possible option), but then if your database is large enough that it is a major issue you won't upgrade in this way anyway - you might use Slony, for example, to replicate your data to the new server until it is caught up (while the old one is still in production), then switch your web servers (or whatever) to point at the new machine.
For the record, PostgreSQL doesn't require you to recreate databases manually before restoring from a script produced by pg_dumpall, (except, maybe, if you use the --data-only option, but I've never tried this so can't say either way). If you open the output script in a text editor you ought to see a series of CREATE DATABASE commands, and then every so often a "\c " command so that psql connects to each database in turn. I know this isn't a support forum, but when you say it "didn't work", what happened exactly? The whole point of pg_dumpall, after all, is to backup your entire cluster in one go - databases, roles, etc etc, so it wouldn't make a lot of sense if it didn't recreate the databases! If you're having problems you should consider subscribing to the mailing list, as I mentioned before, where some frighteningly clever people will help you out. :-)
Finally, the source install didn't pick up on your existing pb_hba.conf because initdb creates entirely a new cluster and leaves your old one untouched. I know that I'd prefer it to do things that way; for example, when installing alongside an older version on the same box, you can verify that all is well before shutting down the old version and firing up the new. You can even run multiple versions alongside each other (as long as they listen on different ports) - indeed, you'd have to in order to use Slony as an upgrade path, as I mentioned above.
»
Reply
Search
Windows for Intel Macs
Todd Ogasawara's Windows for Intel Macs ($7.99USD published by O'Reilly Media) is for Intel Mac users running Bootcamp or Parallels who want to learn more about running Microsoft Windows XP on Mac. It also provides specific tips and hints for using Parallels Desktop for Mac.
Hi Todd, The reason for the
Hi Todd,
The reason for the dump/restore cycle is that there are changes in the disk file structures between major versions. I'm not involved in the development of PG so I can't give you any more detail than that, but subjectively I'm more reassured by a product that moves on from version to version! :-) Yes, it can be time consuming for a large database (and I'll point out that by using --column-inserts you opted for the slowest possible option), but then if your database is large enough that it is a major issue you won't upgrade in this way anyway - you might use Slony, for example, to replicate your data to the new server until it is caught up (while the old one is still in production), then switch your web servers (or whatever) to point at the new machine.
For the record, PostgreSQL doesn't require you to recreate databases manually before restoring from a script produced by pg_dumpall, (except, maybe, if you use the --data-only option, but I've never tried this so can't say either way). If you open the output script in a text editor you ought to see a series of CREATE DATABASE commands, and then every so often a "\c " command so that psql connects to each database in turn. I know this isn't a support forum, but when you say it "didn't work", what happened exactly? The whole point of pg_dumpall, after all, is to backup your entire cluster in one go - databases, roles, etc etc, so it wouldn't make a lot of sense if it didn't recreate the databases! If you're having problems you should consider subscribing to the mailing list, as I mentioned before, where some frighteningly clever people will help you out. :-)
Finally, the source install didn't pick up on your existing pb_hba.conf because initdb creates entirely a new cluster and leaves your old one untouched. I know that I'd prefer it to do things that way; for example, when installing alongside an older version on the same box, you can verify that all is well before shutting down the old version and firing up the new. You can even run multiple versions alongside each other (as long as they listen on different ports) - indeed, you'd have to in order to use Slony as an upgrade path, as I mentioned above.