Performing a Major PostgreSQL Upgrade with pg_dumpall

For most major upgrades using a utility such as pg_upgrade or a replication tool such as pglogical will be the best solution. However if these options are not available, pg_dumpall can be used to perform a major upgrade. What follows is a guide on how you can safely upgrade your database to a newer version of PostgreSQL with pg_dumpall.

Install and Initialize New System

You will first need to install the latest PostgreSQL binaries on the new system (hostname new in this example). Once the binaries are installed you will need to initialize the new instance with the initdb command:

initdb -D /path/to/pgdata/

The new instance can now be started

pg_ctl -D /path/to/pgdata/ -l logfile start

Confirm Connectivity

You can confirm the new instance is started and is accessible by running the following on the new instance:

psql -U postgres

To test connectivity to the old system (hostname old in this example) by running the following on the new instance:

psql -h old -U postgres

Migrate Data

Once connectivity is confirmed the data can be migrated from old to new with the following command run on the new instance:

pg_dumpall -h old -U postgres | psql ---single-transaction --no-psqlrc -h new -U postgres

By running the restore in a single transaction, if any one command fails the entire migration will be rolled back. This helps maintain data consistency. Check out the documentation for more info on psql.

After the command completes all data has been migrated, however the configuration from the old system will need to be migrated manually. You will also need to update any application settings to point to the new instance. The old system should be kept until sufficient testing is performed on the new instance.