Can't connect to Postgres through Docker

Docker Desktop
1

I have created a container with the following Postgres db but when I try to connect to it using pgcli I get this error. I tried everything such as restarting Postgres service, different users/passwords, deleting container/image, uninstall/install Postgres, uninstall/install Docker and nothing worked! Any pointer would be greatly appreciated!

$ pgcli -h localhost -p 5432 -u root -d ny_taxi
Password for root: 
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication 
failed for user "root"

docker run -it \
-e POSTGRES_USER="root" \
-e POSTGRES_PASSWORD="root" \
-e POSTGRES_DB="ny_taxi" \
-v c:/Users/lyesd/Desktop/DE-ZoomCamp/week1_basics_n_setup/2_docker_sql/ny_taxi_postgres_data:/var/lib/postgresql/data \
-p 5431:5432 \
postgres:13

$ docker ps
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
7750be3fadfb   postgres:13   "docker-entrypoint.sā€¦"   18 minutes ago   Up 17 minutes   0.0.0.0:5431->5432/tcp   gallant_agnesi

lyesd@LAPTOP-KB2SKS8F MINGW64 ~/Desktop/DE-ZoomCamp/week1_basics_n_setup/2_docker_sql
$ docker network ls
NETWORK ID     NAME      DRIVER    SCOPE
b371e0426e03   bridge    bridge    local
cc4456f5fb6a   host      host      local
263d85d9a166   none      null      local

$ docker container ls
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    
NAMES
7750be3fadfb   postgres:13   "docker-entrypoint.sā€¦"   29 minutes ago   Up 29 minutes   
0.0.0.0:5431->5432/tcp   gallant_agnesi
2

Hi

According to your ā€œdocker container lsā€
you have made the following mapping:

0.0.0.0:5431->5432/tcp

This means that your LOCAL port 5431 will be mapped to the container port 5432

So you should connect to port 5431 and not 5432 (as you did in your first codeblock)

1 Like
3

@terpz I canā€™t believe it was only the port, you saved me the little sanity I had left after fighting with this issue for the last 48 hours! You are the man!

1 Like
4

(duplicate content deleted by rimelek)

5 
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password au
thentication failed for user "root

pls am having this problem .

$ docker ps
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS
       PORTS                    NAMES
552f1016dce6   postgres:13   "docker-entrypoint.sā€¦"   52 minutes ago   Up 52 min
utes   0.0.0.0:5432->5432/tcp   intelligent_lalande

$  pgcli -h localhost -p 5432 -u root -d ny_taxi
Password for root:
connection to server at "localhost" (::1), port 5432 failed: FATAL:  password au
thentication failed for user "root
6

I edited your first post to delete the content that you repeated in the second post. Make sure you edit your post instead of sending a new repeating the content of the first.
I also edited your second post. Please, use code blocks (</> button) when you insert codes or terminal outputs.

Can you authenticate from the postgresql container instead of the host? Are you sure you used the right password? How did you created that postgres container?

7

Hi, Iā€™m having the same problem as above.

Error connecting to database
panic: failed to connect to `host=localhost user=test_admin database=api`: failed SASL auth (FATAL: password authentication failed for user "test_admin" (SQLSTATE 28P01))

C:\Users\User>docker ps
CONTAINER ID   IMAGE                   COMMAND                  CREATED         STATUS         PORTS                              NAMES
cc1746744c31   postgres:10.14-alpine   "docker-entrypoint.sā€¦"   3 minutes ago   Up 3 minutes   0.0.0.0:5118->5118/tcp, 5432/tcp   payd-exercise-postgres

I have changed the port from 5432 to 5433 and finally 5118. I thought itā€™s a port conflict as I have pgadmin4 running at port 5432 as well. But still, I canā€™t connect to database running from Docker.

8

Please share the exact command you used to create the postgres container.

It looks like you publish host port 5118 on container port 5118. Did you change the postgres port inside the container to 5118 as well? (this is not a suggestion to do so, actually please donā€™t!)

1 Like
9

@lyesd you simply need to use 127.0.0.1 as -h parameter.

When you use ā€œlocalhostā€ pg/mysql clients are assuming to find the .sock file locally and use file based socket communication with the server: but the problem is the file is inside the container!

When you use ā€œ127.0.0.1ā€ pg/mysql clients are instead using network for communication with the server, and it works fine.

10 
docker run --name postgres -p 5433:5432 -h 127.0.0.1 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:latest

I have changed the mapped port from 5432:5432 To 5433:5432 and now itā€™s working fine and connected

2 Likes
11

This worked. Thanks!