Error configuring Clickhouse remotely

[altendorfme]

Server A:

version: "3.3"
services:
  mail:
    container_name: mail
    image: bytemark/smtp
    restart: always
    logging:
      options:
        max-size: 50m

  postgres:
    container_name: postgres
    image: postgres:14-alpine
    restart: always
    ports:
      - 5432:5432
    volumes:
      - /opt/postgres/var:/lib/postgresql/data
    environment:
      - PGDATA=/var/lib/postgresql/data/plausible
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    logging:
      options:
        max-size: 50m

  plausible:
    container_name: plausible
    image: plausible/analytics:latest
    restart: always
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
    depends_on:
      - postgres
      - geoip
      - mail
    environment:
      - TZ=America/Sao_Paulo
      - BASE_URL=https://xyz.xyz
      - SECRET_KEY_BASE=SECRET_KEY_BASE
      - [email protected]
      - SMTP_HOST_ADDR=in-v3.mailjet.com
      - SMTP_HOST_PORT=587
      - SMTP_USER_NAME=SMTP_USER_NAME
      - SMTP_USER_PWD=SMTP_USER_PWD
      - SMTP_HOST_SSL_ENABLED=false
      - SMTP_RETRIES=2
      - GOOGLE_CLIENT_ID=GOOGLE_CLIENT_ID
      - GOOGLE_CLIENT_SECRET=GOOGLE_CLIENT_SECRET
      - DISABLE_REGISTRATION=false
      - GEOLITE2_COUNTRY_DB=/geoip/GeoLite2-Country.mmdb
      - CLICKHOUSE_DATABASE_URL=http:https://10.0.0.92:8123/plausible
      - DATABASE_URL=postgres:https://postgres:postgres@postgres:5432/plausible_db
    ports:
      - 8000:8000
    volumes:
      - /opt/geoip:/geoip:ro
    labels:
      virtual.host: "https://xyz.xyz"
      virtual.port: "8000"
      virtual.tls-email: "[email protected]"
    logging:
      options:
        max-size: 50m

  geoip:
    container_name: geoip
    image: maxmindinc/geoipupdate
    restart: always
    environment:
      - GEOIPUPDATE_EDITION_IDS=GeoLite2-Country
      - GEOIPUPDATE_FREQUENCY=168
      - GEOIPUPDATE_ACCOUNT_ID=GEOIPUPDATE_ACCOUNT_ID
      - GEOIPUPDATE_LICENSE_KEY=GEOIPUPDATE_LICENSE_KEY
      - MAXMIND_LICENSE_KEY=MAXMIND_LICENSE_KEY
      - MAXMIND_EDITION=GeoLite2-City
    volumes:
      - /opt/geoip:/usr/share/GeoIP
    logging:
      options:
        max-size: 50m

  caddy:
    container_name: caddy
    image: wemakeservices/caddy-gen:latest
    restart: always
    volumes:
      - /var/run/docker.sock:/tmp/docker.sock:ro
      - /opt/caddy/:/data/caddy
    ports:
      - 80:80
      - 443:443
    depends_on:
      - plausible
    logging:
      options:
        max-size: 50m

Server B:

version: "3.3"
services:
  clickhouse:
    container_name: clickhouse
    image: clickhouse/clickhouse-server:22.8.13.20-alpine
    restart: always
    network_mode: host
    volumes:
      - /opt/clickhouse/data:/var/lib/clickhouse
      - /opt/clickhouse/clickhouse-config.xml:/etc/clickhouse-server/config.d/logging.xml:ro
      - /opt/clickhouse/clickhouse-user-config.xml:/etc/clickhouse-server/users.d/logging.xml:ro
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    logging:
      options:
        max-size: 50m

Server A is seeing Server B, I validated with:

$curl 10.0.0.92:8123
Ok.

I can also see the plausible table, which I manually created on Server B:

$ curl 10.0.0.92:8123/?query=SHOW%20DATABASES
INFORMATION_SCHEMA
default
information_schema
plausible
system

But when looking at the Plausible logs:

Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
19:26:46.862 [error] Ch.Connection (#PID<0.172.0>) failed to connect: ** (Mint.TransportError) non-existing domain
19:26:46.862 [error] Ch.Connection (#PID<0.171.0>) failed to connect: ** (Mint.TransportError) non-existing domain
create Plausible.IngestRepo database if it doesn't exist
** (MatchError) no match of right hand side value: {:error, %Mint.TransportError{reason: :nxdomain}}
    (plausible 0.0.1) lib/plausible_release.ex:40: anonymous fn/2 in Plausible.Release.createdb/0
    (elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (plausible 0.0.1) lib/plausible_release.ex:39: Plausible.Release.createdb/0
    nofile:1: (file)
    (stdlib 4.2) erl_eval.erl:748: :erl_eval.do_apply/7

Any ideas?
When I up the clickhouse together on Server A everything worked perfectly, I even kept the CLICKHOUSE_DATABASE_URL env to validate.

[altendorfme]

I saw some tips in the topic: #3070
When I put plausible in network_mode: host I can access clickhouse, any alternatives here?

[ruslandoga]

👋 @altendorfme

Could you please try making a request from within the plausible container? It might make things clearer.

$ cd hosting
$ docker compose exec plausible ash
# wget http:https://10.0.0.92:8123 -qSO -

And also please check the configuration within the running application, to make sure the CLICKHOUSE_DATABASE_URL has been handled correctly:

$ cd hosting
$ docker compose exec plausible bin/plausible remote

iex> System.get_env("CLICKHOUSE_DATABASE_URL")
nil # I don't have it set, so in my case the default URL is used

iex> Application.get_env(:plausible, Plausible.ClickhouseRepo)[:url]
"http:https://plausible_events_db:8123/plausible_events_db"

# which is processed into the following config as startup
iex> Plausible.ClickhouseRepo.config()
[
  telemetry_prefix: [:plausible, :clickhouse_repo],
  otp_app: :plausible,
  timeout: 15000,
  pool_size: 10,
  loggers: [Ecto.LogEntry],
  queue_target: 500,
  queue_interval: 2000,
  transport_opts: [keepalive: true, show_econnreset: true],
  settings: [readonly: 1],
  hostname: "plausible_events_db",
  scheme: "http",
  database: "plausible_events_db",
  port: 8123
]

[ruslandoga]

I think the issue might be that the docker's bridge subnet overlaps with your VPC subnet (with maybe both being 10.0.0.0/16).

You might be able to check it with

$ docker network ls
NETWORK ID     NAME              DRIVER    SCOPE
3f24662a31eb   bridge            bridge    local
a11ec9133232   host              host      local
32de2cf18243   hosting_default   bridge    local
440ee10198bf   none              null      local

$ docker network inspect hosting_default | grep Subnet
"Subnet": "172.20.0.0/16"

[altendorfme]

Hello, I think the subnet issue might be the solution.
I did the tests you sent above and when I validated the connection without network_mode: host everything worked normally 🤡😭

As I recreated some environments in the tests, I did not check the value of the subnet, but thank you very much for your help, I will create a new environment from scratch and I will note this detail!

Thank you very much!