Synchronize data from ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL - AnalyticDB

Use Data Transmission Service (DTS) to replicate data from an ApsaraDB RDS for PostgreSQL source into an AnalyticDB for PostgreSQL instance for analytics and reporting. DTS handles both the initial full load and ongoing incremental changes (INSERT, UPDATE, DELETE), so your analytical database stays in sync without manual exports.

Prerequisites

Before you begin, make sure that you have:

Tables to synchronize with primary keys. DTS uses logical replication for change data capture, and tables without primary keys cannot be tracked for row-level changes.
A destination AnalyticDB for PostgreSQL instance. For more information, see Create an AnalyticDB for PostgreSQL instance.

Limitations

Review these limitations before configuring the synchronization task:

Schema synchronization is not supported. DTS does not copy schemas from the source to the destination. Create the target database, schemas, and tables manually before starting the task.
Only tables can be selected as objects to synchronize. Views, sequences, and other object types are not supported.
Unsupported data types: BIT, VARBIT, GEOMETRY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT. Columns of these types are skipped during synchronization.
DDL operations are not automatically propagated. If you run a DDL statement on a source table during synchronization, apply the same DDL to the destination table and then restart the synchronization task.
One task per database. A single synchronization task covers one source database. To synchronize multiple databases, create one task per database.
Primary/secondary switchover is only supported on ApsaraDB RDS for PostgreSQL 11. For other RDS versions and self-managed PostgreSQL databases, a primary/secondary switchover causes the task to fail. If you need switchover support on a PostgreSQL 11 instance, set the rds_failover_slot_mode parameter to sync before performing the switchover. For more information, see Logical Replication Slot Failover.

Billing

Synchronization type	Fee
Schema synchronization and full data synchronization	Free of charge
Incremental data synchronization	Charged. For more information, see Billing overview.

Important considerations

Long-running transactions and WAL accumulation

DTS uses write-ahead logging (WAL) to capture incremental changes. If a long-running transaction remains open in the source database, PostgreSQL cannot remove the WAL segments generated before that transaction started. Those segments accumulate on disk until the transaction commits or is rolled back. If the source disk fills up, the synchronization task fails.

To reduce this risk, monitor open transactions on the source instance during active synchronization and keep transactions short.

New tables created after synchronization starts

DTS can detect tables created in the source database after the task starts, but those tables require row-level identity to be configured first:

ALTER TABLE schema.table REPLICA IDENTITY FULL;

Run this statement on each new table before DTS processes it to make sure row changes are captured correctly.

Prepare the source instance

DTS uses PostgreSQL logical replication to capture incremental changes. Logical replication requires the wal_level parameter to be set to logical.

Warning

Changing wal_level requires an instance restart. Plan this change during off-peak hours to minimize impact on your workload.

Log on to the ApsaraDB RDS console.
In the left-side navigation pane, click Instances.
In the top navigation bar, select the region where the ApsaraDB RDS for PostgreSQL instance resides.
Click the ID of the instance.
In the left-side navigation pane, click Parameters.
On the Parameters page, find wal_level and set its value to logical. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
Restart the instance to apply the change.

Prepare the destination instance

Create the target database, schema, and tables in the AnalyticDB for PostgreSQL instance based on the source schema. DTS does not create these objects automatically. For SQL syntax, see SQL syntax.

Create a synchronization task

Purchase a data synchronization instance. For more information, see Purchase a DTS instance.
On the buy page, set Source Instance to PostgreSQL, set Destination Instance to AnalyticDB for PostgreSQL, and set Synchronization Topology to One-Way Synchronization.
Log on to the DTS console.
If you are redirected to the Data Management (DMS) console, move the pointer over the icon in the lower-right corner and click the icon to return to the DTS console. If you are in the new version of the DTS console, click the icon in the lower-right corner.
In the left-side navigation pane, click Data Synchronization.
In the upper part of the Data Synchronization Tasks page, select the region where the destination instance resides.
Find the data synchronization instance and click Configure Task in the Actions column.

Configure the source and destination instances.

Configure the source and destination instances

Source instance details

Parameter	Description
Synchronization task name	The name DTS automatically generates. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.
Instance type	Select RDS Instance.
Instance region	The source region selected on the buy page. This value cannot be changed.
Instance ID	The ID of the ApsaraDB RDS for PostgreSQL instance.
Database name	The name of the source database.
Database account	The privileged account of the ApsaraDB RDS for PostgreSQL instance. The account must be the owner of the selected database. If the source runs PostgreSQL 9.4 and you synchronize only DML operations, the REPLICATION permission is sufficient.
Database password	The password of the database account.

Destination instance details

Parameter	Description
Instance type	Fixed to AnalyticDB for PostgreSQL.
Instance region	The destination region selected on the buy page. This value cannot be changed.
Instance ID	The ID of the destination AnalyticDB for PostgreSQL instance.
Database name	The name of the destination database. The database must already exist in the instance. If it does not, create a database first.
Database account	The initial account of the AnalyticDB for PostgreSQL instance. You can also use an account with the RDS_SUPERUSER permission. For more information, see Create a database account and Manage users and permissions.
Database password	The password of the database account.

Click Set Whitelist and Next. DTS automatically adds its server CIDR blocks to the IP address whitelist of Alibaba Cloud database instances. For self-managed databases hosted on Elastic Compute Service (ECS) instances, DTS adds the CIDR blocks to the ECS security group rules; if the database spans multiple ECS instances, add the CIDR blocks to each instance manually. For databases deployed in data centers or provided by third-party cloud providers, add the CIDR blocks manually. For more information, see Add the CIDR blocks of DTS servers.
Warning
Adding DTS server CIDR blocks to your whitelist or security group rules introduces network exposure. Before proceeding, evaluate the security implications and take appropriate measures, such as using strong credentials, restricting exposed ports, auditing API calls, reviewing whitelist rules regularly, and connecting through Express Connect, VPN Gateway, or Smart Access Gateway (SAG) instead of public internet.

Configure the synchronization policy and the objects to be synchronized.

Select the objects to be synchronized

Parameter	Description
Initialize synchronization	Initial Full Data Synchronization is selected by default. DTS synchronizes all existing data from the source database before starting incremental synchronization.
Processing mode of conflicting tables	Clear Data: Skips the empty-table check during precheck and clears destination table data before the full load. Use this when testing a task and then switching to production data. Ignore: Skips the empty-table check and appends data to existing destination table data. Use this when merging data from multiple source tables into one destination table.
Synchronization type	Select the operation types to synchronize: Insert, Update, and Delete. The AlterTable option is listed but not supported.
Select the objects to be synchronized	In the Available section, select one or more tables and click the icon to move them to the Selected section. Only tables can be selected. To rename synchronized objects in the destination, use the object name mapping feature. For more information, see Object name mapping.
Retry time for failed connections	Default: 720 minutes (12 hours). If DTS cannot connect to the source or destination within this window, the task fails. Adjust based on your acceptable downtime. DTS billing continues during retries, so release the DTS instance promptly if the source or destination is decommissioned.

Click Precheck.
DTS runs a precheck before starting the task. If the precheck fails, click the icon next to each failed item to view details, fix the issues, and run the precheck again. You can also ignore specific failed items and rerun the precheck if the issues do not affect your use case.
After the Precheck Passed message appears, close the Precheck dialog box. The synchronization task starts automatically.
On the Synchronization Tasks page, wait until the task enters the Synchronizing state, which indicates that the initial full data synchronization is complete and incremental synchronization is active.

What's next

To rename tables or columns in the destination database, see Rename an object to be synchronized.
To monitor synchronization latency and task health, view the task details in the DTS console.