Data Transmission Service:Synchronize data from an ApsaraDB RDS for PostgreSQL instance to a PolarDB for PostgreSQL cluster

DDL synchronization details

DDL operations can be synchronized only by data synchronization tasks created after a certain date. For tasks created before May 12, 2023 (Singapore Time), you must manually create triggers and functions in the source database before configuring the task. For more information, see Use triggers and functions to implement incremental DDL migration for PostgreSQL databases.

DDL synchronization requires a privileged account on the source database and minor engine version 20210228 or later. For information about updating the minor engine version, see Update the minor engine version.

Supported DDL statements:

• CREATE TABLE, DROP TABLE

• ALTER TABLE (RENAME TABLE, ADD COLUMN, ADD COLUMN DEFAULT, ALTER COLUMN TYPE, DROP COLUMN, ADD CONSTRAINT, ADD CONSTRAINT CHECK, ALTER COLUMN DROP DEFAULT)

• TRUNCATE TABLE (source PostgreSQL 11 or later)

• CREATE INDEX ON TABLE

DDL synchronization is not supported in the following cases:

• Additional DDL modifiers such as CASCADE or RESTRICT

• DDL from sessions running SET session_replication_role = replica

• DDL executed by calling a FUNCTION or through other indirect means

• DDL via Server Programming Interface (SPI)

• Commits containing both DML and DDL statements — the DDL is not synchronized

• DDL for objects not included in the synchronization scope

• For data synchronization tasks created before May 12, 2023 (Singapore Time), you must create triggers and functions in the source database to capture DDL information before you configure the task. For more information, see Use triggers and functions to implement incremental DDL migration for PostgreSQL databases.

• BIT type data during incremental data synchronization

Source database requirements

Source-type-specific requirements

Other limitations

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

• Unsupported table types: TimescaleDB extension tables and tables with cross-schema inheritance are not supported.

• SERIAL type fields: If a table contains SERIAL type fields, the source database automatically creates a Sequence. When Schema Synchronization is selected, also select Sequence or synchronize the entire schema to avoid task failure.

• Foreign keys, triggers, and event triggers: For full or incremental sync tasks involving tables with foreign keys, triggers, or event triggers, DTS sets session_replication_role to replica at the session level when the destination account is a privileged account. If the destination account lacks this permission, manually set session_replication_role to replica in the destination database. Cascade update or delete operations on the source database during this period may cause data inconsistency. After the DTS task is released, reset session_replication_role to origin.

• REPLICA IDENTITY FULL requirement: Run ALTER TABLE schema.table REPLICA IDENTITY FULL; on the tables to be synchronized before writing data to them in the following scenarios. Do not perform table lock operations while running this command. If you skip the related precheck item, DTS runs this command automatically during instance initialization.

  • When the instance runs for the first time.

  • When synchronizing at the schema level and a new table is created, or an existing synchronized table is rebuilt using the RENAME command.

  • When using the feature to modify synchronization objects.

  - In the command, replace schema and table with the schema name and table name of the data to be synchronized. - Perform this operation during off-peak hours.

• Temporary tables in source database: DTS creates the following temporary tables in the source database to capture DDL statements, schema information, and heartbeat data. Do not delete these tables during synchronization — they are automatically removed 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, and public.aliyun_dts_instance.

• Heartbeat table: DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to ensure synchronization latency accuracy.

• Replication slot: DTS creates a replication slot with the prefix dts_sync_ in the source database to replicate data. Using this replication slot, DTS can obtain incremental logs from the source database within the last 15 minutes. When synchronization fails or the instance is released, DTS attempts to clean up the slot automatically.

  Important

  If you change the source database account password or remove the DTS IP address whitelist during synchronization, the replication slot cannot be cleaned up automatically. Manually clean up the replication slot to prevent disk space accumulation, which can make the source database unavailable. If a primary/secondary failover occurs, log on to the secondary database to perform the cleanup.

  

• Performance impact: Initial full data synchronization consumes read and write resources on both the source and destination databases. Run synchronization during off-peak hours to avoid increased database load.

• Tablespace growth: Initial full data synchronization runs concurrent INSERT operations, which causes fragmentation in the destination database tables. The tablespace in the destination instance is larger than in the source after initialization.

• Online DDL during table-level sync: If no data other than DTS writes to the destination database, you can use Data Management (DMS) to perform online DDL operations. For more information, see Change schemas without locking tables. Do not allow data from sources other than DTS to be written to the destination database simultaneously — this causes data inconsistency.

• Sequence validation: DTS validates data content but not metadata such as Sequences. Validate Sequences manually. After switching your business to the destination, new Sequences do not increment from the maximum value of the source Sequences. Update Sequence values in the destination before switching. For more information, see Update Sequence values in the destination database.

• Instance failure recovery: If a data synchronization instance fails, DTS helpdesk attempts recovery within 8 hours. During recovery, operations such as restarting the instance or adjusting DTS instance parameters may be performed.

  When parameters are adjusted, only the parameters of the DTS instance are modified. The parameters in the database are not modified. For details, see Modify instance parameters.

Foreign key behavior during synchronization

During schema synchronization, DTS synchronizes foreign keys from the source to the destination database. During full and incremental data synchronization, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. Cascade update and delete operations on the source database during synchronization may cause data inconsistency.

Step 1: Go to the Data Synchronization page

Use one of the following methods to open the Data Synchronization page and select the region of the data synchronization instance.

Step 2: Create the task

Click Create Task to open the task configuration page.

Step 3: Configure source and destination databases

Configure the following parameters for the source and destination databases.

Step 4: Test connectivity and proceed

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

DTS server CIDR blocks must be added to the security settings of the source and destination databases. DTS can add these automatically, or you can add them manually. For more information, see Add DTS server IP addresses to a whitelist. If the source or destination database is a self-managed database with Connection Type not set to Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 5: Configure synchronization objects

1. In the Configure Objects step, configure the following parameters.

   Parameter	Description
   Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. By default, Incremental Data Synchronization is selected. After the precheck completes, DTS synchronizes historical data from the source to the destination as the basis for incremental synchronization.
   Synchronization Topology	Select One-way Synchronization.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: checks for duplicate table names between source and destination. The precheck fails if duplicates exist. To resolve, use the object name mapping feature to rename tables. For more information, see Map object names. Ignore Errors and Proceed: skips the duplicate table name check. During full sync, existing records with matching primary or unique keys are retained. During incremental sync, matching records are overwritten. If schemas differ, some columns may fail to sync or the task may fail. Use with caution.
   Source Objects	Select objects from the Source Objects section and click the right arrow icon to add them to the Selected Objects section. You can select schemas or tables. If you select tables, DTS does not migrate other object types (views, triggers, stored procedures) to the destination. If a table contains SERIAL fields and Schema Synchronization is selected, also select Sequence or synchronize the entire schema.
   Selected Objects	To rename a single object in the destination, right-click it in Selected Objects. For more information, see Map the name of a single object. To rename multiple objects at once, click Batch Edit. For more information, see Map multiple object names at a time. To select specific SQL operations for an object, right-click it and select the desired operations. To filter data with a WHERE condition, right-click the table and set the condition. For more information, see Set filter conditions.

2. Click Next: Advanced Settings and configure the following parameters.

   Parameter	Description
   Dedicated Cluster for Task Scheduling	By default, DTS schedules the task to the shared cluster. Purchase a dedicated cluster to improve stability. For more information, see What is a DTS dedicated cluster.
   Retry Time for Failed Connections	How long DTS retries when the source or destination database is unreachable. Valid values: 10–1440 minutes. Default: 720 minutes. Set to more than 30 minutes. If DTS reconnects within this period, the task resumes; otherwise, the task fails. If multiple tasks share the same source or destination database, the shortest retry time takes precedence. DTS charges for the instance during retries.
   Retry Time for Other Issues	How long DTS retries after DDL or DML operation failures. Valid values: 1–1440 minutes. Default: 10 minutes. Set to more than 10 minutes. Must be smaller than Retry Time for Failed Connections.
   Enable Throttling for Full Data Synchronization	Limits resource consumption during full data synchronization. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Synchronization is selected.
   Enable Throttling for Incremental Data Synchronization	Limits resource consumption during incremental data synchronization. Configure RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
   Environment Tag	An optional tag to identify the instance for your business needs.
   Configure ETL	Whether to enable the extract, transform, and load (ETL) feature. Select Yesalert notification settings to enter data processing statements. For more information, see What is ETL? and Configure ETL in a data migration or data synchronization task.
   Monitoring and Alerting	Whether to configure alerts for the sync instance. Select Yes to set alert thresholds and notification contacts. For more information, see Configure monitoring and alerting when you create a DTS task.

3. Click Next Step: Data Verification to configure data verification. For details, see Configure a data verification task.

Step 6: Save settings and run the precheck

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

To preview the API parameters used to configure this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before saving.

DTS runs a precheck before starting the task. The task can only start after passing the precheck.

If the precheck fails, click View Details next to each failed item to analyze the cause, resolve the issue, and rerun the precheck.

If an alert is triggered for an item:

If the alert cannot be ignored, click View Details, resolve the issue, and run the precheck again.

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

Step 7: Purchase the instance

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

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

   Section	Parameter	Description
   New Instance Class	Billing Method	Subscription: pay upfront; 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 reduce costs.
   	Resource Group Settings	The resource group for the data synchronization instance. Default: default resource group. For more information, see What is Resource Management?
   	Instance Class	DTS provides instance classes with different synchronization speeds. Select based on your requirements. For more information, see Instance classes of data synchronization instances.
   	Subscription Duration	Available when Subscription is selected. 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 in the confirmation dialog box.

The task appears in the task list. You can monitor its progress there.