Synchronize data from an ApsaraDB RDS for PostgreSQL instance to an AnalyticDB for PostgreSQL instance

Table and schema requirements

• Tables must have a PRIMARY KEY or UNIQUE constraint with all fields unique. Without this, the destination database may contain duplicate records.

  Note

  If you create a destination table outside of DTS (without selecting Schema Synchronization for Synchronization Types), make sure the destination table has the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table.

• Only tables can be selected as synchronization objects — not views, functions, or other object types.

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

• Tables with inheritance relationships across schemas cannot be synchronized.

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

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

Column mapping and schema differences

• If you use column mapping for non-full-table synchronization, or if the source and destination table schemas differ, data in columns that exist in the source but not in the destination will be lost.

• If you rename tables or columns in the destination database using the object name mapping feature, synchronizing more than 5,000 tables in a single task causes a request error. For tables exceeding 5,000, configure multiple tasks or synchronize entire databases instead.

WAL log requirements

The source database must meet these write-ahead logging (WAL) requirements:

• Set wal_level to logical.

• For incremental-only synchronization: retain WAL logs for more than 24 hours.

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

Failing to meet these requirements may cause DTS to fail to obtain WAL logs, resulting in task failure or, in exceptional cases, data inconsistency or loss.

Runtime constraints

• Do not execute DDL statements during schema synchronization or full data synchronization. Doing so fails the task.

• If you add a table to a synchronized schema or rename a table using RENAME, execute ALTER TABLE schema.table REPLICA IDENTITY FULL; before writing data to the table. This prevents data inconsistency. Perform this operation during off-peak hours to avoid deadlocks.

  Note

  Replace schema and table with the actual schema name and table name.

• Do not delete the temporary tables that DTS creates in the source database: 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. DTS automatically deletes these tables when the instance is released.

• Long-running transactions in the source database may cause WAL log accumulation and exhaust disk space.

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

• A single incremental data change must not exceed 256 MB. If it does, the task fails and cannot be recovered. Reconfigure the task.

Replication slots

DTS creates a replication slot prefixed with dts_sync_ in the source database. This slot retains incremental logs from the last 15 minutes.

DTS also adds a heartbeat table named dts_postgres_heartbeat to the source database to track synchronization latency.

Source-specific constraints

ApsaraDB RDS for PostgreSQL:

• Do not modify the endpoint or zone of the source RDS for PostgreSQL instance while the task is running. Doing so fails the task.

• To perform a primary/secondary switchover on the source instance, enable the Logical Replication Slot Failover feature first. 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 to be created.

• A primary/secondary switchover on a self-managed PostgreSQL database fails the task.

Google Cloud SQL for PostgreSQL:

• Set the Database Account parameter to an account with the cloudsqlsuperuser permission. When selecting objects to synchronize, only select objects the account is authorized to manage, or grant the OWNER permission on those objects to the account.

  Note

  An account with the cloudsqlsuperuser permission cannot manage data owned by other accounts with the same permission.

Data consistency

• Evaluate the impact on source and destination database performance before starting the task. Run the task during off-peak hours.

• During full data synchronization, concurrent INSERT operations create table fragmentation in the destination database, so the destination tablespace may be larger than the source after full synchronization completes.

• Data inconsistency occurs if data from sources other than DTS is written to the destination database during synchronization. If you use Data Management (DMS) to perform online DDL operations while other sources write to the destination, data loss may occur.

• If you use only DTS to write data to the destination, use DMS to perform online DDL operations on source tables during synchronization. See Change schemas without locking tables.

Task recovery

If a DTS task fails, DTS technical support attempts recovery within 8 hours. During recovery, the task may be restarted and task parameters (not database parameters) may be modified. For details on which parameters may change, see the Modify instance parameters section of Modify the parameters of a DTS instance.

Step 1: Go to Data Synchronization Tasks

1. Log on to the Data Management (DMS) console.

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

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

   Note

   • Navigation may vary based on the DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console.

   • You can also go directly to the Data Synchronization Tasks page of the new DTS console.

Step 2: Create a task

1. On the right side of Data Synchronization Tasks, select the region where the synchronization instance will reside.

   Note

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

2. Click Create Task.

3. (Optional) Click New Configuration Page in the upper-right corner.

   Note

   • Skip this step if Back to Previous Version is displayed instead.

   • Use the new configuration page — some parameters differ between versions.

Step 3: Configure source and destination databases

Configure the following parameters:

Task name

Source database

Destination database

Step 4: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the whitelists of Alibaba Cloud database instances and the security group rules of ECS-hosted databases. For self-managed databases in data centers or on third-party cloud platforms, manually add the DTS server CIDR blocks to the database whitelist. See CIDR blocks of DTS servers.

Step 5: Configure synchronization objects

Configure objects and conflict handling

Parameter	Description
DDL and DML Operations to Be Synchronized	The SQL operations to synchronize. To configure per-table, right-click an object in Selected Objects and select the operations. See SQL operations that support incremental synchronization.
Processing Mode of Conflicting Tables	Precheck and Report Errors: fails the precheck if destination tables share names with source tables. Use object name mapping to resolve conflicts. Ignore Errors and Proceed: skips the name conflict check. During full sync, existing destination records with matching primary or unique keys are retained; during incremental sync, they are overwritten. If schemas differ, initialization may partially fail.
Capitalization of Object Names in Destination Instance	How DTS handles the case of database, table, and column names in the destination. Default is DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select objects from this section and click to add them to Selected Objects. Selectable granularity: columns, tables, or schemas.
Selected Objects	Right-click a table to rename it, set data filters, or configure per-table SQL operations. To rename multiple objects at once, click Batch Edit. See Map object names and Set filter conditions.

Note

Renaming an object using object name mapping may cause dependent objects to fail synchronization.

Step 6: Save settings and run a precheck

Click Next: Save Task Settings and Precheck.

Step 7: Purchase and start the instance

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

On the purchase page, configure the following:

Read and select 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. You can monitor its progress there.