Postgresql Docker role does not exist

60,067

Solution 1

The problem was simple enough that my computer was already running an instance of Postgres that I was not aware was still running (not inside Docker) on :5432, checked with:

$ lsof -n -i:5432 | grep LISTEN

So I remembered I installed it via https://gist.github.com/sgnl/609557ebacd3378f3b72, I ran

$ pg-stop

And then I had no problem connecting to the Docker instance.

Edit (2019/07/02)

This question recently passed 10,000 views, so I thought I should elaborate more on why this happened.

Usually running through docker, using python and connecting to a postgres database requires you to install psycopg2, via pip3 install psycopg2, but if you run this command you will get:

Error: pg_config executable not found.

This is because psycopg2 requires an operating system install of the postgres libraries:

yum install postgresql-devel
apt-get install postgresql-client

Now, on a Mac, you will need to do the same with brew:

brew install postgresql

One thing that I didn't realise, is that on Mac, doing the above will not only install required libraries, but also start a database on :5432. Because this was all done in the background, it didn't occur to me that this was the problem as none of the usual errors popped up to inform that the port was being used, etc...

Solution 2

In my case, the problem may be due also to the use of a super user who is not postgres. Lhe developers chose the user georchestra in the geOrchestra docker composition. Extract from the dockerfile:

environment:
  - POSTGRES_USER=georchestra
HEALTHCHECK --interval=30s --timeout=30s \
  CMD pg_isready -U $POSTGRES_USER

consequently, you will have to use the option "-U georchestra" to connect to the container.

$ docker exec -it docker_database_1 psql -U georchestra
psql (10.5 (Debian 10.5-1.pgdg90+1))
Type "help" for help.

and of course, trying to connect with the user postgres causes an error:

docker exec -it docker_database_1 psql -U postgres
psql: FATAL:  role "postgres" does not exist
Share:
60,067
jupiar
Author by

jupiar

Updated on July 09, 2022

Comments

  • jupiar
    jupiar 6 months

    I downloaded the docker container for postgres: https://hub.docker.com/r/library/postgres/, and did the following:

    $ docker run --name satgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres
    satgres | "docker-entrypoint.s…" | 5 seconds ago | Up 6 seconds | 0.0.0.0:5432->5432/tcp | satgres
    $ docker exec -it c8f1b22cc41e /bin/bash        
    # psql -U postgres
    psql (10.1)
    postgres=# CREATE DATABASE mytest;
    postgres=# \password postgres
    postgres=# CREATE ROLE ming WITH LOGIN PASSWORD 'pass1234';
    postgres=# ALTER ROLE ming CREATEDB;
    postgres=# CREATE DATABASE sjk;
    postgres=# GRANT ALL PRIVILEGES ON DATABASE youtube TO ming;
    postgres=# \connect sjk
    youtube=# ALTER ROLE ming lOGIN;  
    

    My plan is to use this database on docker with Django, so first want to check I can connect, but I cant.

    $ psql -h localhost -p 5432 -U postgres
    $ psql: FATAL:  role "postgres" does not exist
    $ psql -h localhost -p 5432 -U ming
    $ psql: FATAL:  role "ming" does not exist
    

    I do the same with PSequal.app, says the same thing, Im running on Mac High Sierra.

    Why am I getting this error? The role exists, or at least it seems it to me...

  • SRC
    SRC over 4 years
    Same happened with me. Your answer saved my day :) Thanks. (For future users, if you are in mac and installed postgres using brew then to stop it just use brew services stop postgresql and you will be able to connect to the docker postgres instance)
  • Ayudh
    Ayudh about 4 years
    I was on my MAC so I had to do: brew services stop postgresql
  • deusofnull about 3 years
    I had the same issue, and decided to make a function in my ~/.bash_profile to check whats running on a port: checkaport(){ lsof -n -i:$1 }. Then you can use it like checkaport 5432 after doing source ~/.bash_profile.
  • bogdan.rusu
    bogdan.rusu over 2 years
    lost hours with this issue - I was on Mac with a local postgres running and tried to connect to one inside Docker
  • allexiusw
    allexiusw about 2 years
    Thanks a lot, the problem was just that I didn't have a superuser called postgres.