Upgrading major versions of PostgreSQL with Debian or Ubuntu

One problem with PostgreSQL as compared to MySQL is that PostgreSQL is a real database, whereas MySQL is a bunch of hacks.

This means that PostgreSQL doesn’t let you just drop a new version of the executable on an old database file. Which is pretty much the way that real databases prefer to work. So, Oracle, Microsoft SQL, etc. all don’t work like MySQL. Whereas Microsoft Access works like MySQL.

Anyway, they have a fairly detailed set of processes to dump and then restore a database properly between versions. It’s the sort of thing which can be automated for the most part if you aren’t running a major production setup. (actually, as somebody who’s watched giant MySQL setups get migrated between versions, if you are running a real production database setup, you are in for just as much trouble with MySQL as you are with PostgreSQL, if not more)

Debian made it easy, but I never find the documentation when I need it. It’s something like this:

  • apt-get install postgresql-_new version_
  • pg_dropcluster –stop _new version_ main
  • pg_upgradecluster -v _new verson_ _old version>_ main
  • (test to make sure the upgrade works)
  • pg_dropcluster _old version_ main