Data Transmission Service:Synchronize data from a PolarDB for MySQL cluster to an AnalyticDB for PostgreSQL instance

Source database

• Tables to synchronize must have a primary key or a UNIQUE constraint with unique fields. Without this, duplicate data may appear in the destination database.

• For table-level synchronization where you need to edit the tables (such as mapping column names), a single task supports a maximum of 1,000 tables. If you exceed this limit, split the tables across multiple tasks or configure a task to synchronize the entire database.

• Binary log retention must be at least 3 days. If DTS cannot obtain the binary logs, the task may fail—in extreme cases causing data inconsistency or data loss. Issues from a retention period shorter than required are not covered by the DTS Service-Level Agreement (SLA).

• Do not run DDL operations that modify primary keys or add table comments (for example, ALTER TABLE table_name COMMENT='Table comment';) during synchronization. These operations will fail.

• Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. The task will fail. During full synchronization, DTS queries the source database, creating metadata locks that can block DDL operations on the source.

• If the source database contains date values of 0000-00-00 00:00:00, the task may fail. DTS converts these values to null in the destination. As a workaround, change the source values to 0001-01-01 00:00:00 or configure the destination field to allow null values.

Other limitations

• Only table-level synchronization is supported.

• The following data types are not supported: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, TXID_SNAPSHOT.

• Prefix indexes are not supported. If the source has prefix indexes, synchronization may fail.

• Synchronization from a read-only node of the source PolarDB for MySQL cluster is not supported.

• OSS foreign tables cannot be synchronized from the source.

• The following object types are not supported: PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, FK, INDEX.

• Append-Only (AO) tables are not supported as destination tables.

• Primary/secondary failover is not supported during initial full data synchronization. If a failover occurs, reconfigure the task.

• Online DDL tools: pt-online-schema-change is not supported and will cause the task to fail. For online DDL in temporary table mode or function-based indexes on unique key columns, data loss or task failure may occur.

• If you use column mapping for non-full table synchronization, or if the source and destination table schemas are inconsistent, data in columns missing from the destination table will be lost.

• If the table to synchronize has a primary key, the primary key column in the destination table must match the source table. If the table lacks a primary key, the primary key column in the destination table must match the distribution key.

• The unique key in the destination table—including the primary key column—must include all columns in the distribution key.

• Initial full data synchronization runs concurrent INSERT operations, which causes fragmentation. As a result, the tablespace of the destination instance may be larger than that of the source after full synchronization completes.

• Schedule synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases and may increase the database load.

• For table-level synchronization, do not write data other than the data from DTS to the destination database. This may cause data inconsistency between source and destination.

• For table-level synchronization, do not use pt-online-schema-change for online DDL operations on synchronization objects in the source database.

• For table-level synchronization, if no data other than data from DTS is written to the destination database, you can use Data Management (DMS) for online DDL operations. See Change schemas without locking tables.

• If a task fails, DTS support staff will attempt to restore it within 8 hours. They may restart the task or adjust DTS task parameters (not database parameters). Parameters that may be adjusted are listed in Modify instance parameters.

Conflict resolution behavior

Usage notes

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log offset.

• When using DMS or gh-ost for online DDL changes, configure the Copy the temporary table of the Online DDL tool advanced setting accordingly.

Step 1: Open the task configuration page

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

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

   The navigation path may vary based on the DMS console mode and layout. See Simple mode console and Customize the layout and style of the DMS console. You can also go directly to the Data Synchronization Tasks page of the DTS console.

4. Click Create Task.

Step 2: Configure source and destination databases

On the task configuration page, fill in the following fields:

After filling in all fields, click Test Connectivity and Proceed.

Make sure that the DTS IP address blocks have been added to the security settings of both the source and destination databases—either automatically by DTS or manually. See Add the IP address whitelist of DTS servers.

Step 3: Configure synchronization objects

On the Configure Objects page, configure the following settings:

Synchronization types

DTS always selects Incremental Data Synchronization. Also select Schema Synchronization and Full Data Synchronization (selected by default). After the precheck, DTS initializes the destination instance with the full data of the selected source objects, which serves as the baseline for subsequent incremental synchronization. With all three types enabled, DTS:

1. Copies the schema to the destination.

2. Loads a full snapshot of the selected source objects as the baseline.

3. Applies ongoing incremental changes from the binary log.

Objects and operations

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following options:

Step 5: Configure data verification (optional)

Click Data Verification to set up a data verification task. See Configure data verification.

Step 6: Configure destination table fields (optional)

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, and Distribution Key for each destination table in AnalyticDB for PostgreSQL.

This step is available only when Schema Synchronization is selected. Set Definition Status to All to modify the fields. To form a composite primary key, specify multiple columns in Primary Key Column, then select one or more of those columns as the Distribution Key. See Manage tables and Define table distribution.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

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

Before a synchronization task starts, DTS performs a precheck. The task starts only if the precheck passes.

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

• If the precheck returns warnings:

  • For non-ignorable warnings, click View Details, fix the issue, and rerun the precheck.

  • For ignorable warnings, click Confirm Alert Details, then Ignore, then OK, then Precheck Again to proceed. Ignoring warnings may lead to data inconsistencies. Proceed with caution.

Step 8: Purchase and start the instance

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

On the Purchase page, select the billing method and instance class:

Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start and confirm in the dialog box.

Monitor the task progress on the Data Synchronization Tasks page.Data Synchronization Tasks page of the new DTS console