PostgreSQL database misoperation recovery method and device, equipment and medium

Abstract

The application discloses a PostgreSQL database misoperation recovery method and device, equipment and medium, and relates to the technical field of PostgreSQL database. The method comprises the following steps: monitoring operation actions in a PostgreSQL database, and acquiring operation information of the operation actions; wherein the operation actions comprise operation actions on a data manipulation language, operation actions for deleting tables based on a data definition language, and operation actions on system tables of the PostgreSQL database; creating corresponding preset record tables based on the operation information; when there is a misoperation in the PostgreSQL database, filtering and querying the content in the preset record tables to determine data information of the misoperation; and recovering the PostgreSQL database according to the data information. Through the technical scheme of the application, the deficiencies and limitations of recovering data when a PostgreSQL database is misoperated in the prior art can be overcome, and the problem of data loss caused by human misoperation of the PostgreSQL database can be solved without stopping service.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of PostgreSQL database, and in particular to a PostgreSQL database error operation recovery method, device, equipment and medium. BACKGROUND

[0002] The PostgreSQL (hereinafter referred to as PG) database is the most advanced open source database in the world, and its development has been relatively rapid in recent years, and it is applied to more and more industries and fields. In the process of using the database, manual errors often occur, resulting in loss of some data and other problems. In view of the above problems, the PG database can perform timeline-based recovery through pre-prepared backups: physical backup and logical backup, but often needs to stop the service for operation, which is very costly. At present, the prior art proposes a PG database data flashback query and recovery method and device, which proposes a delayed data cleaning technology. However, in the implementation, it is limited to table-based row data error operation recovery, and the delay time is affected by the PG server configuration, and the recorded error operations have the risk of being erased, so the recoverability of the error operation data is limited, and once the function is started, all database table operation records will be recorded, lacking flexibility.

[0003] In summary, how to realize the recovery of the error operation without stopping the service and overcome the deficiencies and limitations of the PG error operation recovery is a problem to be solved at present. SUMMARY

[0004] Therefore, the purpose of the present application is to provide a PostgreSQL database error operation recovery method, device, equipment and medium, which can realize the recovery of the error operation without stopping the service and overcome the deficiencies and limitations of the PG error operation recovery. The specific scheme is as follows:

[0005] In a first aspect, the present application discloses a PostgreSQL database error operation recovery method, comprising:

[0006] monitoring operation actions in the PostgreSQL database and obtaining operation information of the operation actions; wherein the operation actions include operation actions on data manipulation language, operation actions of deleting tables based on data definition language and operation actions on system tables of the PostgreSQL database;

[0007] creating a corresponding preset record table based on the operation information;

[0008] When there is an error operation in the PostgreSQL database, the content in the preset record table is queried and screened to determine the data information of the error operation;

[0009] restoring the PostgreSQL database according to the data information.

[0010] Optionally, the operation information of the operation action comprises:

[0011] obtaining an operation table of the data manipulation language;

[0012] Correspondingly, the operation information comprises:

[0013] determining whether the operation table already has a first record table, if yes, deleting the first record table and re-creating a second record table corresponding to the operation table, if not, directly creating the second record table corresponding to the operation table;

[0014] determining whether a first trigger function exists in the PostgreSQL database, if not, creating the first trigger function, if yes, determining whether a first trigger acting on the operation table exists; wherein the first trigger function is used to perform different operations on the second record table for different types of type commands; the first trigger is used to capture the type commands based on different levels and execute the first trigger function after the type commands act on the operation table;

[0015] if the first trigger exists, starting the first trigger and executing the first trigger function, and then performing different operations on the second record table according to the type commands to obtain a corresponding preset record table; if the first trigger does not exist, creating the first trigger, and then executing the step of starting the first trigger and executing the first trigger function.

[0016] Optionally, the operation information of the operation action comprises:

[0017] obtaining a database name and a user name corresponding to deleting a table in the PostgreSQL database based on the data definition language;

[0018] Correspondingly, the operation information comprises:

[0019] determining a target database for deleting a table according to the database name and the user name;

[0020] loading a first preset plug-in in the target database and determining whether a second trigger function exists in the target database;

[0021] If the second trigger function exists, the second trigger function is executed through a second trigger, and a corresponding preset record table is created based on an archiving mode table;

[0022] If the second trigger function does not exist, the second trigger function is created, and then the step of executing the second trigger function through the second trigger is performed.

[0023] Optionally, the changing, by the first preset plug-in, of the name of the non-archiving mode table and the changing of the non-archiving mode table into the archiving mode table based on the preset changing rule comprises:

[0024] The changing, by the first preset plug-in, of the name of the non-archiving mode table and the changing of the non-archiving mode table into the archiving mode table based on an MD5 encoding string of a random number and a time stamp.

[0025] Optionally, the obtaining of the operation information of the operation action comprises:

[0026] The modified operation command is obtained based on a hook, and a system table name corresponding to the modified operation command is obtained;

[0027] Correspondingly, the creating of the preset record table based on the operation information comprises:

[0028] It is determined whether a second preset plug-in is loaded in a corresponding system table according to the system table name;

[0029] If the second preset plug-in is loaded, a third record table corresponding to the system table in a public mode is recorded by using the second preset plug-in, so as to obtain the preset record table;

[0030] If the second preset plug-in is not loaded, the second preset plug-in is added in a configuration file based on an object permission type in the PostgreSQL database and a system table type identifier of the system table, and then the third record table corresponding to the system table in the public mode is recorded by using the second preset plug-in, so as to obtain the preset record table.

[0031] Optionally, the screening and querying of the content in the preset record table to determine the data information of the misoperation comprises:

[0032] A screening parameter in the preset record table is determined, and a recovery parameter of a misoperation in the preset record table is screened based on the screening parameter;

[0033] Correspondingly, the restoring of the PostgreSQL database according to the data information comprises:

[0034] According to the recovery parameter, all records of the misoperation are acquired and traversed to perform different recovery on the records.

[0035] Optionally, the misoperation recovery method based on the PostgreSQL database further includes:

[0036] The data information in the PostgreSQL database is monitored, and a monitoring period for monitoring the data information is set to create a timing task.

[0037] A preset proportion threshold and a preset cleaning step length are set for the data information when the data is automatically cleaned based on the timing task, wherein the preset proportion threshold is a percentage of data size in the data information occupying storage, and the preset cleaning step length is a number of tables cleaned in the data information.

[0038] If the percentage of current data size occupying the storage in the current data information is greater than the preset proportion threshold, the records in the preset record table are sorted in ascending order based on the time stamp, and then the same number of preset record tables as the preset cleaning step length are filtered out and sequentially deleted.

[0039] In a second aspect, the application discloses a misoperation recovery device based on a PostgreSQL database, which includes:

[0040] An operation information acquisition module is configured to monitor operation actions in the PostgreSQL database and acquire operation information of the operation actions, wherein the operation actions include operation actions on a data manipulation language, operation actions of deleting tables based on a data definition language, and operation actions on system tables of the PostgreSQL database.

[0041] A preset record table creation module is configured to create corresponding preset record tables based on the operation information.

[0042] A screening query module is configured to, when there is a misoperation in the PostgreSQL database, screen and query contents in the preset record tables to determine data information of the misoperation.

[0043] A recovery module is configured to recover the PostgreSQL database according to the data information.

[0044] In a third aspect, the application discloses an electronic device, which includes a processor and a memory; wherein the memory is configured to store a computer program, the computer program is loaded and executed by the processor to implement the misoperation recovery method based on the PostgreSQL database as described above.

[0045] In a fourth aspect, the application discloses a computer readable storage medium for storing a computer program, wherein the computer program is executed by a processor to implement the method for recovering from a misoperation based on a PostgreSQL database as described above.

[0046] In the application, an operation action in the PostgreSQL database is monitored, and operation information of the operation action is acquired; the operation action includes an operation action on a data manipulation language, an operation action of deleting a table based on a data definition language, and an operation action on a system table of the PostgreSQL database; a corresponding preset record table is created based on the operation information; when a misoperation exists in the PostgreSQL database, content in the preset record table is filtered and queried to determine data information of the misoperation; and the PostgreSQL database is recovered according to the data information. It can be seen that, for the operation action on the data manipulation language and the operation action on the system table of the PG database, the record of the record table improves the recoverable degree of the PG; for the operation action of deleting a table based on the data definition language, the recovery based on the record table improves the speed and efficiency of the recovery; and for different misoperations, the recovery function of the PG database is realized, and when the record table is queried and recovered after the misoperation, the service does not need to be stopped, the fault tolerance and the recoverable ability of the PG service are improved. Meanwhile, all misoperations since the misoperation function is started can be recovered, and the recoverable degree of the database is not limited, and the misoperation function can be flexibly started for a certain table. In this way, the misoperation recovery of the data manipulation language is realized by recording, querying and recovering different operation actions, the misoperation recovery based on the data definition language is realized, and the misoperation recovery of the system table operation is realized. BRIEF DESCRIPTION OF DRAWINGS

[0047] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiments or the prior art description. Obviously, the drawings in the following description only constitute the embodiments of the present application, and for those skilled in the art, other drawings can be obtained without creative labor on the basis of the provided drawings.

[0048] Figure 1 A method for recovering from a misoperation based on a PostgreSQL database is disclosed in the application;

[0049] Figure 2 A specific method for recovering from a misoperation based on a PostgreSQL database is disclosed in the application;

[0050] Figure 3A flowchart for recording operations of a data manipulation language according to the present application;

[0051] Figure 4 A flowchart for restoring functions of a data manipulation language according to the present application;

[0052] Figure 5 A specific flowchart of a method for recovering from an error operation based on a PostgreSQL database according to the present application;

[0053] Figure 6 A flowchart for recording deletion of tables of a data definition language according to the present application;

[0054] Figure 7 A specific flowchart of a method for recovering from an error operation based on a PostgreSQL database according to the present application;

[0055] Figure 8 A structural schematic diagram of an apparatus for recovering from an error operation based on a PostgreSQL database according to the present application;

[0056] Figure 9 A structural diagram of an electronic device according to the present application. DETAILED DESCRIPTION

[0057] The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative work fall within the scope of protection of the present application.

[0058] Currently, in a PG database, recovery from an error operation of a physical backup and a logical backup is based on a timeline, and often needs to stop services for operation, which is very costly. The data flashback query and recovery method of a PG database proposed in the prior art is limited to recovery from an error operation of row data based on a table in implementation, the recoverable degree of error operation data is limited, and once the function is started, the operation of all database tables is recorded, which lacks flexibility.

[0059] Therefore, the present application provides a recovery scheme from an error operation based on a PostgreSQL database, which can recover from an error operation without stopping services, and overcomes the deficiencies and limitations of PG error operation recovery.

[0060] The embodiments of the present application disclose a method for recovering from an error operation based on a PostgreSQL database, referring to Figure 1 The method comprises the following steps.

[0061] Step S11: monitoring an operation action in the PostgreSQL database and obtaining operation information of the operation action; wherein the operation action comprises an operation action on a data manipulation language, an operation action of deleting a table based on a data definition language, and an operation action on a system table of the PostgreSQL database.

[0062] In the embodiments of the present application, during the use of the database, manual misoperation often leads to loss of some data and other problems, therefore, the operation action in the PG database is monitored, and the operation information of the operation action in the PG database is recorded, and data recovery in misoperation is performed based on the operation information.

[0063] In the embodiments of the present application, the recovery of misoperation of PG is mainly realized through three modules, including a data manipulation language module (DML), a data definition language module (DDL), and a system table module. That is, the operation action information of three aspects is obtained, including an operation action on a data manipulation language such as INSERT (insert), UPDATE (update), DELETE (delete), and TRUNCATE (delete), an operation action of deleting a table based on a data definition language such as DROP TABLE (remove table), and an operation action on a system table of the PG database such as DROP ROLE (remove role) and ALTER ROLE (change role) commands acting on pg_authid in the system table and modification in the system table, so as to correspond to the recovery of different types of misoperation.

[0064] Step S12: creating a corresponding preset record table based on the operation information.

[0065] In the embodiments of the present application, for the recovery of different types of misoperation, the operation information is first recorded, and after the operation information of different types is recorded, the data information in the record table can be used for recovery in manual misoperation. Based on the record of the library file, the recoverable degree of PG is improved.

[0066] In the embodiments of the present application, the corresponding preset record table is created, and the content of the preset record table corresponding to different types of misoperation is different. For the DML module, the preset record table records some operation actions and data of the DML; for the DDL module, the preset record table records the operation action of deleting a table and the information thereof; and for the system table module, the preset record table records the specific record of the operation table before the command of modifying the system table is executed.

[0067] Step S13: When there is a misoperation in the PostgreSQL database, the content in the preset record table is queried to determine the data information of the misoperation.

[0068] In the embodiment of the present application, after the corresponding preset record table is created, when there is a misoperation on the PG database, the record in the preset record table is queried to locate the related data information of the misoperation. Specifically, when there is a misoperation in the PostgreSQL database, the filtering parameters in the preset record table are determined, and the recovery parameters of the misoperation in the preset record table are filtered based on the filtering parameters.

[0069] It can be understood that when the preset record table is queried to locate the misoperation information, the filtering parameters in the preset table need to be determined first. Through the filtering parameters, it can be known which data is filtered. For example, the database name of the misoperation recovery, the start time and end time of the misoperation, the operation user, etc. are obtained. Based on the filtering parameters, the query of the record is improved, the flexibility of starting the misoperation function is improved, and the performance loss of the PG service is greatly reduced.

[0070] In the embodiment of the present application, after the specific record table with a misoperation is queried through the filtering parameters, the query result obtained is the recovery parameter of the misoperation. For example, when there is a misoperation in the PG database for the DML module, the identifier xid of the misoperation is located by querying the record in the preset table; when there is a misoperation in the PG database for the DML module, the current timestamp of the misoperation is located by querying the record in the preset table; when there is a misoperation in the PG database for the system table module, the unique identifier ID of the misoperation line record is located by querying the record in the preset table. The recovery parameter can be used to realize the recovery of the misoperation table.

[0071] Step S14: The PostgreSQL database is recovered according to the data information.

[0072] Since in the prior art, when the data recovery of the misoperation is started, the operation record of all database tables lacks flexibility. In the embodiment of the present application, the recovery function of the misoperation can be started for a specified table. Specifically, all records of the misoperation are obtained according to the recovery parameters, and the records are traversed to recover the records differently.

[0073] In the embodiment of the present application, when the misoperation in the database is recovered, the operation of the record in the preset record table is recovered. Since the recovery parameter used for data recovery is determined when the preset record table is queried, the recovery parameter can be used to query the corresponding preset record table, and all records of the misoperation are obtained.

[0074] In the embodiments of the present application, all records of misoperations are traversed, and different recovery operations can be performed on each record according to different operation types until all records are recovered.

[0075] In the embodiments of the present application, the space can be prewarned and automatically cleaned, and the stability of the PG and the sustainability of the misoperation recovery can be improved through data monitoring and automatic data cleaning. Specifically, the data information in the PostgreSQL database is monitored, and a monitoring period for monitoring the data information is set to create a timing task; a preset proportion threshold and a preset cleaning step length of the data information when the data is automatically cleaned are set based on the timing task; the preset proportion threshold is the percentage of the data size in the data information occupying the storage; and the preset cleaning step length is the number of tables cleaned in the data information; if the percentage of the current data size occupying the storage in the current data information is greater than the preset proportion threshold, the records in the preset record table are sorted in ascending order based on the time stamp, and then the same number of preset record tables as the preset cleaning step length are filtered out for sequential deletion.

[0076] In the present application, the operation actions in the PostgreSQL database are monitored, and operation information of the operation actions is obtained; wherein the operation actions include operation actions on data manipulation language, operation actions of deleting tables based on data definition language, and operation actions on system tables of the PostgreSQL database; corresponding preset record tables are created based on the operation information; when there is a misoperation in the PostgreSQL database, the contents in the preset record tables are filtered and queried to determine the data information of the misoperation; and the PostgreSQL database is recovered according to the data information. It can be seen that, for the operation actions on the data manipulation language and the operation actions on the system tables of the PG database, the recovery degree of the PG is improved based on the records of the record tables; for the operation actions of deleting tables based on the data definition language, the recovery speed and efficiency are improved based on the recovery of the record tables; and for different misoperations, the PG database recovery function is realized, and when the record tables are queried and recovered after the misoperation, the service does not need to be stopped, the fault tolerance and recoverability of the PG service are improved. At the same time, all misoperations since the misoperation function is started can be recovered, and the recoverability of the database is not limited, and the misoperation function of a certain table can be flexibly started. In this way, by recording, querying and recovering different operation actions, the misoperation recovery of the data manipulation language is realized, the misoperation recovery based on the deletion of tables based on the data definition language is realized, and the misoperation recovery of the system table operation is realized.

[0077] The embodiments of the present application disclose a specific misoperation recovery method based on a PostgreSQL database, as shown in Figure 2As shown, the DML module is specifically described, the DML module captures the user DML operation based on the trigger, and saves the user DML operation and related information into a record table corresponding to the DML operation table, the record table and the DML operation table are in the same mode, and the DML operation recovery is realized through the information of the record table; the method comprises:

[0078] Step S21: acquiring an operation table of the data manipulation language.

[0079] In the embodiment of the application, the DML such as INSERT, UPDATE, DELETE and TRUNCATE can be recovered without stopping service. First, a table of starting DML operation function is acquired, such as public.test.

[0080] Step S22: judging whether the corresponding first record table exists in the operation table, if yes, deleting the first record table and re-creating a second record table corresponding to the operation table, if not, directly creating the second record table corresponding to the operation table.

[0081] In the embodiment of the application, after the table of starting DML operation function public.test is acquired, it is judged whether the record table corresponding to public.test exists, the record table and the operation table are in the same database, and the naming format is <database>The undo_ operation table, for example, has a corresponding first record table, which is public.undo_test. It can be understood that the operation table can have a corresponding record table or not. If the corresponding first record table exists, the record table corresponding to the operation table is deleted, and then a second record table corresponding to the operation table is created based on the operation table.

[0082] For example, the fields of the second record table are shown in Table 1:

[0083] Table 1

[0084]

[0085]

[0086] Where id is an auto-increment sequence and is set as a primary key; xid is a transaction number; table_schema is a schema name; table_name is a table name; oper is a command; encoding is an encoding format; old_rect is original data; new_rect is new data; crt_time is a timestamp; and user_name is a user name.

[0087] Step S23: determining whether the first trigger function exists in the PostgreSQL database. If the first trigger function does not exist, the first trigger function is created. If the first trigger function exists, it is determined whether the first trigger that acts on the operation table exists.

[0088] In the embodiments of the present application, the second record table corresponds to the operation table one by one. When different operations are performed on the database, the trigger function is used to perform different operations on the second record table for different types of type commands. When the type command is captured, the trigger function is executed by the trigger after the type command acts on the operation table, thereby realizing the recording of the operation action and data.

[0089] In the embodiments of the present application, it is determined whether the first trigger function undo_t_trace exists under the library. In a specific implementation, if undo_t_trace does not exist, the trigger function undo_t_trace is created. The function is triggered by the trigger, and the operation action and data are recorded. The function performs different operations on the second record table public.undo_test for different commands such as INSERT, UPDATE, DELETE, and TRUNCATE, that is, the inserted content is different. For example, Figure 3 As shown, the specific implementation is as follows: the transaction number xid and the encoding format encoding are obtained through the txid_current() and pg_client_encoding() functions respectively; the built-in variables session_user, tg_table_schema, and tg_table_name are the username, schema name, and operation table name respectively. The built-in variables NEW and OLD of the trigger are the data operated on the operation table by the current operation and the original data in the operation table respectively, and different operations are performed according to the type of the built-in variable TG_OP of the trigger. If it is an INSERT operation, the old_rect in the record table is empty and the new_rect is NEW; if it is an UPDATE operation, the old_rect in the record table is OLD and the new_rect is NEW; if it is a DELETE operation, the old_rect in the record table is OLD and the new_rect record is empty; if it is a TRUNCATE operation, the old_rect in the record table is OLD and the new_rect record is empty.

[0090] In another specific embodiment, if the first trigger function exists, it is further judged whether a trigger for executing the trigger function exists, the trigger acting on the operation table and being used for capturing the type of command based on different levels and executing the first trigger function after the type of command acts on the operation table.

[0091] In the embodiment of the application, the first trigger can capture the INSERT, UPDATE, and DELETE commands based on the row level and can capture the TRUNCATE command based on the statement level. Therefore, it is judged whether the triggers trigger_ operation_table_1 and trigger_ operation_table_2 that act on the operation table exist. Exemplarily, it is judged whether trigger_test_1 and trigger_test_2 exist.

[0092] Step S24: If the first trigger exists, the first trigger is started and the first trigger function is executed, and then different operations are performed on the second record table according to the type of command to obtain a corresponding preset record table; if the first trigger does not exist, the first trigger is created, and then the step of starting the first trigger and executing the first trigger function is performed.

[0093] In a specific embodiment, if the first trigger exists, it is further required to determine whether the triggers trigger_ operation_table_1 and trigger_ operation_table_2 respectively acting on the operation table are enabled, and if not, the triggers respectively acting on the operation table are enabled, the first trigger function is executed by the first trigger, and then different operations are performed on the second record table according to the type command to obtain a corresponding preset record table.

[0094] In another specific embodiment, if the first trigger does not exist, triggers respectively acting on the operation table are created, such as trigger_test_1 and trigger_test_2. Trigger_test_1 is used to capture INSERT, UPDATE and DELETE commands based on row level, and the trigger function is executed after the above commands act on the operation table. Trigger_test_2 is used to capture TRUNCATE command based on statement level, and the trigger function is executed before the command acts on the operation table.

[0095] Step S25: When the PostgreSQL database has a misoperation, the content in the preset record table is queried to determine the data information of the misoperation.

[0096] In the embodiment of the application, when the PG database has a misoperation, the record in the preset record table is queried to locate the identifier xid of the misoperation. In a specific implementation, the start time and end time of the misoperation, the operation user, the database name, the schema name and the table name are obtained, and the default parameters of the operation user, the database name and the schema name are postgres, postgres and public respectively. Then, the specific table, i.e., the preset record table having the misoperation, is queried based on the filtering parameters, and the xid of the misoperation is obtained according to the query result.

[0097] Step S26: The PostgreSQL database is recovered according to the data information.

[0098] In the embodiment of the application, the recovery function of the DML module is to recover the operation of the record in the record table. Figure 4 As shown, in the implementation process, the xid obtained based on the query function can be used to filter and query the records in the preset record table corresponding to the operation table, to obtain all the records; the obtained records are traversed, and different recovery operations are performed based on the operation type. If the type command is INSERT, the rows in the operation table corresponding to the new_rect in the record table are filtered, and then a DELETE operation is performed; if the type command is UPDATA, the rows in the operation table corresponding to the new_rect in the record table are filtered and a DELETE operation is performed, and then the old_rect data in the record table is inserted into the operation table; if the type command is DELETE, the old_rect data in the record table is inserted into the corresponding operation table; if the type command is TRUNCATE, the old_rect data in the record table is inserted into the corresponding operation table.

[0099] It can be seen that, for the operation action of the data manipulation language, the records of the record table improve the recoverability of the PG; and for different misoperations, the recovery function of the PG database is realized, and when the record table is queried and recovered after the misoperation, the service does not need to be stopped, thereby improving the fault tolerance and recoverability of the PG service. At the same time, all misoperations since the misoperation function is started can be recovered, and the recoverability of the database is not limited, and the misoperation function can be flexibly started for a certain table. In this way, the misoperation recovery of the data manipulation language is realized by recording, querying and recovering different operation actions, the misoperation recovery based on the data definition language to delete a table is realized, and the misoperation recovery of the system table operation is realized.

[0100] The embodiment of the application discloses a specific misoperation recovery method based on a PostgreSQL database, as shown in Figure 5 As shown, the DDL module is specifically described. The DDL module captures and intercepts the DROP TABLE command of a user based on an event trigger, and rolls back the transaction by an exception exit mechanism, renames the DDL operation table, and moves the DDL operation table to the ddl_recycle mode of the library, and records the operation process in the trace table in the ddl_recycle mode, and the recovery of the DROP TABLE command is realized through the records in the trace table. The module monitors the data volume in the recycle mode in real time and can give a warning, and can safely clean up data, thereby avoiding the problem that the data in the ddl_recycle mode is too large to affect the performance of the database. The method comprises the following steps:

[0101] Step S31: obtaining the database name and user name corresponding to the deletion of a table based on a data definition language in the PostgreSQL database.

[0102] In the embodiments of the present application, the DML, such as DROP TABLE and the like, can be recovered without stopping service. First, the database name and the user name of the user starting the DDL error operation are obtained. For example, the database name ddl_db and the user name ddl_user of the user starting the DROPTABLE error operation are obtained.

[0103] Step S32: determining the target database of the deleted table according to the database name and the user name.

[0104] In the embodiments of the present application, the target database of the deleted table can be determined according to the database name and the user name.

[0105] Step S33: loading the first preset plug-in in the target database, and judging whether the second trigger function exists in the target database.

[0106] In the embodiments of the present application, it is judged whether the dblink (Database Link) plug-in exists under the target database. If not, the dblink plug-in is loaded. If the first preset plug-in has been loaded, it is judged whether the event trigger function, i.e., the second trigger function, exists under the target database.

[0107] It can be understood that when it is needed to access the data in another database table across a local database, the dblink of the remote database must be created in the local database. Through the dblink, the local database can access the data in the remote database table as if it is accessing the local database.

[0108] Step S34: if the second trigger function exists, executing the second trigger function through the second trigger, and creating the corresponding preset record table based on the archive mode table.

[0109] In a specific embodiment, if the second trigger function exists, it is further judged whether the second trigger for executing the second trigger function exists. If not, the event trigger based on sql_drop, such as event_tri_sql_drop, is created to execute the second trigger function. If the second trigger exists, it is judged whether the second trigger is started. If not, the second trigger is started to execute the second trigger function. If the second trigger is started, the second trigger function is executed.

[0110] In the embodiment of the present application, if the second trigger is enabled, a preset record table is created based on the archiving mode table. Specifically, it is determined whether the ddl_recycle mode (archiving mode) exists under the library. If the ddl_recycle mode does not exist, the ddl_recycle mode is created under the target database, and a record table trace is created. The table records the original belonging mode, name and other information of the deleted table. The fields of the trace table are shown in Table 2:

[0111] Table 2

[0112]

[0113]

[0114] Wherein id is a self-increment sequence, which is set as a primary key; user_name is a user name; nsp_name is a mode name; obj_name is an object name; new_nsp_name is a new mode name; new_obj_name is a new object name; crt_time is a current time stamp.

[0115] In the embodiment of the present application, after the preset record table is created based on the archiving mode table, a general user (non-super user) is authorized, and the create and usage permissions of ddl_recycle, the select and insert permissions of the trace record table, the execution permission of the dblink_connect_u function, and the trace_id_seq sequence select and usage permissions of ddl_recycle are granted respectively.

[0116] Step S35: If the second trigger function does not exist, the second trigger function is created, and then the step of executing the second trigger function through the second trigger is performed.

[0117] In another specific embodiment, if the second trigger function does not exist, the second trigger function is created, such as event_tri_sql_drop_func. The second trigger function is implemented as shown in Figure 6 All deleted objects are obtained through the pg_event_trigger_dropped_objects() function, and all deleted tables under the ddl_recycle mode are screened. The names and belonging modes of all screened tables are changed through dblink in the exception handling. The name is a MD5 encoding string based on a random number and a time stamp, and the belonging mode is changed to ddl_recyle. At the same time, the exception exits and the user's operation of deleting the table is rolled back.

[0118] Step S36: When there is a misoperation in the PostgreSQL database, the content in the preset record table is queried to determine the data information of the misoperation.

[0119] In the embodiment of the application, when there is a misoperation in the PG database, the record in the preset record table is queried to obtain the crt_time. In an example, in the specific implementation, the start time and the end time of the DDL misoperation, the operation user, and the database name are obtained, and the operation user and the database name are the default parameters postgres and postgres, respectively. Then, the records in the ddl_recycle.trace are queried based on the filtering parameters. According to the query result, the crt_time of the misoperation is obtained as the recovery parameter.

[0120] Step S37: The PostgreSQL database is recovered according to the data information.

[0121] In the embodiment of the application, the recovery function of the DDL module is to recover the deleted table in the preset record table. In the specific implementation process, the crt_time parameter obtained by the query step is used to filter all the records in the ddl_recycle.trace. The obtained records are traversed, and the corresponding relationship between new_nsp_name and nsp_name and new_obj_name and obj_name in the trace table is used to change the name and the belonging mode of the specific table in the ddl_recycle mode, respectively.

[0122] In the embodiment of the application, the DDL module can improve the stability of the PG and the sustainability of the misoperation recovery by monitoring the data of the ddl_recycle and automatically cleaning the data. When the data in the ddl_recycle mode is monitored, the data is automatically cleaned if the size exceeds a certain set value, so as to realize the space warning and automatic cleaning function.

[0123] In an example, first, the period of monitoring the size of the data in the ddl_recycle mode is set and the timing task is created. Then, the threshold shreshold (the percentage of the entire storage) and the cleaning step step_size (the number of tables cleaned at a time) of automatically cleaning the data are set. It is judged whether the current ddl_recycle occupancy rate is greater than the shreshold. If yes, the first step_size records in the ddl_recycle.trace table are sorted in ascending order based on the crt_time filtering. Further, all the obtained records are traversed, and the corresponding tables are deleted in turn until all the records are traversed.

[0124] It can be seen that the operation action of deleting a table based on a data definition language improves the speed and efficiency of recovery based on a record table. The recovery function of a PG database is realized for different misoperations, and the PG service fault tolerance and recoverability are improved without stopping the service when the record table is queried and recovered after the misoperation. Meanwhile, all misoperations since the misoperation function is started can be recovered, and the recoverability of the database is not limited. The misoperation function of a table can be flexibly started. In this way, the misoperation recovery of a data manipulation language is realized by recording, querying and recovering different operation actions, the misoperation recovery based on a data definition language is realized when a table is deleted, and the misoperation recovery of a system table operation is realized.

[0125] The embodiment of the application discloses a specific misoperation recovery method based on a PostgreSQL database, as shown in Figure 7 The system table module is specifically described. The system table module captures operation commands of a system table based on a hook, and saves related information such as user operations into a record table corresponding to the system table in a public mode. The misoperation recovery of the system table operation is realized through the information of the record table. The method comprises the following steps:

[0126] Step S41: A modification operation command is obtained based on a hook, and a system table name corresponding to the modification operation command is obtained.

[0127] In the embodiment of the application, the deletion and modification in the system table can be realized without stopping the service, such as the recovery of misoperations of DROP ROLE and ALTER ROLE commands acting on pg_authid. First, the system table name of the user starting the misoperation function of the system table module is obtained, such as pg_authid.

[0128] Step S42: Whether a second preset plug-in is loaded in the corresponding system table is determined according to the system table name.

[0129] In the embodiment of the application, whether the corresponding second preset plug-in, such as record_role_oper.so, is loaded in the PG service where the system table is located can be determined according to the name of the system table.

[0130] Step S43: If the second preset plug-in is loaded, a third record table corresponding to the system table in the public mode is recorded by using the second preset plug-in, so as to obtain the preset record table.

[0131] In a specific embodiment, if the second preset plug-in is loaded, it is further judged whether there is a record table corresponding to the action table one by one in the public mode, for example, public: undo_t_pg_authid, if not, the record table is created, and the third record table corresponding to the system table in the public mode is recorded by using the second preset plug-in to obtain the preset record table.

[0132] The fields of the preset record table add sequence type id (primary key), timestamp and the like on the basis of the system table corresponding to the system table name when the user opens the system table module misoperation function, for example, public: undo_t_pg_authid, the fields are shown in Table 3:

[0133] Table 3

[0134] column type id serial8 primary key oid oid rolname name rolsuper bool rolinherit bool rolcreaterole bool rolcreatedb bool rolcanlogin bool rolreplication bool rolbypassrls bool rolconnlimit int4 rolpassword text rolvaliduntil text username text command_tag text

[0135] Among them, id is an auto-increment serial number, which is set as a primary key; oid is a row unique identifier; rolname is a role name; rolsuper is a flag bit whether it is a super user; rolinherit is a flag bit whether it is inherited; rolcreaterole is a flag bit whether it can create a user; rolcreatedb is a flag bit whether it can create a database; rolcanlogin is a flag bit whether it can log in; olreplication is a flag bit whether it is a copy; rolbypassrls is a flag bit whether it can pass through a row-level security policy; rolconnlimit is the maximum number of connections of the user; rolpassword is the login password of the user; rolvaliduntil is the validity period of the password of the user; username is an operation user; commang_tag is a command tag.

[0136] Step S44: if the second preset plug-in is not loaded, the second preset plug-in is added in the configuration file based on the object permission type in the PostgreSQL database and the system table type identification of the system table, and then the third record table corresponding to the system table in the public mode is recorded by using the second preset plug-in to obtain the preset record table.

[0137] In another specific embodiment, if the second preset plug-in record_role_oper.so is not loaded, the second preset plug-in is added in share_preload_libraries in the configuration file postgresql.conf, which is based on the object authority type in the PG transaction, such as OAT_POST_CREATE, OAT_DROP, and OAT_POST_ALTER, which respectively represent the CREATE, DROP, and ALTER command types in the transaction; a specific action on a specific table where a misoperation exists is captured through the table type id, such as AuthIdRelationId, and the data in the third record table is recorded before the action is committed, so as to obtain the preset record table. It should be noted that the PG service needs to be restarted after the second preset plug-in is added.

[0138] Step S45: When the misoperation exists in the PostgreSQL database, the content in the preset record table is screened and queried to determine the data information of the misoperation.

[0139] In the embodiments of the present application, when the misoperation exists in the PG database, the records in the preset record table are queried, the records of a preset record table where a misoperation exists are screened and queried, and the unique identifier id of the corresponding misoperation line record is obtained. For example, in the specific implementation, the database, schema, system table name, user name, and operation time interval of the misoperation recovery are obtained; then the data of the record table is screened based on the above parameters, and the id of the misoperation record is obtained.

[0140] Step S46: The PostgreSQL database is recovered according to the data information.

[0141] In the embodiments of the present application, when the recovery function of the system table module is implemented, the id obtained by the query function is obtained; all records are obtained by screening and querying the preset record table based on the id of the misoperation record; all the obtained records are traversed, and different operations are performed according to the command type of each record, for example, if the misoperation type is DROP ROLE, the recovery is implemented through CREATE ROLE based on the corresponding information in the record; if the misoperation type is ALTER ROLE, the recovery is implemented through ALTER ROLE based on the corresponding information in the record table.

[0142] It can be seen that the operation action on the system table of the PG database improves the recoverability of the PG based on the record of the record table; and the recovery function of the PG database is realized for different misoperations, and the service does not need to be stopped when the record table is queried and recovered after the misoperation, which improves the fault tolerance and recoverability of the PG service. At the same time, all misoperations since the misoperation function is started can be recovered, and the recoverability of the database is not limited, and the misoperation function can be flexibly started for a certain table. In this way, the misoperation recovery of the data manipulation language is realized by recording, querying and recovering different operation actions, the misoperation recovery when the table is deleted based on the data definition language is realized, and the misoperation recovery of the system table operation is realized.

[0143] Correspondingly, the embodiment of the application further discloses a misoperation recovery device based on a PostgreSQL database, which refers to Figure 8 The device comprises:

[0144] An operation information acquisition module 11 is configured to monitor operation actions in the PostgreSQL database and acquire operation information of the operation actions; wherein the operation actions comprise operation actions on a data manipulation language, operation actions of deleting a table based on a data definition language, and operation actions on a system table of the PostgreSQL database.

[0145] A preset record table creation module 12 is configured to create corresponding preset record tables based on the operation information.

[0146] A screening and querying module 13 is configured to, when there is a misoperation in the PostgreSQL database, screen and query contents in the preset record tables to determine data information of the misoperation.

[0147] A recovery module 14 is configured to recover the PostgreSQL database according to the data information.

[0148] It can be seen that, by the above scheme of the embodiment, the operation action in the PostgreSQL database is monitored, and operation information of the operation action is acquired; wherein the operation action includes an operation action on a data manipulation language, an operation action of deleting a table based on a data definition language, and an operation action on a system table of the PostgreSQL database; a corresponding preset record table is created based on the operation information; when there is a misoperation in the PostgreSQL database, the content in the preset record table is queried and screened to determine data information of the misoperation; and the PostgreSQL database is recovered according to the data information. It can be seen that, for the operation action on the data manipulation language and the operation action on the system table of the PG database, the record of the record table improves the recoverable degree of the PG; for the operation action of deleting a table based on the data definition language, the recovery based on the record table improves the speed and efficiency of the recovery; and for different misoperations, the PG database recovery function is realized, and according to the record table, the service does not need to be stopped for query and recovery after the misoperation, which improves the fault tolerance and recoverability of the PG service. At the same time, all misoperations since the misoperation function is started can be recovered, and the recoverable degree of the database is not limited, and the misoperation function can be flexibly started for a certain table. In this way, by recording, querying and recovering different operation actions, the misoperation recovery of the data manipulation language is realized, the misoperation recovery based on the data definition language to delete a table is realized, and the misoperation recovery of the system table operation is realized.

[0149] Further, the embodiment of the application further discloses an electronic device, Figure 9 is an electronic device 20 structure diagram shown according to an exemplary embodiment, the contents in the figure cannot be considered as any limitation on the use range of the application.

[0150] Figure 9 A structure diagram of an electronic device 20 provided by the embodiment of the application is provided. The electronic device 20 can specifically include at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25 and a communication bus 26. Wherein, the memory 22 is used to store a computer program, the computer program is loaded and executed by the processor 21, to realize the related steps in the above-mentioned PostgreSQL database misoperation recovery method disclosed by any one of the preceding embodiments. In addition, the electronic device 20 in the embodiment can be a computer.

[0151] In this embodiment, the power supply 23 is configured to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 is configured to create a data transmission channel between the electronic device 20 and external devices, and the communication protocol followed by the communication interface 24 can be any communication protocol applicable to the technical solution of the present application, which will not be limited here; the input and output interface 25 is configured to obtain external input data or output data to the outside, and the specific interface type can be selected according to the specific application needs, which will not be limited here.

[0152] In addition, the memory 22 as a carrier of resource storage can be a read-only memory, a random access memory, a magnetic disk or an optical disk, etc., and the resources stored thereon can include an operating system 221, a computer program 222 and data 223, etc., and the data 223 can include various data. The storage mode can be temporary storage or permanent storage.

[0153] The operating system 221 is configured to manage and control each hardware device on the electronic device 20 and the computer program 222, and can be Windows Server, Netware, Unix, Linux, etc. In addition to the computer program capable of completing the PostgreSQL database error operation recovery method executed by the electronic device 20 disclosed in any of the preceding embodiments, the computer program 222 can further include a computer program capable of completing other specific work.

[0154] Further, the present application also discloses a computer readable storage medium, which includes a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a hard disk, a magnetic disk or an optical disk, or any other form of storage medium known in the technical field. The computer program is executed by the processor to implement the foregoing PostgreSQL database error operation recovery method. For the specific steps of the method, refer to the corresponding content disclosed in the foregoing embodiments, which will not be repeated here.

[0155] In the specification, each embodiment is described in a progressive manner, and each embodiment focuses on the difference from other embodiments. The same or similar parts between each embodiment can be referred to each other. For the device disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple, and the related parts can be referred to the method part.

[0156] The steps of the PostgreSQL database misoperation recovery or algorithm based on the embodiments disclosed herein can be implemented directly with hardware, software modules executed by a processor, or a combination of both. The software modules can be placed in random access memory (RAM), internal memory, read only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disks, removable disks, CD-ROMs, or any other form of storage medium known in the art.

[0157] Finally, it should be noted that, in this document, the terms such as first and second are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between such entities or operations. Moreover, the terms "comprise", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or inherent to such a process, method, article or device. Without more limitations, the element defined by the statement "comprises a" does not exclude the presence of additional identical elements in the process, method, article or device including the element.

[0158] The above describes in detail the PostgreSQL database misoperation recovery method, device, equipment and medium provided by the present application. The principles and implementation modes of the present application are described by applying specific examples. The above description of the embodiments is only used to help understand the method of the present application and its core idea. For those skilled in the art, according to the idea of the present application, the specific implementation mode and application range can be changed. In summary, the content of the specification should not be understood as a limitation of the present application.< / database>

Claims

1. A method for recovering from erroneous operations in a PostgreSQL database, characterized in that, include: Monitor the operations performed in the PostgreSQL database and obtain the operation information of the operations performed; wherein, the operations performed include operations performed on data manipulation language, operations performed to delete tables based on data definition language, and operations performed on system tables of the PostgreSQL database; Create a corresponding preset record table based on the operation information; When there is an erroneous operation in the PostgreSQL database, the contents of the preset record table are filtered and queried to determine the data information of the erroneous operation; Restore the PostgreSQL database based on the data information; The step of obtaining the operation information of the operation action includes: The modification operation command is obtained based on the HOOK, and the system table name corresponding to the modification operation command is obtained; Accordingly, the step of creating a corresponding preset record table based on the operation information includes: Determine whether the second preset plugin is loaded in the corresponding system table based on the system table name; If the second preset plugin is loaded, the second preset plugin is used to record the third record table corresponding to the system table in public mode to obtain the preset record table; If the second preset plugin is not loaded, the second preset plugin is added to the configuration file based on the object permission type in the PostgreSQL database and the system table type identifier of the system table. Then, the second preset plugin is used to record the third record table corresponding to the system table in public mode to obtain the preset record table. The step of using the second preset plugin to record the third record table corresponding to the system table in public mode to obtain the preset record table includes: Using the second preset plugin based on the object permission type in PG transactions, specific actions on specific tables where erroneous operations may occur are captured by the table type, and data in the third record table is recorded before the action is COMMIT, so as to obtain the preset record table.

2. The method for recovering from erroneous operations in a PostgreSQL database according to claim 1, characterized in that, The step of obtaining the operation information for the operation action includes: Obtain the operation table of the data manipulation language; Accordingly, the step of creating a corresponding preset record table based on the operation information includes: Determine whether the operation table already has a corresponding first record table. If so, delete the first record table and recreate the second record table corresponding to the operation table. Otherwise, directly create the second record table corresponding to the operation table. Determine whether a first trigger function exists in the PostgreSQL database. If the first trigger function does not exist, create it. If the first trigger function exists, determine whether a first trigger that acts on the operation table exists. The first trigger function is used to perform different operations on the second record table for different types of type commands. The first trigger is used to capture the type commands at different levels and execute the first trigger function after the type command acts on the operation table. If the first trigger exists, the first trigger is enabled and the first trigger function is executed. Then, different operations are performed on the second record table according to the type command to obtain the corresponding preset record table. If the first trigger does not exist, the first trigger is created and the steps of enabling the first trigger and executing the first trigger function are executed.

3. The method for recovering from erroneous operations in a PostgreSQL database according to claim 1, characterized in that, The step of obtaining the operation information for the operation action includes: Retrieve the database name and username corresponding to the deletion of a table in the PostgreSQL database based on the Data Definition Language; Accordingly, the step of creating a corresponding preset record table based on the operation information includes: The target database for deleting the table is determined based on the database name and the user name; Load the first preset plugin in the target database, and determine whether the second trigger function exists in the target database; If the second trigger function exists, the second trigger function is executed through the second trigger, and a corresponding preset record table is created based on the archive mode table; If the second trigger function does not exist, then the second trigger function is created, and then the step of executing the second trigger function through the second trigger is performed.

4. The method for recovering from erroneous operations in a PostgreSQL database according to claim 3, characterized in that, Also includes: The first preset plugin changes the name of the non-archived mode table and changes the non-archived mode table to an archived mode table based on the MD5 encoded string of the random number and timestamp.

5. The method for recovering from erroneous operations in a PostgreSQL database according to claim 1, characterized in that, The step of filtering and querying the contents of the preset record table to determine the data information of the erroneous operation includes: Determine the filtering parameters in the preset record table, and filter out the recovery parameters for erroneous operations in the preset record table based on the filtering parameters; Accordingly, the recovery of the PostgreSQL database based on the data information includes: Based on the recovery parameters, obtain all records of the erroneous operation and iterate through the records to perform different recovery operations on the records.

6. The method for recovering from erroneous operations in a PostgreSQL database according to any one of claims 1 to 5, characterized in that, Also includes: Monitor the data information in the PostgreSQL database and set the monitoring period for the data information to create a scheduled task; The scheduled task sets a preset percentage threshold and a preset cleaning step size for the data information when it is automatically cleaned up; wherein, the preset percentage threshold is the percentage of storage occupied by the data in the data information; and the preset cleaning step size is the number of tables to be cleaned in the data information. If the current data size occupies a percentage of the storage that is greater than the preset percentage threshold, then the records in the preset record table are sorted in ascending order based on the timestamp, and then the preset record table with the same number of preset cleanup steps is selected and deleted sequentially.

7. A PostgreSQL database erroneous operation recovery device, characterized in that, The apparatus is used to implement the PostgreSQL database erroneous operation recovery method as described in any one of claims 1 to 6, the apparatus comprising: The operation information acquisition module is used to monitor the operation actions in the PostgreSQL database and acquire the operation information of the operation actions; wherein, the operation actions include operation actions on the data manipulation language, operation actions to delete tables based on the data definition language, and operation actions on the system tables of the PostgreSQL database; The preset record table creation module is used to create a corresponding preset record table based on the operation information; The filtering and query module is used to filter and query the contents of the preset record table when there is an erroneous operation in the PostgreSQL database, so as to determine the data information of the erroneous operation; The recovery module is used to recover the PostgreSQL database based on the data information.

8. An electronic device, characterized in that, The electronic device includes a processor and a memory; wherein the memory is used to store a computer program, which is loaded and executed by the processor to implement the PostgreSQL database erroneous operation recovery method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, Used to store computer programs; wherein the computer programs, when executed by a processor, implement the PostgreSQL database erroneous operation recovery method as described in any one of claims 1 to 6.

Images