with docker-compose.yml "Root or postgres or any role does not exist", postgres, postgrest, swagger connect to db

[Ihsan06]

Hi,

i'm new to docker and need your help .. i just cant find any solution. Trying to connect to the DB via docker-compse.yml.

C:\Users\iso01>docker ps
CONTAINER ID        IMAGE                   COMMAND                  CREATED             STATUS              PORTS                            NAMES
4960b440ac65        postgrest/postgrest     "/bin/sh -c 'exec po…"   5 minutes ago       Up 5 minutes        0.0.0.0:3000->3000/tcp           server
de9721b44783        swaggerapi/swagger-ui   "/docker-entrypoint.…"   5 minutes ago       Up 5 minutes        80/tcp, 0.0.0.0:8080->8080/tcp   swagger
4bbcb3f9a698        postgres                "docker-entrypoint.s…"   5 minutes ago       Up 5 minutes        0.0.0.0:5432->5432/tcp           level3

C:\Users\iso01>docker exec -it level3 bash
root@4bbcb3f9a698:/# psql
psql: error: could not connect to server: FATAL:  role "root" does not exist
root@4bbcb3f9a698:/# psql -U postgres
psql: error: could not connect to server: FATAL:  role "postgres" does not exist
root@4bbcb3f9a698:/# psql -U app_user
psql: error: could not connect to server: FATAL:  database "app_user" does not exist
root@4bbcb3f9a698:/#

I'm using this docker-compose.yml

docker-compose.yml

version: '3.5'
services:
  server:
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    links:
      - db:db
    environment:
      PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
      PGRST_DB_SCHEMA: public
      PGRST_DB_ANON_ROLE: app_user #In production this role should not be the same as the one used for the connection
      PGRST_SERVER_PROXY_URI: "http://127.0.0.1:3000"
    depends_on:
      - db
    container_name: server

  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: password
  # Uncomment this if you want to persist the data.
    volumes:
      - "./pgdata:/var/lib/postgresql/data"
    container_name: level3


  swagger:
    image: swaggerapi/swagger-ui
    ports:
      - "8080:8080"
    expose:
      - "8080"
    environment:
      API_URL: http://localhost:3000/
    container_name: swagger

maybe there is a problem with starting the container without the env variables set. So it was then initialized without user or DB info?

Thank you!

[wglambert]

Your error Database directory appears to contain a database; Skipping initialization means the environment variables weren't applied. Whatever is in the host's ./pgdata is what's in the container, if you don't see anything in that folder then there's probably a hidden file that's causing it to be non-empty

https://github.com/docker-library/docs/tree/master/postgres#environment-variables

the Docker specific variables will only have an effect if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup.

You might also try using a named volume instead

[Ihsan06]

First of all thanks for your quick response. However, what do you mean with in the host's ./pgdata . The only time i use it is in the .yml
i've tried a lots of things from your link, but it didnt worked for me. I'm not a Developer maybe that's why i dont get it :D

• I noticed a new user, wich i couldnt delete because there always a Notification with th folder is been used so i cant delete. Maybe thats the hidden problem?

[wglambert]

The folder being used as the data directory ./pgdata:/var/lib/postgresql/data has files in it so the entrypoint script won't modify your data, which means the docker-specific environment variables aren't going to be interpreted in the container. #203 (comment)

You could also try asking over at the Docker Community Forums, Docker Community Slack, or Stack Overflow. Since these repos aren't really a user-help forum

[Ihsan06]

alright, yea i will try it there, too. Just could you please tell me how i can find the data directory to ./pgdata:/var/lib/postgresql/data ? #203 didn't help for me either. I want to inspect it.

So all in all you can't tell me why this problem appears? i'm learning a lot but going insane too...
When i do docker inspect level3 only i can see it. I tried to change the ports or remove all (volumes too) and start all over again etc. but no

Thank you a lot!

[yosifkit]

remove all (volumes too) and start all over again

You are not technically using a docker volume, you are mounting a local folder, ./pgdata, into the container. To "start all over", you need to delete that folder (or everything in it; likely as root). Be warned, that this will delete anything that you had put into the database.

[Ihsan06]

Ok, I see that point.. so the big question is, how can I delete that folder in the container? There is no problem for me to delete anything in the database, because it's empty and I'll put in data after i can bring up an API for postgres ...

Thank you guys! :)

i tried this before docker system prune -a

[yosifkit]

how can I delete that folder in the container

The point is that it is not in the container. Just delete the folder: sudo rm -rf ./pgdata

[limsammy]

I'm not sure why or how this actually works, but I was hung up on this issue for a while now.

I had a postgres service where I specified an env_file that looked like this:

POSTGRES_USER=test_user
POSTGRES_PASSWORD=test_pass
POSTGRES_DB=test_db

Here's the docker-compose excerpt:

db:
        image: postgres:latest
        restart: always
        env_file:
            - ./.env.db
        ports:
            - "5423:5432"
        volumes:
            - postgres-data:/var/lib/postgresql/data

Outputting my config showed that all of those vars WERE being set, except for POSTGRES_USER:

  db:
    environment:
      POSTGRES_DB: test_db
      POSTGRES_PASSWORD: test_pass
      POSTGRES_USER: postgres
    image: postgres:latest
    ports:
    - published: 5423
      target: 5432
    restart: always
    volumes:
    - postgres-data:/var/lib/postgresql/data:rw
version: '3.8'
volumes:
  postgres-data: {}

I noticed that the order of environment variables differed slightly from how I defined them in my ./env.db, specifically the POSTGRES_USER var: it appeared to be defined last.

So I changed my .env.db to reflect that order:

POSTGRES_PASSWORD=test_pass
POSTGRES_DB=test_db
POSTGRES_USER=test_user

Now, running docker-compose config to check my env vars, showed the following correct values 😄 😄 😄

db:
    environment:
      POSTGRES_DB: tesdt_db
      POSTGRES_PASSWORD: test_pass
      **POSTGRES_USER: test_user**
    image: postgres:latest
    ports:
    - published: 5423
      target: 5432
    restart: always
    volumes:
    - postgres-data:/var/lib/postgresql/data:rw

References: #203

[aelfannir]

I had the same issue, i proactively remove the volumes docker-compose down --volumes, then worked for me

[AngelGuante]

delete and create the postgres_data folder and run docker-compose up -d again.

[Maksim-Burtsev]

remove all (volumes too) and start all over again

You are not technically using a docker volume, you are mounting a local folder, ./pgdata, into the container. To "start all over", you need to delete that folder (or everything in it; likely as root). Be warned, that this will delete anything that you had put into the database.

Thank you, it's help me. (+ removed all containers and start from scratch)

[tianon]

In the future, these sorts of questions/requests would be more appropriately posted to a dedicated support forum, such as the Docker Community Forums, the Docker Community Slack, or Stack Overflow.

[leonardo403]

I had the same issue, i proactively remove the volumes docker-compose down --volumes, then worked for me

After run command again docker-compose up <container_name>