Data Transmission Service:Configure one-way data synchronization between ApsaraDB RDS for PostgreSQL instances

Source database requirements

• Tables must have a PRIMARY KEY or UNIQUE constraint, with all fields unique. Without these constraints, the destination database may contain duplicate records.

  If you create the synchronization task without selecting Schema Synchronization, the destination table must have the same PRIMARY KEY or NOT NULL UNIQUE constraints as the source table. Otherwise, duplicate records may appear.

• If you select tables as the synchronization objects and want to rename tables or columns in the destination, a single task supports a maximum of 5,000 tables. For more than 5,000 tables, split the workload across multiple tasks, or synchronize at the database level instead of the table level.

• DTS cannot synchronize temporary tables, internal triggers, or certain internal procedures and functions written in C. DTS can synchronize custom parameters of the COMPOSITE, ENUM, and RANGE types. Tables must have PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraints.

• DTS creates a replication slot prefixed with dts_sync_ on the source database, giving DTS access to incremental logs from the past 15 minutes. Replication slots that accumulate can exhaust disk space. Set a disk usage alarm on the source instance when logical replication is active. Replication slot cleanup behavior:

  • After the DTS instance is released, the slot is deleted automatically — unless you changed the source database password or removed DTS IP addresses from the IP whitelist, in which case you must delete the slot manually.

  • If a primary/secondary switchover occurs on the source, log on to the secondary database and delete the slot manually.

• DTS creates the following temporary tables in the source database to capture DDL statements, incremental table schemas, and heartbeat information. Do not delete them while synchronization is running — they are deleted 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

• DTS adds a heartbeat table named dts_postgres_heartbeat to the source database to track synchronization latency accurately.

• To perform a primary/secondary switchover on the source ApsaraDB RDS for PostgreSQL instance without interrupting synchronization, enable the Logical Replication Slot Failover feature first. See Logical Replication Slot Failover.

• If a single incremental data change exceeds 256 MB, the running synchronization instance fails and cannot be recovered — you must reconfigure the task.

• During schema synchronization and full data synchronization, do not execute DDL statements that change database or table schemas. The task will fail if you do.

• If the source database has long-running transactions during incremental data synchronization, WAL logs generated before those transactions are committed may accumulate and exhaust disk space.

• If a major version upgrade is performed on the source database while a synchronization instance is running, the instance fails and cannot be recovered — you must reconfigure the task.

Other requirements

• A single task synchronizes data from one database only. To synchronize multiple databases, create a separate task for each.

• DTS cannot synchronize tables that have inheritance relationships across schemas.

• If you select a schema as the synchronization object, run the following statement before writing data to any new or renamed table in that schema:

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with the actual names. Run this statement during off-peak hours and avoid locking the table — locking it while running this statement can cause a deadlock.

• DTS does not validate metadata such as sequences. Check sequence validity manually.

• Before switching workloads to the destination database, update the starting value of sequences in the destination. After the switch, newly written sequences do not continue from the maximum value in the source.

• When the destination account is a privileged or superuser account and synchronized tables contain foreign keys, triggers, or event triggers, DTS temporarily sets session_replication_role to replica at the session level. If the destination account lacks the required permissions, set this parameter to replica manually. Cascade UPDATE or DELETE operations on the source during this time may cause data inconsistency. After the task is released, set the parameter back to origin.

• Writing data from other sources to the destination database during synchronization causes data inconsistency. For example, running online DDL statements from DMS while other sources write to the destination database may cause data loss.

• If a task fails, DTS technical support will attempt to restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be modified.

Requirements for specific source database types

ApsaraDB RDS for PostgreSQL source

Do not modify the endpoint or zone of the source instance while synchronization is running. This will cause the task to fail.

Self-managed PostgreSQL source

The values of max_wal_senders and max_replication_slots must each be greater than the sum of the number of replication slots currently in use and the number of DTS instances you plan to create for this database.

Cloud SQL for PostgreSQL (Google Cloud) source

Set Database Account to an account with the cloudsqlsuperuser permission. When selecting objects to synchronize, choose only objects the account is authorized to manage, or grant the OWNER permission on those objects to the account.

An account with cloudsqlsuperuser permission cannot manage data owned by other accounts with the same permission.

Step 1: Open the data synchronization page

Use either the DTS console or the DMS console.

Step 2: Create the task

1. Click Create Task.

2. If the page shows a New Configuration Page button in the upper-right corner, click it.

   Skip this step if Back to Previous Version is shown instead. The new and previous configuration pages may differ slightly — use the new version.

Step 3: Configure source and destination databases

Step 4: Test connectivity

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

DTS server CIDR blocks must be added to the security settings of both databases. See Add the CIDR blocks of DTS servers. If either database is self-managed and not set to Alibaba Cloud Instance access, click Test Connectivity in the CIDR Blocks of DTS Servers dialog.

Step 5: Configure objects to synchronize

In the Configure Objects step, set the following parameters:

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following:

Step 7: Configure data verification (optional)

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

Step 8: Run the precheck

Click Next: Save Task Settings and Precheck.

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

DTS runs a precheck before the task can start. If the precheck fails:

• Click View Details next to each failed item, resolve the issue, then click Precheck Again.

• If an item shows an alert that can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may lead to data inconsistency.

Step 9: Purchase and start the instance

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

2. On the purchase page, configure the following:

   Section	Parameter	Description
   New Instance Class	Billing Method	Subscription: You pay for a subscription when you create a data synchronization instance. The subscription billing method is more cost-effective than the pay-as-you-go billing method for long-term use. Pay-as-you-go: A pay-as-you-go instance is billed on an hourly basis. The pay-as-you-go billing method is suitable for short-term use. If you no longer require a pay-as-you-go data synchronization instance, you can release the instance to reduce costs.
   	Resource Group Settings	The resource group to which the data synchronization instance belongs. Default value: default resource group. For more information, see What is Resource Management?
   	Instance Class	DTS provides instance classes that vary in synchronization speed. You can select an instance class based on your business requirements. For more information, see Instance classes of data synchronization instances.
   	Subscription Duration	If you select the subscription billing method, specify the subscription duration and the number of data synchronization instances that you want to create. The subscription duration can be one to nine months, one year, two years, three years, or five years. Note This parameter is available only if you select the Subscription billing method.

3. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

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

View the task progress in the task list.