Use Data Transmission Service (DTS) to migrate data from a PolarDB for PostgreSQL (Compatible with Oracle) cluster to a Message Queue for Apache Kafka instance. You can run a one-time full migration or set up continuous incremental migration to stream changes to Kafka in near real-time.
Prerequisites
Before you begin, make sure you have:
Set the
wal_levelparameter tologicalon the source PolarDB for PostgreSQL (Compatible with Oracle) cluster. This enables logical decoding, which DTS requires to capture incremental changes from the write-ahead log (WAL). For details, see Set cluster parameters.A Message Queue for Apache Kafka instance with enough available disk space to hold the migrated data. Available disk space must exceed the disk space used by the source cluster.
A topic created in the Kafka instance to receive the migrated data. For details, see Step 1: Create a topic.
For supported database versions, see Migration solutions.
Billing
| Migration type | Link configuration fee | Data transfer cost |
|---|---|---|
| Schema migration and full data migration | Free | Charged when migrating data out of Alibaba Cloud over the public network. See Billing overview. |
| Incremental data migration | Charged. See Billing overview. | — |
Limitations
Source database requirements
Bandwidth: The source database server must have enough egress bandwidth. Insufficient bandwidth reduces migration speed.
Primary key or UNIQUE constraint: Tables to be migrated must have a primary key or a UNIQUE constraint with unique field values. Without this, duplicate data may appear in the destination.
Table-level migration limit: A single task can migrate up to 1,000 tables when using table-level object selection with column mapping. If you exceed this limit, split the tables across multiple tasks or migrate the entire database instead.
WAL retention for incremental migration: If the retention period is too short, DTS may fail to read the required WAL logs, which can cause task failure, data inconsistency, or data loss. Issues caused by insufficient log retention are not covered by the DTS Service-Level Agreement (SLA).
Incremental-only tasks: retain WAL logs for more than 24 hours.
Tasks that include both full and incremental migration: retain WAL logs for at least 7 days. After full migration completes, you can reduce the retention period to more than 24 hours.
DDL operations during migration: Do not perform DDL operations that change the database or table structure during the schema migration and full migration phases. This causes the task to fail.
Write operations during full-only migration: If you run only full data migration, do not write new data to the source instance during migration. To maintain real-time data consistency, select schema migration, full data migration, and incremental data migration together.
Logical replication slot failover: To prevent logical subscription interruptions caused by a primary/secondary switchover, make sure the source cluster supports and has Logical Replication Slot Failover enabled. For details, see Enable Logical Replication Slot Failover.
ImportantIf the source cluster does not support this feature (for example, when the Database Engine is Oracle Syntax Compatible 2.0), the migration instance may fail and cannot be recovered after an HA failover on the source database.
Single incremental data piece size: If a single piece of incremental data exceeds 256 MB, the DTS instance fails and cannot be recovered. You must reconfigure the DTS instance.
Long-running transactions: If the source database has long-running transactions and the task includes incremental migration, WAL logs before the transaction commit accumulate and cannot be cleared, which can exhaust disk space.
Operational restrictions
One database per task: A single data migration task migrates only one database. Configure separate tasks for each additional database.
Unsupported objects: DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, and FK objects.
REPLICA IDENTITY FULL requirement: For incremental data migration tasks, run the following command on each table before writing data to it:
ALTER TABLE schema.table REPLICA IDENTITY FULL;Replace
schemaandtablewith the actual schema name and table name. Run this during off-peak hours to avoid locking tables.This command is required in two situations:
When the instance runs for the first time.
When the migration object granularity is Schema, and a new table is created in the schema or an existing table is rebuilt using the RENAME command.
If you skip the precheck item for this requirement, DTS automatically runs this command during instance initialization.
Partitioned tables: Include both the parent table and all child partitions as migration objects. In PolarDB for PostgreSQL (Compatible with Oracle), the parent table does not store data directly — all data is in the child partitions. Omitting either may cause data inconsistency.
DTS temporary tables: DTS creates the following temporary tables in the source database to obtain DDL statements, schemas, and heartbeat information. Do not delete them during migration — 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,public.aliyun_dts_instanceMigration latency display: DTS adds a
dts_postgres_heartbeattable to the source database to accurately display migration latency.Performance impact: DTS consumes read and write resources on the source and destination databases during full data migration. Run migration tasks during off-peak hours to reduce database load.
Failure and recovery behavior
Replication slot: During incremental data migration, DTS creates a replication slot with the prefix
dts_sync_in the source database. This slot retains the incremental logs from the last 15 minutes. When the task fails or the migration instance is released, DTS attempts to automatically delete this slot.If you change the password of the source database account or remove the DTS IP address from the source database whitelist during migration, the replication slot cannot be cleared automatically. Manually delete the replication slot to prevent it from accumulating and consuming disk space, which can make the source database unavailable. If an HA failover occurs, log on to the secondary database to manually clear the slot.
Kafka scaling: If the destination Kafka instance is scaled out or in during migration, restart the migration instance.
Task auto-resume: DTS attempts to resume failed tasks within 7 days. Before switching your business to the destination instance, end or release the task, or revoke the write permissions of the account DTS uses to access the destination instance. This prevents the task from resuming and overwriting data in the destination.
DTS technical support: If the task fails, DTS technical support attempts to recover it within 8 hours. During recovery, DTS may restart the task or adjust its parameters. Only DTS task parameters are modified — database parameters remain unchanged. For the parameters that may be modified, see Modify instance parameters.
SQL operations supported for incremental migration
| Operation type | SQL statements |
|---|---|
| DML | INSERT, UPDATE, DELETE |
| DDL | CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE; CREATE VIEW, ALTER VIEW, DROP VIEW; CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE; CREATE FUNCTION, DROP FUNCTION; CREATE INDEX, DROP INDEX |
Additional DDL clauses such as CASCADE or RESTRICT are not supported.
DDL statements in a session where
SET session_replication_role = replicais run are not supported.DDL statements run by calling a FUNCTION or through other indirect methods are not supported.
If a single submission from the source database contains both DML and DDL statements, the DDL statements are not migrated.
If a single submission contains DDL statements for objects that are not included in the migration scope, those DDL statements are not migrated.
Permissions required
| Database | Required permission |
|---|---|
| PolarDB for PostgreSQL (Compatible with Oracle) cluster | Privileged account |
For instructions on creating and authorizing accounts, see Create and manage a database account.
Create a migration task
Step 1: Go to the migration task list
Use one of the following methods to access the task creation page.
From the DTS console:
Log on to the Data Transmission Service (DTS) console.
In the left navigation pane, click Data Migration.
In the upper-left corner, select the region where the migration instance will be located.
From the DMS console:
The steps may vary depending on the DMS console mode and layout. For details, see Simple mode console and Customize the layout and style of the DMS console.
Log on to the Data Management (DMS) console.
In the top navigation bar, choose Data + AI > Data Transmission (DTS) > Data Migration.
To the right of Data Migration Tasks, select the region where the migration instance will be located.
Step 2: Configure source and destination databases
Click Create Task, then configure the source and destination databases.
For information on how to get the parameters of the destination Kafka instance, see Configure the parameters of a Message Queue for Apache Kafka instance.
General settings:
| Parameter | Description |
|---|---|
| Task Name | DTS generates a name automatically. Specify a descriptive name for easy identification. The name does not have to be unique. |
Source database:
| Parameter | Description |
|---|---|
| Select Existing Connection | To use a previously registered database instance, select it from the drop-down list — the fields below are filled in automatically. In the DMS console, this parameter is named Select a DMS database instance. To configure a new connection, leave this field blank and fill in the fields below manually. |
| Database Type | Select PolarDB (Compatible with Oracle). |
| Access Method | Select Alibaba Cloud Instance. |
| Instance Region | Select the region where the source cluster resides. |
| Replicate Data Across Alibaba Cloud Accounts | Select No for same-account migration. |
| Instance ID | Select the source PolarDB for PostgreSQL (Compatible with Oracle) cluster ID. |
| Database Name | Enter the name of the database that contains the objects to migrate. |
| Database Account | Enter the database account. See Permissions required for the required permission level. |
| Database Password | Enter the password for the database account. |
Destination database:
| Parameter | Description |
|---|---|
| Select Existing Connection | To use a previously registered database instance, select it from the drop-down list. In the DMS console, this parameter is named Select a DMS database instance. |
| Database Type | Select Kafka. |
| Access Method | Select Express Connect, VPN Gateway, or Smart Access Gateway. The Message Queue for Apache Kafka instance is configured as a self-managed Kafka database for the migration instance. |
| Instance Region | Select the region where the destination Kafka instance resides. |
| Connected VPC | Select the virtual private cloud (VPC) to which the Kafka instance belongs. |
| Domain Name or IP | Enter any IP address from the Default Endpoint of the target ApsaraMQ for Kafka instance. |
| Port Number | Enter the service port. The default is 9092. |
| Database Account | Not required for this configuration. |
| Database Password | Not required for this configuration. |
| Kafka Version | Select the version of the Kafka instance. |
| Encryption | Select Non-encrypted or SCRAM-SHA-256 based on your security requirements. |
| Topic | Select the topic to receive the migrated data. |
| Use Kafka Schema Registry | Kafka Schema Registry provides a RESTful interface for storing and retrieving Avro schemas. Select No to skip, or Yes to use it — then enter the URL or IP address of the Avro schema in the URL or IP Address of Schema Registry field. |
Step 3: Test connectivity
After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.
Make sure the DTS server IP address ranges are added to the security settings (whitelists) of the source and destination databases. For details, see Add DTS server IP addresses to a whitelist.
If either database is self-managed (the Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box that appears.
Step 4: Configure migration objects
On the Configure Objects page, set the migration scope and options.
Migration types:
| Option | When to use |
|---|---|
| Schema Migration + Full Data Migration | One-time migration with no ongoing change capture. |
| Schema Migration + Full Data Migration + Incremental Data Migration | Zero-downtime migration with continuous change streaming. |
If the destination Kafka instance Access Method is Alibaba Cloud Instance, Schema Migration is not supported.
If you do not select Incremental Data Migration, do not write new data to the source instance during migration to avoid data inconsistency.
Object-level settings:
| Parameter | Description |
|---|---|
| Processing Mode of Conflicting Tables | Precheck and Report Errors (default): The precheck fails if destination tables with the same names exist, preventing the task from starting. Ignore Errors and Proceed: Skips the check. During full migration, DTS keeps existing records in the destination and does not overwrite them. During incremental migration, DTS overwrites existing records with source data. Proceed with caution — selecting this option can cause data inconsistency. |
| Data Format in Kafka | Select the message format for Kafka: Canal JSON (see Canal JSON), DTS Avro (parse using the DTS Avro schema definition and deserialization sample code), or Shareplex JSON (see Shareplex JSON). Note Canal JSON is only available in the China (Qingdao) and China (Beijing) regions. |
| Kafka Data Compression Format | LZ4 (default): fastest compression, lowest ratio. GZIP: highest compression ratio, lowest speed, high CPU usage. Snappy: balanced compression and speed. |
| Policy for Shipping Data to Kafka Partitions | Select the desired partition policy. |
| Message acknowledgement mechanism | Select the desired message acknowledgment mechanism. |
| Topic That Stores DDL Information | Select a topic to store DDL information. If not selected, DDL information is stored in the default data topic. |
| Capitalization of Object Names in Destination Instance | Configure the case policy for migrated object names (databases, tables, columns). Defaults to DTS default policy. For details, see Case sensitivity of object names in the destination database. |
| Source Objects | Click objects in the Source Objects box, then click the right-arrow icon to move them to the Selected Objects box. You can select tables as migration objects. |
| Selected Objects | To map a source table to a specific topic, set partitions, or configure a partition key, hover over the table name and click Edit. See Configure topic mapping for details. Note Using object name mapping may cause the migration of dependent objects to fail. To select specific SQL operations for incremental migration, right-click the object in the Selected Objects section and choose the operations in the dialog box. |
Step 5: Configure advanced settings
Click Next: Advanced Settings to configure performance and monitoring options.
| Parameter | Description |
|---|---|
| Dedicated Cluster for Task Scheduling | By default, DTS schedules tasks on a shared cluster. To run tasks on dedicated infrastructure for more stable performance, purchase a dedicated cluster. |
| Retry Time for Failed Connections | How long DTS retries after losing connectivity to the source or destination database. Default: 720 minutes. Range: 10–1440 minutes. Set to more than 30 minutes. If DTS reconnects within this window, the task resumes automatically. Note For multiple DTS instances sharing the same source or destination, the retry time is determined by the last created task. You are charged during the retry period, so set this value based on your business needs or release the instance promptly after the source and destination are released. |
| Retry Time for Other Issues | How long DTS retries after non-connectivity errors (such as DDL or DML execution failures). Default: 10 minutes. Range: 1–1440 minutes. Set to more than 10 minutes. This value must be less than Retry Time for Failed Connections. |
| Enable Throttling for Full Data Migration | Set speed limits to reduce load during full migration. Available only when Full Data Migration is selected. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). You can also adjust the speed after the instance is running. |
| Enable Throttling for Incremental Data Migration | Set speed limits for incremental migration. Available only when Incremental Data Migration is selected. Configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s). You can also adjust the speed after the instance is running. |
| Environment Tag | Attach an environment tag to identify the instance. Optional. |
| Configure ETL | Enable the extract, transform, and load (ETL) feature to transform data during migration. Select Yes and enter processing statements in the code editor. For details, see Configure ETL in a data migration or data synchronization task. Select No to skip. For an ETL overview, see What is ETL? |
| Monitoring and Alerting | Select Yes to set an alert threshold and configure notifications. The system sends alerts if migration fails or latency exceeds the threshold. For threshold configuration, see Configure monitoring and alerting. |
Step 6: Run the precheck
Click Next: Save Task Settings and Precheck to save and start the precheck.
To view the OpenAPI parameters for this instance configuration, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters.
The task starts only after passing the precheck.
If the precheck fails, click View Details next to the failed item, fix the issue, and run the precheck again.
If the precheck reports a warning:
For items that must be fixed: click View Details, fix the issue, and run the precheck again.
For items that can be skipped: click Confirm Alert Details, Ignore, OK, and Precheck Again. Skipping warnings may cause data inconsistency.
Step 7: Purchase and start the instance
When the Success Rate reaches 100%, click Next: Purchase Instance.
On the Purchase page, select the instance class.
Parameter Description Resource Group Settings Select the resource group for the instance. Defaults to the default resource group. See What is Resource Management? Instance Class Select a specification based on your migration volume and performance needs. For specification details, see Data migration link specifications. Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, then click OK in the confirmation dialog box.
The migration instance appears on the Data Migration Tasks list page.
If the instance does not include incremental migration, it stops automatically when migration completes. The Status changes to Completed.
If the instance includes incremental migration, it continues running. The Status remains Running.
Configure topic mapping
Use topic mapping to control which Kafka topic a source table writes to, along with its partition count and partition key.
In the Selected Objects area, hover over the destination topic name at the table level.
Click Edit.
In the Edit Table dialog box, configure the following options.
At the schema level, the dialog box is named Edit Schema and has fewer configurable options.
If the migration granularity is not an entire schema, you cannot modify Name of target Topic or Number of Partitions in the Edit Schema dialog box.
| Parameter | Description |
|---|---|
| Name of target Topic | The Kafka topic to which the source table's data is written. Defaults to the Topic selected in the destination database configuration. If the destination is a Message Queue for Apache Kafka instance, the topic must already exist — DTS does not create it. If the destination is a self-managed Kafka database with schema migration enabled, DTS attempts to create the topic. |
| Filter Conditions | Set row-level filters to migrate only specific data. See Set filter conditions. |
| Number of Partitions | The number of partitions for the destination topic. |
| Partition Key | Available only when Policy for Shipping Data to Kafka Partitions is set to Ship Data to Separate Partitions Based on Hash Values of Primary Keys. Specify one or more columns as the partition key. DTS calculates a hash value from these columns and routes each row to a partition accordingly. Without this, the partition policy does not take effect during the incremental write phase. |
Click OK.