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?

Answer 1

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

Answer 2

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