A database-level fine-grained isolation recovery method, system and medium

Abstract

The application discloses a database-level fine isolation recovery method and system and a medium, belongs to the technical field of database recovery, and is used for solving the technical problems that the existing PostgreSQL database only supports instance-level coarse-grained recovery, cannot realize single-database independent isolation recovery, is easy to cause non-target database data loss, cross-business interruption, and is difficult to adapt to multi-tenant fine operation and maintenance scenes. The method comprises the following steps: reading a target database OID; acquiring a recovery starting point LSN value when entering a database recovery process, and positioning a corresponding pre-write log file; reading data change records in the pre-write log file in batches, and extracting the ownership database OID corresponding to the data change records; matching the target database OID with the ownership database OID of each data change record, and screening out the data change records of the target database; and recovering the data change records of the target database in batches until the recovery target condition is reached.

Description

Technical Field

[0001] This invention relates to the field of database recovery technology, and in particular to a database-level fine-grained isolation recovery method, system, and medium. Background Technology

[0002] PostgreSQL, as a mainstream relational database, is widely used in multi-tenant, multi-service production environments. It typically employs an architecture where multiple independent databases are deployed on a single instance, each hosting different business modules. Currently, PostgreSQL database recovery methods mainly include point-in-time recovery, recovery to the latest state, and recovery to a specified transaction point. However, existing recovery mechanisms are all instance-level coarse-grained recovery, meaning the recovery operation affects the entire database instance, not just a single database within it.

[0003] In scenarios with multiple databases coexisting, if a database within an instance experiences accidental operation, data loss, or corruption, traditional recovery solutions require rolling back the entire instance to the target time point, starting point, or specified transaction point. This inevitably overwrites or loses data in other healthy databases within the same instance, leading to cross-service interruptions, data security risks, and cascading failures. Existing technologies cannot isolate the recovery scope and lack targeted recovery capabilities for single databases, making it difficult to meet the practical needs of multi-service isolation and refined operation and maintenance. This has become a key technical bottleneck for high availability assurance and data recovery of PostgreSQL databases. Summary of the Invention

[0004] This invention provides a database-level fine-grained isolation recovery method, system, and medium to solve the following technical problems: Existing PostgreSQL databases only support instance-level coarse-grained recovery, which cannot achieve independent isolation recovery of a single database, easily leading to data loss of non-target databases, cross-business interruption, and difficulty in adapting to multi-tenant fine-grained operation and maintenance scenarios.

[0005] The embodiments of the present invention adopt the following technical solutions: On one hand, embodiments of the present invention provide a database-level fine-grained isolation recovery method, the method comprising: reading the target database OID from the GUC parameters included in the database recovery instruction; the target database OID is used to identify a single database to be recovered; Obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file based on the recovery start point LSN value; Read each data change record in the write-ahead log file and extract the corresponding database OID for each data change record; Match the target database OID with the database OID to which each data change record belongs, and filter out the data change records of the target database; The data change records of the target database are restored one by one according to the configured recovery target type until the recovery target conditions are met, thus completing the single database recovery of the target database.

[0006] In one feasible implementation, the target database OID is read from the GUC parameters included in the database recovery command, specifically including: After obtaining the database recovery command, parse and read the GUC parameters from the database recovery command; The GUC parameters read the OID of the target database to be restored and the restoration trigger conditions; wherein, the restoration trigger conditions are configured based on a time point, LSN value or transaction number.

[0007] In one feasible implementation, the recovery start point LSN value at the time of entering the database recovery process is obtained, and the corresponding write-ahead log file is located based on the recovery start point LSN value, specifically including: After entering the database recovery process, obtain the recovery start point corresponding to the recovery trigger condition, and read the LSN value of the recovery start point; Based on the LSN value of the recovery starting point, locate the corresponding write-ahead log file and the log offset position.

[0008] In one feasible implementation, data change records are read one by one from the write-ahead log file, and the corresponding OID of the data change record is extracted, specifically including: The starting change record is located in the write-ahead log file based on the log offset position. Read the data change records in the pre-written log file one by one, starting from the initial change record; The data change records are parsed to extract the database OID to obtain the belonging database OID.

[0009] In one feasible implementation, the target database OID is matched with the OID of the database to which each data change record belongs, and the data change records of the target database are filtered out, specifically including: The database OID of each data change record is compared with the target database OID. If they match, the current data change record belongs to the target database; otherwise, the current data change record does not belong to the target database. The data change records of the target database are summarized and filtered.

[0010] In one feasible implementation, before restoring the data change records of the target database one by one according to the configured recovery target type, the method further includes: Check if the user has configured a recovery target. If not, directly execute the change data replay process. If a recovery target has been configured, the write-ahead log file is verified during the change data replay process according to the recovery target type of the recovery target; wherein, the recovery target type includes: target LSN value, target time point, and target transaction number; When the record position of the write-ahead log file reaches the recovery target, it is determined that the target database has been restored to the expected state, and the replay of the changed data of the target database is stopped.

[0011] In one feasible implementation, data change records of the target database are restored one by one according to the configured recovery target type until the recovery target conditions are met, thus completing the single database recovery of the target database. Specifically, this includes: The changed data in the data change record belonging to the target database is replayed to the corresponding database to complete the recovery of a single record. Return to the record reading step of the write-ahead log file, and repeatedly execute the process of parsing the OID of the host database, comparing and filtering, and replaying data until all write-ahead log files have been processed or the recovery goal is met.

[0012] In one feasible implementation, after matching the target database OID with the belonging database OID of each data change record to filter out the data change records of the target database, the method further includes: Match the target database OID with the database OID to which each data change record belongs, and filter out data change records that are not in the target database; If both the target database and the non-target database require database recovery, once the target database has achieved its recovery goal, the non-target database that has not yet achieved its recovery goal is allowed to continue replaying the changed data normally.

[0013] On the other hand, embodiments of the present invention also provide a database-level fine-grained isolation recovery system, the system comprising: The write-ahead log location module is used to read the target database OID from the GUC parameters included in the database recovery command; the target database OID is used to identify a single database to be recovered; obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file according to the recovery start point LSN value; The change record extraction module is used to read data change records one by one from the pre-written log file and extract the corresponding database OID of the data change record; match the target database OID with the database OID of each data change record to filter out the data change records of the target database; The database isolation and recovery module is used to recover the data change records of the target database one by one according to the configured recovery target type until the recovery target conditions are met, thereby completing the single database recovery of the target database.

[0014] Finally, this embodiment of the invention also provides a storage medium, which is a non-volatile computer-readable storage medium storing at least one program, each program including instructions, which, when executed by a terminal, cause the terminal to execute the database-level fine-grained isolation recovery method.

[0015] Compared with the prior art, the database-level fine-grained isolation recovery method, system, and medium provided in this embodiment of the invention have the following beneficial effects: This invention proposes a single-database recovery mechanism based on GUC parameter configuration of the target database OID. By specifying the database to be recovered through configuration parameters, the recovery granularity decreases from the instance level to the database level, breaking through the limitations of traditional recovery granularity. During the replay process of the Write-Ahead Log (WAL), the database OID carried by each data change record is parsed and compared with the configured target OID. Records that match successfully are replayed. When the target LSN, time point, or transaction point is reached, only the replay of the target database is stopped, while the replay of non-target databases continues normally without being rolled back, overwritten, or interrupted, ensuring that multi-database business isolation is not affected.

[0016] This invention builds upon the existing instance-level recovery framework by adding OID filtering and target determination logic before data replay, without disrupting the original WAL replay mechanism, thus maintaining recovery efficiency and stability. It achieves single-database targeted recovery, avoiding overall instance rollback and eliminating the risk of cross-database data interference and business interruption. Through precise database OID filtering and recovery scope isolation, it achieves accurate database-level recovery. A differentiated termination mechanism ensures that the target database is restored to a specified point while other databases remain unaffected, solving the problem that traditional instance-level recovery requires an overall rollback, easily leading to cross-database data loss and business interruption. This significantly improves the security, accuracy, and operational flexibility of data recovery in PostgreSQL databases in multi-database coexistence scenarios. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart of a database-level fine-grained isolation recovery method provided in an embodiment of the present invention; Figure 2 A flowchart illustrating a specific embodiment of a database-level fine-grained isolation recovery method provided by this invention; Figure 3 This is a schematic diagram of a database-level fine-grained isolation recovery system provided in an embodiment of the present invention. Detailed Implementation

[0018] To enable those skilled in the art to better understand the technical solutions of this invention, the technical solutions of the embodiments of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this invention, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this invention.

[0019] This invention provides a database-level fine-grained isolation recovery method, such as... Figure 1 As shown, the database-level fine-grained isolation recovery method specifically includes steps S101-S105: S101. Read the target database OID from the GUC parameters included in the database recovery command; the target database OID is used to identify a single database to be recovered.

[0020] Specifically, after obtaining the database recovery command, the GUC parameter is parsed and read from the database recovery command. The OID of the target database to be recovered and the recovery trigger condition are read from the GUC parameter. The recovery trigger condition is configured based on a time point, LSN value or transaction number.

[0021] As a feasible implementation method, the database-level data recovery process is initiated first. The system reads the preset target database OID (the other databases in the system are restored to the latest state) from the GUC configuration parameters. This OID is used to uniquely identify the single database that needs to be recovered. The recovery trigger conditions can be configured based on the specified LSN value, time point or transaction number.

[0022] S102. Obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file based on the recovery start point LSN value.

[0023] Specifically, after entering the database recovery process, the recovery starting point corresponding to the recovery trigger condition is obtained, and the LSN value of the recovery starting point is read. Based on the LSN value of the recovery starting point, the corresponding write-ahead log file and log offset position are located.

[0024] As a feasible implementation method, after entering the recovery process, the system obtains the recovery starting LSN value, locates the corresponding WAL log file and log offset position based on the LSN, and reads WAL log records one by one from that position and performs replay judgment.

[0025] S103. Read each data change record in the write-ahead log file and extract the corresponding database OID of the data change record.

[0026] Specifically, the starting change record is located in the write-ahead log file based on the log offset position. Starting from the starting change record, each data change record in the write-ahead log file is read sequentially. The data change records are parsed to extract their respective database OIDs, thus obtaining the originating database OID.

[0027] As a feasible implementation, the system reads each data change record from the WAL log, with each record corresponding to one data change operation. The record content is then parsed to extract the database OID to which the change belongs, and compared with the target database OID configured in the GUC parameters.

[0028] S104. Match the target database OID with the database OID to which each data change record belongs, and filter out the data change records of the target database.

[0029] Specifically, the OID of each data change record's originating database is compared with the OID of the target database. If they match, the current data change record belongs to the target database; otherwise, it does not. The data change records from the target databases are then aggregated to complete the filtering process.

[0030] At the same time, data change records in non-target databases are filtered out; if both the target database and non-target databases need to be restored, after the target database has achieved its restoration goal, the non-target databases that have not yet achieved their restoration goal are allowed to continue to replay the changed data normally.

[0031] As a feasible implementation method, the database OID to which the changed data belongs is extracted and compared with the target database OID configured in the GUC parameters. If they do not match, it indicates that the change belongs to a non-target database. If they match, it indicates that it belongs to the target database. After distinguishing and summarizing the data change records belonging to the target database and non-target databases, the process proceeds to the subsequent recovery target point determination process.

[0032] S105. Restore the data change records of the target database one by one according to the configured recovery target type until the recovery target conditions are met, and complete the single database recovery of the target database.

[0033] Specifically, the system checks whether the user has configured a recovery target. If not, it directly executes the change data replay process.

[0034] If a recovery target has been configured, the write-ahead log file will be validated during the data replay process based on the recovery target type. The recovery target type includes: target LSN value, target time point, and target transaction number.

[0035] The changed data in the data change record belonging to the target database is replayed to the corresponding database, completing the recovery of a single record. Then, returning to the record reading step of the pre-written log file, the process of parsing the OID of the belonging database, comparing and filtering, and replaying the data is executed in a loop.

[0036] When all write-ahead log files have been processed or the record position of the write-ahead log files has reached the recovery target, it is determined that the target database has been restored to the expected state, and the replay of the changed data on the target database is stopped.

[0037] As a feasible implementation method, in the target point determination stage, the system first checks whether a recovery target point is configured. If not, data replay is executed directly; if configured, verification is performed based on three types: LSN, time point, and transaction number. When the current WAL record position meets the target conditions, it is determined that the target database has been restored to the expected state, and subsequent replay of that database is stopped, but non-target databases are allowed to continue normal replay; if the target conditions are not met, data recovery continues. After confirming that the replay conditions are met, the system replays the changed data in this WAL record to the corresponding database, completing the recovery of a single record. Then, it returns to the WAL record reading step, cyclically executing the complete process of parsing OID, comparison and filtering, target determination, and data replay until all WAL logs are processed or the recovery termination conditions are met.

[0038] In one embodiment, Figure 2 A flowchart illustrating a specific embodiment of a database-level fine-grained isolation recovery method provided by this invention is shown below. Figure 2As shown, the specific execution flow of the method is as follows: The process is as follows: 1. Begin database-level data recovery; 2. Read the database OID to be recovered from the GUC parameter of the data recovery command. This parameter is effective based on the specified time, LSN or transaction number. 3. When entering the database recovery process, first obtain the starting point LSN for the recovery process; 4. Locate the corresponding WAL log file based on the LSN, and then start replaying the data line by line; 5. Read a record (corresponding to a data change record) from the WAL log and proceed with the recovery judgment and execution process; 6. Parse the record data and obtain the OID value of the database to which it belongs.

[0039] 7. Compare the OID of the originating database in the record with the OID of the target database configured in the GUC parameters; if they match, proceed to step 8; if they do not match, proceed to step 10. 8. Determine if a recovery target point has been set: If no recovery target has been set, proceed to step 10; if a recovery target has been set, proceed to step 9.

[0040] 9. Check according to the recovery target type: 1) If the target is an LSN value: Determine if the target LSN value has been reached. If yes, the data recovery of the target database is complete, and other data needs to be recovered; otherwise, continue the recovery process. 2) Target time point: Determine if the target time point has been reached. If yes, the data recovery of the target database is complete, and other data needs to be recovered; otherwise, continue the recovery process. 3) Target is a transaction point: Determine if the target transaction point has been reached. If yes, the data recovery of the target database is complete, and other data needs to be recovered; otherwise, continue the recovery process. 10. Replay the recovered data into the database; 11. Return to step 5, read the next WAL record, and repeat the judgment and replay process of steps 5-10 until the recovery goal is achieved or the WAL log ends.

[0041] In addition, embodiments of the present invention also provide a database-level fine-grained isolated recovery system, such as... Figure 3 The database-level fine-grained isolation recovery system 300 shown specifically includes: The write-ahead log location module 310 is used to read the target database OID from the GUC parameters included in the database recovery instruction; the target database OID is used to identify a single database to be recovered; obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file according to the recovery start point LSN value; The change record extraction module 320 is used to read data change records one by one in the pre-written log file and extract the corresponding database OID of the data change record; match the target database OID with the database OID of each data change record to filter out the data change records of the target database; The database isolation and recovery module 330 is used to recover the data change records of the target database one by one according to the configured recovery target type until the recovery target conditions are met, thereby completing the single database recovery of the target database.

[0042] Finally, this embodiment of the invention also provides a storage medium, which is a non-volatile computer-readable storage medium storing at least one program, each program including instructions, which, when executed by a terminal, cause the terminal to perform: The target database OID is read from the GUC parameters included in the database recovery command; the target database OID is used to identify the individual database to be recovered. Obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file based on the recovery start point LSN value; Read each data change record in the write-ahead log file and extract the corresponding database OID for each data change record; Match the target database OID with the database OID to which each data change record belongs, and filter out the data change records of the target database; The data change records of the target database are restored one by one according to the configured recovery target type until the recovery target conditions are met, thus completing the single database recovery of the target database.

[0043] The various embodiments in this invention are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0044] The foregoing has described specific embodiments of the present invention. Furthermore, the processes depicted in the drawings do not necessarily require a specific or sequential order to achieve the desired results. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0045] The above description is merely an embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, various modifications and variations can be made to the embodiments of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the embodiments of the present invention should be included within the protection scope of the present invention.

Claims

1. A database-level fine-grained isolation recovery method, characterized in that, The method includes: The target database OID is read from the GUC parameters included in the database recovery command; the target database OID is used to identify the individual database to be recovered. Obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file based on the recovery start point LSN value; Read each data change record in the write-ahead log file and extract the corresponding database OID for each data change record; Match the target database OID with the database OID to which each data change record belongs, and filter out the data change records of the target database; Check if the user has configured a recovery target. If not, directly execute the change data replay process. If a recovery target has been configured, the write-ahead log file is verified during the change data replay process according to the recovery target type of the recovery target; wherein, the recovery target type includes: target LSN value, target time point, and target transaction number; When the record position of the write-ahead log file reaches the recovery target, it is determined that the target database has been restored to the expected state, and the replay of the changed data of the target database is stopped. Match the target database OID with the database OID to which each data change record belongs, and filter out data change records that are not in the target database; If both the target database and the non-target database need to be restored, after the target database has achieved its recovery goal, the non-target database that has not yet achieved its recovery goal is allowed to continue to replay the changed data normally. According to the configured recovery target type, the data change records of the target database are restored one by one until the recovery target conditions are met, thus completing the single database recovery of the target database. Specifically, this includes: The changed data in the data change record belonging to the target database is replayed to the corresponding database to complete the recovery of a single record. Return to the record reading step of the write-ahead log file, and repeatedly execute the process of parsing the OID of the host database, comparing and filtering, and replaying data until all write-ahead log files have been processed or the recovery goal is met.

2. The database-level fine-grained isolation recovery method according to claim 1, characterized in that, The target database OID is read from the GUC parameters included in the database recovery command, specifically including: After obtaining the database recovery command, parse and read the GUC parameters from the database recovery command; The GUC parameters read the OID of the target database to be restored and the restoration trigger conditions; wherein, the restoration trigger conditions are configured based on a time point, LSN value or transaction number.

3. The database-level fine-grained isolation recovery method according to claim 2, characterized in that, Obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file based on the recovery start point LSN value, specifically including: After entering the database recovery process, obtain the recovery start point corresponding to the recovery trigger condition, and read the LSN value of the recovery start point; Based on the LSN value of the recovery starting point, locate the corresponding write-ahead log file and the log offset position.

4. The database-level fine-grained isolation recovery method according to claim 3, characterized in that, Read each data change record in the write-ahead log file and extract the corresponding database OID for each data change record, specifically including: The starting change record is located in the write-ahead log file based on the log offset position. Read the data change records in the pre-written log file one by one, starting from the initial change record; The data change records are parsed to extract the database OID to obtain the belonging database OID.

5. The database-level fine-grained isolation recovery method according to claim 1, characterized in that, Matching the target database OID with the database OID to which each data change record belongs, and filtering out the data change records of the target database, specifically including: The database OID of each data change record is compared with the target database OID. If they match, the current data change record belongs to the target database; otherwise, the current data change record does not belong to the target database. The data change records of the target database are summarized and filtered.

6. A database-level fine-grained isolation recovery system, employing the database-level fine-grained isolation recovery method as described in any one of claims 1-5, characterized in that, The system includes: The write-ahead log location module is used to read the target database OID from the GUC parameters included in the database recovery command; the target database OID is used to identify a single database to be recovered; obtain the recovery start point LSN value when entering the database recovery process, and locate the corresponding write-ahead log file according to the recovery start point LSN value; The change record extraction module is used to read data change records one by one from the pre-written log file and extract the corresponding database OID of the data change record; match the target database OID with the database OID of each data change record to filter out the data change records of the target database; The database isolation and recovery module is used to recover the data change records of the target database one by one according to the configured recovery target type until the recovery target conditions are met, thereby completing the single database recovery of the target database.

7. A storage medium, characterized in that, The storage medium is a non-volatile computer-readable storage medium that stores at least one program, each program including instructions that, when executed by a terminal, cause the terminal to perform a database-level fine-grained isolation recovery method according to any one of claims 1-5.

Images