How to run a PostgreSQL database in a Docker container

by Jason Swett,

Why you would want to do this

There are two main reasons for Dockerizing an application: for production use and for development use.

When Dockerizing an application for development, sometimes you might want to package up every single thing in the development environment. For example, in a Rails app, you might want to run each of PostgreSQL, Redis, and Ruby/Rails inside containers.

The drawbacks of Dockerizing everything

But then again you might not want to do that. To continue with the Rails app example, it can be very slow to e.g. run your tests inside a Docker container every single time you want to run a test.

You may instead wish to run PostgreSQL and Redis inside containers but leave Rails out of it, and instead connect Rails to these various containerized components.

This particular blog post covers the most basic building block of the above scenario: running PostgreSQL inside of a Docker container. I’m not going to cover actually connecting an application to the database in this post. This post is not meant to give you a useful result, but rather to help you gain an understanding of this building block.

What we’re going to do

Here’s what we’re going to do:

  1. Create a Docker image that can run PostgreSQL
  2. Run a container based on that image
  3. Connect to the PostgreSQL instance running inside that container to verify that it’s working

Let’s get started.

Creating the image

The first step is to create a Dockerfile. Name this file Dockerfile and put it inside a fresh empty directory.

FROM library/postgres
COPY init.sql /docker-entrypoint-initdb.d/

The first line says to use the postres image from Docker Hub as our base image.

The second line says to take our init.sql file (shown below) and copy it into the special /docker-entrypoint-initdb.d/ directory. Anything inside the /docker-entrypoint-initdb.d/ directory will get run each time we start a container.

Here are the contents of init.sql, which should go right next to our Dockerfile.

CREATE USER docker_db_user;
CREATE DATABASE docker_db_user;
GRANT ALL PRIVILEGES ON DATABASE docker_db_user TO docker_db_user;

As you can see, this file contains SQL code that creates a user, creates a database, and then grants permissions to that user on the database. It’s necessary to do all this each time a container starts because a container doesn’t “remember” all this stuff from run to run. It starts as a blank slate each time.

Building the image

Now let’s build our image.

docker build .

Once this runs, we can run docker images to see the image we just created.

docker images

You should see something that looks like this:

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
<none>              <none>              396bfb8e3373        7 minutes ago       314MB

Running the container

We can use the docker run command to run a container based on our image. There are a few arguments that need to be passed, which I’ve annotated below.

docker run \
  --name dev-postgres \             # the arbitrary name of the container we're starting
  -e POSTGRES_PASSWORD=mypassword \ # the arbitrary db superuser password, which must be set to something
  396bfb8e3373                      # the image ID from our previous docker build step

Connecting to the database

Finally, we can connect to the database to verify that it works.

docker exec --interactive dev-postgres psql -U docker_db_user

If running this command puts you inside the PostgreSQL CLI, you’re good!

Leave a Reply

Your email address will not be published.