Trouble connecting to Docker Postgres DB from host system

[phlbnks]

I'm having trouble connecting to the Postgres server from the host system. I want to look under the hood at the raw database data.

docker ps shows me that the Postgres container is listening on port 5432
sudo docker exec -it plausible_plausible_db_1 cat /var/lib/postgresql/data/postgresql.conf shows me Postgres should be listening on all interfaces (listen_addresses = '*')

But I can't get the command to connect to it right. I've tried:

• psql postgres:https://postgres:postgres@plausible_plausible_db_1:5432/plausible_db returns "could not translate host name "plausible_plausible_db_1" to address: Name or service not known"
• sudo psql -h localhost returns "Connection refused \n Is the server running on host "localhost" (127.0.0.1) ..."

I have the feeling I'm making a rookie Docker error - or does the config need changing to allow this?

[ruslandoga]

👋 @phlbnks

In the default setup PostgreSQL runs in an isolated network namespace so it's not accessible from host.

However, it doesn't stop you from accessing it and there are several ways to do it:

1. (The easiest) Using docker compose exec to "enter" the container

$  cd hosting
$ docker compose exec plausible_db ash
/ # psql -U postgres
psql (14.7)
Type "help" for help.

postgres=# \l
                                  List of databases
     Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
--------------+----------+----------+------------+------------+-----------------------
 plausible_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 rows)

postgres=# \c plausible_db
You are now connected to database "plausible_db" as user "postgres".
plausible_db=#

[ruslandoga]

2. (This one is a bad idea) Modifying docker-compose.yml to make the PostgreSQL container be available from the host:

services:
  plausible_db:
    image: postgres:14-alpine
+   network_mode: host
    ...etc...

and restarting that container:

$ docker compose stop plausible plausible_db
$ docker compose rm plausible_db
$ docker compose up -d

Now you should be able to connect from the host. But note that plausible wouldn't be able to see it anymore at the old URL (and the app is now broken).

[ruslandoga]

3. Same as 2 but adding port mapping instead:

services:
  plausible_db:
    image: postgres:14-alpine
+   ports:
+     - 5432:5432
    ...etc...

And connecting to it from the host

$ psql -h localhost -p 5432 -U postgres

[ruslandoga]

4. Linking another container

$ docker run -it --link hosting-plausible_db-1:postgres --rm --network hosting_default postgres psql -h postgres -U postgres
Password for user postgres: <-- (it's "postgres")
psql (15.3 (Debian 15.3-1.pgdg120+1), server 14.7)
Type "help" for help.

postgres=#

[phlbnks]

Thanks for the detailed and speedy answer @ruslandoga!

Annoyingly I tried port mapping and it didn't work. But now it does... I think I got caught out by not using docker compose down --remove-orphans. It seems like something in the network config didn't/doesn't update without doing that 🤷‍♂️

FWIW / anyone searching - the same technique also works to expose port 9000 of the events/Clickhouse database.