Running Postgres in Docker for Local Development

If you’re building an application that uses PostgreSQL in production for data storage, then at some point you are probably going to need to run PostgreSQL for development on your local machine.

Docker Vs System Installation

It’s possible to just install Postgres on your computer. There are builds for all three major operating systems. That seems like the easiest way, but there are a few problems with this approach.

Problems with A System Level Install of Postgres

• Postgres will always be running as a system service
• You can easily have one version of Postgres installed and running at a time
• Every app that needs it’s own Postgres database will need to share the same instance
• Requires root/admin access to your machine
• If you work on a team, each setup might be slightly different, which makes sharing dev configs hard.

Docker Solves (Most) Of These Problems

With Docker each project under development can have it’s own Postgres locked to the correct version, with a standard setup that is shared across team mates and can be checked-in to source control.

The biggest problem with running Postgres in a Docker container is, well, learning Docker if you’ve never used it before.

Best Practices For Running Postgres In Docker

Be sure you are using the official Postgres image.

We recommend managing development postgres in the following ways:

1. Store your postgres config in a Docker Compose file (see below for an example).
2. Check the docker compose file into source control (like Git) along side your project source code.
3. Have everyone on your team use the same settings for postgres in development.
4. Bootstrap your postgres image (if you need to), using some of the more advanced features of the docker image.
5. Make sure to specify a volume for the container to use to store data, that way it persists across runs.
6. Use the built in features of the image to run some bootstrap SQL, set credentials, and create databases.

Postgres Docker Compose Templates

Here are a couple of docker compose examples from projects that we manage

Minimal Postgres Docker Example For Rails, Django, or Similar

This docker-compose example doesn’t do much beyond creating the container and setting a username and psassword for use. This is great for projects that handle their own database migrations, like Ruby on Rails, Django, or the Play Framework.

version: '3'
volumes:
  psql:
services:
  psql:
    image: postgres # this uses the 'latest' version
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: example # only do this in development
    volumes:
      - psql:/var/lib/postgresql/data # save data between restarts
    ports:
      - 5555:5432 # binds port 5555 on your computer to the postgres port!

Using This With Your App

Run docker-compose up to start your container (or docker-compose up -d to start it in daemon mode).

In your app you can connect to the container with the following connection configs, which can be the same for everyone in your team:

{
  host: "localhost",
  port: 5555,
  username: "postgres",
  password: "example"
}

In Rails (or similar frameworks) you can also use a connection string

postgresql://postgres:example@localhost:5555

Or using Beekeeper Studio

Advanced Postgres Docker Example With Bootstrapped Data

In this example we spin up a postgres and use some of the image features (see ‘How to extend this image’) to bootstrap our postgres instance with some special development data.

You can see this template in action in the Beekeeper Studio repository. You can see the init SQL scripts we use in this directory.

version: '3'
volumes:
  psql13:

services:
  psql13:
    image: postgres:13
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: example
      POSTGRES_DB: myapp
    volumes:
      - psql13:/var/lib/postgresql/data
      - ./psql_init:/docker-entrypoint-initdb.d
    ports:
      - 5999:5432

To use this template:

1. Create the directory psql_init and populate it with .sql scripts to run
2. Run docker-compose up to run it, or docker-compose up -d to run the container in the background.
3. Connect to the postgres instance on localhost:5999 - you can see in the ports section we forward this port to container port 5432 - the port that Postgres is running on.