webbureaucrat

Setting up a new development environment from scratch can feel like a slog. This is a quick copy-paste recipe for getting up and running quickly with Postgres in docker compose.

Start with a .env file

At the very least, we know we will need a POSTGRES_PASSWORD environment variable, and our .env file doesn't rely on any other files, so it's a good place to start.

I personally am not crazy about hiding files by beginning them with periods, so I'm going to call my file database.env.

database.env

POSTGRES_USER=postgres_user
POSTGRES_PASSWORD=postgres_password
POSTGRES_DB=postgres_database

It goes without saying, but this is for local development. Sensitive information for any database you intend to stand up for a while should be treated better than this. Use stronger passwords, and don't publish them in a blog post on the Internet, etc., etc.

Create database scripts

This section is more or less the "Draw the rest of the owl" portion of this article. The idea here is to create whatever database scripts--tables, stored procedures, etc--in ./database/docker-entrypoint-initdb.d. Any .sql or compressed .sql file in this folder will be run by the database at startup. (Other file extensions are supported. See the official documentation for more.)

Create a docker-compose.yml file

Now we're ready to get started with our docker compose file. There's no magic here--we're just exposing our ports, referencing our database.env file, and feeding in our scripts from our scripts directory.

docker-compose.yml

version: "3.8"
services:
  database:
    env_file: "database.env"
    image: postgres:latest
    ports:
      - "5432:5432"
    volumes:
     - ./database/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d

You should now be able to run this with docker compose up.

Connecting to a locally-running Postgres container

With that compose file running, we can connect via SSH like so:

docker compose exec database bash

Note that "database" is the identifier chosen in the docker-compose.yml.

Lastly, we can run psql from within the container to run commands directly inside the container. Use the values from your database.env file.

psql --dbname postgres_database --host localhost --username postgres_user

This can be shortened to:

psql -d postgres_database -h localhost -U postgres_user

From here, you can run Postgres queries against your database.

Wrapping up

Starting with a blank sheet of paper can be kind of daunting, so writing (and having) basic tutorials like this to lay out some basic first steps to be a good motivational and organizational tool. I hope this has been helpful.

I write to learn, so I welcome your constructive criticism. Report issues on GitLab.