1. Precautions
A DTS migration/synchronization process includes the following phases: precheck, structure synchronization, full data migration/synchronization, and incremental data migration/synchronization. To ensure that each phase runs smoothly, read the following usage notes before you create a migration task.
Before You Start
Source Database Requirements
Triggers on partitioned tables in the source database must be enabled.
During full data migration, the standby nodes of the source database are supported. To support standby database nodes, the value of the hot_standby_feedback parameter must be set to on. In incremental data migration, the standby nodes of the source database are not supported.
If you need to perform incremental data migration, the file pg_hba.conf of your source database must meet the following conditions: 1. host replication all 0.0.0.0/0 md5 is specified and the parameter wal_level is set to logical. 2. decoderbufs is installed. 3. The replica identity attribute is set to full for tables without primary keys. 4. The value of max_replication_slots is greater than the number of currently used replication slots. 5. The value of max_wal_senders is equal to or greater than that of max_replication_slots. 6. If the TOAST attribute of a primary key column in a table is MAIN, EXTERNAL, or EXTENDED, the replica identity attribute of the table is full.
The objects that the source objects depend on and are associated with must also be migrated. Otherwise, the task may fail.
Destination Database Requirements
The value of the block_size parameter in the destination database configuration must be greater than or equal to that in the source database configuration.
The value of the lc_monetary parameter in the destination database configuration must be the same as that in the source database configuration.
To perform incremental data migration, if the objects to be migrated include foreign keys, triggers, or event triggers, set the value of the session_replication_role parameter in the destination database configuration to replica. After the data migration task is complete, change the value of the parameter to origin.
The destination database cannot contain objects with the same type and of the same names as the objects to be migrated, such as schemas, tables, and sequences. Otherwise, the migration task may fail. If the name of the system databases, system schemas, and system tables are the same, the migration task is not affected.
If you migrate selected tables, you are not recommended to rename the tables during incremental data migration.
Before starting a full and incremental data migration task or an incremental data migration task, make sure that no long-running transactions are enabled in the source database. Long-running transactions in the source database may block logical replication slot creation. As a result, the task fails.
To migrate DDL operations, make sure that the destination database supports the operations performed on the source database.
During structure and full data migration
Do not change the ports of the source and destination databases, or change or delete the passwords or permissions that are used to connect to the source and destination databases. Otherwise, the task may fail.
Do not run DDL statements on the source database. Otherwise, the data in destination database may be inconsistent with the data in the source database, or the task may fail.
Do not write data to the destination database. Otherwise, the data in destination database may be inconsistent with the data in the source database.
During incremental data migration
Do not change the ports of the source and destination databases, or change or delete the passwords or permissions that are used to connect to the source and destination databases. Otherwise, the task may fail.
Do not change the primary keys or unique keys (if the tables have no primary keys) of tables in the source database. Otherwise, the incremental data migrated to the destination database may be inconsistent with the incremental data added to the source database, or the task may fail.
Do not change the replica identity attribute of tables in the source database. Otherwise, the incremental data migrated to the destination database may be inconsistent with the incremental data added to the source database, or the task may fail.
Do not write data to the destination database. Otherwise, the data in destination database may be inconsistent with the data in the source database.
During database-level migration, if you add a table without a primary key to the source database, you must set the replica identity attribute of the table to full before writing data to the table. Otherwise, the data in the destination database may be inconsistent with the data in the source database, or the task may fail.
During database-level migration, if you add a table with a primary key to the source database and the toast attribute of the primary key column is main, external, or extended, you must set the replica identity attribute of the table to full before writing data to the table. Otherwise, the data in the destination database may be inconsistent with the data in the source database, or the task may fail.
To migrate DDL operations, make sure that the destination database supports the operations performed on the source database.
Data Audit
To avoid negative data inconsistency error reports and reduce impacts on the source database and the DTS task, we recommend that you perform data comparison during off-peak hours.
If new data is written to the source database during incremental data migration, the comparison result may show data inconsistency.
2. Procedure
Purchase a DTS Migration Instance
For information about how to purchase a DTS instance, see Purchasing a Data Migration Instance.
Configure your DTS instance based on the following description:
Select Data migration as the instance type, select Public EIP as the network type and select the purchased public EIP, specify PostgreSQL as the database type, and select an RDS PostgreSQL instance as the destination database instance.
Configure Task Information and Start a Task
After you purchase a DTS instance, go to the Configure page to configure your task.
Configure Source and Destination Databases
Configure the information about the source and destination databases, including the database type, IP addresses, ports, database accounts, and database passwords. Then, perform a connectivity test to check whether the source and destination databases can be connected.
Configure Objects to Be Migrated
After you configure the source and destination databases, click Test Connectivity and Proceed. Then, select the objects that you want to migrate.
Configuration description:
| Configuration | Description |
|---|---|
| Step | To perform only full data migration, select Struct Migration and Full Data Migration. To migrate data without service downtime, select Struct Migration, Full Data Migration, and Incremental Data Migration. Note: If Incremental Data Migration is not selected, to ensure data consistency, do not perform DML and DDL operations on the source database instance during data migration. |
| Migration Objects | You can migrate multiple types of objects from a source PostgreSQL database, including tables, views, functions, stored procedures, materialized views, rules, triggers, domains, and custom types. Select the objects that you want to migrate in the left-side multi-select box and click the > icon to move the objects to the right-side box. You can also click the < icon to remove an object from the right-side box. If you select the database-level object instead of some specific objects in the source database, DTS migrates the entire database. During subsequent incremental data migration, you can add new tables to the source database, and the newly added tables will also be migrated to the destination database. However, other types of newly added objects cannot be migrated during incremental migration. |
| Configuring name mapping | DTS supports database name mapping, table name mapping, and column name mapping. To change the name of an object in the destination instance, such as the destination database name or a table or column name, locate the object and click Edit. To change object names in the destination instance in a batch, click Batch Edit in the upper right corner of the right-side box. Note: If a migration task includes only structure migration and full data migration, you can configure column name mapping. If the task includes incremental data migration, you cannot configure column name mapping. The name of a database or table can be up to 64 characters in length and can only contain letters, digits, and underscores (_). If you migrate the entire database, you are not recommended to map database names or table names. |
| Filtering the data to be migrated | You can set a WHERE condition to filter data. The WHERE clause can contain single quotation marks (') and cannot contain semicolons (;) or two consecutive hyphens (--). DTS migrates only data that meets the WHERE condition to the destination database. |
| DML operations supported during incremental data migration | Configure DML operations for incremental data migration. 1. Select an object that you want to migrate. 2. Click Edit. 3. In the pop-up window, select the DML operations for incremental data migration. If you configure DML operations for both the database and table levels, the table-level settings overwrite the database-level settings. |
| DDL operations supported during incremental data migration | You can select whether to migrate incremental DDL statements during incremental data migration. Note: 1. If the task involves migrating an entire database, you must select Incremental DDL Statements. 2. In partial database migration, if Incremental DDL Statements is selected, DTS only migrates the DDL statements for objects that you want to migrate. 3. In partial database migration, if Incremental DDL Statements is not selected, DTS does not migrate DDL statements in the incremental data migration stage. How to implement incremental DDL migration: The source database captures DDL statements through event triggers and records the DDL statements in specific tables. Therefore, you must create event triggers and functions in the source database before data migration. For more information, see Prerequisites for DDL Operations for Synchronizing Incremental Data from a PostgreSQL Database. |
| Start Task as Scheduled | You can specify the start time of the task. By default, the migration task starts immediately after you click Start Task. |
Precheck and Start the Task
If the precheck is passed, click Start Migration to start the data migration task.
Task Management
For information about how to view migration progress, see Viewing Migration Progress.
For information about how to view a migration log, see Viewing a Migration Log.