Connect to postgres in docker container from host machine

Question

How can I connect to postgres in docker from a host machine?

docker-compose.yml

version: '2'

networks:
    database:
        driver: bridge
services:
    app:
        build:
            context: .
            dockerfile: Application.Dockerfile
        env_file:
            - docker/Application/env_files/main.env
        ports:
            - "8060:80"
        networks:
           - database
        depends_on:
            - appdb

    appdb:
        image: postdock/postgres:1.9-postgres-extended95-repmgr32
        environment:
            POSTGRES_PASSWORD: app_pass
            POSTGRES_USER: www-data
            POSTGRES_DB: app_db
            CLUSTER_NODE_NETWORK_NAME: appdb
            NODE_ID: 1
            NODE_NAME: node1
        ports:
            - "5432:5432"
        networks:
            database:
                aliases:
                    - database

docker-compose ps

           Name                          Command               State               Ports
-----------------------------------------------------------------------------------------------------
appname_app_1     /bin/sh -c /app/start.sh         Up      0.0.0.0:8060->80/tcp
appname_appdb_1   docker-entrypoint.sh /usr/ ...   Up      22/tcp, 0.0.0.0:5432->5432/tcp

From container I can connect successfully. Both from app container and db container.

List of dbs and users from running psql inside container:

# psql -U postgres
psql (9.5.13)
Type "help" for help.

postgres=# \du
                                       List of roles
    Role name     |                         Attributes                         | Member of
------------------+------------------------------------------------------------+-----------
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replication_user | Superuser, Create role, Create DB, Replication             | {}
 www-data         | Superuser                                                  | {}

postgres=# \l
                                       List of databases
      Name      |      Owner       | Encoding |  Collate   |   Ctype    |   Access privileges
----------------+------------------+----------+------------+------------+-----------------------
 app_db         | postgres         | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres       | postgres         | UTF8     | en_US.utf8 | en_US.utf8 |
 replication_db | replication_user | UTF8     | en_US.utf8 | en_US.utf8 |
 template0      | postgres         | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                |                  |          |            |            | postgres=CTc/postgres
 template1      | postgres         | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                |                  |          |            |            | postgres=CTc/postgres
(5 rows)

DB image is not official postgres image. But Dockerfile in GitHub seem looking fine.

cat /var/lib/postgresql/data/pg_hba.conf from DB container:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

host all all all md5
host replication replication_user 0.0.0.0/0 md5

I tried both users with no luck

$ psql -U postgres -h localhost
psql: FATAL:  role "postgres" does not exist
$ psql -h localhost -U www-data appdb -W
Password for user www-data:
psql: FATAL:  role "www-data" does not exist

Looks like on my host machine there is already PSQL running on that port. How can I check it?

How can you check if in your host machine there is already postgresql running on the same port? Why don't you stop the containers and run psql? — ikusimakusi, Jun 26 '18 at 20:42

score 17 · Answer 1 · answered Jun 28 '18 at 09:29

17

I believe the problem is you have postgres running on the local machine at port 5432. Issue can be resolved by mapping port 5432 of docker container to another port in the host machine. This can be achieved by making a change in docker-compose.yml

Change

"5432:5432"

to

"5433:5432"

Restart docker-compose

Now the docker container postgres is running on 5433. (Locally installed postgres is on 5432) You can try connecting to the docker container.

psql -p 5433 -d db_name -U user -h localhost

answered Jun 28 '18 at 09:29

Sreeragh A R

2,871
3
27
54

1

Thank you! I had the identical problem as the original question. Your answer worked for me! – Philip Mar 17 '19 at 03:06
1

Thank you. You inspire me. But for my case, to make it work, I have to specify host for psql as `0.0.0.0`. Something like, `psql -p 5433 -h 0.0.0.0 -U postgres` – worrawut May 11 '21 at 16:11
I can confirm that usually, just changing the first of the two port numbers is the whole trick because of a port clash since `5432` is the default port used by a local installation. The first of the two ports is for the Docker host_port on Linux. If that is already occupied, the port cannot be used to reach the host. The same trick works in docker-compose, see [Connect to dockerized postgres from Windows Docker host?](https://stackoverflow.com/a/68929090/11154841) and [https://stackoverflow.com/a/55259785/11154841](https://stackoverflow.com/a/55259785/11154841). – questionto42 Aug 26 '21 at 13:43

score 3 · Answer 2 · edited Jun 21 '18 at 19:51

I ran this on Ubuntu 16.04

$ psql -h localhost -U www-data app_db
Password for user www-data:
psql (9.5.13)
Type "help" for help.

app_db=# \du
                                       List of roles
    Role name     |                         Attributes                         | Member of
------------------+------------------------------------------------------------+-----------
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replication_user | Superuser, Create role, Create DB, Replication             | {}
 www-data         | Superuser                                                  | {}

And below from my mac to the VM inside which docker was running (192.168.33.100 is the IP address of the docker VM)

$ psql -h 192.168.33.100 -U www-data app_db
Password for user www-data:
psql (9.6.9, server 9.5.13)
Type "help" for help.

app_db=# \du
                                       List of roles
    Role name     |                         Attributes                         | Member of
------------------+------------------------------------------------------------+-----------
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replication_user | Superuser, Create role, Create DB, Replication             | {}
 www-data         | Superuser                                                  | {}

They both work for me.

PSQL version on VM

$ psql --version
psql (PostgreSQL) 9.5.13

PSQL version on Mac

$ psql --version
psql (PostgreSQL) 9.6.9

that's not working for me. Looks like there is already PSQL running on my host machine on that port. How can I check it? — mef_, Jun 25 '18 at 04:02
To check used ports, use one of the following commands `lsof -i -P -n | grep 5432` or `ss -tulwn | grep 5432` or `netstat -tulpn | grep 5432`. It's advised that to use `sudo` with these commands, and I believe, the last two commands are not available on Mac. — Esmaeil MIRZAEE, Sep 03 '21 at 07:07

score 2 · Answer 3 · answered Jan 06 '19 at 04:27

2

I had the same problem on elementary OS 5.0 while running postgres 10 official docker container though. I was able to connect from host using the ip of the running container.

sudo docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' container_name/id

Test after getting the ip: psql -h 172.17.0.2 -U postgres

answered Jan 06 '19 at 04:27

Bogdan Costea

49
1
3

A comment mentions they're running on OSX; the `docker inspect` IP addresses are inaccessible on MacOS hosts (among other places) and aren't necessary on any platform. – David Maze Mar 11 '22 at 01:54

score 1 · Answer 4 · edited Jun 28 '18 at 07:19

1

I believe you have an issue in pg_hba.conf. Here you've specified 1 host that has access - 127.0.0.1/32.

You can change it to this:

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

This will make sure your host (totally different IP) can connect.

To check if there is an instance of postgresql already running, you can do netstat -plnt | grep 5432. If you get any result from this you can get the PID and verify the process itself.

edited Jun 28 '18 at 07:19

Hexaholic

3,299
7
30
39

answered Jun 28 '18 at 07:07

wadhg

21
4

Thanks for the `netstat | grep 5432` tip. I used that to find indeed I already had postgres running locally – Philip Mar 17 '19 at 03:05

antislice · Answer 5 · 2018-06-25T18:23:03.417

0

I have a relatively similar setup, and the following works for me to open a psql session on the host machine into the docker postgres instance: docker-compose run --rm db psql -h db -U postgres -d app_development

Where:

db is the name of the container
postgres is the name of the user
app_development is the name of the database

So for you, it would look like docker-compose run --rm appdb psql -h appdb -U www-data -d app_db.

edited Jun 25 '18 at 18:23

answered Jun 21 '18 at 18:12

antislice

179
1
11

I need to have access from host machine – mef_ Jun 25 '18 at 04:01
@mef_ I updated my answer to be more specific about what the command does. If you try it, what specifically doesn't work? – antislice Jun 25 '18 at 18:24

meshde · Answer 6 · 2018-06-26T02:35:48.053

Since you’re running it in OSX, you can always use the pre-installed Network Utility app to run a Port Scan on your host and identify if the postgres server is running (and if yes, on which port).

But I don’t think you have one running on your host. The problem is that Postgres by default runs on 5432 and the docker-compose file that you are trying to run exposes the db container on the same port i.e. 5432. If the Postgres server were already running on your host, then Docker would have tried to expose a a container to a port which is already being used, thereby giving an error.

Another potential solution:
As can be seen in this answer, mysql opens a unix socket with localhost and not a tcp socket. Maybe something similar is happening here.

Try using 127.0.0.1 instead of localhost while connecting to the server in the container.

Connect to postgres in docker container from host machine

6 Answers6