Upgrade via data migration

Prerequisites

Before you begin, make sure you have:

• Two Community-compatible Edition clusters, both in Running status

  To migrate between Community-compatible Edition and Enterprise Edition, see Migrate a ClickHouse Community-compatible Edition cluster to an Enterprise Edition cluster.

• A database account and password configured on both clusters

• The same tiered storage configuration for hot and cold data on both clusters

• Both clusters in the same region and Virtual Private Cloud (VPC), with each cluster's IP address added to the other's whitelist

  Run SELECT * FROM system.clusters; to find a cluster's IP address. For whitelist configuration, see Set a whitelist.

• A destination cluster whose major engine version is later than the source cluster's

• Available disk storage on the destination cluster (excluding cold storage) that is at least 1.2x the source cluster's used disk storage (excluding cold storage)

• Each local table in the source cluster paired with exactly one distributed table

For supported versions, see Release notes for ApsaraDB for ClickHouse Community-compatible Edition.

What can and cannot be migrated

Supported migration content:

• Databases, tables (MergeTree engine), materialized views, data dictionaries (SQL-created only), user permissions, and cluster configurations

• Table schemas of non-MergeTree tables (external tables and Log tables) — schemas only, without business data

  If the source cluster contains non-MergeTree tables, the destination cluster will only have their schemas after migration, without any business data. To migrate the business data, you can use the remote function. For more information, see Use the remote function to migrate data.

Not supported:

• Data dictionaries created using XML

• Kafka and RabbitMQ engine tables (business data)

Preparation checklist

Before starting the migration:

• Confirm no management operations (scale-out, upgrade, or downgrade) are running on either cluster.

• Check for XML-created data dictionaries by running: SELECT * FROM system.dictionaries WHERE (database = '') OR isNull(database); — if this returns results, remove those dictionaries before proceeding.

• If a data dictionary accesses an external service, confirm the service is reachable and its whitelist allows access from the destination cluster.

• If a data dictionary uses an internal ClickHouse table as its source with the HOST parameter set to an IP address, the IP changes after migration — manually recreate that data dictionary with the new IP after migration.

• For Kafka and RabbitMQ tables: clear them from the source cluster and recreate them on the destination cluster, or use different consumer groups to avoid data splitting.

• Keep total cold data in the source cluster under 1 TB. Larger cold data volumes significantly extend migration time and may cause the task to fail.

• After the upgrade, update the endpoint in your client settings to point to the destination cluster.

Potential impacts

Source cluster during migration:

• Read and write operations continue normally.

• Data Definition Language (DDL) operations (adding, deleting, or modifying databases and tables) are blocked.

• When the estimated remaining migration time drops to 10 minutes or less, the source cluster automatically pauses writes to keep data consistent:

  • If this happens within the preset write-stop time window, writes stop immediately.

  • If this happens outside the write-stop window and within 5 days of task creation, modify the write-stop time window to continue.

  • If this happens outside the write-stop window and beyond 5 days of task creation, the migration fails. Cancel the task, clear migrated data from the destination cluster, and start over.

• Writes resume automatically when all data is migrated, or when the write-stop time window ends before migration completes.

Destination cluster after migration:

The destination cluster runs frequent merge operations for a period after migration, increasing I/O usage and query latency. Plan for this elevated latency before switching traffic. To estimate how long the merge operations will take, see Calculate the merge duration after migration.

Migration overview

All steps are performed on the destination cluster, not the source cluster.

1. Record and clean up Kafka/RabbitMQ engine tables (skip if not applicable).

2. Create a migration task on the destination cluster.

3. Assess whether the migration can complete (required only if source write speed exceeds 20 MB/s).

4. Monitor the migration task.

5. Rebuild Kafka/RabbitMQ engine tables on the source cluster (skip if not applicable).

6. Delete Kafka/RabbitMQ engine tables on the source cluster (skip if not applicable).

7. Rebuild Kafka/RabbitMQ engine tables on the destination cluster (skip if not applicable).

8. (Optional) Cancel the migration task if needed.

9. (Optional) Modify the write-stop time window.

Step 1: record and clean up Kafka/RabbitMQ engine tables

Before starting the migration, record the definitions of all Kafka/RabbitMQ engine tables and their downstream materialized views in the source cluster, handle implicit tables, and then delete these tables to avoid migration errors.

1. Log on to the source cluster and query all Kafka and RabbitMQ engine tables along with their downstream dependencies.

   /*
   create_table_query: table definition
   dependencies_database: database of the table that depends on this table
   dependencies_table: table that depends on this table
   From dependencies_database and dependencies_table, you can identify the materialized views that depend on Kafka/RabbitMQ tables
   */
   SELECT * FROM system.tables WHERE engine IN ('RabbitMQ', 'Kafka');

2. View the materialized view definition to check whether its target table is an implicit table.

   /*
   View the materialized view definition.
   If the target table of the materialized view is an implicit table, pay special attention:
   Dropping the materialized view will also drop the implicit table, causing data loss.
   Example: If CREATE MATERIALIZED VIEW [db.]table_name [TO[db.]name] does not specify TO,
   the system automatically creates an implicit table, possibly in the format '.inner_id.<TABLE_UUID>' or '.inner.<TABLE>'
   */
   SELECT * FROM system.tables WHERE database='<DATABASE>' AND name = '<MATERIALIZED_VIEW_NAME>';

3. If the target table of a materialized view is an implicit table, RENAME the target table to a new name to prevent data loss when the materialized view is dropped later.

   -- Rename the implicit target table to a new name to protect data
   RENAME TABLE <DATABASE>.`.inner_id.<TABLE_UUID>` TO <DATABASE>.<new_target_table_name>;

4. Delete the Kafka/RabbitMQ engine tables and their downstream materialized views.

   -- Drop materialized views first
   DROP TABLE <DATABASE>.<MATERIALIZED_VIEW_NAME>;
   -- Then drop Kafka/RabbitMQ engine tables
   DROP TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

Step 2: create a migration task

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Clusters page, select the Clusters of Community-compatible Edition tab, then click the ID of the destination cluster.

3. In the left navigation pane, click Data Migration and Synchronization > Migration from ClickHouse.

4. Click Create Migration Task.

5. Configure the source and destination instances, then click Test Connectivity and Proceed.

   If the connection test fails, reconfigure the instances as directed by the error message.

   

6. Review the migration content details, then click Next: Pre-detect and Start Synchronization.

7. The system runs a precheck with the following checks:

   Check	Requirement
   Instance status	No management tasks (scale-out, configuration changes) running on either cluster
   Storage space	Destination disk storage >= 1.2x source disk storage (excluding cold storage)
   Local table and distributed table	Each local table in the source cluster has exactly one distributed table

   • If the precheck succeeds:

     1. Review the impact details on the page.

     2. Set the Time of Stopping Data Writing.

        Set the write-stop window to at least 30 minutes to improve the migration success rate. The end date must be no later than 5 days from today. Schedule the window during off-peak hours to minimize business impact.

     3. Click Completed to create and start the task.

   • If the precheck fails: Resolve the issues as indicated, then retry the migration.

Step 3: assess whether the migration can complete

Skip this step if the source cluster's write speed is less than 20 MB/s.

If the source cluster's write speed exceeds 20 MB/s, check that the destination cluster can keep up:

1. Open View cluster monitoring information and check the Disk throughput metric for the destination cluster.

2. Compare the two write speeds:

   • Destination write speed >= source write speed: The migration has a high success rate. Proceed to Step 4.

   • Destination write speed < source write speed: The migration may fail. Cancel the migration task and perform a manual migration instead.

Step 4: monitor the migration task

1. On the Clusters page, select the Clusters of Community-compatible Edition tab, then click the ID of the destination cluster.

2. In the left navigation pane, click Migration from ClickHouse. The migration list shows the Migration Status, Running Information, and Data Write-Stop Window for each task.

   When the estimated remaining time in the Running Information column drops to 10 minutes or less and the status is Migrating, the write-stop logic triggers. See Potential impacts for what happens in each scenario.

   Important

   • If the source cluster contains Kafka/RabbitMQ engine tables: when the migration task enters the data migration phase (that is, table schema migration is complete), perform Step 5 to rebuild Kafka/RabbitMQ engine tables on the source cluster so that incremental data resumes flowing and is synchronized to the destination cluster.

   • Shortly before the configured write-stop window, perform Step 6 to delete the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster to prevent message backlog during the write-stop period from causing data inconsistency.

Step 5: rebuild Kafka/RabbitMQ engine tables on the source cluster

After the migration task enters the data migration phase (that is, table schema migration is complete), use the previously saved DDL statements to rebuild the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster. Once rebuilt, incremental data resumes flowing and is automatically synchronized to the destination cluster.

-- Rebuild Kafka/RabbitMQ engine tables on the source cluster
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

-- Rebuild materialized view (pointing to the renamed target table)
CREATE MATERIALIZED VIEW <database>.<materialized_view_name> TO <database>.<new_target_table_name>
AS SELECT ... FROM <database>.<kafka_or_rabbitmq_table_name>;

Step 6: delete Kafka/RabbitMQ engine tables on the source cluster

Shortly before the configured write-stop window, delete the Kafka/RabbitMQ engine tables and their downstream materialized views on the source cluster to stop incremental data writes and ensure consistency of the final data synchronization.

-- Drop materialized views first
DROP TABLE <DATABASE>.<MATERIALIZED_VIEW_NAME>;
-- Then drop Kafka/RabbitMQ engine tables
DROP TABLE <DATABASE>.<KAFKA_OR_RABBITMQ_TABLE_NAME>;

Step 7: rebuild Kafka/RabbitMQ engine tables on the destination cluster

After the migration task is complete, use the previously saved DDL statements to rebuild the Kafka/RabbitMQ engine tables and their downstream materialized views on the destination cluster to restore the incremental data consumption pipeline.

-- Rebuild Kafka/RabbitMQ engine tables on the destination cluster
CREATE TABLE <database>.<kafka_or_rabbitmq_table_name> (...)
ENGINE = Kafka/RabbitMQ
SETTINGS ...;

-- Rebuild materialized view
CREATE MATERIALIZED VIEW <database>.<materialized_view_name> TO <database>.<target_table_name>
AS SELECT ... FROM <database>.<kafka_or_rabbitmq_table_name>;

Step 8: (Optional) cancel the migration task

1. On the Clusters page, select the Clusters of Community-compatible Edition tab, then click the ID of the destination cluster.

2. In the left navigation pane, click Migration from ClickHouse.

3. In the Actions column for the target task, click Stop Migration.

4. In the Stop Migration dialog box, click OK.

The task list may not update immediately after cancellation. Refresh the page to check the latest status. After cancellation, the Migration Status changes to Completed. Before starting a new migration, clear the migrated data from the destination cluster to avoid data duplication.

Step 9: (Optional) modify the write-stop time window

1. On the Clusters page, select the Clusters of Community-compatible Edition tab, then click the ID of the destination cluster.

2. In the left navigation pane, click Migration from ClickHouse.

3. In the Actions column for the target task, click Modify Data Write-Stop Time Window.

4. In the Modify Data Write-Stop Time Window dialog box, select a new Write-stop Time. The same rules that apply when creating the task also apply here.

5. Click OK.

What's next

After confirming that all business data has been successfully migrated to the destination cluster, delete the source cluster.

For deletion instructions, see Delete a cluster.