Docker PostgreSQL Workflow

This workflow example uses Stackbrew’s trusted PostgreSQL image. You could develop your own and accomplish the same. You can copy-paste all of the commands below editing only the database name, role name, and password.

Perhaps ideally you’ll use your PostgreSQL instance from within other Docker containers, but if you’re not ready to make the switch to running each of your services as separate Docker containers, you can expose your PostgreSQL container’s port onto the host to make it in essence be (and appear as) a standard PostgreSQL installation.

For use with other containers:

docker run \
  --detach \
  --name postgres \
  stackbrew/postgres:latest
For use as a standard PostgreSQL installation:
docker run \
  --detach \
  --name postgres \
  --publish 127.0.0.1:5432:5432 \
  stackbrew/postgres:latest
You now have a database container named “postgres”. We have detached from it and left it running. It exposes it’s own port 5432 to localhost:5432 in whatever container you link it with, and/or with the host if using it as a standard PostgreSQL installation. Docker will automatically pull the stackbrew/postgres image to your local machine if you do not yet have it.
Each of the next examples use containers run from the same stackbrew/postgres image, yet are temporary and will be removed after running. Each will also link to the now created “postgres” container and run their own copies of the psql/pg_dump/pg_restore clients.

Create role/database:

It is (from all appearances) required to have single quotes around the password in the CREATE ROLE command, so we’ll echo it into Docker’s stdin so it will not be escaped by the shell:
echo "CREATE ROLE \"demorole\" WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;" | docker run \
  --rm \
  --interactive \ 
  --link postgres:postgres \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'
 
echo "CREATE DATABASE \"demodatabase\" WITH OWNER \"demorole\" TEMPLATE template0 ENCODING 'UTF8';" | docker run \
  --rm \
  --interactive \
  --link postgres:postgres \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

echo "GRANT ALL PRIVILEGES ON DATABASE \"demodatabase\" TO \"demorole\";" | docker run \
  --rm \
  --interactive \
  --link postgres:postgres \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

Restore/load a database file:

pg_restore through Docker’s stdin can be kinda slow, so instead bind mount in the .sql or .tar file:
From a dump.sql file in your current directory:
docker run \
  --rm \
  --interactive \
  --link postgres:postgres \
  --volume $PWD/:/tmp/ \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d demodatabase < /tmp/dump.sql'
From a dump.tar file in your current directory:
docker run \
  --rm \
  --interactive \
  --link postgres:postgres \
  --volume $PWD/:/tmp/ \
  stackbrew/postgres:latest \
  bash -c 'exec pg_restore -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d demodatabase -F tar -v /tmp/dump.tar'

Accessing the database from another container:

Now you have your container named “postgres” with your database loaded. You can give access to this database to another container:
docker run \
  --detach \
  --link postgres:postgres \
  localhost:5000/ubuntu/ruby-bundler-rails
..the “ruby-bundler-rails” container sees PostgreSQL running on it’s own localhost:5432.

Accessing the database from the host:

If you chose to run your PostgreSQL instance with the ‘–publish 127.0.0.1:5432:5432’ option, then it’s already ready to be accessed by other applications on your host. You could install for example the psql client on the host and start using it as usual, but why bother installing it when you you already have an image with psql. Whether you’re exposing your PostgreSQL instance to your host or to other containers or both, you can use the same stackbrew/postgres image to run your normal commands on the running instance.
Here are a few examples from which you can extrapolate how to accomplish all the things you’re already familiar with doing  with PostgreSQL:

Dump the database into your current dir on the host:

docker run \
  --interactive \
  --link postgres:postgres \
  --volume $PWD/:/tmp/ \
  stackbrew/postgres:latest \
  bash -c 'exec pg_dump -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -F tar -v -d demodatabase > /tmp/dump.tar'

Interactive mode:

docker run \
  --rm \
  --interactive \
  --tty \
  --link postgres:postgres \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

List databases, etc:

docker run \
  --rm \
  --interactive \
  --tty \
  --link postgres:postgres \
  stackbrew/postgres:latest \
  bash -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -c "\l"'

Thoughts/Questions:

You will not want to remove the container named ‘postgres’ until you have a dump of your lastest database updates.

Some have thought to link the actual database files onto the host by running the instance with something like ‘–volume /demodatabase:/var/lib/postgresql/9.1/main’ which would make the container removable/replaceable. – This deserves more consideration/experimentation.

Conclusion:

If you’re picky and want to RTFM on each of Docker’s Command Line Interface options, good for you! It’ll take you slightly longer and benefit us all with more impact. But regardless of that, with a near zero learning curve, anywhere you have the Docker daemon installed you can start using this service now. It’ll be especially easy if you’re already relatively familiar with PostgreSQL.

Advertisements

2 thoughts on “Docker PostgreSQL Workflow

  1. Thanks for posting this example! We’re working on similar things as an open-source project (https://github.com/ClusterHQ/flocker). Our project let’s you define your app as a set of containers, deploy to multiple nodes, and them migrate db containers and their volumes after, in case you wanted to do something like upgrade your db version, or a machine fails. We’ve got a tutorial for doing this with Mongo (https://docs.clusterhq.com/en/0.0.6/gettingstarted/tutorial/index.html) and would love your thoughts on the postgres example we’re working on. Cheers and keep up the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s