Replication error connecting to Clickhouse Cloud

[ssube]

I'm trying to set up a self-hosted instance of Plausible in kubernetes, connecting to another pod for the Postgres database and Clickhouse Cloud for the Clickhouse DB.

The logs indicate that the pod was able to connect to both, but it fails shortly after, while trying to create some tables (I think):

Loading plausible..                                                                                                                                                                                                                                                      
Starting dependencies..                                                                                                                                                                                                                                                  
Starting repos..                                                                                                                                                                                                                                                         
create Plausible.Repo database if it doesn't exist                                                                                                                                                                                                                       
create Plausible.IngestRepo database if it doesn't exist                                                                                                                                                                                                                 
Creation of Db successful!                                                                                                                                                                                                                                               
Loading plausible..                                                                                                                                                                                                                                                      
Starting dependencies..                                                                                                                                                                                                                                                  
Starting repos..                                                                                                                                                                                                                                                         
Running migrations for Elixir.Plausible.Repo                                                                                                                                                                                                                             
Running migrations for Elixir.Plausible.IngestRepo
18:35:32.196 [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.IngestRepo,                                                                                        
      migration_source: "some_other_table_for_schema_migrations",
      migration_repo: AnotherRepoForSchemaMigrations                                                                                
                                                                  
The full error report is shown below.
                                                                                                                                                                                                                                                                         
** (Ch.Error) Code: 56. DB::Exception: Only tables with a Replicated engine or tables which do not store data on disk are allowed in a Replicated database. (UNKNOWN_STORAGE) (version 23.8.1.41638 (official build))
                                                                                                                                                                                                                                                                         
    (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1                                                                                                                                                                              
    (elixir 1.14.3) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2                                     
    (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4                                                                                                                                                                                      
    (ecto_sql 3.9.2) lib/ecto/migrator.ex:677: Ecto.Migrator.verbose_schema_migration/3
    (ecto_sql 3.9.2) lib/ecto/migrator.ex:491: Ecto.Migrator.lock_for_migrations/4                                   
    (ecto_sql 3.9.2) lib/ecto/migrator.ex:403: Ecto.Migrator.run/4                                                                                                                                                                                                       
    (ecto_sql 3.9.2) lib/ecto/migrator.ex:146: Ecto.Migrator.with_repo/3       
    nofile:1: (file)                                                            

Running the plausible/analytics:latest container, which is currently docker.io/plausible/analytics@sha256:cd5f75e1399073669b13b4151cc603332a825324d0b8f13dfc9de9112a3c68a1.

> kubectl -n plausible describe pods | grep mage
    Image:         plausible/analytics:latest
    Image ID:      docker.io/plausible/analytics@sha256:cd5f75e1399073669b13b4151cc603332a825324d0b8f13dfc9de9112a3c68a1

> docker pull plausible/analytics:latest
latest: Pulling from plausible/analytics
...
Digest: sha256:cd5f75e1399073669b13b4151cc603332a825324d0b8f13dfc9de9112a3c68a1
Status: Downloaded newer image for plausible/analytics:latest

There are a number of other Ch.* connection errors reported here, but I don't see anything for Clickhouse Cloud specifically.

[ruslandoga]

👋 @ssube

schema_migrations table uses TinyLog engine by default, which probably is not a replicated engine.

The table engine for the schema_migrations table can be configured by adding

config :ecto_ch, default_table_engine: "MergeTree order by tuple()"

to config/runtime.exs, but that would require to either modify the container image or mount a volume with a new config at /app/releases/0.0.1/runtime.exs

[ruslandoga]

You can also create the schema_migrations table manually, I think that's what Plausible Cloud did. Here's the default schema

CREATE TABLE plausible_events_db.schema_migrations
(
    `version` Int64,
    `inserted_at` DateTime
)
ENGINE = TinyLog

plausible would then just reuse it instead of attempting to create a new table.

[ssube]

It took me a few tries to find the right engine, but it seems like this worked:

CREATE TABLE plausible.schema_migrations
(
    `version` Int64,
    `inserted_at` DateTime
)
ENGINE = ReplicatedMergeTree
ORDER BY tuple();

(where plausible is the name of your database)

After creating that table, the migrations appear to have run correctly:

> kubectl -n plausible logs plausible-c56d7d6b-4p7b6 -c plausible-init -f
Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
create Plausible.IngestRepo database if it doesn't exist
Creation of Db successful!
Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
Running migrations for Elixir.Plausible.IngestRepo
Migrations successful!

[harrytang]

In case you are interested, I've written fully high-availability (HA) self-hosted tutorial (self-hosted Plausible, ClickHouse, PostgreSQL):
https://harrytang.xyz/blog/high-availability-self-hosted-plausible-analytics-k8s