Upgrading to PostgreSQL 7.3.x

Upgrading from a Previous Version

The internal data storage format changes with new releases of PostgreSQL. Therefore, if you are upgrading an existing installation that does not have a version number "7.3.x", you must back up and restore your data as shown here.

These instructions assume that your existing installation is under the /usr/local/pgsql directory, and that the data area is in /usr/local/pgsql/data. Substitute your paths appropriately.

1.   Make sure that your database is not updated during or after the backup. This does not affect the integrity of the backup, but the changed data would of course not be included. If necessary, edit the permissions in the file /usr/local/pgsql/data/pg_hba.conf (or equivalent) to disallow access from everyone except you.

2.   To back up your database installation, type:

pg_dumpall > outputfile

If you need to preserve OIDs (such as when using them as foreign keys), then use the -o option when running pg_dumpall. pg_dumpall does not save large objects.

To make the backup, you can use the pg_dumpall command from the version you are currently running. For best results, however, try to use the pg_dumpall command from PostgreSQL 7.3, 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.

3.   If you are installing the new version at the same location as the old one then shut down the old server, at the latest before you install the new files:

kill -INT `cat /usr/local/pgsql/data/postmaster.pid`

Versions prior to 7.0 do not have this postmaster.pid file. If you are using such a version you must find out the process id of the server yourself, for example by typing ps ax | grep postmaster, and supply it to the kill command.

On systems that have PostgreSQL started at boot time, there is probably a start-up file that will accomplish the same thing. For example, on a Red Hat Linux system one might find that

/etc/rc.d/init.d/postgresql stop

works. Another possibility is pg_ctl stop.

4.   If you are installing in the same place as the old version then it is also a good idea to move the old installation out of the way, in case you have trouble and need to revert to it. Use a command like this:

mv /usr/local/pgsql /usr/local/pgsql.old

After you have installed PostgreSQL 7.3, create a new database directory and start the new server. Remember that you must execute these commands while logged in to the special database user account (which you already have if you are upgrading).

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data

Finally, restore your data with

/usr/local/pgsql/bin/psql -d template1 -f outputfile

using the new psql.

Migration Between Releases

As a general rule, the internal data storage format is subject to change between releases of PostgreSQL. This does not apply to different "patch levels", these always have compatible storage formats. For example, releases 7.0.1, 7.1.2, and 7.2 are not compatible, whereas 7.1.1 and 7.1.2 are. When you update between compatible versions, then you can simply reuse the data area in disk by the new executables. Otherwise you need to "back up" your data and "restore" it on the new server, using pg_dump. (There are checks in place that prevent you from doing the wrong thing, so no harm can be done by confusing these things.)

The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like

pg_dumpall -p 5432 | psql -d template1 -p 6543

to transfer your data, or use an intermediate file if you want. Then you can shut down the old server and start the new server at the port the old one was running at. You should make sure that the database is not updated after you run pg_dumpall, otherwise you will obviously lose that data. In practice you probably want to test your client applications on the new setup before switching over.

If you cannot or do not want to run two servers in parallel you can do the back up step before installing the new version, bring down the server, move the old version out of the way, install the new version, start the new server, restore the data. For example:

pg_dumpall > backup pg_ctl stop mv /usr/local/pgsql /usr/local/pgsql.old cd /usr/src/postgresql-7.3 gmake install initdb -D /usr/local/pgsql/data postmaster -D /usr/local/pgsql/data psql template1 < backup

Note: When you "move the old installation out of the way" it is no longer perfectly usable. Some parts of the installation contain information about where the other parts are located. This is usually not a big problem but if you plan on using two installations in parallel for a while you should assign them different installation directories at build time.