|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
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.
The internal data storage format typically changes in every major release of PostgreSQL. Therefore, if you are upgrading an existing installation that does not have a version number of “9.0.x”, you must back up and restore your data. If you are upgrading from PostgreSQL “9.0.x”, the new version can use your current data files so you should skip the backup and restore steps below because they are unnecessary.
If making a backup, make sure that your database is not being updated.
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 9.0.1, 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.
Shut down the old server:
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 system one might find that this works:
If restoring from backup, rename or delete the old installation
directory. It is a good idea to rename the directory, rather than
delete it, in case you have trouble and need to revert to it. Keep
in mind the directory might consume significant disk space. To rename
the directory, use a command like this:
mv /usr/local/pgsql /usr/local/pgsql.old
- Install the new version of PostgreSQL as outlined in section 1.5 Installation Procedure.
Create a new database cluster if needed. 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
- Restore your previous ‘pg_hba.conf’ and any ‘postgresql.conf’ modifications.
Start the database server, again using the special database user
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
Finally, restore your data from backup with:
/usr/local/pgsql/bin/psql -d postgres -f outputfileusing the new psql.
Further discussion appears in section 10.4 Migration Between Releases, including instructions on how the previous installation can continue running while the new installation is installed.
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|