So I’m a loyal acolyte in the church of docker. I also have this little schoolgirl crush on PostgreSQL. Here’s how you can combine both into a crime-fighting dream team.

The Long, Instructive Way

Just the basics:

Spin up a container, install a text editor and snapshot an image:

sudo docker run -i -t ubuntu:precise /bin/bash

Inside the container install a text editor (because the default precise image doesn’t come with one installed):

apt-get update
apt-get install vim-tiny
exit

Snap an image. Your name is probably not amattn, however just for a moment, pretend otherwise. I know it is unpleasant, but only for a short while. I called my image precise-vim but you can call it dinglemuffin if you really want to.

sudo docker commit CONTAINER_ID amattn/precise-vim

Install the default PostgreSQL

Again with the spinning up of a new container:

sudo docker run -i -t amattn/precise-vim /bin/bash

Do the basic install. The assist with the repo info is credited to https://wiki.postgresql.org/wiki/Apt

apt-get update
apt-get install -y wget
wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get update
apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3
exit

Just a note, the above will install postgres-9.3.X where X is the latest. At the time of this update (Early Jan 2014), that is 9.3.2, but obviously, that may or may not be the case when you read this.

Again with the snapping of an image. Just a note here, I got odd failures when my image names had capital letters (as of docker 0.6.1).

sudo docker commit CONTAINER_ID amattn/postgresql-9.3.2

Container Cleanup

You can list all containers with docker ps -a We don’t actually need the containers that we used to create images. Once we have images, we simply spin up totally new containers, while the sad, lonely ones we used to create the images get rm’d.

sudo docker rm CONTAINER_ID CONTAINER_ID ... CONTAINER_ID

Typical configuration from here:

Here’s the magic part. We want to configure PostgreSQL to put its data in the container’s a directory at the root level called /data. This folder is shared with the docker host. This way, we can use any container configured to look at /data with a persistent file on the host. Our data becomes decoupled from our container. In this example we use $HOME/postgresdata, but feel free mount any host directory you like.

mkdir -p $HOME/postgresdata
sudo docker run -v="$HOME/postgresdata":"/data"  -i -t -p 5432 amattn/postgresql-9.3.2 /bin/bash

First setup our .conf & .hba files:

cp /etc/postgresql/9.3/main/postgresql.conf /data/postgresql.conf
cp /etc/postgresql/9.3/main/pg_hba.conf /data/pg_hba.conf

Use our custom data directory (/data/main) & .hba file:

sed -i '/^data_directory*/ s|/var/lib/postgresql/9.3/main|/data/main|' /data/postgresql.conf
sed -i '/^hba_file*/ s|/etc/postgresql/9.3/main/pg_hba.conf|/data/pg_hba.conf|' /data/postgresql.conf

Create /data/main/ and fill it with stuff.

mkdir -p /data/main
chown postgres /data/*
chgrp postgres /data/*
chmod 700 /data/main
su postgres --command "/usr/lib/postgresql/9.3/bin/initdb -D /data/main"
cp /postgresql.conf /data/postgresql.conf
cp /pg_hba.conf /data/pg_hba.conf

If you want to allow access from any ip address, the next three commands are for you. This is obviously a huge security risk, especially if you don’t have a firewall or similar in place. Caveat Developor

sed -i "/^#listen_addresses/i listen_addresses='*'" /data/postgresql.conf
sed -i "/^# DO NOT DISABLE\!/i # Allow access from any IP address" /data/pg_hba.conf
sed -i "/^# DO NOT DISABLE\!/i host all all 0.0.0.0/0 md5\n\n\n" /data/pg_hba.conf

Start PostgreSQL

su postgres --command "/usr/lib/postgresql/9.3/bin/postgres -D /data/main -c config_file=/data/postgresql.conf" &

# As the user postgres, create a user named docker
su postgres --command 'createuser -P -d -r -s docker'

# As the user postgres, create a db docker owned by postgres user docker
su postgres --command 'createdb -O docker docker'

Shutdown PostgreSQL

su postgres --command '/usr/lib/postgresql/9.3/bin/pg_ctl --pgdata=/data/main stop'
exit

Now we commit, but we should use a tag! Until now, all our commits are for general purpose containers. Even though all data and configuration is “outside” the container, we still want to be able to identify for what purpose a container exists. As of this writing, tags are the best way to do so.

sudo docker commit CONTAINER_ID amattn/postgresql-9.3.2 TAGNAME

I’ve found that tags in the format of amattn/component:appname work very well in practice:

amattn/postgres-9.2.1:favstarclone
amattn/postgres-9.3.2:flickrclone
amattn/mariadb-55:bookmarker
amattn/redis-2.6.16:bookmarker

The tags also help us remember not to delete those containers.

Launching the Container

Launch the container with the run command. Notice that we aren’t spinning up a shell anymore. We are launching a container w/ the tag TAGNAME, running a single process (postgres) as the user postgres, with a random port forwarded to the container’s port 5432 and a directory mounted to the container’s /data.

sudo docker run -v="$HOME/postgresdata":"/data" -d -p 5432 amattn/postgresql-9.3.2:TAGNAME su postgres --command "/usr/lib/postgresql/9.3/bin/postgres -D /data/main -c config_file=/data/postgresql.conf"

At this point, the container should be humming along in the background. You can even prove it to your disbelieving self with the ps command. In particular, the status column should list an uptime and not an exit code:

docker ps -a

Start and stop the container with:

sudo docker stop CONTAINER_ID
sudo docker start CONTAINER_ID

Get the host port with either of:

sudo docker ps -a
sudo docker port CONTAINER_ID

The Short, Borderline Cheating Way

In the host:

mkdir -p $HOME/postgresdata
sudo docker run -v="$HOME/postgresdata":"/data"  -i -t -p 5432 amattn/postgresql-9.3.2 /bin/bash

Inside the container:

cp /etc/postgresql/9.3/main/postgresql.conf /data/postgresql.conf
cp /etc/postgresql/9.3/main/pg_hba.conf /data/pg_hba.conf
sed -i '/^data_directory*/ s|/var/lib/postgresql/9.3/main|/data/main|' /data/postgresql.conf
sed -i '/^hba_file*/ s|/etc/postgresql/9.3/main/pg_hba.conf|/data/pg_hba.conf|' /data/postgresql.conf

mkdir -p /data/main
chown postgres /data/*
chgrp postgres /data/*
chmod 700 /data/main
su postgres --command "/usr/lib/postgresql/9.3/bin/initdb -D /data/main"

# OPTIONAL: configure /data/postgresql.conf & /data/pg_hba.conf to allow access from trusted IP addresses

# Start PostgreSQL
su postgres --command "/usr/lib/postgresql/9.3/bin/postgres -D /data/main -c config_file=/data/postgresql.conf" &

# OPTIONAL: add PostgreSQL user(s), go other setup config

# Stop PostgreSQL
su postgres --command '/usr/lib/postgresql/9.2/bin/pg_ctl --pgdata=/data/main stop'

exit

Back in the host, optionally commit and tag. Launch the container with the run command:

sudo docker run -v="$HOME/postgresdata":"/data" -d -p 5432 amattn/postgresql-9.3.2:OPTIONAL_TAGNAME su postgres --command "/usr/lib/postgresql/9.3/bin/postgres -D /data/main -c config_file=/data/postgresql.conf"