Migrate a dockerized PostgreSQL database from one server to another

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@104.236.186.236 "docker run --rm --link brewformulas_postgres_1:postgres -i postgres sh -c 'psql --host=172.17.0.31 --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@104.236.186.236 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=172.17.0.31 --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=172.17.0.31 as for an unknown reason the $POSTGRES_PORT_5432_TCP_ADDR variable wasn't resolved)