Major PostgreSQL upgrades offline

How to perform a major PostgreSQL upgrade by dumping all the instance content and restoring it in the target server.

IMPORTANT : We recommend to perform this procedure in a test environment first and to test all the applications extensively against the new PostgreSQL major version. This step is always recommended because every change has a risk, and a well-planned upgrade requires testing and preparation. You should allow time to plan and test your upgrade. Moreover, testing this procedure will allow you to measure the amount of downtime required and plan accordingly.

Two running PostgreSQL instances:

1. the old one you want to migrate away (let's call it oldpg), and
2. an empty new one you want to move on (newpg)

For the rest of this article, we'll assume the PostgreSQL instances run on two different servers.

We'll use the pg_dump and pg_restore utilities. To take advantage of enhancements that might have been made in newer versions of these, we suggest to use the ones of the latest major version. Therefore, you have two options:

1. Allow connections from the new instance to the old one using an user with superuser permissions.
2. Alternatively, install the upgraded client on the old server and use that one to perform the following pg_dump commands, then copy the generated files on the new server. We suggest to use the full path to the pg_dump executable in this case to be sure you're using the one of the correct major version.

We also advise that you put all commands in a bash script.

You're now ready to perform the upgrade:

3. Stop the applications that point to the current PostgreSQL instance. This is necessary in order to avoid updates to the source database, since no change performed after we start copying the content of a PostgreSQL database will be dumped. This effectively requires downtime for the whole time the dump and restore operations run. For this reason we speak of offline upgrade. You do not need to do this in case you are testing the procedure (consider though that pg_dump might have an impact on your source database).
4. Dump the global objects and the databases of oldpg. Add the correct connection parameters for your infrastructure to the commands (e.g.: host, port, user, ...) or use the environment variables (PGHOST, PGPORT, PGUSER, ...)
5. Dump global objects (roles and tablespaces):

pg_dumpall -g > globals.out

6. Dump the databases. We can then dump a single database like this:

pg_dump -Fc -f <db_name>.dump <db_name>

If we want to backup the entire cluster we can run multiple pg_dump tasks, one against each database. For example:

for db in $(psql -qAt -c "SELECT datname from pg_database where not datistemplate datname NOT IN ('postgres')")
do
pg_dump -Fc -f $db.dump $db
done

This command produces a file <dbname>.dump for every not default database present inside the PostgreSQL instance.

7. Restore the backup on newpg. Again, add the correct connection parameters for your infrastructure to the commands.

8. Restore the global objects:

psql -f globals.out

9. Restore the dumps.

You can restore a single dump with

pg_restore -d postgres -C <dbname>.dump

Or all of them with a for cycle:

for file in *.dump
do
pg_restore -d postgres -C $file
done

10. Run ANALYZE on all the database that have been restored on newpg to gather the statistics for the query planner.

psql <dbname> -c "ANALYZE"

11. Verify that the content of newpg is the expected one.
12. Point the applications to the newpg and restart them.