Customized large object data backup and recovery methods for PostgreSQL

Abstract

The present application discloses a custom large object data backup method and recovery method for PostgreSQL, which generates a table of contents (TOC) for the data object being backed up to record each data object in the TOC, and the storage structure of any data object is an extended tocEntry structure; when the data object being backed up contains large object data, the attribution table of each large object data is queried, and the oid of the large object data contained in each attribution table is recorded in the blog field of the TOC; and the large object system table pg_largeobject is divided into multiple sub-data tables according to the attribution table of the large object data therein, wherein the number of sub-data tables corresponds to the number of attribution tables containing large object data, and a sub-data table only contains the large object data of one attribution table. The embodiment of the present application achieves efficient time and space and flexible data customization by extending the tocEntry structure to selectively recover large objects in PostgreSQL data.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a custom large object data backup method and recovery method for PostgreSQL. Background Art

[0002] In a PostgreSQL database, data can be backed up and restored. However, when restoring data, large objects in the data must be either restored entirely or not at all, rather than selectively restored.

[0003] Large objects in a database refer to extremely large data objects, such as audio, video, and large files, which can exceed 1GB or even reach terabytes in size. In PostgreSQL, large objects are stored separately from regular data. During data backups, large objects are backed up along with regular data, and during data restores, large objects are restored along with the restoring data (though you can choose not to restore large objects).

[0004] Relational databases efficiently process common small data types, such as numbers, characters, and IP addresses. These fields are stored row by row in database tables. Large objects, however, are large data files. In relational databases, large objects are also stored and managed like small data, broken down into rows (each row is up to 2KB) and stored in database tables. Therefore, if a 1GB large object file is stored in a table, it will be divided into 524,288 records. Reading this large object requires reading each of these 524,288 records and concatenating them into a single 1GB large object file. In the real world, large object files often reach tens of GB or more, meaning that a single large object must be broken down into tens of millions of records. Consequently, large objects cannot be efficiently read or written in relational databases.

[0005] Therefore, in the prior art, when restoring data, the recovery of large objects is slow and time-consuming. Summary of the Invention

[0006] The embodiments of the present application provide a customized large object data backup method and recovery method for PostgreSQL, which implements the selective recovery of large objects in the PostgreSQL database, thereby achieving efficient time and space as well as flexible data customization.

[0007] The present application embodiment provides a custom large object data backup method for PostgreSQL. When PostgreSQL performs data backup, the following backup steps are performed:

[0008] Generate a table of contents (TOC) for the data objects to be backed up, so as to record each data object in the TOC, and the storage structure of each data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field, so as to record the OID of the large object data contained in the data object based on the blog field;

[0009] In the case where the data objects being backed up include large object data, query the attribution tables of each large object data, and record the OID of the large object data contained in each attribution table into the blog field of the TOC; and

[0010] The large object system table pg_largeobject is split into multiple sub-data tables based on the owning tables of the large object data. The number of sub-data tables corresponds to the number of owning tables containing large object data, and each sub-data table only contains large object data from one owning table.

[0011] Optionally, after splitting the large object system table pg_largeobject into multiple sub-tables, the backup steps also include:

[0012] The large object metadata table pg_largeobject_metadata is split into multiple sub-metadata tables based on the ownership table of the large object data. The number of sub-metadata tables corresponds to the number of ownership tables containing large object data, and each sub-metadata table only contains the large object metadata of one ownership table.

[0013] Optionally, when the data object to be backed up does not contain large object data, the backup is performed according to the original backup logic.

[0014] The present application also provides a method for recovering custom large object data in PostgreSQL. When recovering data in PostgreSQL, the following recovery steps are performed:

[0015] Reading the TOC in the backup data, where the TOC is used to record each data object, and the storage structure of any data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field to record the OID of the large object data contained in the data object based on the blog field;

[0016] Check the blog field in each extended tocEntry structure to determine whether the backup data contains large object data;

[0017] When it is determined that the backup data contains large object data, the sub-data table corresponding to the large object data is obtained, and the sub-data table is inserted into the large object system table pg_largeobject.

[0018] Optionally, the following recovery steps are also included:

[0019] Get the sub-metadata table corresponding to the large object data and insert the sub-metadata table into the large object metadata table pg_largeobject_metadata.

[0020] An embodiment of the present application further provides a terminal device, including a processor and a memory, wherein the memory stores a computer program. When the computer program is executed by the processor, the computer program implements the aforementioned custom large object data backup method for PostgreSQL and / or implements the steps of the aforementioned custom large object data recovery method for PostgreSQL.

[0021] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the computer program implements the aforementioned custom large object data backup method for PostgreSQL and / or implements the steps of the aforementioned custom large object data recovery method for PostgreSQL.

[0022] The embodiment of the present application extends the tocEntry structure to achieve selective recovery of large objects in PostgreSQL data, thereby achieving high efficiency in time and space and flexible customization of data.

[0023] The above description is only an overview of the technical solution of the present application. In order to more clearly understand the technical means of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Various other advantages and benefits will become apparent to those skilled in the art upon reading the detailed description of the preferred embodiment below. The accompanying drawings are for illustration purposes only and are not to be considered as limiting the present application. The same reference symbols are used throughout the drawings to represent the same components. In the drawings:

[0025] Figure 1 This is a basic flow chart of the customized large object data backup method according to an embodiment of the present application;

[0026] Figure 2 This is a basic flow chart of the customized large object data recovery method according to an embodiment of the present application. DETAILED DESCRIPTION

[0027] Exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0028] How large objects are stored in the database

[0029] For example, a database contains five tables: table1, table2, table3, table4, and table5. These tables store movie information. Each table has two fields: name and file. Name holds the movie name, and file is a large object that holds the movie file. Assume each movie file is 1GB in size. If table1 holds 100 movies, these 100 movie files will be stored in the pg_largeobject table, occupying 100GB of disk space. Table1 itself only stores the movie names and the 100 large object IDs, so it takes up very little space. If table2 holds 200 movie files, they will also be stored in the pg_largeobject table, increasing its size to 300GB. Table2 itself stores the 200 movie names and the 200 large object IDs (called OIDs in the database). If table3 holds 300 movies, table4 holds 400 movies, and table5 holds 500 movies, then the pg_largeobject table will ultimately hold 1500 movie files, occupying 1500GB of disk space.

[0030] If you back up the above data and then restore it, PostgreSQL will provide a table of contents (TOC) for the database backup, a basic database feature. This table displays the contents of the backup. Here, it shows table1, table2, table3, table4, and table5 (nothing else). The user can select which table to restore. However, as mentioned in the previous paragraph, the large objects contained in these five tables are stored together. All 1,500 large objects from these five tables are stored in the pg_largeobject system table. Currently, PostgreSQL restores the pg_largeobject table in its entirety or not at all (either 1,500 GB or 0 GB). Therefore, if you choose to restore table1 and table2, PostgreSQL will restore 0 GB of large objects. After the restore is complete, you will find that the large object data for table1 and table2 is missing.

[0031] Therefore, users can only restore large objects by restoring all of table1, table2, table3, table4, and table5; otherwise, all large objects will be lost.

[0032] As mentioned above, large objects in tables are centrally stored in the pg_largeobject system table. In the example above, the 1500GB of large objects from the five tables are all stored in the pg_largeobject system table. If I only want to recover the large objects in table1, I need to find the large object data for table1 in the pg_largeobject table and restore it. However, this is very difficult because, as mentioned earlier, all large object data in the pg_largeobject table is split into 2KB chunks for storage. Therefore, 1500GB of large object data would be stored as 700 to 800 million records in the pg_largeobject table.

[0033] In order to solve the technical problems of PostgreSQL database data backup and recovery of large object data, the embodiment of the present application provides a custom large object data backup method for PostgreSQL. When PostgreSQL performs data backup, Figure 1 As described, perform the following backup steps:

[0034] A table of contents TOC is generated for the data object to be backed up, so as to record each data object in the TOC, and the storage structure of any data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field to record the OID of the large object data contained in the data object based on the blog field. Specifically, the solution of the present application transforms the default TOC structure of the database. When postgresql performs a data backup, it generates a TOC (table of contents) file, which stores a backup list. All objects to be exported (such as tables) are listed in the TOC, and the storage structure of each object is a tocEntry structure. In this embodiment, the tocEntry structure is extended and a new blog field is added to store the OID of the large object data contained in this data object (such as a table).

[0035] In the case where the data objects being backed up include large object data, query the attribution tables of each large object data, and record the OID of the large object data contained in each attribution table into the blog field of the TOC; and

[0036] Split the large object system table pg_largeobject into multiple sub-datatables based on the tables to which the large object data belongs. The number of sub-datatables corresponds to the number of tables containing the large object data, and each sub-datatable contains the large object data of only one table. For example, if pg_largeobject contains large object data from five tables, it can be split into five tables: pg_largeobject_table1, pg_largeobject_table2, pg_largeobject_table3, pg_largeobject_table4, and pg_largeobject_table5. pg_largeobject_table1 will only contain the large object data from table1.

[0037] In some embodiments, after splitting the large object system table pg_largeobject into multiple sub-tables, the backup step further includes:

[0038] The large object metadata table pg_largeobject_metadata is split into multiple sub-metadata tables, based on the table that owns the large object data. The number of sub-metadata tables corresponds to the number of tables containing the large object data, and each sub-metadata table only contains the large object metadata of one table. This example further splits pg_largeobject_metadata as described above. pg_largeobject_metadata stores the owner and permissions of each large object data, making it easier to recover the large object data after splitting.

[0039] In some embodiments, when the data object to be backed up does not contain large object data, the backup is performed according to the original backup logic.

[0040] By extending the tocEntry structure, the embodiment of the present application identifies whether large objects exist in user-selected tables during data recovery, and only recovers these large objects, thereby enabling selective recovery of large objects in PostgreSQL data, achieving efficient time and storage space utilization, and flexible data customization.

[0041] The embodiment of the present application also provides a custom large object data recovery method for PostgreSQL. When PostgreSQL performs data recovery, as shown in FIG. Figure 2 As shown, perform the following recovery steps:

[0042] The TOC in the backup data is read. The TOC is used to record each data object, and the storage structure of any data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field to record the oid of the large object data contained in the data object based on the blog field.

[0043] Check the blog field in each extended tocEntry structure to determine whether the backup data contains large object data.

[0044] When it is determined that the backup data contains large object data, the sub-data table corresponding to the large object data is obtained, and the sub-data table is inserted into the large object system table pg_largeobject.

[0045] For the recovery of large object data, in this example, the TOC in the backup data is first read, and then the blog field of each tocEntry is checked to see if it contains large object data. If so, the corresponding pg_largeobject subtable for this object is found (for example, if the recovery object is table1, its corresponding pg_largeobject subtable is pg_largeobject_table1), and then the contents of this subtable are inserted into the pg_largobject table (because in the real data, there is no pg_largeobject_table1 table, only the pg_largeobject table).

[0046] In some embodiments, the following recovery step is further included: obtaining a sub-metadata table corresponding to the large object data, and inserting the sub-metadata table into the large object metadata table pg_largeobject_metadata.

[0047] Find the corresponding pg_largeobject_metadata subtable and insert its contents into the pg_largeobject_metadata table.

[0048] The remaining non-large object data is restored according to the original logic until all data is restored and the recovery is completed.

[0049] The solution in this application modifies PostgreSQL's existing TOC structure, adding a "blog" field to its tocEntry to identify the large object data associated with this data object. The existing data backup tool is also modified to obtain the large object data for each data object and store it in the TOC described in step 1. The pg_largeobject and pg_largeobject_metadata tables are then split accordingly. During the recovery process, the large object data for each restored object is obtained, and the corresponding pg_largeobject subtable and pg_largeobject_metadata subtable are inserted into the pg_largeobject and pg_largeobject_metadata tables. This completes the recovery of the large object data for a data object.

[0050] In existing solutions, large object data in PostgreSQL databases are either restored in full or not at all, and partial restoration is not possible according to actual conditions. The method of the present application can solve the problem of being unable to selectively restore large objects in existing PostgreSQL data backup and restoration. During restoration, the large object data contained in the data object to be restored can be automatically identified and restored, thereby improving efficiency and making up for the functional defects of native PostgreSQL.

[0051] An embodiment of the present application further provides a terminal device, including a processor and a memory, wherein the memory stores a computer program. When the computer program is executed by the processor, the computer program implements the aforementioned custom large object data backup method for PostgreSQL and / or implements the steps of the aforementioned custom large object data recovery method for PostgreSQL.

[0052] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the computer program implements the aforementioned custom large object data backup method for PostgreSQL and / or implements the steps of the aforementioned custom large object data recovery method for PostgreSQL.

[0053] It should be noted that, in this document, the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, article, or apparatus comprising a series of elements includes not only those elements but also other elements not explicitly listed, or elements inherent to such process, method, article, or apparatus. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, article, or apparatus comprising the element.

[0054] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0055] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus the necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal (which can be a mobile phone, computer, server or network device, etc.) to execute the methods described in each embodiment of the present application.

[0056] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of this application, ordinary technicians in this field can also make many forms without departing from the purpose of this application and the scope of protection of the claims, all of which are protected by this application.

Claims

1. A custom large object data backup method for PostgreSQL, characterized in that: When backing up data in PostgreSQL, perform the following backup steps: Generate a table of contents (TOC) for the data objects to be backed up, so as to record each data object in the TOC, and the storage structure of each data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field, so as to record the OID of the large object data contained in the data object based on the blog field; In the case where the data objects being backed up include large object data, query the attribution tables of each large object data, and record the OID of the large object data contained in each attribution table into the blog field of the TOC; and The large object system table pg_largeobject is split into multiple sub-data tables based on the owning tables of the large object data. The number of sub-data tables corresponds to the number of owning tables containing large object data, and each sub-data table only contains large object data from one owning table.

2. The method for backing up custom large object data for PostgreSQL according to claim 1, wherein: After splitting the large object system table pg_largeobject into multiple sub-tables, the backup steps also include: The large object metadata table pg_largeobject_metadata is split into multiple sub-metadata tables based on the ownership table of the large object data. The number of sub-metadata tables corresponds to the number of ownership tables containing large object data, and each sub-metadata table only contains the large object metadata of one ownership table.

3. The method for backing up custom large object data for PostgreSQL according to claim 1, wherein: When the data object to be backed up does not contain large object data, the backup is performed according to the original backup logic.

4. A custom large object data recovery method for PostgreSQL, characterized in that: When restoring data in PostgreSQL, perform the following recovery steps: Reading the TOC in the backup data, where the TOC is used to record each data object, and the storage structure of any data object is an extended tocEntry structure, wherein the extended tocEntry structure has a newly added blog field to record the OID of the large object data contained in the data object based on the blog field; Check the blog field in each extended tocEntry structure to determine whether the backup data contains large object data; When it is determined that the backup data contains large object data, the sub-data table corresponding to the large object data is obtained, and the sub-data table is inserted into the large object system table pg_largeobject.

5. The method for recovering custom large objects in PostgreSQL according to claim 4, wherein: The following recovery steps are also included: Get the sub-metadata table corresponding to the large object data and insert the sub-metadata table into the large object metadata table pg_largeobject_metadata.

6. A terminal device, characterized in that: The system comprises a processor and a memory, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the system implements the method for backing up custom large object data for PostgreSQL according to any one of claims 1 to 3, and / or implements the steps of the method for restoring custom large object data for PostgreSQL according to claim 4 or 5.

7. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program. When the computer program is executed by a processor, the computer program implements the customized large object data backup method for PostgreSQL according to any one of claims 1 to 3, and / or implements the steps of the customized large object data recovery method for PostgreSQL according to claim 4 or 5.

Images