Cannot initialize postgres database

[msiebuhr]

Past Issues Searched

• I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

I have pointed a self-hosted Plausible at a pre-created Postgresql database (CREATE DATABASE ... + setting up a role for the database). After clearing the first hurdle by asking it not to create the database per #405, I now get this error:

+ /entrypoint.sh db migrate
Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
09:46:58.660 [error] Clickhousex.Protocol (#PID<0.180.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
09:46:58.660 [error] Clickhousex.Protocol (#PID<0.179.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
Running migrations for Elixir.Plausible.ClickhouseRepo
09:47:00.092 [error] Clickhousex.Protocol (#PID<0.179.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
09:47:00.243 [error] Clickhousex.Protocol (#PID<0.180.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
09:47:02.930 [error] Clickhousex.Protocol (#PID<0.179.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
09:47:04.434 [error] Clickhousex.Protocol (#PID<0.180.0>) failed to connect: ** (ErlangError) Erlang error: :nxdomain
09:47:04.447 [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)

To fix the first issue, run "mix ecto.create".

To address the second, you can run "mix ecto.drop" followed by
"mix ecto.create". Alternatively you may configure Ecto to use
another table and/or repository for managing migrations:

    config :plausible, Plausible.ClickhouseRepo,
      migration_source: "some_other_table_for_schema_migrations",
      migration_repo: AnotherRepoForSchemaMigrations

The full error report is shown below.

** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 5745ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

  1. Ensuring your database is available and that you can connect to it
  2. Tracking down slow queries and making sure they are running fast enough
  3. Increasing the pool_size (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.13.4) lib/enum.ex:1593: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1024: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:696: Ecto.Migrator.verbose_schema_migration/3
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:510: Ecto.Migrator.lock_for_migrations/4
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:422: Ecto.Migrator.run/4
    (ecto_sql 3.8.3) lib/ecto/migrator.ex:146: Ecto.Migrator.with_repo/3
    (plausible 0.0.1) lib/plausible_release.ex:91: Plausible.Release.run_migrations_for/1

Inside Kubernetes, the relevant container looks like this:

Init Containers:
  plausible-init:
    Container ID:  containerd://950c8e066e9c00ac3fcf1bea1d0e49693f84698d31e70a62dea5677c0515991e
    Image:         harbor.one.com/mirror/docker.io/plausible/analytics:v1.5
    Image ID:      harbor.one.com/mirror/docker.io/plausible/analytics@sha256:3fbdcfac56ffa79e778e9e4bc1ca8c4dca0d7deb330ed5694f59eaa1125cc0a8
    Port:          <none>
    Host Port:     <none>
    Command:
      /bin/sh
      -x
      -c
      /entrypoint.sh db migrate && /entrypoint.sh db init-admin
    State:          Waiting
      Reason:       CrashLoopBackOff
    Last State:     Terminated
      Reason:       Error
      Exit Code:    1
      Started:      Thu, 04 May 2023 11:58:39 +0200
      Finished:     Thu, 04 May 2023 11:58:46 +0200
    Ready:          False
    Restart Count:  6
    Limits:
      cpu:     1
      memory:  1000Mi
    Requests:
      cpu:     500m
      memory:  500Mi
    Environment:
      DATABASE_URL:     <set to the key 'DATABASE_URL' in secret 'systems-plausible-analytics-db'>  Optional: false
      SECRET_KEY_BASE:  <set to the key 'SECRET_KEY_BASE' in secret 'systems-plausible-analytics'>  Optional: false
      DISABLE_AUTH:     true
      BASE_URL:         https://systems-plausible.default.wip1-k8s-cph3.one.com
    Mounts:
      /app/tmp from app-tmp (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-jf8rh (ro)
Containers:
  plausible-analytics:
    Container ID:  
    Image:         harbor.one.com/mirror/docker.io/plausible/analytics:v1.5
    Image ID:      
    Port:          8000/TCP
    Host Port:     0/TCP
    Command:
      /entrypoint.sh
      run
    State:          Waiting
      Reason:       PodInitializing
    Ready:          False
    Restart Count:  0
    Limits:
      cpu:     1
      memory:  1000Mi
    Requests:
      cpu:      500m
      memory:   500Mi
    Liveness:   http-get http://:http/ delay=0s timeout=1s period=10s #success=1 #failure=3
    Readiness:  http-get http://:http/ delay=0s timeout=1s period=10s #success=1 #failure=3
    Environment:
      DATABASE_URL:     <set to the key 'DATABASE_URL' in secret 'systems-plausible-analytics-db'>  Optional: false
      SECRET_KEY_BASE:  <set to the key 'SECRET_KEY_BASE' in secret 'systems-plausible-analytics'>  Optional: false
      DISABLE_AUTH:     true
      BASE_URL:         https://systems-plausible.default.wip1-k8s-cph3.one.com
    Mounts:
      /app/tmp from app-tmp (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-jf8rh (ro)

I have a sneaking suspicion it could be related to the fact it writes a lot about Clickhouse, even though I haven't configured anything Clickhouse-related.

Expected behavior

The database should be initialized and Plausible running!

Screenshots

No response

Environment

- OS: Ubuntu LTS inside Kubernetes
- Browser: N/A
- Browser Version: N/A

[ruslandoga]

👋 @msiebuhr

I have a sneaking suspicion it could be related to the fact it writes a lot about Clickhouse, even though I haven't configured anything Clickhouse-related.

Yeah, the startup error seems to be caused by ClickHouse being unavailable.

[msiebuhr]

Yeah, but I'm using Postgres, so why the clickhouse-errors?

(Are they both "clickhouse"-database connectors, like people often do with CockroachDB and Postgres?)

[ruslandoga]

Sorry, I missed the reply.

Please note that Plausible doesn't work without ClickHouse. In your particular case Plausible stops when it fails to run the migrations. But even if that step was skipped (by removing /entrypoint.sh db migrate from plausible command), it'd still not work as events and sessions wouldn't be stored anywhere.

[msiebuhr]

Ah, right, my bad then - since the helm chart at 8gears/plausible-analytics-helm-chart has a clickhouse.enabled=true setting, I figured it was optional (or an alternative to postgres) and skippable.

As it is then obviously not any fault of yours - apologies for the troubles and time wasted.