Unsupported versions: 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

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 "7.1.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 dump your database installation, type:

    pg_dumpall > outputfile
    If you need to preserve the 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. Check Section 8.1.4 if you need to do this.

    Make sure that you use the pg_dumpall command from the version you are currently running. 7.1.3's pg_dumpall should not be used on older databases.

  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
  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 still need it later on. Use a command like this:

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

After you have installed PostgreSQL 7.1.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.

You can also install the new version in parallel with the old one to decrease the downtime. These topics are discussed at length in Section 8.3, which you are encouraged to read in any case.