Global Postgres Database using Docker Compose

docker-compose-dummystack.yaml

# /Users/xyz/docker/dummystack/docker-compose.yml
version: "3.8"
services:
  dummyapp:
    image: app/app:latest
    environment:
      PG_SERVICE_USER: dummyapp
      PG_SERVICE_PASSWORD: dummypassword
      PG_SERVICE_DB: dummydb
    volumes:
      - pg_data:/var/lib/postgresql/data
      - /User/xyz/docker/postgres/postgres_entrypoint.sh:/app/postgres_entrypoint.sh
    entrypoint: ["/bin/bash", "/app/postgres_entrypoint.sh"]
    networks:
      - network_databases
     
volumes:
  pg_data:

networks:
  network_databases:
    external: true

docker-compose-postgres.yaml

# /Users/xyz/docker/postgress/docker-compose.yml
version: "3.8"

services:
  postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: ${POSTGRES_ADMIN_USER}
      POSTGRES_PASSWORD: ${POSTGRES_ADMIN_PASSWORD}
      POSTGRES_DB: ${POSTGRES_ADMIN_DB}
    ports:
      - 5432:5432
    network:
      - network_databases
    volumes:
      - pg_data:/var/lib/postgresql/data
      
networks:
  network_databases:
    external: true
volumes:
  pg_data:

GlobalPostgresDatabase.md

Goal

1. Establish a centralized postgres db that all other compose files / containers can access
2. Allow other containers to automatically create credentials/databases that are scoped specifically for them
3. Make sure that the databases aren't overwritten each time docker-compose up -d is ran
4. Avoid having to duplicate entrypoint files and keep the postgres_entrypoint.sh in a centralized location

File Structure

🗂️ docker
|___ 🗂️ DummyStack
|     |___ 📄 docker-compose.yml
|     |___ 📄 .env
|___ 🗂️ Postgres
|     |___ 📄 docker-compose.yml
|     |___ 📄 postgres_entrypoint.sh
|     |___ 📄 .env

Logic Flow

1. Postgres is running in the background
2. New docker-compose is created elsewhere (dummyStack)
3. When docker-compose up -d is ran on the dummyStack, dummyStack calls up Postgres and says "Hey! Pleasure to meet you. Can you please create a database called dummyStack and allow me (I go by dummystack and I want a password of pass123)"
4. Postgres checks to see if dummyStack has already been there before A. Postgres says "You big dummy, you were my Made of Honor at me and Redis's wedding and we've been friends since middle school. You gotta stop poppin' Ambien." To which DummyStack says, "Oh my bad, I've been hitting the Ambien hard lately because the world is coming to an end, I'll never be able to afford a house of my own, the rich are getting richer, and democracy is a lie perpetuated by the elite in an attempt to keep us complacent. I'm coming over with the house key you gave me and moving my shit into your garage" B. Postgres says "Great to meet you as well, here's a house key, come on over and throw your shit in my garage"
5. DummyStack proceeds to throw his shit in Postgres's garage

postgress_entrypoint.sh

# /Users/xyz/docker/postgress/postgres_entrypoint.sh
#!/usr/bin/env bash
set -e

# Directly setting database credentials
POSTGRES_ADMIN_USER="postgres"
POSTGRES_ADMIN_PASSWORD="password123"
POSTGRES_ADMIN_HOST="postgres"

echo "Attempting to create database $PG_SERVICE_DB with a user of $PG_SERVICE_USER and a pw of $PG_SERVICE_PASSWORD"
# Function to check if a PostgreSQL user exists
user_exists() {
    POSTGRES_ADMIN_PASSWORD="$POSTGRES_ADMIN_PASSWORD" psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='$1'" -h "$POSTGRES_ADMIN_HOST" -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_ADMIN_DB" | grep -q 1
}

# Function to check if a PostgreSQL database exists
db_exists() {
    POSTGRES_ADMIN_PASSWORD="$POSTGRES_ADMIN_PASSWORD" psql -tAc "SELECT 1 FROM pg_database WHERE datname='$1'" -h "$POSTGRES_ADMIN_HOST" -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_ADMIN_DB" | grep -q 1
}

# Wait for PostgreSQL to become available
echo "Waiting for PostgreSQL ($POSTGRES_ADMIN_HOST) to start..."
until POSTGRES_ADMIN_PASSWORD="$POSTGRES_ADMIN_PASSWORD" psql -h "$POSTGRES_ADMIN_HOST" -U "$POSTGRES_ADMIN_USER" -d "$POSTGRES_ADMIN_DB" -c '\q'; do
  >&2 echo "PostgreSQL is unavailable - sleeping"
  sleep 1
done

echo "PostgreSQL is up - executing command"

# Only create the user if it doesn't already exist
if ! user_exists "$PG_SERVICE_USER"; then
    POSTGRES_ADMIN_PASSWORD="$POSTGRES_ADMIN_PASSWORD" psql -v ON_ERROR_STOP=1 --username "$POSTGRES_ADMIN_USER" --host "$POSTGRES_ADMIN_HOST" --dbname "$POSTGRES_ADMIN_DB" <<-EOSQL
        CREATE USER "$PG_SERVICE_USER" WITH ENCRYPTED PASSWORD '$PG_SERVICE_PASSWORD';
EOSQL
else
    echo "User $PG_SERVICE_USER already exists, skipping creation."
fi

# Only create the database if it doesn't already exist
if ! db_exists "$PG_SERVICE_DB"; then
    POSTGRES_ADMIN_PASSWORD="$POSTGRES_ADMIN_PASSWORD" psql -v ON_ERROR_STOP=1 --username "$POSTGRES_ADMIN_USER" --host "$POSTGRES_ADMIN_HOST" --dbname "$POSTGRES_ADMIN_DB" <<-EOSQL
        CREATE DATABASE "$PG_SERVICE_DB";
        GRANT ALL PRIVILEGES ON DATABASE "$PG_SERVICE_DB" TO "$PG_SERVICE_USER";
EOSQL
else
    echo "Database $PG_SERVICE_DB already exists, skipping creation."
fi

echo "Database initialization completed successfully."