22.4. Migration Between Releases

As a general rule, the internal data storage format is subject to change between major releases of PostgreSQL (where the number after the first dot changes). This does not apply to different minor releases under the same major release (where the number after the second dot changes); 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, you can simply replace the executables and reuse the data area on disk. 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 using a data area with an incompatible version of PostgreSQL, so no harm can be done by confusing these things. It is recommended that you use the pg_dump program from the newer version of PostgreSQL to take advantage of any enhancements in pg_dump that may have been made. The precise installation procedure is not the subject of this section; those details are in Chapter 14, Installation Instructions.

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. See Chapter 19, Client Authentication for information on how to prohibit access. 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 backup 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 ~/postgresql-8.0.0beta5
gmake install
initdb -D /usr/local/pgsql/data
postmaster -D /usr/local/pgsql/data
psql template1 < backup

See Chapter 16, Server Run-time Environment about ways to start and stop the server and other details. The installation instructions will advise you of strategic places to perform these steps.

You will always need a SQL dump (pg_dump dump) for migrating to a new release. Filesystem-level backups (including on-line backups) will not work, for the same reason that you can't just do the update in-place: the file formats won't necessarily be compatible across major releases.

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.