Migrate data from an ApsaraDB RDS for PostgreSQL instance to an ApsaraDB for ClickHouse cluster

Source database

Bandwidth and object requirements

• The source database server must have at least 100 Mb/s of outbound bandwidth. Lower bandwidth reduces migration speed.

• Tables must have primary keys or UNIQUE constraints with unique field values. Otherwise, duplicate records may appear in the destination database.

  If you create the destination table manually (without selecting Schema migration), the table must have the same primary key or a non-empty UNIQUE constraint as the source table.

• The source database name cannot contain a hyphen (-). For example, dts-testdata is not supported.

• When migrating at the table level with column mapping, a single task supports a maximum of 1,000 tables. To exceed this limit, split the migration across multiple tasks or migrate at the database level.

Unsupported objects

DTS does not support migrating the following object types:

• TimescaleDB extension tables

• Tables with cross-schema inheritance

• Tables with expression-based unique indexes

Write-ahead log (WAL) requirements for incremental data migration

• Set wal_level to logical on the source instance.

• Retain WAL logs for at least 24 hours for an incremental-only task. For a combined full and incremental task, retain WAL logs for at least 7 days.

  Important

  If DTS cannot retrieve WAL logs due to insufficient retention, the task may fail or cause data inconsistency or data loss. Such issues are not covered by the DTS Service-Level Agreement (SLA).

• If the source database has long-running transactions, WAL logs before the transaction commit cannot be cleared, which may cause logs to accumulate and exhaust disk space.

Operation restrictions

• Do not perform DDL operations using non-standard syntax during migration. This may cause the task to fail or result in data loss.

• Do not perform a major engine version upgrade on the source database while the data migration instance is running. The instance will fail and cannot be recovered—you must reconfigure it.

• Do not perform DDL operations on the source schema or tables during full data migration. The task will fail.

• If a single incremental data record exceeds 256 MB, the data migration instance may fail and cannot be recovered. You must reconfigure it.

• For full data migration without incremental data migration: do not write new data to the source database during migration. To maintain real-time data consistency, select both Full Data Migration and Incremental Data Migration.

Destination database

• A single task migrates data from one database only. Configure a separate task for each additional source database.

• ApsaraDB for ClickHouse has range limits for time-type data. If a value falls outside the supported range, the migrated value will be incorrect. See Time information for the supported ranges.

• Do not select nullable fields for the Partition Key. The partition key supports only BIGINT, INT, TIMESTAMP, DATETIME, and DATE types.

• The total number of databases to migrate must not exceed 256, which is the ApsaraDB for ClickHouse limit.

• Database, table, and column names must follow ApsaraDB for ClickHouse naming conventions. See Object naming conventions.

DDL operations for incremental data migration

The following table shows how DTS handles DDL changes during incremental data migration.

Schema migration behavior

During schema migration, DTS adds three system columns to each destination table: _sign, _is_deleted, and _version. If you create the destination table manually, add these columns yourself. See Table and field requirements for data types and default values.

Incremental data migration behavior

Before writing data to source tables, run the following command on each table to be migrated. This ensures data consistency when the data migration instance runs for the first time, or when the migration object granularity is Schema and a new table is created or rebuilt using RENAME.

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Replace schema and table with the actual schema name and table name. Run this command during off-peak hours to avoid locking the tables.

If you skip the related precheck item, DTS runs this command automatically during instance initialization.

Replication slot: DTS creates a replication slot with the prefix dts_sync_ in the source database to replicate data. This slot retains incremental logs from the last 15 minutes. When the task fails or the instance is released, DTS attempts to clear the slot automatically.

Temporary tables: DTS creates the following temporary tables in the source database to track DDL statements, incremental table structure, and heartbeat information. Do not delete them during migration—they are removed automatically when the instance is released.

public.dts_pg_class, public.dts_pg_attribute, public.dts_pg_type, public.dts_pg_enum, public.dts_postgres_heartbeat, public.dts_ddl_command, public.dts_args_session, and public.aliyun_dts_instance

Partitioned tables

When migrating partitioned tables, include both the parent table and all its child partitions as migration objects. The parent table in a PostgreSQL partitioned table does not store data directly—all data resides in the child partitions. Omitting any child partition causes data inconsistency.

Other considerations

• Full data migration uses concurrent INSERT operations, which creates fragmentation in the destination tables. Destination tables may use more storage space than the source tables after the full migration completes.

• If another data source writes to the destination database while the data migration instance is running, data inconsistency may occur and the instance may fail.

• DTS validates data content but does not validate metadata such as sequences. Validate this metadata separately.

• If the task fails, DTS technical support attempts to recover it within 8 hours. Recovery may involve restarting the task or adjusting task parameters (database parameters are not modified). For adjustable parameters, see Modify instance parameters.

• Full data migration increases the read and write load on both the source and destination databases. Run the migration during off-peak hours when the CPU load on both databases is below 30%.

Special cases

• When the source instance is an ApsaraDB RDS for PostgreSQL instance, do not change its endpoint or zone during migration. The migration will fail.

• When the source is a self-managed PostgreSQL database, make sure that max_wal_senders and max_replication_slots are each greater than the sum of the replication slots currently in use and the number of DTS instances you plan to create with this database as the source.

• When the source is Google Cloud Platform Cloud SQL for PostgreSQL, use an account with the cloudsqlsuperuser permission for the Database Account field. Select only objects that this account has permission to manage, or grant the account Owner permission for the objects by running:

  An account with cloudsqlsuperuser permission cannot manage data owned by another account with cloudsqlsuperuser permission.

  GRANT <owner_of_objects_to_migrate> TO <source_db_account_for_task>;

Step 1: Go to the data migration task list

Use one of the following methods to reach the task list for the destination region.

From the DTS console

1. Log on to the Data Transmission Service (DTS) console.

2. In the left navigation pane, click Data Migration.

3. In the upper-left corner, select the region where the data migration instance is located.

From the DMS console

The actual navigation may vary based on the DMS console mode and layout. See Simple mode console and Customize the layout and style of the DMS console.

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, choose Data + AI > Data Transmission (DTS) > Data Migration.

3. To the right of Data Migration Tasks, select the region where the data migration instance is located.

Step 2: Configure source and destination databases

Click Create Task. On the task configuration page, configure the following settings.

Task settings

Source database

Destination database

Click Test Connectivity and Proceed at the bottom of the page.

Add the DTS server IP address ranges to the security settings (whitelist) of the source and destination databases. See Add DTS server IP addresses to a whitelist.

If the source or destination database uses an access method other than Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Configure migration objects

On the Configure Objects page, set the following parameters.

If you use the object name mapping feature, other objects that depend on the mapped object may fail to migrate.

Click Next: Advanced Settings.

Step 4: Configure advanced settings

For multiple DTS instances sharing the same source or destination, the network retry time is determined by the last created task. During the retry period, the task is still charged—release the DTS instance promptly if the source or destination database instance is released.

Step 5: Configure ClickHouse table fields

Click Next: Configure Database and Table Fields to configure the Type, Primary Key Column, Sort Key, Distribution Key, and Partition Key for each destination table in ApsaraDB for ClickHouse.

DTS provides a default configuration. To modify it, set Definition Status to All.

• Primary Key Column and Sort Key support composite keys. Select one or more columns from the drop-down list.

• Select one column from the Primary Key Column to serve as the Partition Key. Only one column is allowed for the Distribution Key.

• Leave the Partition Key blank if not needed. Do not select a nullable field—this causes the task to fail. The partition key supports only BIGINT, INT, TIMESTAMP, DATETIME, and DATE types.

For details on primary keys, sort keys, and partition keys, see CREATE TABLE.

Step 6: Save the task and run the precheck

To preview the API parameters for the task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

Click Next: Save Task Settings and Precheck. DTS runs a precheck before the task starts.

The task starts only after passing the precheck.

If the precheck fails, click View Details next to the failed item, fix the issue, and run the precheck again.

For warning items that cannot be ignored, fix the issue and rerun the precheck. For items that can be safely ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alert items may cause data inconsistency.

Step 7: Purchase and start the instance

1. When the Success Rate reaches 100%, click Next: Purchase Instance.

2. On the Purchase page, configure the following:

   Parameter	Description
   Resource Group Settings	Select the resource group for the instance. Default: default resource group. See What is Resource Management?
   Instance Class	Select a link specification based on the required migration speed. See Data migration link specifications.

3. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then click OK in the confirmation dialog.

After the task starts, monitor progress on the Data Migration Tasks list page.

• Tasks without incremental data migration stop automatically when complete. The Status changes to Completed.

• Tasks with incremental data migration run continuously. The Status shows Running during normal operation.

Time information

ApsaraDB for ClickHouse has range limits for time-type data. Values outside these ranges will be incorrect after migration.

Table and field requirements

-- Local table
CREATE TABLE orders_local ON CLUSTER default
(
    id          Int64,
    user_id     Int64,
    amount      Float64,
    created_at  DateTime,
    _sign       Int8    DEFAULT 1,
    _is_deleted UInt8   DEFAULT 0,
    _version    UInt64  DEFAULT 1
)
ENGINE = ReplicatedReplacingMergeTree(_version, _is_deleted)
ORDER BY (id);

-- Distributed table (name must match the source table name)
CREATE TABLE orders ON CLUSTER default
AS orders_local
ENGINE = Distributed(default, default, orders_local, rand());

SELECT *
FROM orders FINAL
WHERE _sign > 0;

Partition key calculation logic