Migrate data from MongoDB to AnalyticDB for PostgreSQL-Data Transmission Service(DTS)-阿里云帮助中心

Prerequisites

You have created a target AnalyticDB for PostgreSQL instance. Its storage space must be larger than the storage used by the source ApsaraDB for MongoDB instance. For more information, see Create an instance.

Note
The target instance's storage space should be at least 10% larger than the storage used by the source database.
In the target AnalyticDB for PostgreSQL instance, you have created a database, a schema, and a table with a primary key. For more information, see SQL syntax.
Important
- Ensure that the data types in the target table are compatible with the source MongoDB data. For example, if the _id field in MongoDB is of the ObjectId type, the corresponding data type in the AnalyticDB for PostgreSQL instance must be varchar.
- The target table in the AnalyticDB for PostgreSQL instance cannot have columns named _id or _value.
If the source ApsaraDB for MongoDB instance is a sharded cluster, you must obtain endpoints for all shard nodes. The database account and password for each shard must be the same. For more information, see Apply for a shard endpoint.

Limitations

Type	Description
Source database limitations	Bandwidth requirement: Insufficient egress bandwidth on the source database server will slow down data migration. When mapping collection names or performing other edits, a single migration task supports up to 1,000 collections. If you exceed this limit, the task will fail upon submission. To work around this, split the collections into smaller batches and create a separate migration task for each. If the source database is a MongoDB instance with a single-node architecture, an Azure Cosmos DB for MongoDB instance, or an Amazon DocumentDB elastic cluster, only full migration is supported. To perform incremental data migration: The source database must have the oplog enabled, and the oplog must be retained for at least seven days. Alternatively, change streams must be enabled, and DTS must be able to subscribe to data changes from the source database within the last seven days by using change streams. If these requirements are not met, the migration task may fail because it cannot obtain data changes from the source. In extreme cases, this can lead to data inconsistency or loss. Issues arising from this are not covered by the DTS Service Level Agreement (SLA). Important We recommend using the oplog to obtain data changes from the source database. Only MongoDB 4.0 and later versions support obtaining data changes through change streams. If the source database is an Amazon DocumentDB (non-elastic) cluster, you must manually enable change streams. When you configure the task, set the Migration Method to ChangeStream and the Architecture to Sharded Cluster. If the source is a MongoDB instance with a sharded cluster architecture, the _id field in the collections to be migrated must be unique. Otherwise, data inconsistency may occur. If the source instance is a self-managed MongoDB database with a sharded cluster architecture: The Access Method supports only Public IP Address, Express Connect, VPN Gateway, or Smart Access Gateway, and Cloud Enterprise Network (CEN). If the source database is MongoDB 8.0 or later and the Migration Method is Oplog, you must ensure that the shard account used by the migration task has the `directShardOperations` permission. You can grant this permission by running the following command: `db.adminCommand({ grantRolesToUser: "username", roles: [{ role: "directShardOperations", db: "admin"}]})` Note Replace `username` in the command with the shard account for the migration task. If the Migration Method is Oplog and the task includes a full migration, grant the mongos account of the source MongoDB sharded cluster permission to run the `db.runCommand({"balancerStatus":1})` command. DTS uses this command during the precheck to verify that the source database's balancer is disabled. DTS does not support connecting to a MongoDB database by using an SRV record. If the source is a MongoDB sharded cluster instance, the number of mongos nodes cannot exceed 10. You must also remove any orphaned documents from the cluster. Otherwise, the migration may fail or cause data inconsistency. For more information, see Orphaned Documents and How to Clean Up Orphaned Documents in a MongoDB Sharded Cluster. Operational limitations on the source database: During the full migration phase, do not perform schema changes on databases or collections, including updates to array-type data. Otherwise, the migration task may fail or cause data inconsistency. If you perform only a full migration, do not write new data to the source instance. Otherwise, data inconsistency may occur. If the source is a MongoDB sharded cluster instance, do not run commands that change the data distribution of the objects to be migrated, such as shardCollection, reshardCollection, unshardCollection, moveCollection, and movePrimary, while the migration instance is running. Otherwise, data inconsistency may occur. If the source is a MongoDB instance with a sharded cluster architecture, the balancer's rebalancing activity may increase migration latency.
Other limitations	Only collection-level migration is supported. The destination table in the AnalyticDB for PostgreSQL instance must have a single primary key column. Composite primary keys are not supported. In the Selected Objects step, set the Parameter Value field for the primary key column to `bson_value("_id")`. The destination table in the AnalyticDB for PostgreSQL instance cannot have fields named _id or _value. Otherwise, the migration will fail. If data types in the AnalyticDB for PostgreSQL instance are incompatible with the corresponding MongoDB data types, the task will fail. DTS does not support migrating data from the admin, config, or local databases. Append-Optimized (AO) tables are not supported as destination tables. Transaction information is not preserved. Transactions from the source database are converted into individual records in the destination database. Before you start the data migration, evaluate the performance of both the source and destination databases. Perform the migration during off-peak hours. During a full migration, DTS consumes read and write resources on both the source and destination instances, which can increase the database load. During a full migration, DTS performs concurrent INSERT operations, which can cause fragmentation in the destination collections. As a result, the storage space used by the collections in the destination database may be larger than in the source instance. Verify that the migration precision that DTS uses for FLOAT or DOUBLE data types meets your business requirements. DTS reads values of these types using `ROUND(COLUMN,PRECISION)`. If you do not explicitly define the precision, FLOAT values are migrated with a precision of 38 digits and DOUBLE values with a precision of 308 digits. DTS attempts to resume failed migration tasks for up to seven days. Therefore, before switching your business to the destination instance, you must stop or release the task. Alternatively, run the `revoke` command to revoke the write permissions of the account that DTS uses to access the destination instance. This prevents the task from auto-resuming and overwriting destination data with source data. The incremental migration latency is calculated by comparing the timestamp of the last record migrated to the destination with the current timestamp. If the source database has been idle for a long time, the reported latency may be inaccurate. To get an accurate value, perform a small update on the source database. Time series collections, introduced in MongoDB 5.0, are not supported for migration. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.

Billing

Migration type	Task configuration fee	Data transfer fee
full data migration	Free.	This tutorial is free. However, a data transfer fee applies if the Access Method for the destination database is Public IP Address.
incremental data migration	Fees apply. For details, see billing overview.

Migration types

Type	Description
Full migration	Migrates all existing data from the source ApsaraDB for MongoDB instance to the target AnalyticDB for PostgreSQL instance.
Incremental migration	Performs a full migration and then migrates incremental updates from the source ApsaraDB for MongoDB instance to the target AnalyticDB for PostgreSQL instance. Note Only supports the incremental migration of document inserts, updates, and deletions within a collection. For incremental document updates, only changes made by using the `$set` command are replicated.

Account permissions

Database	Full migration	Incremental migration	Actions
Source ApsaraDB for MongoDB	Read permission on the databases to be migrated.	Read permission on the databases to be migrated, the admin database, and the local database.	Manage permissions for MongoDB database accounts
Destination AnalyticDB for PostgreSQL	Read and write permissions on the database.		Create and manage users and manage user permissions. Note You can use the initial account or an account with the RDS_SUPERUSER permission.

Procedure

Navigate to the migration task list page for the destination region using one of the following methods.
From the DTS console
1. Log on to the Data Transmission Service (DTS) console.
2. In the navigation pane on the left, click Data Migration.
3. In the upper-left corner of the page, select the region where the migration instance is located.
From the DMS console

Note
The actual operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode console and Customize the layout and style of the DMS console.
1. Log on to the Data Management (DMS) console.
2. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.
3. To the right of Data Migration Tasks, select the region where the migration instance is located.
Click Create Task to navigate to the task configuration page.

Configure the source and destination databases.

Category	Parameter	Description
None	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.
Source Database	Select Existing Connection	To use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured. Note In the DMS console, this parameter is named Select a DMS database instance.. If you have not registered the database instance with the system, or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select MongoDB.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region of the source ApsaraDB for MongoDB instance.
	Replicate Data Across Alibaba Cloud Accounts	In this example, a database instance under the current Alibaba Cloud account is used. Select No.
	Architecture	In this example, select Replica Set architecture. Note If your source ApsaraDB for MongoDB instance uses a Sharded Cluster architecture, you must also specify Shard account and Shard password.
	Migration Method	Select a method for incremental data migration based on your requirements. Oplog (Recommended): This option is available if an oplog is enabled for the source database. Note An oplog is enabled by default for both self-managed MongoDB databases and ApsaraDB for MongoDB instances. This method offers lower latency for incremental data migration due to faster log pulling. Therefore, we recommend selecting Oplog. ChangeStream: This option is available if Change Streams are enabled for the source database. Note If the source database is an Amazon DocumentDB instance (non-elastic cluster), you can only select ChangeStream. If you set Architecture to Sharded Cluster for the source database, you do not need to enter a Shard account or Shard password.
	Instance ID	Select the instance ID of the source ApsaraDB for MongoDB instance.
	Authentication Database	Enter the name of the database to which the database account of the source ApsaraDB for MongoDB instance belongs. If you have not changed it, the default value is admin.
	Database Account	Enter the database account for the source ApsaraDB for MongoDB instance. For information about the required permissions, see Database account permissions.
	Database Password	Enter the password for the specified database account.
	Encryption	DTS supports three connection methods: Non-encrypted, SSL-encrypted, and Mongo Atlas SSL. The options for Encryption vary based on the selected Access Method and Architecture. The options displayed in the console prevail. Note A MongoDB database where the Architecture is Sharded Cluster and the Migration Method is Oplog does not support SSL-encrypted. If the source is a self-managed MongoDB database (Access Method is not Alibaba Cloud Instance) with a Replica Set architecture, and you select SSL-encrypted, DTS also allows you to upload a CA certificate to verify the connection.
Destination Database	Select Existing Connection	To use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured. Note In the DMS console, this parameter is named Select a DMS database instance.. If you have not registered the database instance with the system, or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select AnalyticDB for PostgreSQL.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region of the destination AnalyticDB for PostgreSQL instance.
	Instance ID	Select the instance ID of the destination AnalyticDB for PostgreSQL instance.
	Database Name	Enter the name of the database in the destination AnalyticDB for PostgreSQL instance that will receive the migrated data.
	Database Account	Enter the database account for the destination AnalyticDB for PostgreSQL instance. For information about the required permissions, see Database account permissions.
	Database Password	Enter the password for the specified database account.

After you complete the configuration, click Test Connectivity and Proceed at the bottom of the page.
Note
- Ensure that the IP address segment of the DTS service is automatically or manually added to the security settings of the source and destination databases to allow access from DTS servers. For more information, see Add DTS server IP addresses to a whitelist.
- If the source or destination database is a self-managed database (the Access Method is not Alibaba Cloud Instance), you must also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box that appears.

Configure the task objects.

On the Configure Objects page, configure the objects that you want to migrate.

Parameter	Description
Migration Types	If you only need to perform a full migration, select Full Data Migration. To perform a migration with no downtime, select both Full Data Migration and Incremental Data Migration. Note If you do not select Incremental Data Migration, do not write new data to the source instance during data migration to ensure data consistency.
DDL and DML Operations to Be Synchronized	Select the operations for incremental migration at the instance level. Note To select operations for incremental migration at the collection level, right-click a migration object in the Selected Objects list and select the checkboxes in the dialog box.
Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks whether collections with the same names exist in the destination database. If no collections with the same names exist, the precheck is passed. If collections with the same names exist, an error is reported during the precheck, and the data migration task does not start. Note If a collection in the destination database has the same name but cannot be easily deleted or renamed, you can change the name of the collection in the destination database. For more information, see Object name mapping. Ignore Errors and Proceed: Skips the check for collections with the same names. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and business risks. For example: If a record in the destination database has the same primary key value as a record in the source database, the record in the destination database is kept. The record from the source database is not migrated to the destination database. Data initialization may fail, only some data may be migrated, or the migration may fail.
Source Objects	In the Source Objects box, click the objects to migrate, and then click to move them to the Selected Objects box. Note You can select objects to migrate at the collection level.
Selected Objects	Edit the database name mapping. Right-click the database that contains the collection to be migrated in the Selected Objects list. In the Selected Objects panel, find the selected database, such as dtsdb, and click its name to edit the mapping. Change the Database Name to the name of the schema that will receive the data in the destination AnalyticDB for PostgreSQL instance. In the Edit Database Name dialog box, modify the value in the Database Name field. For example, change the original schema name from `dtsdb` to `dtstest`. After editing, the destination database name is updated to the new name. Optional: In the Select DDL and DML Operations to Be Synchronized area, select the operations for incremental migration. In the Select DML Operations to Synchronize area, select insert, update, and delete. You can also select Select All. Click OK. Edit the table name mapping. Right-click the collection to be migrated in the Selected Objects list. In the Selected Objects panel, expand the destination database, such as `dtstest`, and find the table object for which you want to edit the mapping, such as `class`. Change the Table Name to the name of the table that will receive the data in the destination AnalyticDB for PostgreSQL instance. In the Edit Table dialog box, modify the Table Name as needed, for example, to `class`. After editing, the table and column names in the destination database are updated. Optional: Set filter conditions. For more information, see Set filter conditions. In the Filter Condition text box, you can enter a filter statement. The supported syntax differs from standard SQL WHERE clauses. Example format: `{"_id": {$gt:"user100844658590795**",$lte:"user101674868045948"}}`, where `$gt` means greater than, and `$lte` means less than or equal to. Optional: In the Select DDL and DML Operations to Be Synchronized area, select the operations for incremental migration. Configure the MongoDB fields to migrate. By default, DTS maps the data of the collection to be migrated. In the Parameter Value column, configure an expression. Check whether the expression meets your requirements and configure parameters such as Column Name, Type, Length, and Precision. Important The primary key column in the destination table must be set to `bson_value("_id")`. When you configure the `bson_value()` expression, you must follow the hierarchy down to the smallest sub-field. Otherwise, this may result in data loss or task failure. In the Parameter Value column's `bson_value()` expression, view the field name for the row in MongoDB. The value inside `""` is the name of the field in MongoDB. For example, if the expression is `bson_value("age")`, this row of data corresponds to the `age` field in MongoDB. Optional: Delete fields that you do not need to migrate. Note To remove a field that you do not want to migrate, click at the end of its row. Configure the fields to be migrated. Take further action based on whether the `bson_value()` expression meets your requirements. Fields with correct expressions Enter the Column Name. Note This is the name of the column in the destination AnalyticDB for PostgreSQL instance that will receive the data. Select the Type of the column data. Important Ensure that the data types in the destination table are compatible with the source MongoDB data. Optional: Configure the Length and Precision for the column data. Repeat these steps to map all relevant fields. Fields with incorrect expressions Note An example is a field with a hierarchical structure (parent-child). In the Operation column, click for the entry. Click + Add Column. Configure Column Name, Type, Length, and Precision. In the text box under Parameter Value**, enter the `bson_value()` expression. For more information, see Assignment configuration examples. Repeat these steps to map all relevant fields. Click OK.

Click Next: Advanced Settings to configure advanced parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster. You do not need to select one. If you want more stable tasks, you can purchase a dedicated cluster to run DTS migration tasks.
Retry Time for Failed Connections	After the migration task starts, if the connection to the source or destination database fails, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1440 minutes. We recommend that you set the duration to more than 30 minutes. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note For multiple DTS instances that share the same source or destination, the network retry time is determined by the setting of the last created task. Because you are charged for the task during the connection retry period, we recommend that you customize the retry time based on your business needs, or release the DTS instance as soon as possible after the source and destination database instances are released.
Retry Time for Other Issues	After the migration task starts, if a non-connectivity issue, such as a DDL or DML execution exception, occurs in the source or destination database, DTS reports an error and immediately begins to retry the operation. The default retry duration is 10 minutes. You can customize the retry time to a value from 1 to 1440 minutes. We recommend that you set the duration to more than 10 minutes. If the related operations succeed within the specified retry duration, the migration task automatically resumes. Otherwise, the task fails. Important The value of Retry Time for Other Issues must be less than the value of Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	During full migration, DTS consumes read and write resources on the source and destination databases, which may increase the database load. If required, you can enable throttling for the full migration task. You can set Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Full Data Migration for Migration Types. You can also adjust the full migration speed after the migration instance is running.
Only one data type for primary key _id in a table of the data to be synchronized	In the data to be migrated, is the data type of the primary key `_id` uniform within a single collection? Important Select an option based on your requirements. Otherwise, data loss may occur. This parameter is available only if you select Full Data Migration for Migration Types. Yes: The data type is unique. During full data migration, DTS does not scan the data types of primary keys in the source data. For a single collection, DTS migrates only the data corresponding to one primary key data type. No: The data type is not unique. During full data migration, DTS scans the data types of primary keys in the source data and migrates all data.
Enable Throttling for Incremental Data Migration	If required, you can also choose to set speed limits for the incremental migration task. You can set RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Incremental Data Migration for Migration Types. You can also adjust the incremental migration speed after the migration instance is running.
Environment Tag	You can select an environment tag to identify the instance based on your needs. In this example, no selection is required.
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.
Monitoring and Alerting	Select whether to set alerts and receive alert notifications based on your business needs. No: Does not set an alert. Yes: Configure alerts by setting an alert threshold and an alert contact. If a migration fails or the latency exceeds the threshold, the system sends an alert notification.

Save the task and run a precheck.
- To view the parameters for configuring this instance when you call the API operation, move the pointer over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the bubble that appears.
- If you do not need to view or have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.
Note
- Before the migration task starts, DTS performs a precheck. The task starts only after it passes the precheck.
- If the precheck fails, click View Details next to the failed check item, fix the issue based on the prompt, and then run the precheck again.
- If a warning is reported during the precheck:
  
  For check items that cannot be ignored, click View Details next to the failed item, fix the issue based on the prompt, and then run the precheck again.
  
  For check items that can be ignored, you can click Confirm Alert Details, Ignore, OK, and Precheck Again to skip the alert item and run the precheck again. If you choose to ignore a warning, it may cause issues such as data inconsistency and pose risks to your business.

Purchase the instance.

When the Success Rate is 100%, click Next: Purchase Instance.

On the Purchase page, select the link specification for the data migration instance. For more information, see the following table.

Category	Parameter	Description
New Instance Class	Resource Group Settings	Select the resource group to which the instance belongs. The default value is default resource group. For more information, see What is Resource Management?
New Instance Class	Instance Class	DTS provides migration specifications with different performance levels. The link specification affects the migration speed. You can select a specification based on your business scenario. For more information, see Data migration link specifications.

After the configuration is complete, read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start. In the OK dialog box that appears, click OK.

You can view the progress of the migration task on the Data Migration Tasks list page.
Note
- If the migration task does not include incremental migration, it stops automatically after the full migration is complete. After the task stops, its Status changes to Completed.
- If the migration task includes incremental migration, it does not stop automatically. The incremental migration task continues to run. While the incremental migration task is running, the Status of the task is Running.

Assignment configuration

Source MongoDB data structure

{
  "_id":"62cd344c85c1ea6a2a9f****",
  "person":{
    "name":"neo",
    "age":26,
    "sex":"male"
  }
}

Destination AnalyticDB for PostgreSQL table schema

Name	Type
mongo_id	varchar Note Primary key column.
person_name	varchar
person_age	decimal

Column configuration

Important

Configure the bson_value() expression according to the data hierarchy to prevent data loss or task failure. For example, if you configure the expression as bson_value("person"), Data Transmission Service (DTS) cannot write incremental changes from the sub-fields of the source person object, such as name, age, and sex, to the destination.

Name	Type	Value
mongo_id	STRING	bson_value("_id")
person_name	STRING	bson_value("person","name")
person_age	DECIMAL	bson_value("person","age")