Upgrading PostgreSQL from 8.2 to 8.4

This article takes you through upgrading from PostgreSQL 8.2 to 8.4.  If you are familiar with upgrading databases like MySQL then you probably think this is a trivial task.  It’s not trivial, but it’s not all that complicated either.  You just need a bit of understanding of how it all works and to think it through before you lose any data.

MY SETUP
A couple of years ago I installed PostgreSQL 8.2 using a yum repository at http://yum.pgsqlrpms.org/8.2/ .   This allows you to get all the updates to the 8.2 line for patching purposes, but does not introduce any new features.  My notes on this install process are available here.

THE PROBLEM
So, if you have a repo. clearly marked 8.2  why not just change your baser to show 8.4 and then yum clean all and yum update?  I tried this and it all seemed to work very well until I tested the database.  Apparently there are some fundamental data structure changes.

THEORY
In theory you can use a tool called pg_migrator (http://pg-migrator.projects.postgresql.org/)  to do an ‘in-place’ incremental upgrade, but if you need to skip a version then you may have an issue with the migrator and it my understanding that you can end up with a problem with the migrator simply because it’s not a mature application yet..  So do a dump and restore.

DUMP AND RESTORE
As a Mac user I’m a fan of the tool pgadmin III which allows you to manage databases in a more visual manner.  This is best for me because I’m not a full-time dedicated DBA.  Seeing things in a graphical interface makes all the difference.  You may want to grab a copy, especially if you are a casual PostgreSQL user.  However, all the commands below are designed to be used on the command line.

Continue reading