Connect to postgres in docker container from host machine

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?

5 Answers

Answers 1

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 

Answers 2

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.

Answers 3

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.

Answers 4

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.

Answers 5

I believe the problem is you have postgres running on the local machine at port 5432. Issue can be resolved by mapping port 542 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" 

Now the docker container postgres is running on 5433. You can try connecting to it.

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

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati