Database Point-in-Time Recovery via Transaction Log Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database recovery systems fail to preserve transactions that occur after a full backup, leading to data loss when restoring a database to a previous point in time due to corruption or errors.
Innovation Solution
A point-in-time recovery system that uses transaction logs to continuously backup and log write transactions across multiple database nodes, allowing for the restoration of a database to a specific point in time without losing subsequent transactions by storing snapshots of data pages and their metadata, enabling fine-grained backups and efficient data recovery.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a full database backup is performed periodically, then the database can be restored to a previous state in case of failure, but transactions that occur after the backup time are lost during restoration
Solution Approach 1:
The system performs preliminary actions by continuously logging all write transactions to a transaction log file as they occur, before any potential failure happens. This preliminary logging ensures that when restoration is needed, the transactions that occurred after the last full backup can be recovered by applying the logged transactions to the restored database, preventing data loss while maintaining reliable recovery capability
Solution Approach 2:
The backup system is segmented into two independent components: full database backups and incremental transaction logging. The transaction log captures individual write transactions separately from the main database backup, allowing the system to restore the database to any point in time by combining the last full backup with the relevant transaction log entries, thus preventing transaction loss while maintaining reliable recovery
2Loss of information
If transaction logs are continuously maintained for all database nodes, then point-in-time recovery without data loss is achieved, but the system complexity and storage requirements increase
Solution Approach 1:
A transaction log file serves as an intermediary between the database nodes and the backup system. Instead of directly backing up each individual transaction from multiple database nodes, the system uses a single transaction log file as a mediator to capture and store all write transactions in a centralized, sequential manner. This intermediary approach preserves all transactions for point-in-time recovery while significantly simplifying the backup system architecture compared to maintaining separate logging mechanisms at each node
Solution Approach 2:
The system creates a copy of the database state at full backup intervals and maintains a separate copy of all write transactions in the transaction log. This copying strategy allows point-in-time recovery by combining the appropriate database copy with the relevant transaction copy, achieving complete transaction preservation without the complexity of maintaining multiple redundant systems or distributed logging infrastructure
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for providing point in time recovery on a database. An embodiment operates by determining that one or more values were written to one of a plurality of database nodes of a database as part of a write transaction. The one or more data pages to which the one or more values were written are copied to a storage location of a backup corresponding to the write transaction. The storage location of the one or more data pages in the backup are written to a location in a transaction log corresponding to the write transaction.