Postgres setup problem

[korczas]

Problem

Hey guys! I have a problem with connecting to my PostgreSQL database from my backend service in a Docker Swarm setup. I have Traefik set up as a reverse proxy, and I'm using Docker Swarm for orchestration. The backend service is unable to connect to the PostgreSQL database, and I'm not sure why.

INFRA config

version: "3"

services:
  traefik:
    image: traefik:v3.1
    hostname: "{{.Node.Hostname}}"
    ports:
      # listen on host ports without ingress network
      - target: 80
        published: 80
        protocol: tcp
        mode: host
      - target: 443
        published: 443
        protocol: tcp
        mode: host
    networks:
      - proxy
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - letsencrypt:/letsencrypt
    command:
      - --api.dashboard=true
      - --log.level=INFO
      - --accesslog=true
      - --providers.swarm.exposedByDefault=false
      - --providers.swarm.network=proxy

      - --entrypoints.web.address=:80
      - --entrypoints.web.http.redirections.entrypoint.to=websecure
      - --entryPoints.web.http.redirections.entrypoint.scheme=https
      - --entrypoints.websecure.address=:443
      - --entrypoints.websecure.asDefault=true
      - --entrypoints.websecure.http.tls.certresolver=myresolver

      - --entrypoints.postgres.address=:5432

      - "--certificatesresolvers.myresolver.acme.dnschallenge=true"
      - "--certificatesresolvers.myresolver.acme.dnschallenge.provider=cloudflare"
      - "--certificatesresolvers.myresolver.acme.email=xxxxxx@gmail.com"
      - "--certificatesresolvers.myresolver.acme.storage=/letsencrypt/acme.json"
    deploy:
      mode: global
      placement:
        constraints:
          - node.role==manager
      labels:
        - traefik.enable=true
        - traefik.http.routers.mydashboard.rule=Host(`traefik.xxxxxx.dev`)
        - traefik.http.routers.mydashboard.service=api@internal
        - traefik.http.routers.mydashboard.middlewares=myauth
        - traefik.http.services.mydashboard.loadbalancer.server.port=1337

  whoami:
    image: traefik/whoami:v1.10
    hostname: "{{.Node.Hostname}}"
    networks:
      - proxy
    deploy:
      mode: global
      labels:
        - traefik.enable=true
        - traefik.http.routers.whoami.rule=Host(`whoami.xxxxxx.dev`)
        - traefik.http.services.whoami.loadbalancer.server.port=80
        - traefik.http.routers.whoami.tls.certresolver=myresolver

networks:
  proxy:
    name: proxy
    driver: overlay
    attachable: true

volumes:
  letsencrypt:
    name: letsencrypt

Backend config

version: "3.8"

services:
    skippy:
        image: xxxxxx/skippy:backend-latest
        hostname: "{{.Node.Hostname}}"
        networks:
            - proxy
        environment:
            - POSTGRES_HOST=db
            - POSTGRES_PORT=5432
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=password
            - POSTGRES_DB=skippy
            - POSTGRES_SSLMODE=disable
            - JWT_SECRET=development-secret
            - OPENAI_API_KEY=sk-dummy-key-for-development-only-replace-in-production
        depends_on:
            - db

        deploy:
            replicas: 1
            labels:
                - traefik.enable=true
                - traefik.http.routers.skippy.rule=Host(`skippy.xxxxxx.dev`)
                - traefik.http.routers.skippy.entrypoints=websecure
                - traefik.http.routers.skippy.tls.certresolver=myresolver
                - traefik.http.services.skippy.loadbalancer.server.port=3000
            # restart_policy:
            #     condition: on-failure
            # update_config:
            #     order: start-first

    db:
        image: postgres:latest
        networks:
            - proxy
        environment:
            - POSTGRES_DB=skippy
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=password
        volumes:
            - postgres-data:/var/lib/postgresql/data
        healthcheck:
            test: ["CMD-SHELL", "pg_isready -U postgres -d skippy"]
            interval: 10s
            timeout: 5s
            retries: 5
        deploy:
            replicas: 1
            labels:
                - traefik.enable=true
                - traefik.tcp.routers.db.rule=HostSNI(`*`)
                - traefik.tcp.routers.db.tls=true
                - traefik.tcp.routers.db.entryPoints=postgres
                - traefik.tcp.routers.db.service=postgres
                - traefik.tcp.services.db.loadbalancer.server.port=5432


volumes:
    postgres-data:

networks:
    proxy:
        external: true

[bluepuma77]

In general your config looks okay. You use Docker Swarm services, they are connected to a shared Docker network and you use the service name to connect, so the DB connection is not going through Traefik.

Is your postgres db up and running? Is the "Nest" log from "skippy"? Have you tried a ping from within the app container to "db"?

Note that - traefik.tcp.routers.db.tls=true will probably enable Traefik TLS with a custom TLS cert, that is not trusted by a client.

Note that depends_on does not work with Docker Swarm and docker stack deploy.

[korczas]

Is your postgres db up and running?

yes

Is the "Nest" log from "skippy"?

yes

Have you tried a ping from within the app container to "db"?

No. Not sure how to do this.

One thing that I found with chatGPT is that NestJS is trying to connect to 10.0.1.164, which is the same as in docker inspect skippy_db

but when I run docker network inspect proxy I see 10.0.1.165

[korczas]

Found the problem. I ahd to set endpoint_mode to dnsrr.