Theo's Typesafe Cult•2y ago

Postgres docker image with database creation?

Currently I am struggling to have a postgres docker image which automatically generates a database. Doing this right now

version: '3.3'
services:
  development:
    container_name: '${POSTGRES_CONTAINER}'
    image: postgis/postgis:13-3.1-alpine
    shm_size: '2g'
    ports:
      - 5433:5432
    environment:
      POSTGRES_DB: '${POSTGRES_DB}'
      POSTGRES_PASSWORD: '1234'
      POSTGRES_USER: '${POSTGRES_USER}'
    volumes:
      - ./volumes/pg-backend:/var/lib/postgresql/data
    healthcheck:
      test: [ 'CMD-SHELL', 'pg_isready -U postgres' ]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      default:
        aliases:
          - postgres.service

version: '3.3'
services:
  development:
    container_name: '${POSTGRES_CONTAINER}'
    image: postgis/postgis:13-3.1-alpine
    shm_size: '2g'
    ports:
      - 5433:5432
    environment:
      POSTGRES_DB: '${POSTGRES_DB}'
      POSTGRES_PASSWORD: '1234'
      POSTGRES_USER: '${POSTGRES_USER}'
    volumes:
      - ./volumes/pg-backend:/var/lib/postgresql/data
    healthcheck:
      test: [ 'CMD-SHELL', 'pg_isready -U postgres' ]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      default:
        aliases:
          - postgres.service

3 Replies

utdev•2y ago

I also have a start-dev-db.sh script which runs that yaml file

#!/bin/bash
set -e

export POSTGRES_CONTAINER="pflegia-dev-db"
export POSTGRES_DB="pflegia-dev-db"
export POSTGRES_USER="postgres"

docker-compose -f tools/local-dev-db/development_db.yml up -d

#!/bin/bash
set -e

export POSTGRES_CONTAINER="pflegia-dev-db"
export POSTGRES_DB="pflegia-dev-db"
export POSTGRES_USER="postgres"

docker-compose -f tools/local-dev-db/development_db.yml up -d

Right now the docker container is built correctly I just want to add a database to it

circles•2y ago

you can create a db service in your docker-compose like so:

  db:
    image: postgres:11.2-alpine
    container_name: postgres-${BRANCH_NAME}
    restart: always
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_DATABASE}
    volumes:
      - ./project-root/init-postgres.sh:/docker-entrypoint-initdb.d/init-postgres.sh
      - /opt/project-root-db-${BRANCH_NAME}:/var/lib/postgresql/data
    ports:
      - "${DB_PORT}:5432"

  db:
    image: postgres:11.2-alpine
    container_name: postgres-${BRANCH_NAME}
    restart: always
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_DATABASE}
    volumes:
      - ./project-root/init-postgres.sh:/docker-entrypoint-initdb.d/init-postgres.sh
      - /opt/project-root-db-${BRANCH_NAME}:/var/lib/postgresql/data
    ports:
      - "${DB_PORT}:5432"

For this to work, you will also need this script: ./project-root/init-postgres.sh

#!/bin/bash

psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<EOF
ALTER USER $POSTGRES_USER CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;
CREATE DATABASE $DB_DATABASE
EOF

#!/bin/bash

psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<EOF
ALTER USER $POSTGRES_USER CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;
CREATE DATABASE $DB_DATABASE
EOF

Note that I use this with gitlab CD/CI, so you will probably need to change/remove some of the env variables, but this is just to get the idea. Hope it helps.

Scot•2y ago

If its for local you can give postgres a default sql script to run on startup

Gaming

Programming

Postgres docker image with database creation?