PostgreSQL via Docker Compose

• Create Docker Compose file in location that you want to start it in

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=<postgres_user>
      - POSTGRES_PASSWORD=<postgres_user_pass>
      - APP_DB_USER=<app_user>
      - APP_DB_PASS=<app_user_pass>
      - APP_DB_NAME=<app_database_name>
    volumes:
      - ./db:/docker-entrypoint-initdb.d/
    ports:
      - 5432:5432

• Create Database INIT Script

# Create the folder in docker compose file location
mkdir <location_of_docker_compose_file>/db

# Create the script
touch <location_of_docker_compose_file>/db/01-init.sh

Should look like this:

drwxr-xr-x 2 user user 4096 Jul 16 14:22 db
-rw-r--r-- 1 user user  479 Jul 16 14:24 docker-compose.yml
drwxr-xr-x 5 user user 4096 Jul 16 14:33 ..
drwxr-xr-x 3 user user 4096 Jul 16 14:33 .

• Add the following script to is:

#!/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

The script will:

• create a new user and password assigned in the variables in the composed file

• create the App database

• grant the priviilages for the user on the database

• connect to the database cand create a table called event

Only thing left is to run the docker composed script:

docker-compose up -d

Note:

This should run the script automatically but if it doesn't run it with bash 01-init.sh

• This should now be reachable via DBeaver or pgAdmin