|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
1.4 If You Are Upgrading
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 “8.2.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.
- 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.
To back up your database installation, type:
pg_dumpall > outputfileIf you need to preserve OIDs (such as when using them as foreign keys), then use the
-ooption when running pg_dumpall. 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 8.2.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.
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:
pg_ctl stopOn 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 Linuxsystem one might find that
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 8.2.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/postgres -D /usr/local/pgsql/data
Finally, restore your data with
/usr/local/pgsql/bin/psql -d postgres -f outputfile
using the new psql.
Further discussion appears in section 10.5 Migration Between Releases, which you are encouraged to read in any case.
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|