Migrating Postgres databases in containers
Posted on November 4, 2021 (Last modified on July 11, 2024) • 1 min read • 178 wordsYou should have two directories next to each other - one is the “old” postgres data dir, one is the new one. My setup was this:
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:14The migration afterwards is as simple as this:
docker run --rm \
-v /srv/postgres/data:/var/lib/postgresql \
tianon/postgres-upgrade:10-to-14 --linkThe docker image tag (here: 10-to-14) describes the migration step.
Source: tianon/docker-postgres-upgrade.
docker run -v $(pwd):/var/lib/postgresql --rm -u 999 -ti postgres:10 /bin/bashSource: Also somewhere in the websters.
$ 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.
$ docker exec -ti -u 999 postgres_container_name psql
postgres=# \password nextcloud
Enter new password:
Enter it again:
postgres=# _Source: Stackoverflow.