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.
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 -D /path/to/pgdata/
The new instance can now be started
pg_ctl -D /path/to/pgdata/ -l logfile start
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
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.
November 26, 2018 •More by this author