docker postgres role does not exist

user_4915008 0
Viewed 1357

I am using postgres with docker and having trouble with it. I successfully docker-compose up --build

When I run below command it works fine. psql starts with my_username user as expected. I can see my database, \l, \dt commands works ok.

docker-compose exec db psql --username=my_username --dbname=my_database

But when I run below commands I get error role “postgres” does not exist,
Additionally \l, \dt not works, even psql command not works

docker exec -it my_db_container_1 bash

su - postgres

createuser --createdb --password new_user

How can I get it right in the second case? What is going wrong? I am confused

docker-compose.yml

version: "3.9"

services:
  #### other services ####
  db:
    image: postgres:latest
    restart: always
    environment:
      POSTGRES_DB: my_database
      POSTGRES_USER: my_username
      POSTGRES_PASSWORD: my_password
    ports:
      - 5432
    volumes:
      - postgres_data:/var/lib/postgresql/data/


volumes:
  postgres_data:
docker-compose docker postgresql django python
1 Answers

You have changed the default username/database/password that the postgres database is initialized with by providing the POSTGRES_USER, POSTGRES_DB, and POSTGRES_PASSWORD environment variables. When you run createuser without a -U option, it tries to connect as the current user (postgres in this case) which doesn't exist on the database because you initialized it with the user my_username. The reason docker-compose exec db psql --username=my_username --dbname=my_database works is because you are correctly supplying the username and database names that the database was initialized with.

If you remove the POSTGRES_USER and POSTGRES_DB environment variables, it will initialize with the defaults postgres/postgres.

Note that because you are mounting a volume into this container, which will now have an initialized database in it already, it will not be reinitialized even if you restart your compose. You need to docker volume rm that volume in order to have the database be reinitialized when you start the container.

user_147071090
Related