Upgrade Postgres in a Docker/Podman Container Network

I had to upgrade my Postgres from an EOL 11.2 version to 16.2 recently. And we use it in a “docker compose” setup with a custom network, persistent volume, and needed to keep the data. And it had to be easy for users in the field to update, as we are not a SaaS.

I hunted for a bit then found out “well upgrading is spinning up the new one and copying data over”. The wrinkle was this is in a container network with persistent storage. So we had to do it correctly in the right steps.

This goes over how we successfully did that. I am going to use our OpenRMF Professional application to showcase this as it is a real use case for a production solution. Your steps should be similar.

Copying data between Postgres instances in a container network

Backup your current data

Please do this first, however you normally (should) do it. Whether a snapshot, tar xvf type of backup, or whatever you do. Preserve the data first.

Setup a separate YML file to bring up Postgres

We have a few YML files when we build our docker/podman network with compose. You do this migration/copying of data while your production stack is running. So we added another one and run it, attaching it to the running container network. And storing the data in a separate persistent volume to use.

Figure out what Portainer image and tag to use at https://hub.docker.com/_/postgres/tags. Chose one that works for your application (we use Keycloak) and has a very low count of vulnerabilities if possible.

services:
  postgres16:
    image: docker.io/postgres:16.2-alpine
    container_name: openrmfpro-postgres16
    restart: on-failure:5
    ports:
      - 5432
    environment:
      - POSTGRES_DB=xxxxxxxxxxxxxx
      - POSTGRES_USER=xxxxxxxxxxxxxxx
      - POSTGRES_PASSWORD=yyyyyyyyyyyyyyyy
    networks:
      - openrmfpro
    volumes:
      - openrmfpro-keycloak-postgres16:/var/lib/postgresql/data

networks:
  openrmfpro:
    external: true

volumes:
  openrmfpro-keycloak-postgres16:

The above YML file is basically what we used. We setup a new volume name, similar to the current one, as we will need to reference that later. Update your database, user, and pwd as required.

We did use the same database, user, and password for the newer configuration so it matches easily. K.I.S.S.

Start the new YML file configuration

Next you would run something like the below to attach the new YML file (we named ours postgres16upgrade.yml) configuration to your running container network.

docker compose -f ./postgres16upgrade.yml up -d

Note that now you will have two Postgres containers running if you do a “ps” command to list them all.

Run the Upgrade Commands

Now that both are up you need to run a command similar to the following. It goes into the one container running inside the network, and basically “copies” all data over to the other one using Postgres commands.

docker exec openrmfpro-postgres pg_dumpall -U openrmf-keycloak | \
docker exec -i openrmfpro-postgres16 psql -U openrmf-keycloak

You will see an image similar to the one at the top of this article if your login/password/configuration is correct.

Once done, to be safe, we logged into the newer container and reset the password to what the current (older) one is. You do this from a terminal window on the machine running the container stack. Each command below is on its own line. You need to use your own username w/o the {{ }} brackets.

docker exec -it openrmfpro-postgres16 /bin/sh 

psql -h localhost -p 5432 -U {{ USER-NAME-GOES-HERE }} -W

\password {{ USER-NAME-GOES-HERE }}

\q

exit

When you do the \password command it will ask you for the new password. Use the one you have in the older Postgres instance for that user. That keeps them the same.

The \q and exit commands just get you back to your terminal window.

Stop the Newer YML configuration

Now stop the extra YML you just ran to spin up the newer Postgres image.

Then stop your full application container stack so nothing is running.

Run something like docker ps to list out running processes and make sure your application is not running.

Update your Current Production YML file

When that is done, you need to update 3 things in your YML file(s):

• the older Postgres image tag version
• the volume name in the “postgres” YML configuration area to the new one
• the volume name where they are all listed under volumes: section

Restart and Test!

With those three things done to use your selected new image and newer Postgres persistent volume, bring back up your full container application stack.

Test your application to make sure all works, the stack comes up with the proper database/user/password you set, and you can interact with it properly.

You basically pointed it to the new Postgres image, the new volume you made, and the data you copied over from the older one.

If all is done, you can use a docker volume rm {{ name of volume }} to remove the older volume name. Use docker volume ls to find out which full name it is.

CONGRATULATIONS! That was not too hard.