Migrate a dockerized PostgreSQL database from one server to another

Posted by ZedTuX 0n R00t on August 12, 2015

Today I’ve been informed that Brewformulas.org is down. Checking the server and no more disk space :_(

As I wasn’t able to get some more space quickly, I’ve decided to prepare a new server and migrate the application. As I’m using Docker and docker-compose, it’s super easy and takes 5 minutes but what about the data ?

In this case, only the Postgres database needs to be restored, so how to perform this in an amazing way ?

The magic command

Given a first server where is running the existing database that we’d like to duplicate, and a second server where the Postgres server is up and running and the database created but empty:

sudo docker run --rm --link brewformulasdb:postgres -it postgres sh -c 'pg_dump --host=$POSTGRES_PORT_5432_TCP_ADDR --dbname=brewformulas_org_prod --username=postgres' | ssh -C root@ "docker run --rm --link brewformulas_postgres_1:postgres -i postgres sh -c 'psql --host= --dbname=brewformulas_org_prod --username=postgres'"

Ouf ! That’s a lot ! :-)

Basically, this command is using the pg_dump command in a container linked to the running database to be dumped, SSH the second server and send the dump data (We’re using the -C flag from SSH for the data compression) and then use the psql command on the remote server in a container in order to populate the database with the dumped data. (In this case, the first server is an Ubuntu server, so I had to use sudo while on the second server I’m running Debian as root).

  • docker run --rm --link brewformulasdb:postgres -it postgres will create a new container, linked to the container where the database, to be dumped, is running
  • sh -c is used in order to execute the line within the container (otherwise the environment variable POSTGRES_PORT_5432_TCP_ADDR is not yet accessible)
  • pg_dump --host=$POSTGRES_PORT_5432_TCP_ADDR --dbname=brewformulas_org_prod --username=postgres will dump the brewformulas_org_prod database content to the stdout
  • | ssh -C will push the dumped data through SSH using compression
  • ssh -C root@ connects to the remote machine and send the data
  • docker run --rm --link brewformulas_postgres_1:postgres -i postgres is create a new container of the postgres docker image, linking the running postgres empty database (Here it’s important to not have the -t flag in order to avoid the error cannot enable tty mode on non tty input)
  • sh -c 'psql --host= --dbname=brewformulas_org_prod --username=postgres' will fill in the empty database with the data received from the remote host (Here I had to use --host= as for an unknown reason the $POSTGRES_PORT_5432_TCP_ADDR variable wasn’t resolved)