This page in other versions: Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0

14.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.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 back up your database installation, type:

    pg_dumpall > outputfile

    If you need to preserve OIDs (such as when using them as foreign keys), then use the -o option 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.1.23, 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.

  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:

    pg_ctl stop

    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


    Very old versions might not have pg_ctl. If you can't find it or it doesn't work, find out the process ID of the old server, for example by typing

    ps ax | grep postmaster

    and signal it to stop this way:

    kill -INT processID
  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 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.1.23, 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 postgres -f outputfile

using the new psql.

Further discussion appears in Section 23.4, which you are encouraged to read in any case.


Nov. 8, 2005, 7:21 p.m.

A note to Windows users:

You can follow this guide with the following changes:

- pg_dumpall > outputfile might not work if you don't have a database superuser called "Administrator". Use pg_dumpall --username=root or whatever your superuser is called instead.
- Instead of step 3, just go to start -> run -> services.msc and stop the PostgreSQL service
- Instead of step 4, run the PostgreSQL uninstaller (from control panel -> add/remove programs) and then rename what remains of the previous folder (just incase you need it later)
- After installing 8.1, you DON'T need to perform the following:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
- Instead of "/usr/local/pgsql/bin/psql -d postgres -f outputfile" you should run "psql -d postgres -U superusername -f outputfile" from the command prompt after entering the postresql/8.1/bin directory

April 6, 2006, 5:35 a.m.

Some conclusions from my experience migrating from 8.0 to 8.1.3

If your database(s) was created in 8.0 using "template1" as a template, it may contain definitions of various functions imported from loadable modules. Some of these functions were renamed; some were built into server (therefore they need not to be imported from loadable modules and moreover these loadable modules don’t exist in 8.1.3).

Running script generated by pg_dumpall will lead to errors (something like "file '$libdir/admin' not found" or "there is no such function "pg_file_stat(text)").

I've just removed all CREATE FUNCTION, ALTER FUNCTION and CREATE VIEW statements (ALTER TABLE statements related to views were removed too). Of course, remove only statements related to ‘foreign’ objects.

Also, you may found it necessary to remove all the code modifying "template1" database (After getting working script, I’ve reinstalled 8.1.3 version to restore "template1" to it's fresh state for sure).

See section 19.3. Template Databases for details about template databases.

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group