Documentation / help for recovering Langfuse from a Clickhouse DB backup

[andresC98]

I am having a look at the https://langfuse.com/self-hosting/configuration/backups docs since we are interested on doing backups of a (selfhosted also) Clickhouse Langfuse DB however we are having issues restoring the data.

In the docs backup is explained (via several methods, we are using the clickhouse-backup tool), however we have no info on the restore process.

Once the Clickhouse DB is restored, the langfuse web container at restart shows the following error:

Script executed successfully.
Prisma schema loaded from packages/shared/prisma/schema.prisma
Datasource "db": PostgreSQL database "langfuse", schema "public" at "langfuse-postgresql"
324 migrations found in prisma/migrations
No pending migrations to apply.
error: Dirty database version 1. Fix and force version.
Applying clickhouse migrations failed. This is mostly caused by the database being unavailable.

I already tried the first time with e.g. kubectl exec -n langfuse statefulset.apps/langfuse-clickhouse-shard0 -- clickhouse-client -u default --password xxxxxx --query "ALTER TABLE default.schema_migrations UPDATE dirty = 0 WHERE version = 1" as suggested here but only resulted after restarting langfuse web in the same message but this time with dirty database version 2.

Are there any guidelines/docs on how to recover langfuse after a restore of the clickhouse db?

[inkeep-ai-assistant[bot]]

The issue you're experiencing is covered in the Langfuse documentation for handling failed ClickHouse migrations(1). When you restore a ClickHouse database backup, the migration tool marks the database as 'dirty' and prevents additional migrations from running(1).

The error "Dirty database version X. Fix and force version" indicates your database has been marked as 'dirty'(1). You need to investigate whether the migration was partially applied or not applied at all, then force your database to the version that accurately reflects its current state(1).

Here's the process to fix this:

1. Determine the last successful migration version - If migration 16 failed, the last successful version would be 15(1)

2. Force the database to the correct version using golang-migrate. Make sure you have golang-migrate installed and navigate to the root of your Langfuse project folder(1)

3. Run the force command:

migrate -path migrations/ -database clickhouse://test:test@localhost:8123/dummy force 15

(1)

Replace the database credentials with your own(1). The version number (15 in this example) should be the last successfully applied migration(1).

4. After forcing the correct version, you can run migrations again, and the output should show something like:

16/u migration_name (12.718637ms)

(1)

The key point is that once you've forced the correct version and fixed the migration, your database will be 'clean' again, allowing you to proceed with subsequent migrations(1).

For your specific case where you're seeing incrementing version numbers (version 1, then version 2), you'll need to determine which was the actual last successful migration version before the restore and force to that version rather than trying to update the dirty flag directly.

📚 Sources:

1. Clickhouse handling failed migrations in self-hosted Langfuse

---

Have another question? Just tag @inkeep.

[Steffen911]

@andresC98 Can you share the state of the schema_migrations table? Was that part of the backups and how does it look like immediately after a restore?

[andresC98]

I ended up switching to https://clickhouse.com/docs/operations/backup#configuring-backuprestore-to-use-an-s3-endpoint using clickhouse native BACKUP/RESTORE TO/FROM S3 operations and this time worked fine. Seemed like the clickhouse-backup tool did not properly back everything that langfuse needed.