docker compose psql: error: FATAL: role "postgres" does not exist

Question

I faced a problem when I try to use psql command with my docker-compose file on my local Ubuntu machine: psql: error: FATAL: role "postgres" does not exist

I tried to use others solution like removing docker image, volume. psql -U postgres doesn't work for me either.

I try to use first docker-compose up, then docker exec -it database bash

There's my docker-compose file

services:
  db:
    container_name: postgres
    image: postgres:13.3-alpine
    restart: always
    user: postgres
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=root
    ports:
      - "5432:5432"
    volumes:
      - ./data/db:/var/lib/postgresql/data

Maybe this string tells something? postgres | PostgreSQL Database directory appears to contain a database; Skipping initialization

OUTPUT:

Attaching to postgres
postgres | 
postgres | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres | 
postgres | 2021-08-02 17:29:10.426 UTC [1] LOG:  starting PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
postgres | 2021-08-02 17:29:10.426 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres | 2021-08-02 17:29:10.426 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres | 2021-08-02 17:29:10.429 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres | 2021-08-02 17:29:10.433 UTC [12] LOG:  database system was shut down at 2021-08-02 17:22:17 UTC
postgres | 2021-08-02 17:29:10.438 UTC [1] LOG:  database system is ready to accept connections
postgres | 2021-08-02 17:37:53.452 UTC [33] FATAL:  role "postgres" does not exist
postgres | 2021-08-02 17:37:56.958 UTC [35] FATAL:  role "user" does not exist
postgres | 2021-08-02 17:41:54.294 UTC [45] FATAL:  role "postgres" does not exist```

Matthew Bowolick · Accepted Answer · 2023-03-04 04:05:41Z

25

If you're using 'docker volumes', you are likely using the same volume in another project (at least I was), and the database already exists.

Renaming the volume fixed the issue for me:

volumes:
  volume_name:                                 # <- Rename "volume_name"
    name: volume_name                          # <- Rename "volume_name"

services:
  db:
    container_name: postgres
    image: postgres
    restart: always
    user: postgres
    environment:
      - POSTGRES_USER=pguser
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - volume_name:/var/lib/postgresql/data    # <- Rename "volume_name"

answered Mar 4, 2023 at 4:05

Matthew Bowolick

3513 silver badges2 bronze badges

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

Comments

larsks · Accepted Answer · 2021-08-02 17:58:38Z

16

First, you've set POSTGRES_USER to root, so you're going to have a root user instead of postgres user.

Second, if a database already exists, it doesn't matter what you set for POSTGRES_USER and POSTGRES_PASSWORD -- postgres will use whatever is in the database.

So you can either:

Delete the database (rm -rf data/db) and start over, or
Edit your pg_hba.conf so that you don't need a password

answered Aug 2, 2021 at 17:58

larsks

318k49 gold badges473 silver badges482 bronze badges

7 Comments

Mike 'Pomax' Kamermans Over a year ago

I'm not sure I understand how this would have fixed things? making sure the user matches the username makes sense, but with that change, and destroying/re-up-ing the container, you would still get this error because the image does not appear to create the postgres user role at any point. Also note that according to hub.docker.com/_/postgres, the POSTGRES_HOST_AUTH_METHOD env var set to trust should prevent any need for messing with the .conf file.

larsks Over a year ago

The image creates (when initializing a new database) whatever user is defined in POSTGRES_USER (e.g. postgres in this question).

Mike 'Pomax' Kamermans Over a year ago

I have a docker-compose very similar to the one in the OP, and it very much does not, even after blasting it away and rebuilding it =(

larsks Over a year ago

I refer you to the documentation, which says about POSTGRES_USER: "This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used." If it doesn't behave that way for you, feel free to open a new question and we can figure out what's going on with your environment.

Mike 'Pomax' Kamermans Over a year ago

fair enough, will do.

|

starball · Accepted Answer · 2024-04-17 19:16:51Z

0

Setting up .env helped me, after I specified the variables in docker-compose, deleted the docker folder that was in the project and after that deleted all the containers and started the build for it.

edited Apr 17, 2024 at 19:16

starball♦

59.5k52 gold badges312 silver badges1k bronze badges

answered Apr 17, 2024 at 19:14

Семилеткин Максим Евгеньевич

1

1 Comment

Samuel Aramide Over a year ago

I am not sure this will solves the problem . as this is a volume issue , not a container issue.

Kerli Schroeder · Accepted Answer · 2024-05-26 18:50:38Z

-3

Just renaiming the volumes, solve my problem.

answered May 26, 2024 at 18:50

Kerli Schroeder

1

Collectives™ on Stack Overflow

docker compose psql: error: FATAL: role "postgres" does not exist

4 Answers 4

Comments

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related