Failure to connect to Docker Postgresql instance from Python

Question

I am using Docker to "containerize" a PostgreSQL deployment. I can spin up the container and connect to PostgreSQL via the command line as shown below:

minime2@CEBERUS:~/Projects/skunkworks$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
dc176901052a        df:pg               "docker-entrypoint..."   About an hour ago   Up About an hour    5432/tcp            vigilant_agnesi

minime2@CEBERUS:~/Projects/skunkworks$ CONTAINER_ID=dc176901052a
minime2@CEBERUS:~/Projects/skunkworks$ IP=$(docker inspect -f '{{.NetworkSettings.Networks.bridge.IPAddress}}' $CONTAINER_ID)

minime2@CEBERUS:~/Projects/skunkworks$ echo $IP
172.17.0.2


minime2@CEBERUS:~/Projects/skunkworks$ docker exec -it vigilant_agnesi psql -U postgres -W cookiebox
Passwod for user postgres:
psql (9.6.5)
Type "help" for help

cookiebox#

Now attempting connection with Python:

Python 3.5.2 (default, Sep 14 2017, 22:51:06) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> conn = psycopg2.connect("dbname='cookiebox' user='postgres' host='172.17.0.2' password='nunyabiznes'")                                     Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/minime2/Projects/skunkworks/archivers/env/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
        Is the server running on host "172.17.0.2" and accepting
        TCP/IP connections on port 5432?

>>> 

Can anyone explain why I can't connect to PostgreSQL using Python - even though I'm using the same arguments/parameters that enable a successful connection at the command line (using docker exec?).

[[Additional Info]]

As suggested by @Itvhillo, I tried to use a desktop application to connect to the PG service. I run the docker service using the following command:

docker run -i -p 5432:5432 --name $CONTAINER_NAME $DOCKER_IMAGE

I am using Db Visualizer to connect to the database, and I have set the hostname to 'localhost'. I can successfully ping the port, but still get an error message when I try to connect to the database (possible permissions related error):

An error occurred while establishing the connection:

Long Message:
The connection attempt failed.

Details:
   Type: org.postgresql.util.PSQLException
   SQL State: 08001

Incidentally, this is the tail end of the output for the PG service instance:

PostgreSQL init process complete; ready for start up.

LOG:  could not bind IPv6 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was shut down at 2018-01-30 16:21:59 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[[Additional Info2]]

Here is the tail end of my Dockerfile:

# modified target locations (checked by login onto Docker container)
# show hba_file;
# show config_file;

#################################################################################
# From here: https://docs.docker.com/engine/examples/postgresql_service/
# Adjust PostgreSQL configuration so that remote connections to the
# database are possible.
RUN echo "host all  all    0.0.0.0/0  md5" >> /var/lib/postgresql/data/pg_hba.conf

# And add ``listen_addresses`` to ``/var/lib/postgresql/data/postgresql.conf``
RUN echo "listen_addresses='*'" >> /var/lib/postgresql/data/postgresql.conf
#################################################################################


EXPOSE 5432

# Add VOLUMEs to allow backup of config, logs and databases
VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql", "/usr/lib/postgresql/"]

Americaamerican

Your postgresql container couldn't bind socket to listen for TCP connections for some reason. Which image do you use? Why wouldn't you use the official one? – Excretion

@Excretion As I mentioned earlier, I'm pulling from the official postgres:9.6 image. I am writing my own Dockerfile because I have some C extension libraries which I am building to be used by PostgreSQL (before anyone gets distracted by this red herring, I have to say that I am not using the custom built extensions yet - so it's completely irrelevant). – Americaamerican

Answer 1

If you are are running

$ docker run -i -p 5432:5432 --name $CONTAINER_NAME $DOCKER_IMAGE

Then you should be able to connect to localhost:5432 from the host. The easiest way to check whether something is listening on port 5432 is using netcat. In case of success you should get:

$ nc -zv localhost 5432 
Connection to localhost 5432 port [tcp/postgresql] succeeded!

In this case, you should be able to connect using:

>>> psycopg2.connect("dbname='cookiebox' user='postgres' host='localhost' password='nunyabiznes'") 

If, on the other hand, you get something like:

$ nc -zv localhost 5432 
nc: connect to localhost port 5432 (tcp) failed: Connection refused

Then it means that PostgreSQL is not listening, and hence something is wrong in your Dockerfile, and you'll need to post more details on your Dockerfile to diagnose it.

Nero

Answer 2

It seems that PostgreSQL couldn't bind socket to listen for TCP connections for some reason. It still listens the default UNIX socket inside the container, though, so you could connect to it via docker exec -it $CONTAINER_NAME psql.

Excretion

Answer 3

In the postgresql.conf file change listen_addresses = 'localhost' to listen_addresses = '*' Then try to connect via psql

psql -h docker-ip -u username -w

Quar

Answer 4

Try to isolate the issue: run clean postgres instance without any extensions on default port:

docker run  -d --name tst-postgres-01 -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword postgres:9.6

And try to connect to it. If you can, then you have to review your Dockerfile accordingly: just remove everything from there and then add new things one by one. Otherwise, if it doesn't connect, then try to run it on other port:

docker run  -d --name tst-postgres-01 -p 45432:5432 -e POSTGRES_PASSWORD=mysecretpassword postgres:9.6

And try to connect. If it works this time, then the issue your network configuration on host machine, somehow 5432 port is blocked or in use by other app.

Inferior

Answer 5

---

I have two docker containers, one for jupyter-notebook, one for the Postgres server. Even if I had the port 5432 open, I could not reach it with psycopg2 from my notebook. I tried with both Localhost and 127.0.0.1, but it was not working for me.

The solution was, to create a separate docker network and add both images to it.

docker network create con
docker network connect con postgres-db-1
docker network connect con jupyter-lab-cuda

To get the correct ip of your postgres server, run:

docker network inspect con

Search for something like this:

"Name": "postgres-db-1",
                "EndpointID": "c470f620cb02ef19d6c1213df5c86e5e02531206a0a9c4759cd5cccd0665a5e5",
                "MacAddress": ".....",
                "IPv4Address": "172.28.0.2/16",

Then you should use 172.28.0.2:5432 to connect to your server.

Beekeeping