Data Transmission Service:Migrate data between AnalyticDB for PostgreSQL instances

Bandwidth: The source database server must have at least 100 Mb/s of outbound bandwidth. Lower bandwidth reduces migration speed.

Version: The source AnalyticDB for PostgreSQL instance must be version 7.2.1.4 or later.

Logical replication: Set the wal_level parameter to logical to enable logical replication. See Configure parameters.

High-availability Edition: If the source instance uses the High-availability Edition, set hot_standby, hot_standby_feedback, and sync_replication_slots to on. This prevents logical subscription interruptions caused by primary/secondary failovers.

Database name: Database names cannot contain hyphens (-), for example, dts-testdata.

Primary key or UNIQUE constraint: Tables to be migrated must have a primary key or a UNIQUE constraint with unique fields. Otherwise, duplicate data may appear in the destination database.

Unsupported objects: DTS cannot migrate tables with cross-schema inheritance relationships, temporary tables, internal triggers, C language functions, internal PROCEDURE and FUNCTION functions, or extensions. DTS can migrate custom data types of COMPOSITE, ENUM, or RANGE, and primary key, UNIQUE, and CHECK constraints.

Partitioned tables: DTS does not migrate partitioned table schemas. All partitioned tables are created as non-partitioned tables in the destination.

Table-level editing limit: When migrating at the table level with object edits such as name mapping, a single task supports a maximum of 5,000 tables. Exceeding this limit causes an error when submitting the task. Split the tables across multiple tasks or migrate the entire database instead.

During schema migration and full data migration, do not run DDL operations on the source database. DDL changes will cause the migration task to fail.

If you run only full data migration (without incremental migration), do not write new data to the source database during migration. To maintain real-time consistency between source and destination, select schema migration, full data migration, and incremental data migration.

Long-running transactions in the source database during incremental migration can prevent Write-Ahead Logging (WAL) data from being cleared, causing WAL to accumulate and potentially exhausting disk space.

A single migration task can migrate data from only one database. To migrate multiple databases, create a separate task for each database.

DDL operations on the source database are not migrated.

After schema migration, the owner of the migrated schema becomes the destination database account used for the task.

The distribution key is unchanged by default during schema migration. If the source table has a primary key, the destination table uses the same primary key column. If there is no primary key, the distribution key is used as the primary key column.

If a table contains a SERIAL type field, the source database automatically creates a Sequence for that field. When configuring Source Objects, if you select Schema Migration for Migration Types, also select Sequence or migrate the entire schema. Otherwise, the migration instance may fail.

For incremental data migration, run ALTER TABLE schema.table REPLICA IDENTITY FULL; on the source tables before writing data to them. Do not lock tables when running this command — locking can cause a deadlock. If you skip the relevant precheck item, DTS automatically runs this command during instance initialization in these cases:

• When the instance runs for the first time.

• 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.

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

After switching your business to the destination instance, the initial value of a new Sequence does not automatically continue from the maximum value of the corresponding Sequence in the source. Before switching, query the maximum Sequence values in the source and set them as initial values in the destination. Run the following command on the source database to get the setval statements for all Sequences:

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 setval statements for all Sequences in the source database. Run the relevant statements on the destination database as needed.

DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to track incremental migration latency accurately.

DTS creates the following temporary tables in the source database during migration. Do not delete them — deleting them causes the DTS task to fail. These tables are automatically removed 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.

During incremental data migration, DTS creates a replication slot with the prefix dts_sync_ in the source database. DTS uses this slot to retrieve incremental logs from the last 15 minutes. When the migration fails or the migration instance is released, DTS attempts to clean up the replication slot.

Full data migration uses concurrent INSERT operations, which causes table fragmentation in the destination. After full data migration, the destination tables occupy more storage space than the source tables.

During full data migration, DTS consumes read and write resources on both source and destination databases, increasing the database load. Evaluate the performance of both databases before migrating. Run migrations during off-peak hours — for example, when CPU load is below 30% on both databases.

While the migration instance is running:

• Do not change the endpoint or zone of the AnalyticDB for PostgreSQL instance. Such changes cause the migration instance to fail.

• Do not write to the destination database from a data source other than DTS. This can cause data inconsistency and may cause the migration instance to fail.

If a migration instance fails, DTS will attempt recovery within 8 hours. During recovery, DTS may restart the instance or adjust its parameters.