Data Transmission Service:Synchronize PolarDB for PostgreSQL to RDS PostgreSQL

Source database requirements

• Primary key or non-null unique index required. Tables to be synchronized must have a primary key or a non-null unique index. Without one, DTS cannot reliably synchronize UPDATE and DELETE operations on that table—those operations are silently skipped, and data consistency cannot be guaranteed. If this affects your tables, consider adding a primary key or unique index before starting the task.

• Avoid long-running transactions during incremental synchronization. Long-running transactions cause write-ahead log (WAL) files to accumulate before commit, which can exhaust source disk space.

• Logical Replication Slot Failover must be enabled. The PolarDB for PostgreSQL cluster must support and enable Logical Replication Slot Failover. If the cluster does not support this feature (for example, when the Database Engine is PostgreSQL 14), a high-availability (HA) switchover may cause the synchronization instance to fail unrecoverably.

• 256 MB incremental data limit. If a single piece of data exceeds 256 MB after an incremental change, the synchronization instance may fail unrecoverably. In this case, reconfigure the synchronization instance.

• No DDL operations during schema or full synchronization. Do not run DDL operations that change database or table schemas during schema synchronization or full data synchronization—such operations cause the synchronization task to fail.

  During full data synchronization, DTS queries the source database, which creates metadata locks that may block DDL operations.

Other limitations

• One database per task. A single synchronization task can synchronize only one database. Configure a separate task for each additional database.

• Unsupported table types. DTS does not support synchronizing:

  • TimescaleDB extension tables

  • Tables with cross-schema inheritance

  • Tables with unique indexes based on expressions

  • Schemas created by installing plugins

• REPLICA IDENTITY FULL requirement. Before writing data to the following tables in the source database, run ALTER TABLE schema.table REPLICA IDENTITY FULL; on them to ensure data consistency. Run the command during off-peak hours and do not lock the tables while the command runs to avoid deadlocks. If you skip the related precheck items, DTS runs this command automatically during instance initialization. This applies in three scenarios:

  • The instance is running for the first time.

  • You selected schema as the synchronization granularity, and a new table was created in the schema or a table was rebuilt using the RENAME command.

  • You used the modify synchronization objects feature to update the task configuration.

  In the command, replace schema and table with the actual schema name and table name.

• SERIAL type fields require Sequence synchronization. If a table contains a SERIAL-type field, the source database automatically creates a sequence for that field. When Schema Synchronization is selected as a synchronization type, also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

• Run tasks during off-peak hours. Initial full data synchronization consumes read and write resources on both the source and destination databases. Running during peak hours increases database load.

• Destination table space is larger after full synchronization. Initial full data synchronization runs concurrent INSERT operations, which causes table fragmentation in the destination database. As a result, the table space in the destination instance is larger than in the source instance after initial full data synchronization completes.

• Do not write to the destination from other sources. During synchronization, do not write data to the destination database from sources other than DTS. Doing so causes data inconsistency. For example, using Data Management Service (DMS) to perform online DDL operations while other data is being written to the destination may result in data loss.

• Metadata validation is not automatic. DTS validates data content but does not validate metadata such as sequences. Validate metadata separately after synchronization completes.

• DTS creates temporary tables in the source database. DTS creates the following temporary tables to obtain DDL statements, incremental table structures, and heartbeat information. Do not delete these tables during synchronization—they are automatically deleted 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, and public.aliyun_dts_instance

• Replication slot management. DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This slot retains incremental logs for the last 15 minutes. When a synchronization task fails or the synchronization instance is released, DTS attempts to automatically clear the slot.

  - If the password of the source database account used by the task is changed, or the DTS IP address is removed from the source database whitelist during synchronization, the slot cannot be automatically cleared. In this case, manually clear the replication slot in the source database to prevent disk space accumulation. - If a failover occurs in the source database, log on to the secondary database to manually clear the slot.

  

• Update sequence values before business cutover. After switching your business to the destination instance, new sequences do not increment from the maximum value of the source sequence. Before the business cutover, update the sequence value in the destination database. For details, see Update the sequence value in the destination database.

• session_replication_role for tables with foreign keys or triggers. For full or incremental synchronization tasks on tables containing foreign keys, triggers, or event triggers: if the destination database account has privileged or superuser permissions, DTS automatically sets the session_replication_role parameter to replica at the session level. If the account does not have these permissions, manually set the parameter to replica in the destination database. While session_replication_role is replica, cascade update or delete operations in the source database may cause data inconsistency. After the DTS task is released, set the parameter back to origin.

• Task failure recovery. If a task fails, DTS support staff attempt to restore it within eight hours. During restoration, they may restart the task or adjust task parameters (not database parameters). For adjustable parameters, see Modify instance parameters.

• Partitioned tables require parent and child partitions. When synchronizing partitioned tables, include both the parent table and all its child partitions as synchronization objects. The parent table of a PostgreSQL partitioned table does not directly store data—all data is in the child partitions. Excluding child partitions results in data inconsistency.

Notes on schema synchronization

• 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 foreign key cascade operations at the session level. Data inconsistency may occur if cascade update or delete operations are performed on the source database while the task is running.

Step 1: Go to the data synchronization task list

Go to the task list in one of two ways:

Step 2: Create a task

Click Create Task to open the task configuration page.

Step 3: Configure source and destination databases

If you set Encryption to SSL-encrypted for a self-managed PostgreSQL database, you must upload a CA Certificate.

To use a client certificate, upload Client Certificate and Private Key of Client Certificate, and specify Private Key Password of Client Certificate.

For information on configuring SSL encryption for RDS PostgreSQL, see SSL encryption.

After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.

Add the CIDR blocks of the DTS servers to the security settings of both the source and destination databases (either automatically or manually). For details, see Add the IP address whitelist of DTS servers.

If the source or destination is a self-managed database (that is, the Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 4: Configure synchronization objects

On the Configure Objects page, configure the synchronization settings.

Click Next: Advanced Settings.

Step 5: Configure advanced settings

Click Data Verification to configure a data verification task. For details, see Configure data verification.

Step 6: Save and run a precheck

Click Next: Save Task Settings and Precheck at the bottom of the page.

To view the API parameters for this configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters in the tooltip.

Before a synchronization task starts, DTS runs a precheck. The task starts only if the precheck passes.

If the precheck fails, click View Details next to the failed item, fix the issue, and rerun the precheck.

For non-ignorable warnings, click View Details, fix the issue, and rerun the precheck. For ignorable warnings, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring warnings may lead to data inconsistency—proceed with caution.

Step 7: Purchase the instance

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

2. On the Purchase page, select the billing method and instance class.

   Category	Parameter	Description
   New Instance Class	Billing Method	Subscription: Pay upfront for a fixed duration. Cost-effective for long-term, continuous tasks. Pay-as-you-go: Billed hourly for actual usage. Suitable for short-term or test tasks—release the instance at any time to stop billing.
   Resource Group Settings	—	The resource group for the instance. Defaults to default resource group. See What is Resource Management?.
   Instance Class	—	Affects the synchronization rate. Select a class based on your requirements. See Data synchronization link specifications.
   Subscription Duration	—	For Subscription billing, select the duration: 1–9 months or 1, 2, 3, or 5 years.

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

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

Monitor the task progress on the data synchronization page.

Update sequence values in the destination database

After you switch your business to the destination instance, new sequences do not increment from the maximum value of the source sequence. Before the business cutover, update the sequence value in the destination database. For instructions, see Update the sequence value in the destination database.