Migrating Postgres databases in containers

1 minute read

Run-through

You should have two directories next to each other - one is the “old” postgres data dir, one is the new one. My setup was this:

  • /srv/postgres/data/10/data
  • /srv/postgres/data/14/data

First, initialize the data dir of the “new” version:

# note: mounting -v /.../14:/var/lib/postgresql will _NOT_ work.
# no idea why. some volume issue.
docker run --rm \
    -v /srv/postgres/data/14/data:/var/lib/postgresql/14/data \
    -e PGDATA=/var/lib/postgresql/14/data \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_PASSWORD=postgres \
    postgres:14

The migration afterwards is as simple as this:

docker run --rm \
    -v /srv/postgres/data:/var/lib/postgresql \
    tianon/postgres-upgrade:10-to-14 --link

The docker image tag (here: 10-to-14) describes the migration step.

Source: tianon/docker-postgres-upgrade.

Troubleshooting

Unclean exit

docker run -v $(pwd):/var/lib/postgresql --rm -u 999 -ti postgres:10 /bin/bash

Source: Also somewhere in the websters.

Adjust database collations

$ docker exec -ti -u 999 silly_wescoff psql
postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';
postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template0';
postgres=# update pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template1';

Source: Stackoverflow.

Re-set passwords

$ docker exec -ti -u 999 postgres_container_name psql
postgres=# \password nextcloud
Enter new password:
Enter it again:
postgres=# _

Source: Stackoverflow.