Data Transmission Service:Migrate data from a self-managed PostgreSQL database to a PolarDB for PostgreSQL (Compatible with Oracle) cluster

Prerequisites

Before you begin, make sure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster. For more information, see Create a PolarDB for PostgreSQL (Compatible with Oracle) cluster

• Enough available storage space in the destination cluster (must exceed the total data size of the source database)

• The required database accounts and permissions (see Permissions required)

Choose a migration type

Select the migration type based on your downtime tolerance.

Recommended: Select both Full Data Migration and Incremental Data Migration to keep your application running during migration. If you select full migration only, stop all writes to the source database before starting — otherwise data inconsistency will occur.

Permissions required

Specify the database owner when you create the destination database.

For instructions on creating accounts and granting permissions, see:

• Self-managed PostgreSQL: CREATE USER and GRANT

• PolarDB for PostgreSQL (Compatible with Oracle): Create database accounts

Billing

SQL operations supported for incremental migration

Usage notes

During schema migration, DTS migrates foreign keys from the source database to the destination database.

During full and incremental data migration, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. Performing CASCADE or DELETE operations on the source database during migration may cause data inconsistency.

Source database limits

Other limits

Limit	Details
Pre-create databases and tables	Create the target databases and tables in the destination cluster before configuring the migration task.
Newly created or renamed tables during incremental migration	If you create a table or run RENAME on a table in a schema selected for migration, execute ALTER TABLE schema.table REPLICA IDENTITY FULL; before writing data to that table. Replace schema and table with the actual names.
Temporary tables in the source database	DTS creates temporary tables 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, and public.dts_args_session. Do not delete these tables during migration. They are automatically removed when the DTS instance is released.
Heartbeat table	DTS creates a heartbeat table named dts_postgres_heartbeat in the source database to track incremental migration latency.
Replication slot	DTS creates a replication slot prefixed with dts_sync_ in the source database, which retains incremental logs for up to 15 minutes. The slot is automatically cleared when the task is released or fails. If a primary/secondary switchover occurs on the source ApsaraDB for PostgreSQL instance, log in to the secondary instance to manually clear the replication slot.
One database per task	A single migration task migrates data from one database only. To migrate multiple databases, create a separate task for each.
Performance impact	Full data migration uses read and write resources on both the source and destination databases, increasing server load. Schedule migration during off-peak hours.
Tablespace growth	Concurrent INSERT operations during full migration cause table fragmentation in the destination database. Expect the destination tablespace to be larger than the source after migration.
FLOAT/DOUBLE precision	DTS retrieves FLOAT and DOUBLE values using ROUND(COLUMN, PRECISION). Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify these defaults meet your requirements and adjust if needed.
Failed task retry	DTS retries failed tasks for up to 7 days. Before switching workloads to the destination database, stop or release any failed tasks. Alternatively, execute REVOKE to remove DTS write permissions on the destination database — otherwise source data may overwrite destination data when the failed task resumes.
Sequence metadata	DTS does not validate sequence metadata. After switching workloads, sequences in the destination database do not automatically continue from the maximum value in the source. Before the cutover, query the current maximum sequence values in the source database and set them as the starting values in the destination database. Use the following SQL to get the setval statements:

do language plpgsql $$
declare
  nsp name;
  rel name;
  val int8;
begin
  for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S'
  loop
    execute format($_$select last_value from %I.%I$_$, nsp, rel) into val;
    raise notice '%',
    format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
  end loop;
end;
$$;

Prepare the source database

Follow the steps for your PostgreSQL version.

Configure the migration task

1. Go to the Data Migration Tasks page.

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

   2. In the top navigation bar, click DTS.

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

   - Navigation varies by DMS console mode. See Simple mode and Customize the layout and style of the DMS console. - Alternatively, go directly to the Data Migration Tasks page of the new DTS console.

2. From the drop-down list next to Data Migration Tasks, select the region where the data migration instance resides.

   In the new DTS console, select the region in the upper-left corner.

3. Click Create Task. Configure the source and destination databases using the following parameters.

   Warning

   After selecting the source and destination instances, read the limits displayed at the top of the page before proceeding.

   Source database

   Parameter	Description
   Database Type	Select PostgreSQL.
   Access Method	Select Express Connect, VPN Gateway, or Smart Access Gateway. For network preparation, see Preparation overview.
   Instance Region	The region where the source database resides.
   Replicate Data Across Alibaba Cloud Accounts	Select No for this scenario.
   Connected VPC	The virtual private cloud (VPC) connected to the source database.
   IP Address	The IP address of the source database server.
   Port Number	The service port of the source database. Default: 5432.
   Database Name	The name of the source database.
   Database Account	The account for the source database. See Permissions required.
   Database Password	The password for the database account.

   Destination database

   Parameter	Description
   Database Type	Select PolarDB (Compatible with Oracle).
   Access Method	Select Express Connect, VPN Gateway, or Smart Access Gateway.
   Instance Region	The region where the destination PolarDB cluster resides.
   Connected VPC	The VPC connected to the destination cluster. Find the VPC ID on the cluster's Overview page.
   Domain Name or IP Address	The IP address of the primary node in the destination cluster. Run ping against the cluster endpoint to get the IP address.
   Port Number	The service port of the destination database. Default: 1521.
   Database Name	The name of the database in the destination cluster.
   Database Account	The account for the destination cluster. See Permissions required.
   Database Password	The password for the database account.

4. If the source database has an IP address whitelist, add the DTS server CIDR blocks to it. Then click Test Connectivity and Proceed.

   Warning

   Adding DTS server CIDR blocks to a database whitelist or ECS security group introduces security risks. Before proceeding, take preventive measures: strengthen account passwords, restrict exposed ports, authenticate API calls, regularly audit IP whitelists and security group rules, and remove unauthorized CIDR blocks. Use Express Connect, VPN Gateway, or Smart Access Gateway to connect the database to DTS where possible.

5. Configure migration objects and advanced settings.

   Migration types and objects

   Parameter	Description
   Migration Types	Select Full Data Migration for a one-time migration with downtime. Select both Full Data Migration and Incremental Data Migration to minimize downtime. If you do not select Incremental Data Migration, stop writes to the source database during migration to ensure data consistency.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: checks whether the destination contains tables with the same names as the source. If identical names exist, the precheck fails and the task cannot start. Use object name mapping to rename destination tables if needed. Ignore Errors and Proceed: skips the duplicate-name check. Warning This may cause data inconsistency. Records with the same primary key values are not migrated, and schema differences may cause partial migration or task failure.
   Source Objects	Select objects from the Source Objects section and click the arrow icon to move them to Selected Objects. Selectable granularity: columns, tables, or schemas. Selecting tables or columns excludes views, triggers, and stored procedures.
   Selected Objects	To rename a single object in the destination, right-click it in Selected Objects. See Map the name of a single object. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. Note Renaming an object may cause dependent objects to fail migration. To filter rows, right-click a table and specify WHERE conditions. See Use SQL conditions to filter data.

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

   Parameter	Description
   Set Alerts	No: disables alerting. Yes: enables alerting. Specify an alert threshold and alert contacts. See Configure monitoring and alerting.
   Retry Time for Failed Connections	The retry window for failed connections. DTS retries immediately within this window. Range: 10–1,440 minutes. Default: 120 minutes. Set this to at least 30 minutes. If DTS reconnects within the window, the task resumes; otherwise, the task fails. Note If multiple tasks share the same source or destination database and have different retry windows, the shortest window applies. DTS charges for the instance during retry periods.
   Retry time for other issues	The retry window for failed DML or DDL operations after the task starts. Range: 1–1,440 minutes. Default: 10 minutes. Set this to at least 10 minutes. Important This value must be less than Retry Time for Failed Connections.
   Configure ETL	Yes: enables the extract, transform, and load (ETL) feature. Enter data processing statements in the code editor. See Configure ETL in a data migration or data synchronization task and What is ETL?. No: disables ETL.

7. Click Next: Save Task Settings and Precheck.

   - To preview the OpenAPI parameters for this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters. - 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. Fix the issues based on the error messages, then run the precheck again. - If the precheck raises an alert: if the alert cannot be ignored, fix the issue and rerun the precheck. If the alert can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may cause data inconsistency.

8. Wait until the success rate reaches 100%, then click Next: Purchase Instance.

9. On the Purchase Instance page, configure the instance class.

   Parameter	Description
   Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?.
   Instance Class	The instance class determines migration speed. Select based on your requirements. See Specifications of data migration instances.

10. Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms by selecting the check box.

11. Click Buy and Start. Monitor the task progress in the task list.

Verify and cut over

Before switching production traffic to the destination cluster, complete the following steps.