Overview
During data consistency check, Data Transfer Service (DTS) compares the table datas in the source and target databases, providing comparison results and inconsistency details to help you quickly verify synchronization results before cutover. A data consistency check task is independent and does not affect the normal business of the source database or other DTS tasks.
Note:
Consistency check only serves as a supplementary data verification method. Before the final cutover, you should perform drills to ensure the results meet the cutover requirements.
Links currently supporting data consistency check are as follows:
PostgreSQL > PostgreSQL
Notes and Restrictions
1. A data consistency check task may increase the load in the source database instance. Therefore, you need to perform such tasks during off-peak hours.
2. Currently, data consistency check is supported only when the migration type is Full + Incremental Migration.
3. For sampling comparison, the table to be checked must have a primary key or unique key; otherwise, it will be skipped. Row count check does not require a primary key or unique key. If a table has no primary key or unique key and the data exceeds 10,000 rows, a full comparison cannot be performed.
4. If you choose to Complete or Stop a DTS task before a data consistency check task is completed, the check task will fail.
Creating a Data Consistency Check Task
Note:
Creating a separate data verification task only supports independent check, not built-in check.
Independent check is separate from a DTS task and compares the same data blocks at the source and target ends. After the DTS task stops running, the check cannot be initiated.
An independent check task can be initiated only when the task is running and is in the incremental synchronization phase. If the task is completed, the check task cannot be initiated. You can initiate check tasks multiple times based on different cutover phases, in batches, and with different check methods.
1. Log in to the DTS console, select Data Migration in the left sidebar, and click View in the Operation column of the task list, or click the task ID directly to view task details. 2. Click Data Consistency Check to enter the task list of Data Consistency Check. Click Create Data Consistency Check Task to create a data verification task.
Configure the details of the verification task as follows:
|
Task Type | Independent Check: The verification service operates independently of a DTS task and can compare other data not involved in the migration. |
Structure Verification | Verify the data object structures at both the source and target ends. If you have confirmed the structures are consistent, disabling structure verification can save time. |
Check Object | All migration objects: Check all the objects selected in a migration task. Custom: Choose the objects to be verified from the selected migration objects. |
Comparison Type | Full comparison: Check all the data for the selected objects. Sampling: Check the data of a certain proportion (10%, 20%, 30%, ..., 90%) for the selected objects. Row count check: Only compare the data row count for the selected objects. In this case, even tables without primary keys can also be checked. |
Thread Count | The value ranges from 1 to 8 and can be set as needed. Adding more threads can speed up the consistency check, but doing so will put more load on the source and target databases. Each added thread will require one extra CPU core. |
3. After configuration, click Create and Start Consistency Check to initiate the verification task.
Viewing Data Consistency Check Results
Note:
When an inconsistency is first detected in a table during the verification task, the system will mark the comparison result as inconsistent and the task status as failed.
1. On the migration task homepage, in the Last Check Result column, you can view whether the verification result is consistent or not. Click View > Data Consistency Check to enter the verification details page.
2. Click View to check the verification result.
Summary of Data Verification Results
The summary information of the data verification results is shown in the following table:
|
Overview | Comparison Type: All are currently Independent Check. Comparison Method: The available options are Full comparison, Sampling, and Row count check. Structure Verification: The available options are Enabled and Disabled. Status: The current status of the verification task, which can be Created, Awaiting Execution, Running, or Completed. Compare Conclusion: The result of the current verification task, which can be Inconsistent or Consistent. Thread Count: The number of threads configured for the current task. |
Estimated Tables | The system's estimated total number of tables that need verification. |
Checked Tables | The number of tables that have currently been successfully checked. |
Inconsistent Tables | The number of tables with inconsistencies between the source and target ends among the tables that have already been checked. You can view specific inconsistent tables in Details. |
Inconsistent Chunks | The number of chunks with inconsistencies between the source and target ends among the tables that have already been checked. During system verification, chunks are used for verification at both ends, with each chunk containing 10,000 rows of data. |
Data Verification Result Details
PostgreSQL data verification tasks will display the following types of inconsistencies during the verification process. Below is an introduction for each type:
Inconsistency Details
This section will display detailed information about the chunks with inconsistencies in the tables that have already been checked. You can locate specific data by clicking View in the Operation column of the details list, as shown in the figure below:
Table Structure Inconsistency Details
Only tasks with structure verification enabled will display details of structure inconsistencies. During structure verification, the system will list all the details of the inconsistent table structures that have been checked, as shown in the figure below:
You can view the detailed SQL statements of structure inconsistencies by clicking Details in the Structure column of the list, as shown in the figure below:
Owner Inconsistency Details
The data verification task will list the details of tables with inconsistent owners among all the tables that have been checked, as shown in the figure below:
Unchecked Table Details
The data verification task will list the details of all the tables that have not been checked.
How It Works
Row count comparison: Check whether the numbers of rows in the source and target tables are consistent.
Sampling comparison:
Tables without a primary key or unique index cannot be sampled and will be skipped directly.
For other tables, the system will first check whether the numbers of rows in the source and target tables are consistent. If not, they will be marked as inconsistent, and the system will skip these tables and proceed with verifying other tables.
When the numbers of rows in the tables are consistent, the system will partition the table into multiple blocks according to the sampling ratio. It will randomly select rows in each block and calculate the MD5 values for both the source and target tables for comparison. Identical MD5 values indicate consistency.
Full comparison:
For tables without a primary key or unique index:
If the table has more than 10,000 rows, the system will not perform a data consistency check.
If it has fewer than 10,000 rows, the system will sort all columns and obtain all the data from the table. It will then calculate the MD5 values for both the source and target tables. Identical MD5 values indicate consistency.
Was this page helpful?