Upgrade PostgreSQL with Docker Compose

An stable diffusion impression of two elephants looking like the PostgreSQL elephant walking a trail

Applications can have a lot of benefit from new PostgreSQL versions. In terms of speed, featureset as well as security, the importance of running the latest (patch) release cannot be underestimated. However, to reap these benefits, we have to do something that many dread: database migration.

This guide aims to conceptually talk you through the process of migrating between any major (or minor) version of PostgreSQL. For the sake of clarity, provided snippets will be simplified. However, they should fit any well architected 12-factor-app.

Attention, here be dragons! Always make sure you have a restorable backup of your data. You would not be the first person to discover their backup is corrupted, after things have gone south.

Upgrading database versions, especially major versions is not always trivial. Before you start, it can be worth it to check for backwards incompatible changes between your current release and the release you’re targeting for in their release docs.

After you’ve taken all precautions, you’re ready to do the actual work.

Below is our original docker compose file. The important detail to note here is that our database service contains the version identifier as suffix. This assures that once we’re finished migrating, we can point our app to the new database by simply changing the connection string in our app config (e.g. DATABASE_URL).

version: "3.8"

services:
  app:
    image: web-app:1.0
    environment:
      DATABASE_URL: postgresql://postgres:secret@db_postgres_146/postgres

  db_postgres_146:
    image: postgres:14.6
    volumes:
      - db_postgres_146:/var/lib/postgresql/data
      - db_backup:/backup
    environment:
      POSTGRES_PASSWORD: secret

volumes:
  db_backup: {}
  db_postgres_146: {}

Our next step is to add our target database container. To do this, we create a new named volume and a new service. For convenience, we keep the name of the service and the name of the volume in sync.

version: "3.8"

services:
  app:
    image: web-app:1.0
    environment:
      DATABASE_URL: postgresql://postgres:secret@db_postgres_146/postgres

  db_postgres_146:
    image: postgres:14.6
    volumes:
      - db_postgres_146:/var/lib/postgresql/data
      - db_backup:/backup
    environment:
      POSTGRES_PASSWORD: secret

  # we add the new database instance
  db_postgres_151:
    image: postgres:15.1
    volumes:
      - db_postgres_151:/var/lib/postgresql/data
      - db_backup:/backup
    environment:
      POSTGRES_PASSWORD: secret

volumes:
  db_backup: {}
  db_postgres_146: {}

  # and use a separate volume
  db_postgres_151: {}

Now that our docker compose file is updated, we can start our migration procedure. Please note that the single quotes are required here so that our $POSTGRES_USER variable is evaluated in our container and not by our host shell executing the docker compose statements.

# spin up our new (empty) database
docker compose up -d db_postgres_151

# dump our existing database
docker compose exec -it db_postgres_146 \
  /bin/bash -c 'pg_dumpall -U $POSTGRES_USER > /backup/20230127.sql'

# restore that dump in our new database
docker compose exec -it db_postgres_151 \
  /bin/bash -c 'psql -d $POSTGRES_DB -U $POSTGRES_USER < /backup/20230127.sql'

After we have migrated, all we have to do is update the DATABASE_URL environment variable in our docker compose file, reload the container and check whether your application functions correctly.

If you have confirmed everything is running fine, you can drop the old database service — and volume if you are confident. The docker compose file will look something like this after migration and cleanup:

version: "3.8"

services:
  app:
    image: web-app:1.0
    environment:
      DATABASE_URL: postgresql://postgres:secret@db_postgres_151/postgres

  db_postgres_151:
    image: postgres:15.1
    volumes:
      - db_postgres_151:/var/lib/postgresql/data
      - db_backup:/backup
    environment:
      POSTGRES_PASSWORD: secret

volumes:
  db_backup: {}
  db_postgres_151: {}

Congratulations, you can now enjoy the safety and all the new cool features of the most up-to-date release of PostgreSQL!