PostgreSQL is a full-fledged enterprise-grade database brought into the public domain. Many homemade webmasters now have to deal with what is a very viable — if not an outright better — alternative to the raging popularity of a significantly simpler MySQL.
Yet, for all its merits and addictive features, PostgreSQL is a beast to upgrade between major version releases.
If you were going from 8.2.x to the latest stable at the time of this writing, 8.3.x, then the move from the 8.2 series to 8.3 would require you to dump/restore your entire database. Not a simple chore if you have a 100 GB worth of critical data, but you have no choice as the innards of PG typically change between “major upgrades”. For this, replication technologies such as Slony are a useful option as you can install the new database separately, replicate the data over, and then make a switch when the two databases are mirrored — thereby avoiding the downtime of the hassle (here’s an informative PGCON presentation).
Fortunately, minor version upgrades are much simpler.
No dump/restore is needed. If you were to go from, say 8.2.3 to 8.2.9, all you would have to do is to upgrade the RPMs. I’m assuming Linux here but the same would work for other platforms.
- Check what version you have currently installed:
rpm -qa | grep postgres | tee /root/rpm_pgsql
- Stop your postgres server
/sbin/service postgresql stop
- We will now simply execute the RPM upgrade command, which of course assumes that you have the latest RPMs downloaded from the PG website. Notice below that they’re all lumped into the same command to prevent any (unlikely) possibility of dependency issues, so make sure all of this appears on one line — the backslash is included here for formatting (so it won’t break on the command line).
rpm -Uvh postgresql-8.2.9-1PGDG.rhel4.i386.rpm postgresql-contrib-8.2.9-1PGDG.rhel4.i386.rpm postgresql-devel-8.2.9-1PGDG.rhel4.i386.rpm postgresql-libs-8.2.9-1PGDG.rhel4.i386.rpm postgresql-server-8.2.9-1PGDG.rhel4.i386.rpm
- Hopefully that went without a hitch. Now restart the postgresql server:
service postgresql restart
That’s it. That should do it. It is highly unlikely that you’ll see errors. And if you’re running an older 8.2.x, it’s highly recommended you upgrade to 8.2.9 anyway due to performance increases and some bug fixes.