Data recovery method and device for PostgreSQL database

Abstract

The invention relates to the technical field of databases, and discloses a data recovery method and device for a PostgreSQL database, which are executed on the premise of not depending on pre-stored backup of the PostgreSQL database, and comprises the following steps: scanning file system metadata of a storage partition where the database is located, positioning a physical storage area which is occupied by database data files, and storing the physical storage area in the storage partition; the range is used as a target range of subsequent scanning data fragments; within the target range, executing deep scanning for the PostgreSQL storage structure to extract an effective data fragment set; based on the effective data fragment set, reconstructing a logic structure definition of a deleted table; and analyzing and assembling the effective data fragment set according to the logic structure definition, recombining the effective data fragment set into a complete logic data row record, and exporting a data file in a preset format to complete data recovery of the database.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data recovery method and device for a PostgreSQL database. Background Technology

[0002] In enterprise-level PostgreSQL database operations and maintenance, incidents frequently occur where DBAs or developers accidentally delete core business tables (such as order tables and user tables) due to operational errors in executing the DROP TABLE command. Traditional recovery methods heavily rely on pre-configured backup archives.

[0003] After a PostgreSQL database performs a DROP TABLE operation by mistake, how can it accurately identify, structurally parse, and completely reconstruct fragmented data from the underlying storage device and achieve a high success rate and high efficiency in emergency recovery under extreme conditions such as no effective backup, loss of metadata, and physical files being reclaimed by the operating system? Summary of the Invention

[0004] This specification provides one or more embodiments of a data recovery method and device for a PostgreSQL database, which is used to solve the technical problems mentioned in the background.

[0005] One or more embodiments of this specification employ the following technical solutions: This specification provides one or more embodiments of a PostgreSQL database data recovery method, which is executed without relying on a pre-stored backup of the PostgreSQL database. The method includes: Based on scanning the file system metadata of the storage partition where the database is located, the physical storage area that was once occupied by the database data file is located, which can be used as the target range for subsequent scanning of data fragments. Within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments; Based on the set of valid data fragments, the logical structure definition of the deleted table is reconstructed; Based on the defined logical structure, the set of valid data fragments is parsed and assembled, reorganized into complete logical data row records, and exported as a data file in a preset format to complete the data recovery of the database.

[0006] It should be noted that the data recovery method provided by this invention creatively bypasses the dependence on database backup files and complete metadata, directly addressing the issue at the operating system storage level. This solves the industry-wide problem of data unrecoverability in extreme scenarios where there is no backup and metadata has been cleared. The method first scans file system metadata to accurately locate the physical storage area previously occupied by the database, thus defining the potential data residue range within disk fragments and laying the foundation for recovery. Then, within this target range, a deep scan of the PostgreSQL heap file and TOAST (The Oversized-Attribute Storage Technique) storage structure is performed. Through a series of operations, including verifying the magic number of data pages, using MVCC rules for transaction visibility judgment, and parsing and reconstructing large object data associated with TOAST pointers, all valid "data fragment sets" can be accurately filtered and extracted from the underlying storage fragments. Based on this, the method reconstructs the complete logical structure definition of the deleted table by forward querying the residual directory or reverse statistical analysis of data characteristics. Finally, based on the reconstructed table structure, the previously extracted fragmented data is parsed, assembled, and its consistency is verified, reconstructing it into complete logical data rows and exporting it as a standard data file.

[0007] Therefore, this invention provides a direct, effective, and last-resort recovery solution for PostgreSQL databases when they encounter critical situations such as DROP TABLE errors and have no available backups. This solution does not rely on upper-level database metadata and can maximize the recovery and reconstruction of business data from the physical storage level, significantly reducing the risk and loss of permanent data loss due to operational errors.

[0008] Furthermore, the step of scanning the file system metadata of the storage partition where the database resides to locate the physical storage area previously occupied by database data files includes: The file extension mapping interface provided by the operating system kernel is invoked to query the segment mapping table of the storage partition in order to obtain records of all physical block segments that have been occupied by files.

[0009] It's important to note that the biggest efficiency bottleneck in data recovery without backups lies in locating residual data fragments from massive disk spaces. Traditional full-disk scans are extremely time-consuming, making them unsuitable for emergency recovery. This invention addresses this by calling file extension mapping interfaces (such as fiemap) provided by the operating system kernel to directly query the segment mapping table of the storage partition, accurately locating all physical block intervals previously occupied by database files. The core value of this method is that it bypasses the reliance on deleted file records at the file system's logical layer, efficiently tracing and locking down potentially remaining "physical traces" of data directly from the underlying storage allocation metadata. This is equivalent to drawing a precise "treasure map" before starting time-consuming deep data analysis, significantly narrowing the subsequent scan scope from the entire partition to a limited physical area containing only potential data fragments.

[0010] Therefore, this method fundamentally solves the efficiency dilemma of full-disk scanning, enabling subsequent key operations such as data identification, extraction, and reconstruction of fragments to be initiated and completed within an acceptable time, thus laying a crucial efficiency foundation for the feasibility of the entire "backup-free emergency recovery" solution.

[0011] Furthermore, when performing the scan, the disk I / O priority of the scan process is set to the idle level.

[0012] It should be noted that, when performing low-level storage scanning, this invention sets the disk I / O priority of the scanning process to the idle level, automatically putting the background recovery operation at a disadvantage when competing for disk I / O resources with other online business processes within the system. This means that when the database host is still running online business, the operating system kernel's I / O scheduler will prioritize disk requests from production business, and only schedule the scan requests of this recovery process when the I / O channel is idle. This mechanism ensures that the high I / O-intensive operation of deep scanning of storage will not compete for resources with online business, thereby avoiding a sudden drop in disk performance or business response delays caused by the recovery operation. It achieves resource isolation and reconciliation between the "emergency" data recovery process and production business operation, ensuring that while attempting to salvage lost data, it will not cause secondary interference or interruption to critical services that are still running.

[0013] Furthermore, within the target scope, performing a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments includes: The target range is scanned in units of data pages, and the data pages belonging to the heap table or the TOAST table of PostgreSQL are identified by verifying the magic number field of the page header. For the identified heap table data pages, the transaction status information of the internal tuple header is parsed based on the multi-version concurrency control rules, and tuples with valid transaction status are selected as basic data fragments. For the underlying data fragment containing references to large objects, resolve its TOAST pointer; Based on the TOAST pointer, extract and reassemble the associated large object data from the identified TOAST table data pages; The basic data fragments and the recombined large object data together constitute the effective data fragment set.

[0014] It should be noted that this invention performs a deep scan of the underlying storage format of PostgreSQL within the located physical storage area. Through a hierarchical and structured parsing process, it achieves accurate extraction and reconstruction of valid data from raw disk fragments. The method first verifies the key characteristic of the data page magic number to accurately identify PostgreSQL-specific heap pages and TOAST pages from massive disk blocks, ensuring the targeting of the scan and laying the foundation for accurate data recovery. Subsequently, for the identified heap table data pages, the method does not simply extract all data. Instead, it parses the tuple header information and strictly applies PostgreSQL's core multi-version concurrency control rules to determine the transaction status of the tuples, thereby accurately selecting tuples with a "valid transaction status" as the basic data fragments. This step is crucial, ensuring that the recovered data satisfies the database's transaction consistency semantics, rather than containing invalid data that has been logically deleted or uncommitted, guaranteeing the correctness and availability of the recovered data. Furthermore, for valid tuples containing large object references, the method establishes an accurate association between heap table tuples and TOAST table storage fragments by parsing their internal TOAST pointers, and extracts and reassembles complete large object data from the identified TOAST table pages accordingly. Finally, the filtered basic tuple data is integrated with the reassembled large object data to form a complete "set of valid data fragments".

[0015] Therefore, this deep scanning process successfully transforms meaningless physical disk bitmaps into structured and consistent data sets with clear semantics and conforming to database logic by decoding PostgreSQL's physical storage format, transaction semantics, and large object storage mechanism layer by layer. This provides high-quality and reliable raw materials for subsequent table structure reconstruction and data row assembly, which is the core and key to achieving backup-free, physical storage-based data recovery.

[0016] Furthermore, the step of scanning the target range in units of data pages includes: Multiple worker threads are created based on the number of processor cores, the target range is divided into multiple sub-ranges, and each worker thread scans the assigned sub-ranges in parallel.

[0017] It should be noted that when recovering data from massive storage spaces, the main time-consuming step is the sequential traversal of data pages. This invention dynamically creates worker threads based on the number of processor cores and intelligently divides the physical area to be scanned into multiple sub-regions, enabling parallel scanning of each sub-region by multiple worker threads. This design allows the intensive I / O reads and page verification operations, which originally had to be executed serially, to be performed simultaneously on multiple processor cores, thus distributing the scanning time of a single thread across multiple concurrent execution units. This method effectively overcomes the core bottlenecks of traditional sequential scanning—long processing time and slow recovery startup—when dealing with large-scale storage devices. It allows subsequent critical steps such as data identification, extraction, and reconstruction to obtain processing materials earlier, significantly shortening the critical path time from the occurrence of the accident to the start of data reconstruction attempts, providing fundamental efficiency assurance for the timeliness of the entire emergency data recovery process.

[0018] Furthermore, the step of identifying data pages belonging to PostgreSQL heap tables or TOAST tables by verifying the magic number field in the page header includes: If the magic number field at the beginning of the data page is the first preset field, it is identified as a data page of a PostgreSQL heap table; If the magic number field at the beginning of the data page is the second preset field, it is identified as a data page of the TOAST table.

[0019] It should be noted that this invention, when scanning data fragments from the underlying storage, achieves rapid and accurate classification of specific types of PostgreSQL data pages by examining the key identifier—the magic number segment pre-defined at the starting position of the data page. This method distinguishes the originally chaotic and disordered physical disk blocks into heap pages carrying regular data and TOAST pages storing large objects, based on their inherent, immutable format identifiers. This classification is the primary and necessary prerequisite for all subsequent differentiated and refined processing flows. It establishes a clear data processing path for the entire recovery engine: for identified heap pages, tuple parsing and transaction visibility judgment rules can be applied to extract valid row data; for identified TOAST pages, large object fragmentation and reassembly logic can be applied.

[0020] Therefore, this step establishes the correct data context early in the recovery process through a low-overhead identifier comparison operation. This avoids the waste of computing resources and logical errors caused by blindly and uniformly parsing all disk fragments in the future. It ensures that the recovery process can proceed efficiently and accurately along the correct branch that matches the PostgreSQL storage design. It is the technical cornerstone that ensures the entire recovery solution can correctly handle all data types, including regular data and large objects.

[0021] Furthermore, the step of extracting and reorganizing the associated large object data from the identified TOAST table data pages based on the TOAST pointer includes: The associated TOAST table object identifier and fragment sequence number are parsed from the TOAST pointer; Based on the TOAST table object identifier, locate the corresponding data fragment in the identified TOAST table data page; Based on the fragment sequence number, multiple data fragments belonging to the same large object are sorted and concatenated to obtain the large object data.

[0022] It should be noted that when processing valid tuples containing large object references recovered from disk, this invention obtains two key metadata items—the associated TOAST table object identifier and the shard sequence number—by parsing the embedded TOAST pointer. Based on the object identifier, this method can accurately locate and filter all data shards belonging to the same target large object from the identified, discrete TOAST table data pages, solving the problem of "finding the right component" among massive fragments. Furthermore, based on the shard sequence number, this method can sort and concatenate these scattered data shards in the correct original order. This process successfully reverses PostgreSQL's automatic splitting operation of large objects during storage, logically reorganizing physically dispersed data blocks into a complete and continuous large object dataset.

[0023] Therefore, this step ensures that the recovery of large object fields (such as long text, images, etc.) in database tables is no longer an isolated collection of meaningless binary fragments, but rather the reconstruction of their complete original content. This guarantees the business semantic integrity and direct availability of the recovered data, allowing the core business tables containing large fields to be completely salvaged.

[0024] Furthermore, the reconstruction of the logical structure definition of the deleted table based on the set of valid data fragments includes any of the following paths: Connect to the PostgreSQL database and query its system directory table to obtain residual table structure metadata; Alternatively, statistical analysis can be performed on the set of valid data fragments to deduce the number of fields, data types, and constraint relationships based on PostgreSQL data type alignment rules and characteristics.

[0025] It should be noted that after successfully extracting the valid set of data fragments, this invention provides two complementary and adaptive paths for reconstructing the logical structure definition of the deleted table. The first path attempts to connect to the original database and query its system directory tables, aiming to directly obtain any remaining, accurate table structure metadata. This is the most efficient and reliable reconstruction method when the metadata is not completely overwritten. When the database connection is unavailable or the relevant metadata in the system directory has been cleared, this invention initiates the second path: performing in-depth statistical analysis on the extracted set of data fragments themselves. Based on PostgreSQL's internal data type alignment rules and storage characteristics, it reverse-engineers the number of fields, data types, and even constraints of the table. This dual-path mechanism combining "forward retrieval" and "reverse deduction" ensures that regardless of the state of the database system's metadata layer after the erroneous operation (partially remaining or completely lost), the recovery process can find a feasible method to reconstruct the blueprint of the table structure. This fundamentally solves the "unknown structure" obstacle caused by the direct deletion of system directories by the DROP TABLE operation, which is insurmountable by traditional methods. It makes it possible to accurately assemble unstructured raw data fragments into meaningful logical data, thus completing the key leap from "data recovery" to "usable data recovery".

[0026] Furthermore, the export of the data file into a preset format includes any of the following methods: The reorganized and validated logical data row records are used to generate a script file containing standard SQL INSERT statements; Alternatively, the reorganized and verified logical data row records can be generated into a plain text file supported by the PostgreSQL COPY command.

[0027] It should be noted that after completing data reconstruction and verification, this invention provides two preset export methods: generating the recovered data as a standard SQL INSERT statement script or a PostgreSQL native COPY command format file. This ensures the high direct usability and operational flexibility of the recovery results. Generating a standard SQL INSERT statement script allows the recovered data to be imported into the database in the most universal and cross-platform way through any SQL-enabled client or tool, providing the broadest compatibility. Generating a PostgreSQL-specific COPY format file utilizes the database's own efficient batch data loading mechanism, providing the optimal performance path for scenarios requiring the rapid import of massive amounts of recovered data back into the PostgreSQL database. This design ensures that regardless of the subsequent data loading environment and requirements, the final result of this recovery method can be seamlessly integrated, transforming the error-free logical data records obtained from all the aforementioned complex recovery steps into a directly recognizable and usable data package that can be immediately used by the database system. This completes the "last mile" delivery from underlying physical fragments to operational business data, effectively solving the problem of data recovery implementation.

[0028] This specification provides one or more embodiments of a PostgreSQL database data recovery device that performs the following operations without relying on a pre-existing backup of the PostgreSQL database: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Based on scanning the file system metadata of the storage partition where the database is located, the physical storage area that was once occupied by the database data file is located, which can be used as the target range for subsequent scanning of data fragments. Within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments; Based on the set of valid data fragments, the logical structure definition of the deleted table is reconstructed; Based on the defined logical structure, the set of valid data fragments is parsed and assembled, reorganized into complete logical data row records, and exported as a data file in a preset format to complete the data recovery of the database.

[0029] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects: The data recovery method provided by this invention creatively bypasses the dependence on database backup files and complete metadata, directly addressing the issue at the operating system storage level. This solves the industry-wide problem of unrecoverable data in extreme scenarios where there are no backups and metadata has been cleared. The method first scans file system metadata to accurately locate the physical storage areas previously occupied by the database, thus defining the potential data residue range within disk fragments and laying the foundation for recovery. Then, within this target range, a deep scan of the PostgreSQL heap file and TOAST storage structure is performed. Through a series of operations, including verifying the magic number of data pages, applying MVCC rules for transaction visibility judgment, and parsing and reconstructing large object data associated with TOAST pointers, all valid "data fragment sets" can be accurately filtered and extracted from the underlying storage fragments. Based on this, the method reconstructs the complete logical structure definition of the deleted table by forward querying the residual directory or reverse statistical analysis of data characteristics. Finally, based on the reconstructed table structure, the previously extracted fragmented data is parsed, assembled, and its consistency verified, reconstructing it into complete logical data rows and exporting it as a standard data file. Attached Figure Description

[0030] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. 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 illustrating a data recovery method for a PostgreSQL database provided in one or more embodiments of this specification; Figure 2 Internal logic block diagram of the fragment recognition engine provided in one or more embodiments of this specification; Figure 3 A schematic diagram illustrating the bidirectional derivation of table structure reconstruction provided for one or more embodiments of this specification; Figure 4 This is a schematic diagram of the structure of a data recovery device for a PostgreSQL database provided in one or more embodiments of this specification. Detailed Implementation

[0031] This specification provides a data recovery method and device for a PostgreSQL database.

[0032] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, 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 specification.

[0033] Figure 1 This diagram illustrates a data recovery method for a PostgreSQL database provided in one or more embodiments of this specification. The method is executed without relying on pre-stored backups of the PostgreSQL database, and the process can be performed by a data recovery system. Certain input parameters or intermediate results in the process can be manually adjusted to help improve accuracy.

[0034] The method flow steps of the embodiments in this specification are as follows: S101, based on scanning the file system metadata of the storage partition where the database is located, locate the physical storage area that was previously occupied by the database data file, and use it as the target range for subsequent scanning of data fragments.

[0035] In the embodiments described in this specification, this step serves as the starting point of the recovery process, aiming to efficiently define the scan boundaries. The system will call the file extension mapping interface provided by the operating system kernel to query the segment mapping table of the storage partition where the database resides. By analyzing this metadata, the system can obtain records of all physical block ranges that have been occupied by PostgreSQL data files. Even if the user has performed a DROP TABLE operation, these ranges will still have a temporary "trace" in the file system before being completely overwritten by new data. This step automatically excludes blank areas that have never contained database files, focusing the subsequent time-consuming deep scan on the "target range" most likely to contain data fragments.

[0036] S102, within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments.

[0037] In the embodiments described in this specification, within the "target range" locked in S101, the system initiates deep parsing of the PostgreSQL storage engine format. First, it scans data pages of fixed size, and by verifying a specific magic number field at the beginning of each page, it accurately identifies heap table data pages and TOAST table data pages belonging to PostgreSQL. For the identified heap table data pages, the system further parses the header information of each tuple, especially the transaction status flag, and strictly applies PostgreSQL's multi-version concurrency control rules to filter out all tuples with a "valid" transaction status as "basic data fragments." For the portions of these basic data fragments that reference large objects, the system parses their embedded TOAST pointers, then locates, sorts, and splices the scattered data fragments from the identified TOAST table data pages, thereby reconstructing the complete large object data. Finally, the system aggregates all basic data fragments and the reconstructed large object data to form a "set of valid data fragments."

[0038] S103, Based on the set of valid data fragments, reconstruct the logical structure definition of the deleted table.

[0039] In the embodiments described in this specification, this step aims to reconstruct the "blueprint" (i.e., table structure) for fragmented data. The system provides two adaptive paths, which can be used individually or in combination. The first path is the "forward reconstruction path": it attempts to connect to the target PostgreSQL database (if it is still running) and directly queries the system directory table to obtain table structure metadata that may not have been completely cleared. The second path is the "reverse reconstruction path": when metadata is completely lost, the system performs statistical analysis on the "set of valid data fragments" produced by S102, and reverse-engineers the number of fields, possible data types, and constraints of the table based on the memory alignment rules, length characteristics, and encoding patterns of PostgreSQL data types. Under this path, manual intervention is allowed to review, confirm, or correct the derived field names, types, and other results to improve the accuracy of the structure definition.

[0040] S104, based on the logical structure definition, the set of valid data fragments is parsed and assembled, reorganized into a complete logical data row record, and exported as a data file in a preset format to complete the data recovery of the database.

[0041] In this embodiment of the specification, this is the final stage of the recovery process. Based on the "logical structure definition" reconstructed in S103, the system performs final parsing and assembly of the "set of valid data fragments" obtained in S102. Specifically, according to the table structure definition, the system parses the binary data in each tuple fragment by field and combines them into a complete logical data row record. To ensure data quality, the reassembled data rows undergo secondary transaction consistency checks and business logic-level integrity checks. Finally, the system exports the verified data to a standard format file. Users can choose to generate a script file containing standard SQL INSERT statements or a plain text file supported by PostgreSQL's native, efficient COPY command, for quick loading into a new table.

[0042] It should be noted that the data recovery method provided by this invention creatively bypasses the dependence on database backup files and complete metadata, directly addressing the issue at the operating system storage level. This solves the industry-wide problem of data unrecoverability in extreme scenarios where there is no backup and metadata has been cleared. The method first scans file system metadata to accurately locate the physical storage area previously occupied by the database, thus defining the potential data residue range within disk fragments and laying the foundation for recovery. Then, within this target range, a deep scan of the PostgreSQL heap file and TOAST storage structure is performed. Through a series of operations, including verifying the magic number of data pages, applying MVCC rules for transaction visibility judgment, and parsing and reconstructing large object data associated with TOAST pointers, all valid "data fragment sets" can be accurately filtered and extracted from the underlying storage fragments. Based on this, the method reconstructs the complete logical structure definition of the deleted table by forward querying the residual directory or reverse statistical analysis of data characteristics. Finally, based on the reconstructed table structure, the previously extracted fragmented data is parsed, assembled, and its consistency is verified, reconstructing it into complete logical data rows and exporting it as a standard data file.

[0043] Therefore, this invention provides a direct, effective, and last-resort recovery solution for PostgreSQL databases when they encounter critical situations such as DROP TABLE errors and have no available backups. This solution does not rely on upper-level database metadata and can maximize the recovery and reconstruction of business data from the physical storage level, significantly reducing the risk and loss of permanent data loss due to operational errors.

[0044] Furthermore, in the process of scanning the file system metadata of the storage partition where the database is located to locate the physical storage area that was previously occupied by the database data file, the method flow steps of this embodiment are as follows: S201, call the file extension mapping interface provided by the operating system kernel to query the segment mapping table of the storage partition to obtain all physical block range records that have been occupied by files.

[0045] In the embodiments described in this specification, this step begins with a low-level probing of the storage partition where the database resides. The system does not directly scan all data content; instead, it sends a query request to the file system by calling the file extension mapping interface provided by the operating system kernel. This interface can access and return the segment mapping table of the storage partition, which records the physical space allocation metadata of the file system. By parsing this mapping table, the system can obtain a critical list containing all physical block range records that have been occupied by files (including deleted database data files). Even if the DROPTABLE operation has deleted the files and released the space, in modern file systems (such as ext4 and XFS), these allocation records usually remain for a short period. Therefore, this method can bypass the "file deleted" state of the file system's logical layer and directly trace and locate the "trace areas" where data may remain from the physical storage management layer.

[0046] It's important to note that the biggest efficiency bottleneck in data recovery without backups lies in locating residual data fragments from massive disk spaces. Traditional full-disk scans are extremely time-consuming, making them unsuitable for emergency recovery. This invention addresses this by calling file extension mapping interfaces (such as fiemap) provided by the operating system kernel to directly query the segment mapping table of the storage partition, accurately locating all physical block intervals previously occupied by database files. The core value of this method is that it bypasses the reliance on deleted file records at the file system's logical layer, efficiently tracing and locking down potentially remaining "physical traces" of data directly from the underlying storage allocation metadata. This is equivalent to drawing a precise "treasure map" before starting time-consuming deep data analysis, significantly narrowing the subsequent scan scope from the entire partition to a limited physical area containing only potential data fragments.

[0047] Therefore, this method fundamentally solves the efficiency dilemma of full-disk scanning, enabling subsequent key operations such as data identification, extraction, and reconstruction of fragments to be initiated and completed within an acceptable time, thus laying a crucial efficiency foundation for the feasibility of the entire "backup-free emergency recovery" solution.

[0048] Furthermore, when performing the scan, the disk I / O priority of the scan process is set to the idle level.

[0049] It's important to note that when the system initiates the scanning process for the target storage area, it explicitly sets the disk I / O priority of this recovery scan process to "idle level" by calling the operating system's process I / O scheduling interface. In Linux-like systems, this is typically achieved using the `ionice` command with the `-c3` parameter. This setting is an instruction that tells the operating system's I / O scheduler that the disk read / write requests of this process have the lowest priority. This means that when there is I / O request contention on the disk channel, the scheduler will prioritize requests from other high-priority processes such as online database services and the operating system, allocating bandwidth only to this recovery scan process when the disk is idle or lightly loaded.

[0050] It should be noted that, when performing low-level storage scanning, this invention sets the disk I / O priority of the scanning process to the idle level, automatically putting the background recovery operation at a disadvantage when competing for disk I / O resources with other online business processes within the system. This means that when the database host is still running online business, the operating system kernel's I / O scheduler will prioritize disk requests from production business, and only schedule the scan requests of this recovery process when the I / O channel is idle. This mechanism ensures that the high I / O-intensive operation of deep scanning of storage will not compete for resources with online business, thereby avoiding a sudden drop in disk performance or business response delays caused by the recovery operation. It achieves resource isolation and reconciliation between the "emergency" data recovery process and production business operation, ensuring that while attempting to salvage lost data, it will not cause secondary interference or interruption to critical services that are still running.

[0051] Furthermore, in the process of performing a deep scan of the PostgreSQL storage structure within the target range to extract a valid set of data fragments, the method flow steps of this embodiment are as follows: S301, scan the target range in units of data pages, and identify data pages belonging to the PostgreSQL heap table or the TOAST table by verifying the magic number field of the page header.

[0052] In the embodiments described in this specification, within the target range, the system traverses disk blocks in steps of the default PostgreSQL page size (typically 8KB). For each disk block, the system reads a specific byte at the beginning of its page, known as the "magic number field," and compares it with a predefined PostgreSQL page identifier. If the magic number field is 0x5350514C, the page is determined to be a "heap table data page" storing regular row data; if it is 0x544F4153, it is determined to be a "TOAST table data page" storing large objects. This step establishes a precise data context for all subsequent processing.

[0053] S302: For the identified heap table data pages, based on the multi-version concurrency control rules, parse the transaction status information of the header of its internal tuples, and filter out tuples with valid transaction status as basic data fragments.

[0054] In the embodiments described in this specification, for each heap table data page identified by S301, the system further analyzes its internal structure. It reads the header information of each tuple (i.e., row data) and extracts key transaction status fields, including t_xmin (insertion transaction ID), t_xmax (deletion transaction ID), and t_infomask (status flags). Subsequently, the system strictly applies PostgreSQL's multi-version concurrency control rules to determine the transaction visibility of the tuples. Only those tuples that meet the condition of "insertion transaction committed and not effectively deleted" are judged as having a "valid transaction status" and are retained as "basic data fragments." This step ensures that the recovered data meets the database's consistency semantics, filtering out historical data that has been rolled back or deleted.

[0055] S303, for the underlying data fragment containing a large object reference, resolve its TOAST pointer.

[0056] In the embodiments described in this specification, the system traverses the basic data fragments filtered in S302, checking whether they contain references to large objects. For tuples containing references, the system locates and parses their embedded toast_pointer structure. This structure is key metadata, encapsulating information on how to locate the associated large object fragment.

[0057] S304, based on the TOAST pointer, extract and reorganize the associated large object data from the identified TOAST table data pages.

[0058] In the embodiments described in this specification, based on the TOAST pointer parsed in S303, the system first extracts the associated TOAST table object identifier. Using this identifier, the system locates all the scattered data fragments belonging to the large object within all the TOAST table data pages identified in S301. Next, the system sorts and concatenates these data fragments in the correct original order according to the fragment sequence number contained in the pointer or within the fragment, thereby reconstructing a complete and continuous large object data.

[0059] S305, the basic data fragments and the recombined large object data are combined to form the effective data fragment set.

[0060] In the embodiments described in this specification, the system finally summarizes all the "basic data fragments" (i.e., valid regular row data) obtained in S302 and the "reconstructed large object data" in S304 to form the final "set of valid data fragments". This set contains all data units that are valid at the transaction level and structurally related, and have been recovered from the disk.

[0061] It should be noted that this invention performs a deep scan of the underlying storage format of PostgreSQL within the located physical storage area. Through a hierarchical and structured parsing process, it achieves accurate extraction and reconstruction of valid data from raw disk fragments. The method first verifies the key characteristic of the data page magic number to accurately identify PostgreSQL-specific heap pages and TOAST pages from massive disk blocks, ensuring the targeting of the scan and laying the foundation for accurate data recovery. Subsequently, for the identified heap table data pages, the method does not simply extract all data. Instead, it parses the tuple header information and strictly applies PostgreSQL's core multi-version concurrency control rules to determine the transaction status of the tuples, thereby accurately selecting tuples with a "valid transaction status" as the basic data fragments. This step is crucial, ensuring that the recovered data satisfies the database's transaction consistency semantics, rather than containing invalid data that has been logically deleted or uncommitted, guaranteeing the correctness and availability of the recovered data. Furthermore, for valid tuples containing large object references, the method establishes an accurate association between heap table tuples and TOAST table storage fragments by parsing their internal TOAST pointers, and extracts and reassembles complete large object data from the identified TOAST table pages accordingly. Finally, the filtered basic tuple data is integrated with the reassembled large object data to form a complete "set of valid data fragments".

[0062] Therefore, this deep scanning process successfully transforms meaningless physical disk bitmaps into structured and consistent data sets with clear semantics and conforming to database logic by decoding PostgreSQL's physical storage format, transaction semantics, and large object storage mechanism layer by layer. This provides high-quality and reliable raw materials for subsequent table structure reconstruction and data row assembly, which is the core and key to achieving backup-free, physical storage-based data recovery.

[0063] Furthermore, during the process of scanning the target range in units of data pages, the method flow steps of this embodiment are as follows: S401, multiple worker threads are created according to the number of processor cores, the target range is divided into multiple sub-intervals, and each worker thread scans the assigned sub-intervals in parallel.

[0064] In the embodiments described in this specification, this step begins with the intelligent scheduling of computing resources. The system first detects the number of processor cores in the operating environment and, based on this, dynamically creates a corresponding number of worker threads. These worker threads will collaboratively undertake the scanning task of the "target range". To achieve efficient parallel processing, the system logically divides the continuous "target range" (i.e., the physical block interval that may contain data fragments) locked in the previous steps into multiple "sub-intervals" of roughly equal size and without overlap. Subsequently, the system allocates these sub-intervals evenly or as needed to each worker thread. Each worker thread operates independently, performing the same depth scan and parsing logic (such as verifying the page magic number, parsing tuples, etc.) within its allocated sub-interval, using data pages as the step size. In this way, the time-consuming full-range traversal work that originally required a single thread to complete serially is broken down into multiple tasks that can be executed simultaneously and processed in parallel by a multi-core processor.

[0065] It should be noted that when recovering data from massive storage spaces, the main time-consuming step is the sequential traversal of data pages. This invention dynamically creates worker threads based on the number of processor cores and intelligently divides the physical area to be scanned into multiple sub-regions, enabling parallel scanning of each sub-region by multiple worker threads. This design allows the intensive I / O reads and page verification operations, which originally had to be executed serially, to be performed simultaneously on multiple processor cores, thus distributing the scanning time of a single thread across multiple concurrent execution units. This method effectively overcomes the core bottlenecks of traditional sequential scanning—long processing time and slow recovery startup—when dealing with large-scale storage devices. It allows subsequent critical steps such as data identification, extraction, and reconstruction to obtain processing materials earlier, significantly shortening the critical path time from the occurrence of the accident to the start of data reconstruction attempts, providing fundamental efficiency assurance for the timeliness of the entire emergency data recovery process.

[0066] Furthermore, in the process of identifying data pages belonging to PostgreSQL heap tables or TOAST tables by verifying the magic number field of the page header, the method flow steps in this embodiment are as follows: S501, if the magic number field at the beginning of the data page is the first preset field, it is identified as a data page of a PostgreSQL heap table.

[0067] In the embodiments described in this specification, during the traversal scan of the target range in units of data pages, the system reads a specific byte sequence at the beginning of each data page, namely the "magic number field". The system compares the measured value of this field with a predefined fixed value (i.e., the "first preset field") that identifies a PostgreSQL regular heap table. If the two match perfectly, the system determines that the current disk block carries a structurally complete PostgreSQL heap table data page, which stores ordinary row data (tuples).

[0068] S502, if the magic number field at the beginning of the data page is the second preset field, it is identified as a data page of the TOAST table.

[0069] In the embodiments described in this specification, in parallel with S501, the system also makes a judgment based on the "magic number field" at the beginning of the data page. However, the predefined value compared this time is another fixed magic number (i.e., the "second preset field") that specifically identifies the TOAST table in PostgreSQL. If the magic number field matches this value, the system determines that the current disk block is a TOAST table data page in PostgreSQL, which stores the split large object data fragments.

[0070] It should be noted that this invention, when scanning data fragments from the underlying storage, achieves rapid and accurate classification of specific types of PostgreSQL data pages by examining the key identifier—the magic number segment pre-defined at the starting position of the data page. This method distinguishes the originally chaotic and disordered physical disk blocks into heap pages carrying regular data and TOAST pages storing large objects, based on their inherent, immutable format identifiers. This classification is the primary and necessary prerequisite for all subsequent differentiated and refined processing flows. It establishes a clear data processing path for the entire recovery engine: for identified heap pages, tuple parsing and transaction visibility judgment rules can be applied to extract valid row data; for identified TOAST pages, large object fragmentation and reassembly logic can be applied.

[0071] Therefore, this step establishes the correct data context early in the recovery process through a low-overhead identifier comparison operation. This avoids the waste of computing resources and logical errors caused by blindly and uniformly parsing all disk fragments in the future. It ensures that the recovery process can proceed efficiently and accurately along the correct branch that matches the PostgreSQL storage design. It is the technical cornerstone that ensures the entire recovery solution can correctly handle all data types, including regular data and large objects.

[0072] Furthermore, in the process of extracting and reorganizing the associated large object data from the identified TOAST table data pages according to the TOAST pointer, the method flow steps of this embodiment are as follows: S601, the associated TOAST table object identifier and fragment sequence number are parsed from the TOAST pointer.

[0073] In the embodiments described in this specification, this step is the starting point for reorganization. The system locates and parses the embedded toast_pointer structure from the "basic data fragments" containing references to large objects obtained in the previous step. By parsing this structure, the system extracts two key metadata: one is the "associated TOAST table object identifier," which uniquely identifies the specific TOAST table storing this large object fragment; the other is the "fragment sequence number," which indicates the order of each data fragment within the complete large object. This metadata constitutes the "navigation map" for subsequent data location and sorting.

[0074] S602, based on the TOAST table object identifier, locate the corresponding data fragment in the identified TOAST table data page.

[0075] In the embodiments described in this specification, based on the "TOAST table object identifier" parsed in S601, the system searches and filters all "identified TOAST table data pages" that have been indexed in the previous step (page type identification). The system traverses these TOAST table data pages to quickly locate all data fragments whose chunk_id (or similar field) matches the object identifier. This step completes the mapping from logical identifier to physical storage location, collecting all multiple data blocks belonging to the same large object that are physically discrete.

[0076] S603, based on the fragment sequence number, sort and concatenate multiple data fragments belonging to the same large object to obtain the large object data.

[0077] In the embodiments described in this specification, after collecting all associated data fragments, the system sorts all fragments in ascending order according to the "fragment sequence number" parsed by S601, ensuring that they are restored to their original byte order before being split by the PostgreSQL storage engine. Finally, the system concatenates the valid data payloads of each fragment in this correct order, thereby reconstructing a complete and continuous large object data. This data is logically completely consistent with the content of the field before the DROP TABLE operation.

[0078] It should be noted that when processing valid tuples containing large object references recovered from disk, this invention obtains two key metadata items—the associated TOAST table object identifier and the shard sequence number—by parsing the embedded TOAST pointer. Based on the object identifier, this method can accurately locate and filter all data shards belonging to the same target large object from the identified, discrete TOAST table data pages, solving the problem of "finding the right component" among massive fragments. Furthermore, based on the shard sequence number, this method can sort and concatenate these scattered data shards in the correct original order. This process successfully reverses PostgreSQL's automatic splitting operation of large objects during storage, logically reorganizing physically dispersed data blocks into a complete and continuous large object dataset.

[0079] Therefore, this step ensures that the recovery of large object fields (such as long text, images, etc.) in database tables is no longer an isolated collection of meaningless binary fragments, but rather the reconstruction of their complete original content. This guarantees the business semantic integrity and direct availability of the recovered data, allowing the core business tables containing large fields to be completely salvaged.

[0080] Furthermore, the reconstruction of the logical structure definition of the deleted table based on the set of valid data fragments includes any of the following paths: Path 1: Connect to the PostgreSQL database and retrieve residual table structure metadata by querying its system directory table.

[0081] This path is implemented when the database service is still accessible and the system directory has not been completely overwritten. The system will attempt to establish a connection with the target PostgreSQL database. Upon successful connection, the system accesses system directory tables such as `pg_class` and `pg_attribute` by executing specific SQL queries. Within these system tables, the system retrieves and extracts metadata records that may remain from the deleted tables, including but not limited to table names, column names, data types, and constraints (such as primary keys and NOT NULL). By integrating this information, the system can directly reconstruct an accurate and complete "logical structure definition." This path is efficient and reliable, and is the preferred recovery method.

[0082] Path 2: Perform statistical analysis on the set of valid data fragments, and deduce the number of fields, data types, and constraint relationships based on PostgreSQL data type alignment rules and characteristics.

[0083] This path is enabled when the database connection fails or related metadata in the system directory has been completely cleared. The system will perform in-depth statistical analysis on the "effective data fragment set" itself. Specifically, the system will scan and statistically analyze the length distribution and number of fields of all data fragments, and analyze the binary pattern of each field. Based on PostgreSQL's internal data type alignment rules (such as fixed length for integer types and specific encoded headers for strings) and known type storage characteristics, the system intelligently derives the most likely data type for each field (such as INT4, TEXT, TIMESTAMP). Furthermore, by analyzing the repetition patterns and value ranges between data, certain constraints (such as uniqueness and non-nullability) may be derived. During this process, the system can provide an interactive interface to present the derivation results (such as suggested field names and types) to users (such as DBAs), allowing them to confirm, name, or correct them based on business knowledge, thereby improving the accuracy of the structure definition.

[0084] It should be noted that after successfully extracting the valid set of data fragments, this invention provides two complementary and adaptive paths for reconstructing the logical structure definition of the deleted table. The first path attempts to connect to the original database and query its system directory tables, aiming to directly obtain any remaining, accurate table structure metadata. This is the most efficient and reliable reconstruction method when the metadata is not completely overwritten. When the database connection is unavailable or the relevant metadata in the system directory has been cleared, this invention initiates the second path: performing in-depth statistical analysis on the extracted set of data fragments themselves. Based on PostgreSQL's internal data type alignment rules and storage characteristics, it reverse-engineers the number of fields, data types, and even constraints of the table. This dual-path mechanism combining "forward retrieval" and "reverse deduction" ensures that regardless of the state of the database system's metadata layer after the erroneous operation (partially remaining or completely lost), the recovery process can find a feasible method to reconstruct the blueprint of the table structure. This fundamentally solves the "unknown structure" obstacle caused by the direct deletion of system directories by the DROP TABLE operation, which is insurmountable by traditional methods. It makes it possible to accurately assemble unstructured raw data fragments into meaningful logical data, thus completing the key leap from "data recovery" to "usable data recovery".

[0085] Furthermore, the export of the data file into a preset format includes any of the following methods: Method 1: Generate a script file containing standard SQL INSERT statements from the reorganized and verified logical data row records.

[0086] It's important to note that this method transforms (or "renders") each "logical data row record" into an INSERT INTO statement that conforms to SQL syntax. Each statement explicitly specifies the target table name (based on the reconstructed table structure definition), the list of fields, and the corresponding data values. The system writes the INSERT statements for all data rows sequentially into a text file, typically with a .sql extension. This script file is generic and declarative; users can execute it directly through any PostgreSQL client (such as psql) or graphical management tools to recreate the tables containing all the recovered data in the target database.

[0087] Method 2: Generate the reorganized and verified logical data row records into a plain text file supported by the PostgreSQL COPY command.

[0088] It's important to note that this method leverages PostgreSQL's native, efficient data import / export mechanism. The system converts "logical data row records" into a specific plain text format that strictly adheres to the syntax required by the PostgreSQL COPY ... FROM command. This typically uses tabs (or other specified delimiters) to separate fields, newlines to separate lines, and escapes special characters. The generated file (e.g., .csv or .txt) itself does not contain SQL commands, only the organized data. Users can then create an empty table in the database and use the COPY table_name FROM 'file_path' WITH (FORMAT text); command for extremely high-speed batch loading.

[0089] It should be noted that after completing data reconstruction and verification, this invention provides two preset export methods: generating the recovered data as a standard SQL INSERT statement script or a PostgreSQL native COPY command format file. This ensures the high direct usability and operational flexibility of the recovery results. Generating a standard SQL INSERT statement script allows the recovered data to be imported into the database in the most universal and cross-platform way through any SQL-enabled client or tool, providing the broadest compatibility. Generating a PostgreSQL-specific COPY format file utilizes the database's own efficient batch data loading mechanism, providing the optimal performance path for scenarios requiring the rapid import of massive amounts of recovered data back into the PostgreSQL database. This design ensures that regardless of the subsequent data loading environment and requirements, the final result of this recovery method can be seamlessly integrated, transforming the error-free logical data records obtained from all the aforementioned complex recovery steps into a directly recognizable and usable data package that can be immediately used by the database system. This completes the "last mile" delivery from underlying physical fragments to operational business data, effectively solving the problem of data recovery implementation.

[0090] In enterprise-level PostgreSQL database operations and maintenance, incidents of DBAs or developers accidentally deleting core business tables (such as order tables and user tables) due to operational errors in executing the DROPTABLE command are frequent. Traditional recovery methods heavily rely on pre-configured backups or WAL log archiving. However, in the following typical backup-less scenarios, existing solutions become completely ineffective: - Scenario 1: WAL archiving is not enabled in the test / pre-production environment, and there are no regular backups; - Scenario 2: Scheduled backup tasks in the production environment fail silently due to disk fullness, network interruption, or other reasons, and are not detected in time; - Scenario 3: After accidental deletion, the system continues to run for several hours, and new data is written to overwrite part of the old file space.

[0091] At this point, although a large number of un-overwritten table data fragments remain on the physical disk, these fragments become "ownerless islands" and cannot be recognized by the database because the system directory metadata has been cleared and the physical file handles have been released.

[0092] The core technical problem that this invention aims to solve is: After a PostgreSQL database performs a DROP TABLE or TRUNCATE TABLE operation by mistake, how can it accurately identify, structurally parse, and completely reconstruct fragmented data from the underlying storage device under extreme conditions such as no effective backup, loss of metadata, and physical files being reclaimed by the operating system, to achieve a high success rate and high efficiency in emergency recovery?

[0093] This solution consists of four stages: intelligent scanning → accurate identification → structural reconstruction → complete export.

[0094] Technical solution: Step 1: Intelligent Scan This step is the entry point for the recovery process. For terabyte-scale disks, a blind full scan is too time-consuming (>48 hours), failing to meet the RTO (Recovery Time Objective) requirements for emergency recovery. This step aims to define the "minimum search range" for the subsequent "precise identification" step. Targeted scanning based on file system metadata eliminates irrelevant data, improving efficiency. (Solving the "full scan is too slow" problem) Specific scenario: A financial client has a 10TB data disk. A full scan would take more than 48 hours, exceeding the RTO requirement.

[0095] The specific implementation scheme of this invention is as follows: 1. Trace Tracking: Call the Linux kernel's fiemap interface to query the Extent mapping table of the partition where the PostgreSQL data directory is located. Even if the data files have been deleted, modern file systems such as ext4 / xfs will still retain the allocation records of these physical blocks for a short period of time (i.e., "traces of when the files once existed").

[0096] 2. Region Locking: Parses Extent information to extract all physical block ranges previously occupied by database files. Simultaneously, it automatically excludes non-data system directories such as / sys and / proc, narrowing the search scope to regions containing only potential data fragments.

[0097] 3. Low-interference operation: When starting the scanning process, force setting ionice -c3 (idle I / O priority) to ensure that the scanning operation is only performed when the disk is idle, without interfering with normal online business.

[0098] Technical benefits: Compared to a full scan, this step reduces the search area by more than 90%, improves overall scanning efficiency by 5–10 times, and saves valuable time for subsequent steps.

[0099] Step Two: Accurate Identification Following the "physical block range" identified in Step One (Intelligent Scan), this step enters the core processing stage. These ranges contain valid data pages, deleted junk pages, and file system metadata fragments. This step uses multi-threaded parallelism and filtering to accurately select valid data units belonging to PostgreSQL.

[0100] Detailed operating procedures ( Figure 2 (Internal logic diagram of the fragment recognition engine) 1. Start multi-threaded scanning Fragment identification first improves efficiency through a multi-threaded parallel scanning mechanism. Specifically, the system creates twice the number of worker threads as the number of CPU cores, and evenly divides the physical block range obtained in step one into independent scanning ranges and assigns them to each thread, thereby avoiding contention. Next, each thread uses mmap memory mapping technology to map the file system region to memory space, and then traverses its assigned disk blocks with a step size of PostgreSQL's default page size (8KB), achieving efficient parallel scanning of the target region.

[0101] 2. Page Feature Validation (Magic Number and Header) After the initial scan, the system performs page feature verification on each disk block to identify PostgreSQL database pages. First, a magic number verification is performed, checking if the disk block offset 0-3 bytes matches the PostgreSQL page magic number. If it's 0x5350514C ("SPQL"), it's marked as a Heap table page (normal data table); if it's 0x544F4153 ("TOAS"), it's marked as a TOAST table page (large object data table); other values ​​are considered useless disk blocks and are discarded. Pages that pass the magic number verification proceed to the page header integrity verification stage. The system sequentially checks the validity of the pd_pagesize_version field, verifies the pd_checksum checksum (if database checks are enabled), and verifies that the pd_lower and pd_upper pointer ranges are within valid ranges. Disk blocks that fail either the magic number verification or the page header integrity verification are filtered out, retaining only valid PostgreSQL pages for subsequent processing.

[0102] 3. Tuple validity screening (MVCC visibility assessment) After page verification, the system further performs deep validity filtering on the tuples within the page. First, in the incomplete page detection stage, the system checks whether the page header structure is partially covered or damaged, verifies the constraint pd_lower ≤ pd_upper, and checks the integrity of the ItemId array to ensure the page structure is complete. Then, in the tuple traversal stage, the system extracts the offset and length of each tuple from the ItemId array, then parses the tuple header information to extract key fields such as t_xmin (insertion transaction ID), t_xmax (deletion transaction ID), t_infomask (tuple status flag), and t_infomask2 (expansion flag).

[0103] Finally, based on the MVCC rules, the visibility of tuples is determined: tuples that satisfy the condition t_infomask&HEAP_XMIN_COMMITTED (insertion transaction has been committed) and (t_xmax==0 or t_infomask&HEAP_XMAX_INVALID) (not deleted or invalid deletion) are considered valid tuples and need to be retained; tuples with t_infomask&HEAP_XMIN_INVALID (insertion transaction has been rolled back) are directly filtered, i.e., dirty data is discarded directly; and tuples with t_infomask&HEAP_XMAX_COMMITTED (deletion transaction has been committed) are marked as deleted tuples, i.e., deleted data is discarded directly.

[0104] This step ensures that only "live" data is restored, completely filtering out historical remnants from transaction rollbacks or deletions.

[0105] 4. TOAST Fragment Association For valid tuples containing large objects, the system performs TOAST fragment association processing to recover the complete large object data. First, TOAST pointer extraction is performed: the system traverses valid tuples to check if the TOAST attribute exists. For tuples containing large objects, the toast_pointer structure is further extracted, including key information such as va_valueid (unique identifier of the large object), va_toastrelid (associated TOAST table OID), va_extsize (size of each TOAST chunk), and va_rawsize (total size of the original data).

[0106] Then, the system enters the TOAST fragmentation scanning phase. Based on the TOAST table magic number (0x544F4153), the system rescans the disk area, matches the TOAST table page corresponding to va_toastrelid, and extracts the chunk_id and chunk_seq fields from the TOAST tuple.

[0107] Finally, in the large object data reconstruction stage, the system groups fragments of the same large object by chunk_id, arranges all chunks (data fragments) of the same large object in ascending order by chunk_seq, and finally splices all the chunk data in sequence to restore the complete content of the large object.

[0108] Output: After this step, the system outputs a "list of valid tuples", which includes all the recovered original binary data and their transaction states, as well as the reconstructed large object data.

[0109] Step 3: Table Structure Reconstruction (Two-way Derivation Mechanism) Contextual Relationship: Although step two has obtained the "data block," it lacks the "skeleton" (table structure definition). Since DROP TABLE typically clears metadata from system tables such as pg_class, this step provides two paths: "forward lookup" and "reverse deduction," providing the necessary structured definition for data reorganization in step four.

[0110] Objective: To reconstruct the complete logical structure of a deleted table in the event that metadata may be missing.

[0111] Detailed operating procedures (refer to) Figure 3 (Schematic diagram of bidirectional derivation of table structure reconstruction) 1. Forward Reconstruction (Metadata Residual Scenarios) When metadata of a deleted table still remains in the system, the system uses a forward reconstruction approach to restore the table structure. Specifically, the system first connects to the target database, and then executes SQL queries on system tables such as pg_class and pg_attribute to retrieve and extract residual table structure information, including key metadata such as table name, field definitions, data types, and constraints, thereby quickly reconstructing the complete logical structure of the deleted table.

[0112] 2. Reverse reconstruction (scenario where metadata is completely missing) When metadata is completely lost and cannot be retrieved from system tables, the system resorts to reverse engineering, deriving the table structure by analyzing actual data. Reverse engineering comprises three core steps: First, statistical analysis is performed, where the system counts the number and length distribution of fields in all valid tuples to obtain the data's structural characteristics. Next, a type matching phase is initiated, where the system generates candidate data types and matches them to each field based on PostgreSQL data type alignment rules (e.g., int4 occupies 4 bytes) and the type characteristics of the pg_type system table (e.g., numeric types have fixed lengths, strings contain UTF-8 identifiers). Finally, user or DBA interaction is performed for confirmation. The system provides an interactive interface to display the derivation results, inviting users or DBAs to confirm and correct field names, data types, and constraints, thereby optimizing and ultimately determining the table structure.

[0113] Output: A complete, executable CREATE TABLE DDL statement as a blueprint for data reorganization.

[0114] Step 4: Data Reorganization and Export (Solving the problem of "how to turn fragmented data into usable data") Contextualization: This is the final stage of the recovery process. Using the "Blueprint (DDL)" generated in step three, the "raw materials (valid tuples)" selected in step two are finally assembled, inspected, and packaged before being delivered to the user.

[0115] Objective: To reassemble fragmented tuples into complete logical data rows and export them in a standard format.

[0116] Detailed operating procedures: 1. Data Restructuring Based on the table structure reconstructed in the previous step, the system first performs data reorganization. Specifically, the system assembles the scattered valid tuple fragments into complete logical data rows in an orderly manner according to field definitions and constraints. At the same time, it handles field alignment and padding operations to ensure that the data length and byte alignment of each field conform to the PostgreSQL storage specification, thereby restoring the fragmented data into a structurally complete row record.

[0117] 2. Transaction verification After data reorganization, the system performs a second transaction check to ensure data validity. By reapplying MVCC rules, the system rigorously checks the transaction status of each row of data, filtering out invalid data from uncommitted insertion transactions or committed deletion transactions. This ensures that the final retained data rows are all validly visible at the transaction level, providing a reliable foundation for subsequent data quality verification.

[0118] 3. Integrity Verification Data that passes transaction validation then enters the integrity validation phase. The system performs corresponding validity checks on different types of fields: for numeric fields, it checks the value range and type compatibility; for string fields, it checks the encoding standards and character integrity; and for date fields, it checks the format validity and date rationality. Through multi-dimensional validation, the system ensures the accuracy and reliability of the exported data.

[0119] 4. Format Export Finally, the system exports the verified complete data into a standard format according to the user's requirements. Users can choose to generate a standard INSERT statement script (.sql file) for direct execution of recovery within the database; or generate a high-speed COPY format file (.csv file) to utilize PostgreSQL's batch import mechanism for rapid data recovery, providing users with a flexible and efficient data export solution.

[0120] Figure 4 This is a schematic diagram of the structure of a data recovery device for a PostgreSQL database provided in one or more embodiments of this specification, which performs the following operations without relying on a pre-stored backup of the PostgreSQL database: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Based on scanning the file system metadata of the storage partition where the database is located, the physical storage area that was once occupied by the database data file is located, which can be used as the target range for subsequent scanning of data fragments. Within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments; Based on the set of valid data fragments, the logical structure definition of the deleted table is reconstructed; Based on the defined logical structure, the set of valid data fragments is parsed and assembled, reorganized into complete logical data row records, and exported as a data file in a preset format to complete the data recovery of the database.

[0121] The various embodiments in this specification 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.

[0122] The various embodiments in this specification 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 apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0123] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0124] In the embodiments provided in this application, it should be understood that the disclosed apparatus / network devices and methods can be implemented in other ways. For example, the apparatus / network device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0125] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0126] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The aforementioned units can be implemented in hardware or software.

[0127] If the integrated module / unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electrical carrier signals and telecommunication signals.

[0128] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A data recovery method for a PostgreSQL database, characterized in that, Execution without relying on pre-stored backups of the PostgreSQL database, including: Based on scanning the file system metadata of the storage partition where the database is located, the physical storage area that was once occupied by the database data file is located, which can be used as the target range for subsequent scanning of data fragments. Within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments; Based on the set of valid data fragments, the logical structure definition of the deleted table is reconstructed; Based on the defined logical structure, the set of valid data fragments is parsed and assembled, reorganized into complete logical data row records, and exported as a data file in a preset format to complete the data recovery of the database.

2. The method according to claim 1, characterized in that, The step of scanning the file system metadata of the storage partition where the database is located to locate the physical storage area previously occupied by database data files includes: The file extension mapping interface provided by the operating system kernel is invoked to query the segment mapping table of the storage partition in order to obtain records of all physical block segments that have been occupied by files.

3. The method according to claim 1 or 2, characterized in that, When performing the scan, the disk I / O priority of the scan process is set to the idle level.

4. The method according to claim 1, characterized in that, Within the target scope, a deep scan of the PostgreSQL storage structure is performed to extract a set of valid data fragments, including: The target range is scanned in units of data pages, and the data pages belonging to the heap table or the TOAST table of PostgreSQL are identified by verifying the magic number field of the page header. For the identified heap table data pages, the transaction status information of the internal tuple header is parsed based on the multi-version concurrency control rules, and tuples with valid transaction status are selected as basic data fragments. For the underlying data fragment containing references to large objects, resolve its TOAST pointer; Based on the TOAST pointer, extract and reassemble the associated large object data from the identified TOAST table data pages; The basic data fragments and the recombined large object data together constitute the effective data fragment set.

5. The method according to claim 4, characterized in that, The step of scanning the target range in units of data pages includes: Multiple worker threads are created based on the number of processor cores, the target range is divided into multiple sub-ranges, and each worker thread scans the assigned sub-ranges in parallel.

6. The method according to claim 5, characterized in that, The process of identifying data pages belonging to PostgreSQL heap tables or TOAST tables by verifying the magic number field in the page header includes: If the magic number field at the beginning of the data page is the first preset field, it is identified as a data page of a PostgreSQL heap table; If the magic number field at the beginning of the data page is the second preset field, it is identified as a data page of the TOAST table.

7. The method according to claim 4, characterized in that, The step of extracting and reorganizing associated large object data from the identified TOAST table data pages based on the TOAST pointer includes: The associated TOAST table object identifier and fragment sequence number are parsed from the TOAST pointer; Based on the TOAST table object identifier, locate the corresponding data fragment in the identified TOAST table data page; Based on the fragment sequence number, multiple data fragments belonging to the same large object are sorted and concatenated to obtain the large object data.

8. The method according to claim 1, characterized in that, The process of reconstructing the logical structure definition of the deleted table based on the set of valid data fragments includes any of the following paths: Connect to the PostgreSQL database and query its system directory table to obtain residual table structure metadata; Alternatively, statistical analysis can be performed on the set of valid data fragments to deduce the number of fields, data types, and constraint relationships based on PostgreSQL data type alignment rules and characteristics.

9. The method according to claim 1, characterized in that, The data file exported as a preset format includes any of the following methods: The reorganized and validated logical data row records are used to generate a script file containing standard SQL INSERT statements; Alternatively, the reorganized and verified logical data row records can be generated into a plain text file supported by the PostgreSQL COPY command.

10. A data recovery device for a PostgreSQL database, characterized in that, Execute the following without relying on a pre-existing backup of the PostgreSQL database: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Based on scanning the file system metadata of the storage partition where the database is located, the physical storage area that was once occupied by the database data file is located, which can be used as the target range for subsequent scanning of data fragments. Within the target range, perform a deep scan of the PostgreSQL storage structure to extract a set of valid data fragments; Based on the set of valid data fragments, the logical structure definition of the deleted table is reconstructed; Based on the defined logical structure, the set of valid data fragments is parsed and assembled, reorganized into complete logical data row records, and exported as a data file in a preset format to complete the data recovery of the database.

Images