Migrate data from PolarDB for PostgreSQL to SelectDB

Source database

• Tables to be migrated must have a primary key or a UNIQUE NOT NULL index. Depending on your tables:

  • If all tables have a primary key or non-null unique index: ensure that table fields are unique; otherwise, duplicate data may exist in the destination database.

  • If some tables have neither a primary key nor a non-null unique index: when configuring the instance, select Schema Migration under Migration Types, then in the Configurations for Databases, Tables, and Columns step, set Engine to duplicate for these tables. Otherwise, the migration instance may fail or data may be lost.

• During schema migration and full data migration, do not run DDL operations that change the database or table schema — the migration task will fail.

• During incremental data migration, a single data change exceeding 256 MB causes the migration instance to fail unrecoverably. You must reconfigure the migration instance.

• Long-running transactions during incremental migration can cause write-ahead logging (WAL) logs to accumulate, which may exhaust storage space on the source database.

Destination database

• Tables in the destination SelectDB instance must use the Unique or Duplicate engine.

• Database and table names must start with a letter. Use the object name mapping feature to rename objects that do not start with a letter, or objects with names containing Chinese characters.

• Do not add backend (BE) nodes to the SelectDB instance during migration — the task will fail. Restart the migration instance to resume.

• Do not create clusters in the destination SelectDB instance during migration — the task will fail. Restart the migration instance to resume.

Migration behavior

• A migration instance can migrate only one database. To migrate multiple databases, create a separate migration instance for each database.

• DTS does not support TimescaleDB extension tables or tables with cross-schema inheritance.

• DTS does not validate metadata such as sequences. Verify metadata validity manually after migration.

• When migrating partitioned tables, include both the parent table and all child partitions as migration objects. The parent table of a PostgreSQL partitioned table does not store data directly — all data resides in child partitions. Missing child partitions causes data inconsistency.

• In a multi-table merge scenario (multiple source tables migrated to a single destination table), the schemas of all source tables must be identical.

• You cannot run DDL operations that modify multiple columns simultaneously or modify the same table in succession.

• DTS creates the following temporary tables in the source database during incremental migration. Do not delete them — deleting them causes the task to fail. DTS removes them automatically after 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, and public.aliyun_dts_instance

Unique engine behavior

If the destination table uses the Unique engine, all unique keys in the destination table must exist in the source table and be included as migration objects — otherwise data inconsistency may occur.

Duplicate engine behavior

If the destination table uses the Duplicate engine:

• DTS converts UPDATE and DELETE statements to INSERT statements.

• Duplicate rows may appear in the destination in these cases: a retry occurred, the migration instance restarted, or two or more DML operations were performed on the same row after migration started. Use the additional columns (_is_deleted, _version, and _record_id) to identify and remove duplicates.

Incremental data migration requirements

Before writing data to tables included in an incremental migration, run the following command on each table in the source database:

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Replace schema and table with the actual schema name and table name. Run this command during off-peak hours and do not lock the tables — locking may cause a deadlock.

If you skip the related precheck item, DTS runs this command automatically during instance initialization — when the instance runs for the first time, or when the migration object granularity is set to Schema and a new table is created or rebuilt using the RENAME command.

Replication slot management

DTS creates a replication slot with the prefix dts_sync_ in the source database to replicate incremental data. The slot retains up to 15 minutes of incremental logs.

When a migration task fails or the migration instance is released, DTS attempts to clean up the replication slot automatically. Manual cleanup is required in these cases:

• The source database account password was changed during migration.

• The DTS IP address was removed from the whitelist during migration.

• A primary/secondary failover occurred — log on to the secondary database to perform cleanup.

Replication slots that are not cleaned up accumulate and consume disk space, which may make the source database unavailable.

Step 1: Go to the Data Migration page

Use one of the following methods:

Step 2: Create a task

Click Create Task to go to the task configuration page.

Step 3: Configure source and destination databases

Configure the parameters described in the following table.

After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.

DTS server IP addresses must be added to the security settings of both the source and destination databases. DTS can add these IP addresses automatically, or you can add them manually. See Add DTS server IP addresses to a whitelist.

Step 4: Configure migration objects

On the Configure Objects page, set the following parameters.

Parameter	Description
Migration Types	Select the migration types based on your strategy: <br>- Full migration (no downtime tolerance): Select Schema Migration and Full Data Migration. Stop all writes to the source before starting. <br>- Online migration (zero downtime): Select Schema Migration, Full Data Migration, and Incremental Data Migration.
Processing Mode of Conflicting Tables	- Precheck and Report Errors: DTS checks for tables with the same name in the destination. If duplicates exist, the precheck fails. To resolve, rename the destination table using the object name mapping feature. <br>- Ignore Errors and Proceed: DTS skips the duplicate-name check. Warning This may cause data inconsistency — if schemas match, source records overwrite destination records with the same primary key; if schemas differ, migration may fail or produce incomplete data. Use with caution.
Capitalization of Object Names in Destination Instance	Controls the capitalization of database, table, and column names in the destination. The default is DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select the objects to migrate. Click the icon to move them to Selected Objects. Migration object granularity can be schema, table, or column.
Selected Objects	- To map an object to a different name or destination, right-click the object and select the mapping option. See Map object names. <br>- To remove an object, click it and then click the icon. <br>- To set the bucket_count parameter for a table (available when Schema Migration is selected and migration object granularity is table): right-click the table, set Enable Parameter Settings to Yes, enter the value, and click OK. <br>- To filter rows by SQL conditions, right-click a table and specify the conditions. See Specify filter conditions. <br>- To select which SQL operations to migrate incrementally, right-click an object and select the operations.

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Step 6: Configure database and table fields (optional)

Click Next: Configure Database and Table Fields to set the Primary Key Column, Distribution Key, and Engine for destination tables.

This step is available only when Schema Migration is selected. To view all tables, set Definition Status to All.

Primary Key Column can be a composite primary key. Select one or more columns from Primary Key Column to use as the Distribution Key.

For tables without a primary key or UNIQUE constraint, set Engine to duplicate — otherwise migration may fail or data may be lost.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for configuring this task, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.

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

• For failed items: click View Details, analyze the results, resolve the issues, then click Precheck Again.

• For alert items: if the alert can be ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Note that ignoring alerts may cause data inconsistency.

Step 8: Purchase the instance

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

2. On the Purchase Instance page, configure the instance class:

   Section	Parameter	Description
   New Instance Class	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. See Instance classes of data migration instances.

3. Read and select the Data Transmission Service (Pay-as-you-go) Service Terms checkbox.

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

Monitor progress on the Data Migration page:

• Full migration only: the task stops automatically when complete. Status shows Completed.

• Incremental migration included: the task runs continuously and never stops automatically. Status shows Running.

During full data migration, DTS consumes read and write resources on both the source and destination databases, which increases database load. Run migration during off-peak hours when CPU load on both databases is below 30%.

Incremental migration latency

DTS uses a batch synchronization policy to reduce load on the destination. By default, each synchronization object is written at most once every 5 seconds, resulting in a normal synchronization latency usually within 10 seconds.

To reduce latency, adjust the selectdb.reservoir.timeout.milliseconds parameter of the migration instance in the DTS console. The valid range is 1,000–10,000 milliseconds.

A lower batching time increases write frequency, which raises the load and response time on the destination. This can in turn increase synchronization latency. Adjust based on the destination's actual load.

Instance failure recovery

If a migration instance fails, DTS support will attempt recovery within 8 hours. During recovery, the instance may be restarted or its parameters adjusted (only DTS instance parameters are modified — database parameters are not changed). For the list of parameters that may be modified, see Modify instance parameters.

Off-peak migration

Run migration during off-peak hours when the CPU load on both the source and destination databases is below 30%. This reduces the risk of performance degradation on production workloads.