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.
