Upgrading Postgres in parallel with your old version

Today I got caught up in PostgreSQL. A feature didn’t work as planned and when the mailing list responded to my plea for help it turned out the feature didn’t work fully in my version of Postgres, v.8.1.4.

Sad to say, I haven’t compiled a pgsql version for 13 months. I got so lazy that the last time I installed on my dev machine I just used the package that came with Kubuntu. Now I’m in a bind. I have a production machine and a dev machine to backup, upgrade and restore.

I learned one important lesson in this and that is to name your postgres installation directory the same as the current version. That kind of goes against my best judgement but it means that you can run two Postgres instances beside each other. Why would you want to do that? Take this line from the upgrade instructions:
Today I got caught up in PostgreSQL. A feature didn’t work as planned and when the mailing list responded to my plea for help it turned out the feature didn’t work fully in my version of Postgres, v.8.1.4.

Sad to say, I haven’t compiled a pgsql version for 13 months. I got so lazy that the last time I installed on my dev machine I just used the package that came with Kubuntu. Now I’m in a bind. I have a production machine and a dev machine to backup, upgrade and restore.

I learned one important lesson and that is to name your postgres installation directory the same as the current version. That kind of goes against my best judgement but it means that you can run two Postgres instances beside each other. Why would you want to do that? Take this line from the upgrade instructions:

To back up your database installation, type:

pg_dumpall > outputfile

To make the backup, you can use the pg_dumpall command from the version you are currently running. For best results … try to use the pg_dumpall command from [your new version of] PostgreSQL … since this version contains bug fixes and improvements over older versions. While this advice might seem idiosyncratic since you haven’t installed the new version yet, it is advisable to follow it if you plan to install the new version in parallel with the old version. In that case you can complete the installation normally and transfer the data later. This will also decrease the downtime.

Try to backup the existing database from the new version, which hasn’t been installed and can’t be installed until the old is backed up. That is, use the binary from your newly compiled version and run it on the old backend.

Seems like a problem, but if you run the new version in parallel you have a couple of benefits as I see it.

1. You don’t have to take your database down during upgrade! Once you are sure things are working on your new version then you simply flick the switch and it is nearly transparent. Don’t be like the company down the block who took their site down for 2 days to perform a db upgrade 🙂

2. If things go horribly wrong on your upgrade then you should still have the existing data, database and files to work from and find out the problem.

Keep these things in mind:

1. Change the install path by specifying it with the –prefix switch during ./configure Normally it installs to /usr/local/pgsql but because I already have a server installed and running there I chose to specify –prefix=/usr/local/pgsql-8.2.3

2. You can literally run it in parallel but change the port on the new server. In this fashion you can actually pipe the output of pg_dumpall to the input of the new server eliminating the SQL plain text file usually produced.

Make sure you block write access to the old version while performing the dump. When you are sure that everything dumped correctly you can shutdown the old server and start the new one on the old port.

There you have it. A nearly seamless database upgrade. A couple good reasons for running your new Postgres database upgrade in parallel with your current version.