Data Transmission Service:Two-way synchronization between PolarDB for PostgreSQL clusters

Source database requirements

• Tables must have a primary key or a non-null unique index.

• Avoid long-running transactions on the source cluster. Uncommitted transactions cause write-ahead log (WAL) accumulation, which can exhaust disk space.

• If a single incremental change exceeds 256 MB, the synchronization instance fails and cannot be recovered. Reconfigure the task from scratch.

• Do not run DDL operations during schema synchronization or full data synchronization. DTS creates metadata locks during full sync that can block DDL on the source.

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

Partitioned tables

Include both the parent table and all child partitions as synchronization objects. A PostgreSQL partitioned table stores no data in the parent table — all data lives in child partitions. If you omit any child partition, that data is not synchronized.

SERIAL columns and sequences

If a synchronized table has a SERIAL column, PostgreSQL automatically creates a backing sequence. When Schema Synchronization is selected, also select Sequence or synchronize the entire schema to avoid synchronization instance failures.

After a business switchover to the destination cluster, sequences do not automatically resume from the source's maximum value. Before switching over, run the following query on the source cluster to get current sequence values, then apply the returned setval statements on the destination cluster:

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 query returns setval statements for all sequences in the source database. Run only the statements relevant to the tables you synchronized.

Tables requiring REPLICA IDENTITY FULL

Run ALTER TABLE schema.table REPLICA IDENTITY FULL; on source tables before writing data in any of these three situations:

1. When the synchronization instance runs for the first time.

2. When using schema-level object selection and a new table is added to the schema, or an existing table is rebuilt with RENAME.

3. When using the modify synchronization objects feature.

Run this command during off-peak hours and avoid table locks to prevent deadlocks. If you skip the related precheck items, DTS runs this command automatically during initialization.

Foreign keys, triggers, and event triggers

For 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 lacks those permissions, set session_replication_role to replica on the destination manually. Cascade update or delete operations on the source during this period may cause data inconsistency. After the DTS task is released, reset session_replication_role to origin.

Replication slots

DTS creates a replication slot with the dts_sync_ prefix in the source database to read incremental logs from up to 15 minutes prior. When a synchronization task fails or its instance is released, DTS attempts to clean up the slot automatically.

The slot cannot be cleaned automatically if you change the source database account password or remove DTS IP addresses from the source cluster's whitelist. In those cases, manually delete the replication slot in the source database to prevent disk exhaustion. If a failover occurs, log on to the secondary node to clear the slot.

Temporary tables

DTS creates the following temporary tables in the source database to capture DDL statements, incremental table structures, and heartbeat data. Do not delete these tables while the task is running — they are removed automatically when 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

Other limits

• A single synchronization task can synchronize only one database. Create a separate task for each additional database.

• Do not write data to the destination cluster from sources other than DTS during synchronization. This causes data inconsistency.

• 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: only one task can perform full and incremental synchronization; the other supports incremental synchronization only. Data from the source of one task syncs only to that task's destination — it does not become source data for the other task.

• Full data synchronization runs concurrent INSERT operations and consumes read and write resources on both clusters. Run full synchronization during off-peak hours when CPU load on both clusters is below 30%. Expect the destination table space to be larger than the source after full synchronization due to table fragmentation.

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

• If a task fails, DTS support attempts to restore it within 8 hours. Restoration may involve restarting the task or adjusting task parameters (not database parameters). See Modify instance parameters for the list of adjustable parameters.

Conflict resolution policies

Select a policy when configuring the two-way synchronization task:

These policies apply only when the task is running normally. If the task is paused or restarted with latency, DTS defaults to overwriting destination data regardless of the configured policy.

Forward vs. reverse task configuration differences

Before you start, review how the two tasks differ. Configuring the wrong settings in the reverse task is the most common source of errors.

Step 1: Open the data synchronization task list

Open the DTS task list using either the DTS console or the DMS console.

Step 2: Create the task

Click Create Task to open the task configuration page.

Step 3: Configure source and destination databases

Click Test Connectivity and Proceed at the bottom of the page.

DTS must be able to reach both clusters. Add the DTS service IP address blocks to the security settings of both clusters — either automatically during the connectivity test or manually beforehand. See Add the IP address whitelist of DTS servers.

Step 4: Configure synchronization objects

On the Configure Objects page, set the following options:

Click Next: Advanced Settings.

Step 5: Configure advanced settings

Step 6: Configure data verification (optional)

Click Data Verification to set up a data verification task. See Configure data verification.

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

Step 7: Save settings, run precheck, and purchase the instance

1. To preview API parameters before saving, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

2. Click Next: Save Task Settings and Precheck. DTS runs a precheck before starting the task.

   • If the precheck fails, click View Details next to the failed item, fix the issue, and rerun the precheck.

   • If the precheck shows warnings:

     • For non-ignorable warnings: click View Details, fix the issue, and rerun.

     • For ignorable warnings: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring warnings may cause data inconsistencies.

3. When Success Rate reaches 100%, click Next: Purchase Instance.

4. On the Purchase page, select billing and instance options:

   Option	Description
   Billing Method	Subscription: Pay upfront for a fixed duration. Suited for long-term continuous tasks. Pay-as-you-go: Billed hourly. Suited for short-term or test tasks.
   Resource Group Settings	The resource group for the instance. Default: default resource group. See What is Resource Management?
   Instance Class	Select based on your required synchronization throughput. See Data synchronization link specifications.
   Subscription Duration	Available when Subscription is selected. Monthly: 1–9 months. Yearly: 1, 2, 3, or 5 years.

5. Select the Data Transmission Service (Pay-as-you-go) Service Terms checkbox, then click Buy and Start > OK.

The forward synchronization task appears on the data synchronization page.

Step 8: Configure the reverse synchronization task

1. Wait until the forward task's Status shows Running.

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

3. Repeat steps 3–6 for the reverse task, with the following differences:

   Important

   - The source and destination are swapped: the destination in the forward task is the source in the reverse task, and vice versa. Verify instance IDs, database names, accounts, and passwords carefully. - Instance Region cannot be changed for the reverse task. Fewer configuration options are available. - Processing Mode of Conflicting Tables for the reverse task does not check for tables already synchronized by the forward task. - The reverse task can only synchronize objects already in the forward task's Selected Objects list. - Do not use the object name mapping feature in the reverse task — this can cause data inconsistency.

4. When Success Rate shows 100%, click Back.

5. Wait until both the forward and reverse tasks show Status as Running.

Two-way synchronization is now active.