PolarDB:Migrate data between PolarDB for PostgreSQL (Compatible with Oracle) clusters

Create an evaluation task

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region where the source cluster is deployed.

3. Open the evaluation dialog using one of these methods:

   • From the Clusters page: click Migration/Upgrade Evaluation in the upper-left corner.

   • From the Migration/Upgrade page: click Migration/Upgrade Evaluation in the upper-left corner.

4. In the Migration /Upgrade Evaluation dialog box, configure the parameters and click Next.

   Parameter	Value
   Creation Method	Upgrade from PolarDB
   Source PolarDB Version	Oracle 1.0
   Source PolarDB Cluster	Select the source cluster
   Destination Database Engine	Oracle 2.0
   Database Name	Select the source database

5. Review the compatibility results.

   • Overview — shows a summary of compatible and incompatible objects.

   • Details — lists specific incompatible objects.

   Focus on incompatible objects in the results:

   • Oracle native objects can be ignored.

   • Objects involved in SQL statements must be adapted at the business side. If you cannot adapt them, contact Alibaba Cloud support.

Iterate until evaluation passes

After fixing incompatible objects, create a new evaluation task to confirm the issues are resolved. Repeat until all objects are compatible.

To view existing evaluation tasks, go to the Migration/Evaluation page.

Evaluation tasks are retained for seven days. Create a new task if yours has expired.

Source database limitations

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database. During full data synchronization and incremental data synchronization, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. If cascade updates or deletions run on the source database during this time, data inconsistency may occur.

• Tables must have PRIMARY KEY or UNIQUE constraints with all fields unique. Otherwise, the destination database may contain duplicate records.

• When migrating selected tables with object renaming (such as renaming tables or columns), a single migration task supports up to 1,000 tables. To migrate more than 1,000 tables, create multiple tasks or migrate the entire database instead.

• For incremental data migration:

  • Write-ahead logging (WAL) must be enabled on the source cluster.

  • WAL log retention must be more than 24 hours for incremental-only migration, or at least 7 days for full + incremental migration. If WAL logs are unavailable, the task may fail or data loss may occur. After full migration completes, you can reduce the retention to more than 24 hours.

  • If you need to perform a primary/secondary switchover on the source cluster during migration, enable the Logical Replication Slot Failover feature first. For more information, see Logical replication slot failover.

• During schema migration and full data migration, do not execute DDL statements. Doing so will fail the migration task.

• For full-only migration, do not write to the source database during migration. Data written after migration starts will not appear in the destination. To avoid this, use full + incremental migration instead.

• If the source database has long-running transactions with an incremental migration task active, uncommitted WAL data may accumulate and cause disk space issues.

Other limitations

• Each migration task covers a single source database. Create separate tasks for multiple databases.

• For incremental migration with schema-level objects: after creating a new table or renaming a table in a schema, run the following statement before writing data to that table:

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with your schema name and table name.

• DTS creates a table named dts_postgres_heartbeat in the source database to track latency. The following figure shows the table structure.

  

• DTS creates a replication slot prefixed with dts_sync_ on the source database to capture incremental logs from the last 15 minutes.

  - After the DTS instance is released, the replication slot is deleted automatically. If you change the source database password or remove DTS from the IP address whitelist, the replication slot cannot be deleted automatically — you must delete it manually to prevent accumulation and cluster unavailability. - If the migration task is released or fails, DTS clears the replication slot automatically. If a primary/secondary switchover occurs on the source cluster, log on to the secondary instance and clear the replication slot manually.

• Migrate data during off-peak hours. Full data migration reads from the source and writes to the destination concurrently, which increases load on both databases.

• After full data migration, the destination tablespace is larger than the source due to fragmentation from concurrent INSERT operations.

• DTS uses ROUND(COLUMN, PRECISION) to retrieve values from FLOAT and DOUBLE columns. Default precision: FLOAT = 38 digits, DOUBLE = 308 digits. Verify these settings meet your requirements.

• DTS retries failed tasks for up to 7 days. Before switching workloads to the destination, stop or release any failed tasks — or revoke DTS write permissions using REVOKE — to prevent stale source data from overwriting the destination after a retry.

• DTS does not validate sequence metadata. Check sequence validity manually.

• After cutover, sequences do not automatically continue from the maximum value in the source database. Query and reset them before switching workloads. See Reset sequences after cutover.

Migration types

SQL statements supported in incremental migration

DML: INSERT, UPDATE, DELETE

DDL (requires a privileged account on the source):

• 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 cluster version 1.0 or later)

• CREATE INDEX ON TABLE

DDL not supported:

Step 1: Open the Data Migration page

Use one of the following methods:

From the DTS console:

1. Log on to the DTS console.

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

3. In the upper-left corner, select the region where the migration instance resides.

From the DMS console:

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

1. Log on to the DMS console.

2. In the top navigation bar, go to Data + AI > DTS (DTS) > Data Migration.

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

Step 2: Configure source and destination databases

Click Create Task, then configure the source and destination.

Set Database Type to PolarDB for PostgreSQL (Compatible with Oracle) for both source and destination.

Task settings:

Source database (PolarDB for PostgreSQL (Compatible with Oracle) 1.0):

Destination database (PolarDB for PostgreSQL (Compatible with Oracle) 2.0):

Click Test Connectivity and Proceed. DTS adds its CIDR blocks to the destination cluster's whitelist.

Step 3: Select objects and configure synchronization settings

All namespaces in the source database are listed. Select the namespaces you want to migrate.

Parameter	Description
Synchronization Type	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. All three are required.
Synchronization Topology	One-way synchronization: data flows from source to destination only. Two-way synchronization: data also flows from destination to source (incremental only, DDL not supported in reverse).
Processing Mode in Existed Target Table	Precheck and Report Errors: fails precheck if destination has tables with identical names. Use object name mapping to resolve conflicts. For more information, see Map object names. Ignore Errors and Proceed: skips the check. During full sync, existing destination records are kept; during incremental sync, source records overwrite destination records. Schema mismatches may cause partial sync or task failure.
Capitalization of Object Names in Destination Instance	Controls capitalization of database, table, and column names. Default: DTS default policy. For more information, see Specify the capitalization of object names in the destination instance.
Source Objects	Select objects and click to add them to Selected Objects. Select columns, tables, or databases. If you select tables or columns, views, triggers, and stored procedures are not migrated.
Selected Objects	Right-click an object to rename it or filter rows with a WHERE condition. Click Batch Edit to rename multiple objects at once. For more information, see Map object names and Use SQL conditions to filter data.

Step 4: Configure advanced settings

Click Next: Advanced Settings.

Step 5: Configure data verification

Click Next Step: Data Verification.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

• To preview the corresponding API parameters, hover over the button and click Preview OpenAPI parameters.

DTS runs a precheck before starting migration.

• If an item fails, click View Details to see the cause, fix the issue, and click Precheck Again.

• If an alert is generated for an item you can safely ignore, click Confirm Alert Details, then click Ignore in the dialog box, then click Precheck Again.

Ignoring alert items may cause data inconsistency or expose your business to risks.

Step 7: Purchase the DTS instance

After the precheck reaches 100%, click Next: Purchase Instance.

Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start.

Track task progress in the task list.

Test before cutting over

After migration reaches the incremental phase, add the destination cluster to a test environment and run comprehensive tests. Test for at least one week to confirm the destination cluster is stable before proceeding with cutover.

Reset sequences after cutover

After cutover, sequences do not automatically continue from the maximum value in the source database. Before switching workloads, query the maximum sequence values from the source and set them as the initial values in the destination.

Run the following statement on the source database to get the setval commands:

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' and relowner != 10
 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;
$$;

The output lists setval() statements for each sequence. Run those statements on the destination database.

Perform the cutover

Plan a cutover window (for example, 10 hours). During this window:

1. Stop all write workloads on the source database.

2. Wait until the incremental migration latency reaches zero on the DTS console, which indicates that data replay on the destination database is complete.

3. Update the database connection string in your application to point to the destination cluster.

4. Restart the application and run basic functional tests.

5. If tests pass, the destination cluster is live.

Keep the source database running until you confirm the destination cluster is completely stable. Release the source after you have verified all workloads are behaving as expected.