1 minute read

Major releases of Postgres require upgrading with migration of system tables to new formats. There are multiple ways to upgrade Postgres including pg_upgrade, logical replication, and dump/restore. In this tutorial you will learn how to perform the dump/restore method.

Preparing for migration

Password Hashing

Newer hub.docker images use SCRAM-SHA-256 by default in pg_hba.conf, this configuration will prevent Roles using md5 hashing from authenticating.

If upgrading from Postgres <= 10 or if the environment is still using the old MD5 passport hashing, you should migrate to using SCRAM-SHA-256. Alternatively set your configuration back to md5 however this is not recommended and has security implications.

To get a list of Roles with md5 hashed passwords that need to be reset you can execute the following SQL query:

SELECT rolname FROM pg_authid 
               WHERE rolpassword LIKE 'md5%'
                 AND rolcanlogin;


SELECT rolname FROM pg_authid
               WHERE rolpassword NOT LIKE 'SCRAM-SHA-256%'
                 AND rolcanlogin;

Passwords can then be reset with psql using

\password username

for each Role.

There are many online tutorials for this process: https://www.cybertec-postgresql.com/en/from-md5-to-scram-sha-256-in-postgresql/

Volume mapping

The dump/restore method requires that additional disk space equal to the current database size plus a little bit more. A new Docker volume or local disk path should be mapped to the new container. Do not map the old data directory to the new container.


For all commands below, ${old_pg_container} will refer to our source container we wish to upgrade, ${new_pg_container} will refer to our upgraded container.

  1. Stop all applications writing to the current database. Leave the ${old_pg_container} container running.
  2. Start the ${new_pg_container} with the upgraded version with docker run or by creating a new container via docker-composer, mounting a new persistent data volume
  3. Install any extensions into the new container, but do not install the schema with CREATE EXTENSION
  4. Migrate the SCHEMA and data from ${old_pg_container} to ${new_pg_container}
     docker exec -i ${old_pg_container} su -c "pg_dumpall --clean" postgres \
      | docker exec -i ${new_pg_container} su -c "psql" postgres
  5. Perform a Vacuum to ensure statistics are gathered
     docker exec -i ${new_pg_container} su -c "vacuumdb -a -z" postgres
  6. Stop ${old_pg_container}
  7. (Optional) Rename ${new_pg_container} to ${old_pg_container} if you do not wish to modify application configuration
     docker rename ${new_pg_container} ${old_pg_container}

    or if running via docker-compose, update the configuration of the old container to point to the new docker image and new data volume.

You should now be able ot start your application(s).