Unable to connect to Postgres from inside Docker container

Question

I have a bunch of Docker containers (which in fact is Apache Superset system). I run them using docker-compose, like so:

$ docker-compose run

The docker-compose.yml file can be taken from official Apache Superset repo. The problem is - I can not create new database connection to my Postgresql database running on the same host where dockerized Apache Superset is running. I can connect to Postgresql from wherever I like. On the host machine I can connect to it from Python, using both 127.0.0.1 address and 192.X.X.X address. But Apache Superset container can not establish connection to this Postgres instances - I tried both 127.0.0.1 and 192.X.X.X - and they are both not working.

When I enter the superset Docker container shell, I can check all relevant IP addresses:

superset@2cf0e6dde567:/app$ ip addr show eth0
48: eth0@if49: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
link/ether 02:42:ac:12:00:06 brd ff:ff:ff:ff:ff:ff link-netnsid 0
inet 172.18.0.6/16 brd 172.18.255.255 scope global eth0
   valid_lft forever preferred_lft forever

So, it seems like my superset Docker container has 172.18.0.6 address. I also check this:

superset@2cf0e6dde567:/app$ route
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
default         172.18.0.1      0.0.0.0         UG    0      0        0 eth0
172.18.0.0      0.0.0.0         255.255.0.0     U     0      0        0 eth0

So, if I'm not mistaken my host address where Postgres is running can be reached using this ip address - 172.18.0.1. I check it with ping and see that it's ok:

$ ping 172.18.0.1
PING 172.18.0.1 (172.18.0.1) 56(84) bytes of data.
64 bytes from 172.18.0.1: icmp_seq=1 ttl=64 time=0.061 ms
64 bytes from 172.18.0.1: icmp_seq=2 ttl=64 time=0.103 ms
64 bytes from 172.18.0.1: icmp_seq=3 ttl=64 time=0.063 ms

But still I can not connect to Postgres using this IP address. Probably, I need to configure Postgresql itself, but I do not know how. My pg_hba.conf looks like so:

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 ident
host    all             all             0.0.0.0/0               md5

And my postgresql.conf contains:

listen_addresses = '*'

Probably, I'm still missing something. How can I fix it?

PS.

This is what I tried inside superset container in Python shell:

import psycopg2
import sqlalchemy as db

#engine = db.create_engine('postgresql://postgres:[email protected]:5433/test')
#engine = db.create_engine('postgresql://postgres:[email protected]:5433/test')
engine = db.create_engine('postgresql://postgres:[email protected]:5433/test')
cnx = engine.connect()

I tried all possible ips: 192.168.234.137 (my host machine ip), 127.0.0.1 and 172.18.0.1. None of them works. I just get an error message from Python:

No route to host
    Is the server running on host "X.X.X.X" and accepting
    TCP/IP connections on port 5433?

where X.X.X.X is one of 192.168.234.137, 127.0.0.1 and 172.18.0.1

Cano

I know about this thread - #24320162 . But as you can see, it does not help 172.X.X.X address is reachable from Docker, but database connection still can not be established – Cano

And If I simply add network_mode: host to the official docker-compose.yml, it crashes everything. So, I would rather fix this problem, without tweaking any code in the repository. – Cano

According to the docker-compose.yml you linked, it listens on localhost. From the same host, can you connect to it on host 127.0.0.1, port 5432? – Wulfila

@Robert Seaman. On my host machine Postgres is running at port 5433 in order not to conflict with Apache Superset own Postgres container. And I can not establish connection to my Postgres instance, using host 127.0.0.1, port 5433. I will attach serevral screenshots – Cano

How did you configure postgres to run on 5433? Are you not using the docker-compose.yml you linked? – Wulfila

I'm using the default Dockerized postgres. I just do not change the default docker-compose.yml file. But on my host machine I have another Postgres instance running at port 5433, and it is absolutely ok. I can connect to it from wherever I like. I configured it with port = 5433 in postgresql.conf file. – Cano

And what I want is to create a new datasource in Apache Superset. To do this I need to supply it with ip, port and etc. of my Postgres instance running on the host machine – Cano

try put in docker-compose.yml before any image configuration networks: <network_name>: driver: bridge this instruction will create a new bridge network, then you put it in each image you want to run it on this network, like this: superset-worker: networks: <network_name> build: *superset-build hope this will help you – Evocation

@alim91. Thanks! Can you make a formated answer from this comment? And I will check it – Cano

Answer 1

in docker-compose.yml put before any image configuration instruction:

networks:
    <network_name>:
        driver: bridge

then put networks: <network_name> instruction in any image you want to run it on this network, like this:

superset-worker:
    networks: 
        - <network_name> 
    build: *superset-build

Evocation

It does not work. I get ERROR: The Compose file './docker-compose.yml' is invalid because: services.db.networks contains an invalid type, it should be an array, or an object. Probably, I do something wrong. – Cano

make sure you put the first network instruction outside services and at the same level of indent, and I did edit my answer, for the image service, the network name should be on a new line, my bad. – Evocation

. plus <network_name> is a place holder, so you can write anything name you want without <> symbols – Evocation

I checked it. It did not help. I checked even connection to dockerized Postgres, running through the same docker-compose up command. I can connect it only using service name db. I tried 127.0.0.1 and it does not work. – Cano

did you put networks instruction in db service? – Evocation

I put it to all services, including db service – Cano

did you put double quotes to network name and depends_on? if you can post the docker-compose.yml file would be better? – Evocation

In fact, you can easily reproduce the whole problem. Just clone the official repo, make sure that you have enough RAM, then run it with docker-compose and try to connect to Postgresql instance running on host from supersetcontainer. – Cano

I did not put any dooble quotes and I'm not using depends_on construct.I just added what you adviced to add. – Cano

networks: test_network: driver: bridge - it looks like this with necessary indenting – Cano

Answer 2

In your docker-compose.yml add "extra_hosts" field like this:

version: "3.7"
  services:
    backend:
      build: .
      ports:
        - 8080:8080
      extra_hosts:
        - "pghost:172.18.0.1"

and in your code, you should use "pghost" to connect your postgresql server like this:

engine = db.create_engine('postgresql://postgres:postgres@pghost:5433/test'

Displace