Data Transmission Service:Synchronize data between AnalyticDB for PostgreSQL instances

REPLICA IDENTITY requirement

In the following three scenarios, run ALTER TABLE schema.table REPLICA IDENTITY FULL; on the tables to be synchronized before writing data to them. Run this command during off-peak hours to avoid table locks.

• When the instance runs for the first time.

• When synchronizing at the schema level and a new table is created, or an existing table is rebuilt using the RENAME command.

• When using the modify-synchronization-objects feature.

Replace schema and table with the actual schema name and table name. If you skip the related precheck, DTS runs this command automatically during initialization.

Replication slot behavior

DTS creates a replication slot prefixed with dts_sync_ in the source database. This slot provides access to incremental logs from the last 15 minutes.

DTS cleans up the replication slot when the task fails or the instance is released. The slot is not cleaned up automatically if you change the source database account password or remove the DTS IP address from the source database whitelist during synchronization. In that case, clean up the replication slot manually to prevent disk space accumulation.

If a primary/secondary failover occurs, log in to the secondary database to clean up the replication slot.

Temporary tables created by DTS

DTS creates the following temporary tables in the source database to support incremental synchronization. Do not delete them 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, and public.aliyun_dts_instance.

Step 1: Go to the Data Synchronization page

Step 2: Configure source and destination databases

1. Click Create Task.

2. Configure the parameters described in the following table.

1. Click Test Connectivity and Proceed.

Make sure the DTS server CIDR blocks are added to the security settings of the source and destination databases. See Add DTS server IP addresses to a whitelist.

Step 3: Configure synchronization objects

1. In the Configure Objects step, set the following parameters.

Parameter	Description
Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Full data synchronization copies the historical data needed as the basis for incremental synchronization.
DDL and DML Operations to Be Synchronized	Select the SQL operations for incremental synchronization at the instance level. To configure at the schema or table level, right-click a synchronization object in Selected Objects and choose the desired operations.
Processing Mode of Conflicting Tables	Precheck and Report Errors (default): checks whether tables in the destination have the same names as tables in the source. If identical names exist, the precheck fails and the task cannot start. To work around naming conflicts, use the object name mapping feature to rename the tables in the destination. Ignore Errors and Proceed: skips the naming check. During full synchronization, conflicting rows in the destination are kept. During incremental synchronization, conflicting rows are overwritten. If the source and destination have different schemas, initialization may fail or only some columns may be synchronized.
Storage Engine Type	Select the storage engine for the destination tables. Default: Beam. Available only if the destination instance runs kernel version v7.0.6.6 or later and Schema Synchronization is selected.
Capitalization of Object Names in Destination Instance	Controls how database names, table names, and column names are capitalized in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select one or more objects and click the icon to move them to Selected Objects. Select at the schema or table level.
Selected Objects	To rename an object in the destination or map it to a different receiving object, right-click it. See Map object names. To remove an object, click it and then click the icon. To filter rows by condition, right-click a table and specify the WHERE clause. See Specify filter conditions.

Renaming an object with object name mapping may cause dependent objects to fail synchronization.

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

1. (Optional) Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, and Distribution Key for tables to be synchronized to the destination instance. You can select multiple columns for the primary key to create a composite primary key — at least one primary key column must also be the distribution key. See Manage data tables and Table distribution definition.

This step is available only if Schema Synchronization is selected in Synchronization Types. Set Definition Status to All to view and edit all tables.

Step 4: Run the precheck and purchase the instance

1. Click Next: Save Task Settings and Precheck.

To preview the OpenAPI parameters for this task configuration, hover over the button and click Preview OpenAPI parameters before clicking it.

DTS performs a precheck before the task can start.

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

• If an alert appears for an item that can be ignored, click Confirm Alert Details, then Ignore, then OK, then Precheck Again. Ignoring alerts may cause data inconsistency.

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following parameters.

1. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

2. Click Buy and Start, then click OK in the dialog box.

The task appears in the task list. Monitor its progress from there.

Verify synchronization latency

To ensure the accuracy of the displayed synchronization latency, DTS adds a heartbeat table named dts_postgres_heartbeat to the source database. Monitor the latency value in the DTS console task list before switching traffic.

Handle sequences

After switching to the destination, new sequences do not automatically start from the maximum value of the source sequences. Before cutover, query the maximum sequence values in the source and set them as the starting values in the destination.

Run the following command on the source database to get the required setval statements:

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 includes setval statements for all sequences in the source database. Run the relevant statements on the destination database as required before cutover.

DTS validates data content but does not validate sequence metadata. Validate sequences manually.