Migrating from PostgreSQL source databases - Data Transmission Service

Before you configure a data migration task for a PostgreSQL source database, such as self-managed PostgreSQL or ApsaraDB RDS for PostgreSQL, review the notes and limits in this topic to ensure that the migration is successful.

Scenarios

Select a migration scenario to view its usage notes and limitations.

Migrate data between PostgreSQL databases
Migrate data from PostgreSQL to MySQL
Migrate data from PostgreSQL to PolarDB for PostgreSQL (Compatible with Oracle)

Migration between PostgreSQL databases

Migrate data between ApsaraDB RDS for PostgreSQL instances

Type	Description
Source database limits	Tables selected for migration must have a primary key or a unique constraint, and the fields in the constraint must be unique. Otherwise, duplicate data may occur in the destination database. Note If the destination table was not created by Data Transmission Service (DTS) because you did not select Schema Migration as the Migration Types, you must ensure that the destination table has the same primary key or non-null unique constraint as the source table. Otherwise, duplicate data may occur in the destination database. The name of the database to be migrated cannot contain hyphens (-), for example, dts-testdata. If you select tables as the migration objects and need to edit them (for example, by mapping table or column names), a single migration task can migrate up to 1,000 tables. Exceeding this limit causes an error upon task submission. In this case, split the tables across multiple tasks or configure a task to migrate the entire database. DTS does not migrate temporary tables, internal triggers, or certain functions (such as C-language and internal functions for PROCEDURE and FUNCTION) from the source database. DTS supports migrating some custom data types (COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK. For an incremental data migration, the following write-ahead logging (WAL) requirements apply: The wal_level parameter must be set to logical. For an incremental data migration task, the WAL in the source database must be retained for more than 24 hours. For a task that includes both full and incremental data migration, DTS requires that the WAL be retained for at least 7 days. After the full data migration is complete, you can reduce the retention period to more than 24 hours. If DTS cannot obtain the required WAL, the migration task may fail. In extreme cases, data inconsistency or loss may occur. Issues caused by a WAL retention period shorter than the required duration are not covered by the DTS Service Level Agreement (SLA). Limits on operations in the source database: During the schema migration and full data migration phases, do not perform DDL operations, as they will cause the migration task to fail. If you perform only a full data migration, do not write new data to the source database. Doing so causes data inconsistency between the source and destination databases. To ensure real-time data consistency, select a migration type that includes schema migration, full data migration, and incremental data migration. Due to the limitations of logical subscriptions, if a migration instance that includes incremental data migration is running and the size of a single row to be migrated exceeds 256 MB after an incremental change, the migration instance fails irrecoverably, and you must reconfigure it. If the source database has long-running transactions and the instance is configured for an incremental migration task, the write-ahead logging (WAL) generated before the transactions are committed cannot be cleared. This may cause WAL to accumulate and exhaust the disk space of the source database. If you perform a major version upgrade on the source database while a migration instance is running, the instance fails irrecoverably, and you must reconfigure it. Tables with generated columns in PostgreSQL 18 do not support migration. If you configure migration for such tables, DML operations on those tables will be blocked.
Other limits	To prevent logical subscription interruptions caused by a failover and ensure a stable data migration task, you must enable Logical Replication Slot Failover for your ApsaraDB RDS for PostgreSQL instance. For instructions, see Logical Replication Slot Failover. DTS migrates only one database per task. To migrate multiple databases, configure a separate task for each one. DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or tables with expression-based unique indexes. Schemas created by plug-ins cannot be migrated and are not available for selection in the console during task configuration. If a table to be migrated contains a column of the SERIAL type, a sequence is automatically created for that column in the source database. Therefore, when you configure Source Objects, if the Migration Types includes Schema Migration, we recommend that you also select Sequence or migrate the entire schema. Otherwise, the migration instance may fail. For tasks that include incremental data migration, you must run the `ALTER TABLE schema.table REPLICA IDENTITY FULL;` command on the tables to be migrated in the source database before you write data to them. This ensures data consistency for the tables in the following two scenarios. To prevent deadlocks, avoid table-locking operations while this command runs. If you skip the related checks during the precheck, DTS automatically runs this command when it initializes the instance. When the instance runs for the first time. When the migration object granularity is set to Schema and a new table is created in the schema or an existing table is rebuilt by using the RENAME command. Note In the command, replace `schema` and `table` with the schema name and table name of the data to be migrated. Perform this operation during off-peak hours. DTS validates data content but not metadata such as sequences; you must validate the metadata yourself. After you switch your workloads to the destination instance, newly written sequences do not increment from the maximum value of the corresponding sequences in the source database. Before you switch your workloads, you must update the sequence values in the destination database. For more information, see Update sequence values in the destination database. DTS creates the following temporary tables in the source database to obtain information such as DDL statements for incremental data, the schemas of incremental tables, and heartbeat data. Do not delete these temporary tables during migration. Otherwise, the DTS task will be disrupted. DTS automatically deletes these tables 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`. This limit applies to full or incremental data migration tasks where the tables to be migrated from the source database contain foreign keys, triggers, or event triggers. DTS temporarily sets the `session_replication_role` parameter to `replica` at the session level during the migration. If the destination database account does not have the required permissions, you must manually set the parameter to `replica` in the destination database. During this period (while `session_replication_role` is set to `replica`), cascade update or delete operations in the source database may cause data inconsistency. After the migration task is released, you can set the parameter back to `origin`. To ensure the accuracy of the displayed latency for incremental data migration, DTS creates a heartbeat table named `dts_postgres_heartbeat` in the source database. During incremental data migration, DTS creates a replication slot prefixed with `dts_sync_` in the source database to replicate data. This replication slot allows DTS to obtain incremental logs from the source database from the last 15 minutes. When a data migration task fails or the migration instance is released, DTS attempts to automatically clean up the replication slot. Note If you change the password of the source database account used by the task or remove the DTS IP addresses from the IP address whitelist of the source database during migration, the replication slot cannot be automatically cleaned up. In this case, you must manually clean up the replication slot in the source database to prevent log accumulation, which can exhaust disk space and cause the source database to become unavailable. If a primary/secondary switchover occurs on the source database, you must log on to the secondary database to manually clean up the replication slot. Before you migrate data, evaluate the performance of the source and destination databases. Perform data migration during off-peak hours. During full data migration, DTS consumes read and write resources on both the source and destination databases, which may increase the database load. Full data migration involves concurrent INSERT operations, which can cause table fragmentation in the destination database, resulting in the destination database consuming more storage space than the source. Confirm whether the migration precision for columns of the FLOAT or DOUBLE data type meets your business requirements. DTS uses the `ROUND(COLUMN,PRECISION)` function to read values from these columns. If you do not explicitly define a precision, DTS uses a precision of 38 digits for FLOAT and 308 digits for DOUBLE. DTS attempts to resume a failed migration task for up to seven days. Therefore, before you switch your workloads to the destination instance, you must stop or release the task. Alternatively, revoke the write permissions of the account that DTS uses to access the destination instance by using the `REVOKE` command. This prevents an automatically resumed task from overwriting data in the destination instance. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters. When you migrate a partitioned table, you must include both the parent table and its child partitions as migration objects. Otherwise, data in the partitioned table may become inconsistent. Important The parent table of a PostgreSQL partitioned table does not store data directly. All data is stored in its child partitions. A data migration task must include the parent table and all its child partitions. Otherwise, data in the child partitions may be missed, which leads to data inconsistency between the source and destination. Migration of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned table and all its partitions are in the same database. Also, ensure that the parent table and all its child tables are in the same database.
Special cases	Do not modify the connection endpoint or zone of a source ApsaraDB RDS for PostgreSQL instance during migration, as this will cause the task to fail.

Migrate data from a self-managed PostgreSQL database to an ApsaraDB RDS for PostgreSQL instance

Type	Description
Source database limits	The server hosting the source database must have sufficient outbound bandwidth; insufficient bandwidth will affect the migration speed. Tables selected for migration must have a primary key or a unique constraint, and the fields in the constraint must be unique. Otherwise, duplicate data may occur in the destination database. Note If the destination table was not created by Data Transmission Service (DTS) because you did not select Schema Migration as the Migration Types, you must ensure that the destination table has the same primary key or non-null unique constraint as the source table. Otherwise, duplicate data may occur in the destination database. The name of the database to be migrated cannot contain hyphens (-), for example, dts-testdata. If you select tables as the migration objects and need to edit them (for example, by mapping table or column names), a single migration task can migrate up to 1,000 tables. Exceeding this limit causes an error upon task submission. In this case, split the tables across multiple tasks or configure a task to migrate the entire database. DTS does not migrate temporary tables, internal triggers, or certain functions (such as C-language and internal functions for PROCEDURE and FUNCTION) from the source database. DTS supports migrating some custom data types (COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK. For an incremental data migration, the following write-ahead logging (WAL) requirements apply: The wal_level parameter must be set to logical. For an incremental data migration task, the WAL in the source database must be retained for more than 24 hours. For a task that includes both full and incremental data migration, DTS requires that the WAL be retained for at least 7 days. After the full data migration is complete, you can reduce the retention period to more than 24 hours. If DTS cannot obtain the required WAL, the migration task may fail. In extreme cases, data inconsistency or loss may occur. Issues caused by a WAL retention period shorter than the required duration are not covered by the DTS Service Level Agreement (SLA). Limits on operations in the source database: Performing a primary/secondary switchover on the self-managed PostgreSQL source database will cause the migration to fail. During the schema migration and full data migration phases, do not perform DDL operations, as they will cause the migration task to fail. Due to the limitations of logical subscriptions, if a migration instance that includes incremental data migration is running and the size of a single row to be migrated exceeds 256 MB after an incremental change, the migration instance fails irrecoverably, and you must reconfigure it. If the source database has long-running transactions and the instance is configured for an incremental migration task, the write-ahead logging (WAL) generated before the transactions are committed cannot be cleared. This may cause WAL to accumulate and exhaust the disk space of the source database. If you perform a major version upgrade on the source database while a migration instance is running, the instance fails irrecoverably, and you must reconfigure it. Tables with generated columns in PostgreSQL 18 do not support migration. If you configure migration for such tables, DML operations on those tables will be blocked.
Other limits	Data may be inconsistent between the primary and secondary nodes of the source database due to replication lag. When you migrate data, use the primary node of the source database as the migration source. DTS migrates only one database per task. To migrate multiple databases, configure a separate task for each one. DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or tables with expression-based unique indexes. Schemas created by plug-ins cannot be migrated and are not available for selection in the console during task configuration. If a table to be migrated contains a column of the SERIAL type, a sequence is automatically created for that column in the source database. Therefore, when you configure Source Objects, if the Migration Types includes Schema Migration, we recommend that you also select Sequence or migrate the entire schema. Otherwise, the migration instance may fail. For tasks that include incremental data migration, you must run the `ALTER TABLE schema.table REPLICA IDENTITY FULL;` command on the tables to be migrated in the source database before you write data to them. This ensures data consistency for the tables in the following two scenarios. To prevent deadlocks, avoid table-locking operations while this command runs. If you skip the related checks during the precheck, DTS automatically runs this command when it initializes the instance. When the instance runs for the first time. When the migration object granularity is set to Schema and a new table is created in the schema or an existing table is rebuilt by using the RENAME command. Note In the command, replace `schema` and `table` with the schema name and table name of the data to be migrated. Perform this operation during off-peak hours. DTS validates data content but not metadata such as sequences; you must validate the metadata yourself. After you switch your workloads to the destination instance, newly written sequences do not increment from the maximum value of the corresponding sequences in the source database. Before you switch your workloads, you must update the sequence values in the destination database. For more information, see Update sequence values in the destination database. DTS creates the following temporary tables in the source database to obtain information such as DDL statements for incremental data, the schemas of incremental tables, and heartbeat data. Do not delete these temporary tables during migration. Otherwise, the DTS task will be disrupted. DTS automatically deletes these tables 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`. To ensure the accuracy of the displayed latency for incremental data migration, DTS creates a heartbeat table named `dts_postgres_heartbeat` in the source database. During incremental data migration, DTS creates a replication slot prefixed with `dts_sync_` in the source database to replicate data. This replication slot allows DTS to obtain incremental logs from the source database from the last 15 minutes. When a data migration task fails or the migration instance is released, DTS attempts to automatically clean up the replication slot. Note If you change the password of the source database account used by the task or remove the DTS IP addresses from the IP address whitelist of the source database during migration, the replication slot cannot be automatically cleaned up. In this case, you must manually clean up the replication slot in the source database to prevent log accumulation, which can exhaust disk space and cause the source database to become unavailable. If a primary/secondary switchover occurs on the source database, you must log on to the secondary database to manually clean up the replication slot. This limit applies to full or incremental data migration tasks where the tables to be migrated from the source database contain foreign keys, triggers, or event triggers. DTS temporarily sets the `session_replication_role` parameter to `replica` at the session level during the migration. If the destination database account does not have the required permissions, you must manually set the parameter to `replica` in the destination database. During this period (while `session_replication_role` is set to `replica`), cascade update or delete operations in the source database may cause data inconsistency. After the migration task is released, you can set the parameter back to `origin`. Before you migrate data, evaluate the performance of the source and destination databases. Perform data migration during off-peak hours. During full data migration, DTS consumes read and write resources on both the source and destination databases, which may increase the database load. Full data migration involves concurrent INSERT operations, which can cause table fragmentation in the destination database, resulting in the destination database consuming more storage space than the source. Confirm whether the migration precision for columns of the FLOAT or DOUBLE data type meets your business requirements. DTS uses the `ROUND(COLUMN,PRECISION)` function to read values from these columns. If you do not explicitly define a precision, DTS uses a precision of 38 digits for FLOAT and 308 digits for DOUBLE. DTS attempts to resume a failed migration task for up to seven days. Therefore, before you switch your workloads to the destination instance, you must stop or release the task. Alternatively, revoke the write permissions of the account that DTS uses to access the destination instance by using the `REVOKE` command. This prevents an automatically resumed task from overwriting data in the destination instance. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters. When you migrate a partitioned table, you must include both the parent table and its child partitions as migration objects. Otherwise, data in the partitioned table may become inconsistent. Important The parent table of a PostgreSQL partitioned table does not store data directly. All data is stored in its child partitions. A data migration task must include the parent table and all its child partitions. Otherwise, data in the child partitions may be missed, which leads to data inconsistency between the source and destination. Migration of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned table and all its partitions are in the same database. Also, ensure that the parent table and all its child tables are in the same database.
Special cases	If the source database is a self-managed PostgreSQL instance, ensure the `max_wal_senders` and `max_replication_slots` parameter values exceed the sum of existing replication slots and the number of DTS migration instances you plan to create for the source. If the source instance is Cloud SQL for PostgreSQL, you must specify an account with the `cloudsqlsuperuser` permission in the Database Account field. When you select the migration objects, select objects that this account is authorized to manage. Alternatively, grant the `OWNER` role for the objects to this account (for example, run the `GRANT <owner_of_the_object_to_be_migrated> TO <source_database_account_used_by_the_task>` command). Note An account with the `cloudsqlsuperuser` permission cannot manage data owned by another `cloudsqlsuperuser` account.

Migrate from PostgreSQL to MySQL

Type	Description
Source database limits	The server hosting the source database must have sufficient outbound bandwidth; insufficient bandwidth will affect the migration speed. Tables selected for migration must have a primary key or a unique constraint, and the fields in the constraint must be unique. Otherwise, duplicate data may occur in the destination database. Note If the destination table was not created by Data Transmission Service (DTS) because you did not select Schema Migration as the Migration Types, you must ensure that the destination table has the same primary key or non-null unique constraint as the source table. Otherwise, duplicate data may occur in the destination database. The name of the database to be migrated cannot contain hyphens (-), for example, dts-testdata. If you select tables as the migration objects and need to edit them (for example, by mapping table or column names), a single migration task can migrate up to 1,000 tables. Exceeding this limit causes an error upon task submission. In this case, split the tables across multiple tasks or configure a task to migrate the entire database. For an incremental data migration, the following write-ahead logging (WAL) requirements apply: The wal_level parameter must be set to logical. For an incremental data migration task, the WAL in the source database must be retained for more than 24 hours. For a task that includes both full and incremental data migration, DTS requires that the WAL be retained for at least 7 days. After the full data migration is complete, you can reduce the retention period to more than 24 hours. If DTS cannot obtain the required WAL, the migration task may fail. In extreme cases, data inconsistency or loss may occur. Issues caused by a WAL retention period shorter than the required duration are not covered by the DTS Service Level Agreement (SLA). Limits on operations in the source database: Performing a primary/secondary switchover on the self-managed PostgreSQL source database will cause the migration to fail. To prevent logical subscription interruptions caused by a failover and ensure a stable data migration task, you must enable Logical Replication Slot Failover for your ApsaraDB RDS for PostgreSQL instance. For instructions, see Logical Replication Slot Failover. During the full data migration phase, do not perform DDL operations to change database or table schemas. Otherwise, the data migration task fails. Due to the limitations of logical subscriptions, if a migration instance that includes incremental data migration is running and the size of a single row to be migrated exceeds 256 MB after an incremental change, the migration instance fails irrecoverably, and you must reconfigure it. If you perform only full data migration, do not write new data to the source database during the migration. Otherwise, data inconsistency will occur between the source and destination databases. To maintain real-time data consistency, we recommend that you select both full data migration and incremental data migration. If the source database has long-running transactions and the instance is configured for an incremental migration task, the write-ahead logging (WAL) generated before the transactions are committed cannot be cleared. This may cause WAL to accumulate and exhaust the disk space of the source database. If you perform a major version upgrade on the source database while a migration instance is running, the instance fails irrecoverably, and you must reconfigure it. Tables with generated columns in PostgreSQL 18 do not support migration. If you configure migration for such tables, DML operations on those tables will be blocked. The following PostgreSQL data types are not supported for data migration: inet, cidr, macaddr, macaddr8, interval, enum, composite, range, jsonpath, pg_lsn, oid, txid_snapshot, point, line, lseg, box, path, polygon and circle. If the migration objects contain columns of these types, the task may fail.
Other limits	DTS does not support migrating DATATYPE, VIEW, PROCEDURE, FUNCTION, SEQUENCE, EXTENSION, OPERATOR, RULE, DEFAULT_CONSTRAINT, or TRIGGER. If your data includes four-byte characters—such as rare Chinese characters or emojis—the destination database and table must use the utf8mb4 charset. Note If you use DTS to migrate schemas, set the instance-level parameter `character_set_server` to utf8mb4 in the destination database. If DDL writes fail on the destination database, the DTS task continues running. Check the failed DDL statements in the task logs. For instructions, see View task logs. If you write columns with identical names but different cases into the same table in the destination MySQL database, unexpected results may occur. MySQL column names are case-insensitive. After migration completes—the task status is Status and the status changes to Completed—run `analyze table <table_name>` to confirm all data is written to the destination table. For example, after a high-availability (HA) switchover in the destination MySQL database, data may remain in memory and never reach disk, causing data loss. DTS migrates only one database per task. To migrate multiple databases, configure a separate task for each one. DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or tables with expression-based unique indexes. Schemas created by plug-ins cannot be migrated and are not available for selection in the console during task configuration. For tasks that include incremental data migration, you must run the `ALTER TABLE schema.table REPLICA IDENTITY FULL;` command on the tables to be migrated in the source database before you write data to them. This ensures data consistency for the tables in the following two scenarios. To prevent deadlocks, avoid table-locking operations while this command runs. If you skip the related checks during the precheck, DTS automatically runs this command when it initializes the instance. When the instance runs for the first time. When the migration object granularity is set to Schema and a new table is created in the schema or an existing table is rebuilt by using the RENAME command. Note In the command, replace `schema` and `table` with the schema name and table name of the data to be migrated. Perform this operation during off-peak hours. DTS creates the following temporary tables in the source database to obtain information such as DDL statements for incremental data, the schemas of incremental tables, and heartbeat data. The DDL statements are not written to the destination database. Do not delete these temporary tables during migration. Otherwise, the DTS task may fail or behave unexpectedly. The temporary tables 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`. To ensure the accuracy of the displayed latency for incremental data migration, DTS creates a heartbeat table named `dts_postgres_heartbeat` in the source database. During incremental data migration, DTS creates a replication slot prefixed with `dts_sync_` in the source database to replicate data. This replication slot allows DTS to obtain incremental logs from the source database from the last 15 minutes. When a data migration task fails or the migration instance is released, DTS attempts to automatically clean up the replication slot. Note If you change the password of the source database account used by the task or remove the DTS IP addresses from the IP address whitelist of the source database during migration, the replication slot cannot be automatically cleaned up. In this case, you must manually clean up the replication slot in the source database to prevent log accumulation, which can exhaust disk space and cause the source database to become unavailable. If a primary/secondary switchover occurs on the source database, you must log on to the secondary database to manually clean up the replication slot. Before you migrate data, evaluate the performance of the source and destination databases. Perform data migration during off-peak hours. During full data migration, DTS consumes read and write resources on both the source and destination databases, which may increase the database load. Full data migration involves concurrent INSERT operations, which can cause table fragmentation in the destination database, resulting in the destination database consuming more storage space than the source. Confirm whether the migration precision for columns of the FLOAT or DOUBLE data type meets your business requirements. DTS uses the `ROUND(COLUMN,PRECISION)` function to read values from these columns. If you do not explicitly define a precision, DTS uses a precision of 38 digits for FLOAT and 308 digits for DOUBLE. DTS attempts to resume a failed migration task for up to seven days. Therefore, before you switch your workloads to the destination instance, you must stop or release the task. Alternatively, revoke the write permissions of the account that DTS uses to access the destination instance by using the `REVOKE` command. This prevents an automatically resumed task from overwriting data in the destination instance. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters. When you migrate a partitioned table, you must include both the parent table and its child partitions as migration objects. Otherwise, data in the partitioned table may become inconsistent. Important The parent table of a PostgreSQL partitioned table does not store data directly. All data is stored in its child partitions. A data migration task must include the parent table and all its child partitions. Otherwise, data in the child partitions may be missed, which leads to data inconsistency between the source and destination. Migration of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned table and all its partitions are in the same database. Also, ensure that the parent table and all its child tables are in the same database.
Special cases	Do not modify the connection endpoint or zone of a source ApsaraDB RDS for PostgreSQL instance during migration, as this will cause the task to fail. If the source database is a self-managed PostgreSQL instance, ensure the `max_wal_senders` and `max_replication_slots` parameter values exceed the sum of existing replication slots and the number of DTS migration instances you plan to create for the source. If the source instance is Cloud SQL for PostgreSQL, you must specify an account with the `cloudsqlsuperuser` permission in the Database Account field. When you select the migration objects, select objects that this account is authorized to manage. Alternatively, grant the `OWNER` role for the objects to this account (for example, run the `GRANT <owner_of_the_object_to_be_migrated> TO <source_database_account_used_by_the_task>` command). Note An account with the `cloudsqlsuperuser` permission cannot manage data owned by another `cloudsqlsuperuser` account. If the destination is an ApsaraDB RDS for MySQL instance, DTS automatically creates a database. However, if the name of the source database does not comply with the naming conventions of ApsaraDB RDS for MySQL, you must manually create the database in the destination instance before you configure the data migration task. For more information, see Manage databases.

Migration from PostgreSQL to PolarDB for PostgreSQL (Compatible with Oracle)

The following notes and limitations apply:

Type	Description
Limits on the source database	The server hosting the source database must have sufficient outbound bandwidth; insufficient bandwidth will affect the migration speed. Tables selected for migration must have a primary key or a unique constraint, and the fields in the constraint must be unique. Otherwise, duplicate data may occur in the destination database. Note If the destination table was not created by Data Transmission Service (DTS) because you did not select Schema Migration as the Migration Types, you must ensure that the destination table has the same primary key or non-null unique constraint as the source table. Otherwise, duplicate data may occur in the destination database. The name of the database to be migrated cannot contain hyphens (-), for example, dts-testdata. If you select tables as the migration objects and need to edit them (for example, by mapping table or column names), a single migration task can migrate up to 1,000 tables. Exceeding this limit causes an error upon task submission. In this case, split the tables across multiple tasks or configure a task to migrate the entire database. For an incremental data migration, the following write-ahead logging (WAL) requirements apply: The wal_level parameter must be set to logical. For an incremental data migration task, the WAL in the source database must be retained for more than 24 hours. For a task that includes both full and incremental data migration, DTS requires that the WAL be retained for at least 7 days. After the full data migration is complete, you can reduce the retention period to more than 24 hours. If DTS cannot obtain the required WAL, the migration task may fail. In extreme cases, data inconsistency or loss may occur. Issues caused by a WAL retention period shorter than the required duration are not covered by the DTS Service Level Agreement (SLA). Limits on operations in the source database: Performing a primary/secondary switchover on the self-managed PostgreSQL source database will cause the migration to fail. Due to the limitations of logical subscriptions, if a migration instance that includes incremental data migration is running and the size of a single row to be migrated exceeds 256 MB after an incremental change, the migration instance fails irrecoverably, and you must reconfigure it. During the full data migration phase, do not perform DDL operations to change database or table schemas. Otherwise, the data migration task fails. If you perform only full data migration, do not write new data to the source database during the migration. Otherwise, data inconsistency will occur between the source and destination databases. To maintain real-time data consistency, we recommend that you select both full data migration and incremental data migration. If the source database has long-running transactions and the instance is configured for an incremental migration task, the write-ahead logging (WAL) generated before the transactions are committed cannot be cleared. This may cause WAL to accumulate and exhaust the disk space of the source database. If you perform a major version upgrade on the source database while a migration instance is running, the instance fails irrecoverably, and you must reconfigure it.
Other limits	This note applies to full or incremental data migration instances for tables that contain foreign keys, triggers, or event triggers. DTS temporarily sets the `session_replication_role` parameter to `replica` at the session level during full or incremental migration. If the destination database account does not have this permission, you must manually set the `session_replication_role` parameter to `replica`. During this period, if cascade update or delete operations occur in the source database, data inconsistency may result. After the DTS data migration instance is released, you can change the `session_replication_role` parameter value back to `origin`. If a table to be migrated contains a column of the SERIAL type, a sequence is automatically created for that column in the source database. Therefore, when you configure Source Objects, if the Migration Types includes Schema Migration, we recommend that you also select Sequence or migrate the entire schema. Otherwise, the migration instance may fail. For tasks that include incremental data migration, you must run the `ALTER TABLE schema.table REPLICA IDENTITY FULL;` command on the tables to be migrated in the source database before you write data to them. This ensures data consistency for the tables in the following two scenarios. To prevent deadlocks, avoid table-locking operations while this command runs. If you skip the related checks during the precheck, DTS automatically runs this command when it initializes the instance. When the instance runs for the first time. When the migration object granularity is set to Schema and a new table is created in the schema or an existing table is rebuilt by using the RENAME command. Note In the command, replace `schema` and `table` with the schema name and table name of the data to be migrated. Perform this operation during off-peak hours. DTS creates the following temporary tables in the source database to obtain information such as DDL statements for incremental data, the schemas of incremental tables, and heartbeat data. Do not delete these temporary tables during migration. Otherwise, the DTS task will be disrupted. DTS automatically deletes these tables 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`. To ensure the accuracy of the displayed latency for incremental data migration, DTS creates a heartbeat table named `dts_postgres_heartbeat` in the source database. During incremental data migration, DTS creates a replication slot prefixed with `dts_sync_` in the source database to replicate data. This replication slot allows DTS to obtain incremental logs from the source database from the last 15 minutes. When a data migration task fails or the migration instance is released, DTS attempts to automatically clean up the replication slot. Note If you change the password of the source database account used by the task or remove the DTS IP addresses from the IP address whitelist of the source database during migration, the replication slot cannot be automatically cleaned up. In this case, you must manually clean up the replication slot in the source database to prevent log accumulation, which can exhaust disk space and cause the source database to become unavailable. If a primary/secondary switchover occurs on the source database, you must log on to the secondary database to manually clean up the replication slot. DTS migrates only one database per task. To migrate multiple databases, configure a separate task for each one. DTS does not support migrating TimescaleDB extension tables, tables with cross-schema inheritance, or tables with expression-based unique indexes. Schemas created by plug-ins cannot be migrated and are not available for selection in the console during task configuration. Before you migrate data, evaluate the performance of the source and destination databases. Perform data migration during off-peak hours. During full data migration, DTS consumes read and write resources on both the source and destination databases, which may increase the database load. Full data migration involves concurrent INSERT operations, which can cause table fragmentation in the destination database, resulting in the destination database consuming more storage space than the source. Confirm whether the migration precision for columns of the FLOAT or DOUBLE data type meets your business requirements. DTS uses the `ROUND(COLUMN,PRECISION)` function to read values from these columns. If you do not explicitly define a precision, DTS uses a precision of 38 digits for FLOAT and 308 digits for DOUBLE. DTS attempts to resume a failed migration task for up to seven days. Therefore, before you switch your workloads to the destination instance, you must stop or release the task. Alternatively, revoke the write permissions of the account that DTS uses to access the destination instance by using the `REVOKE` command. This prevents an automatically resumed task from overwriting data in the destination instance. DTS validates data content but not metadata such as sequences; you must validate the metadata yourself. After you switch your workloads to the destination instance, newly written sequences do not increment from the maximum value of the corresponding sequences in the source database. Before you switch your workloads, you must update the sequence values in the destination database. For more information, see Update sequence values in the destination database. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters. When you migrate a partitioned table, you must include both the parent table and its child partitions as migration objects. Otherwise, data in the partitioned table may become inconsistent. Important The parent table of a PostgreSQL partitioned table does not store data directly. All data is stored in its child partitions. A data migration task must include the parent table and all its child partitions. Otherwise, data in the child partitions may be missed, which leads to data inconsistency between the source and destination. Migration of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned table and all its partitions are in the same database. Also, ensure that the parent table and all its child tables are in the same database.
Special cases	Do not modify the connection endpoint or zone of a source ApsaraDB RDS for PostgreSQL instance during migration, as this will cause the task to fail. If the source instance is Cloud SQL for PostgreSQL, you must specify an account with the `cloudsqlsuperuser` permission in the Database Account field. When you select the migration objects, select objects that this account is authorized to manage. Alternatively, grant the `OWNER` role for the objects to this account (for example, run the `GRANT <owner_of_the_object_to_be_migrated> TO <source_database_account_used_by_the_task>` command). Note An account with the `cloudsqlsuperuser` permission cannot manage data owned by another `cloudsqlsuperuser` account. If the source database is a self-managed PostgreSQL instance, ensure the `max_wal_senders` and `max_replication_slots` parameter values exceed the sum of existing replication slots and the number of DTS migration instances you plan to create for the source.