Synchronize data from an RDS MariaDB instance to an RDS PostgreSQL instance - Data Transmission Service

Use Data Transmission Service (DTS) to synchronize data from an ApsaraDB RDS for MariaDB instance to an ApsaraDB RDS for PostgreSQL instance. This topic describes how to prepare both instances and configure a one-way synchronization task that covers schema, full, and incremental data synchronization.

Prerequisites

Complete the following preparations before you configure the synchronization task.

Destination instance — You have created a destination ApsaraDB RDS for PostgreSQL instance whose storage space is larger than the storage space that the source ApsaraDB RDS for MariaDB instance uses. For more information, see create an ApsaraDB RDS for PostgreSQL instance.
Destination database — You have created a database in the destination ApsaraDB RDS for PostgreSQL instance to receive the synchronized data. For more information, see Create an account and a database.
Binary logging on the source instance — ApsaraDB RDS for MariaDB enables binary logging by default. Make sure that the binlog_row_image parameter is set to full. Otherwise, the precheck reports an error and the data synchronization task cannot start. For more information about how to configure the parameter, see Configure instance parameters.
Binary log retention period — For an incremental data synchronization task, DTS requires the local binary logs of the source database to be retained for at least 24 hours. For a task that performs both full and incremental data synchronization, DTS requires the local binary logs to be retained for at least 7 days. You can change the retention period to 24 hours or more after the full data synchronization is complete. Otherwise, DTS may fail to obtain the binary logs and the task fails. In extreme cases, data inconsistency or data loss may occur. Issues caused by a binary log retention period shorter than the period that DTS requires are not covered by the DTS Service Level Agreement (SLA).
Primary keys on the source tables — The tables to synchronize must have a primary key or a UNIQUE constraint, and the fields must be unique. Otherwise, duplicate data may appear in the destination database.
Database account permissions — The source and destination database accounts that you use in the task must have the permissions that DTS requires. For more information, see Database account permissions.
Network access for DTS servers — Make sure that the CIDR blocks of DTS servers are added to the security settings of the source and destination databases so that DTS servers can access both databases. You can add them automatically or manually. For more information, see Add the CIDR blocks of DTS servers to a whitelist.

Usage notes

The following table describes the behaviors and risks that apply while the synchronization task runs.

Note

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full data synchronization and incremental data synchronization, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. Data inconsistency may occur if cascade update or delete operations are performed on the source database while the task is running.

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full data synchronization and incremental data synchronization, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. Data inconsistency may occur if cascade update or delete operations are performed on the source database while the task is running.

Note

During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full and incremental synchronization, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. If cascade update or delete operations occur in the source database while the task is running, data inconsistency may occur.

Type	Description
Source database limitations	- If you synchronize objects at the table level and need to edit them, such as mapping column names, a single synchronization task supports up to 1,000 tables. If you exceed this limit, an error is returned after you submit the task. In this case, split the tables and configure several tasks, or configure one task that synchronizes the entire database. - Do not run DDL operations that change database or table schemas during schema synchronization or full synchronization. Otherwise, the synchronization task fails. Note: During full synchronization, DTS queries the source database. This creates metadata locks that may block DDL operations on the source database. - If the data to be synchronized contains invisible columns, data loss may occur because DTS cannot obtain the data in those columns.
Other limitations	- Evaluate the performance of the source and destination databases before you synchronize data, and synchronize data during off-peak hours. Otherwise, initial full data synchronization consumes read and write resources of both databases and may increase their load. To limit the resources that the task consumes, configure throttling when you configure the advanced settings. - Initial full data synchronization runs INSERT operations concurrently. The concurrent writes cause fragmentation in the tables of the destination database, so after the initial full data synchronization is complete, the tablespace of the destination instance is larger than the tablespace of the source instance. - If the synchronization objects are one or more tables instead of an entire database, do not use a tool such as pt-online-schema-change to perform an online DDL operation on the synchronization objects in the source database during synchronization. Otherwise, the synchronization task fails. You can use Data Management (DMS) to perform online DDL operations, which are described in the topic about lock-free schema changes. - While DTS is synchronizing data, do not let any writer other than DTS write data to the destination database. Otherwise, the data in the source and destination databases becomes inconsistent. For example, if another writer writes data to the destination database, performing an online DDL operation in DMS may cause data loss in the destination database. - Time fields support the Timestamp data type. If the value of a time field in the source database is 0, DTS automatically converts the value to null in the destination database. - For a full or incremental data synchronization task, if the source tables to be synchronized contain foreign keys, triggers, or event triggers, DTS temporarily sets the `session_replication_role` parameter to `replica` at the session level. If the destination database account does not have the required permissions, you must manually set the `session_replication_role` parameter to `replica` in the destination database. During this period, if cascade update or delete operations occur in the source database while `session_replication_role` is set to `replica`, data inconsistency may occur. After the DTS task is released, change the `session_replication_role` parameter back to `origin`. - If a task fails, DTS technical support attempts to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note: Only the parameters of the DTS task are modified, not the parameters of the database. The parameters that may be adjusted include, but are not limited to, the task parameters that DTS can adjust.
Special cases	The following limitations apply only if the source database is a self-managed MariaDB database, not an ApsaraDB RDS for MariaDB instance: - If a primary/secondary switchover occurs on the source database during synchronization, the synchronization task fails. - DTS calculates the latency by comparing the timestamp of the last data record synchronized to the destination database with the current timestamp. If no DML operation runs on the source database for a long time, the reported latency may be inaccurate. If the task reports an unexpectedly high latency, run a DML operation on the source database to update the latency. Note: If you select an entire database as the synchronization objects, you can also create a heartbeat table that is updated or written to every second. - DTS periodically runs the CREATE DATABASE IF NOT EXISTS `test` command on the source database to advance the binary log position.

Supported synchronization topologies

DTS supports the following one-way synchronization topologies between ApsaraDB RDS for MariaDB and ApsaraDB RDS for PostgreSQL:

One-to-one one-way synchronization
One-to-many one-way synchronization
Many-to-one one-way synchronization

Supported SQL operations

The following table describes the SQL operations that DTS synchronizes from the source ApsaraDB RDS for MariaDB instance to the destination ApsaraDB RDS for PostgreSQL instance.

Operation type	SQL operations
DML	INSERT, UPDATE, DELETE

Database account permissions

The following table describes the permissions that the database accounts used by the synchronization task require, and how to create the accounts and grant the permissions.

Database	Required permissions	How to create an account and grant permissions
ApsaraDB RDS for MariaDB instance	The SELECT permission	Create an account and Modify or reset account permissions
ApsaraDB RDS for PostgreSQLApsaraDB RDS for PostgreSQL instance	The CREATE and USAGE permissions on the objects to synchronize, and the owner permission on the schema	Create an account and Authorize a service account

Billing

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

Billing

The following table describes how DTS charges for each synchronization type. Review the pricing before you purchase the synchronization instance.

Synchronization type	Pricing
Schema synchronization and full data synchronization	Free of charge.
Incremental data synchronization	Charged.

Procedure

Go to the synchronization task list page of the destination region. Use either of the following methods:
The following steps use the DTS console. If you manage your databases in Data Management (DMS), use the DMS console instead. The DMS console interface varies based on its mode and layout, and some parameters have different names.
a. Log on to the Data Transmission Service (DTS) console.
b. In the left-side navigation pane, click Data Synchronization.
c. In the upper-left corner of the page, select the region where the synchronization instance resides.
Note
The actual steps may differ depending on the mode and layout of the DMS console. For more information, see Simple mode and Customize the layout and style of the DMS interface.
a. Log on to Data Management (DMS).
b. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Synchronization.
c. To the right of Data Synchronization Tasks, select the region where the synchronization instance resides.
Click Create Task to open the task configuration page.

Configure the source and destination databases. The following table describes the parameters.

Category	Parameter	Description
N/A	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique. DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not have to be unique.
Source database	Select Existing Connection	To use a database instance that is added to the system (newly created or saved), select the database instance from the drop-down list. The database information is filled in automatically. In the DMS console, this parameter is named Select a DMS database instance.. If you have not added the database instance to the system, or you do not want to use an instance that is already added, configure the following database information manually.
	Database Type	Select MariaDB.
	Connection Type	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the source instance (an ApsaraDB RDS for MariaDB instance) resides.
	Replicate Data Across Alibaba Cloud Accounts	This example uses a database instance that belongs to the current Alibaba Cloud account. Select No.
	Instance ID	Select the ID of the source ApsaraDB RDS for MariaDB instance.
	Database Account	Enter the database account of the source ApsaraDB RDS for MariaDB instance.
	Database Password	Enter the password for the specified database account. Enter the password that corresponds to the database account.
	Connection Method	Select Non-encrypted.
Destination database	Select Existing Connection	To use a database instance that is added to the system (newly created or saved), select the database instance from the drop-down list. The database information is filled in automatically. In the DMS console, this parameter is named Select a DMS database instance.. If you have not added the database instance to the system, or you do not want to use an instance that is already added, configure the following database information manually.
	Database Type	Select PostgreSQL.
	Connection Type	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the destination instance (an ApsaraDB RDS for PostgreSQLApsaraDB RDS for PostgreSQL instance) resides.
	Instance ID	Select the ID of the destination instance (an ApsaraDB RDS for PostgreSQLApsaraDB RDS for PostgreSQL instance).
	Database Name	Enter the name of the database that contains the synchronization objects in the destination instance (an ApsaraDB RDS for PostgreSQLApsaraDB RDS for PostgreSQL instance).
	Database Account	Enter the database account of the destination instance (an ApsaraDB RDS for PostgreSQLApsaraDB RDS for PostgreSQL instance). The account must have the owner permission on the schema. For more information about how to create an account and grant permissions, see Create an account.
	Database Password	Enter the password for the specified database account. Enter the password that corresponds to the database account.
	Encryption	Specifies whether to encrypt the connection to the destination instance. Select a value based on your security requirements: select Non-encrypted if the connection does not need to be encrypted, or SSL-encrypted if your business requires an encrypted connection. In this example, Non-encrypted is selected. If you select SSL-encrypted, upload CA Certificate, Client Certificate, and Private Key of Client Certificate as needed, and then specify Private Key Password of Client Certificate: To use the client certificate, you must upload Client Certificate and Private Key of Client Certificate and specify Private Key Password of Client Certificate. If you set Encryption to SSL-encrypted for a self-managed PostgreSQL database, you must upload CA Certificate. For more information about how to configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance, see Configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance.

After you complete the configuration, click Test Connectivity and Proceed at the bottom of the page.
If the source or destination database is a self-managed database, which means that Access Method is not set to Alibaba Cloud Instance, you must also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

On the Configure Objects page, configure the objects to synchronize. The following table describes the parameters.

Parameter	Description
Synchronization Types	The synchronization types. By default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. After the precheck is complete, DTS synchronizes the historical data of the selected objects from the source database to the destination cluster. The historical data is the basis for subsequent incremental synchronization. The synchronization types. By default, Incremental Data Synchronization is selected. You must also select Schema Synchronization and Full Data Synchronization. After the precheck is complete, DTS synchronizes the historical data of the selected objects from the source database to the destination instance. The historical data is the basis for subsequent incremental synchronization. Note: The binary log retention period that DTS requires depends on the synchronization types that you select. For more information, see Prerequisites.
Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks for tables with the same names in the destination database. If any tables with the same names are found, an error is reported during the precheck and the data synchronization task does not start. Otherwise, the precheck is successful. Note If you cannot delete or rename the table with the same name in the destination database, you can map it to a different name in the destination. For more information, see Object name mapping. Ignore Errors and Proceed: Skips the check for tables with the same name in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and put your business at risk. For example: If the table schemas are consistent and a record in the destination database has the same primary key or unique key value as a record in the source database: During full data synchronization, DTS retains the destination record and skips the source record. During incremental synchronization, DTS overwrites the destination record with the source record. If the table schemas are inconsistent, data initialization may fail. This can result in only partial data synchronization or a complete synchronization failure. Use with caution. - Precheck and Report Errors: Checks whether a table with the same name exists in the destination database. If a table with the same name does not exist, the precheck passes. If a table with the same name exists, the precheck fails and the data synchronization task does not start. Note: If you cannot delete or rename the table with the same name in the destination database, you can map it to a different table name. For more information, see the topic about mapping table and column names. - Ignore Errors and Proceed: Skips the check for duplicate table names in the destination database. > Warning: Selecting Ignore Errors and Proceed may cause data inconsistency and put your business at risk. For example: if the table schemas are the same and a record in the destination database has the same primary key or unique key value as a record in the source database, DTS retains the record in the destination instance during full synchronization and does not synchronize the corresponding record from the source database, and during incremental synchronization the record from the source database overwrites the record in the destination database. If the table schemas are different, initial data synchronization may fail. This can result in only partial column data being synchronized or a complete synchronization failure. Proceed with caution.
Source Objects	In the Source Objects box, click the objects, and then click to move them to the Selected Objects box. In the Source Objects box, click an object to synchronize, and then click the rightward arrow to move it to the Selected Objects box. Note You can select objects at the database, table, or column level. If you select only tables or columns, DTS does not synchronize other object types (such as views, triggers, and stored procedures). Note: You can select databases, tables, and columns as synchronization objects. If you select tables or columns, other objects such as views, triggers, and stored procedures are not synchronized to the destination database.
Selected Objects	To rename a single object in the destination instance, right-click the object in the Selected Objects box. For more information, see Map a single object name. To rename multiple objects in bulk, click Batch Edit in the upper-right corner of the Selected Objects box. For more information, see Map multiple object names in bulk. - To rename a single synchronization object in the destination instance, right-click the object in the Selected Objects box. For more information, see the topic about mapping a single database, table, or column. - To change the names of multiple synchronization objects in the destination instance in a batch, click Batch Edit in the upper-right corner of the Selected Objects box. For more information, see the topic about mapping databases, tables, and columns in a batch.

Click Next: Advanced Settings and configure the advanced parameters. The following table describes the parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster, so you do not need to select a cluster. For more stable performance, you can purchase a dedicated cluster to run DTS synchronization tasks. For more information, see What is a DTS dedicated cluster?.
Retry Time for Failed Connections	After the synchronization task starts, if DTS fails to connect to the source or destination database, DTS reports an error and immediately starts to retry the connection. The default retry duration is 720 minutes. You can specify a custom retry duration from 10 to 1,440 minutes. We recommend that you set the duration to 30 minutes or more. If DTS reconnects to the source and destination databases within the specified duration, the synchronization task resumes automatically. Otherwise, the task fails. Note: - If you have several DTS instances that share the same source or destination database, for example instance A and instance B, and you set the network retry duration to 30 minutes for instance A and 60 minutes for instance B, the shorter duration of 30 minutes applies to both instances. - DTS charges for the task runtime during the connection retry period. We recommend that you set the retry duration based on your business requirements, or release the DTS instance as soon as the source and destination database instances are released.
Retry Time for Other Issues	After the synchronization task starts, if an issue other than a connection failure occurs on the source or destination database, such as a DDL or DML execution exception, DTS reports an error and immediately starts to retry the operation. The default retry duration is 10 minutes. You can specify a custom retry duration from 1 to 1,440 minutes. We recommend that you set the duration to 10 minutes or more. If the operation succeeds within the specified duration, the synchronization task resumes automatically. Otherwise, the task fails. Important: The value of Retry Time for Other Issues must be smaller than the value of Retry Time for Failed Connections.
Enable Throttling for Full Data Synchronization	During full synchronization, DTS consumes read and write resources of the source and destination databases, which may increase their load. To reduce the load, you can throttle the full synchronization task by configuring Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Note: - This parameter is available only when Synchronization Types is set to Full Data Synchronization. - You can also adjust the full synchronization rate after the synchronization instance starts running.
Enable Throttling for Incremental Data Synchronization	You can also throttle the incremental synchronization task. To reduce the load on the destination database, set RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
Environment Tag	Select an environment tag to identify the instance based on your requirements. In this example, you do not need to select a tag.
Configure ETL	Choose whether to enable the extract, transform, and load (ETL) feature. For more information, see What is ETL? Valid values: Yes: Enables the ETL feature. Enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. No: Disables the ETL feature. Choose whether to enable the extract, transform, and load (ETL) feature. Valid values: - Yes: Enables the ETL feature. Enter data processing statements in the code editor. For more information, see the topic about configuring ETL in a data migration or data synchronization task. - No: Disables the ETL feature.
Monitoring and Alerting	Specifies whether to configure alerts. If the synchronization fails or the latency exceeds the specified threshold, DTS sends a notification to the alert contact. No: does not configure alerts. Yes: configures alerts. Set an alert threshold and specify an alert notification. For more information, see Configure monitoring and alerting during task configuration.

Save the task and perform a precheck.
- To view the parameters for configuring this instance via an API operation, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the tooltip.
- If you have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.
Note
- Before a synchronization task starts, DTS performs a precheck. You can start the task only if the precheck passes.
- If the precheck fails, click View Details next to the failed item, fix the issue as prompted, and then rerun the precheck.
- If the precheck generates warnings:
  For non-ignorable warning, click View Details next to the item, fix the issue as prompted, and run the precheck again.
  For ignorable warnings, you can bypass them by clicking Confirm Alert Details, then Ignore, and then OK. Finally, click Precheck Again to skip the warning and run the precheck again. Ignoring precheck warnings may lead to data inconsistencies and other business risks. Proceed with caution.

Save the task and run a precheck.

To view the API parameters for configuring this instance, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the bubble.
If you have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.

Note

Before the synchronization job starts, DTS runs a precheck. The job can start only after all precheck items pass.
If the precheck fails, click View Details for the failed item. Fix the issue as prompted, and then run the precheck again.
If the precheck returns a warning:
- If a check item fails and cannot be ignored, click View Details next to the item. Follow the instructions to fix the issue, and then run the precheck again.
- For check items that can be ignored, you can click Confirm Alert Details, Ignore, OK, and Precheck Again in sequence to skip the warning and rerun the precheck. If you choose to shield the warning item, it may cause issues such as data inconsistency and pose risks to your business.

Save the task and run a precheck.
a. To view the API parameters for configuring this instance, hover over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the bubble.
b. When you finish viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.
Note
- Before the synchronization task starts, DTS runs a precheck. The task can start only after all precheck items pass.
- If the precheck fails, click View Details for the failed item. Fix the issue as prompted, and then run the precheck again.
- If the precheck returns a warning and the check item cannot be ignored, click View Details next to the item. Follow the instructions to fix the issue, and then run the precheck again.
- For check items that can be ignored, you can click Confirm Alert Details, Ignore, OK, and Precheck Again in sequence to skip the warning and rerun the precheck. If you choose to shield the warning item, it may cause issues such as data inconsistency and pose risks to your business.
Purchase the instance.
a. When Success Rate reaches 100%, click Next: Purchase Instance.
b. On the Purchase page, select the billing method and the link specification of the data synchronization instance. The following table describes the parameters.

Category	Parameter	Description
New Instance Class	Billing Method	Subscription: You pay when you create the instance. This method suits long-term requirements. It costs less than pay-as-you-go, and the longer the subscription duration, the larger the discount. Pay-as-you-go: You are charged by the hour. This method suits short-term requirements. You can release the instance right after you finish using it to save costs.
	Resource Group Configuration	The resource group to which the instance belongs. The default value is default resource group. For more information, see What is Resource Management?.
	Link Specification	DTS provides synchronization specifications with different performance levels. The link specification affects the synchronization rate, so select a specification based on your business scenario. For more information, see Data synchronization link specifications.
	Subscription Duration	For a subscription instance, select the duration and quantity. You can select 1 to 9 months, or 1, 2, 3, or 5 years. Note: This parameter appears only when the billing method is Subscription.

c. After you complete the configuration, read and select Data Transmission Service (Pay-as-you-go) Service Terms.

d. Click Buy and Start. In the OK dialog box, click OK.

You can view the progress of the task on the Data Synchronization page.