Dockerizing PostgreSQL - psql Connection refused

Question

I'm playing around with Docker and I would like to Dockerize a Postgres container.

I'm following the official example but I can not connect to the image running using psql.

I created the Dockerfile with the content of the example. I builded an image from the Dockerfile and assigned it a name. Then I run the PostgreSQL server container (in the foreground).

~/test » docker run --rm -P --name pg_test eg_postgresql                                                                                                       
2014-10-10 06:12:43 UTC LOG:  database system was interrupted; last known up at 2014-10-10 06:12:29 UTC
2014-10-10 06:12:43 UTC LOG:  database system was not properly shut down; automatic recovery in progress
2014-10-10 06:12:43 UTC LOG:  redo starts at 0/1782F68
2014-10-10 06:12:43 UTC LOG:  record with zero length at 0/1782FA8
2014-10-10 06:12:43 UTC LOG:  redo done at 0/1782F68
2014-10-10 06:12:43 UTC LOG:  last completed transaction was at log time 2014-10-10 06:12:29.2487+00
2014-10-10 06:12:43 UTC LOG:  database system is ready to accept connections
2014-10-10 06:12:43 UTC LOG:  autovacuum launcher started

Then I open another terminal to find out the port:

~/test » docker ps                                                                                                                                             
CONTAINER ID        IMAGE                  COMMAND                CREATED             STATUS              PORTS                     NAMES
aaedb0479139        eg_postgresql:latest   "/usr/lib/postgresql   3 days ago          Up 41 seconds       0.0.0.0:49154->5432/tcp   pg_test

So I can use psql to connect to the instance. But I can't...

~/test » psql -h localhost -p 49154 -d docker -U docker --password                                                                                             
Password for user docker:
psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 49154?
could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 49154?
could not connect to server: Connection refused
    Is the server running on host "localhost" (fe80::1) and accepting
    TCP/IP connections on port 49154?

Any help is appreciated.

Nievelt

Does it work using container linking' ? – Anderson

Yes, it does. But I want to be able to connect from my host system. – Nievelt

What does netstat | grep 49154 say ? – Anderson

If you use 5433:5432 it won't expose 5433 port, and you will get connection refused. As soon as you change it to "5433:5432" it will work. Tested with the official postgres:13.2 image(13 as well). – Appliance

Answer 1

Running this on my mac worked for me:

 $ boot2docker ip
 The VM's Host only interface IP address is: 192.168.59.103

And then connect along the lines of:

 $ psql -h 192.168.59.103 -p 49159 -d docker -U docker --password

It's less than ideal to have to do this all, but the instructions at https://docs.docker.com/installation/mac/ indicate it is the correct solution if you want to connect directly from you mac.

Honshu

Is this maybe a network configuration issue? I know it wasn't easy to set up the network to expose my containers to the local host's env. My case is VirtualBox running a centos7 guest. I needed to add a second interface to the virtual box network config and use host-only mode to join the host and guest on a second subnet. Then I used squid to reverse proxy between the networks. There probably is a way to do this with routes, but this seems to work fine. – Quincey

Answer 2

Solution for me was simply using host.docker.internal instead of localhost in your connection string.

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/225

Nammu

This worked for me. (note: i am also running pgadmin on docker) – Cavell

Worked for me as well, and I hadn't needed it the last time I did this same setup... I'm not sure why. However, it makes me happy that it worked! – Bigg

Worked for me, running docker with WSL on Windows – Knotting

Answer 3

If you add the --publish option to the docker run command

docker run --rm -P --publish 127.0.0.1:5432:5432 --name pg_test eg_postgresql 

when you run the docker file, then the following will work (note the port is now 5432)

psql -h localhost -p 5432 -d docker -U docker --password

Tabber

Still happening, looks like publishing the port does not solves the issue. – Nievelt

Do you have a firewall that is preventing you from connecting to the port? – Tabber

Answer 4

To get IP address of your running container you can:

docker inspect pg_test | grep IPAddress

and then use it instead of 'localhost'.

Where pg_test is container name received from

docker ps

Olathe

Or for an even cleaner output docker inspect <container-name> | jq '.[].NetworkSettings.IPAddress' – Matchboard

Answer 5

Someone has answered to this question here

To sum up : It is not enough to publish the container's port because on OS x the docker containers are running on virtual machines. To retrieve the "real" address of the virtual machine you should use the tool which is managing your docker daemon and containers.

I would recommand to use docker-machine

The logic is very similar to wf answer but instead of using boot2docker it uses docker-machine.

1-/ Retrieve the name of the virtual machine

docker-machine ls

2-/ Retrieve the IP Address of the virtual machine by using its name e.g, default

docker-machine ip default

3-/ Use this value wherever you need to specify an host value.

Parasiticide

doesn't work with m1 machine. not sure why. It doesn't list my dockers – Allodium

Answer 6

This question comes up whenever you search up "postgres connection refused running on docker container" - so I wanted to show a possible solution for people that are looking on here and are using WSL.

If you are getting any issues such as

postgres access denied | password invalid for postgres

or anything of the sort on connecting, then, while seemingly like an obvious answer, check if the port is already being used.

Since this answer is for WSL users, you want to check if there are any ports running on powershell / cmd prompt (running as administrator) - link on how to do so here

If there are ports running on there, make sure they are not important processes (though, if its running on 5432, that usually is only mapped to postgres, meaning you might have had postgres running on your machine before) - then kill the process. Connections should work from here.

This troubled me for a bit before I realized I was stupid and already had set something up earlier before, but not on my linux subsystem.

Joub

Answer 7

In my case, I was missing post confiuguration in the docker run command (see the end)

docker run -d --name daily_db -e POSTGRES_PASSWORD=secps12et -e PGDATA=/var/lib/postgresql/data/pgdata -v postgres_data:/var/lib/postgresql/data -p 5432:5432 postgres

So make sure you are using that as well. It basically creates a mapping from host's port to container's port.

Chartres