Data Transmission Service:Synchronize data from an ApsaraDB RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance

Source database requirements

Table structure

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

If you create a synchronization task without selecting Schema Synchronization, make sure the destination table has the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table.

If you select individual tables as the synchronization scope and plan to rename tables or columns in the destination, a single task can handle at most 5,000 tables. For more than 5,000 tables, configure multiple tasks, or synchronize the entire database instead.

WAL log requirements

Operations that cause task failure

Other limits

• The destination table cannot be an append-optimized (AO) table.

• For this synchronization path, you can select only tables as the objects to be synchronized.

• DTS does not synchronize the following data types: BIT, VARBIT, GEOMETRY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.

• A single task synchronizes data from only one database. To synchronize multiple databases, create a separate task for each.

• DTS cannot synchronize tables with inheritance relationships across schemas.

• DDL operations can only be synchronized by tasks created after October 1, 2020.

• Column mapping or schema mismatches between source and destination tables cause data in source-only columns to be lost.

Schema-level synchronization

If you synchronize an entire schema, run the following statement on any table you create or rename in that schema before writing data to it:

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Replace schema and table with the actual schema and table names. Run this statement during off-peak hours, and avoid locking the table — locking can cause a deadlock.

Temporary objects created by DTS

DTS creates the following temporary tables in the source database to capture DDL statements, schema changes, and heartbeat information. Do not delete them while the task is running — the task will fail. DTS removes them automatically after 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

• public.aliyun_dts_instance

DTS also creates a heartbeat table named dts_postgres_heartbeat to track synchronization latency.

Replication slot

DTS creates a replication slot with the prefix dts_sync_ in the source database. This slot provides access to incremental logs from the past 15 minutes.

The replication slot is automatically deleted when the DTS instance is released. If the slot is not deleted automatically (for example, because the source database password was changed, or DTS IP addresses were removed from the whitelist), delete it manually in the source database to prevent slot accumulation.

If the task is released or fails, DTS automatically deletes the replication slot. After a primary/secondary switchover on the source PostgreSQL database, log on to the secondary database to delete the replication slot manually.

Writing from multiple sources

If data from other sources is written to the destination database during synchronization, data inconsistency may occur. For example, running online DDL statements via Data Management (DMS) while other sources write to the destination may cause data loss. To avoid this, use DTS as the only writer to the destination.

DTS task failure and recovery

If a task fails, DTS technical support attempts to restore it within 8 hours. During restoration, the task may be restarted and task parameters may be adjusted. Database parameters are not modified.

Source-specific limits

ApsaraDB RDS for PostgreSQL

• Do not modify the endpoint or zone of the source instance while the task is running.

• For primary/secondary switchovers, enable the Logical Replication Slot Failover feature to prevent logical subscription interruptions. See Logical Replication Slot Failover.

Self-managed PostgreSQL

• Set max_wal_senders and max_replication_slots to values greater than the sum of existing replication slots and the number of DTS instances you plan to create.

• Primary/secondary switchover on a self-managed PostgreSQL database causes the synchronization task to fail.

Google Cloud SQL for PostgreSQL

• Set Database Account to an account with the cloudsqlsuperuser permission.

• Select only objects that the specified account owns or is authorized to manage. Otherwise, grant the OWNER permission on those objects to the account.

An account with cloudsqlsuperuser cannot manage data owned by another cloudsqlsuperuser account.

Step 1: Go to the Data Synchronization Tasks page

1. Log on to the Data Management (DMS) console.Data Synchronization Tasks page of the new DTS console

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

Navigation may vary based on the DMS console mode. See Simple mode and Customize the layout and style of the DMS console for details. Alternatively, go directly to the Data Synchronization Tasks page in the DTS console.

Step 2: Select the region

On the right side of Data Synchronization Tasks, select the region where the synchronization instance resides.

In the new DTS console, select the region in the top navigation bar.

Step 3: Create a task

Click Create Task to open the task configuration page.

If the New Configuration Page button appears in the upper-right corner, click it to switch to the new configuration UI. Skip this step if Back to Previous Version is shown instead.

Parameters may differ slightly between the new and previous configuration pages. Use the new version.

Step 4: Configure source and destination databases

Step 5: Test connectivity

Click Test Connectivity and Proceed at the bottom of the page.

For Alibaba Cloud database instances, DTS automatically adds its CIDR blocks to the instance whitelist. For self-managed databases hosted on ECS, DTS adds CIDR blocks to the ECS security group rules — if the database spans multiple ECS instances, add the CIDR blocks manually to each. For on-premises or third-party cloud databases, add the DTS CIDR blocks manually. See CIDR blocks of DTS servers.

Step 6: Configure synchronization objects

Select objects

Parameter	Description
Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Full and schema synchronization must complete before incremental synchronization begins.
DDL and DML Operations to Be Synchronized	Select the operations to synchronize. For per-table operation selection, right-click an object in Selected Objects and choose the operations. See SQL operations supported for incremental synchronization.
Processing Mode of Conflicting Tables	Precheck and Report Errors: fails the precheck if tables with identical names exist in source and destination. Use object name mapping if destination tables cannot be renamed or deleted. Ignore Errors and Proceed: skips the check. During full sync, existing destination records with matching primary or unique keys are retained; during incremental sync, they are overwritten. Use with caution.
Capitalization of Object Names in Destination Instance	Controls capitalization of database, table, and column names. Default is DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select objects from the Source Objects section and click to add them to Selected Objects. Columns, tables, or schemas can be selected.
Selected Objects	To rename a single object, right-click it. See Map the name of a single object. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. To filter rows by condition, right-click a table and specify conditions. See Set filter conditions.

Object name mapping may cause dependent objects to fail synchronization.

Step 7: Run a precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this configuration, hover over the button and click Preview OpenAPI parameters before proceeding.

The task cannot start until the precheck passes.

If the precheck fails, click View Details next to each failed item, fix the issues, and click Precheck Again.

If an item shows an alert:

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

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

Step 8: Purchase the synchronization instance

Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

On the buy page, configure the following:

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

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