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/"]

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.

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.