Submitted by Greg Smith (not verified) on Fri, 2008-02-15 09:20.
It would have been less painful if you'd have found the manual section on upgrading before going through this, but learning the hard way is certainly good for making any lessons stick.
pg_dumpall does create the databases for you; I'm not sure what issue you ran into but a regular old "pg_dumpall > db.out" starts by creating roles and then does databases, schemas, tables, and data. I'm not quite sure where you got the syntax for what you're calling a "generic SQL dump" because that's still plenty of PostgreSQL specific stuff in that format; INSERT-based dumps are not the best choice for version upgrades.
As for the larger issues here...anyone who has a database with hundreds of gigabytes of valuable data doesn't just apply a major update to the live database; that's crazy. What you do is bring up a new instance running the new version, test test test, and if everything works great only then do you consider switching to the new one. The PostgreSQL model is admittedly a little harder than it should be, but anyone who has a database with enough data that the dump/reload will take a while is less likely to find an in-place upgrade useful anyway.
The standard way to solve this problem for larger environments (my largest PostgreSQL database is 2TB) is to use the Slony add-on to replicate the data. You can bring up identical copies of the data in 8.2 and 8.3, for example, and keep them in sync until you're ready to swap to the new one.
As for MySQL, it's about 2.5 years since their last major production version was released, so it's no wonder people aren't used to feeling any upgrade pain there. It's adding disruptive new features that breaks software backward compatibility, and as long as they're not doing that upgrades are easy.
»
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.
It would have been less
It would have been less painful if you'd have found the manual section on upgrading before going through this, but learning the hard way is certainly good for making any lessons stick.
pg_dumpall does create the databases for you; I'm not sure what issue you ran into but a regular old "pg_dumpall > db.out" starts by creating roles and then does databases, schemas, tables, and data. I'm not quite sure where you got the syntax for what you're calling a "generic SQL dump" because that's still plenty of PostgreSQL specific stuff in that format; INSERT-based dumps are not the best choice for version upgrades.
As for the larger issues here...anyone who has a database with hundreds of gigabytes of valuable data doesn't just apply a major update to the live database; that's crazy. What you do is bring up a new instance running the new version, test test test, and if everything works great only then do you consider switching to the new one. The PostgreSQL model is admittedly a little harder than it should be, but anyone who has a database with enough data that the dump/reload will take a while is less likely to find an in-place upgrade useful anyway.
The standard way to solve this problem for larger environments (my largest PostgreSQL database is 2TB) is to use the Slony add-on to replicate the data. You can bring up identical copies of the data in 8.2 and 8.3, for example, and keep them in sync until you're ready to swap to the new one.
As for MySQL, it's about 2.5 years since their last major production version was released, so it's no wonder people aren't used to feeling any upgrade pain there. It's adding disruptive new features that breaks software backward compatibility, and as long as they're not doing that upgrades are easy.