Clickhouse migration rollback

Question

I'm currently working with ClickHouse and need to rollback some migrations due to an issue in my application, but I couldn't find a direct way to rollback migrations in ClickHouse using this tool.

I would like to know if there is a recommended approach or workaround to rollback migrations in ClickHouse. Specifically, I am looking for a way to modify the schema_migrations table to achieve this.

Any help or guidance would be greatly appreciated.

Thank you!

I tried to look at the schema but its appearance left me bewilderedenter image description here

Answer 1

If you use https://github.com/golang-migrate/migrate you need to write down migration you need always use ON CLUSTER clause for your DDL if you have multiple servers in clickhouse cluster

then look https://github.com/golang-migrate/migrate/tree/master/database/clickhouse

use ?x-migrations-table-engine=MergeTree in connection URL

if your migration failure with default golang-migrate settings

you need manually convert your TinyLog schema_migrations table to MergeTree

use

CREATE TABLE mergetree_schema_migrations ON CLUSTER 'cluster-name'
ENGINE=MergeTree() ORDER BY tuple() 
AS SELECT * FROM schema_migrtions;

after it you can change records in mergetre_schema_migrations via mutations

ALTER TABLE mergetree_schema_migrations UPDATE / DELETE ... WHERE ..

look details

• https://clickhouse.com/docs/en/sql-reference/statements/alter/update
• https://clickhouse.com/docs/en/sql-reference/statements/alter/delete
• https://clickhouse.com/docs/en/sql-reference/statements/alter#synchronicity-of-alter-queries

after it you can drop exists schema_migrations and rename mergetree_schema_migrations

DROP TABLE IF EXISTS schema_migrations ON CLUSTER 'cluster-name' SYNC;

RENAME TABLE mergetree_schema_migrations 
ON CLUSTER 'cluster-name' TO schema_migrations;