Postgres: 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]

https://github.com/docker-library/postgres/issues/203#issuecomment-255200501 :eyes:

[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: https://github.com/docker/compose/issues/2127#issuecomment-144896032)

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".
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

:warning: 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.