ApsaraDB for ClickHouse:Migrate between Enterprise clusters

Use cases

• Migrate data from a restored backup to a target cluster.

• Promote data across environments: development to pre-production, or pre-production to production.

How it works

The migration runs as a single SQL statement. One cluster connects to the other through the remote function, reads the specified table, and writes rows into a local table.

The process is:

1. Create a read-only user on the source cluster and get the table schema.

2. Recreate the table on the destination cluster.

3. Configure whitelists so the two clusters can connect.

4. Run the migration statement.

5. (Optional) Clean up credentials and whitelist entries.

Prerequisites

Before you begin, ensure that you have:

• Access to both the source and destination clusters

• Admin privileges on both clusters (to create users and modify whitelists)

Step 1: Set up the source cluster

Create a read-only user on the source cluster. This user is used only during migration and is removed afterward.

CREATE USER exporter
IDENTIFIED WITH SHA256_PASSWORD BY 'password-here'
SETTINGS readonly = 1;

Grant the user read access to the table you want to migrate:

GRANT SELECT ON test_db.test_tb TO exporter;

Get the table schema — you need this to recreate the table on the destination cluster:

SELECT create_table_query
FROM system.tables
WHERE database = 'test_db' AND TABLE = 'test_tb';

The output is a CREATE TABLE statement similar to:

CREATE TABLE test_db.test_tb (`id` UInt32, `name` String, ...) ENGINE = MergeTree ORDER BY id;

Copy this statement. You'll use it in the next step.

Step 2: Create the table on the destination cluster

Create the target database:

CREATE DATABASE test_db;

Paste the CREATE TABLE statement from Step 1 to create the table:

CREATE TABLE test_db.test_tb ...

Step 3: Configure whitelists

The source and destination clusters must be able to reach each other. Configure both whitelists:

• Add the source cluster's IP address to the destination cluster's whitelist.

• Add the destination cluster's IP address to the source cluster's whitelist.

To find a cluster's IP address, run:

SELECT * FROM system.clusters;

Tip: If the destination cluster has an existing whitelist that you need to restore after migration, note the current entries before adding the temporary migration entry. This makes it easier to revert to the original access controls after cleanup.

Step 4: Run the migration

Run the following statement on the destination cluster. It reads data from the source cluster using the remote function and inserts it into the destination table.

INSERT INTO test_db.test_tb
SELECT *
FROM remote(
  'source-hostname:9000', -- Endpoint and port of the source cluster
  'test_db',              -- Source database name
  'test_tb',              -- Source table name
  'exporter',             -- Username
  'password-here'         -- User password
);

Replace source-hostname with the hostname or IP address of the source cluster.

Step 5: Clean up (optional)

After migration is complete, remove the temporary access that was set up for the migration:

1. If the destination cluster will serve as the new production environment, configure its whitelist to allow only the intended production traffic.

2. Drop the read-only user from the source cluster:

   DROP USER exporter;

3. Remove the destination cluster's IP address from the source cluster's whitelist.

What's next

• Configure the whitelist to restrict access to your clusters after migration.