Docker compose with external postgres database and plausible

Question

Hi I'm struggling to configure the docker compose to work with external postgres database on Debian 11. It seems like the container doesn't resolve host.docker.internal correctly. The first time I ran the container it was working because I used the IP 172.17.0.1 for the database and the container started as usual on 172.17.0.4 but each time I run docker-compose up it increment the ip (172.18.0.4 172.19.0.4 ... 172.28.0.4 ).

So the solution I've tried :

• make host.docker.internal working

• find a way to force the plausible container IP address to 172.17.0.4 and use 172.17.0.1 for the database.

• make the host mode work with localhost but that's not the most secure one

Here's the logs

Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
06:57:08.578 [error] Postgrex.Protocol (#PID<0.164.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (host.docker.internal:5432): connection refused - :econnrefused
06:57:08.578 [error] Postgrex.Protocol (#PID<0.163.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (host.docker.internal:5432): connection refused - :econnrefused
06:57:09.988 [error] Postgrex.Protocol (#PID<0.163.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (host.docker.internal:5432): connection refused - :econnrefused
06:57:10.343 [error] Postgrex.Protocol (#PID<0.164.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (host.docker.internal:5432): connection refused - :econnrefused
06:57:11.505 [error] Could not create schema migrations table. This error usually happens due to the following:

  * The database does not exist
  * The "schema_migrations" table, which Ecto uses for managing
    migrations, was defined by another library
  * There is a deadlock while migrating (such as using concurrent
    indexes with a migration_lock)

docker-compose.yml

version: "3.3"

#networks:
 # lan_access:
  #  driver: bridge

services:
  mail:
    image: bytemark/smtp
    restart: always

#  plausible_db:
    # supported versions are 12, 13, and 14
 #   image: postgres:14-alpine
 #   restart: always
 #   volumes:
 #     - db-data:/var/lib/postgresql/data
 #   environment:
 #     - POSTGRES_PASSWORD=postgres

  plausible_events_db:
    image: clickhouse/clickhouse-server:23.3.7.5-alpine
    restart: always
    volumes:
      - event-data:/var/lib/clickhouse
      - ./clickhouse/clickhouse-config.xml:/etc/clickhouse-server/config.d/logging.xml:ro
      - ./clickhouse/clickhouse-user-config.xml:/etc/clickhouse-server/users.d/logging.xml:ro
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  plausible:
    image: plausible/analytics:v2.0
    restart: always
    command: sh -c "sleep 10 && /entrypoint.sh db migrate && /entrypoint.sh run"

    #command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
    depends_on:
   #   - plausible_db
      - plausible_events_db
      - mail
   # networks:
    #  - lan_access
     # - default
   # network_mode: bridge
   # network_mode: host

    ports:
      - 8001:8000
    env_file:
      - plausible-conf.env
    extra_hosts:
      - "host.docker.internal:host-gateway"
volumes:
 # db-data:
  #  driver: local
  event-data:
    driver: local

Here's the plausible-conf.env

BASE_URL=https://analytics.example.com
SECRET_KEY_BASE=SECRET
DATABASE_URL=postgres://plausible:[email protected]:5432/plausible_db
#DATABASE_URL=postgres://plausible:[email protected]::5432/plausible_db
#DATABASE_URL=postgres://plausible:[email protected]:5432/plausible_db
MAILER_NAME=plausible
SMTP_HOST_ADDR=localhost
SMTP_HOST_PORT=25

Do you know what's wrong with my configuration ?

Answer 1

Please have a look original page and the code:

• https://plausible.io/docs/self-hosting
• https://github.com/plausible/hosting

Run:

git clone https://github.com/plausible/hosting
cd hosting

# to create secret key to paste SECRET_KEY_BASE in plausible-conf.env 
openssl rand -base64 64 | tr -d '\n' ; echo
docker-compose up -d

docker-compose.yaml (that I used):

version: "3.3"
services:
  mail:
    image: bytemark/smtp
    restart: always

  plausible_db:
    # supported versions are 12, 13, and 14
    image: postgres:14-alpine
    restart: always
    volumes:
      - db-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=postgres

  plausible_events_db:
    image: clickhouse/clickhouse-server:23.3.7.5-alpine
    restart: always
    volumes:
      - event-data:/var/lib/clickhouse
      - ./clickhouse/clickhouse-config.xml:/etc/clickhouse-server/config.d/logging.xml:ro
      - ./clickhouse/clickhouse-user-config.xml:/etc/clickhouse-server/users.d/logging.xml:ro
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  plausible:
    image: plausible/analytics:v2.0
    restart: always
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
    depends_on:
      - plausible_db
      - plausible_events_db
      - mail
    ports:
      - 8000:8000
    env_file:
      - plausible-conf.env

volumes:
  db-data:
    driver: local
  event-data:
    driver: local

plausible-conf.env (that I used):

When you use the DB settings, plausible/analytics:v2.0 cannot run properly. So remove it.

BASE_URL=http://localhost
SECRET_KEY_BASE=AlxO9FylRkXEzARzNoYFsGQHB8ZaS1wiKt31DmnTaSYOafDopfmxmf1hyF3AF7zIsu9elF+teLRmw5DL0ZJv3Q==

Running containers:

Browser (http://localhost:8080), it works:

Debugging:

If you want to debug it, after running docker-compose up -d, run docker container ls -a to get the container id of plausible/analytics:v2.0 (e.g. a14d4b63ea97), then run docker logs a14 to see the error/logs.

After running 'compose up' and 40 secs later, if plausible/analytics:v2.0 is restarted again and again, there is a problem on it, and view it with 'docker logs' command.

Connection to another Postgresql container: (this part was added after the comment)

Create a new bridge network with:

docker network create mynetwork

Add to the docker-compose.yml file:

networks:
  - mynetwork:

services:
 plausible_db:
   ...
   networks:
   - mynetwork

plausible-conf.env:

DATABASE_URL=postgresql://postgres:postgres@<PostgresqlContainerName>:5432/plausible_db

With this configuration, plausible/analytics:v2.0 can connect the Postgresql container via the docker network.

After running, please have a look at the plausible/analytics:v2.0 container with docker logs .

DB connection is done (because I created plausible_db on the Postgresql container and there is no DB connection error), but there is a migration problem on the DB. I think that this is not related with the Docker network, or DB connection. It is DB migration issue.