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

The command: runs instead of the standard image CMD; you are trying to create a table instead of running a database. If you can't use the standard environment variables or an initialization script then this needs to run in a separate container; tasks like creating tables are best done as part of your application's database migrations. — David Maze
– David Maze, Commented Jan 26, 2023 at 12:45
No, CREATE DATABASE and CREATE TABLE are separate SQL statements. — David Maze
– David Maze, Commented Jan 26, 2023 at 18:39
then why this: "you are trying to create a table instead of running a database. " — Jotter Kain
– Jotter Kain, Commented Jan 26, 2023 at 22:05
Because of the command: line, you are not running the PostgreSQL server but you are running the psql command instead; and since the database server is not running there is nothing for psql to connect to. The SQL CREATE DATABASE statement (not "table", my mistake) can't execute because there's no database server running. — David Maze
– David Maze, Commented Jan 26, 2023 at 22:15

e741af0d41bc74bf854041f1fbdbf · Accepted Answer · 2023-01-26 13:43:43Z

25

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:

edited Jan 26, 2023 at 13:43

answered Jan 26, 2023 at 12:41

e741af0d41bc74bf854041f1fbdbf

1,2691 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jotter Kain Over a year ago

I saw it on the postgres official images on hub. but it didn't work for me. the POSTGRES_USER is still used by default. I forgot to mention sorry

Jotter Kain Over a year ago

it didn't work, i get this error FATAL: role "root" does not exist, and a database is ceated using the postgres_user as name

Joe Phillips Over a year ago

This worked perfectly for me. Love you

Victor Biga Over a year ago

I think there was a typo in: test: [ "CMD-SHELL", "pg_isready -U postgres" ] I had test: [ "CMD-SHELL", "pg_isready -d postgres" ] and it was also giving me FATAL: role "root" does not exist

EsmaeelE · Accepted Answer · 2024-06-18 09:07:24Z

11

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.

edited Jun 18, 2024 at 9:07

EsmaeelE

2,7557 gold badges28 silver badges36 bronze badges

answered Jul 8, 2023 at 0:12

Daniel S.

8508 silver badges17 bronze badges

1 Comment

Tanveer Over a year ago

docker-compose down -v will remove Volume and after that docker-compose up --build

Amir nazary · Accepted Answer · 2023-07-20 15:22:03Z

Here is another setup for creating local PG using Docker:

Create a file called init.sql containing your sql statements.
Create Dockerfile with the following content:

# dump build stage
FROM postgres:11-alpine as dumper

COPY init.sql /docker-entrypoint-initdb.d/

RUN ["sed", "-i", "s/exec \"$@\"/echo \"skipping...\"/", "/usr/local/bin/docker-entrypoint.sh"]

ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=postgres
ENV PGDATA=/data

RUN ["/usr/local/bin/docker-entrypoint.sh", "postgres"]

# final build stage
FROM postgres:11-alpine

COPY --from=dumper /data $PGDATA

Run the following command in the directory that Dockerfile resides:

docker image build . -t preloaded_db:latest

This will create a docker image with name preloaded_db and latest tag.

Run the Docker image with the following command:

docker container run -d --rm -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres --name test_preloaded_db preloaded_db:latest

Here you can find the complete source for it.

Aleksander Eriksen · Accepted Answer · 2025-01-07 12:25:45Z

1

Had the same issues, and I made sure to follow the docs at: https://www.postgresql.org/docs/14/libpq-envars.html.

I previously used e.g. POSTGRES_USER, which gave me the same issues, however it worked once I switched to PGUSER (etc..) from the link above.

Here is my configuration file to illustrate:

services:

  db:
    image: postgres
    restart: always
    env_file:
      - .env
    environment:
      PGDATA: /var/lib/postgresql/data
      PGUSER: ${POSTGRES_USER}
      PGDATABASE: ${POSTGRES_DB}
      PGPASSWORD: ${POSTGRES_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      retries: 5
      start_period: 30s
      timeout: 10s
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - "${POSTGRES_PORT}:5432"

volumes:
  db-data: {}

Hope this helps!

It's also worth taking a look at https://github.com/docker-library/docs/blob/master/postgres/README.md

answered Jan 7 at 12:25

Aleksander Eriksen

1311 silver badge7 bronze badges

1 Comment

OJVM Aug 5 at 18:49

The use of PGUSER instead of POSTGRES_USER, avoids the problem of trying to conect the same database of the user name.

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related