Connect to dockerized postgres from Windows Docker host

Question

my setup is Docker for Windows (Version 18.09.2) on a Windows 10 Pro machine with installed postgres (not a database, only the command line tools).

I run the official postgres image from docker hub with

docker run -it -v D:/postgres:/home/dump --name some_postgres -e POSTGRES_PASSWORD=root -d postgres

Afterwards I am able to connect via

docker exec -it <docker-id> bash

and run

psql -U postgres

Everything works fine. Now I want to connect from my host. I call

psql -h <local-ip> -U postgres

and got

psql: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "192.168.116.74" and accepting
        TCP/IP connections on port 5432?

I am pretty sure, the database could be accessed. Because if I change the IP I receive

psql: could not connect to server: Connection timed out (0x0000274C/10060)
        Is the server running on host "192.168.116.11" and accepting
        TCP/IP connections on port 5432?

Has anyone any clue how I can fix this issue? Or what I am doing wrong?

Answer 1

It took me quite some time to find out how to enter the container's databases from a normal psql prompt on Windows. This was due to an additional local Windows installation.

---

Creating a Docker container (Linux) without a port clash at 5432

The structure of the port parameter (both in docker run and in docker-compose) is:

<docker_host_port_on_linux>:<docker_container_port_on_linux>

See Connecting to Postgresql in a docker container from outside. The <host_port> is the port that you can find on Windows to connect to the container's port. And the core trick that seems to be needed here is to avoid a port clash which is in another answer of the same thread.

If you take 5432:5432, this might conflict with a local installation of postgres on Windows which uses port 5432 as a standard. You can find out about such a conflict by opening psql, logging in with the menu (in the standard test phase, you will probably just need to press Enter at any menu point) and then print all available databases with \l. If these are the databases of your local Windows installation, you know that you have to use another port when using docker.

If there is a conflict between the ports, use a new port for the Docker host, either with parameter

-p 5433:5432

or when using docker-compose, the file needs to have:

ports:
    - "5433:5432"

It is not relevant whether you start in -d detached mode or not:

`docker-compose up -d`

or:

`docker-compose up`

With the latter, you will just see every change directly in the container log.

Check that your container is started:

docker ps -a

If it is not started, start with:

docker container start CONTAINER_NAME

The container PORTS attribute will look as follows:

0.0.0.0:5433->5432/tcp, :::5433->5432/tcp

That means: the container uses port 5433 for the Docker host (Linux) which can then find the Docker container (Linux) at port 5432.

Use psql on Windows to connect to the Docker container

After this, you can open psql on Windows, in the easy test phase you will usually just need to press enter for every menu point except for the port where you enter 5433 to connect to the Docker host (Linux):

Server [localhost]:
Database [postgres]:
Port [5432]: 5433
Username [postgres]:
Passwort für Benutzer postgres:
psql (13.3, Server 10.3)
Warnung: Konsolencodeseite (850) unterscheidet sich von der Windows-
         Codeseite (1252). 8-Bit-Zeichen funktionieren möglicherweise nicht
         richtig. Einzelheiten finden Sie auf der psql-Handbuchseite unter
         »Notes for Windows users«.
Geben Sie »help« für Hilfe ein.

postgres=#

and then you will be in the postgres shell and \l will show that you are in the container's postgres and not in the Windows postgres, since it will have the databases of the container. In my case, I added the database db with the docker-compose file, and there it is:

postgres=# \l
                                 Liste der Datenbanken
   Name    | Eigent³mer | Kodierung | Sortierfolge | Zeichentyp |  Zugriffsprivilegien
-----------+------------+-----------+--------------+------------+-----------------------
 db        | postgres   | UTF8      | en_US.utf8   | en_US.utf8 |
 postgres  | postgres   | 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
(4 Zeilen)

While I had different databases on my Windows local PostgreSQL installation.

For example, you could now connect to the db database:

\c db

Create an empty table:

CREATE TABLE "test" (

);

and show the tables:

\dt

db=# create table test (); CREATE TABLE db=# \dt Liste der Relationen Schema | Name | Typ | Eigent³mer --------+----------------+---------+------------ public | test | Tabelle | db (1 Zeile)

Use psql on the Linux container to check the new table

At the same time, this table will be available in the container since you have now changed the container from outside. In the normal Linux terminal of your WSL, run:

docker exec -it CONTAINER_ID_OR_NAME psql -U postgres -W -d db

Which leads to:

Password for user postgres:
psql (10.3)
Type "help" for help.

db=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | test           | table | postgres
(1 row)

Sidenote about -W

By the way, using -W seems recommended as the PostgreSQL docs say about it:

Force psql to prompt for a password before connecting to a database.

This option is never essential, since psql will automatically prompt for a password if the server demands password authentication. However, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

-W does not expect a value after it, that is, the plain text password does not follow the -W. Instead, it just shows that the user will have to enter a password to connect, which is avoiding one useless connection attempt.

Strangely, at the password prompt, I could also enter a wrong password or none at all and still enter the database.

Answer 2

To connect to a container from your host you have to publish ports to your host. That is done using port publishing and -p option :

docker run -it -v D:/postgres:/home/dump --name some_postgres -p 5432:5432 -e POSTGRES_PASSWORD=root -d postgres

Notice the -p option. Now when you will be trying to connect to port 5432 on your localhost, the traffic will be redirected to the port 5432 of the container.