All Products
Search
Document Center

AnalyticDB:Use DTS to synchronize data from a user-created MySQL database connected by a leased line, VPN Gateway, or Smart Access Gateway to an AnalyticDB for PostgreSQL instance

Last Updated:Sep 22, 2026

This topic describes how to use Data Transmission Service (DTS) to continuously synchronize data from a self-managed MySQL database — connected to Alibaba Cloud over Express Connect, VPN Gateway, or Smart Access Gateway — to an AnalyticDB for PostgreSQL instance.

Prerequisites

Before you begin, ensure that you have:

Limitations

Supported objects: Tables only. Databases and other object types cannot be selected as synchronization objects.

Unsupported data types: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, TXID_SNAPSHOT, and POINT. Columns with these types are skipped during synchronization.

Prefix indexes: Prefix indexes are not synchronized. If the source database contains prefix indexes, data synchronization may fail.

DDL operations: Only ADD COLUMN is synchronized. CREATE TABLE is not supported — to synchronize data from a new table, add the table to the selected objects manually. For instructions, see Add an object to a data synchronization task.

Online schema change tools: Do not use gh-ost or pt-online-schema-change to perform DDL operations on source objects while synchronization is running. Doing so may cause the synchronization task to fail.

SQL operations that can be synchronized

  • DML: INSERT, UPDATE, DELETE

  • DDL: ADD COLUMN

Supported synchronization topologies

  • One-way one-to-one synchronization

  • One-way one-to-many synchronization

  • One-way many-to-one synchronization

Term mappings

MySQL AnalyticDB for PostgreSQL
Database Schema
Table Table

Performance considerations

DTS reads from the source database and writes to the destination database during initial full data synchronization. This increases load on both database servers. To reduce risk:

  • Run the synchronization task during off-peak hours, when CPU utilization on both source and destination databases is below 30%.

  • Avoid running large numbers of slow SQL queries on the source database during synchronization.

  • Verify that source tables have primary keys before starting (tables without primary keys significantly increase resource usage).

Set up data synchronization

Step 1: Purchase a DTS synchronization instance

Purchase a data synchronization instance as described in Purchase a DTS instance.

On the buy page, set the following options:

  • Source Instance: MySQL

  • Target Instance: AnalyticDB for PostgreSQL

  • Synchronization Topology: One-Way Synchronization

Step 2: Configure the synchronization channel

  1. Log on to the DTS console.

  2. In the left-side navigation pane, click Data Synchronization.

  3. At the top of the Synchronization Tasks page, select the region where the destination instance resides.

  4. Find the synchronization instance you purchased, then click Configure Synchronization Channel in the Actions column.

  5. Configure the source and destination instances.

    Source instance

    Parameter Description
    Synchronization Task Name DTS auto-generates a name. Specify a descriptive name for easy identification. The name does not need to be unique.
    Instance Type Select User-Created Database Connected over Express Connect, VPN Gateway, or Smart Access Gateway.
    Instance Region Pre-filled from the buy page. Cannot be changed.
    Peer VPC Select the ID of the VPC connected to the self-managed MySQL database.
    Database Type Pre-filled as MySQL. Cannot be changed.
    IP Address Enter the server IP address of the self-managed MySQL database.
    Port Number Enter the service port of the source database. Default: 3306.
    Database Account Enter the MySQL account you created for DTS. The account must have SELECT, REPLICATION CLIENT, REPLICATION SLAVE, and SHOW VIEW permissions.
    Database Password Enter the password for the database account.

    Destination instance

    Parameter Description
    Instance Type Pre-filled as AnalyticDB for PostgreSQL. Cannot be changed.
    Instance Region Pre-filled from the buy page. Cannot be changed.
    Instance ID Select the destination AnalyticDB for PostgreSQL instance.
    Database Name Enter the name of the destination database.
    Database Account Enter the initial account of the AnalyticDB for PostgreSQL instance, or an account with the RDS_SUPERUSER role. For details, see Create a database account and Manage users and permissions.
    Database Password Enter the password for the database account.

    Configure the source and destination instances

  6. Click Set Whitelist and Next.

    DTS automatically adds its server CIDR blocks to the whitelist of the destination AnalyticDB for PostgreSQL instance, allowing DTS to connect to the instance.
  7. Select the synchronization policy and objects. Objects to synchronize In the Available section, select the tables to synchronize and click Rightwards arrow to move them to the Selected section.

    Only tables can be selected as synchronization objects. To rename objects in the destination instance, use the object name mapping feature. For details, see Rename an object to be synchronized.
    DTS charges for the synchronization instance during retry periods. Release the DTS instance promptly if the source and destination instances are released.

    Synchronization policy

    Setting Options
    Initial Synchronization In most cases, select both Initial Schema Synchronization and Initial Full Data Synchronization. DTS synchronizes the schemas and existing data from the source to the destination before starting incremental synchronization.
    Processing Mode of Conflicting Tables Clear Target Table: Skips the Schema Name Conflict precheck item and clears destination table data before the initial full sync. Use this when running a production sync after testing. Ignore: Skips the Schema Name Conflict precheck item and appends to existing data during initial full sync. Use this when merging data from multiple tables into one.
    Synchronization Type Select the DML and DDL operations to synchronize: Insert, Update, Delete, AlterTable.

    Additional settings

    Setting Description
    Rename Databases and Tables Use object name mapping to rename objects in the destination. See Object name mapping.
    Replicate Temporary Tables When DMS Performs DDL Operations If you use Data Management (DMS) for online DDL on the source, choose whether to replicate the temporary tables DMS generates. Yes: Replicates temporary table data (may cause synchronization lag if DDL generates large data volumes). No: Replicates only the original DDL changes; does not replicate temporary tables (may cause table locks in the destination).
    Retry Time for Failed Connections The time window in which DTS retries a failed connection before marking the task as failed. Default: 720 minutes (12 hours). Set this based on your business requirements. If DTS reconnects within the window, the task resumes automatically.

    Synchronize data from MySQL to AnalyticDB for PostgreSQL

  8. If you selected Initial Schema Synchronization, specify the primary key column and distribution column for each table on the next page. For background on these settings, see Define constraints and Define table distribution.

    Specify primary key and distribution columns

  9. Click Precheck.

    DTS runs a precheck before starting the task. The task cannot start until it passes the precheck. If any precheck item fails, click the 提示 icon next to the failed item to view details, fix the issue, and run the precheck again. If the failed item is not critical to your use case, you can ignore it and re-run the precheck.
  10. After the message The precheck is passed. appears, close the Precheck dialog box. The synchronization task starts automatically.

  11. Wait for the initial synchronization to complete. The task status changes to Synchronizing when incremental synchronization begins. Monitor the task status on the Synchronization Tasks page.

    View the status of a data synchronization task