Allow docker container to connect to a local/host postgres database
I've recently been playing around with Docker and QGIS and have installed a container following the instructions in this tutorial.
Everything works great, although I am unable to connect to a localhost postgres database that contains all my GIS data. I figure this is because my postgres database is not configured to accept remote connections and have been editing the postgres conf files to allow remote connections using the instructions in this article.
I'm still getting an error message when I try and connect to my database running QGIS in Docker: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections to port 5433? The postgres server is running, and I've edited my pg_hba.conf file to allow connections from a range of IP addresses (172.17.0.0/32). I had previously queried the IP address of the docker container using docker ps and although the IP address changes, it has so far always been in the range 172.17.0.x
Any ideas why I can't connect to this database? Probably something very simple I imagine!
I'm running Ubuntu 14.04; Postgres 9.3
- Use 172.17.0.0/16 as IP address range, not 172.17.0.0/32.
- Don't use localhost to connect to the PostgreSQL database on your host, but the host's IP instead. To keep the container portable, start the container with the --add-host=database:<host-ip> flag and use database as hostname for connecting to PostgreSQL.
- Make sure PostreSQL is configured to listen for connections on all IP addresses, not just on localhost. Look for the setting listen_addresses in PostgreSQL's configuration file, typically found in /etc/postgresql/9.3/main/postgresql.conf (credits to @DazmoNorton).
172.17.0.0/32 is not a range of IP addresses, but a single address (namly 172.17.0.0). No Docker container will ever get that address assigned, because it's the network address of the Docker bridge (docker0) interface.
When Docker starts, it will create a new bridge network interface, that you can easily see when calling ip a:
$ ip a ... 3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN link/ether 56:84:7a:fe:97:99 brd ff:ff:ff:ff:ff:ff inet 172.17.42.1/16 scope global docker0 valid_lft forever preferred_lft forever
As you can see, in my case, the docker0 interface has the IP address 172.17.42.1 with a netmask of /16 (or 255.255.0.0). This means that the network address is 172.17.0.0/16.
The IP address is randomly assigned, but without any additional configuration, it will always be in the 172.17.0.0/16 network. For each Docker container, a random address from that range will be assigned.
This means, if you want to grant access from all possible containers to your database, use 172.17.0.0/16.
Docker for Mac solution
Thanks to @Birchlabs' comment, now it is tons easier with this special Mac-only DNS name available:
docker run -e DB_PORT=5432 -e DB_HOST=docker.for.mac.host.internal
From 17.12.0-cd-mac46, docker.for.mac.host.internal should be used instead of docker.for.mac.localhost. See release note for details.
@helmbert's answer well explains the issue. But Docker for Mac does not expose the bridge network, so I had to do this trick to workaround the limitation:
$ sudo ifconfig lo0 alias 10.200.10.1/24
Open /usr/local/var/postgres/pg_hba.conf and add this line:
host all all 10.200.10.1/24 trust
Open /usr/local/var/postgres/postgresql.conf and edit change listen_addresses:
listen_addresses = '*'
Reload service and launch your container:
$ PGDATA=/usr/local/var/postgres pg_ctl reload $ docker run -e DB_PORT=5432 -e DB_HOST=10.200.10.1 my_app
What this workaround does is basically same with @helmbert's answer, but uses an IP address that is attached to lo0 instead of docker0 network interface.
Simple Solution for mac:
The newest version of docker (18.03) offers a built in port forwarding solution. Inside your docker container simply have the db host set to host.docker.internal. This will be forwarded to the host the docker container is running on.
Documentation for this is here: https://docs.docker.com/docker-for-mac/networking/#per-container-ip-addressing-is-not-possible
Just add --network=host to docker run. That's all!
This way container will use the host's network, so localhost and 127.0.0.1 will point to the host (by default they point to a container). Example:
docker run -d --network=host \ -e "DB_DBNAME=your_db" \ -e "DB_PORT=5432" \ -e "DB_USER=your_db_user" \ -e "DB_PASS=your_db_password" \ -e "DB_HOST=127.0.0.1" \ --name foobar foo/bar
One more thing needed for my setup was to add
so that Docker would point to 172.17.0.1 as the DB hostname, and not rely on a changing outer ip to find the DB. Hope this helps someone else with this issue!
First You have to check that is the Docker Database port is Available in your system by following command -
sudo iptables -L -n
Chain DOCKER (1 references) target prot opt source destination ACCEPT tcp -- 0.0.0.0/0 172.17.0.2 tcp dpt:3306 ACCEPT tcp -- 0.0.0.0/0 172.17.0.3 tcp dpt:80 ACCEPT tcp -- 0.0.0.0/0 172.17.0.3 tcp dpt:22
Here 3306 is used as Docker Database Port on 172.17.0.2 IP, If this port is not available Run the following command -
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
Now, You can easily access the Docker Database from your local system by following configuration
host: 172.17.0.2 adapter: mysql database: DATABASE_NAME port: 3307 username: DATABASE_USER password: DATABASE_PASSWORD encoding: utf8
First You have to check that is the Docker Database port is Available in your firewall by following command -
sudo firewall-cmd --list-all
target: default icmp-block-inversion: no interfaces: eno79841677 sources: services: dhcpv6-client ssh **ports: 3307/tcp** protocols: masquerade: no forward-ports: sourceports: icmp-blocks: rich rules:
Here 3307 is used as Docker Database Port on 172.17.0.2 IP, If this port is not available Run the following command -
sudo firewall-cmd --zone=public --add-port=3307/tcp
In server, You can add the port permanently
sudo firewall-cmd --permanent --add-port=3307/tcp sudo firewall-cmd --reload
Now, You can easily access the Docker Database from your local system by the above configuration.
The another solution is service volume, You can define a service volume and mount host's PostgreSQL Data directory in that volume. Check out the given compose file for details.
version: '2' services: db: image: postgres:9.6.1 volumes: - "/var/lib/postgresql/data:/var/lib/postgresql/data" ports: - "5432:5432"
By doing this, another PostgreSQL service will run under container but uses same data directory which host PostgreSQL service is using.