Docker container for Postgres 9.1 not exposing port 5432 to host

Question

I'm trying to use a Docker container to run a PostgreSQL server, and connect with it from my host machine.

My configuration is:

• Host machine: Mac OS X 10.10.5
• Docker 1.10.1

I've done this:

Step 1: create a volume for permanent postgres data

docker volume create --name postgres_data

Step 2: Start the postgres instance

UPDATE: As suggested in comments, I specified port mapping when running the container

docker run --name my_postgres_container -e POSTGRES_PASSWORD=my_password -v postgres_data:/var/lib/postgresql/data -p 5432:5432 -d postgres:9.1

Step 3: connect to Docker instance by doing this:

docker run -it --link my_postgres_container:postgres --rm postgres:9.1 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

But I want to connect to that instance just by:

psql -h localhost -p 5432 -U postgres

Like if I had installed Postgres locally in my host machine.

The problem is port 5432 is not exposed. So, I can't connect with it:

sudo lsof -i -P | grep -i "listen" --> no port 5432 open

UPDATE 2: Even stranger. I've also done this:

Stop Docker. Then, run a normal PostgreSQL 9.4.4 instance in my host machine (no docker involved here, just postgres running in my Mac OS X host, listening on port 5432). Everything is normal:

sudo lsof -i -P | grep -i "postgres"

postgres  14100          jorge    6u  IPv4 0x780274158cebef01      0t0  TCP localhost:5432 (LISTEN)

I can connect with my local postgres instance without any problem (look the output of the command: is the postgres compiled for Mac OS X, my host):

psql -h localhost -U postgres -c "select version()"
                                                                version
---------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-apple-darwin14.3.0, compiled by Apple LLVM version 6.1.0 (clang-602.0.53) (based on LLVM 3.6.0svn), 64-bit
(1 row)

Now the fun part. I start my Docker instance again, while the host PostgreSQL instance is running.

It starts! (and it shouldn't). I can even connect using docker run...

docker run -it --link my_postgres_instance:postgres --rm postgres:9.1 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

If I run select version() now, it shows postgres running inside my docker instance at the same time postgres is running in my host, out of docker, using the same 5432 port. (Look at the ouput, is postgres compiled for Debian, the OS inside the postgres:9.1 container)

postgres=# select version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.20 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

Why?

Does it make sense? My final goal is to run a Django app in another Docker container and connect with my Postgres instance. How could I do that?

Answer 1

Your docker host is a virtual machine, which has it's own IP adddres. You can detect this IP address by entering the following command:

docker-machine ip

The answer will be something like 192.168.99.100

When you have mapped the ports using the -p 5432:5432 switch, you will be able to connect to postgres with any tool from your dev machine using the IP address mentioned.

Answer 2

Run the postgre image with the correct Port Mapping using -p <host_port>:<container_port>:

docker run --same-options-as-step-one -d -p 5432:5432 postgres:9.1

Answer 3

I was able to connect using container IP or host IP, except localhost (127.0.0.1).

To get container id run

docker ps

Find required container id and run

docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <container_id>

Port must be exposed.

Here is an example of docker-compose.yml which starts two containers postgres and adminer, which is database management tool you can use to connect to postgres:

version: '3'
services:
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
  postgres:
    image: postgres:11.4-alpine
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres

Answer 4

I had a similar issue. My problem was simply 0.0.0.0 not mapping to localhost so I just had to add to psql

psql --host=0.0.0.0

This is presuming

docker port <container name>

outputs

5432/tcp -> 0.0.0.0:5432

Answer 5

Other answers work, but don't explain why they work.

Given the command:

psql -h localhost -p 5432:5432 -U postgres

localhost is actually a special value that tells psql to look for a unix socket connection, instead of going over TCP. We can't use unix sockets to connect to docker services.

Changing the command like so fixes it, by forcing TCP:

psql -h 127.0.0.1 -p 5432:5432 -U postgres

That will work as long as you docker run ... -p 5432:5432 .... Specifying the IP returned by docker-machine ip also forces TCP, so that also works.

Answer 6

I had a similar problem working in a VMWare virtual machine with Lubuntu. The VM had been paused and then was restarted. The PostgreSQL Docker container was correctly mapped and listening on localhost:5432, but I always got:

psql: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Restarting the VM solved the problem in my case.

Answer 7

Try this to install postgresql

 docker run --name postgres -d -p 5432:5432 -e POSTGRES_PASSWORD=fred postgres:alpine

Or Change port host machine Heree (mac)

 docker run --name postgres -d -p 5436:5432 -e POSTGRES_PASSWORD=fred postgres:alpine

Tip: Install pgadmin4

docker run -p 5050:80  -e "[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=admin"  -d dpage/pgadmin4