Data Transmission Service:Synchronize data from an ApsaraDB RDS for MySQL instance to an ApsaraDB RDS for PostgreSQL instance

Source database requirements

• Tables must have PRIMARY KEY or UNIQUE constraints, with all fields unique. Otherwise, duplicate records may appear in the destination database.

• If you select tables as synchronization objects and need to rename tables or columns, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or synchronize the entire database in one task.

• Do not run DDL statements during schema synchronization or full data synchronization. DDL changes during these phases cause the task to fail.

• Data changes from physical backup restoration or cascade operations are not recorded or synchronized to the destination database while the data synchronization instance is running. If this occurs, you can remove the affected databases and tables from the synchronization objects and then re-add them. For more information, see Modify the objects to be synchronized.

• MySQL 8.0.23 and later: If the data to be synchronized includes invisible columns, DTS cannot read those columns and data loss occurs. To make columns visible, run:

  ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

  Tables without primary keys automatically generate invisible primary keys — make those visible as well. See Invisible Columns and Generated Invisible Primary Keys.

• If the EncDB feature is enabled on the source instance, full data synchronization cannot be performed. Instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

Binary log requirements

Objects and data types

• Only tables can be selected as synchronization objects. DTS does not synchronize views, triggers, or stored procedures.

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

• Prefix indexes cannot be synchronized. If the source database contains prefix indexes, the task may fail.

• Do not use tools such as pt-online-schema-change for online DDL operations during table-level synchronization. Use Data Management (DMS) instead.

Foreign key handling

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.

During full data synchronization and incremental data synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you run cascade update or delete operations on the source database during synchronization, data inconsistency may occur.

If you use a privileged or superuser account for the destination database and the tables contain foreign keys, triggers, or event triggers, DTS temporarily sets session_replication_role to replica at the session level. If the account does not have the required permissions, set session_replication_role to replica manually. After the task is released, change the value back to origin.

Performance considerations

• Evaluate the impact on source and destination database performance before starting. Run synchronization during off-peak hours.

• During initial full data synchronization, DTS uses read and write resources of both databases, which increases server load.

• Concurrent INSERT operations during initial full data synchronization cause fragmentation in destination tables. After full data synchronization completes, the destination tablespace will be larger than the source.

• Do not write data to the destination database from other sources during synchronization, as this may cause data inconsistency or loss.

Synchronization latency

DTS calculates synchronization latency based on the timestamp of the latest synchronized record in the destination and the current timestamp in the source. If no DML operations run on the source for an extended period, the reported latency may be inaccurate. Run a DML operation on the source to refresh the latency reading.

Tip: If you synchronize an entire database, create a heartbeat table that receives updates every second to keep the latency accurate.

DTS also runs CREATE DATABASE IF NOT EXISTS 'test' on the source database on a schedule to advance the binary log file position.

DTS task failure recovery

If a DTS task fails, DTS technical support will attempt to restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be modified.

Step 1: Open the Data Synchronization Tasks 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.

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

Step 2: Select the region

On the right side of Data Synchronization Tasks, select the region where the data synchronization instance resides.

In the new DTS console, select the region in the top navigation bar.

Step 3: Configure source and destination databases

Click Create Task. In the Create Data Synchronization Task wizard, configure the following parameters.

Source Database

Destination Database

Step 4: Test connectivity and proceed

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances such as ApsaraDB RDS for MySQL. For self-managed databases on Elastic Compute Service (ECS) instances, DTS adds the CIDR blocks to the ECS security group rules — make sure the ECS instance can access the database. For self-managed databases in data centers or from third-party cloud providers, manually add the DTS server CIDR blocks to the database whitelist. See Add the CIDR blocks of DTS servers.

Step 5: Configure synchronization objects and advanced settings

Basic settings

Advanced settings

Step 6: Save settings and run the precheck

• To view the API parameters for this 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 can start. If the precheck fails, click View Details next to each failed item, resolve the issues based on the check results, and then click Precheck Again. If a precheck item triggers an alert that can be ignored, click Confirm Alert Details, click Ignore in the dialog box, click OK, and then click Precheck Again. Ignoring alert items may result in data inconsistency.

Step 7: Wait for the precheck to pass

Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

Step 8: Purchase the synchronization instance

On the purchase page, configure the following parameters.

Step 9: Start the task

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

The task appears in the task list. Track its progress from there.