A database flashback method and device based on WAL log files

Abstract

The application provides a database flash-back method and device based on a WAL log file, which comprises the following steps: obtaining a DDL statement executed by a target database; calling a corresponding function to generate a DDL rollback statement of the DDL statement based on the statement type of the DDL statement; saving the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record a data storage process of the auxiliary table to a WAL log file of the target database; when a flash-back instruction of the target database is received, analyzing the WAL log file based on the flash-back instruction, so as to extract the DDL rollback statement from the preset auxiliary table; and executing the DDL rollback statement, so as to restore the target database to a state before the DDL statement is executed. The application solves the problem that the existing database flash-back tool cannot rollback the DDL statement, improves the accuracy and integrity of the flash-back, is beneficial to quickly restore the misoperation data, shortens the fault time, and reduces the influence on the production application.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a database flashback method and device based on WAL log files. BACKGROUND

[0002] PostgreSQL is an open source client / server relational database management system, and with the increasing power of Postgresql, it has been applied to various fields in life. The Write-Ahead Logging (WAL) is a high-efficiency log algorithm in a database, which adopts an append-write mode to efficiently record user modification operations to a log file. It is widely used in database stream replication, logical replication, database fault recovery and other scenarios. The basic working principle of database flashback is to parse the WAL log file into specific operation transaction statements through a log analysis plug-in, quickly generate the corresponding reverse operation flashback statements and apply them to the database, which is a common means of quickly recovering misoperation data.

[0003] In the prior art, the working principle of the database flashback device is as follows: the content of the WAL log file is parsed, and then the transaction operation type is converted into a reverse operation flashback statement, which is then applied to the target data to quickly recover the misoperation database state. However, if the database has executed a DDL statement, since the WAL log file only records the Data Manipulation Language (DML) and does not record the Data Definition Language (DDL), the flashback device cannot roll back the DDL statement to the state before the DDL statement is executed by parsing the WAL log, which affects the accuracy of the database flashback result. SUMMARY

[0004] Therefore, the embodiments of the present application provide a database flashback method and device based on WAL log files to overcome the problem in the prior art that the database flashback device cannot roll back the DDL statement to the state before the DDL statement is executed by parsing the WAL log, which affects the accuracy of the database flashback result.

[0005] According to a first aspect, the embodiments of the present application provide a database flashback method based on WAL log files, comprising:

[0006] obtaining a DDL statement executed by a target database;

[0007] calling a corresponding function to generate a DDL rollback statement of the DDL statement based on the statement type of the DDL statement;

[0008] The DDL statement and the DDL rollback statement are stored in a preset auxiliary table, and a data storage process of the auxiliary table is recorded in a WAL log file of the target database;

[0009] When a flashback instruction of the target database is received, the WAL log file is parsed based on the flashback instruction to extract the DDL rollback statement from the preset auxiliary table, and the flashback instruction is an instruction for restoring the target database to a state before the DDL statement is executed;

[0010] The DDL rollback statement is executed to restore the target database to a state before the DDL statement is executed.

[0011] Optionally, the DDL statement executed by the target database is obtained by:

[0012] The DDL statement executed by the target database is intercepted by creating a DDL event trigger.

[0013] Optionally, the WAL log file is parsed based on the flashback instruction to extract the DDL rollback statement from the preset auxiliary table, and the method comprises the following steps of:

[0014] The WAL log file is parsed by using a corresponding flashback time in the flashback instruction;

[0015] It is judged whether the record corresponding to the flashback time belongs to the preset auxiliary table;

[0016] When the DML statement record corresponding to the flashback time belongs to the preset auxiliary table, the DDL rollback statement is extracted from the preset auxiliary table.

[0017] Optionally, when the DML statement record corresponding to the flashback time does not belong to the preset auxiliary table, the DML statement record is parsed to generate a reverse reverse DML statement;

[0018] The reverse reverse DML statement is executed to restore the target database to a state before the DML statement is executed.

[0019] Optionally, the data storage process of the auxiliary table is recorded in the WAL log file of the target database, and the method comprises the following steps of:

[0020] The data storage process of the auxiliary table is converted into a DML statement record and recorded in the WAL log file of the target database.

[0021] Optionally, it is judged whether the record corresponding to the flashback time belongs to the preset auxiliary table, and the method comprises the following steps of:

[0022] determine whether the DML statement corresponding to the flashback time contains information of the preset auxiliary table;

[0023] when the DML statement corresponding to the flashback time contains information of the preset auxiliary table, determine that the DML statement corresponding to the flashback time belongs to the preset auxiliary table.

[0024] Optionally, after saving the DDL statement and the DDL rollback statement to the preset auxiliary table, the method further comprises:

[0025] saving element information of the DDL statement and the DDL rollback statement to the preset auxiliary table.

[0026] According to a second aspect, an embodiment of the present application provides a database flashback device based on a WAL log file, comprising:

[0027] an acquisition module configured to acquire a DDL statement executed by a target database;

[0028] a first processing module configured to generate a DDL rollback statement of the DDL statement based on a statement type of the DDL statement by calling a corresponding function;

[0029] a second processing module configured to save the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record a data storage process of the auxiliary table to a WAL log file of the target database;

[0030] a third processing module configured to, when a flashback instruction of the target database is received, parse the WAL log file based on the flashback instruction, so as to extract the DDL rollback statement from the preset auxiliary table, wherein the flashback instruction is an instruction for restoring the target database to a state before the DDL statement is executed;

[0031] a fourth processing module configured to execute the DDL rollback statement, so as to restore the target database to the state before the DDL statement is executed.

[0032] According to a third aspect, an embodiment of the present application provides an electronic device, comprising:

[0033] a memory and a processor, which are communicatively connected, and the memory stores computer instructions, and the processor executes the computer instructions to perform the method in the first aspect and any optional implementation manner thereof.

[0034] According to a fourth aspect, an embodiment of the present application provides a computer readable storage medium storing computer instructions for causing a computer to execute the method of the first aspect, or any optional implementation thereof.

[0035] The technical scheme of the present application has the following advantages.

[0036] The database flashback method and device based on a WAL log file provided by the embodiment of the present application, by acquiring a DDL statement executed by a target database; calling a corresponding function based on the statement type of the DDL statement to generate a DDL rollback statement of the DDL statement; saving the DDL statement and the DDL rollback statement to a preset auxiliary table, to record a data storage process of the auxiliary table to a WAL log file of the target database; when a flashback instruction of the target database is received, parsing the WAL log file based on the flashback instruction, to extract the DDL rollback statement from the preset auxiliary table; executing the DDL rollback statement, to make the target database return to a state before the DDL statement is executed. Thus, by capturing the DDL statement executed by the database and storing the DDL statement and a corresponding DDL rollback statement in the auxiliary table, the storage action to the auxiliary table is used to complete the conversion of the DDL statement to a DML statement, when a user needs to flashback to before the DDL statement is executed, the DDL rollback statement is found from the auxiliary table by parsing the WAL log, to execute the DDL rollback statement, to return the database to the state before the DDL is executed, thereby solving the problem that the existing flashback tool cannot rollback the DDL statement, improving the accuracy and integrity of the flashback, being conducive to quickly recovering the misoperation data, shortening the fault time, and reducing the influence on production application. BRIEF DESCRIPTION OF DRAWINGS

[0037] In order to more clearly illustrate the technical scheme in the specific embodiments or prior art of the present application, the drawings needed in the specific embodiments or prior art description will be briefly introduced as follows. Obviously, the drawings in the following description are some embodiments of the present application, and those skilled in the art can also obtain other drawings according to these drawings without creative labor.

[0038] Figure 1 A schematic diagram of the principle of the existing technology for implementing database flashback of the flashback device;

[0039] Figure 2 A flowchart of the database flashback method based on a WAL log file of the embodiment of the present application;

[0040] Figure 3 A schematic diagram of the overall working process of the database flashback based on a WAL log file of the embodiment of the present application;

[0041] Figure 4This is a schematic diagram of the structure of a database flashback device based on WAL log files according to an embodiment of the present invention;

[0042] Figure 5 This is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. Detailed Implementation

[0043] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0044] The technical features involved in the different embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0045] like Figure 1 As shown, the working principle of a database flashback mechanism in existing technology is as follows: by parsing the contents of the WAL log file, and then converting them into flashback statements that reverse the operation type of the transaction, these statements are applied to the target data to quickly restore the database from a state of erroneous operation. However, if the database has executed DDL statements, since the WAL log file only records Data Manipulation Language (DML) statements and not Data Definition Language (DDL) statements, the flashback mechanism cannot restore the database to the state before the DDL statements were executed by parsing the WAL log and rolling back the DDL statements, thus affecting the accuracy of the database flashback results.

[0046] To address the aforementioned problems, embodiments of the present invention provide a database flashback method based on WAL log files, such as... Figure 2 As shown, this database flashback method based on WAL log files specifically includes the following steps:

[0047] Step S101: Obtain the DDL statements executed by the target database.

[0048] Specifically, DDL event triggers are created to intercept DDL statements executed by the target database.

[0049] In actual application, the DDL statement of the specified type (changing table structure) can be intercepted by creating a DDL event trigger, because the DDL statement of the specified type (changing table structure) will change the table structure of the database and cause structural changes to the database. For example, the DDL statement of the specified type can be DROPTABLE, DROP SEQUENCE, and the like, but is not limited thereto. By automatically intercepting the DDL statement of the specified type, accurate positioning can be facilitated when subsequent flashback operation is performed.

[0050] Step S102: generating a DDL rollback statement of the DDL statement based on the statement type of the DDL statement.

[0051] Specifically, the rollback DDL is generated according to the statement type of the original DDL. For example, assuming that the DDL statement is createtable xxx; the rollback statement drop table xxx is generated by calling the corresponding function. The specific function can be customized in advance according to the statement type, and the present application is not limited thereto.

[0052] Step S103: saving the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record the data storage process of the auxiliary table to the WAL log file of the target database.

[0053] The preset auxiliary table is a data table created in advance for storing the DDL statement and the corresponding DDL rollback statement. Specifically, the data storage process of the auxiliary table is converted into a DML statement and recorded to the WAL log file of the target database.

[0054] Step S104: when receiving a flashback instruction of the target database, analyzing the WAL log file based on the flashback instruction to extract the DDL rollback statement from the preset auxiliary table.

[0055] The flashback instruction is an instruction for restoring the target database to a state before the execution of the DDL statement.

[0056] Step S105: executing the DDL rollback statement to restore the target database to a state before the execution of the DDL statement.

[0057] By performing the above steps, the database flashback method based on the WAL log file provided by the embodiment of the present application solves the problem that the existing flashback tool cannot roll back the DDL statement by capturing the DDL statement executed on the database and storing it in the auxiliary table with the corresponding DDL rollback statement, and by using the storage operation to the auxiliary table to complete the conversion of the DDL statement to the DML statement, and by retrieving the DDL rollback statement from the auxiliary table by parsing the WAL log when the user needs to flashback to before the DDL statement is executed, to restore the state of the database before the DDL is executed, thereby improving the accuracy and integrity of the flashback, facilitating the quick recovery of the misoperation data, shortening the fault time, and reducing the impact on the production application.

[0058] Specifically, in an embodiment, as shown in Figure 3 The step S104 of parsing the WAL log file based on the flashback instruction from the preset auxiliary table to extract the DDL rollback statement specifically includes the following steps:

[0059] Step S401: The WAL log file is parsed by using the corresponding flashback time in the flashback instruction.

[0060] The flashback time is the time corresponding to the state to which the user expects the database to be restored.

[0061] Step S402: Determine whether the record corresponding to the flashback time belongs to the preset auxiliary table.

[0062] Specifically, the step S402 determines whether the DML statement corresponding to the flashback time contains information of the preset auxiliary table, and determines that the DML statement corresponding to the flashback time belongs to the preset auxiliary table when the DML statement corresponding to the flashback time contains the information of the preset auxiliary table. In actual application, when the data information is stored in the preset auxiliary table, the corresponding DML statement is generated, and the DML statement contains the table name of the operation object, i.e., the preset auxiliary table, and the like. For example, whether the DML statement contains the table name corresponding to the preset auxiliary table can be used to determine whether the DML statement corresponding to the flashback time belongs to the preset auxiliary table, and if it belongs, it means that the statement executed by the flashback time is a DDL statement.

[0063] Step S403: When the DML statement record corresponding to the flashback time belongs to the preset auxiliary table, extract the DDL rollback statement from the preset auxiliary table.

[0064] Step S404: When the DML statement record corresponding to the flashback time does not belong to the preset auxiliary table, parse the DML statement record to generate the reverse and reverse DML statement.

[0065] Step S405: Execute the reverse and reverse DML statement to restore the target database to the state before the DML statement is executed.

[0066] In practical applications, the statements executed by the database during flashback may be ordinary DML statements. Therefore, the database flashback can be achieved by generating reverse DML statements.

[0067] Specifically, in one embodiment, after performing step S103 to save the DDL statements and DDL rollback statements to a preset auxiliary table, the database flashback method based on WAL log files provided in this embodiment of the invention further includes the following steps:

[0068] Step S301: Save the element information of DDL statements and DDL rollback statements to a preset auxiliary table.

[0069] For example, this element information includes: the objects and contents of DDL statements and DDL rollback statements. Therefore, when performing a DDL rollback, the DDL rollback statements to be executed can be directly selected based on this element information, further improving execution efficiency.

[0070] like Figure 3 As shown, the database flashback method based on WAL log files provided in this embodiment of the invention, by setting up a DDL interceptor and a flashback device, saves the metadata information of the DDL executed by the user in an auxiliary table through the DDL interceptor mechanism, completing the conversion of DDL statements to DML statements to achieve DDL statement rollback. By setting a flashback function, the rollback statement of the DDL is automatically stored in the auxiliary table, converted into a DML statement and stored in the WAL log file. When the user needs to roll back the DDL statement, the DDL rollback statement is parsed from the fb_ddl_command table in the WAL log to restore the state before the DDL execution, achieving the effect of restoring the DDL statement and ensuring that subsequent DML recovery can proceed smoothly. This solves the pain point of users being unable to roll back DDL statements and improves the accuracy and completeness of the flashback tool.

[0071] By performing the above steps, the database flashback method based on WAL log files provided in this embodiment of the invention captures the DDL statements executed by the database and stores them along with the corresponding DDL rollback statements in an auxiliary table. The conversion from DDL statements to DML statements is completed by storing the data in the auxiliary table. When a user needs to flashback to before the execution of the DDL statement, the DDL rollback statement is retrieved from the auxiliary table by parsing the WAL log, thus restoring the database to its state before the DDL execution. This solves the problem that existing flashback tools cannot roll back DDL statements, improves the accuracy and completeness of the flashback, facilitates rapid recovery of erroneous data, shortens downtime, and reduces the impact on production applications.

[0072] The embodiment of the present application also provides a database flashback device based on a WAL log file. Figure 4 As shown in the figure, the database flashback device based on the WAL log file comprises:

[0073] The acquisition module 101 is configured to acquire a DDL statement executed by a target database. For details, refer to the related description of step S101 in the method embodiment, which will not be repeated here.

[0074] The first processing module 102 is configured to call a corresponding function to generate a DDL rollback statement of the DDL statement based on the statement type of the DDL statement. For details, refer to the related description of step S102 in the method embodiment, which will not be repeated here.

[0075] The second processing module 103 is configured to save the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record a data storage process of the auxiliary table to a WAL log file of the target database. For details, refer to the related description of step S103 in the method embodiment, which will not be repeated here.

[0076] The third processing module 104 is configured to parse the WAL log file based on a flashback instruction when the flashback instruction of the target database is received, so as to extract the DDL rollback statement from the preset auxiliary table, wherein the flashback instruction is an instruction for restoring the target database to a state before the DDL statement is executed. For details, refer to the related description of step S104 in the method embodiment, which will not be repeated here.

[0077] The fourth processing module 105 is configured to execute the DDL rollback statement, so as to restore the target database to a state before the DDL statement is executed. For details, refer to the related description of step S105 in the method embodiment, which will not be repeated here.

[0078] The database flashback device based on the WAL log file provided by the embodiment of the present application is used to execute the database flashback method based on the WAL log file provided by the above embodiment, and the implementation manner and principle are the same, and for details, refer to the related description of the method embodiment, which will not be repeated here.

[0079] Through the cooperation of the above various components, the database flashback device based on the WAL log file provided by the embodiment of the present application can capture the DDL statement executed on the database and store the corresponding DDL rollback statement in the auxiliary table, and the conversion of the DDL statement to the DML statement is completed by the storage operation to the auxiliary table. When the user needs to flashback to before the execution of the DDL statement, the DDL rollback statement is found from the auxiliary table by analyzing the WAL log, and the state of the database before the execution of the DDL statement is restored, thereby solving the problem that the existing flashback tool cannot rollback the DDL statement, improving the accuracy and integrity of the flashback, and being beneficial to quickly recover the misoperation data, shorten the fault time, and reduce the impact on the production application.

[0080] Figure 5 An electronic device is shown, such as Figure 5 The electronic device includes a processor 901 and a memory 902, wherein the processor 901 and the memory 902 can be connected by a bus or other means, Figure 5 The bus connection is taken as an example.

[0081] The processor 901 can be a central processing unit (CPU). The processor 901 can also be other general-purpose processors, digital signal processors (DSP), application specific integrated circuits (ASIC), field programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or a combination of the above.

[0082] The memory 902 is a non-transitory computer readable storage medium, which can be used to store non-transitory software programs, non-transitory computer executable programs and modules, such as program instructions / modules corresponding to the methods in the above method embodiments. The processor 901 performs various functional applications and data processing of the processor by running the non-transitory software programs, instructions and modules stored in the memory 902, that is, implements the methods in the above method embodiments.

[0083] The memory 902 can include a program storage area and a data storage area, where the program storage area can store an operating system, application programs required by at least one function, and the data storage area can store data created by the processor 901 and the like. In addition, the memory 902 can include a high-speed random access memory, and can further include a non-transitory memory, such as at least one disk storage device, a flash memory device, or other non-transitory solid-state storage device. In some embodiments, the memory 902 can optionally include a memory disposed remotely from the processor 901, which can be connected to the processor 901 through a network. Examples of the above network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and a combination thereof.

[0084] One or more modules are stored in the memory 902, which, when executed by the processor 901, perform the methods in the above method embodiments.

[0085] The above electronic device specific details can be understood in correspondence with the relevant description and effects of the corresponding method embodiments described above, which will not be described here again.

[0086] Those skilled in the art can understand that all or part of the processes in the above embodiments can be completed by a computer program instructing related hardware, and the implemented program can be stored in a computer readable storage medium. When the program is executed, it can include the processes of the above embodiments. The storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), a random access memory (RAM), a flash memory, a hard disk drive (HDD) or a solid state drive (SSD), etc. The storage medium can also include a combination of the above types of memories.

[0087] Although the embodiments of the present application are described in conjunction with the accompanying drawings, various modifications and changes can be made by those skilled in the art without departing from the spirit and scope of the present application, and such modifications and changes fall within the scope defined by the appended claims.

Claims

1. A database flashback method based on a WAL log file, characterized in that, The method comprises the following steps: acquiring a DDL statement executed by a target database; generating a DDL rollback statement of the DDL statement based on a statement type of the DDL statement by calling a corresponding function; saving the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record a data storage process of the auxiliary table to a WAL log file of the target database; when a flashback instruction of the target database is received, analyzing the WAL log file based on the flashback instruction, so as to extract the DDL rollback statement from the preset auxiliary table, wherein the flashback instruction is an instruction for restoring the target database to a state before the DDL statement is executed; executing the DDL rollback statement, so as to restore the target database to the state before the DDL statement is executed; analyzing the WAL log file by using a corresponding flashback time in the flashback instruction; determining whether a record corresponding to the flashback time belongs to the preset auxiliary table; when the DML statement record corresponding to the flashback time belongs to the preset auxiliary table, extracting the DDL rollback statement from the preset auxiliary table; when the DML statement record corresponding to the flashback time does not belong to the preset auxiliary table, analyzing the DML statement record to generate a reverse and reverse-ordered DML statement; executing the reverse and reverse-ordered DML statement, so as to restore the target database to a state before the DML statement is executed. The acquiring of the DDL statement executed by the target database comprises the following steps:

2. The method of claim 1, wherein, intercepting the DDL statement executed by the target database by creating a DDL event trigger. The recording of the data storage process of the auxiliary table to the WAL log file of the target database comprises the following steps:

3. The method of claim 1, wherein, converting the data storage process of the auxiliary table into a DML statement and recording the DML statement to the WAL log file of the target database. The determination of whether the record corresponding to the flashback time belongs to the preset auxiliary table comprises the following steps:

4. The method of claim 3, wherein, determining whether the preset auxiliary table information is contained in the DML statement corresponding to the flashback time; when the preset auxiliary table information is contained in the DML statement corresponding to the flashback time, it is determined that the DML statement corresponding to the flashback time belongs to the preset auxiliary table. After the DDL statement and the DDL rollback statement are saved to the preset auxiliary table, the method further comprises the following steps:

5. The method of claim 1, wherein, saving element information of the DDL statement and the DDL rollback statement to the preset auxiliary table. The method comprises the following steps:

6. A database flashback apparatus based on a WAL log file, characterized by, an acquiring module, configured to acquire a DDL statement executed by a target database; a first processing module, configured to generate a DDL rollback statement of the DDL statement based on a statement type of the DDL statement by calling a corresponding function; a second processing module, configured to save the DDL statement and the DDL rollback statement to a preset auxiliary table, so as to record a data storage process of the auxiliary table to a WAL log file of the target database; ​ The third processing module is configured to, when the flashback instruction of the target database is received, parse the WAL log file based on the flashback instruction to extract the DDL rollback statement from the preset auxiliary table, and the flashback instruction is an instruction for restoring the target database to a state before the DDL statement is executed. The method further includes: parsing the WAL log file based on the flashback instruction to extract the DDL rollback statement from the preset auxiliary table, including: parsing the WAL log file by using a corresponding flashback time in the flashback instruction; determining whether a record corresponding to the flashback time belongs to the preset auxiliary table; extracting the DDL rollback statement from the preset auxiliary table when the DML statement record corresponding to the flashback time belongs to the preset auxiliary table; and parsing the DML statement record to generate a reverse and reverse DML statement when the DML statement record corresponding to the flashback time does not belong to the preset auxiliary table; and executing the reverse and reverse DML statement to restore the target database to a state before the DML statement is executed. The fourth processing module is configured to execute the DDL rollback statement to restore the target database to a state before the DDL statement is executed.

7. An electronic device, comprising: The memory and the processor are connected in communication with each other, the memory stores computer instructions, and the processor executes the computer instructions to perform the method according to any one of claims 1-5. The computer readable storage medium stores computer instructions for causing a computer to perform the method according to any one of claims 1-5.

8. A computer-readable storage medium, characterized in that, ​

Images