Data Transmission Service:Migrate data from PolarDB for PostgreSQL to a self-managed Oracle database

Blocking limitations

Address these before starting the migration. Ignoring them may cause the task to fail or become unrecoverable.

• No schema migration. Create all destination schemas and tables in Oracle before configuring the migration task.

• Logical Replication Slot Failover required for incremental migration. If the source PolarDB for PostgreSQL cluster does not support Logical Replication Slot Failover (for example, if the cluster's Database Engine is PostgreSQL 14), a high availability (HA) switchover during migration may cause the migration instance to fail and become unrecoverable.

• 256 MB per-row limit for incremental migration. If a single row exceeds 256 MB after an incremental change, the migration instance fails and cannot be recovered. To resume, reconfigure the migration instance.

• Primary key or non-null unique index required. Tables to be migrated must have a primary key or a non-null unique index.

• No DDL operations during full migration. DDL operations that change database or table structure cause the migration task to fail.

• Foreign keys and triggers in the destination database. Disable foreign keys and triggers in the destination Oracle database before migration. If they remain active, the migration task may fail.

• REPLICA IDENTITY setting for incremental migration. Before writing data to migrated tables, run the following command in the source database. Run this during off-peak hours and avoid concurrent table lock operations to prevent deadlocks.

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Run this command in the following scenarios: when the instance runs for the first time; when the migration object is a schema and a new table is created in the schema; or when the migration object is a schema and an existing table is rebuilt using the RENAME command. If you skip the related precheck item, DTS automatically runs this command during instance initialization.

• Partitioned tables. Include both the parent table and all child tables as migration objects. In PostgreSQL, parent tables of partitioned tables do not store data directly — all data is in child tables. Omitting child tables causes data inconsistency.

General limitations

• A migration task migrates only one database. Configure a separate task for each database you want to migrate.

• DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or tables with unique indexes based on expressions.

• Schemas created by installing plugins cannot be migrated and do not appear in the console when you configure the task.

• Cascade update and delete operations in the source database may cause data inconsistency in the destination.

• Because full data migration runs concurrent INSERT operations, the destination table space may be larger than the source after full migration.

• DTS validates data content but does not validate metadata such as sequences. Validate sequences and other metadata separately.

• Long-running transactions combined with an incremental migration task cause write-ahead log (WAL) accumulation, which may exhaust disk space on the source database.

Replication slot management

During incremental data migration, DTS creates a replication slot with the prefix dts_sync_ in the source database. This slot holds incremental logs from the last 15 minutes. When migration fails or the migration instance is released, DTS attempts to clean up the slot automatically.

Automatic cleanup fails in the following cases, and you must clean up the slot manually to prevent disk accumulation:

• The source database account password is changed during migration.

• The DTS IP address is removed from the source database's IP address whitelist during migration.

• A failover occurs on the source database — log in to the secondary database to clean up the slot.

DTS temporary tables

DTS creates the following temporary tables in the source database during migration. Do not delete them — deleting them causes the migration task to fail. DTS deletes them automatically when the migration 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

Step 1: Open the migration task configuration page

Use one of the following entry points.

From the DTS console:

1. Log on to the Data Transmission Service (DTS) console.

2. In the left navigation pane, click Data Migration.

3. In the upper-left corner, select the region where the migration instance will be located.

4. Click Create Task.

From the DMS console:

The actual navigation may vary based on the DMS console mode and layout. For details, see Simple mode console and Customize the layout and style of the DMS console.

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

2. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.

3. To the right of Data Migration Tasks, select the destination region.

4. Click Create Task.

Step 2: Configure source and destination databases

On the task configuration page, set Task Name to a descriptive name for easy identification. The name does not need to be unique.

Source database (PolarDB for PostgreSQL):

Destination database (Oracle):

After completing the configuration, click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

Make sure the DTS server IP address ranges are added to the security settings of both the source and destination databases. See Add DTS server IP addresses to a whitelist.

Step 3: Configure migration objects

On the Configure Objects page, set the following parameters:

Click Next: Advanced Settings.

Step 4: Configure advanced settings

Step 5: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this instance configuration, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters.

DTS runs a precheck before starting the migration. If the precheck fails:

• Click View Details next to the failed item, fix the issue, then run the precheck again.

• For warnings that can be ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring warnings may cause data inconsistency.

Step 6: Purchase the instance and start migration

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

2. On the Purchase page, select the resource group and instance class. The instance class affects migration speed. See Data migration link specifications.

3. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

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

The migration task appears on the Data Migration Tasks list page. Full migration tasks stop automatically when complete (status: Completed). Incremental migration tasks continue running (status: Running) until you stop them.