DocsICP FilingConsole
官方文档
首页

Notes and limits for PostgreSQL source databases

更新时间：
复制 MD 格式
产品详情
我的收藏

If your source database is a PostgreSQL database, such as an ApsaraDB RDS for PostgreSQL instance or a self-managed PostgreSQL database, review the notes and limitations in this topic before configuring a data synchronization task to ensure it runs as expected.

Synchronization scenarios for a PostgreSQL source

Review the notes and limits for each data synchronization scenario:

Note

By default, Data Transmission Service (DTS) disables foreign key constraints for the destination database in a data synchronization task. Therefore, DTS does not synchronize cascade and delete operations from the source database to the following destination databases:

  • ApsaraDB RDS for PostgreSQL

  • AnalyticDB for PostgreSQL

  • PolarDB for PostgreSQL (Compatible with Oracle)

  • ApsaraDB RDS for MySQL

  • PolarDB for PostgreSQL

PostgreSQL synchronization

  • One-way synchronization between RDS for PostgreSQL instances

    Type

    Description

    Source database limits

    • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

      Note

      If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

    • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

    • DTS does not support synchronizing temporary tables, internal triggers (TRIGGER), or certain functions such as C language functions and internal functions for PROCEDURE and FUNCTION from the source database. DTS supports synchronizing some custom data types (TYPE is COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK.

    • Write-ahead logging (WAL):

      • You must enable WAL by setting the wal_level parameter to logical.

      • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

    • Operational limits on the source database

      • To ensure smooth task execution and prevent logical subscription interruptions from a primary/secondary switchover, enable the Logical Replication Slot Failover feature on the ApsaraDB RDS for PostgreSQL instance. For more information, see Logical Replication Slot Failover.

      • Due to the inherent limitations of logical subscriptions, if a single row change during incremental synchronization exceeds 256 MB, the data synchronization instance may fail and cannot be recovered. You must reconfigure the data synchronization instance.

      • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

        Note

        During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

    • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

    • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

    Other limits

    • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

    • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

      • When the instance runs for the first time.

      • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

      • When you use the feature to modify synchronization objects.

      Note

      • Replace schema and table in the command with the names of the schema and table to be synchronized.

      • Perform this operation during off-peak hours.

    • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

    • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, you must update the sequence values in the destination database. For more information, see Update the sequence values in the destination database.

    • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

    • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

      Note

      • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

      • If the source database fails over, log on to the standby database to perform the cleanup manually.

      Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

    • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

    • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

    • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

    • During DTS synchronization, do not write data to the destination database from other sources. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written to the destination database from other sources, data loss may occur.

    • For full or incremental data synchronization tasks, if the source tables to be synchronized contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level, provided the destination database account has high-level or superuser permissions. If the account does not have these permissions, you must manually set the session_replication_role parameter to replica in the destination database. During this period, if cascade update or delete operations occur in the source database while session_replication_role is set to replica, data inconsistency may occur. After the DTS task is released, change the session_replication_role parameter back to origin.

    • 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

      • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

    Special cases

    • If the source is an ApsaraDB RDS for PostgreSQL instance:

      During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

    • If the source is a self-managed PostgreSQL database:

      Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

    • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

      Note

      An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

  • Self-managed PostgreSQL to RDS for PostgreSQL

    Type

    Description

    Source database limits

    • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

      Note

      If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

    • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

    • DTS does not support synchronizing temporary tables, internal triggers (TRIGGER), or certain functions such as C language functions and internal functions for PROCEDURE and FUNCTION from the source database. DTS supports synchronizing some custom data types (TYPE is COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK.

    • Write-ahead logging (WAL):

      • You must enable WAL by setting the wal_level parameter to logical.

      • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

    • If a primary/secondary switchover is performed on the self-managed PostgreSQL database, the synchronization task fails.

    • Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

    • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

    • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

      Note

      An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

    • Due to the inherent limitations of logical subscriptions in the source database, if a single data record to be synchronized exceeds 256 MB after an incremental change, the synchronization instance will fail and must be reconfigured.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

    • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

    Other limits

    • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

    • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

      • When the instance runs for the first time.

      • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

      • When you use the feature to modify synchronization objects.

      Note

      • Replace schema and table in the command with the names of the schema and table to be synchronized.

      • Perform this operation during off-peak hours.

    • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

    • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, you must update the sequence values in the destination database. For more information, see Update the sequence values in the destination database.

    • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

    • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

      Note

      • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

      • If the source database fails over, log on to the standby database to perform the cleanup manually.

      Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

    • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

    • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

    • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

    • During DTS synchronization, do not write data to the destination database from other sources. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written to the destination database from other sources, data loss may occur.

    • For full or incremental data synchronization tasks, if the source tables to be synchronized contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level, provided the destination database account has high-level or superuser permissions. If the account does not have these permissions, you must manually set the session_replication_role parameter to replica in the destination database. During this period, if cascade update or delete operations occur in the source database while session_replication_role is set to replica, data inconsistency may occur. After the DTS task is released, change the session_replication_role parameter back to origin.

    • 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

      • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

  • Two-way synchronization between RDS for PostgreSQL instances

    Type

    Description

    Limits on source and destination databases

    • If the tables to be synchronized do not have a primary key or unique constraint, you must enable the Exactly-Once write feature when configuring the task. Otherwise, data duplication may occur in the destination database. For more information, see Synchronize tables that do not have a primary key or unique constraint.

    • The name of the database to be synchronized cannot contain hyphens (-), for example, dts-testdata.

    • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

    • DTS does not support synchronizing temporary tables, internal triggers (TRIGGER), or certain functions such as C language functions and internal functions for PROCEDURE and FUNCTION from the source database. DTS supports synchronizing some custom data types (TYPE is COMPOSITE, ENUM, or RANGE) and the following constraints: primary key, foreign key, unique, and CHECK.

    • Write-ahead logging (WAL):

      • You must enable WAL by setting the wal_level parameter to logical.

      • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

    • Operational limits on the source database

      • To ensure smooth task execution and prevent logical subscription interruptions from a primary/secondary switchover, enable the Logical Replication Slot Failover feature on the ApsaraDB RDS for PostgreSQL instance. For more information, see Logical Replication Slot Failover.

      • Due to the inherent limitations of logical subscriptions, if a single row change during incremental synchronization exceeds 256 MB, the data synchronization instance may fail and cannot be recovered. You must reconfigure the data synchronization instance.

      • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

        Note

        During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

    • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

    Other limits

    • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • If a two-way synchronization instance in the China (Chengdu) or China (Shanghai) region includes a full data synchronization task, DTS creates a full data verification task for that instance by default. For this verification task, Full Data Verification is set to Verify based on the number of table rows. If you have already configured a full data verification task, your configuration takes precedence.

    • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

    • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

      • When the instance runs for the first time.

      • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

      • When you use the feature to modify synchronization objects.

      Note

      • Replace schema and table in the command with the names of the schema and table to be synchronized.

      • Perform this operation during off-peak hours.

    • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

    • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, query the maximum value of the corresponding sequence in the source database and use it as the initial value for the corresponding sequence in the destination database. Run the following command to query the sequence values in the source database:

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

      The SQL statements output by the preceding command include all sequences from the source database. Run them in the destination database based on your business requirements.

    • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

    • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

      Note

      • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

      • If the source database fails over, log on to the standby database to perform the cleanup manually.

      Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

    • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

    • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

    • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

    • During DTS synchronization, do not write data to the destination database from other sources. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written to the destination database from other sources, data loss may occur.

    • For full or incremental data synchronization tasks, if the source tables to be synchronized contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level, provided the destination database account has high-level or superuser permissions. If the account does not have these permissions, you must manually set the session_replication_role parameter to replica in the destination database. During this period, if cascade update or delete operations occur in the source database while session_replication_role is set to replica, data inconsistency may occur. After the DTS task is released, change the session_replication_role parameter back to origin.

    • A two-way synchronization task includes forward and reverse synchronization tasks. When you configure or reset the task, if the destination object of one task matches the synchronization object of the other task:

      • Allow only one task to synchronize full and incremental data. The other task supports only incremental synchronization.

      • Data from the source of the current task synchronizes only to the destination of the current task. It does not serve as source data for the other task.

    • 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

      • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

    Special cases

    • If the source is an ApsaraDB RDS for PostgreSQL instance:

      During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

    • If the source is a self-managed PostgreSQL database:

      Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

    • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

      Note

      An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

Synchronize PostgreSQL to AnalyticDB for PostgreSQL

Type

Description

Source database limits

  • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

    Note

    If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

  • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

  • Write-ahead logging (WAL):

    • You must enable WAL by setting the wal_level parameter to logical.

    • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

  • If a primary/secondary switchover is performed on the source self-managed PostgreSQL database, the synchronization task fails.

  • Operational limits on the source database

    • To ensure smooth task execution and prevent logical subscription interruptions from a primary/secondary switchover, enable the Logical Replication Slot Failover feature on the ApsaraDB RDS for PostgreSQL instance. For more information, see Logical Replication Slot Failover.

    • Due to the inherent limitations of logical subscriptions, if a single row change during incremental synchronization exceeds 256 MB, the data synchronization instance may fail and cannot be recovered. You must reconfigure the data synchronization instance.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

  • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

  • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

  • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

Other limits

  • The destination table cannot be an append-optimized (AO) table.

  • If you use column mapping for partial table synchronization or if the source and destination table schemas are inconsistent, data in columns that exist in the source table but not in the destination table is lost.

  • Requirements for synchronization objects:

    • Only table-level synchronization is supported.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • Synchronization of the following data types is not supported: BIT, VARBIT, GEOMETRY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.

    • The following object types cannot be synchronized: DATATYPE, SEQUENCE, INDEX, PROCEDURE, FUNCTION, VIEW, OPERATOR, DEFAULT_CONSTRAINT, UK, PK, RULE, DOMAIN, AGGREGATE, EXTENSION, FK, and TRIGGER.

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

  • The unique key in the destination table—including the primary key column—must include all columns in the distribution key.

  • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

  • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

    • When the instance runs for the first time.

    • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

    • When you use the feature to modify synchronization objects.

    Note

    • Replace schema and table in the command with the names of the schema and table to be synchronized.

    • Perform this operation during off-peak hours.

  • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

  • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

    Note

    • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

    • If the source database fails over, log on to the standby database to perform the cleanup manually.

    Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

  • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

  • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

  • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

  • During DTS synchronization, do not write data to the destination database from any source other than DTS, as this will cause data inconsistency. For example, if you use DMS to perform online DDL changes while data is being written from other sources, data loss may occur 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

    • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

Special cases

  • If the source is an ApsaraDB RDS for PostgreSQL instance:

    During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

  • If the source is a self-managed PostgreSQL database:

    Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

  • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

    Note

    An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

Synchronize self-managed PostgreSQL to PolarDB for PostgreSQL

Type

Description

Limits on the source database

  • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

    Note

    If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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.

  • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

  • The name of the database to be synchronized cannot contain hyphens (-), for example, dts-testdata.

  • Write-ahead logging (WAL):

    • You must enable WAL by setting the wal_level parameter to logical.

    • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

  • A primary/secondary switchover on the source self-managed PostgreSQL database causes the data synchronization task to fail.

  • Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

  • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

  • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

    Note

    An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

  • Due to the inherent limitations of logical subscriptions in the source database, if a single data record to be synchronized exceeds 256 MB after an incremental change, the synchronization instance will fail and must be reconfigured.

  • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

    Note

    During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

  • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

Other limits

  • For full or incremental data synchronization, if the synchronized tables contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level if the destination database account is a privileged account. If the account does not have this permission, you must manually set the session_replication_role parameter to replica in the destination database. During this period (while session_replication_role is set to replica), data inconsistency may occur if cascade update or delete operations occur in the source database. After the DTS data synchronization task is released, you can change the session_replication_role parameter back to origin.

  • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

  • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

  • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

  • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

  • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

    • When the instance runs for the first time.

    • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

    • When you use the feature to modify synchronization objects.

    Note

    • Replace schema and table in the command with the names of the schema and table to be synchronized.

    • Perform this operation during off-peak hours.

  • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

  • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, you must update the sequence values in the destination database. For more information, see Update the sequence values in the destination database.

  • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

  • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

    Note

    • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

    • If the source database fails over, log on to the standby database to perform the cleanup manually.

    Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

  • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

  • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

  • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

  • During DTS synchronization, do not write data to the destination database from any other source. This can cause data inconsistency. For example, if you use Data Management (DMS) to perform online DDL changes while data is being written from other sources, it may cause data loss 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

    • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

Synchronize RDS for PostgreSQL to RDS for MySQL

Type

Description

Source database limits

  • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

    Note

    If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

  • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

  • Write-ahead logging (WAL):

    • You must enable WAL by setting the wal_level parameter to logical.

    • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

  • Operational limits on the source database

    • To ensure smooth task execution and prevent logical subscription interruptions from a primary/secondary switchover, enable the Logical Replication Slot Failover feature on the ApsaraDB RDS for PostgreSQL instance. For more information, see Logical Replication Slot Failover.

    • Due to the inherent limitations of logical subscriptions, if a single row change during incremental synchronization exceeds 256 MB, the data synchronization instance may fail and cannot be recovered. You must reconfigure the data synchronization instance.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

  • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

  • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

  • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

Other limits

  • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

  • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

  • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

  • DTS does not support synchronizing the following object types: DATATYPE, VIEW, PROCEDURE, FUNCTION, SEQUENCE, EXTENSION, OPERATOR, RULE, DEFAULT_CONSTRAINT, and TRIGGER.

  • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

    • When the instance runs for the first time.

    • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

    • When you use the feature to modify synchronization objects.

    Note

    • Replace schema and table in the command with the names of the schema and table to be synchronized.

    • Perform this operation during off-peak hours.

  • DTS creates the following temporary tables in the source database to obtain incremental DDL (which is not written to the destination database), the schema of incremental tables, and heartbeat information. Deleting these temporary tables during synchronization will cause the DTS task to fail. DTS automatically deletes these temporary tables after the 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

  • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

    Note

    • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

    • If the source database fails over, log on to the standby database to perform the cleanup manually.

    Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

  • 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 synchronize table schemas, set the character_set_server parameter at the instance level in the destination database to utf8mb4.

  • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

  • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

  • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

  • During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination databases. For example, using DMS to perform online DDL changes while data is being written from other sources can cause data loss in the destination database.

  • If a DDL statement fails to be written to the destination database, the DTS task continues to run. You must find the failed DDL statement in the task logs. For more information about how to view task logs, see View task logs.

  • If you write columns with names that differ only in case to the same table in the destination MySQL database, unexpected results may occur because MySQL column names are case-insensitive.

  • After data synchronization completes (the instance's Status is Completed), you should use the ANALYZE TABLE <table_name> command to confirm that all data is written to the target table. For example, after the HA failover mechanism is triggered in the target MySQL database, data might be written only to memory, which can cause data loss.

  • 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

    • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

Special cases

  • If the source is an ApsaraDB RDS for PostgreSQL instance:

    During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

  • If the source is a self-managed PostgreSQL database:

    Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

  • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

    Note

    An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

Synchronize RDS for PostgreSQL to PolarDB for PostgreSQL

  • one-way synchronization

    Type

    Description

    Source database limits

    • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

      Note

      If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

    • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

    • Write-ahead logging (WAL):

      • You must enable WAL by setting the wal_level parameter to logical.

      • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

    • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

    • Due to the inherent limitations of logical subscriptions in the source database, if a single data record to be synchronized exceeds 256 MB after an incremental change, the synchronization instance will fail and must be reconfigured.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

    • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

    Other limits

    • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

    • For full or incremental data synchronization, if the synchronized tables contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level if the destination database account is a privileged account. If the account does not have this permission, you must manually set the session_replication_role parameter to replica in the destination database. During this period (while session_replication_role is set to replica), data inconsistency may occur if cascade update or delete operations occur in the source database. After the DTS data synchronization task is released, you can change the session_replication_role parameter back to origin.

    • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

      • When the instance runs for the first time.

      • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

      • When you use the feature to modify synchronization objects.

      Note

      • Replace schema and table in the command with the names of the schema and table to be synchronized.

      • Perform this operation during off-peak hours.

    • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

    • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

      Note

      • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

      • If the source database fails over, log on to the standby database to perform the cleanup manually.

      Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

    • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

    • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

    • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

    • During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written from other sources, data loss may occur in the destination database.

    • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

    • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, you must update the sequence values in the destination database. For more information, see Update the 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

      • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

    Special cases

    • If the source is an ApsaraDB RDS for PostgreSQL instance:

      During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

    • If the source is a self-managed PostgreSQL database:

      Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

    • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

      Note

      An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.

  • two-way synchronization

    Type

    Description

    Source database limits

    • Tables that you synchronize must have a primary key or a unique constraint. Otherwise, duplicate data can occur in the destination database.

      Note

      If you create the destination table manually (without selecting Synchronization Types as the Schema Synchronization), 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 synchronized cannot contain hyphens (-), for example, dts-testdata.

    • If you synchronize data at the table level and need to edit the objects, such as mapping table or column names, for more than 5,000 tables in a single data synchronization task, we recommend splitting them into multiple tasks or configuring a task to synchronize the entire database. Otherwise, the task may fail upon submission.

    • Write-ahead logging (WAL):

      • You must enable WAL by setting the wal_level parameter to logical.

      • For an incremental data synchronization task, DTS requires that the WAL of the source database be retained for more than 24 hours. For a task that performs both full and incremental data synchronization, the WAL must be retained for at least seven days. You can change the retention period to more than 24 hours after the full data synchronization is complete. If a task fails because DTS cannot obtain the WAL due to a shorter retention period, the failure may lead to data inconsistency or loss in extreme cases. Issues caused by an insufficient WAL retention period are not covered by the DTS Service Level Agreement (SLA).

    • If the source database has long-running transactions and the instance is configured for incremental data synchronization, the write-ahead logging (WAL) data generated before these transactions are committed cannot be cleared. This can cause WAL data to accumulate and may lead to insufficient disk space on the source database.

    • Due to the inherent limitations of logical subscriptions in the source database, if a single data record to be synchronized exceeds 256 MB after an incremental change, the synchronization instance will fail and must be reconfigured.

    • Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails.

      Note

      During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database.

    • If you perform a major version upgrade on the source database while a synchronization instance is running, it will fail and must be reconfigured.

    • Tables with generated columns in PostgreSQL 18 do not support synchronization. If you configure synchronization for such tables, DML operations on those tables will be blocked.

    Other limits

    • A single data synchronization task can synchronize only one database. To synchronize multiple databases, you must configure a separate task for each.

    • DTS does not support synchronizing TimescaleDB plug-in tables, tables with cross-schema inheritance, or tables with expression-based unique indexes.

    • Schemas created by installing plug-ins are not supported for synchronization. You cannot retrieve information about these schemas in the console when you configure a task.

    • If a table to be synchronized contains a SERIAL type column, the source database automatically creates a sequence for that column. Therefore, when you configure Source Objects, if you select Schema Synchronization for Synchronization Types, we recommend that you also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

    • If a two-way synchronization instance in the China (Chengdu) or China (Shanghai) region includes a full data synchronization task, DTS creates a full data verification task for that instance by default. For this verification task, Full Data Verification is set to Verify based on the number of table rows. If you have already configured a full data verification task, your configuration takes precedence.

    • For full or incremental data synchronization, if the synchronized tables contain foreign keys, triggers, or event triggers, DTS temporarily sets the session_replication_role parameter to replica at the session level if the destination database account is a privileged account. If the account does not have this permission, you must manually set the session_replication_role parameter to replica in the destination database. During this period (while session_replication_role is set to replica), data inconsistency may occur if cascade update or delete operations occur in the source database. After the DTS data synchronization task is released, you can change the session_replication_role parameter back to origin.

    • In the following three scenarios, you must run the ALTER TABLE schema.table REPLICA IDENTITY FULL; command on the source tables before writing data to them to ensure data consistency. Do not perform table-locking operations while this command is running to prevent deadlocks. If you skip this check during the precheck, DTS automatically runs this command during instance initialization.

      • When the instance runs for the first time.

      • When you select objects at the schema level, and a new table is created in the schema or a table is rebuilt by using the RENAME command.

      • When you use the feature to modify synchronization objects.

      Note

      • Replace schema and table in the command with the names of the schema and table to be synchronized.

      • Perform this operation during off-peak hours.

    • DTS creates the following temporary tables in the source database to obtain DDL statements for incremental data, the schema of incremental tables, and heartbeat information. Do not delete these temporary tables during synchronization. Otherwise, the DTS task may fail. 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 synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database.

    • During data synchronization, DTS creates a replication slot with the dts_sync_ prefix in the source database to replicate data. This replication slot allows DTS to obtain incremental logs generated within the last 15 minutes from the source database. When a data synchronization task fails or the instance is released, DTS attempts to automatically remove this replication slot.

      Note

      • If you change the password of the source database account or delete the IP whitelist for DTS during data synchronization, the replication slot will not be cleaned up automatically. Manually clean up the replication slot in the source database to prevent it from consuming disk space and making the source database unavailable.

      • If the source database fails over, log on to the standby database to perform the cleanup manually.

      Run the SQL statement SELECT * FROM pg_replication_slots; to view all replication slots in the source database. In the results, the DTS replication slot is the record where the slot_name value starts with dts_sync_ and the active value is true.

    • Before you start data synchronization, evaluate the performance of the source and destination databases. Perform the synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases, which can increase their load.

    • Full data synchronization performs concurrent INSERT operations, which can cause table fragmentation in the destination database. As a result, the tablespace of the destination instance will be larger than that of the source instance after full data synchronization is complete.

    • For table-level data synchronization, if no data from sources other than DTS is written to the destination database, you can use DMS to perform online DDL changes. For more information, see Change schemas without locking tables.

    • During DTS synchronization, do not write data to the destination database from any source other than DTS. This can cause data inconsistency between the source and destination databases. For example, if you use DMS to perform online DDL changes while data is being written from other sources, data loss may occur in the destination database.

    • DTS verifies data content but does not support verifying metadata such as sequences. You must verify such metadata yourself.

    • After you switch your business to the destination database, sequences do not automatically continue from the maximum value of the corresponding sequences in the source database. Before the switchover, query the maximum value of the corresponding sequence in the source database and use it as the initial value for the corresponding sequence in the destination database. Run the following command to query the sequence values in the source database:

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

      The SQL statements output by the preceding command include all sequences from the source database. Run them in the destination database based on your business requirements.

    • When a two-way synchronization instance is running, DTS creates a schema named dts in both the source and destination databases to prevent circular synchronization. Do not modify this schema while the instance is running.

    • A two-way synchronization task includes forward and reverse synchronization tasks. When you configure or reset the task, if the destination object of one task matches the synchronization object of the other task:

      • Allow only one task to synchronize full and incremental data. The other task supports only incremental synchronization.

      • Data from the source of the current task synchronizes only to the destination of the current task. It does not serve as source data for the other task.

    • 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 synchronize partitioned tables, you must include both the parent table and its child partitions as synchronization 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 the child partitions. A data synchronization task must include the parent table and all its child partitions. Otherwise, data from the child partitions may be missed, causing data inconsistency between the source and destination.

      • Synchronization of partitioned tables and inheritance tables (parent-child tables) across different databases is not supported. Ensure that the partitioned tables and all their partitions, and the parent tables and all their child tables, are in the same database.

    Special cases

    • If the source is an ApsaraDB RDS for PostgreSQL instance:

      During data synchronization, do not modify the endpoint or zone of the ApsaraDB RDS for PostgreSQL instance. Otherwise, the data synchronization task fails.

    • If the source is a self-managed PostgreSQL database:

      Ensure that the values of the max_wal_senders and max_replication_slots parameters are both greater than the sum of the number of used replication slots in the database and the number of DTS instances that you want to create with this database as the source.

    • If the source is a Cloud SQL for PostgreSQL instance, you must enter a database account that has the cloudsqlsuperuser permission in the Database Account field. When you select the objects to be synchronized, you must select objects that this account is authorized to manage, or grant the OWNER permission on the objects to this account. For example, run the GRANT <owner_of_objects> TO <source_account_for_task> command to allow the account to perform operations as the owner of the objects.

      Note

      An account that has the cloudsqlsuperuser permission cannot manage data owned by other accounts that also have the cloudsqlsuperuser permission.
Previous：Usage notes and limits for data synchronization from OracleNext：SQL Server source: Limitations and considerations