search

schnerring / k8s-backup-scripts

Scripts that I use for TrueNAS jail cron jobs to backup my Azure Kubernetes Service data
MIT License
3 stars 1 forks source link

Backup ClickHouse `schema_migrations` table #2

Open schnerring opened 2 years ago

schnerring commented 2 years ago

Currently the plausible.schema_migrations ClickHouse table isn't backed up because clickhouse-backup only supports MergeTree family tables.

In case of disaster recovery, the table has to be reconstructed with the following script:

NOTE: the query depends on the Plausible version. The following query is for Plausible v1.4.4

INSERT INTO plausible.schema_migrations (version,inserted_at) VALUES
     (20200915070607,'2022-04-22 04:07:40'),
     (20200918075025,'2022-04-22 04:07:40'),
     (20201020083739,'2022-04-22 04:07:40'),
     (20201106125234,'2022-04-22 04:07:40'),
     (20210323130440,'2022-04-22 04:07:40');
schnerring commented 1 year ago

For v1.5.1, the query is:

INSERT INTO plausible.schema_migrations (version,inserted_at) VALUES
     (20200915070607,'2022-04-22 04:07:40'),
     (20200918075025,'2022-04-22 04:07:40'),
     (20201020083739,'2022-04-22 04:07:40'),
     (20201106125234,'2022-04-22 04:07:40'),
     (20210323130440,'2022-04-22 04:07:40'),
     (20210712214034,'2023-03-19 03:03:24'),
     (20211017093035,'2023-03-19 03:03:24'),
     (20211112130238,'2023-03-19 03:03:25'),
     (20220310104931,'2023-03-19 03:03:25'),
     (20220404123000,'2023-03-19 03:03:25'),
     (20220421161259,'2023-03-19 03:03:26'),
     (20220422075510,'2023-03-19 03:03:26');

Regarding clickhouse-backup support for TinyLog engine tables, see:

https://github.com/AlexAkulov/clickhouse-backup/issues/238#issuecomment-1475089382