Skip to content

Instantly share code, notes, and snippets.

@millerdev millerdev/steps.md
Last active Sep 10, 2018

Embed
What would you like to do?
Update to new version of PostgreSQL in docker

Upgrade to new version of Postgres in Docker

This example upgrades PostgreSQL 9.4 to 9.6

See also https://github.com/docker-library/postgres/issues/37#issuecomment-277621597

Note: you will need compatible pg_dumpall and psql binaries in your path (outside of docker), probably at least at the version you're migrating from. If you don't have those on your local machine, you can use the one in the docker container. Something like this

docker exec hqservice_postgres_1 pg_dumpall ...

Set an environment variable pointing to your docker host

If you're using boot2docker on Mac, it will be something like

DOCKER_HOST=192.168.99.100

Otherwise it will likely be

DOCKER_HOST=localhost

Dump all databases to a file

pg_dumpall -h $DOCKER_HOST -U commcarehq | gzip > pg-backup.sql.gz

Or with pg_dumpall in docker

docker exec hqservice_postgres_1 pg_dumpall -h $DOCKER_HOST -U commcarehq | gzip > pg-backup.sql.gz

Backup the old data dir (just in case you need to get it later).

If DOCKER_HOST is not localhost (you're probably using boot2docker)

Do the following in a new shell window, and keep it open so we can come back to it to verify the new database version later.

docker-machine ssh hq  # or however you get into your docker host

DATA_DIR=/mnt/sda1/var/lib/dockerhq/postgresql

sudo tar -cjvf pg94-data-backup.tbz $DATA_DIR
sudo mv $DATA_DIR ${DATA_DIR}9.4

# keep this shell open, but do next command on the host where you saved
# pg-backup.sql.gz

Otherwise, if DOCKER_HOST is localhost

Adjusting DATA_DIR as needed and do...

DATA_DIR=~/.local/share/dockerhq/postgresql
sudo tar -cjvf pg94-data-backup.tbz $DATA_DIR
sudo mv $DATA_DIR ${DATA_DIR}9.4

Tag old docker image so we can get it back if needed

docker tag dimagi/docker-postgresql dimagi/postgresql9.4

Upgrade docker image and start it

docker pull dimagi/docker-postgresql

# this uses the commcare-hq docker script
./scripts/docker up -d postgres  # --> Recreating hqservice_postgres_1

Verify new database version

sudo cat $DATA_DIR/PG_VERSION  # --> 9.6

Finally, restore the dumped databases

gzip -cd pg-backup.sql.gz | psql -h $DOCKER_HOST -U commcarehq

Or with psql in docker (this is a bit hacky)

gzip -cd pg-backup.sql.gz > $DATA_DIR/pg-backup.sql

docker exec hqservice_postgres_1 psql -h $DOCKER_HOST -U commcarehq --file=/var/lib/postgresql/data/pg-backup.sql
# should see a lot of output here as databases are created, etc.

rm $DATA_DIR/pg-backup.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.