ClickHouse Sync Plan (cksync-plan)
A skill for planning ClickHouse cluster data migration solutions, including migration plans, risks, and considerations.
When to Use
- Data migration between different ClickHouse clusters
- Horizontal scaling (adding/removing nodes) for ClickHouse clusters
- Disk downgrade operations
- Cross-availability zone migrations
- Upgrading to multi-replica, multi-AZ deployments
Workflow
Step 1: Gather Source Cluster Information
Ask user for source cluster type:
- Self-built ClickHouse or non-Alibaba Cloud ClickHouse
- Alibaba Cloud ClickHouse Community Edition
- Alibaba Cloud ClickHouse Enterprise Edition
Ask user for source cluster version (e.g., 20.8, 22.8, 23.8, 24.3):
- Version affects migration method compatibility
- BACKUP/RESTORE requires ≥22.8
- Incremental cksync migration requires target ≥20.8
Step 2: Gather Target Cluster Information
Ask user for target cluster type:
- Alibaba Cloud ClickHouse Community Edition
- Alibaba Cloud ClickHouse Enterprise Edition
- To be determined
Step 3: Collect Cluster Details (REQUIRED)
This step is mandatory. You MUST collect database and table information before proceeding to migration plan selection.
Required Information
- Database list with engines
- Table list with engines, partition counts, data sizes, and write speeds
Option A: User Executes SQL
Provide SQL queries from references/sql.md section 1 for user to execute:
- Database Information - Query for database names and engines
- Table Information - Comprehensive query including table names, engines, engine_full (for TTL), partition counts, data sizes, and write speeds
Key fields to collect:
- : Contains TTL clause (e.g.,
TTL event_time + INTERVAL 7 DAY
- : Partition count per table
- : Data size per shard
write_speed_bytes_per_sec
For complete SQL queries, see references/sql.md section 1.
Option B: Direct Query via HTTP
Request connection details from user:
- : Cluster endpoint (e.g.,
cc-xxx.clickhouse.rds.aliyuncs.com
- : HTTP port (default: )
- : Database username
- : Database password
Use secure credential handling and HTTP query examples from references/sql.md section 5.
Analysis Checklist
After collecting data, verify:
Step 4: Business Requirements
Ask for allowed read-only time:
- 0 minutes
- Within 30 minutes
- Within 1 day
- Not sure yet
Step 5: Select and Present Migration Plan
Based on gathered information, analyze and recommend from these migration methods:
| Method | Best For | Min Read-Only Time |
|---|
| Console (cksync) | Most migrations to Alibaba Cloud | ~10 min |
| BACKUP/RESTORE | Large data, same edition type, version ≥22.8 | Varies by data size |
| INSERT FROM REMOTE | Flexible control, small-medium data | ~10 min per batch |
| Business Double-Write | Zero downtime required | 0 |
| Kafka Double-Write | Existing Kafka pipelines or business writes switched to Kafka | 0 |
| Big Cluster Federation | Large scale, complex scenarios | 0 |
Hard requirement: MUST output a plan, never output empty content.
Even when information is incomplete, you MUST output a provisional migration plan.
The provisional plan must include:
- assumptions used,
- missing-information checklist,
- confidence level and key uncertainties,
- next steps to finalize recommendation after user provides missing inputs.
Migration Methods Overview
1. Console (cksync) Migration
Default choice for most Alibaba Cloud migration scenarios, especially in-place operations.
For support boundaries, engine constraints, TTL/write-speed checks, merge risk, and resource prerequisites, see references/plans.md section 1.
2. BACKUP/RESTORE Migration
Suitable for same-edition migrations where full backup/restore workflow is acceptable.
For version/edition constraints, supported engines, command patterns, and progress monitoring, see references/plans.md section 2.
3. INSERT FROM REMOTE Migration
Best when fine-grained table/partition/time-range control is needed.
For applicability boundaries and operational constraints, see references/plans.md section 3.
For SQL templates and detailed steps, see references/sql.md section 2.
4. Business Double-Write
Use when zero downtime is required and application-side dual-write is feasible.
For detailed conditions, see references/plans.md section 4.
5. Kafka Double-Write
Use when dual-consumer switchover via Kafka is feasible, including both existing Kafka pipelines and cases where business writes can be switched to Kafka first.
For detailed conditions, see references/plans.md section 5.
6. Big Cluster Federation
Advanced option for large/complex migrations with strong business and technical collaboration.
- Community + Enterprise: See references/big-cluster-community-enterprise.md
- Self-built + Cloud: See references/big-cluster-self-built-community.md
Output Format
Default deliverable: Produce one migration plan only. Structure it using assets/migration-plan-template.md and include the key sections below (cluster facts and commands may appear inline in the plan; that counts as the single deliverable).
Additional files only on request: Do not create separate files for cluster-information documentation, scripts, or SQL unless the customer explicitly asks for them. When they do, use assets/cluster-info-template.md for cluster documentation and place scripts/SQL in clearly named files as requested.
Key sections in the migration plan:
- Executive Summary - Method, data size, duration, downtime
- Source Cluster Analysis - Databases, tables, compatibility check
- Migration Method Selection - Rationale and alternatives
- Migration Steps - Pre/execution/post with commands
- Risks & Mitigations - With probability and impact
- Rollback Plan - Trigger conditions and steps
- Timeline - Phase schedule with owners
- Reference Links - Documentation URLs
Method Selection Reference
For quick scenario-to-method mapping and method-specific constraints (including in-place migration priority and Enterprise → Enterprise options), see references/plans.md section "Method Selection Priority" and related method sections.
Additional Resources
- references/plans.md - Detailed migration plan conditions
- references/sql.md - SQL templates and commands
- references/stop-merge-storm.md - How to stop post-sync merge storm
- references/big-cluster-community-enterprise.md - Community + Enterprise federation
- references/big-cluster-self-built-community.md - Self-built + Cloud federation