Cannot properly back up Plausible Analytics because the schema_migrations table of type TinyLog is ignored

[vitobotta]

Hi! Thanks for this project, very useful. I am trying to set up backups for Plausible Analytics, but there is a problem: the table schema_migrations is of type TinyLog and it seems that clickhouse backup ignores it and creates an empty backup for it. I can see with the debug log level that it says "skipped" for that table.

Therefore when I restore from a backup, that table will be empty and the Plausible Analytics app will re-run all the migrations, which fail because the tables etc already exist, so the app doesn't start. How can I ensure that that kind of table is also fully backed up? Thanks!

[Slach]

according to https://github.com/golang-migrate/migrate/tree/master/database/clickhouse
you can re-define x-migrations-table-engine as MergeTree
and all migration history will backup successfully

[vitobotta]

Hi @Slach ! And thanks for your reply. Are there any consequences from the app's point of view when using a table whose engine has been changed? Thanks

[Slach]

@vitobotta I see only one concern here
before applying x-migrations-table-engine, you should create a new table with CREATE schema_migrations_mt AS default.schema_migrations Engine=MergeTree() ORDER BY tuple() move data from the old schema_migrations table and execute DROP TABLE IF EXISTS schema_migrations; RENAME TABLE schema_migrations_mt TO schema_migrations

[vitobotta]

I see, I will give it a try and see if I manage :D

[AlexAkulov]

I think the best solution of this issue is to ask the 'Plausible Analytics' developers to use the MergeTree engine instead of TinyLog.

[schnerring]

I'm in the same boat... I have been using clickhouse-backup to backup my Plausible Analytics database.

I think the best solution of this issue is to ask the 'Plausible Analytics' developers to use the MergeTree engine instead of TinyLog.

Plausible's migrations table uses the TinyLog engine. Even though I have a very limited understanding of the Plausible Analytics source code and ClickHouse, I'm kind of confident that not the Plausible devs decided what engine to use for the migrations table, but rather the developers of a 3rd party ORM. If that's the case, Plausible isn't the only application suffering from this issue. Maybe @ukutaht or @metmarkosaric can shed some light?

Is there a valid technical reason why TinyLog engine tables aren't supported by clickhouse-backup? If not, I don't think the solution is to design an application around its backup tool.

edit: I didn't mean to necro-post - I can open a new issue if desired.

[Slach]

@schnerring

Is there a valid technical reason why TinyLog engine tables aren't supported by clickhouse-backup? If not, I don't think the solution is to design an application around its backup tool.

clickhouse-backup works outside from clickhouse-server and use ALTER TABLE ... FREZZE command for safe make hardlinks of current data files, this command doesn't support TinyLog and all other *Log engines

try to use in your configuration:

general:
  use_embedded_backup_restore: true

it will use available in clickhouse-server version 23.x+ BACKUP/RESTORE command,
https://clickhouse.com/docs/en/operations/backup#command-summary

but it also have some bugs
https://github.com/ClickHouse/ClickHouse/issues?q=is%3Aopen+is%3Aissue+author%3ASlach+backup

[schnerring]

Amazing work, thanks for the info!