upgrade-postgresql-with-docker-compose

upgrade-postgresql-with-docker-compose.txt

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

# login to postgres
$ psql -U postgres
$ CREATE DATABASE database_name

# 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'

source: https://eelkevdbos.medium.com/upgrade-postgresql-with-docker-compose-99d995e464

Upgrade PostgreSQL with Docker Compose

Step-1:

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: {}

Step-2:

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: {}

Step-3:

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

# login to postgres
$ psql -U postgres
$ CREATE DATABASE database_name

# 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'

Final Result:

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: {}