All Products
Search

This Product

    AnalyticDB:Synchronize RDS for MySQL to serverless AnalyticDB for PostgreSQL

    Document Center

    AnalyticDB:Synchronize RDS for MySQL to serverless AnalyticDB for PostgreSQL

    Last Updated:Jun 29, 2026

    Data Transmission Service (DTS) supports data synchronization from an ApsaraDB RDS for MySQL instance to a serverless AnalyticDB for PostgreSQL instance. You can use this feature to centralize your enterprise data for analysis.

    Prerequisites

    • A source ApsaraDB RDS for MySQL instance is created. For more information, see Create a RDS MySQL instance.

    • You must have a target AnalyticDB for PostgreSQL instance in serverless mode with a kernel version of V1.0.3.1 or later.

    Supported MySQL types

    You can synchronize data from the following MySQL source databases to an AnalyticDB for PostgreSQL instance. This topic uses an ApsaraDB RDS for MySQL instance to demonstrate the configuration. The procedure is similar for other source databases.

    • ApsaraDB RDS for MySQL instance

    • Self-managed database that is hosted on Elastic Compute Service (ECS)

    • Self-managed database that is connected over Express Connect, VPN Gateway, or Smart Access Gateway

    • Self-managed database that is connected over Database Gateway

    • Self-managed database that is connected over Cloud Enterprise Network (CEN)

    In addition to MySQL, DTS supports PostgreSQL, SQL Server, and DB2 as data sources. For more information about supported databases, see Supported databases.

    Usage notes

    Note

    By default, DTS disables foreign key constraints when synchronizing data to the destination database. Therefore, DTS does not synchronize cascade and delete operations from the source database.

    Type

    Description

    Source database limits

    • The tables to synchronize must have a primary key or a unique constraint, and the fields must be unique. Otherwise, duplicate data may appear in the destination database.

    • If you synchronize at the table level and need to edit mappings (such as column name mapping), each synchronization task supports up to 1,000 tables. If you exceed this limit, the task fails with an error. To fix this, split the tables across multiple tasks or configure a full-database synchronization task.

    • Binary logs:

      • ApsaraDB RDS for MySQL enables binary logging by default. Ensure that the binlog_row_image parameter is set to full. Otherwise, the precheck fails and the synchronization task cannot start. For instructions, see Configure instance parameters.

        Important

        • If your source instance is a self-managed MySQL database, enable binary logging and set binlog_format to row and binlog_row_image to full.

        • If your self-managed MySQL database is a dual-primary cluster (where both nodes act as primary and secondary), enable the log_slave_updates parameter so DTS can capture all binary log events. For instructions, see Create an account and configure binary logging for a self-managed MySQL database.

      • The local binary logs for an ApsaraDB RDS for MySQL instance must be retained for at least three days (seven days is recommended). For a self-managed MySQL database, retain local binary logs for at least seven days. Otherwise, DTS may fail to retrieve binary logs, causing the task to fail. In extreme cases, this may cause data inconsistency or data loss. Issues caused by binary log retention periods shorter than DTS requires are not covered under the DTS SLA.

        Note

        To configure the retention period for local binary logs on an ApsaraDB RDS for MySQL instance, see Automatically delete local logs.

    • Do not run DDL operations that modify primary keys or add comments—such as ALTER TABLE table_name COMMENT='Table comment';—while synchronization is running. Otherwise, the DDL operation fails.

    • If your source database contains date values of 0000-00-00 00:00:00, the task may fail.

      Note

      DTS converts this date to null in the destination database. Temporarily change the source data to 0001-01-01 00:00:00 or set the corresponding destination column to allow null values.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • Data generated by changes that do not write to binary logs—such as data restored from physical backups or created by cascade operations—is not synchronized to the destination database.

      Note

      If this occurs, remove the affected database or table from the synchronization objects. Then add it back. You can do this only if your business allows it. For more information, see Modify synchronization objects.

    • If your source database is MySQL 8.0.23 or later and contains invisible hidden columns, DTS may not read those columns. This may cause data loss.

      Note

      Run the ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE; command to make the hidden column visible. For more information, see Invisible Columns.

    Other limits

    • Synchronization object requirements:

      • Only table-level synchronization is supported.

      • DTS does not support synchronizing VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, TXID_SNAPSHOT, or POINT data types.

      • DTS does not support synchronizing prefix indexes. If your source database contains prefix indexes, synchronization may fail.

      • DTS does not support synchronizing partitions, views, stored procedures, functions, triggers, foreign keys, or indexes.

    • If your source database uses online DDL operations in temporary table mode—including but not limited to multi-table merge scenarios—or adds function-based indexes to unique key columns, data loss or task failure may occur in the destination database.

    • If a primary key or unique key conflict occurs while the task is running:

      • If the table schemas are consistent and a record in the destination database has the same primary key or unique key value as a record in the source database:

        • During full data synchronization, DTS retains the destination record and skips the source record.

        • During incremental synchronization, DTS overwrites the destination record with the source record.

      • If the table schemas are inconsistent, data initialization may fail. This can result in only partial data synchronization or a complete synchronization failure. Use with caution.

    • 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 to synchronize 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.

    • Assess the performance of both the source and destination databases before starting synchronization. Run synchronization during off-peak hours. Otherwise, full initialization consumes read and write resources on both databases and may increase database load.

    • Full initialization runs INSERT operations concurrently. This fragments destination tables. After full initialization, the tablespace of the destination instance is larger than that of the source instance.

    • If you synchronize one or more tables—not a full database—do not use tools like pt-online-schema-change to perform online DDL operations on the source tables. Otherwise, synchronization fails.

      You can use Data Management (DMS) to perform online DDL operations. For more information, see Online DDL without locking tables.

    • Do not write data to the destination database except through DTS while synchronization is running. Otherwise, data inconsistency may occur between the source and destination databases. For example, if you use DMS to perform online DDL operations while other data is written to the destination database, data loss may occur.

    • The destination table does not support append-optimized (AO) tables.

    • If you use column mapping and do not synchronize full tables—or if the source and destination table schemas do not match—data in columns missing from the destination side is lost.

    • If your ApsaraDB RDS for MySQL instance has Always-Encrypted enabled, full data synchronization is not supported.

      Note

      ApsaraDB RDS for MySQL instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

    • If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters.

      Note

      Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.

    Special cases

    • For a self-managed MySQL source database:

      • If a primary/secondary switchover occurs in the source database during synchronization, the task fails.

      • DTS calculates latency by comparing the timestamp of the last synchronized record with the current time. If no DML operations run for a long time in the source database, latency reporting may become inaccurate. If latency appears too high, run a DML operation in the source database to update the latency.

        Note

        If you select a full database for synchronization, create a heartbeat table. Update or write to this table every second.

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

      • If your source database is Amazon Aurora MySQL or another clustered MySQL instance, ensure the domain name or IP address used in the task configuration—and its DNS resolution—always points to a read/write (RW) node. Otherwise, synchronization may fail.

    • For an ApsaraDB RDS for MySQL source database:

      • Read-only instances—such as ApsaraDB RDS for MySQL 5.6 read-only instances—that do not record transaction logs cannot serve as source databases.

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

    Billing

    Synchronization type

    Pricing

    Schema synchronization and full data synchronization

    Free of charge.

    Incremental data synchronization

    Charged. For more information, see Billing overview.

    Supported synchronization topologies

    • One-to-one, one-way synchronization.

    • One-to-many, one-way synchronization.

    • Many-to-one, one-way synchronization.

    Supported SQL operations

    • DML operations: INSERT, UPDATE, and DELETE.

    • DDL operation: ADD COLUMN.

      Note

      The CREATE TABLE operation is not supported. To synchronize a new table, you must add it as a synchronization object.

    Term mappings

    MySQL

    AnalyticDB for PostgreSQL

    Database

    Schema

    Table

    Table

    Procedure

    1. Log on to the Data Synchronization Tasks page of the new DTS console.

      Note

      You can also log on to the Data Management Service (DMS) console. In the top menu bar, choose Integration and Development (DTS). In the left-side navigation pane, choose Data Synchronization.

    2. In the upper-left corner, select the region of the data synchronization instance.

    3. Click Create Task to configure the source and destination databases.

      Category

      Parameter

      Description

      N/A

      Job Name

      DTS automatically generates a task name. We recommend using a descriptive name for easy identification. The name does not need to be unique.

      Source Database

      Select Instance

      Select an existing ApsaraDB RDS for MySQL instance. This parameter is optional.

      Database Engine

      Select MySQL.

      Access Method

      Select Alibaba Cloud Instance.

      Instance Region

      Select the region where the source ApsaraDB RDS for MySQL instance is located.

      Replicate Data Across Alibaba Cloud Accounts

      This tutorial demonstrates data synchronization within a single Alibaba Cloud account. Select No.

      RDS Instance ID

      Select the ID of the source ApsaraDB RDS for MySQL instance.

      Account

      Enter the database account of the source ApsaraDB RDS for MySQL instance. The account must have the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT permissions.

      Database Password

      Enter the password for the database account.

      Encryption

      Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must first enable SSL encryption on the ApsaraDB RDS for MySQL instance. For more information, see Enable SSL encryption.

      Destination Database

      Select Instance

      Select an existing AnalyticDB for PostgreSQL in Serverless mode instance. This parameter is optional.

      Database Type

      Select AnalyticDB for PostgreSQL.

      Access Method

      Select Alibaba Cloud Instance.

      Instance Region

      Select the region where the destination AnalyticDB for PostgreSQL in Serverless mode instance is located.

      Cluster ID

      Select the ID of the destination AnalyticDB for PostgreSQL in Serverless mode instance.

      Database Name

      Enter the name of the database in the destination AnalyticDB for PostgreSQL in Serverless mode instance to which the objects will be synchronized.

      Database Account

      Enter the initial account of the destination AnalyticDB for PostgreSQL in Serverless mode instance.

      Note

      You can also use an account that has the RDS_SUPERUSER permission. For more information about how to create an account, see Manage users and permissions.

      Database Password

      Enter the password for the database account.

    4. After the configuration is complete, click Test Connectivity and Proceed to configure synchronization objects and advanced settings.

      Note

      • DTS automatically adds the CIDR blocks of DTS servers in the corresponding region to the whitelist of the ApsaraDB RDS for MySQL instance. You do not need to manually add them. For a list of CIDR blocks, see IP address blocks of DTS servers.

      • After the data synchronization task is complete or released, manually remove the CIDR blocks of the DTS servers from the whitelist.

      1. Configure the task steps and synchronization objects.

        Parameter

        Description

        Task Stages

        Incremental Data Synchronization is selected by default. You must also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS performs a full data synchronization of the selected objects from the source instance to the destination instance. This process creates a baseline for incremental data synchronization.

        Processing Mode of Conflicting Tables

        • Precheck and Report Errors: DTS checks if the destination database contains tables with the same names as the source tables. If duplicates are found, the precheck fails and the task cannot start. Otherwise, the precheck passes.

          Note

          If a table with a duplicate name in the destination database cannot be easily deleted or renamed, you can change the table name in the destination database. For more information, see Map object names.

        • Ignore Errors and Proceed: Skips the check for tables with the same names in the destination database.

          Warning

          If you select Ignore Errors and Proceed, data inconsistency may occur and pose risks to your business. Examples:

          • If the table schemas are identical and a record in the destination database has the same primary key value as a record in the source database:

            • During full data synchronization, DTS retains the record in the destination database. The record from the source database is not synchronized to the destination database.

            • During incremental data synchronization, DTS does not retain the record in the destination database. The record from the source database overwrites the record in the destination database.

          • If the table schemas are different, the synchronization may fail or only some columns may be synchronized.

        DDL and DML Operations to Be Synchronized

        Select the DDL or DML operations to synchronize at the instance level. For information about supported operations, see Supported SQL operations.

        Note

        To select SQL operations at the database or table level, right-click a synchronization object in the Selected Objects box and select the desired SQL operations in the dialog box that appears.

        Select Objects

        In the Source Objects box, select an object and click 向右小箭头 to move it to the Selected Objects box.

        Note

        You can select objects to be synchronized only at the table level.

        Rename Databases and Tables

        Filter data to synchronize

        You can set a WHERE clause to filter data. For more information, see Set filter conditions.

        SQL operations to synchronize

        Right-click an object in the Selected Objects box. In the dialog box that appears, select the DML and DDL operations to synchronize. For information about supported operations, see Supported SQL operations.

      2. Click Next: Advanced Settings.

        Parameter

        Description

        Set Alerts

        Configure alerts to receive notifications if the task fails or if latency exceeds the specified threshold.

        • No: An alert is not configured.

        • Configure: An alert is configured. You must also set the alert threshold and specify alert contacts.

        Replicate temporary tables generated during DMS online DDL operations

        If you use Data Management Service (DMS) to perform online DDL changes on the source database, select whether to synchronize the data from the temporary tables generated during these changes.

        • Yes: Synchronizes data from the temporary tables that are generated by online DDL changes.

          Note

          If a large amount of data is generated in the temporary tables, the data synchronization task may be delayed.

        • No: Does not synchronize data from the temporary tables. Only the original DDL data from the source database is synchronized.

          Note

          This may cause tables to be locked in the destination database.

        Retry Time for Failed Connections

        If the data synchronization task fails to connect to a database, DTS continuously retries to connect. The default retry duration is 120 minutes. You can customize a retry duration from 10 to 1,440 minutes. We recommend that you set the duration to 30 minutes or more. If DTS reconnects to the source and destination databases within the specified retry duration, the data synchronization task automatically resumes. Otherwise, the task fails.

        Note

        • If multiple instances share a database, the shortest configured retry duration applies to all of them.

        • You are charged for the runtime of a data synchronization task during the connection retry period. We recommend that you customize the retry duration based on your business requirements, or release the DTS instance as soon as the source and destination database instances are released.

        Enclose Object Names in Quotation Marks

        Select whether to enclose destination object names in quotes. If you select Yes and one of the following conditions is met, DTS encloses the destination object names in single or double quotes during schema synchronization and incremental data synchronization:

        • The source database is case-sensitive and uses mixed case.

        • The source table name does not start with a letter and contains characters other than letters, digits, or specific special characters.

          Note

          The supported special characters are underscores (_), number signs (#), and dollar signs ($).

        • The name of the schema, table, or column to be synchronized is a keyword, reserved word, or invalid character in the destination database.

          Note

          If you select to enclose object names in quotes, you must use the quoted object names when you query data after the data synchronization is complete.

        Configure ETL

        Select whether to configure the ETL feature. For more information about ETL, see What is ETL?

    5. After completing the configuration, click Next: Configure Table And Field at the bottom of the page to set the primary key and distribution columns for the tables to be synchronized to the destination AnalyticDB for PostgreSQL.

    6. Save the task and perform a precheck.

      • To view the parameters for configuring this instance via an API operation, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the tooltip.

      • If you have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.

      Note

      • Before a synchronization task starts, DTS performs a precheck. You can start the task only if the precheck passes.

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

      • If the precheck generates warnings:

        • For non-ignorable warning, click View Details next to the item, fix the issue as prompted, and run the precheck again.

        • For ignorable warnings, you can bypass them by clicking Confirm Alert Details, then Ignore, and then OK. Finally, click Precheck Again to skip the warning and run the precheck again. Ignoring precheck warnings may lead to data inconsistencies and other business risks. Proceed with caution.

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

    8. On the Purchase page, select the billing method and link specifications for the data synchronization instance. For more information, see the following table.

      Category

      Parameter

      Description

      New Instance Class

      Billing Method

      • Subscription: You pay upfront for a specific duration. This is cost-effective for long-term, continuous tasks.

      • Pay-as-you-go: You are billed hourly for actual usage. This is ideal for short-term or test tasks, as you can release the instance at any time to save costs.

      Resource Group Settings

      The resource group to which the instance belongs. The default is default resource group. For more information, see What is Resource Management?.

      Instance Class

      DTS offers synchronization specifications at different performance levels that affect the synchronization rate. Select a specification based on your business requirements. For more information, see Data synchronization link specifications.

      Subscription Duration

      In subscription mode, select the duration and quantity of the instance. Monthly options range from 1 to 9 months. Yearly options include 1, 2, 3, or 5 years.

      Note

      This option appears only when the billing method is Subscription.

    9. Read and select the checkbox for Data Transmission Service (Pay-as-you-go) Service Terms.

    10. Click Buy and Start, and then click OK in the OK dialog box.

      You can monitor the task progress on the data synchronization page.

    FAQ

    • If a schema synchronization error persists after you confirm that the table schemas are consistent, Submit a ticket to contact technical support.

    • The VACUUM operation does not run automatically during data synchronization, which can reduce write performance. We recommend periodically running VACUUM on your database.

    • If an error occurs during full data synchronization, you must clear the data from the destination table and restart the synchronization process.

    • Instances in Serverless mode perform well with bulk writes to a single table but may perform poorly with hot data rows or multi-table, small-batch writes. For these scenarios, Submit a ticket for kernel parameter tuning.