Upgrading Postgres between major revisions, the easy way

When you look at the documentation for the pg_update command, you quickly realize that there’s a lot more to it than just an in-place upgrade, especially if you run it in containers like docker. The official upgrade docs mention using pg_dump, but it may be a bit involved and different when applying it to docker and alike container systems. That’s why I wrote this, and also to help myself when I run into this issue again later.

By dumping the output locally and then feeding it back to psql it is actually not super involved, and so far I’ve not had issues with it.

Docker

Let’s jump right in:

  1. Run the appropriate version of the postgres container to open the database. If you use docker-compose, adjust image: postgres:latest to something like image: postgres:14, then bring up the container (just the database, not the app) with something like docker-compose up postgressserver
  2. Dump the database to a file, using something like this: docker exec mypostgresserver pg_dumpall -U mydbuser > db_15-10-2022, this should store the file outside of the container where you’re running the command from. Beware of this when dumping large databases.
  3. Turn off the server again, and readjust tag back to latest or the current version like 15.
  4. (re)move the entire database folder / container storage, so that you have a fresh and empty database server.
  5. Run and let the fresh database server initialize
  6. Copy your database dump into the database server folder, so you can access it from inside the container.
  7. Restore the database from the dump with something like docker exec mypostgresserver psql -U mydbusername -d mydatabasename -f /var/lib/postgres/sql/db_15-10-2022
  8. it should be up and running again!

non-docker

If you’re not running docker, you have to find another way to run the older version of postgres so you can export the data with pg_dumpall. On Fedora/CentOS/Redhat you can use dnf downgrade in some cases, but you can also spin up a container like above and mount the database folder inside the container. Alternative you can download the correct version from the Postgres website. The official documentation’s method of upgrading is mostly written for non-docker approaches, so it may be useful in this case

Updated: