How to Upgrade Your PostgreSQL Version Using Docker

Upgrade your PostgreSQL version and migrate your data using Docker

Photo by Campaign Creators on Unsplash.

Recently, I had to upgrade my local PostgreSQL version from 10.10 to 11.8 to mirror the same version that exists in the production environment. I originally thought the upgrade would be simple: Turn off the container, update the docker-compose service to reference the new version, and then spin the container.

However, as soon as the container went up, an error started showing up in the logs:‌

The error is very explicit: Because it was created by an earlier version, my data was incompatible with the new version. When I searched for possible solutions to this problem, all I could find was related to macOS and relied on using Brew to execute some shell commands that would take care of upgrading the data and making it compatible with the new version.

The problem is that I use Docker for this (well, for everything web-related, really), and since I use Linux Ubuntu, I can’t use Brew (it’s only for macOS). So I needed a solution that was quick, reliable, and that leveraged Docker (preferably) so that I didn’t need to install things on my local machine.

After a lot of searching and some discovery of my own, I came up with the following simple three-step solution.

Step 1: Make a Backup of Your Current Data

With the old PostgreSQL service still running, execute the following command on the directory where the docker-compose file is located:

docker-compose exec {service_name} pg_dumpall -U {postgres_user} > dump.sql

This command makes a SQL file that will contain all our current data from every database, roles, etc. We’ll use this later on when we import it to the new PostgreSQL version.

If you want to do it without relying on a docker-compose file configuration, you can spin up a container dedicated to this process with the following command:

docker run --rm -v ${CWD}:/tmp -v {local_postgres_data_folder}:/var/lib/postgresql/data -w /tmp {postgres_image}:{current_version_tag} pg_dumpall -U {postgres_user} > dump.sql

Step 2: Delete Your Current Service’s Mapped Volume

We can’t use the old data structure because it’ll be incompatible with the new version’s. There’s no other option here but to remove the volume our old PostgreSQL service uses so that we can update the version tag later and let the service create the base folders/files as if it was a first clean run.

Let’s find where the volume is mapped to, open a terminal, go to that location, and run the command to delete it completely:

sudo rm -rf {postgres_volume_directory}

Step 3: Update the PostgreSQL Version

With a backup of our current data, we can now shut down the old PostgreSQL service container, update the docker-compose configuration to reference the newer version tag, and spin that service back up.

After the service is done starting, it should be referencing a new volume with freshly created files that are supported by the new version. We only need to kick off the import process so that our backup can also be used by this new version. We can do it by using the internal bash terminal, with the following command:

# Connect to the container's bash terminal
docker-compose exec {service_name} bash
# Now we run the import command
psql -U {postgres_user} -d {default_postgres_database} < {mapped_volume_folder_path}/dump.sql

Note: This command only works if you have a volume mapped inside the container on your docker-compose file.

If you prefer a one-off command to run this import, you can use this:

docker run --rm -v ${CWD}:/tmp -v {local_postgres_data_folder}:/var/lib/postgresql/data -w /tmp {postgres_image}:{new_version_tag} psql -U {postgres_user} -d {default_postgres_database}  < dump.sql

And that’s it! We now have a new version of PostgreSQL running and we kept all our data compatible with that version.