How to connect to a database running in docker in a digital ocean droplet remotely?

Question

I have airflow and postgres running in a docker container that I have hosted on a digital ocean droplet. Airflow is running successfully and writing data to my postgres database. When I ssh into my digital ocean droplet, I can docker exec -it <mycontainer> bash and from there I can run psql and query my database. I know that it is up and running by inspecting the container, and I have exposed port 5433. I know that there aren't any firewall issues, because I am able to access the airflow webserver/UI from my machine just by going to the droplet ID address and the correct port.

What I would like to do is access the database from my local machine to be able to run queries and do analysis. However, I can not find a way to connect to the database. Ideally I would connect while using a SQL editor (DBeaver).

I have spent 4 hours trying to get the connection to work and I have exhausted all my resources (ChatGPT was unhelpful!). I was able to successfully create a ssh tunnel in DBeaver, but I still wasn't able to get the connection. I get this error, which I don't understand.

The connection attempt failed.
  EOFException
  java.io.EOFException

I have also gotten the Connection Refused error message before. That happens when I do not use the ssh tunnel. When I inspect my docker container running on the server, the IPAddress is listed as "172.20.0.2". I have exposed port 5433 in the docker-compose file. I am trying to find the correct connection string (after successfully ssh tunneling).

username: postgres
password: mypassword
database: postgres
port: 5433
host: ? (I haveve tried "localhost" and "172.20.0.2")

Anyone able to save my sanity?

TL;DR Trying to connect to a postgres db running in a docker container on a digital ocean droplet. Container and Postgres is running (I am able to connect to the container and run queries with psql). I also know there aren't any firewall issues, I am able to connect to the airflow dashboard running in a container in the same droplet.

Edit: Here is my docker-compose file for the postgres container

version: '3.8'
services:
  jupyter_notebook:
    image: "jupyter/minimal-notebook"
    container_name: ${CONTAINER_NAME:-jupyter_notebook}
    environment:
      JUPYTER_ENABLE_LAB: "yes"
    ports:
      - "8888:8888"
    volumes:
      - ${PWD}:/home/jovyan/work
    depends_on:
      - db
    links:
      - db
    networks:
      - adu_network
  db:
    image: postgres:15.2
    restart: always
    environment:
      - "POSTGRES_USER=${POSTGRES_USER}"
      - "POSTGRES_PASSWORD=${POSTGRES_PASSWORD}"
      - "POSTGRES_PORT=${POSTGRES_PORT}"
      - "POSTGRES_HOST=${POSTGRES_HOST}"
    ports:
      - "5433:5433"
    expose:
      - "5433"
    networks:
      - adu_network
    volumes: 
      - ./data:/var/lib/postgresql/data
      - ./adu_db.sql:/docker-entrypoint-initdb.d/init.sql
volumes:
  db:
    driver: local
  
networks:
  adu_network: null 

Answer 1

I set up a simplified version of your docker-compose, and installed DBeaver to test. I initially got the same error as you.

My docker-compose.yml:

version: '3.8'
services:
  db:
    image: postgres:15.2
    environment:
      - POSTGRES_PASSWORD=secret
      - POSTGRES_PORT=5433
    ports:
      - "5433:5433"

The container starts up fine.

On my local machine, I have "Use SSH-Tunnel" ticked on the "SSH" panel in DBeaver, with my SSH connection details there. The Test tunnel configuration button on that pannel shows the ssh connection works fine.

On the DBeaver "Main" config panel I have:

• Host: localhost
• Port: 5433
• Database: postgres
• Username: postgres
• Password: secret

Test Connection ... in DBeaver shows:

The connection attempt failed.
EOFException.
java.io.EOFException

OK so let's investigate. I checked the Docker logs, which show:

... [1] LOG:  starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
... [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
... [1] LOG:  listening on IPv6 address "::", port 5432
... [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

Note port 5432 appears a few times, despite using POSTGRES_PORT=5433 in my compose file. Strange, but OK. I updated my compose file (removed POSTGRES_PORT specification, and updated ports):

version: '3.8'
services:
  db:
    image: postgres:15.2
    environment:
      - POSTGRES_PASSWORD=secret
    ports:
      - "5432:5432"

I changed the Port in DBeaver's Main panel to 5432, and now Test Connection ... shows success!

I'm not familiar with Postgres in Docker, but the docs don't mention anything about POSTGRES_PORT being available to change the port. Some searching turns up a way to change the port using command in your compose file, though I did not test this:

command: -p 5433

Alternatively you could map the default running port of 5432 in the container to 5433 on the host:

ports:
  - "5433:5432"

But unless there's some special reason you don't want it on the default, maybe the easiest is to just not fight it! :-).

Likewise there is no mention of POSTGRES_HOST in the docs, but maybe you're using that to so you can reference the DB container from other containers? If so, you can already do that - containers in docker-compose.yml can reference each other by the container name. Your Postgres container is named db, and you can use that to reach it from your jupyter_notebook container. So unless your POSTGRES_HOST env var is "db", that isn't working as you expected, and it if is, you can remove it anyway. See eg Docker-compose Postgres connection refused

One last note - all containers in a docker-compose.yml are automatically added to a common network, and can communicate on that network. Unless you're trying to restrict or customise networking, or there's more of your yml not shown here with special network requirements, there's no need to specify adu_network or any network details.

Answer 2

postgres image Environment Variables doc doesn't mention POSTGRES_PORT nor POSTGRES_HOST variables. Your host/port configuration does nothing and Postgres still uses port 5432 on startup, hence your issue using port 5433

  db:
    image: postgres:15.2
    environment:
      - "POSTGRES_PORT=${POSTGRES_PORT}" # Ignored by container
      - "POSTGRES_HOST=${POSTGRES_HOST}" # Ignored by container
    ports:
      - "5433:5433" # Wrong port

Use container port 5432 instead of 5433 and remove these unused variables (you can still expose host port 5433 though), for example:

  db:
    image: postgres:15.2
    environment:
      # Remove POSTGRES_PORT / POSTGRES_HOST, you can keep the rest
    ports:
      # Bind host port 5433 to container port 5432
      - "5433:5432"

Now you can reach Postgres container directly via 172.20.0.2:5433, which will map to your container's port 5432.

---

Note: I'd actively recommend not to override directly Postgres startup config to use another host/port as it will only complexify your setup without adding any value.

Such options are not part of the image's interface for a good reason: you don't need them as the container has it's own network interface and ports, overriding such config is of no use.