Migrate data from ApsaraDB RDS for PostgreSQL to ApsaraDB for SelectDB

Source database requirements

• Bandwidth: The server that hosts the source database must have sufficient outbound bandwidth. Otherwise, the data migration speed is affected.

• Tables with primary keys or UNIQUE constraints: Ensure that the table fields are unique. Otherwise, duplicate data may exist in the destination database.

  If the destination table that receives data is not created by DTS (that is, Schema Migration is not selected), ensure that the destination table has the same primary key or non-null UNIQUE constraint as the source table. Otherwise, duplicate data may appear in the destination database.

• Tables with no primary key or UNIQUE constraint: Select Schema Migration when configuring the task and set the table engine to duplicate.

• Database name: The database name cannot contain a hyphen (-). For example, dts-testdata is not supported.

• Table count: When migrating at the table level with column name mapping, a single task supports a maximum of 1,000 tables. For larger migrations, split the tables into multiple tasks or migrate the entire database.

• DDL operations: Do not perform DDL operations on the source database during full data migration.

• Data size: If a single row of incremental change data exceeds 256 MB, the migration instance fails and cannot be recovered. You must reconfigure the migration instance.

• Write-Ahead Logging (WAL):

  • Set wal_level to logical.

  • For incremental-only migration: retain WAL logs for more than 24 hours.

  • For full + incremental migration: retain WAL logs for at least 7 days. You can change the log retention period to more than 24 hours after the full migration is complete.

  Important

  If WAL log retention is shorter than required by DTS and the task fails due to missing logs, this is not covered by the DTS Service-Level Agreement (SLA).

• Logical Replication Slot Failover: The RDS PostgreSQL instance must support and have Logical Replication Slot Failover enabled. See Logical Replication Slot Failover.

• Long-running transactions: If the source database has long-running transactions and the task includes incremental migration, WAL data accumulates until the transaction commits. Monitor source disk space to avoid running out.

• Major version upgrades: Do not upgrade the major version of the source database while the migration instance is running. Doing so causes the instance to fail permanently.

Destination SelectDB requirements

• Tables must use the Unique or Duplicate engine. See Data Model for guidance.

• If the destination table uses the Unique engine, all unique keys of the destination table must also exist in the source table and be included in the migration objects.

• Database and table names must start with a letter. Use the object name mapping feature to rename objects that do not meet this requirement.

• Object names containing Chinese characters must be renamed to ASCII equivalents using object name mapping. Otherwise, the task may fail.

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

• Do not add backend (BE) nodes to the SelectDB database during migration. If the task fails because of this, restart the migration instance to resume.

• Do not create clusters in the destination SelectDB instance during migration. If the task fails because of this, restart the migration instance to resume.

• DTS validates data content but does not validate metadata such as Sequences. Validate this metadata manually.

Incremental migration requirements

• Run the following command on each table to be migrated before writing data to the source:

  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 while running it to avoid deadlocks.

  If you skip the related precheck item, DTS automatically runs this command during instance initialization. This applies 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 an existing table is rebuilt using the RENAME command.

• The supported SQL operations for incremental migration are:

  Operation type	SQL statements
  DML	INSERT, UPDATE, DELETE
  DDL	ADD COLUMN, DROP COLUMN

• DTS converts UPDATE and DELETE statements to INSERT statements for tables using the Duplicate engine.

• DTS cannot migrate TimescaleDB extension tables or tables with cross-schema inheritance.

• When migrating partitioned tables, include both the parent table and all child tables in the migration objects. The parent table itself does not store data, but excluding it causes data inconsistency.

• For multi-table merge scenarios (multiple source tables to one destination table), all source tables must have identical schemas.

Special cases

DTS internal behavior

During migration, DTS creates the following objects in the source database. Do not delete them — they are removed automatically when the DTS instance is released:

• Temporary tables: 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

• Replication slot (prefix: dts_sync_): Used to fetch incremental logs from the last 15 minutes. DTS cleans up this slot when the migration fails or the instance is released.

  If you change the source database account password or remove DTS IP addresses from the allowlist during migration, the replication slot cannot be cleaned up automatically. Clean it up manually to prevent disk accumulation. If a primary/secondary failover occurs, log on to the secondary database to perform the cleanup.

Step 1: Go to the Data Migration page

Use one of the following consoles to access the Data Migration page.

Step 2: Configure source and destination databases

Click Create Task to open the task configuration page, then configure the following parameters.

Task name

Source database

Destination database

Step 3: Test connectivity and configure objects

1. Click Test Connectivity and Proceed.

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

2. On the Configure Objects page, set the following parameters:

   Parameter	Description
   Migration Types	Select based on your migration strategy. See Choose a migration type. For zero-downtime migration, select Schema Migration, Full Data Migration, and Incremental Data Migration. For one-time migration, select Schema Migration and Full Data Migration.
   Processing Mode of Conflicting Tables	Precheck and Report Errors (default): The task fails during precheck if tables with the same name exist in the destination. Ignore Errors and Proceed: DTS skips the check. Use with caution — data inconsistency may occur if schemas differ.
   Capitalization of Object Names in Destination Instance	Determines how database, table, and column names are capitalized in the destination. DTS default policy is selected by default. See Specify the capitalization of object names in the destination instance.
   Source Objects	Select objects to migrate at the schema or table level, then click the icon to add them to Selected Objects.
   Selected Objects	Right-click an object to rename it, set filter conditions, or select SQL operations for incremental migration. To set the bucket_count parameter, right-click a table, go to Parameter Settings, enable the setting, and specify a value. To remove an object, click it and then click the remove icon.

   - The bucket_count parameter must be a positive integer. The default value is auto. - If you rename an object using object name mapping, other objects that depend on it may fail to migrate. - To filter rows, right-click a table in Selected Objects and specify WHERE conditions. See Specify filter conditions.

3. Click Next: Advanced Settings and configure the following optional parameters:

   Parameter	Description
   Dedicated Cluster for Task Scheduling	By default, tasks run on the shared cluster. For higher stability, purchase a dedicated cluster. See What is a DTS dedicated cluster.
   Retry Time for Failed Connections	How long DTS retries before marking the task failed due to connection issues. Range: 10–1,440 minutes. Default: 720 minutes. Set to at least 30 minutes.
   Retry Time for Other Issues	How long DTS retries before failing due to DDL or DML errors. Range: 1–1,440 minutes. Default: 10 minutes. Must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Migration	Limits read/write throughput during full migration to reduce database load. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s).
   Enable Throttling for Incremental Data Migration	Limits throughput during incremental migration. Configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s).
   Environment Tag	Optional. Tag the instance for environment identification.
   Configure ETL	Select Yes to enable the extract, transform, and load (ETL) feature. See Configure ETL in a data migration or data synchronization task.
   Monitoring and Alerting	Select Yes to receive alerts when the task fails or migration latency exceeds a threshold. See Configure monitoring and alerting.

4. (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 if you selected Schema Migration for Migration Types. Set Definition Status to All to edit all tables. - The Primary Key Column can be a composite primary key. Select one or more columns from the Primary Key Column as the Distribution Key. - For tables without a primary key or UNIQUE constraint, set Engine to duplicate. Otherwise, the migration instance may fail or data may be lost.

Step 4: Run the precheck

1. Click Next: Save Task Settings and Precheck.

   To preview the API parameters for this task configuration, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

2. Wait for the precheck to complete. If any items fail:

   • Click View Details next to each failed item, address the issue, then click Precheck Again.

   • For alert items that can be ignored, click Confirm Alert Details, then click Ignore > OK > Precheck Again.

   Important

   Ignoring alert items may cause data inconsistency. Proceed with caution.

Step 5: Purchase the instance and start migration

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

2. 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	Controls migration speed. See Instance classes of data migration instances.

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

Verify migration status

After the task starts, go to the Data Migration page to monitor progress:

• Full migration only: The task stops automatically when complete. The Status changes to Completed.

• Full + incremental migration: The incremental phase runs continuously. The Status shows Running. Stop the task manually when you are ready to cut over to the destination.