New versions of PostgreSQL are periodically released. Most likely, the storage format for internal data change between major releases. If you decide to update your system to a new version, then you will need to back up your data and restore it on the new server using pg_dump.

The only time this is not necessary is when you are upgrading to a new patch level that is compatible with your current system. Patches that are not compatible are patches applied to different versions. Patches that are compatible apply to the same version release. For instance, the patches for 7.0.2 and 7.0.3 are compatible because they are for version 7.0, whereas patches for 6.3.0 and 7.0.3 are not compatible because they span over two different release versions. Migration between different versions require a backing-up and then restoring your data because the system cannot re-use the existing data on disk.

To decrease the down time, you can install the new server in a different directory and run both the new and old server in parallel, but on different ports. Then use the following command to transfer your data:

    pg_dumpall -p 4321 | psql -d template1 -p 6543

After the data is transfered, shut down the old server and start running the new server on the port used by the old server. Check to make sure no updates/changes to the database are processed after you run the pg_dumpall command. If this check is not performed, then those updates/changes to the database will be lost. Also, you should perform tests on the new structure before you transfer the data over.

If it is not possible for you to run parallel machines with the old and new version, then use these steps:

  1. Perform the back up of the old database before installing the new version:

          pg_dumpall > backup

  2. Bring down the old server:

          kill -INT 'cat /usr/local/pgsql/'

  3. Move the old version of PostgreSQL:

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

  4. Install the new version:

          cd /usr/src/postgresql-7.1
          gmake install
          initdb -D /usr/local/pgsql/data
    The last line of code over-writes the existing data directory and rebuilds the data directory for the new version.

  5. Start up the new server using the sysV start up script:

           service postgresql start
    (For information on how to use the script command, refer to the section on Configuring the PostgreSQL SysV Script .)

    Or use the command:

           postmaster -D /usr/local/pgsql/data

  6. Restore the data:

          psql < backup

Note Safety Precaution

Upon installing the new version, it is a good idea to rename your old data directories and keep the old file system in a separate place. You should create completely new folders and directory files to hold the new file system. This can help prevent situations where you discover that there is something missing from this new version that you originally had in the old version before the upgrade. If that situation does occur, you will need to re-install the older version.

Backing up will take a large amount of space on your system. If you can use this method, then it can save you time in the long run.

Help us make a better book, leave feedback. (