Using Docker Compose to Launch a PostgreSQL Database

Before we begin

First we need Docker and Docker Compose installed on our machine before we can do the steps in this tutorial. This tutorial shows you how to install it on Ubuntu 20.04.

Step 1: Create Docker Compose file

First let’s create a folder to store our files:

mkdir ~/postgres-demo && cd ~/postgres-demo

Create a docker-compose.yml file:

touch docker-compose.yml

Add the following contents to the file:

version: '3'

services:
  postgres:
    image: postgres:13.1
    healthcheck:
      test: [ "CMD", "pg_isready", "-q", "-d", "postgres", "-U", "root" ]
      timeout: 45s
      interval: 10s
      retries: 10
    restart: always
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=password
      - APP_DB_USER=docker
      - APP_DB_PASS=docker
      - APP_DB_NAME=docker
    volumes:
      - ./db:/docker-entrypoint-initdb.d/
    ports:
      - 5432:5432

The image option specifies what Docker image to use. Here we are using version 13.1 of the official Postgres image.

The healthcheck option is used to make sure Postgres is running before other services that depend on it can run.

The restart always makes sure the DB starts when the system starts.

The following environment variables are used to assign the username and password of the main Postgres database:

• POSTGRES_USER
• POSTGRES_PASSWORD

The following environment variables are used by the init script to create a database user and a database for our application to use:

• APP_DB_USER
• APP_DB_PASS
• APP_DB_NAME

For the volumes option we are mapping a local folder called db to a folder inside the container at /docker-entrypoint-initdb.d/. This is where we will place the database init script in the next step.

We are also exposing the port to our host by assigning the Postgres port to the ports option. This will allow us to connect to the database from our dev machine.

Step 2: Create Database Init Script

Create a folder called db to store our init script.

mkdir db

Create a script called 01-init.sh

touch db/01-init.sh

Add the following contents to it:

#!/bin/bash
set -e
export PGPASSWORD=$POSTGRES_PASSWORD;
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE USER $APP_DB_USER WITH PASSWORD '$APP_DB_PASS';
  CREATE DATABASE $APP_DB_NAME;
  GRANT ALL PRIVILEGES ON DATABASE $APP_DB_NAME TO $APP_DB_USER;
  \connect $APP_DB_NAME $APP_DB_USER
  BEGIN;
    CREATE TABLE IF NOT EXISTS event (
	  id CHAR(26) NOT NULL CHECK (CHAR_LENGTH(id) = 26) PRIMARY KEY,
	  aggregate_id CHAR(26) NOT NULL CHECK (CHAR_LENGTH(aggregate_id) = 26),
	  event_data JSON NOT NULL,
	  version INT,
	  UNIQUE(aggregate_id, version)
	);
	CREATE INDEX idx_event_aggregate_id ON event (aggregate_id);
  COMMIT;
EOSQL

This script will:

• Create a new user with the name assigned to APP_DB_USER and the password assigned to APP_DB_PASS.
• Create a database with whatever name is assigned to APP_DB_NAME.
• Grants all privileges for the user on the database.
• Connect to the database and create a table called event.

Let’s run Docker Compose and see it in action.

Step 3: Docker Compose Up

Run the docker compose up to start the Postgres database and run the database init script.

docker-compose up

We should now be able to connect to the database using a tool like DBeaver or pgAdmin. The following screenshot shows a connection to the database using pgAdmin on a Mac.

Conclusion

That’s it. Now you are ready to start adding other services to the Docker Compose file that depend on the Postgres image.