Allow docker container to connect to a local host postgres database

Question

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

User · Accepted Answer

TL DR  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  lt host-ip gt  flag and use database as hostname for connecting to PostgreSQL  Make sure PostgreSQL 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    Long version 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   lt NO-CARRIER BROADCAST MULTICAST UP gt  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

User · Answer

for docker-compose you can try just add   network mode   host    example     version   2  services    feedx      build  web     ports      -  127 0 0 1 8000 8000      network mode   host    https   docs docker com compose compose-file  network mode

User · Answer

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

User · Answer

Simple solution  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

User · Answer

Docker for Mac solution  17 06 onwards  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   Older version   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

User · Answer

In Ubuntu   First You have to check that is the Docker Database port is Available in your system by following command -  sudo iptables -L -n   Sample OUTPUT   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   In CentOS   First You have to check that is the Docker Database port is Available in your firewall by following command -  sudo firewall-cmd --list-all   Sample OUTPUT     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

User · Answer

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  i-want-to-connect-from-a-container-to-a-service-on-the-host

User · Answer

The solution posted here does not work for me  Therefore  I am posting this answer to help someone facing similar issue   OS  Ubuntu 18 PostgreSQL  9 5  Hosted on Ubuntu  Docker  Server Application  which connects to PostgreSQL   I am using docker-compose yml to build application    STEP 1  Please add host docker internal  lt docker0 IP gt   version   3  services    bank-server              depends on                 restart  on-failure     ports        - 9090 9090     extra hosts        -  host docker internal 172 17 0 1    To find IP of docker i e  172 17 0 1  in my case  you can use     gt  ifconfig docker0 docker0  flags 4099 lt UP BROADCAST MULTICAST gt   mtu 1500         inet 172 17 0 1  netmask 255 255 0 0  broadcast 172 17 255 255   OR    gt  ip a 1  docker0   lt NO-CARRIER BROADCAST MULTICAST UP gt  mtu 1500 qdisc noqueue state DOWN group default     inet 172 17 0 1 16 brd 172 17 255 255 scope global docker0        valid lft forever preferred lft forever   STEP 2  In postgresql conf  change listen addresses to listen addresses        STEP 3  In pg hba conf  add this line  host    all             all             0 0 0 0 0               md5   STEP 4  Now restart postgresql service using  sudo service postgresql restart  STEP 5  Please use host docker internal hostname to connect database from Server Application  Ex  jdbc postgresql   host docker internal 5432 bankDB  Enjoy

User · Answer

One more thing needed for my setup was to add  172 17 0 1  localhost   to  etc hosts  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

User · Answer

To set up something simple that allows a Postgresql connection from the docker container to my localhost I used this in postgresql conf   listen addresses         And added this pg hba conf   host    all             all             172 17 0 0 16           password   Then do a restart  My client from the docker container  which was at 172 17 0 2  could then connect to Postgresql running on my localhost using host password  database  username and password

[ubuntu] Allow docker container to connect to a local/host postgres database

TL;DR

Long version

Examples related to ubuntu

Examples related to docker

Examples related to postgresql-9.3

Examples related to qgis