Access Postgresql instance with subdomain using traefik

Traefik Traefik v2
2

Hello @aristos

Here is an example docker-compose that will setup Postgres, pgadmin and Traefik. The Postgres container listens by the default on port 5432, I'm also setting up a TCP entrypoint and router in Traefik on the same port.

#Traefik static configuration /sandbox/configs/static/proxy-v2.yml
 api:
  dashboard: true
  debug: true

entryPoints:
  web:
    address: ":80"

  websecure:
    address: ":443"

  tcp:
    address: ":5432"

providers:
  docker: {}
  #file:
  #  filename: /dynamic.yml
  #  watch: true

version: '3'
services:
    postgres:    
      image: postgres
      labels:
        - "traefik.enable=true"
        - "traefik.tcp.routers.postgres.rule=HostSNI(`*`)"
        - "traefik.tcp.routers.postgres.entrypoints=tcp"
        - "traefik.tcp.services.postgres.loadbalancer.server.port=5432"
      environment:
        POSTGRES_USER: ${POSTGRES_USER:-postgres}
        POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
        PGDATA: /data/postgres
      volumes:
        - postgres:/data/postgres
      networks:
        - traefik

    pgadmin:
      image: dpage/pgadmin4
      labels:
        - "traefik.enable=true"
        - "traefik.http.routers.pgadmin.entrypoints=web"
        - "traefik.http.routers.pgadmin.rule=Host(`pgadmin.docker.localhost`)"
        - "traefik.http.services.pgadmin.loadbalancer.server.port=80"
      environment:
        PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
        PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
        PGADMIN_CONFIG_SERVER_MODE: 'False'
      volumes:
        - pgadmin:/var/lib/pgadmin
      networks:
        - traefik

    traefik:
      image: "traefik:v2.8"
      command:
        - "--log.level=DEBUG"
        - "--configfile=/traefik.yml"
      labels:
        - "traefik.enable=true"
        - "traefik.http.routers.api.entrypoints=web"
        - "traefik.http.routers.api.rule=Host(`traefik.docker.localhost`)"
        - "traefik.http.routers.api.service=api@internal"
      ports:
        - "80:80"
        - "443:443"
        - "5432:5432"
      networks:
        - traefik
      volumes:
        - /var/run/docker.sock:/var/run/docker.sock:ro
        - /sandbox/configs/static/proxy-v2.yml:/traefik.yml
        #- /sandbox/configs/file-provider.yml:/dynamic.yml

networks:
  traefik:

volumes:
    postgres:
    pgadmin:

Traefik Dashboard would be accessible at traefik.docker.localhost, given you have that alias configured in Docker.

pgadmin is at pgadmin.docker.localhost and from its own Dashboard you can setup a connection to Postgres directly, by using the address postgres or through Traefik, by using traefik as the server address this time. It works because the rule on the tcp entrypoint is set to *, so its basically redirecting everything Traefik receives on port 5432 to Postgres at port 5432.

I hope that gives you a good idea on how things can be stitched together as we have both HTTP and TCP routers configured on this example.

Alternatively if your Postgres server is an external server you might want to set it up with a static routing rule via the File provider instead of relying on Docker labels in this case. For that I left some lines commented out on the previous static + docker compose files. Here is an example for its content:

#Traefik dynamic config for the File provider /sandbox/configs/file-provider.yml
tcp:
  routers:
    postgres:
      entryPoints:
      - tcp
      service: tcp@file
      rule: HostSNI(`*`)
  services:
    tcp:
      loadBalancer:
        servers:
          - address: postgres:5432