User and database not created with docker-compose

[foucdeg]

(Copy of my SO question)

The docs explain that you can make the image create a user and database on creation, using environment variables.

I can't seem to make that work using docker-compose:

# docker-compose.yml
services:
  postgresql:
    image: postgres:alpine
    environment:
      POSTGRES_DB: iotplatform
      POSTGRES_USER: iotplatform
      POSTGRES_PASSWORD: iotplatform

and here's what I run:

docker-compose up -d --force-recreate postgresql
docker-compose exec postgresql psql -U iotplatform
# psql: FATAL:  role "iotplatform" does not exist

When I run docker-compose exec postgresql env, I see the environment variables as configured.

The logs don't say anything particular:

Attaching to iot-container-tracker_postgresql_1
postgresql_1  | 2018-12-17 17:35:05.754 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgresql_1  | 2018-12-17 17:35:05.754 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgresql_1  | 2018-12-17 17:35:05.757 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgresql_1  | 2018-12-17 17:35:05.770 UTC [21] LOG:  database system was shut down at 2018-12-17 17:35:03 UTC
postgresql_1  | 2018-12-17 17:35:05.772 UTC [1] LOG:  database system is ready to accept connections
postgresql_1  | 2018-12-17 17:35:22.639 UTC [34] FATAL:  role "iotplatform" does not exist

Without docker-compose, the variables work fine:

docker run --name some-postgres -e POSTGRES_PASSWORD=iotplatform -e POSTGRES_DB=iotplatform -e POSTGRES_USER=iotplatform -d postgres:alpine
docker exec -it some-postgres psql -U iotplatform
iotplatform=#

What am I missing?

[wglambert]

I can't reproduce any issue with your compose file

version: '3'
services:
  postgresql:
    image: postgres:alpine
    environment:
      POSTGRES_DB: iotplatform
      POSTGRES_USER: iotplatform
      POSTGRES_PASSWORD: iotplatform

$ docker-compose up -d  
Creating network "postgres-537_default" with the default driver
Creating postgres-537_postgresql_1 ... done

$ docker exec -it postgres-537_postgresql_1 psql -Uiotplatform
psql (11.1)
Type "help" for help.

iotplatform=#

[foucdeg]

@wglambert After running down and up it works. Thanks for your help!

Today I learned: there are harder resets than --force-recreate.

[joaosgreccia]

I am experiencing the same issue. How did you solve?

[foucdeg]

@joaosgreccia docker-compose down and docker-compose up --force-recreate

[yosifkit]

#203 (comment) 👀

[mangei]

recreating the volume solved the problem for me.

found the solution here: https://stackoverflow.com/questions/48629799/postgres-image-is-not-creating-database/54200233#54200233

[jonathanvanschenck]

recreating the volume solved the problem for me.

found the solution here: https://stackoverflow.com/questions/48629799/postgres-image-is-not-creating-database/54200233#54200233

In particular, if your volume is still attached to your container and you want to hard reset it, use:

 $ docker-compose stop
 $ docker-compose rm

(fuller discussion here: docker/compose#2127 (comment))

On the other hand, if your volume was orphaned from your container somehow (which is what happened to me), you can use

 $ docker volume ls

to list out the the current volumes on your system (and the orphaned one will show up here, if it exists), then you can use:

 $ docker volume prune

to remove all the volumes that aren't currently being referenced by a container (i.e. your orphaned volume).

[antooa]

Still have the same problem. Nothing from above helped :(

[frenzymind]

In same situation. I clear volume, run compose file without volume. But still my db name is "postgres".

    image: postgres:12.2
    restart: always
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=notice
    volumes:
      - pgdata:/var/lib/postgresql/data```

I see variable inside container:

root@da56af4f3da7:/# echo $POSTGRES_USER
beaver
root@da56af4f3da7:/# echo $POSTGRES_PASSWORD
de0bzdJgD78UaJM7taZx
root@da56af4f3da7:/# echo $POSTGRES_DB
notice

I can connect to db with this credentials through dbeaver, but for the same time I cant connect in console:

root@da56af4f3da7:/# psql -Ubeaver
psql: error: could not connect to server: FATAL: database "beaver" does not exist
root@da56af4f3da7:/# psql -Upostgre
psql: error: could not connect to server: FATAL: role "postgres" does not exist
root@da56af4f3da7:/# psql postgres

[anentropic]

I had same problem and solution above from @foucdeg worked for me

You should put a note in the docs about this

It's too easy to docker-compose up postgres early on in development with different or incompletely specified env vars and then later settle on the user/pass/db etc you want.... but it's not clear at that point why things don't work as described in the docs

Once you know why it's obvious but it's frustrating for a while before that

[foucdeg]

@anentropic see @yosifkit 's linked issue, it's probably a better place to suggest documentation.

[anentropic]

Pretty sure docker-compose will re-up the container if you change any values in the compose file

So I think the issue is around the persistent volume, i.e. change in env vars has no effect on the existing db.

...right, this is what's described in #203 as you say

The behaviour makes sense when you understand how it works, which is why I think a note about this specific case in the docs would be helpful.

[amirmahdisaadati]

i have same problem
i read all of comment on this post but my problem doesnt solve
role root does not exist

[ginsburgnm]

@amirmahdisaadati I had a similar problem; this is what I ended up doing (slowly tacking on flags until it worked)

sudo docker-compose up --force-recreate --build --remove-orphans --always-recreate-deps --renew-anon-volumes

[yaselc]

Stop containers and remove the volumes created by up.

$ docker-compose down --volumes

Stop containers and remove containers, networks, volumes, and images created by up.

$ docker-compose down --rmi all --volumes

⚠️ Be careful, using --rmi all removes all images used by any service
See the docs to choose the options that best suit your needs.

[GeekEast]

Please make sure there is no other postgres processes running on the same port you export the container.

[LoGoFiOS]

I had the same problem. My solution:

sudo docker-compose down --volumes
sudo docker-compose down --rmi all --volumes

Create /db_init/Dockerfile:

FROM library/postgres
ENV POSTGRES_USER admin
ENV POSTGRES_PASSWORD 123456
ENV POSTGRES_DB admin

In Docker-compose I set for postgre service:

***
services:
  db_docker:
    container_name: database
    build:
      context: ./db_init/
    volumes:
      - ./postgres-data:/var/lib/postgresql
    ports:
      - 5432:5432
    networks:
      - ***
    restart: always

  *another services*

But I don't know how set env from .env in /db_init/Dockerfile

[sybernatus]

I had the same issue.
In addition to remove volumes with docker command, I had to remove also the directory where the volume was mounted on the host

sudo rm -rf /opt/var/data/db

Here is my docker-compose.yml

version: "3.9"

services:
  db:
    container_name: postgres
    image: postgres:10
    environment:
      POSTGRES_DB: xxxxx
      POSTGRES_USER: xxxxx
      POSTGRES_PASSWORD: xxxxxx
    ports:
      - "5432:5432"
    volumes:
      - /var/opt/data/db:/var/lib/postgresql/data```

[IvanCampelo22]

I have a problem, because I do the docker-compose up with postgres, everything works normally, but when I go to my postgres, in psql and give the command \dt, in my database, the message appears: 'Did not find any relations.'. Does anyone know what can it be?

Here is my docker-compose

version: "3.9"
   
services:
  db:
    image: postgres
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    ports: ["5431:5432"]
    environment:
      - POSTGRES_ENGINE=${POSTGRES_ENGINE}
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_HOST=${POSTGRES_HOST}
      - POSTGRES_PORT=${POSTGRES_PORT}
  web:
    build: .
    command: python manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    environment:
      - POSTGRES_ENGINE=${POSTGRES_ENGINE}
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_HOST=${POSTGRES_HOST}
      - POSTGRES_PORT=${POSTGRES_PORT}
    depends_on:
      - db

When I run on localhost, without docker-compose, the database is populated, so I believe the problem is in my docker-compose

[kirinalexdev]

I had the same problem. My solution:
docker-compose down --volumes