Data Transmission Service:Two-way data synchronization between PolarDB for PostgreSQL (Compatible with Oracle) clusters

Source database limits

• Bandwidth: The server hosting the source cluster must have enough outbound bandwidth. Insufficient bandwidth degrades synchronization speed.

• Tables without a primary key or unique key: Enable the Exactly-Once write feature when configuring the task. Otherwise, duplicate records may appear in the destination. See Synchronize tables without a primary key or UNIQUE constraint.

• Large object selections with name mapping: If you need to map table or column names and the task includes more than 1,000 tables, split the tables into multiple tasks or synchronize the entire database instead. Submitting a single task that maps more than 1,000 tables may result in a request error.

• WAL log retention: If WAL logs are purged before DTS reads them, the task fails. In extreme cases, data loss or inconsistency may occur. Issues caused by insufficient WAL retention are not covered by the SLA (Service-Level Agreement).

  • Incremental synchronization only: retain WAL logs for more than 24 hours.

  • Full + incremental synchronization: retain WAL logs for at least 7 days. After full synchronization completes, you can reduce the retention period to more than 24 hours.

• Long-running transactions: WAL generated before a long-running transaction commits accumulates during incremental synchronization. This can exhaust disk space on the source cluster.

• DDL operations during synchronization: Do not perform DDL operations that change the database or table structure during schema synchronization or full data synchronization. The task will fail.

• Writes during full synchronization only: Do not write new data to the source cluster if only full data synchronization is selected. To maintain real-time consistency, also select schema synchronization and incremental data synchronization.

• Logical Replication Slot Failover: To prevent logical replication from breaking on a primary/secondary switchover, the source cluster must support and have Logical Replication Slot Failover enabled.

  If the cluster does not support Logical Replication Slot Failover (for example, if Database Engine is Oracle syntax compatible 2.0), a high-availability (HA) switchover may cause the synchronization instance to fail and become unrecoverable.

• Single change size limit: If a single incremental data change exceeds 256 MB, the synchronization instance may fail and cannot be recovered. You must reconfigure the synchronization instance.

Two-way synchronization limits

• Loop prevention schema: DTS creates a schema named dts in each destination cluster to prevent data loops. Do not modify this schema while the task is running.

• SERIAL type fields: If a table has a field of the SERIAL type, the source cluster automatically creates a sequence for that field. If you select Schema Synchronization under Synchronization Types, also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

• Task data scope: In a two-way synchronization pair, only one task can synchronize both full and incremental data. The other task supports incremental synchronization only.

• Data flow direction: Data from the source of one task synchronizes only to the destination of that task. It is not treated as source data for the other task.

• Single database per task: Each synchronization task syncs one database. Configure separate tasks for additional databases.

• Unsupported objects: DTS does not support TimescaleDB extension tables, tables with cross-schema inheritance, or tables with unique indexes based on expressions.

• Plugin-created schemas: Schemas created by installing plugins cannot be synchronized.

• `REPLICA IDENTITY FULL` requirement: Run ALTER TABLE schema.table REPLICA IDENTITY FULL; on tables in the source cluster before writing data to them in the following scenarios:

  • When the synchronization instance runs for the first time.

  • When you select Schema as the granularity for object selection, and a new table is created or an existing table is rebuilt using the RENAME command.

  • When you use the feature to modify synchronization objects.

  Replace schema and table with the actual schema name and table name. Run this command during off-peak hours. Do not lock the tables while running it — locking can cause deadlocks. If you skip the related precheck items, DTS runs this command automatically during instance initialization.

• Temporary tables: DTS creates the following temporary tables in the source cluster to capture DDL statements, table structure, and heartbeat information. Do not delete them during synchronization — the task will fail. DTS deletes them automatically when the 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, and public.aliyun_dts_instance.

• Heartbeat table: DTS adds a heartbeat table named dts_postgres_heartbeat to the source cluster to keep incremental latency measurements accurate.

• Replication slot: DTS creates a replication slot with the dts_sync_ prefix in the source cluster. This slot provides DTS access to incremental logs from the last 15 minutes. When the synchronization task fails or the instance is released, DTS attempts to clear the slot automatically.

  If you change the source database account password or remove the DTS IP address from the source cluster's whitelist during synchronization, the slot cannot be cleared automatically. Clear it manually to prevent accumulated WAL from filling the disk and making the source cluster unavailable. If a failover occurs, log on to the secondary cluster to clear the slot manually.

  

• Synchronization timing: Synchronize data during off-peak hours when the CPU load on both clusters is below 30%. Full data synchronization runs concurrent INSERT operations, which increases load on both clusters and causes table fragmentation in the destination. The destination table space may be larger than the source after full synchronization completes.

• Constraint checks during synchronization: During schema synchronization, DTS synchronizes foreign keys from the source to the destination. During full and incremental synchronization, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. If cascade update or delete operations occur in the source while the task runs, data inconsistency may occur.

• FLOAT and DOUBLE precision: DTS reads FLOAT and DOUBLE values using ROUND(COLUMN, PRECISION). If precision is not explicitly defined, DTS defaults to 38 for FLOAT and 308 for DOUBLE. Confirm that these defaults meet your requirements.

• Task recovery and write protection: DTS attempts to auto-recover failed tasks for up to 7 days. Before switching your workload to the destination cluster, end or release the task, or use REVOKE to revoke the write permissions of the DTS database account on the destination. This prevents source data from overwriting destination data if the task is recovered.

• Foreign keys, triggers, and event triggers: DTS sets session_replication_role to replica at the session level if the destination account is a privileged account or has superuser permissions. If the account does not have these permissions, set session_replication_role to replica manually on the destination. During this period, cascade update or delete operations in the source may cause data inconsistency. After the task is released, change session_replication_role back to origin.

• DTS support recovery: If a task fails, DTS support staff attempt to restore it within 8 hours and may restart the task or adjust task parameters (not database parameters). Parameters that may be adjusted are listed in Modify instance parameters.

• Partitioned tables: Include both the parent table and all child partitions as synchronization objects. The parent table of a partitioned table stores no data directly — all data is in the child partitions. Missing either the parent table or a child partition may cause data inconsistency.

Step 1: Open the synchronization task list

Go to the synchronization task list in one of two ways.

DTS console

1. Log on to the DTS console.

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

3. In the upper-left corner, select the region where the synchronization instance is located.

DMS console

Steps may vary depending on the DMS console mode and layout. See Simple mode console and Customize the layout and style of the DMS console.

1. Log on to the DMS console.

2. In the top menu bar, choose Data + AI > DTS (DTS) > Data Synchronization.

3. To the right of Data Synchronization Tasks, select the region of the synchronization instance.

Step 2: Create the forward task

1. Click Create Task.

2. Configure the source and destination databases.

   Category	Parameter	Description
   —	Task Name	DTS generates a name automatically. Specify a descriptive name for easier identification. The name does not need to be unique.
   Source Database	Select Existing Connection	Select a registered database instance from the list to auto-fill the fields below. In the DMS console, this parameter is Select a DMS database instance. If you have not registered the instance, fill in the fields manually.
   	Database Type	Select PolarDB (Compatible with Oracle).
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	Select the region where the source cluster resides.
   	Replicate Data Across Alibaba Cloud Accounts	Select No for same-account synchronization.
   	Instance ID	Select the source cluster ID.
   	Database Name	Enter the name of the source database.
   	Database Account	Enter the database account. See Database account permissions.
   	Database Password	Enter the account password.
   Destination Database	Select Existing Connection	Same as source: select a registered instance or fill in manually. In the DMS console, this is Select a DMS database instance.
   	Database Type	Select PolarDB (Compatible with Oracle).
   	Connection Type	Select Alibaba Cloud Instance.
   	Instance Region	Select the region where the destination cluster resides.
   	Instance ID	Select the destination cluster ID.
   	Database Name	Enter the name of the destination database.
   	Database Account	Enter the database account. See Database account permissions.
   	Database Password	Enter the account password.

3. Click Test Connectivity and Proceed.

   Add the DTS server CIDR blocks to the security settings of both clusters to allow DTS access. See Add the IP address whitelist of DTS servers. If the source or destination uses a self-managed access method (not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog.

4. On the Configure Objects page, set the following parameters.

   Parameter	Description
   Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. DTS synchronizes historical data first, then switches to incremental synchronization.
   Processing mode of conflicting tables	Precheck and Report Errors: checks for tables with the same name in the destination. The task does not start if any are found. To proceed, either rename the conflicting table in the destination or use object name mapping. See Database Table Column Name Mapping. Ignore Errors and Proceed: skips the check. During full synchronization, DTS keeps the destination record and skips the source record when keys conflict. During incremental synchronization, DTS overwrites the destination record. If table schemas differ, initialization may fail. Use with caution.
   Synchronization Topology	Select Two-way Synchronization.
   Exclude DDL Operations	Select Yes to skip DDL synchronization in the forward task. Select No to include it. Reverse tasks always filter DDL regardless of this setting.
   Conflict Resolution Policy	Select how DTS handles conflicts. See Supported conflict detection for details on each policy.
   Capitalization of Object Names in Destination Instance	Configure the case policy for database, table, and column names. The default is DTS default policy. See Case policy for destination object names.
   Source Objects	Click objects in the Source Objects box, then click the arrow icon to move them to Selected Objects. Select at the schema or table level. Selecting tables does not include views, triggers, or stored procedures. If tables contain SERIAL data types and Schema Synchronization is selected, also select Sequence or choose entire-schema synchronization.
   Selected Objects	To rename a single object in the destination, right-click it and follow Map a single object name. To rename multiple objects in bulk, click Batch Edit and follow Map multiple object names in bulk. To select specific SQL operations or set a WHERE filter for a table, right-click the table. See Set a filter condition. Object name mapping may prevent dependent objects from synchronizing correctly.

5. Click Next: Advanced Settings and configure the following.

   Parameter	Description
   Dedicated Cluster for Task Scheduling	DTS uses a shared cluster by default. For greater stability, purchase a dedicated cluster. See What is a DTS dedicated cluster?.
   Retry Time for Failed Connections	Duration DTS retries after a connection failure. Default: 720 minutes. Range: 10–1,440 minutes. Recommended: 30 minutes or more. If the connection recovers within the retry window, the task resumes automatically. Note When multiple DTS instances share a source or destination, DTS uses the shortest configured retry duration across all instances. DTS charges for runtime during retries.
   Retry Time for Other Issues	Duration DTS retries after non-connection errors (such as DDL or DML execution failures). Default: 10 minutes. Range: 1–1,440 minutes. Recommended: 10 minutes or more. This value must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Migration	Limit full synchronization rate to reduce load on the destination. Set QPS to the source database, RPS of Full Data Migration, and Data migration speed (MB/s). Available only when Full Data Synchronization is selected. You can also adjust the rate after the task starts.
   Enable Throttling for Incremental Data Synchronization	Limit incremental synchronization rate. Set RPS of Incremental Data Synchronization and Data synchronization speed (MB/s).
   Environment Tag	Tag the instance to identify its environment. Optional.
   Configure ETL	Enable the extract, transform, and load (ETL) feature to transform data in transit. See What is ETL? and Configure ETL.
   Monitoring and Alerting	Set up alerts for task failures or latency exceeding a threshold. See Configure monitoring and alerting.

6. Click Next: Data Validation to optionally configure a data validation task. See Configure data validation.

7. Save the task and run the precheck.

   • To preview API parameters for this 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 starting the task. The task starts only if the precheck passes. If it fails, click View Details next to the failed item, fix the issue, and rerun the precheck. For non-ignorable warnings, fix the issue before proceeding. For ignorable warnings, click Confirm Alert Details > Ignore > OK, then click Precheck Again.

8. Purchase the synchronization instance. The forward task appears on the data synchronization page.

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

   2. Select billing method and instance specifications.

      Parameter	Description
      Billing Method	Subscription: prepay for a fixed duration. Monthly options: 1–9 months. Yearly options: 1, 2, 3, or 5 years. Cost-effective for long-running tasks. Pay-as-you-go: billed hourly for actual usage. Suitable for short-term or test tasks.
      Resource Group Settings	The resource group for the instance. Defaults to default resource group. See What is Resource Management?.
      Instance Class	Determines synchronization performance. See Data synchronization link specifications.

   3. Select the Data Transmission Service (Pay-as-you-go) Service Terms checkbox.

   4. Click Purchase and Start, then click OK in the confirmation dialog.

   If both Full Data Synchronization and Incremental Data Synchronization are selected, the task is listed as a single Incremental Data Synchronization task on the task list page.

Step 3: Configure the reverse task

After the forward task is running, configure the reverse task to complete the two-way setup.

1. Wait until the forward task Status is Running.

2. In the Actions column of the reverse task, click Configure Task.

3. Configure the reverse task using the same steps as the forward task (steps 2–7 above). The reverse task swaps the source and destination — the key differences are shown below.

   Important

   Confirm that the instance information (database name, account, and password) is correct for each direction. The source and destination are swapped in the reverse task.

   Parameter	Forward task	Reverse task
   Source database	Cluster A (original source)	Cluster B (original destination)
   Destination database	Cluster B (original destination)	Cluster A (original source)
   DDL synchronization	Configurable via Exclude DDL Operations	Always filtered out automatically
   Processing mode of conflicting tables	Checks for conflicts with all destination tables	Does not check for tables synchronized by the forward task
   Synchronization objects	Your selection	Must be within the Selected Objects of the forward task
   Instance Region	Configurable	Fixed — cannot be modified
   Object name mapping	Supported	Avoid — may cause data inconsistency

4. When Success Rate reaches 100%, click Back.

5. Wait until the Status of both tasks is Running. Two-way data synchronization is now active.