Data Transmission Service:Synchronize data from an RDS for PostgreSQL instance to a SelectDB instance

Source database requirements

• Primary keys and UNIQUE constraints — All tables to be synchronized must have a primary key or a UNIQUE constraint to avoid duplicate data in the destination.

  • If the destination table was not created by DTS (that is, you did not select Schema synchronization for Synchronization types), make sure the destination table has the same primary key or non-empty UNIQUE constraint as the source table. Otherwise, duplicate data may appear in the destination.

  • For tables that have neither a primary key nor a UNIQUE constraint: select Schema synchronization for Synchronization types, then set Engine to duplicate for those tables on the Configurations for databases, tables, and columns step. If you skip this, the task may fail or data may be lost.

    Note

    During initial schema synchronization, DTS adds columns to the destination table. For details, see Additional columns.

• Database names — Database names to be synchronized cannot contain hyphens (-). For example, dts-testdata is invalid.

• Large numbers of tables — If you synchronize at the table level, edit objects (such as mapping column names), and the total number of tables in a single task exceeds 5,000, split the tables into multiple tasks or configure the task to synchronize the entire database. Exceeding this limit may cause a request error when you submit the task.

• WAL log retention — DTS reads incremental changes from the WAL. If the logs are purged before DTS processes them, the task fails and may result in data inconsistency or data loss that is not covered by the DTS service level agreement (SLA). You can change the log retention period to more than 24 hours after initial full data synchronization completes.

  Synchronization type	Minimum WAL retention period
  Incremental data synchronization only	More than 24 hours
  Full data synchronization + incremental data synchronization	At least 7 days

• Failover behavior — If the source is a self-managed PostgreSQL database and a failover occurs, the synchronization task fails. For RDS for PostgreSQL instances, enable Logical Replication Slot Failover to prevent replication interruptions during failovers. For details, see Logical Replication Slot Failover.

• Large incremental records — If a single incremental change exceeds 256 MB after encoding, the synchronization instance fails and cannot be recovered. Reconfigure the synchronization instance.

• DDL operations during synchronization — Do not run Data Definition Language (DDL) operations that change database or table schemas during schema synchronization or full data synchronization. Such operations cause the task to fail.

  During full data synchronization, DTS queries the source database and acquires metadata locks. This may block DDL operations on the source.

• Long-running transactions — If the source database has long-running transactions during incremental synchronization, WAL logs generated before those transactions are committed cannot be cleared. This causes WAL logs to accumulate and may exhaust the disk space of the source database.

• Major version upgrades — Do not perform a major engine version upgrade on the source database while a synchronization task is running. The task fails and cannot be recovered. Reconfigure the synchronization instance.

Destination and configuration requirements

• Supported table engines — DTS can only synchronize data to tables that use the Unique or Duplicate engine in SelectDB.

• Tables using the Unique engine — All unique keys in the destination table must also exist in the source table and be included as synchronization objects. Otherwise, data inconsistency may occur.

• Tables using the Duplicate engine — Duplicate data may appear in the destination in these scenarios: Use the additional columns (_is_deleted, _version, and _record_id) to identify and remove duplicates. For column details, see Additional columns.

  • The synchronization instance has been retried.

  • The synchronization instance has been restarted.

  • Two or more DML operations are performed on the same data record after the task starts.

  When the destination table uses the Duplicate engine, DTS converts UPDATE and DELETE statements to INSERT statements.

• `bucket_count` parameter — In the Selected objects box, you can only set the bucket_count parameter. The value must be a positive integer. The default is auto.

• Naming requirements — SelectDB only supports database and table names that start with a letter. If a name does not start with a letter, use the mapping feature to rename it. Similarly, if a synchronization object name (database, table, or column) contains Chinese characters, use the mapping feature to rename it to an English name. Otherwise, the task may fail.

• Single database per task — One synchronization instance can synchronize only one database. To synchronize multiple databases, configure a separate instance for each database.

• Unsupported object types — DTS does not synchronize TimescaleDB extension tables, tables with cross-schema inheritance, or tables that contain expression-based unique indexes.

• REPLICA IDENTITY FULL requirement — Before writing data to the following tables, run the command below to make sure data is consistent. Do not perform table locking operations while running this command, or the tables may become locked. If you skip the related precheck items, DTS runs this command automatically during instance initialization. Run this command when:

  • The instance runs for the first time.

  • The synchronization granularity is schema, and a new table is created in the synchronized schema, or a table is rebuilt using RENAME.

  • You use the Modify objects feature.

  ALTER TABLE schema.table REPLICA IDENTITY FULL;

  Replace schema and table with the actual schema and table names. Run this command during off-peak hours.

• Full data synchronization load — During full data synchronization, DTS uses read and write resources on both the source and destination databases, which increases database load. Evaluate the performance of both databases before starting, and schedule synchronization during off-peak hours when CPU load is below 30%.

• Backend node changes — Do not add backend (BE) nodes to the SelectDB database during synchronization. Doing so causes the task to fail. Restart the synchronization instance to resume.

• New clusters — Do not create a new cluster in the destination SelectDB instance during synchronization. Doing so causes the task to fail. Restart the synchronization instance to resume.

• Multi-table merge — In a multi-table merge scenario where data from multiple source tables is synchronized to a single destination table, all source tables must have the same schema. Otherwise, data inconsistency or task failure may occur.

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

• Partitioned tables — Include both the parent table and all its child tables as synchronization objects. The parent table of a PostgreSQL partitioned table does not store data directly — all data resides in the child tables. Omitting child tables results in data inconsistency.

• Temporary tables — DTS creates the following temporary tables in the source database to capture DDL statements, table structures, and heartbeat information. Do not delete these tables during synchronization, or the task becomes abnormal. 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, and public.aliyun_dts_instance.

• Replication slots — DTS creates a replication slot with the prefix dts_sync_ in the source database. DTS uses this slot to fetch incremental logs from the source database within 15 minutes. When the task fails or the instance is released, DTS attempts to clear this slot automatically.

  Important

  If you change the database account password or remove DTS IP addresses from the source database whitelist during synchronization, the replication slot cannot be cleared automatically. Clear it manually to prevent disk space accumulation. If a failover occurs on the source database, log on to the secondary database to clear the replication slot manually.

  

• Incremental synchronization latency — DTS uses a batch synchronization policy to reduce load on the destination. By default, DTS writes data at most once every 5 seconds per synchronization object, resulting in typical latency within 10 seconds. To reduce latency, modify the selectdb.reservoir.timeout.milliseconds parameter of the DTS instance in the console. The valid range is 1,000–10,000 milliseconds.

  Reducing the batching interval increases write frequency. This may increase the load and write response time of the destination instance, which in turn increases synchronization latency. Adjust based on the actual load of the destination.

• Task recovery — If the task fails, DTS technical support attempts recovery within 8 hours. During recovery, DTS may restart the task or adjust task parameters (not database parameters). Parameters that may be modified include those described in Modify instance parameters.

Special cases

Step 1: Go to the data synchronization task list

Use one of the following methods to navigate to the synchronization task list in the destination region.

From the DTS console:

1. Log on to the Data Transmission Service (DTS) console.

2. In the left navigation pane, click Data Synchronization.

3. In the upper-left corner, select the region where the synchronization instance resides.

From the DMS console:

The actual steps may vary based on the mode and layout of the DMS console. For details, see Simple mode console and Customize the layout and style of the DMS UI.

1. Log on to Data Management (DMS).

2. On the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Synchronization.

3. To the right of Data Synchronization Tasks, select the region where the synchronization instance resides.

Step 2: Configure the source and destination databases

1. Click Create Task.

2. Configure the source and destination databases. Task name DTS generates a task name automatically. Specify a descriptive name for easy identification. The name does not need to be unique.

   Source database

   Parameter	Description
   Select existing connection	Select a saved database instance from the list if you have added it to the system. Otherwise, configure the connection manually. In the DMS console, this parameter is named Select a DMS database instance.
   Database type	Select PostgreSQL.
   Access method	Select Alibaba Cloud Instance.
   Instance region	Select the region where the source RDS for PostgreSQL instance resides.
   Replicate data across Alibaba Cloud accounts	Select No if the source instance belongs to the current Alibaba Cloud account.
   Instance ID	Select the ID of the source RDS for PostgreSQL instance.
   Database name	Enter the name of the database containing the objects to be synchronized.
   Database account	Enter the database account. For required permissions, see Required permissions.
   Database password	Enter the password for the database account.

   Destination database

   Parameter	Description
   Select existing connection	Select a saved database instance from the list if you have added it to the system. Otherwise, configure the connection manually. In the DMS console, this parameter is named Select a DMS database instance.
   Database type	Select SelectDB.
   Access method	Select Alibaba Cloud Instance.
   Instance region	Select the region where the destination SelectDB instance resides.
   Replicate data across Alibaba Cloud accounts	Select No if the destination instance belongs to the current Alibaba Cloud account.
   Instance ID	Select the ID of the destination SelectDB instance.
   Database account	Enter the database account. For required permissions, see Required permissions.
   Database password	Enter the password for the database account.

3. Click Test Connectivity and Proceed.

   Make sure the IP address blocks of DTS servers are added to the security settings of the source and destination databases. This can be done automatically or manually. For details, see Add the IP address blocks of DTS servers to a whitelist. If the source or destination is a self-managed database (where Access method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR blocks of DTS servers dialog box.

Step 3: Configure synchronization objects

On the Configure objects page, set the following parameters.

Parameter	Description
Synchronization types	Incremental Data Synchronization is selected by default. Also select Schema Synchronization and Full Data Synchronization. After the precheck passes, DTS initializes all selected objects from the source and uses them as the baseline for incremental synchronization. Important When synchronizing from PostgreSQL to SelectDB, data types are converted. If you do not select Schema Synchronization, create Unique or Duplicate model tables with the correct schemas in the destination SelectDB instance in advance. For details, see Data type mapping and Data models.
Processing mode of conflicting tables	Precheck and report errors: DTS checks whether a table with the same name exists in the destination. If one exists, the precheck fails and the task does not start. To resolve this without deleting the table, use the mapping feature to rename it. See Map schemas, tables, and columns. Ignore errors and proceed: Skips the check. Warning This may cause data inconsistency. If schemas match, records with the same primary key or unique key in the destination are overwritten. If schemas differ, initialization may fail or only partial data may be synchronized.
Capitalization of object names in destination instance	Configure the case sensitivity policy for database, table, and column names synchronized to the destination. The default is the DTS default policy. For details, see Case sensitivity policy for destination object names.
Source objects	Click an object in the Source objects box, then click to move it to the Selected objects box. Select objects at the schema or table level.
Selected objects	Right-click an object to rename it, set a WHERE clause to filter rows, or select which SQL operations to include for incremental synchronization. To remove an object, click it and then click . If you selected Schema Synchronization and added objects at the table level, right-click a table to set the bucket_count parameter under Parameter settings. Note Using the object name mapping feature may cause synchronization failures for other objects that depend on the renamed object.

Click Next: Advanced Settings.

Step 4: Configure advanced settings (optional)

Step 5: Configure database and table fields (optional)

If you selected Schema Synchronization, click Next: Configure Database and Table Fields to set the Primary Key Column, Distribution Key, and Engine for each destination table.

Set Definition status to All to view and modify all tables.

Select one or more columns for Primary Key Column. The Distribution Key must include at least one column from the primary key.

For tables without a primary key or UNIQUE constraint, set Engine to duplicate. Otherwise, the task may fail or data may be lost.

Step 6: Run the precheck and purchase the instance

1. Click Next: Save Task Settings and Precheck.

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

2. Wait for the precheck to complete. The task can start only after all precheck items pass.

   • If a precheck item fails, click View Details, fix the issue, and run the precheck again.

   • If a precheck item generates a warning that can be ignored, click Confirm Alert Details, Ignore, OK, and Precheck Again in sequence. Ignoring warnings may cause data inconsistency.

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

4. On the Purchase page, select the billing method and link specification.

   Parameter	Description
   Billing method	Subscription: Pay upfront for a set duration. Suitable for long-term use and more cost-effective than pay-as-you-go. Longer subscriptions offer higher discounts. Pay-as-you-go: Charged hourly. Suitable for short-term use. Release the instance after use to stop charges.
   Resource group configuration	The resource group to which the instance belongs. The default is the default resource group. See What is Resource Management?.
   Link specification	The synchronization performance level. Higher specifications support higher synchronization rates. See Data synchronization link specifications.
   Subscription duration	(Subscription only) Choose monthly (1–9 months) or yearly (1, 2, 3, or 5 years).

5. Read the Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start. In the confirmation dialog box, click OK.

You can monitor task progress on the Data Synchronization page.