Data Transmission Service:Synchronize RDS for PostgreSQL to PolarDB 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 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 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.

Synchronization type

Fees

Schema synchronization and full synchronization

Free.

Incremental synchronization

Fees apply. See billing overview.

Operation type

SQL operation

DML

INSERT, UPDATE, DELETE

DDL

• Only data synchronization tasks created after October 1, 2020 () support DDL operation synchronization.

  Important

  • For data synchronization tasks created before May 12, 2023 (UTC+8), you must create triggers and functions in the source database to capture DDL information before you configure the task. For more information, see Use triggers and functions to implement incremental DDL migration for PostgreSQL.

  • Data of the BIT type cannot be synchronized during incremental data synchronization.

• DTS can synchronize the following DDL statements if the database account for the source database is a privileged account, the major version of the ApsaraDB RDS for PostgreSQL instance is 9.5 or later, and the minor version is 20210228 or later. For more information about how to update the minor version, see Update the minor engine version.

  • CREATE TABLE, DROP TABLE

  • ALTER TABLE (including RENAME TABLE, ADD COLUMN, ADD COLUMN DEFAULT, ALTER COLUMN TYPE, DROP COLUMN, ADD CONSTRAINT, ADD CONSTRAINT CHECK, ALTER COLUMN DROP DEFAULT)

  • TRUNCATE TABLE (The source PostgreSQL database must be PostgreSQL 11 or later.)

  • CREATE INDEX ON TABLE

  Important

  • Additional information in DDL statements, such as CASCADE or RESTRICT, cannot be synchronized.

  • DDL statements from a session that executes the SET session_replication_role = replica command cannot be synchronized.

  • DDL statements that are executed by calling a FUNCTION are not supported.

  • If multiple SQL statements submitted by the source database at the same time contain both DML and DDL statements, the DDL statements are not synchronized.

  • If multiple SQL statements submitted by the source database at the same time contain DDL statements for objects that are not synchronized, the DDL statements are not synchronized.

  • DDL statements executed directly within a plug-in through the Server Programming Interface (SPI) are not supported.

Database

Required permissions

Account creation and authorization

RDS PostgreSQL

A high-privilege account that must also be the owner (authorized account) of the selected database.

Create an account and create a database.

PolarDB for PostgreSQL

owner permission on the destination SCHEMA.

Create a database account and manage the database.