- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
ISBN 9781906966072
RRP £9.95 ($14.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

10.4.1 Migrating data via pg_dump

To dump data from one major version of PostgreSQL and reload it in another, you must use pg_dump; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of enhancements that might have been made in these programs. Current releases of the dump programs can read data from any server version back to 7.0.

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 postgres -p 6543

to transfer your data. Or you can use an intermediate file if you wish. Then you can shut down the old server and start the new server using the port the old one was running on. You should make sure that the old database is not updated after you begin to run pg_dumpall, otherwise you will lose those updates. See section 5 Client Authentication for information on how to prohibit access.

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 old server, move the old version out of the way, install the new version, start the new server, and restore the data. For example:

pg_dumpall > backup
pg_ctl stop
mv /usr/local/pgsql /usr/local/pgsql.old
# Rename any tablespace directories as well
cd ~/postgresql-9.0.1
gmake install
initdb -D /usr/local/pgsql/data
postgres -D /usr/local/pgsql/data
psql -f backup postgres

See section 3 Server Setup and Operation about ways to start and stop the server and other details. The installation instructions will advise you of strategic places to perform these steps.

Note: When you “move the old installation out of the way” it might no longer be perfectly usable. Some of the executable programs contain absolute paths to various installed programs and data files. 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. (This problem is rectified in PostgreSQL version 8.0 and later, so long as you move all subdirectories containing installed files together; for example if ‘/usr/local/postgres/bin/’ goes to ‘/usr/local/postgres.old/bin/’, then ‘/usr/local/postgres/share/’ must go to ‘/usr/local/postgres.old/share/’. In pre-8.0 releases moving an installation like this will not work.)

ISBN 9781906966072The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration GuideSee the print edition