Database Consistent Copy via Pause Timestamps
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods face challenges in creating a consistent copy of data from a production database to a target database, especially when using different database architectures or vendors, due to differences in hardware and processing speeds, leading to partial or inconsistent data copies.
Innovation Solution
A system that generates consistent copies by employing a bulk copy phase and a catchup phase, using change data streams and pause timestamps to ensure that target database systems reach a synchronized state, even when copying across multiple databases with varying architectures and vendors.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple processes execute in parallel to copy data from production database to target database, then copying speed is improved, but data consistency deteriorates because one process may fail or execute slower resulting in partial information being copied
Solution Approach 1:
The system performs a bulk copy phase that completes the entire data copy operation before any catchup phase begins. This preliminary bulk copy ensures that the foundation for consistency is established before parallel processing of changes occurs.
Solution Approach 2:
The system implements a catchup phase that continuously monitors and applies changes from the production database to the target database, using feedback loops to ensure that all changes are eventually applied consistently across all parallel processes.
2Adaptability or versatility
If production database is copied to multiple target database systems with different architectures, then versatility is improved, but maintaining consistency across all target systems becomes more difficult
Solution Approach 1:
The system employs a universal change data stream format that can be processed by consumer processes across different database architectures (MYSQL, ORACLE, SQL SERVER, POSTGRES). The change data stream serves multiple functions: it captures all data changes, transmits them reliably, and enables consistent reproduction across heterogeneous database systems.
Solution Approach 2:
The system changes the parameter of data representation by using a standardized change data stream format with timestamps and change identifiers that can be interpreted by different database architectures, allowing consistent copying across diverse systems without requiring architecture-specific protocols.
3Adaptability or versatility
If hardware differences exist between target database systems, then adaptability is improved, but processing speed consistency deteriorates as one process may execute much slower compared to others
Solution Approach 1:
The bulk copy phase completes the entire data copy operation on each target system before the catchup phase begins, ensuring that the foundation for consistency is established independently of hardware-specific processing speeds during the critical consistency window.
Solution Approach 2:
The catchup phase continuously applies changes from the change data stream to each target database system without interruption, ensuring that even if hardware differences cause varying processing speeds, all systems eventually reach the same consistent state by continuously processing the same change stream.
Data Source
AI summary
A system generates consistent copies of data stored in a source database in a plurality of target database systems. The system sends a change data stream from the source database system to the target database systems for performing data updates. The system receives timestamps of a latest transactions processed by consumer processes updating the target database system. The system determines a pause timestamp that occurs after the received transactions. The updates to the target database systems are paused once a transaction corresponding to the pause timestamp is processed. The pause timestamp is used to generate a consistent copy of a source database system in a target database system using multiple partitioned change data streams, each partitioned change data stream processing transactions associated with a partition of the source database system.