Data Transmission Service:Migrate data from PolarDB for PostgreSQL to AnalyticDB for PostgreSQL

Source database limitations

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

• If the source database has long-running transactions and the task includes incremental migration, write-ahead log (WAL) generated before those transactions are committed may accumulate and exhaust disk space on the source database.

• The PolarDB for PostgreSQL cluster must support and enable Logical Replication Slot Failover.

  Important

  If the source cluster does not support Logical Replication Slot Failover—for example, if the Database Engine is PostgreSQL 14—a high availability (HA) switchover on the source may cause the migration task to fail and become unrecoverable.

• Do not perform DDL operations that change the database or table structure during schema migration or full data migration. Otherwise, the migration task fails.

• A single piece of data that exceeds 256 MB after an incremental change causes the migration task to fail and become unrecoverable. Reconfigure the migration task in this case.

Scope limitations

• A single migration task migrates only one database. Configure a separate task for each additional database.

• DTS does not support: 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 during task configuration.

• DTS does not migrate the following object types: DATATYPE, SEQUENCE, INDEX, PROCEDURE, FUNCTION, VIEW, OPERATOR, DEFAULT_CONSTRAINT, UK, PK, RULE, DOMAIN, AGGREGATE, EXTENSION, FK, and TRIGGER.

• Destination tables do not support AO (append-optimized) tables.

• For partitioned tables, include both the parent table and all its child tables as migration objects. In PostgreSQL, the parent table does not store data directly—all data resides in child tables. Omitting child tables causes data inconsistency.

Data consistency limitations

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

• During full data migration and incremental data migration, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. If cascade update or delete operations occur on the source while the task runs, data inconsistency may occur.

• Full data migration uses concurrent INSERT operations, which causes table fragmentation in the destination. As a result, the destination table space is larger than the source after full migration.

• If a table has a primary key, the primary key column in the destination must match the source. If a table has no primary key, the primary key column in the destination must match the distribution key.

• The unique key (including the primary key column) of the destination table must contain all columns of the distribution key.

• If the source and destination table schemas are inconsistent, or you use column mapping for a non-full table migration, data in columns that exist only in the source is lost.

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

Incremental migration requirements

Before writing data to the source database for incremental migration, run the following command on each table to be migrated:

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 avoid table lock operations during its execution.

Run this command in the following scenarios:

• When the migration task runs for the first time.

• When the migration object is a schema, and a new table is created in that schema or an existing table is rebuilt using RENAME.

If you skip the related precheck item, DTS automatically runs this command during instance initialization.

Post-migration sequence handling

After switching your business to the destination instance, sequences do not automatically start from the maximum value of the corresponding source sequences. Before switching, query the maximum sequence values in the source database and set them as initial values in the destination.

Run the following command on the source database to get all sequence values:

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;
$$;

The output contains SET statements for all sequences in the source database. Run only the statements relevant to your migration on the destination database.

Replication slot management

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

Manual cleanup is required in the following cases:

• You changed the source database account password during migration.

• You removed the DTS IP address from the source database IP address whitelist during migration.

• A failover occurred on the source database—log on to the secondary database to clean up the slot manually.

Temporary tables

DTS creates the following temporary tables in the source database to obtain DDL statements, incremental table structure information, and heartbeat data. Do not delete these tables during migration—they are automatically deleted after the DTS 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

Step 1: Open the migration task list

Open the migration task list using one of the following methods.

From the DTS console:

1. Log on to the DTS console.

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

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

From the DMS console:

The actual steps may vary based on your DMS console mode and layout. See Simple mode console and Customize the layout and style of the DMS console.

1. Log on to the 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 region where the migration instance is located.

Step 2: Configure source and destination databases

Configure the following settings on the source and destination database connection page.

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

Make sure the DTS service IP address ranges are added to the security settings (whitelist) 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.

Using object name mapping may cause migration to fail for other objects that depend on the renamed object.

Step 4: Configure advanced settings

Click Next: Advanced Settings to configure the following parameters.

Step 5: Configure data validation

Click Next: Data Validation to configure a data validation task. See Configure data validation.

Step 6: Configure destination table fields (optional)

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

This step is available only when Schema Migration is selected. Set Definition Status to All to view and edit all tables.

• Primary Key Column: Select one or more columns. Multiple columns form a composite primary key.

• Distribution Key: Select one or more columns from the Primary Key Column. See Manage data tables and Define table distribution.

Step 7: Save settings and run a precheck

Click Next: Save Task Settings and Precheck.

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

Before the migration task starts, DTS performs a precheck. The task starts only after it passes the precheck.

If the precheck fails, click View Details next to the failed item, fix the issue, and click Precheck Again.

If a warning appears:

• For warnings that cannot be ignored, fix the underlying issue and rerun the precheck.

• For ignorable warnings, click Confirm Alert Details > Ignore > OK > Precheck Again to proceed. Ignoring warnings may cause data inconsistency.

Step 8: Purchase the instance

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

2. On the Purchase page, set the following parameters.

   Parameter	Description
   Resource Group Settings	Resource group for the instance. Default: default resource group. See What is Resource Management?
   Instance Class	Migration link specification that determines migration speed. See Data migration link specifications.

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

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