Data Transmission Service:Configure one-way data synchronization between ApsaraDB RDS for PostgreSQL instances

Source database limitations

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

  If the destination table was created outside DTS (that is, Schema Synchronization was not selected for Synchronization Types), the destination table must have the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table.

• If you select individual tables and need to edit them in the destination (such as renaming tables or columns), a single task supports up to 5,000 tables. For more than 5,000 tables, configure multiple tasks or synchronize the entire database.

• DTS cannot synchronize temporary tables, internal triggers, or internal procedures and functions written in C.

• DTS can synchronize custom parameters of the COMPOSITE, ENUM, and RANGE types.

• Tables must have PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraints.

• During schema synchronization and full data synchronization: Do not execute DDL statements that change database or table schemas. The task will fail.

• If the source database has long-running transactions during incremental synchronization, WAL logs generated before those transactions commit may accumulate and exhaust disk space.

• If the source database undergoes a major version upgrade while a synchronization task is running, the task fails and cannot be recovered. Reconfigure the task.

• Primary/secondary switchover: Enable the Logical Replication Slot Failover feature on the source instance before performing a switchover. Without it, logical subscriptions are interrupted and the task fails.

• Single data size limit: If a single incremental data change exceeds 256 MB, the synchronization instance fails and cannot be recovered. Reconfigure the task.

General limitations

• A single synchronization task can only synchronize data from one database. Create a separate task for each database.

• DTS cannot synchronize tables with inheritance relationships across schemas.

• SERIAL data type: DTS automatically creates a sequence for SERIAL columns in the source database. If you select Schema Synchronization as a synchronization type, also select Sequence or entire schema synchronization. Otherwise, the task fails to run.

• Schema-level synchronization: If you create a table or rename a table within the synchronized schema, run the following statement before writing data to the table to ensure data consistency:

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with the actual names. Do not lock the table when running this statement — otherwise, a deadlock occurs. Run this statement during off-peak hours.

• DTS does not validate metadata such as sequences. Manually verify metadata validity.

• Before switching workloads to the destination database: Update the starting value of sequences in the destination database. Sequences in the destination do not automatically continue from the maximum value of source sequences.

• DTS creates the following temporary tables in the source database. Do not delete these tables during synchronization — they are automatically deleted after 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 track synchronization latency.

• DTS creates a replication slot prefixed with dts_sync_ in the source database to obtain incremental logs from the last 15 minutes.

  The replication slot is automatically deleted when the DTS instance is released. If you change the source database password or remove DTS IP addresses from the whitelist, the replication slot cannot be automatically deleted — delete it manually to prevent replication slot accumulation. After a primary/secondary switchover, log in to the secondary database to delete the replication slot.

  

• Performance impact: Initial full data synchronization reads and writes resources from both databases, which increases server load. Schedule full data synchronization during off-peak hours. After full synchronization completes, the destination tablespace is larger than the source due to fragmentation from concurrent INSERT operations.

• Online DDL during synchronization: If only DTS writes to the destination tables, you can use Data Management Service (DMS) to perform online DDL on source tables. See Change schemas without locking tables. If other sources also write to the destination database during synchronization, data inconsistency or data loss may occur.

• Foreign keys, triggers, or event triggers: If the destination database account is a privileged or superuser account and the synchronized tables contain foreign keys, triggers, or event triggers, DTS temporarily sets session_replication_role to replica at the session level. If the account does not have sufficient permissions, set session_replication_role to replica manually. Cascade update or delete operations on the source during this time may cause data inconsistency. After the task is released, change session_replication_role back to origin.

• Task failure recovery: If a DTS task fails, DTS technical support attempts to restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be modified. For the parameters that may be changed, see Modify the parameters of a DTS instance.

Source-specific limitations

Step 1: Open the Data Synchronization page

Use one of the following methods.

Step 2: Create a task

Click Create Task.

Step 3: Configure source and destination databases

General settings

Source database

Destination database

Step 4: Test connectivity

Click Test Connectivity and Proceed.

Make sure the CIDR blocks of DTS servers are added to the security settings of both source and destination databases. See Add the CIDR blocks of DTS servers. For self-managed databases with access methods other than Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 5: Configure synchronization objects

Configure Objects step

Click Next: Advanced Settings.

Advanced settings

Click Next Step: Data Verification to configure data verification. See Configure a data verification task.

Step 6: Save task settings and run the precheck

• To view the API parameters for this task 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 synchronization task.

• If the precheck fails, click View Details next to the failed item, resolve the issue, and click Precheck Again.

• If an alert is triggered:

  • If the alert cannot be ignored, fix the issue and rerun the precheck.

  • If the alert can be ignored, click Confirm Alert Details, click Ignore, click OK, and then click Precheck Again. Ignoring alerts may cause data inconsistency.

Step 7: Purchase a synchronization instance

1. Wait for Success Rate to reach 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following parameters:

   Parameter	Description
   Billing Method	Subscription: Pay upfront for a set duration. 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 avoid unnecessary charges.
   Resource Group Settings	The resource group for the synchronization instance. Default: default resource group. See What is Resource Management?
   Instance Class	The instance class determines synchronization speed. Select based on your throughput requirements. See Instance classes of data synchronization instances.
   Subscription Duration	Available for the Subscription billing method only. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

3. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then click OK.

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