Create a postgres database within a docker-compose.yml file

Question

I want to create a postgres database named bank within a docker-compose.yml file just after the postgres container has started but when i run docker-compose --env-file .env -f docker-compose.yaml up -d i get this error: /var/run/postgresql:5432 - no response...

when i remove the line with the command: option, everything start correctly and i get: /var/run/postgresql:5432 - accepting connections

But now, i have to run this steps by steps in the terminal:

• docker exec -it postgres bash
• psql -U my_user_name
• create database bank;
• and exit

And i really don't want it to work like that, instead, i want the database to be created within the docker-compose file. (Note that, when i remove the command: option, and i run until pg_isready; do sleep 1; done; echo accepting; inside the container, it ouput accepting almost immediately)

The POSTGRES_DB env variable doesn't work, The username is still used as default

This is my docker-compose file:

services:
  db:
    container_name: postgres
    image: postgres
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - PGDATA=/data/postgres
    volumes:
      - db:/data/postgres
    ports:
      - "5332:5432"
    networks:
      - db
    restart: unless-stopped
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -d postgres" ]
      interval: 30s
      timeout: 10s
      retries: 5
    command: /bin/bash -c "until pg_isready -U ${POSTGRES_USER} -p 5432; do sleep 1; done; psql -U ${POSTGRES_USER} -c 'CREATE DATABASE bank;'"
networks:
  db:
    driver: bridge

volumes:
  db:

The most important line is the one with command: :

command: /bin/bash -c "until pg_isready -U ${POSTGRES_USER} -p 5432; do sleep 1; done; psql -U ${POSTGRES_USER} -c 'CREATE DATABASE bank;'"

Please help me with the correct command to execute so that the database will be created automatically when running docker-compose --env-file .env -f file up -d

Answer 1

Have you considered using the POSTGRES_DB environment variable ?

services:
  db:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGDATA: /data/postgres
      POSTGRES_DB: bank
    volumes:
      - db:/data/postgres
    ports:
      - "5332:5432"
    networks:
      - db
    restart: unless-stopped
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -d postgres" ]
      interval: 30s
      timeout: 10s
      retries: 5
networks:
  db:
    driver: bridge

volumes:
  db:

Answer 2

You can use a create db script and add it as a volume, like this:

version: '3'

services:

  db:
    image: postgres:15.3-alpine3.18
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - '5432:5432'
    volumes: 
      - ./db:/var/lib/postgresql/data
      - ./create-db.sql:/docker-entrypoint-initdb.d/create_database.sql
    networks:
      - backnet

My SQL script is simple as this:

CREATE DATABASE some_database;

If you already have your volume initiated 'db', you'll need to erase it first (afaik) because postgres will declare that a database already exists and will skip initialization.