I was struggling with a pretty common task:

Running a PostgreSQL DB in docker-compose and facing a DB migration towards a new PosgreSQL Major Release.

There are tolls out there like https://github.com/tianon/docker-postgres-upgrade and ofcourse https://www.postgresql.org/docs/9.6/pgupgrade.html.

Somehow docker-postgres-upgrade didn’t work (I didn’t want to fiddle around with it too much and the issues all around pg_hba.conf and users ) and for pg_uprade you need to have a running container with the new PostgreSQL major version (which seems to be a pretty misconception in this case).

I also tried the

pg_dumpall > dump.sql

and

psql < dump.sql 

approach.

And this brought me to a leaner way by omitting the dump.sql file and its copying around.

First I’ll setup a docker-compose that starts the new major and the old major (in this case 12 and 13) of PostgreSQL:

version: '3'
services:
  pg-13:
    image: postgres:13-alpine
    restart: unless-stopped
    environment:
    - POSTGRES_USER=${POSTGRES_USER}
    - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
    - POSTGRES_DB=${POSTGRES_DB}
    volumes:
    - ./volumes/pg-13/data:/var/lib/postgresql/data
  pg-12:
    image: postgres:12-alpine
    restart: unless-stopped
    environment:
    - POSTGRES_USER=${POSTGRES_USER}
    - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
    - POSTGRES_DB=${POSTGRES_DB}
    volumes:
    - ./volumes/pg-12/data:/var/lib/postgresql/data

The path

./volumes/pg-12/data

contains the actual data for the PostgreSQL 12 instance, so you might want to operate on a copy/backup in case something goes wrong.

The environment variables are retrieved by an .env file next to the docker-compose.yaml, you can use the .env approach or adjust these values:

    environment:    
    - POSTGRES_USER=${POSTGRES_USER}    
    - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}    
    - POSTGRES_DB=${POSTGRES_DB}

Start the containers and wait until they are done – specially for the pg-13 one, it will initialize the new PostgreSQL database instance:

docker-compose up -d
docker-compose logs -f

After the databases are up and running, leave the log view by pressing ctrl+c.

You’re now ready to run the migration with

docker-compose exec -T pg-12 pg_dumpall -U ${POSTGRES_USER} | docker-compose exec -T pg-13 psql -U ${POSTGRES_USER}

Notes:

• You have to run docker-compose exec with the „-T“ option to „Disable pseudo-tty allocation.“ as the help quotes. This ensures stdout and stdin are handled appropiately between the containers.
• The ${POSTGRES_USER} variable has to be the same as in docker-compose.yaml respectively .env files.

For me this seems to be the most clean approach and it is pretty configurable. I was thinking about getting rid of the docker-compose dependency since this is not really required, but for the moment I am fine with the approach.