1

Problem

I'm unable to connect to a PostgreSQL database (14.1/latest) running in a docker image on my laptop. I'm trying to connect to the database both with .NET EntityFrameworkCore/Npgsql running in another container on the laptop, and with psql running directly on the laptop.

Environment

  • MacOS 10.15.7
  • Docker Desktop 4.3.2 (72729)

Recreate

I start my postgres container like so. Note that since it's in host network mode, I don't publish ports.

docker run -d --network=host --name=my-database -v dbdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=postgres postgres
docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS      NAMES
18e154fe815c   postgres   "docker-entrypoint.s…"   4 minutes ago   Up 4 minutes   5432/tcp   my-database

Logs from the my-database container show

...
2022-01-11 16:56:18.179 UTC [1] LOG:  starting PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-01-11 16:56:18.179 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-01-11 16:56:18.179 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-01-11 16:56:18.182 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-11 16:56:18.188 UTC [26] LOG:  database system was shut down at 2022-01-11 16:35:54 UTC
2022-01-11 16:56:18.203 UTC [1] LOG:  database system is ready to accept connections

At this point I expect to be able to connect to the database, but I get an error.

$ psql -h localhost -p 5432 -U postgres
psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
Ben
  • 4,980
  • 3
  • 43
  • 84
  • have you allowed local host in https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html – Santosh Karanam Jan 11 '22 at 18:25
  • @SantoshKaranam i have not done anything like that. I really don't think I need to, since I have gotten this working before with the same image. Again, I have done no additional configuration of the database image beyond what you see. – Ben Jan 11 '22 at 18:30
  • I never use network=host but my guess is you would still need to publish the port using for instance `-p 5432:5432` – Chai Jan 11 '22 at 18:34
  • have to logged into the container with "docker container exec -it 18e154fe815c cmd" before you ran the "psql -h localhost -p 5432 -U postgres" – Santosh Karanam Jan 11 '22 at 18:34
  • 2
    @Chai Then you'd be wrong – Hans Kilian Jan 11 '22 at 18:37
  • @Chai, Hans Kilian is correct. In fact combining the two flags gives a warning that published ports are discarded. Satosh Karanam, I'm now certain that there is no need to log in to the container or modify anything in it, as I have successfully made the connection. I'll post my "resolution". I do appreciate the effort, of course – Ben Jan 11 '22 at 19:07
  • great, I learned something here. Thx – Chai Jan 11 '22 at 19:47

1 Answers1

1

I really can't say I understand what's happened here, but after trying a number of combinations of flags, this is what allows me to connect as I expect

docker run -d --rm -p5432:5432 --name=my-datbase -v dbdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=postgres postgres

Obviously this is a different strategy than I proposed above, since it eliminates the --network=host param, and instead publishes its port.

Now, as the comment on this answer suggests, this does not necessarily resolve another, related case, which is how to connect to this database from another container.

To make this work, I had to create a docker network, i.e.

docker network create my-network

and the add the flag --network=my-network to each of the containers: the my-database container and the application container that needs to connect to the database. Now both of these containers are on the same virtual network.

Finally, from the application container I can run (for example) psql -h my-database -p 5432 -U postgres . The hostname is the name of the docker container, i.e. docker run --name=my-database ...

That is, the two containers must be on the same docker network. It appears that, contrary to my initial instincts, the default bridge network does not work this way, so a custom one is required.

docker network ls and docker network inspect my-network will be useful to others debugging similar situations.

Much of this could be simplified using docker-compose but that was not a viable option in my particular case.

Ben
  • 4,980
  • 3
  • 43
  • 84
  • it is directly mapping port 5432 on host to port 5432 on the container, if that is the behavior you want to go with, but using name connection like `my-database` is preferred when you are connecting from another container, in environment variables you would use the container name instead of localhost – M-Raw Jan 11 '22 at 19:18
  • @M-Raw indeed, this is the case, and thanks for pointing it out. In this case, I am developing a local developer workflow, and the particulars are tedious, but involve connection from VisualStudio. In the production version, k8s is where such connection information is handled (And that works great). – Ben Jan 11 '22 at 19:22