Synchronize data from an RDS for PostgreSQL instance to an RDS for MySQL instance-Data Transmission Service(DTS)-阿里云帮助中心

You can use Data Transmission Service (DTS) to synchronize data from an ApsaraDB RDS for PostgreSQL instance to an ApsaraDB RDS for MySQL instance.

Prerequisites

An ApsaraDB RDS for PostgreSQL instance is created as the source. For more information, see Quickly create an RDS PostgreSQL instance.
An ApsaraDB RDS for MySQL instance is created as the destination. For more information, see Step 1: Create an RDS MySQL instance and configure a database.
The destination ApsaraDB RDS for MySQL instance has more available storage space than the total data size in the source ApsaraDB RDS for PostgreSQL instance.

Billing

Synchronization type	Cost
Schema synchronization and full data synchronization	Free of charge.
Incremental data synchronization	Charged. For more information, see Billing overview.

Supported synchronization topologies

One-way one-to-one synchronization
One-way one-to-many synchronization
One-way cascade synchronization
One-way many-to-one synchronization

For more information, see Synchronization topologies.

SQL operations that can be synchronized

Operation type	SQL statement
DML (Data Manipulation Language)	INSERT, UPDATE, and DELETE

Usage notes

Foreign key behavior

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 constraint checks and cascade operations on foreign keys at the session level.

Note

If you perform the cascade update and delete operations on the source database during data synchronization, data inconsistency may occur.

Source database requirements

Primary keys and unique constraints

Tables to be synchronized must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, the destination database may contain duplicate data records.

Note

If a destination table already exists because Schema Synchronization was not selected for the Synchronization Types parameter, make sure the table has the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table. Otherwise, the destination database may contain duplicate data records.

Table count limit

When you select tables as the objects to synchronize and edit them (for example, renaming tables or columns in the destination database), a single data synchronization task supports up to 5,000 tables. To synchronize more than 5,000 tables, configure multiple tasks or synchronize the entire database.

WAL log requirements

Set the wal_level parameter to logical.
Incremental-only synchronization: Retain WAL logs for more than 24 hours.
Full and incremental synchronization: Retain WAL logs for at least seven days. After full data synchronization completes, you can reduce the retention period to more than 24 hours.

Warning

If DTS cannot obtain WAL logs because the retention period is insufficient, the task may fail. In exceptional circumstances, data inconsistency or loss may occur. Compliance with these retention requirements is necessary for DTS to honor the Service Level Agreement (SLA).

Operations on the source database

Primary/secondary switchover: Enable the Logical Replication Slot Failover feature before you perform a primary/secondary switchover on the source ApsaraDB RDS for PostgreSQL instance. This prevents logical subscriptions from being interrupted and keeps the data synchronization task running as expected. For more information, see Logical Replication Slot Failover.
Large data records: If a single data record to be synchronized from the source database exceeds 256 MB during incremental data changes, the running data synchronization instance fails and cannot be recovered. You must configure the task again.
DDL restrictions: Do not execute DDL (Data Definition Language) statements to change database or table schemas during schema synchronization and full data synchronization. Otherwise, the task fails.

Long-running transactions

If the source database has long-running transactions while incremental data synchronization is in progress, WAL logs generated before the long-running transactions are committed may accumulate. This may exhaust the disk space of the source database.

Major version upgrades

Do not perform a major version upgrade on the source database while a data synchronization instance is running. A major version upgrade causes the instance to fail, and the failure is unrecoverable. You must configure the task again.

Destination database requirements

Character set for 4-byte characters

If the data to be synchronized contains characters that occupy four bytes, such as emojis or rare characters, the destination databases and tables must use the utf8mb4 character set.

Note

If you use the schema synchronization feature of DTS, set the character_set_server instance parameter in the destination database to utf8mb4.

Synchronization restrictions

One database per task

A data synchronization task can synchronize data from only one database. To synchronize data from multiple databases, create a separate task for each database.

Cross-schema inheritance

DTS cannot synchronize tables that have inheritance relationships across schemas.

New or renamed tables in schema-level synchronization

When you select a schema as the synchronization object and create a new table or rename a table with the RENAME command, run the following statement before writing data to the table:

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Replace schema and table with the actual schema and table names. This ensures data consistency.

Note

Run this operation during off-peak hours. Do not lock the table when executing this statement. Otherwise, a deadlock occurs.

Column name case sensitivity in MySQL

Column names in MySQL databases are not case-sensitive. If multiple columns in the source database have names that differ only in capitalization, they are written to the same column in the destination MySQL database during synchronization. This can cause unexpected results.

DTS-managed objects in the source database

Temporary tables

DTS creates the following temporary tables in the source database for incremental DDL statements, table schemas, and heartbeat information. Do not delete these tables during data synchronization. They are automatically deleted when the DTS 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

Note

The DDL statements obtained through these temporary tables are not written to the destination database.

Heartbeat table

DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to maintain accurate synchronization latency measurements.

Replication slot

DTS creates a replication slot prefixed with dts_sync_ in the source database. The slot retrieves incremental logs from the source database within the last 15 minutes.

Note

The replication slot is automatically deleted when the DTS instance is released. However, if you change the password of the source database or remove the CIDR blocks of DTS servers from the IP address whitelist, the replication slot cannot be automatically deleted. In this case, manually delete the replication slot to prevent log pileup.

Note

If the data synchronization task is released or fails, DTS automatically deletes the replication slot. If a primary/secondary switchover is performed on the source ApsaraDB RDS for PostgreSQL instance, log on to the secondary database and delete the replication slot manually.

Performance and operations

Performance considerations

Evaluate the impact of data synchronization on the source and destination databases before starting. Synchronize data during off-peak hours when possible. During initial full data synchronization, DTS uses read and write resources of both databases, which may increase server load.
During initial full data synchronization, concurrent INSERT operations cause fragmentation in the destination tables. As a result, the destination tablespace is larger than the source tablespace after full synchronization completes.

Online DDL with Data Management Service (DMS)

If only DTS writes table data to the destination database, you can use DMS to perform online DDL operations on source tables during data synchronization. For more information, see Perform lockless schema changes using lockless change tickets.

Warning

If data from other sources is written to the destination database during synchronization, data inconsistency occurs. If you use DMS to execute online DDL statements while other sources write to the destination database, data loss may occur.

Failed DDL statements

If DDL statements fail to execute in the destination database, the DTS task continues to run. View the failed DDL statements in the task logs. For more information, see View task logs.

Post-synchronization verification

After data synchronization completes (the Status of the instance changes to Completed), run the following command to verify that data is written to the destination table:

analyze table <Table name>

Note

If a high-availability (HA) switchover is triggered in the source MySQL database, data may be written only to memory, which causes data loss.

DTS task failure recovery

If a DTS task fails, DTS technical support will attempt to restore the task within 8 hours. During this process, the task may be restarted, and task parameters may be modified.

Note

Only task parameters may be modified; database parameters are not changed. Parameters that may be modified include but are not limited to those described in the Modify instance parameters section of the Modify the parameters of a DTS instance topic.

Source-specific requirements

ApsaraDB RDS for PostgreSQL source: Do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance during data synchronization. Otherwise, the task fails.
Self-managed PostgreSQL source: The values of the max_wal_senders and max_replication_slots parameters must be greater than the sum of used replication slots in the self-managed PostgreSQL database plus the number of DTS instances to be created for this database.
Google Cloud SQL for PostgreSQL source: Set the Database Account parameter to a database account with the cloudsqlsuperuser permission. Select only objects that the specified account is authorized to manage. Alternatively, grant the OWNER permission on the selected objects to the specified account.
Note
An account with the cloudsqlsuperuser permission cannot manage data owned by other accounts that have the cloudsqlsuperuser permission.

Procedure

Go to the Data Synchronization page of the new DTS console.
Note
Alternatively, log on to the DMS console. In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.
In the upper-left corner of the page, select the region where the data synchronization instance resides.

Click Create Task. Configure the source and destination databases.

Warning

After you configure the source and destination databases, read the Limits displayed on the page. Failing to observe these limits may cause task failure or data inconsistency.

Note

If the source ApsaraDB RDS for PostgreSQL instance version is 9.4 and you synchronize only DML operations, only the REPLICATION permission is required for the database account.

Task settings

Parameter	Value
Task Name	DTS automatically generates a task name. Specify a descriptive name that makes it easy to identify the task. A unique task name is not required.

Source database settings

Parameter	Value
Database Type	Select PostgreSQL.
Connection Type	Select Alibaba Cloud Instance.
Instance Region	The region of the source ApsaraDB RDS for PostgreSQL instance.
Instance ID	The ID of the source ApsaraDB RDS for PostgreSQL instance.
Database Name	The name of the source database in the ApsaraDB RDS for PostgreSQL instance.
Database Account	A privileged account of the source ApsaraDB RDS for PostgreSQL instance. The account must be the owner of the database. For more information, see Create an account and Create a database.
Database Password	The password of the database account.

Destination Database

Parameter	Value
Database Type	Select MySQL.
Connection Type	Select Alibaba Cloud Instance.
Instance Region	The region of the destination ApsaraDB RDS for MySQL instance.
RDS Instance ID	The ID of the destination ApsaraDB RDS for MySQL instance.
Database Account	The database account of the destination ApsaraDB RDS for MySQL instance. The account must have read and write permissions on the destination database.
Database Password	The password of the database account.
Encryption	Select Non-encrypted or SSL-encrypted based on your business requirements. To use SSL-encrypted, enable SSL encryption on the ApsaraDB RDS for MySQL instance before configuring the DTS task. For more information, see Use a cloud certificate to enable SSL encryption.

Click Test Connectivity and Proceed. If the source or destination database is an Alibaba Cloud database instance (such as an ApsaraDB RDS for MySQL instance or an ApsaraDB for MongoDB instance), DTS automatically adds the CIDR blocks of DTS servers to the whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure the ECS instance can access the database. If the database is deployed on multiple ECS instances, manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database deployed in a data center or provided by a third-party cloud service provider, manually add the CIDR blocks of DTS servers to the whitelist of the database. For more information, see the Whitelist DTS server IP addresses section of the Add the CIDR blocks of DTS servers topic.
Warning
Adding the CIDR blocks of DTS servers to the whitelist of the database or instance, or to the ECS security group rules, may introduce security risks. Before using DTS, understand and acknowledge the potential risks. Take preventive measures, including but not limited to: enhancing username and password security, limiting exposed ports, authenticating API calls, regularly reviewing the whitelist or ECS security group rules to remove unauthorized CIDR blocks, or connecting the database to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.

Configure the objects to synchronize and advanced settings.

Note

- If identical table names exist in the source and destination databases and you cannot delete or rename the destination tables, use the object name mapping feature. For more information, see Object name mapping. - If you select Ignore Errors and Proceed and the source and destination databases have the same schema with matching primary key or unique key values: during full data synchronization, DTS does not synchronize the conflicting record and retains the existing record in the destination. During incremental data synchronization, DTS overwrites the existing record. If the schemas differ, data initialization may fail, only some columns may be synchronized, or the task may fail.

Note

- To select SQL operations for a specific database or table, right-click an object in the Selected Objects section and select the operations to synchronize. For supported operations, see the SQL operations that can be synchronized section. - To filter data with WHERE conditions, right-click an object in the Selected Objects section and specify filter conditions. For more information, see Set filter conditions.

Note

- If you specify different retry time ranges for multiple tasks that share the same source or destination database, the shortest retry time range takes precedence. - DTS charges for the instance during the retry period. Set the retry time based on your business requirements. Release the DTS instance promptly after the source and destination instances are released.

Basic settings

Parameter	Value
Synchronization Types	Incremental Data Synchronization is selected by default. You can select only Full Data Synchronization. You cannot select Schema Synchronization. When Full Data Synchronization is selected, DTS synchronizes historical data of the selected objects from the source database to the destination database after the precheck completes. The historical data serves as the basis for subsequent incremental synchronization.
Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks whether the destination database contains tables with the same names as tables in the source database. If identical table names exist, an error is returned during the precheck and the task cannot start. Ignore Errors and Proceed: Skips the precheck for identical table names. See the notes below for behavior details.
Source Objects	Select one or more objects from the Source Objects section and click the right-arrow icon to add them to the Selected Objects section. You can select columns, tables, or databases as objects to synchronize. If you select tables or columns, DTS does not synchronize other objects such as views, triggers, and stored procedures.
Selected Objects	To rename an object in the destination instance, right-click it in Selected Objects. For more information, see Object name mapping. To rename multiple objects at once, click Batch Edit in the upper-right corner of the Selected Objects section. For more information, see Object name mapping.

Advanced settings

Parameter	Value
Monitoring and Alerting	Specifies whether to configure alerting for the task. If the task fails or the synchronization latency exceeds the specified threshold, alert contacts receive notifications. Select No to disable alerting, or Yes to configure alert thresholds and notification settings. For more information, see Configure monitoring and alerting.
Retry Time for Failed Connections	The duration (in minutes) that DTS retries a connection if the source or destination database becomes unreachable after the task starts. Valid values: 10 to 1440. Default: 720. Recommended: greater than 30. If DTS reconnects within this period, the task resumes. Otherwise, the task fails.
Configure ETL	Specifies whether to enable extract, transform, and load (ETL). Select Yes to enter data processing statements in the code editor, or No to skip. For more information, see What is ETL? and Configure ETL in DTS migration or synchronization tasks.

Save the task settings and run a precheck.
- To view the API parameters for configuring this DTS task, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
- When ready, click Next: Save Task Settings and Precheck.
Note
- DTS performs a precheck before the task can start. The task can start only after it passes the precheck. - If the precheck fails, click View Details next to each failed item, analyze the causes, troubleshoot the issues, and rerun the precheck. - If an alert is triggered during the precheck: - If the alert item cannot be ignored, click View Details, troubleshoot the issue, and click Precheck Again. - If the alert item can be ignored, click Confirm Alert Details. In the View Details dialog box, click Ignore, then click OK. Click Precheck Again to rerun the precheck. Ignoring alert items may cause data inconsistency and expose your business to potential risks.
Wait until the Success Rate reaches 100%, then click Next: Purchase Instance.

On the buy page, configure the billing method and instance class for the data synchronization instance.

Section	Parameter	Value
New Instance Class	Billing Method	Subscription: Pay upfront when creating the instance. More cost-effective for long-term use. Pay-as-you-go: Billed hourly. Suitable for short-term use. Release the instance when it is no longer needed to reduce costs.
	Resource Group Settings	The resource group to which the data synchronization instance belongs. Default value: default resource group. For more information, see What is Resource Management?
	Instance Class	Select an instance class based on your synchronization speed requirements. For more information, see Instance classes of data synchronization instances.
	Subscription Duration	Available only for the Subscription billing method. Options: one to nine months, one year, two years, three years, or five years.

Read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start. In the confirmation dialog box, click OK. The task progress appears in the task list.