Synchronize data from an RDS PostgreSQL instance to an RDS MySQL instance - Data Transmission Service

Prerequisites

Before you begin, make sure you have:

An ApsaraDB RDS for PostgreSQL instance to use as the source. See Create an ApsaraDB RDS for PostgreSQL instance.
An ApsaraDB RDS for MySQL instance to use as the destination. See Create an ApsaraDB RDS for MySQL instance.
Available storage space on the destination instance that exceeds the total data size of the source PostgreSQL instance.

Limitations

Review the following limitations before you configure the task. Some limitations require action before you start.

Source database requirements

Table constraints

Tables to be synchronized must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Without these constraints, the destination database may contain duplicate rows.

Note

If you create the destination table manually (without selecting Schema Synchronization in Synchronization Types), the destination table must have the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table. Otherwise, duplicate rows may appear.

Table count limit

If you select individual tables as sync objects and need to rename tables or columns at the destination, a single task supports up to 5,000 tables. For more than 5,000 tables, split into multiple tasks or sync at the database level.

WAL log requirements

DTS uses PostgreSQL logical replication (write-ahead logging) to capture incremental changes. Configure the following before you start the task:

Set wal_level to logical on the source instance.
For incremental-only sync: retain WAL logs for more than 24 hours.
For full data sync + incremental sync: retain WAL logs for at least 7 days. After the full sync phase completes, you can reduce retention to more than 24 hours.

Insufficient WAL log retention causes DTS to fail to read change logs, which can result in task failure or data loss.

WAL disk space

Long-running transactions on the source database prevent WAL logs from being released. If a sync task falls behind or the source has uncommitted long-running transactions, WAL files can accumulate and exhaust source disk space. Monitor source disk usage and WAL log retention during the sync.

DTS creates a replication slot prefixed with dts_sync_ on the source database. The slot retains up to 15 minutes of incremental logs. After the DTS instance is released, the replication slot is automatically deleted. If the data synchronization task is released or fails, DTS automatically deletes the replication slot. If you change the source database password or remove DTS CIDR blocks from the whitelist, delete the replication slot manually to prevent accumulation.

Note

If a primary/secondary switchover occurs on the source instance, log in to the secondary database to delete the replication slot.

Primary/secondary switchover

If you plan to perform a primary/secondary switchover on the source ApsaraDB RDS for PostgreSQL instance during the sync, enable the Logical Replication Slot Failover feature first. See Logical Replication Slot Failover.

Single-row data size

If a single incremental change exceeds 256 MB, the sync instance fails and cannot be recovered. Reconfigure the task if this occurs.

DDL restrictions

Do not run DDL statements to change database or table schemas during the schema synchronization or full data synchronization phases. This causes task failure.

Major version upgrades

A major version upgrade of the source database while the sync is running causes task failure that cannot be recovered. Reconfigure the task after the upgrade completes.

Other limitations

One task can sync from one database only. Create separate tasks for multiple databases.
DTS cannot sync tables that have inheritance relationships across schemas.
DTS synchronizes foreign keys from the source database to the destination database during schema synchronization. During full data synchronization and incremental data synchronization, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. If you perform cascade update or delete operations on the source database during the sync, data inconsistency may occur.
When syncing at the schema level: if you create a new table in the schema or rename a table with RENAME, run the following statement before writing data to that table:
```
ALTER TABLE schema.table REPLICA IDENTITY FULL;
```
Replace schema and table with the actual names. Run this statement during off-peak hours and do not lock the table—locking can cause a deadlock.
DTS creates the following temporary tables in the source database to capture DDL statements, schema changes, and heartbeat information. Do not delete them during the sync; they are removed automatically 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, public.aliyun_dts_instance
DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to measure sync latency.
If the data to be synced contains 4-byte characters (such as rare characters or emoji), the destination database and tables must use the UTF8mb4 character set. If you use DTS schema synchronization, set character_set_server on the destination instance to UTF8mb4.
Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases. This may increase the loads on the database servers.
After the initial full data sync, the destination tablespace may be larger than the source because concurrent INSERT operations cause fragmentation.
Writing data from sources other than DTS to the destination during a sync can cause data inconsistency or data loss.
MySQL column names are case-insensitive. If multiple source columns differ only in capitalization, they are written to the same destination column, which may produce unexpected results.
If a DDL statement fails at the destination, the DTS task continues running. View failed DDL statements in task logs. See View task logs.
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. We recommend that you release the DTS instance at your earliest opportunity after the source and destination instances are released.
After the sync status changes to Completed, run the following command to verify that data was written correctly to the destination:
```
ANALYZE TABLE <table_name>;
```

Special cases

Source type	Additional requirement
ApsaraDB RDS for PostgreSQL	Do not modify the endpoint or zone of the source instance during the sync.
Self-managed PostgreSQL	Set `max_wal_senders` and `max_replication_slots` to a value greater than the sum of existing replication slots and the number of DTS instances that sync from this database.
Google Cloud Platform Cloud SQL for PostgreSQL	Set Database Account to an account with the `cloudsqlsuperuser` permission. Grant OWNER permission on the selected sync objects to this account if the account does not already have it. Note that a `cloudsqlsuperuser` account cannot manage objects owned by other `cloudsqlsuperuser` accounts.

Billing

Synchronization type	Cost
Schema synchronization + full data synchronization	Free
Incremental data synchronization	Charged. See Billing overview.

Supported synchronization topologies

DTS supports the following one-way topologies for this sync path:

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

For details, see Synchronization topologies.

SQL operations that can be synchronized

Operation type	SQL statements
DML	INSERT, UPDATE, DELETE

Note

DDL statements are not synchronized to the destination database.

Create a data synchronization task

Go to the Data Synchronization page in the DTS console. Alternatively, log in to the DMS console, and in the top navigation bar choose Data + AI > DTS (DTS) > Data Synchronization.
In the upper-left corner, select the region where the sync instance will reside.

Click Create Task. In the Create Data Synchronization Task wizard, configure the source and destination databases.

Warning

Read the Limits displayed on the page after configuring source and destination. Skipping this step can result in task failure or data inconsistency.

Source Database

Parameter	Value
Database Type	PostgreSQL
Connection Type	Alibaba Cloud Instance
Instance Region	Region of the source ApsaraDB RDS for PostgreSQL instance
Instance ID	ID of the source instance
Database Name	Name of the source database
Database Account	A privileged account that owns the source database. See Create an account and Create a database. For PostgreSQL 9.4 with DML-only sync, the REPLICATION permission is sufficient.
Database Password	Password for the database account

Destination Database

Parameter	Value
Database Type	MySQL
Connection Type	Alibaba Cloud Instance
Instance Region	Region of the destination ApsaraDB RDS for MySQL instance
RDS Instance ID	ID of the destination instance
Database Account	An account with read and write permissions on the destination database
Database Password	Password for the database account
Encryption	Non-encrypted or SSL-encrypted. To use SSL encryption, enable SSL on the destination RDS MySQL instance first. See Use a cloud certificate to enable SSL encryption.

Click Test Connectivity and Proceed. DTS automatically adds its CIDR blocks to the whitelist of Alibaba Cloud database instances and to the security group rules of Elastic Compute Service (ECS)-hosted databases. For self-managed databases deployed across multiple ECS instances, manually add DTS CIDR blocks to each instance's security group. For on-premises databases or databases hosted by third-party cloud providers, manually add DTS CIDR blocks to the database whitelist. See CIDR blocks of DTS servers.

Warning
Adding DTS CIDR blocks to your whitelist or security group exposes those blocks to network access. Take preventive measures such as using strong credentials, limiting exposed ports, regularly reviewing whitelist entries, and connecting DTS through Express Connect, VPN Gateway, or Smart Access Gateway.

Configure the objects to sync and advanced settings.

Basic settings

Parameter	Description
Synchronization Types	Incremental Data Synchronization is selected by default. Select Full Data Synchronization to include a historical data load before incremental sync begins. Schema Synchronization cannot be selected for this sync path.
Processing Mode of Conflicting Tables	Choose how to handle tables that exist in both source and destination. Precheck and Report Errors (default): the precheck fails if identically named tables exist at the destination. Use object name mapping to rename destination tables if they cannot be deleted. Ignore Errors and Proceed: skips the check. During full sync, existing destination rows are kept; during incremental sync, conflicting rows are overwritten. If source and destination schemas differ, only some columns may sync or the task may fail.
Source Objects	Select columns, tables, or databases. Views, triggers, and stored procedures are not synced when you select tables or columns.
Selected Objects	Right-click an object to rename it, select specific SQL operations to sync, or add WHERE filter conditions. Click Batch Edit to rename multiple objects at once. See Map object names and Specify filter conditions.

Advanced settings

Parameter	Description
Monitoring and Alerting	Set to Yes to receive alerts when the task fails or sync latency exceeds a threshold. Configure the alert threshold and contacts. See Configure monitoring and alerting.
Retry Time for Failed Connections	How long DTS retries a failed connection before marking the task as failed. Range: 10–1,440 minutes. Default: 720. Set this to at least 30 minutes. If multiple tasks share the same source or destination database, the shortest retry time takes precedence. DTS charges continue during retries.
Configure ETL	Set to Yes to transform data in transit using the code editor. See Configure ETL and What is ETL?.

Click Next: Save Task Settings and Precheck. To preview the API parameters for this task, move your pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.

Note
The task must pass the precheck before it can start. If the precheck fails, click View Details next to each failed item, resolve the issue, and click Precheck Again. For alert items that can be safely ignored, click Confirm Alert Details > View Details > Ignore > OK, then click Precheck Again.
Wait until the Success Rate reaches 100%, then click Next: Purchase Instance.

On the buy page, configure the instance billing and class settings.

Parameter	Description
Billing Method	Subscription: pay upfront for a fixed term (1–9 months, or 1, 2, 3, or 5 years). More cost-effective for long-term use. Pay-as-you-go: billed hourly. Suitable for short-term use; release the instance when no longer needed to stop charges.
Instance Class	Determines sync throughput. See Instance classes of data synchronization instances.
Resource Group Settings	Resource group for the sync instance. Default: default resource group. See What is Resource Management?.
Subscription Duration	Available only for the Subscription billing method.

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

The task appears in the task list. Monitor its progress there.

What's next

To perform online DDL operations on source tables without interrupting the sync, use Data Management Service (DMS). See Change schemas without locking tables. This is only safe when DTS is the sole writer to the destination. Writing from other sources simultaneously causes data inconsistency.
To check whether the sync instance parameters need tuning, see Modify instance parameters.