search

Altinity / clickhouse-backup

Tool for easy backup and restore for ClickHouse® using object storage for backup files.
https://altinity.com
Other
1.28k stars 226 forks source link

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

Closed vitobotta closed 3 years ago

vitobotta commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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

AlexAkulov commented 3 years ago

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

schnerring commented 1 year ago

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 commented 1 year ago

@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 commented 1 year ago

Amazing work, thanks for the info!