Data Transmission Service:Two-way Synchronization Between PolarDB for PostgreSQL and RDS PostgreSQL

Primary key or unique index required

Tables to be synchronized must have a primary key or a non-null unique index. In the following three situations, you must run the following command on the tables to be synchronized in the source database before writing data to them:

• When the synchronization instance runs for the first time.

• When you select schema as the granularity for object selection and a new table is added to the schema, or an existing table is rebuilt using RENAME.

• When you use the feature to modify synchronization objects.

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Replace schema and table with the actual schema name and table name. Run this command during off-peak hours to avoid deadlocks — do not lock the table while running it.

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

Logical Replication Slot Failover required

The PolarDB for PostgreSQL cluster must support and enable Logical Replication Slot Failover. Without it, a high-availability (HA) switchover in the source database can cause the synchronization instance to fail permanently and become unrecoverable.

If the cluster's Database Engine is PostgreSQL 14, Logical Replication Slot Failover is not supported. An HA switchover on such a cluster makes the synchronization instance unrecoverable.

Two-way task constraints on full and incremental synchronization

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.

Single-database scope per task

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

Unsupported objects

DTS does not synchronize:

• TimescaleDB extension tables

• Tables with cross-schema inheritance

• Tables with unique indexes based on expressions

• Schemas created by installing plugins (these schemas are also not visible in the console when configuring the task)

Partitioned tables require parent and child partitions

When synchronizing partitioned tables, include both the parent table and all its child partitions as synchronization objects. The parent table of a PostgreSQL partitioned table does not store data directly — all data is in the child partitions. If you omit any child partition, data from that partition is not synchronized, causing inconsistency.

SERIAL type requires sequence synchronization

If a table contains a SERIAL type field, PostgreSQL automatically creates a sequence for that field. When Schema Synchronization is selected as the synchronization type, also select Sequence or synchronize the entire schema. Otherwise, the synchronization instance may fail.

WAL accumulation from long-running transactions

Long-running transactions in the source database cause write-ahead log (WAL) to accumulate. If WAL accumulates faster than it can be replicated, the source database disk can fill up and make the database unavailable. Monitor long-running transactions during synchronization.

256 MB single-record limit

If a single piece of data exceeds 256 MB after an incremental change, the synchronization instance fails and cannot be recovered. You must reconfigure the instance.

No DDL operations during schema or full synchronization

Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. This causes the synchronization task to fail.

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

Performance considerations for full synchronization

During initial full data synchronization, DTS consumes read and write resources on both the source and destination databases, which may increase the database load. Evaluate the performance of the source and destination databases before you synchronize data, and perform the synchronization during off-peak hours (for example, when the CPU load of both databases is below 30%).

Because full data synchronization runs concurrent INSERT operations, it causes table fragmentation in the destination database. As a result, the table space in the destination database is larger than that in the source instance after full synchronization is complete.

No writes to the destination from other sources

During synchronization, do not write data to the destination database from sources other than DTS. Doing so causes data inconsistency between the source and destination.

Replication slot management

DTS creates a replication slot with the dts_sync_ prefix in the source database. This slot retains incremental logs for the last 15 minutes. When a synchronization task fails or the synchronization instance is released, DTS attempts to clear the slot automatically.

The slot cannot be automatically cleared if:

• The password of the source database account used by the task is changed during synchronization.

• The DTS IP address is removed from the whitelist of the source database during synchronization.

In these cases, clear the slot manually to prevent it from accumulating and consuming disk space.

If a failover occurs in the source database, log on to the secondary database to clear the slot manually.

Temporary tables created by DTS

DTS creates the following temporary tables in the source database to capture DDL statements, incremental table structures, and heartbeat information. Do not delete them during synchronization — they are automatically deleted after the DTS instance is released:

public.dts_pg_class, public.dts_pg_attribute, public.dts_pg_type, public.dts_pg_enum, public.dts_postgres_heartbeat, public.dts_ddl_command, public.dts_args_session, public.aliyun_dts_instance

Sequences are not synchronized after business switchover

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

Run the following command in the source database to get the sequence values:

do language plpgsql $$
declare
  nsp name;
  rel name;
  val int8;
begin
  for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S'
  loop
    execute format($_$select last_value from %I.%I$_$, nsp, rel) into val;
    raise notice '%',
    format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
  end loop;
end;
$$;

The output contains setval statements for all sequences. Run only the statements relevant to your use case in the destination database.

Foreign key cascade operations

For tasks involving tables with foreign keys, triggers, or event triggers, DTS temporarily sets session_replication_role to replica at the session level if the destination database account is a privileged account or has superuser permissions. If the account does not have these permissions, manually set session_replication_role to replica in the destination database.

While session_replication_role is replica, cascade update or delete operations on the source database may cause data inconsistency. After the DTS task is released, change session_replication_role back to origin.

Sequence metadata not validated

DTS validates data content but does not validate metadata such as sequences. Validate sequence values manually after synchronization.

Task failure recovery

If a task fails, DTS support staff attempt to restore it within 8 hours. During restoration, they may restart the task or adjust DTS task parameters — database parameters are not modified. Parameters that may be adjusted are listed in Modify instance parameters.

Step 1: Purchase a two-way synchronization instance

Purchase a data synchronization instance. For more information, see Purchase a data synchronization instance.

Step 2: Go to the synchronization task list

Open the data synchronization task list for the destination region using either console:

Step 3: Configure the forward task

1. Find the two-way sync instance. In the Actions column of the forward task, click Configure Task.

2. Configure the source and destination databases.

   Category	Parameter	Description
   —	Task Name	DTS automatically generates a name. Specify a descriptive name for easy identification. Names do not need to be unique.
   Source Database	Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If not registered, fill in the details manually. Note In the DMS console, this is Select a DMS database instance.
   	Database Type	Select PolarDB for PostgreSQL.
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	Fixed to the region selected when purchasing. Cannot be changed.
   	Replicate Data Across Alibaba Cloud Accounts	Select No if the database instance belongs to the current Alibaba Cloud account.
   	Instance ID	Select the ID of the source PolarDB for PostgreSQL cluster.
   	Database Name	Enter the name of the database to synchronize in the source cluster.
   	Database Account	Enter the database account. For required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account.
   Destination Database	Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If not registered, fill in the details manually. Note In the DMS console, this is Select a DMS database instance.
   	Database Type	Select PostgreSQL.
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	Fixed to the region selected when purchasing. Cannot be changed.
   	Instance ID	Select the ID of the destination RDS PostgreSQL instance.
   	Database Name	Enter the name of the database in the destination instance that receives data.
   	Database Account	Enter the database account for the destination instance. For required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account.
   	Encryption	Specifies whether to encrypt the connection to the destination database. Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, upload the CA Certificate, and optionally the Client Certificate and Private Key of Client Certificate, then specify the Private Key Password of Client Certificate. Important For a self-managed PostgreSQL database with SSL encryption, you must upload the CA Certificate. For RDS PostgreSQL SSL configuration, see SSL encryption.

3. Click Test Connectivity and Proceed.

   - Add the CIDR blocks of DTS servers to the security settings of both the source and destination databases. For more information, see Add the IP address whitelist of DTS servers. - If the source or destination is a self-managed database, also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

4. On the Configure Objects page, configure the synchronization objects.

   If the task is paused or restarted and there is a synchronization delay, these policies do not apply during the delay period — data in the destination is overwritten by default.

   Parameter	Description
   Synchronization Types	DTS always selects Incremental Data Synchronization. By default, Schema Synchronization and Full Data Synchronization are also selected. After the precheck, DTS initializes the destination with the full data of the selected source objects as the baseline for incremental synchronization.
   Exclude DDL Operations	Select Yes to skip DDL synchronization, or No to include it. DDL synchronization is only configurable for the forward task — the reverse task automatically filters all DDL operations.
   Global Conflict Resolution Policy	Select a policy for handling conflicts. See the table below.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks for same-named tables in the destination. If found, the precheck fails and the task does not start. Note If you cannot delete or rename the conflicting table, map it to a different name. See Database Table Column Name Mapping. Ignore Errors and Proceed: Skips the check. Warning This can cause data inconsistency. During full synchronization, DTS retains the destination record and skips the conflicting source record. During incremental synchronization, DTS overwrites the destination record. If schemas are inconsistent, initialization may fail.
   Capitalization of object names in destination instance	Configure the case sensitivity policy for migrated object names. By default, DTS default policy is selected. For more information, see Case sensitivity of object names in the destination database.
   Source Objects	In the Source Objects box, select the objects to synchronize and click to move them to the Selected Objects box. Note Select schemas or tables. If you select tables, views, triggers, and stored procedures are not synchronized. If a table contains the SERIAL data type and Schema Synchronization is selected, also select Sequence or synchronize the entire schema.
   Selected Objects	To rename an object in the destination, right-click it in the Selected Objects box and modify the name. See Map table and column names. To remove an object, click it and then click . Note Object name mapping may cause dependent objects to fail synchronization. To filter rows with a WHERE clause, right-click the table and specify conditions. See Filter columns. To select specific DML operations for incremental synchronization, right-click the object and select the operations.

   Global conflict resolution policies:

   Policy	Behavior
   TaskFailed	The task errors out and stops. Requires manual intervention to recover.
   Ignore	Skips the conflicting statement. The existing record in the destination is retained.
   Overwrite	Overwrites the conflicting record in the destination with data from the source.

5. Click Next: Advanced Settings and configure the following parameters.

   Parameter	Description
   Dedicated Cluster for Task Scheduling	DTS uses a shared cluster by default. For greater task stability, purchase a dedicated cluster. For more information, see What is a DTS dedicated cluster?
   Retry Time for Failed Connections	How long DTS retries after a connection failure. Default is 720 minutes. Set a value between 10 and 1,440 minutes — 30 minutes or more is advisable. If the connection is restored within this period, the task resumes automatically. Note If multiple DTS instances share a source or destination, DTS applies the shortest configured retry duration across all instances. DTS charges for task runtime during retries — set a duration appropriate to your business needs, or release the DTS instance after releasing the source or destination.
   Retry Time for Other Issues	How long DTS retries after non-connection issues (such as DDL or DML execution errors). Default is 10 minutes. Set a value between 1 and 1,440 minutes — 10 minutes or more is advisable. Important This value must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Synchronization	Limit the migration rate by setting Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). This parameter is only available when Full Data Synchronization is selected. You can also adjust the throttle while the instance is running.
   Enable Throttling for Incremental Data Synchronization	Limit the incremental synchronization rate by setting RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
   Environment Tag	(Optional) Select environment tags to identify the instance.
   Configure ETL	Choose whether to enable the extract, transform, and load (ETL) feature. Select Yes to enter data processing statements. Select No to disable it. For more information, see What is ETL? and Configure ETL.

6. Click Data Verification to configure a data verification task. For more information, see Configure data verification.

7. Click Next: Save Task Settings and Precheck.

   - To view the OpenAPI parameters for this configuration, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters. - Before the task starts, DTS performs a precheck. The task only starts if the precheck passes. - If the precheck fails, click View Details next to the failed item, fix the issue, and rerun the precheck. - For precheck warnings: fix non-ignorable warnings before proceeding. For ignorable warnings, click Confirm Alert Details, then Ignore, then OK, and then Precheck Again. Ignoring warnings may lead to data inconsistency.

8. When the Success Rate reaches 100%, click Back.

Step 4: Configure the reverse task

1. Wait for the forward synchronization task Status to become Running.

2. In the Actions column of the reverse task, click Configure Task.

3. Follow Step 3 (steps 2 through 8) to configure the reverse task.

   Important

   - The source instance in the reverse task is the destination instance in the forward task, and vice versa. Verify that the instance information — database name, account, and password — is correct. - The Instance Region of the source and destination cannot be modified for the reverse task. Fewer parameters are available compared to the forward task. - The Processing Mode of Conflicting Tables for the reverse task does not check for tables synchronized to the destination by the forward task. - The reverse task does not support objects that are not in the Selected Objects list of the forward task. - Do not use the object name mapping feature for the reverse task — it can cause data inconsistency.

4. When the Success Rate reaches 100%, click Back.

Step 5: Verify the configuration

Wait for the Status of both the forward and reverse synchronization tasks to become Running. Two-way data synchronization is now active.