PostgreSQL 8.3 was announced today (Feb. 4). As a Pgsql newbie (I started testing it less than two weeks ago), I thought it would be a useful experience to see what the upgrade process looked like. MySQL (which I have years of experience using) is pretty much a snap to upgrade (I've been upgrading since the 3.x days).

FYI: I've been testing PostgreSQL on a Linux box. But, my experience should be common across platforms. I've been installing from source code to get a good feel of the system. Configuring, compiling, and installing 8.3 was straight-forward. The shock came when I restarted the server and read the error messages:

FATAL: database files are incompatible with server

DETAIL: The data directory was initialized by PostgreSQL version 8.2, which is not compatible with this version 8.3.0

Reading more carefully through the release notes, I read: A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

This was unbelievable to me. MySQL had required some privilege table changes/fixes and a few other things a couple of upgrades ago. But, I never had to dump and reload my entire database server. And, yet, this is what PostgreSQL forced me to do. Since I am just testing, this was not a huge task. But, what if I had hundreds of databases containing many gigabytes of data? This process would take forever.

The other thing I ran into is that the generic SQL dump (pg_dumpall --clean --column-inserts --verbose > db.out) would not restore (psql -f db.out postgres) at all. The PostgreSQL specific dump (pg_dumpall > db.out) did, however. And, fortunately, I had produced one data dump of each type. However, I also learned that PostgreSQL doesn't recreate the databases during the restore process. I had to manually create each database before restoring the databases' contents. MySQL restores never forced me to do that.

After upgrading and restoring my databases, the Webmin PostgreSQL module and phpPgAdmin could both see and manage the restored databases. However, the Windows client pgAdmin III 1.8.2 (released on Feb. 3) couldn't connect to the pgsql server on the Linux box. It had been working before the upgrade.

I'm going to continue exploring and learning more about PostgreSQL as an insurance for the possibility that Sun's acquisition of MySQL creates problems down the road. But, I think I can see why MySQL is so much more popular than PostgreSQL despite pgsql's richer feature set.