Migrating Postgresql Databases the Easy Way

23 June 2009

When you upgrade Postgresl to a new major version (e.g. 8.1 to 8.3), all databases need to be dumped from the old version and loaded in to the new version. It’s not difficult, but on Debian there’s a really easy way.

Debian has pg_createcluster, pg_dropcluster and pg_upgradecluster (plus a few others). The one I’m referring to here is pg_upgradecluster.

It takes the version and cluster name of the databases you want to upgrade.

e.g. if you’ve installed Postgresql 8.3 and have databases in 8.1, just run:

# pg_upgradecluster 8.1 main

This upgrades the databases in the “main” cluster under version 8.1 and puts them under “main” in version 8.3. If you already have a “main” cluster in 8.3, you’ll need to drop it first.

This little tool not only dumps and loads the database, but it also changes the config so 8.3 runs on the standard port previously used by 8.1 (or whatever your older version). A painless way to upgrade.

Filed under: Postgresql — Scott @ 11:45 am