Method for improving efficiency of database physical backup

Abstract

The application provides a method for improving the efficiency of database physical backup, which provides a user with an add-non-backup-object command and a delete-non-backup-object command, and sets a non-backup table in a database system file, which is used for recording the type, name and path of the non-backup object. When performing a database backup operation, in the data file compression backup process, according to the information in the non-backup table, the non-backup object is excluded, and the content recorded in the non-backup table is exported as a txt file and stored in a backup package. When performing a restoration operation, according to the non-backup table content in the txt file in the backup package, a corresponding empty folder or empty file is generated under the corresponding path, so that the database can normally access the non-backup object after starting, and the content about the non-backup object in the xlog file is cleared, so that the database can normally perform a redo operation after starting. The application can greatly improve the efficiency of database physical backup.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of computer, further belongs to the technical field of distributed database, and more particularly relates to a method for improving the efficiency of database physical backup. BACKGROUND

[0002] A large amount of data is stored in a database, and some databases are as large as TB level. In order to ensure the data recovery capability of the database at the time of failure, daily backup is needed to retain backup files for one week or more. However, for the backup of a database of TB level or more, a large amount of disk space is consumed to store the backup package.

[0003] Database backup includes two methods of physical backup and logical backup. Logical backup is to export the database content as sql commands, and to import the sql commands into the database for execution at the time of recovery, so as to realize data recovery. This method is relatively flexible, but the backup and recovery time is long and the efficiency is low. Physical backup relies on data files and xlog logs for backup and recovery, and the time consumption is short and the efficiency is high.

[0004] The present application mainly optimizes the physical backup of PG series databases to improve the physical backup and recovery efficiency of PG series databases. The information saved in the actual database includes two types: basic configuration information and system information. The basic configuration information is necessary and cannot be lost; however, the system information saves some statistical information of system operation, which is convenient for system operation and maintenance, and the data volume of this type of information is usually large. If these tables are not backed up according to the user's requirements during physical backup, the backup and recovery efficiency of the database can be greatly improved, and the disk space occupied by the backup package can be saved. SUMMARY

[0005] The present application aims at solving the problems in the prior art, and provides a method for improving the efficiency of database physical backup, which aims at improving the efficiency of database physical backup and saving the disk space occupied by the backup package.

[0006] The present application is realized by the following technical solutions:

[0007] A method for improving the efficiency of database physical backup, which comprises:

[0008] The user is provided with an add-non-backup-object command and a delete-non-backup-object command; in the add-non-backup-object command and the delete-non-backup-object command, a first control parameter and a second control parameter for user input are set, the first control parameter is used to fill the type of non-backup object, and the second control parameter is used to fill the name of non-backup object;

[0009] Setting a non-backup table in a database system file for recording the type, name, path and oid information of non-backup objects; when executing an add non-backup object command, the path and oid information of the non-backup object are obtained according to the type and name of the non-backup object in the command and recorded in the non-backup table; when executing a delete non-backup object command, the relevant information of the non-backup object to be deleted is deleted from the non-backup table;

[0010] When performing a database backup operation: in a data file compression backup process, according to the information recorded in the non-backup table, the non-backup objects are excluded; the contents recorded in the non-backup table are exported as a txt file; the exported txt file, data compression backup file and xlog compression backup file are packaged as a backup package;

[0011] When performing a restore operation: the data compression backup file and the xlog compression backup file in the backup package are decompressed; according to the non-backup table contents in the txt file in the backup package, corresponding empty folders or empty files are generated under the corresponding paths, so that the database can normally access the non-backup objects after starting, and the contents about the non-backup objects in the xlog file are cleared, so that the database can normally perform redo operation after starting.

[0012] In the above technical solution, the type of the non-backup object of the first control parameter includes: database, table space, table file.

[0013] In the above technical solution, for the second control parameter, if the non-backup object is a database, the name of the non-backup database is directly filled; if the non-backup object is a table space, the name combined by the database name where the non-backup table space is located and the non-backup table space name is filled; if the non-backup object is a table file, the name combined by the database name where the non-backup table file is located, the table space name where the non-backup table file is located and the non-backup table file name is filled.

[0014] In the above technical solution, if the type of the non-backup object is a database, the type, name and database destination path of the database object are recorded in the non-backup table; if the type of the non-backup object is a table space, the type, name and table space path of the table space object are recorded in the non-backup table; if the type of the non-backup object is a table file, the type, name and table file path of the table file object are recorded in the non-backup table, and the indexes and toast tables attached to the table file object are also recorded in the non-backup table.

[0015] In the above technical solution, after the xlog file and the data file are restored, the database is started, and then the truncate operation is performed on the non-backup table to tell the database system that the current non-backup table content is empty.

[0016] Another aspect of the present application provides an electronic device, comprising: one or more processors; a memory storing one or more programs, wherein the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the method as described above.

[0017] Another aspect of the present application provides a computer-readable storage medium storing computer-executable instructions which, when executed, perform the method as described above.

[0018] Another aspect of the present application provides a computer program product comprising computer-executable instructions which, when executed, perform the method as described above.

[0019] Advantages and beneficial effects of the present application are:

[0020] The present application provides a user with an add-non-backup-object command and a delete-non-backup-object command, can control non-backup objects according to the user's requirements, can greatly improve the efficiency of database physical backup, save backup disk space, and provide a feasible method for reserving multiple backup packages for a database of TB level or above. BRIEF DESCRIPTION OF DRAWINGS

[0021] Figure 1 The application scenario diagram of the method for improving the efficiency of database physical backup of the present application.

[0022] Figure 2 The flowchart of the method for improving the efficiency of database physical backup of the present application when performing a database backup operation.

[0023] Figure 3 The flowchart of the method for improving the efficiency of database physical backup of the present application when performing a restore operation.

[0024] For those skilled in the art, other related drawings can be obtained according to the above drawings without creative labor. DETAILED DESCRIPTION

[0025] Hereinafter, embodiments of the present application will be described with reference to the accompanying drawings. It should be understood, however, that the description is merely exemplary and is not intended to limit the scope of the present application. In the following detailed description of the embodiments of the present application, numerous specific details are set forth in order to provide a thorough understanding of the embodiments of the present application. However, it would be apparent to those skilled in the art that the embodiments of the present application can be practiced without these specific details. In other instances, well-known structures and techniques have been not described in detail in order to avoid obscuring aspects of the present application.

[0026] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. As used herein, the terms "comprises", "comprising", "includes", "including" and the like are specifically intended to be open-ended and to mean that other features, steps, operations, and / or components can be added.

[0027] All terms used herein including technical and scientific terms have the meanings commonly understood by one of ordinary skill in the art unless otherwise defined herein. It should be noted that the terms used herein are defined as having meanings that are consistent with the context of the specification in which they are used, and should not be interpreted in an overly idealized or overly formal way.

[0028] In the technical solutions of the present application, the user information (including but not limited to user personal information, user image information, user equipment information such as location information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved are all information and data authorized by the user or authorized by all parties, and the collection, storage, use, processing, transmission, provision, invention and application of related data comply with relevant laws, regulations and standards, necessary security measures are taken, do not violate public order and good customs, and corresponding operation portals are provided for users to choose authorization or refusal.

[0029] In the scenario of using personal information for automated decision-making, the method, device and system provided by the embodiments of the present application all provide corresponding operation portals for users to choose to agree or refuse the automated decision-making result; if the user chooses to refuse, the expert decision-making process is entered. The expression "automated decision-making" here refers to the activity of automatically analyzing, evaluating the behavior habits, interests and hobbies or economic, health, credit status of individuals, etc. by computer programs, and making decisions. The expression "expert decision-making" here refers to the activity of making decisions by personnel who are engaged in a certain field of work, have specialized experience, knowledge and skills, and have reached a certain professional level.

[0030] Figure 1 An application scenario diagram of the method for improving the efficiency of database physical backup according to the embodiments of the present application is schematically shown.

[0031] As shown in Figure 1 The application scenario 100 according to the embodiments can include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104 and a cluster server 105. The network 104 is used as a medium to provide communication links between the first terminal device 101, the second terminal device 102, the third terminal device 103 and the cluster server 105. The network 104 can include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0032] The user can use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the cluster server 105 through the network 104 to receive or send messages and the like. Various communication client applications can be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, and the like (only as examples).

[0033] The first terminal device 101, the second terminal device 102, and the third terminal device 103 can be various electronic devices with display screens and supporting web browsing, including but not limited to smartphones, tablet computers, laptop computers, desktop computers, and the like.

[0034] The cluster server 105 can be a server providing various services, such as a background management server supporting websites browsed by the user using the first terminal device 101, the second terminal device 102, and the third terminal device 103 (only as an example). A distributed database is deployed on the cluster server 105, and multiple server nodes included in the cluster server 105 can analyze and process received user requests and the like, and feed back the processing results (such as web pages, information, or data generated or obtained according to user requests) to the terminal devices.

[0035] It should be noted that the method for improving the efficiency of database physical backup provided by the embodiments of the present application can generally be executed by the cluster server 105. Alternatively, the method for improving the efficiency of database physical backup provided by the embodiments of the present application can also be executed by other servers or server clusters different from the cluster server 105 and capable of communicating with the first terminal device 101, the second terminal device 102, and the third terminal device 103 and / or the cluster server 105. Alternatively, the method for improving the efficiency of database physical backup provided by the embodiments of the present application can also be executed by the first terminal device 101, the second terminal device 102, or the third terminal device 103, or by other terminal devices different from the first terminal device 101, the second terminal device 102, or the third terminal device 103.

[0036] It should be understood that Figure 1 The number of terminal devices, networks, and servers in the above-mentioned system is only illustrative. According to the needs of implementation, there can be any number of terminal devices, networks, and servers.

[0037] It should be noted that the serial numbers of the various operations in the following methods are only used to represent the operations for description, and should not be regarded as representing the execution order of the various operations. Unless explicitly indicated, the method does not need to be executed in the order shown.

[0038] The following will be based onFigure 1 The described scenario, by Figure 2 and Figure 3 A method for improving the efficiency of database physical backup is described in detail.

[0039] A method for improving the efficiency of database physical backup, the method comprising the following.

[0040] I. Provide a user with an add non-backup object command and a delete non-backup object command.

[0041] In the add non-backup object command and the delete non-backup object command, a first control parameter and a second control parameter are provided for user input, the first control parameter is used to fill in the type of non-backup object, and the second control parameter is used to fill in the name of non-backup object.

[0042] For example, the add non-backup object command is: pg_add_nobackup_list(‘kind’, ‘name’); and the delete non-backup object command is: pg_del_nobackup_list(‘kind’, ‘name’).

[0043] The first control parameter kind is used to fill in the type of non-backup object, which can be filled with ‘database’, ‘table_space’, and ‘table’, respectively, indicating that the type of non-backup object is: database, table space, and table file.

[0044] The second control parameter name is used to fill in the name of non-backup object. If the non-backup object is a database, the name of the non-backup database is directly filled in; if the non-backup object is a table space, “xx.yy” is filled in, where xx represents the name of the database where the non-backup table space is located, and yy represents the non-backup table space; if the non-backup object is a table file, “xx.yy.zz” is filled in, where xx represents the name of the database where the non-backup table file is located, yy represents the name of the table space where the non-backup table file is located, and zz represents the name of the non-backup table file.

[0045] II. Set a non-backup table (pg_no_backup table) in the database system file, which is used to record the type, name, path, and oid information of non-backup object.

[0046] When the command pg_add_nobackup_list(‘kind’, ‘name’) of adding non-backup objects is executed, the path and oid of the non-backup object related to the type kind and name name in the command are obtained and recorded in the non-backup table. Specifically, if the type of the non-backup object is a database, the type, name and database destination path of the database object are recorded in the non-backup table; if the type of the non-backup object is a table space, the type, name and path of the table space object are recorded in the non-backup table; if the type of the non-backup object is a table file, the type, name and path of the table file object are recorded in the non-backup table, and the indexes and toast tables attached to the table file object are also recorded in the non-backup table.

[0047] When the command pg_del_nobackup_list(‘kind’, ‘name’) of deleting non-backup objects is executed, the related information of the non-backup object to be deleted is deleted from the non-backup table according to the type kind and name name of the non-backup object to be deleted in the command.

[0048] III. When the database backup operation is executed, refer to the attached Figure 2 , and the following operations are performed.

[0049] 1. The data file is compressed and backed up, and during the data file compression and backup process, the non-backup objects are excluded according to the information recorded in the non-backup table.

[0050] 2. After the data file compression and backup is completed, the xlog file (the xlog file refers to various transaction log files of the database system) is compressed and backed up.

[0051] 3. The contents recorded in the non-backup table are exported as a txt file.

[0052] 4. The exported txt file, the data compression backup file and the xlog compression backup file are packaged into a backup package.

[0053] IV. When the restoration operation is executed, refer to the attached Figure 3 , and the following operations are performed.

[0054] 1. The data compression backup file and the xlog compression backup file in the backup package are decompressed.

[0055] 2. The non-backup table contents in the txt file in the backup package are generated in the corresponding path to generate corresponding empty folders or empty files, so that the non-backup objects can be normally accessed after the database is started.

[0056] 3, according to the content of the non-backup table in the txt file, the content of the xlog file about the non-backup object is cleared, and it is ensured that the redo operation is normal after the database is started.

[0057] 4, after the xlog file and the data file are recovered, the database is started, and then the truncate operation (i.e., the quick erasing of all data in the non-backup table) is performed on the non-backup table, so as to tell the database system that the content of the non-backup table is empty.

[0058] The application further provides a computer readable storage medium, which carries one or more programs, and when the one or more programs are executed, the method according to the embodiment of the application is realized.

[0059] According to the embodiment of the application, the computer readable storage medium can be a non-volatile computer readable storage medium. For example, it can include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the application, the computer readable storage medium can be any tangible medium containing or storing a program, which can be used by or in combination with an instruction execution system, device or apparatus.

[0060] The embodiment of the application also includes a computer program product, which includes a computer program containing program codes for executing the method provided by the embodiment of the application, and when the computer program product is run on an electronic device, the program codes are used to make the electronic device realize the method for improving the physical backup efficiency of the database provided by the embodiment of the application.

[0061] In one embodiment, the computer program can rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program can also be transmitted, distributed in the form of signals on a network medium, and downloaded and installed through a communication part, and / or installed from a detachable medium. The program codes contained in the computer program can be transmitted by any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0062] According to embodiments of the present application, program code for implementing the computer programs provided by embodiments of the present application can be written in any combination of one or more programming languages, and can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. The programming language includes, but is not limited to, such languages as Java, C++, python, "C" language, or the like. The program code can execute entirely on the user's computing device, partly on the user's device, as a stand-alone software package, partly on the remote computing device, or entirely on the remote computing device or server. In the latter scenario, the remote computing device can be connected to the user's computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computing device, such as through the Internet using an Internet Service Provider.

[0063] The flow diagrams and block diagrams in the drawings are illustrations of architectures, functionalities, and operations of possible implementations of systems, methods, and computer program products according to various embodiments of the present application. In this regard, each block in the flow diagrams or block diagrams can represent a module, a procedure, or a portion of code, which comprises one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or in the reverse order, depending on the functionality involved. It is also noted that each block of the block diagrams or flow diagrams, and combinations thereof, can be implemented by dedicated hardware-based systems which perform the specified functions or operations, or by combinations of dedicated hardware and computer instructions.

[0064] Embodiments of the present application have been described above. However, these embodiments are merely meant to be illustrative and not limiting of the scope of the present application. Although each of the embodiments has been described separately, this does not mean that the measures in the individual embodiments cannot be used advantageously in combination. Numerous alternatives and modifications of embodiments of the present application can be made by those skilled in the art without departing from the scope of the present application, and all such alternatives and modifications are to be included within the scope of the present application.

Claims

1. A method for improving the efficiency of physical backup of a database, characterized in that, The method includes: Provide users with commands to add or delete objects that are not backed up; both commands have a first control parameter and a second control parameter for user input. The first control parameter is used to fill in the type of the object that is not backed up, and the second control parameter is used to fill in the name of the object that is not backed up. A non-backup table is set up in the database system file to record the type, name, path, and OID information of non-backup objects; when the command to add a non-backup object is executed, the path and OID information of the non-backup object are obtained according to the type and name of the non-backup object in the command and recorded in the non-backup table; when the command to delete a non-backup object is executed, the relevant information of the non-backup object to be deleted is deleted from the non-backup table. When performing a database backup operation: During the data file compression backup process, objects that should not be backed up are excluded based on the information recorded in the tables that should not be backed up; the contents of the records in the tables that should not be backed up are exported as txt files; the exported txt files, the data compressed backup files, and the xlog compressed backup files are packaged into a backup package; When performing a restore operation: decompress the data compressed backup file and xlog compressed backup file in the backup package; based on the content of the tables not backed up in the txt file in the backup package, generate the corresponding empty folders or empty files in the corresponding paths so that the database can access the unbacked-up objects normally after startup, and clear the content of the unbacked-up objects in the xlog file to ensure that the redo operation can be executed normally after the database starts.

2. The method for improving the efficiency of database physical backup according to claim 1, characterized in that: The types of objects that are not backed up in the first control parameter include: databases, tablespaces, and table files.

3. The method for improving the efficiency of database physical backup according to claim 2, characterized in that: For the second control parameter, if the object not to be backed up is a database, then directly fill in the name of the database that will not be backed up.

4. The method for improving database physical backup efficiency according to claim 2, characterized in that: For the second control parameter, if the object not being backed up is a tablespace, then fill in a name that is a combination of the database name where the tablespace not being backed up is located and the name of the tablespace not being backed up.

5. The method for improving the efficiency of database physical backup according to claim 2, characterized in that: For the second control parameter, if the object not to be backed up is a table file, then fill in a name that is a combination of the database name where the table file is not backed up, the tablespace name where the table file is not backed up, and the name of the table file not backed up.

6. The method for improving the efficiency of database physical backup according to claim 2, characterized in that: If the type of the object not being backed up is a database, then record the type, name, and destination path of the database object in the "Not Backed Up" table; if the type of the object not being backed up is a tablespace, then record the type, name, and path of the tablespace object in the "Not Backed Up" table. If the type of the object not being backed up is a table file, then the type, name, and path of the table file object will be recorded in the table not being backed up. The index and toast table attached to the table file object will also be recorded in the table not being backed up.

7. The method for improving the efficiency of database physical backup according to claim 1, characterized in that: After restoring both the xlog file and the data file, start the database, and then perform a truncate operation on the tables that are not backed up, informing the database system that the contents of the tables that are not backed up are currently empty.

8. An electronic device, characterized in that, include: One or more processors; A memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the method for improving the efficiency of physical database backup as described in claim 1.

9. A computer-readable storage medium, characterized in that: It stores computer-executable instructions, which, when executed, are used to implement the method for improving the efficiency of database physical backup as described in claim 1.

10. A computer program product, characterized in that: The aforementioned computer program product includes computer-executable instructions that, when executed, implement the method for improving the efficiency of database physical backup as described in claim 1.

Images