A database upgrade method

Abstract

The present invention provides a database upgrade method, which includes: placing an upgrade package on a data node to be upgraded to prepare for the upgrade; performing an upgrade on the database according to the upgrade preparation; verifying whether the database upgrade is successful, and deciding whether to submit the upgrade based on the verification result. According to the upgrade preparation, performing the upgrade on the database includes: finding a master node that can execute the upgrade SQL script; backing up the system tables and configuration files on each node; screening and assembling the scripts to be executed, and setting the master node to upgrade mode; updating the database according to the screened and assembled scripts to complete the upgrade. The database upgrade method of the present invention has a smaller scope, simpler steps, less resource occupation, and more complete execution results, and can effectively save development and operation and maintenance costs.

Description

Technical Field

[0001] The present invention relates to the technical field of database upgrading, and in particular to a database upgrading method. Background Art

[0002] Database upgrades are a common problem in database usage. As database versions update, users need to upgrade to the new version to gain access to new features. During database updates, to protect user data security and ensure the smooth operation of user services, old data must be migrated to the new version.

[0003] Traditional database upgrades involve a data import and export process. First, the old database's data is exported as an SQL script (or other format), a new database is created, and finally, the old database's data is imported into the new database to complete the upgrade. The import process involves executing the SQL script to rebuild the old database's data. In practice, this upgrade mechanism has the following shortcomings: 1) The upgrade process involves all database data, which is labor-intensive and resource-intensive; 2) The upgrade process is cumbersome, requiring developers or administrators to be very familiar with the upgrade process; and 3) Some database information, such as OIDs (object identifiers), is altered after exporting and importing.

[0004] Therefore, how to provide a database upgrade method that simplifies the upgrade process while reducing development and operation and maintenance costs has become a technical problem that needs to be solved urgently. Summary of the Invention

[0005] In view of this, in order to solve the shortcomings of traditional database upgrades, the present invention provides a database upgrade method.

[0006] In one aspect, the present invention provides a database upgrade method, comprising:

[0007] Step S1: Put the upgrade package on the data node to be upgraded to prepare for the upgrade;

[0008] Step S2: According to the upgrade preparation, the database is upgraded;

[0009] Step S3: Verify whether the database upgrade is successful, and decide whether to submit the upgrade based on the verification result.

[0010] Furthermore, the database upgrade method of the present invention further includes: after the database upgrade is successful, all the information in the database can be restored to the old database state through manual rollback.

[0011] Furthermore, in step S2, according to the upgrade preparation, the database is upgraded, including:

[0012] Step S201: Find a master node that can execute the upgrade SQL script;

[0013] Step S202: Back up the system tables and configuration files on each node;

[0014] Step S203: Filter and collect the scripts to be executed, and set the master node to upgrade mode;

[0015] Step S204: Update the database based on the screened and collected scripts to complete the execution upgrade.

[0016] Furthermore, in step S201, when the database is deployed as a single node, the single node itself is used as the master node; when the database is deployed as highly available, the master database is used as the master node.

[0017] Furthermore, in step S202, the system tables and configuration files on each node are backed up, including: on each node, traversing all databases and backing up the system tables stored therein; and backing up the configuration files that depend on each node.

[0018] Furthermore, in step S203, the scripts to be executed are screened and collected, including:

[0019] Use the kernel version number to manage database kernel upgrades. When a function update involves changes to the system table structure and content, write a corresponding script for the function. The script includes an upgrade script and a rollback script. The script name contains the kernel version number. Filter the scripts to be executed based on the kernel version numbers of the new and old databases.

[0020] The contents of the upgrade scripts are collected into a SQL file in ascending order of kernel version numbers; the contents of the rollback scripts are collected into a SQL file in descending order of kernel version numbers, and the SQL files are wrapped separately with transaction blocks to ensure the atomicity of execution.

[0021] Furthermore, in step S204, the database is updated based on the screened and collected scripts to complete the execution upgrade, including:

[0022] Execute the pre-rollback script and pre-upgrade script for the database in sequence. The pre-rollback script and pre-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, while the otherdb script is executed in all databases except the postgres database.

[0023] Manage binary files through soft links, point the soft links to the binary files of the new version of the database, update the database binary files, and restart the database management system;

[0024] Execute the post-rollback script and post-upgrade script for the database in sequence. The post-rollback script and post-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, and the otherdb script is executed in all databases except the postgres database.

[0025] Furthermore, in step S2, the database is upgraded according to the upgrade preparation. If an error occurs during the database upgrade and the upgrade cannot be performed, the database can be automatically rolled back to the state before the upgrade based on the upgrade process and backup information. Specifically, the steps include:

[0026] When the rollback script is successfully executed, the system table recovery is performed only on the master database;

[0027] When the rollback script fails to execute, the system tables are restored on each node by overwriting the physical files backed up on each node; the configuration information that depends on each node is restored on each node by overwriting the physical files backed up on each node.

[0028] On the other hand, the present invention further provides a computer-readable storage medium having a computer program stored thereon, wherein the program implements the steps of the method when executed by a processor.

[0029] Finally, the present invention also provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the method when executing the program.

[0030] Compared with traditional upgrade methods, the database upgrade method of the present invention involves a smaller scope, has simpler steps, occupies fewer resources, and has more complete execution results, and can effectively save development and operation and maintenance costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0032] Figure 1 FIG. 1 is a flowchart of a database upgrading method according to an exemplary first embodiment of the present invention.

[0033] Figure 2 FIG. 4 is a flowchart of an execution method for upgrading a database according to a first exemplary embodiment of the present invention.

[0034] Figure 3 FIG. 1 is a flowchart of a method for performing an upgrade in a database upgrade method according to a second exemplary embodiment of the present invention.

[0035] Figure 4 FIG. 4 is a flowchart of executing an upgrade in a database upgrade method according to a second exemplary embodiment of the present invention. DETAILED DESCRIPTION

[0036] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0037] It should be noted that, in the absence of conflict, the following embodiments and features in the embodiments may be combined with each other; and, based on the embodiments in this disclosure, all other embodiments obtained by persons of ordinary skill in the art without creative work are within the scope of protection of this disclosure.

[0038] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein can be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on this disclosure, it should be understood by those skilled in the art that an aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects described herein can be used to implement an apparatus and / or practice a method. In addition, other structures and / or functionalities other than one or more of the aspects described herein can be used to implement this apparatus and / or practice this method.

[0039] The theoretical technology of the present invention is briefly described as follows:

[0040] Database upgrade methods can be divided into two categories: the first, which can be achieved by directly replacing the database binary files; the second, which requires additional modifications to the database data. The reason for modifying database data is that changes to the structure and content of system tables in the new version of the database create incompatibilities between the old and new versions, making the upgrade impossible by simply replacing the database binary files. From the perspective of optimizing the upgrade process, upgrading databases that require data modifications through in-place upgrades is a key approach to simplifying upgrades and reducing development and maintenance costs.

[0041] OpenGauss supports the upgrade mode switch upgrade_mode, which allows you to modify system tables. In this mode, you can not only modify the structure and content of system tables using SQL statements, but also specify the OIDs used for objects created using SQL statements. This allows you to upgrade new features introduced in a new version of the database by replacing binary files and executing SQL scripts based on the old version.

[0042] The glossary of the present invention is explained as follows:

[0043] In-place upgrade: Compared with the traditional import and export method for database upgrade, in-place upgrade does not require importing or exporting data, and the upgrade operation is performed directly on the original data.

[0044] OpenGauss: An open source relational database with a core derived from PostgreSQL, released under the Magnolia permissive license agreement, with the characteristics of high performance, high availability, high security, easy operation and maintenance, and full openness. It is aimed at transactional applications with high concurrency and large data volumes, mainly based on online transaction processing, as well as IoT data application scenarios with many sensor monitoring devices, high sampling rates, data storage as an append-only model, and equal emphasis on operation and analysis.

[0045] SQL: Structured Query Language (SQL) is a high-level, non-procedural database query and programming language with data definition, data manipulation, and data control functions. It is used to access data and query, update, and manage relational database systems.

[0046] OID: Object Identifier (OID) is a globally unique value associated with an object that unambiguously identifies it. This ensures that the object is correctly located and managed during communication and information processing. In layman's terms, OID is the identification card of an object in network communications.

[0047] Kernel version number: The format is {kernel major version number}.{kernel minor version number}, which is used to indicate changes in the structure and content of system tables and increases monotonically.

[0048] Scripts: There are upgrade scripts and rollback scripts involved. The two types of scripts are described as follows:

[0049] Table 1

[0050]

[0051] It should be noted that pre-scripts are scripts that need to be executed before upgrading to the new database binary version, while post-scripts are scripts that need to be executed after upgrading to the new database binary version. Script names consist of a prefix and a suffix. The script prefix is ​​shown in Table 1. The script suffix format is: {kernel major version number}_{kernel minor version number}.sql. When upgrading system tables that are shared between databases, only write the upgrade SQL / rollback SQL in the maindb script (in OpenGauss, maindb is the postgres database, and all other databases are called otherdb). When upgrading system tables that are not shared between databases, write the upgrade SQL / rollback SQL in both maindb and otherdb.

[0052] Primary database and backup database: When high availability is used, there is only one primary database and multiple backup databases. The primary database is used for customer business, and the backup database is used to back up data for the primary database. When high availability is not used (single node), there is only a primary database and no backup database.

[0053] Figure 1 FIG. 1 is a flowchart of a method for upgrading a database according to a first exemplary embodiment of the present invention. Figure 1 As shown, the database upgrade method in this embodiment includes:

[0054] Step S1: Put the upgrade package on the data node to be upgraded to prepare for the upgrade;

[0055] Step S2: According to the upgrade preparation, the database is upgraded;

[0056] Step S3: Verify whether the database upgrade is successful, and decide whether to submit the upgrade based on the verification result.

[0057] Figure 2 FIG. 1 is a flowchart of a database upgrade method according to an exemplary first embodiment of the present invention. Figure 2 for Figure 1 The preferred implementation of the database upgrade method shown in FIG. Figure 2 It can be seen that the database upgrade method in this exemplary embodiment, when the database upgrade is successful, the upgrade is submitted, and automatic rollback is not supported at this time; when the database upgrade is unsuccessful, the version is rolled back; after the database upgrade is successful, all database information can be restored to the old database state through manual rollback.

[0058] Figure 3 FIG. 4 is a flowchart of a database upgrading method according to an exemplary second embodiment of the present invention. Figure 3 for Figure 1 The preferred implementation of the database upgrade method shown in FIG. Figure 3 As shown, the database upgrade method of this embodiment performs an upgrade on the database, including:

[0059] Step S201: Find a master node that can execute the upgrade SQL script;

[0060] Step S202: Back up the system tables and configuration files on each node;

[0061] Step S203: Filter and collect the scripts to be executed, and set the master node to upgrade mode;

[0062] Step S204: Update the database based on the screened and collected scripts to complete the execution upgrade;

[0063] Specifically, in step S201, when the database is deployed as a single node, the single node itself is used as the master node; when the database is deployed as highly available (i.e., there is a master database / slave database / cascaded slave database), the master database is used as the master node.

[0064] In step S202, backing up the system tables and configuration files on each node includes: traversing all databases on each node and backing up the system tables stored therein; and backing up the configuration files that depend on each node.

[0065] In step S203, the scripts to be executed are screened and collected, including:

[0066] To facilitate upgrade management, the database kernel is upgraded using the kernel version number. When a function update involves changes to the system table structure and content, a corresponding script is written for the function. The script includes an upgrade script and a rollback script. The script name contains the kernel version number. During the upgrade, the script to be executed is filtered based on the kernel version numbers of the new and old databases.

[0067] The contents of the upgrade scripts are collected into a SQL file in ascending order of kernel version numbers; the contents of the rollback scripts are collected into a SQL file in descending order of kernel version numbers, and the SQL files are wrapped separately with transaction blocks to ensure the atomicity of execution.

[0068] In step S204, the database is updated based on the screened and collected scripts to complete the execution upgrade, including:

[0069] Execute the pre-rollback script and pre-upgrade script for the database in sequence. The pre-rollback script and pre-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, while the otherdb script is executed in all databases except the postgres database.

[0070] Manage binary files through soft links, point the soft links to the binary files of the new version of the database, update the database binary files, and restart the database management system;

[0071] Execute the post-rollback script and post-upgrade script for the database in sequence. The post-rollback script and post-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, and the otherdb script is executed in all databases except the postgres database.

[0072] In step S2, the database is upgraded according to the upgrade preparation. If an error occurs during the database upgrade and the upgrade cannot be continued, the database can be automatically rolled back to the state before the upgrade based on the upgrade process and backup information. Specifically, the following steps are performed:

[0073] When the rollback script is successfully executed, the system table recovery is performed only on the master database;

[0074] If the rollback script fails, the system tables are restored on each node by overwriting the physical files backed up on each node. Configuration information dependent on that node is also restored by overwriting the physical files backed up on each node. Rollback primarily relies on an in-place backup of database data, including system tables and configuration information. Configuration information includes postgresql.conf and pg_hba.conf; not all content needs to be backed up.

[0075] Figure 4 FIG. 1 is a flowchart of a database upgrade method according to an exemplary second embodiment of the present invention. Figure 3 The upgrade method in the database upgrade method shown is as follows Figure 4 The process shown is specifically executed.

[0076] The present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the method are implemented.

[0077] The present invention also provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the steps of the method are implemented when the processor executes the program.

[0078] The medium and computer device have the corresponding technical effects of the above-mentioned database upgrading method.

[0079] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in the present invention should be included in the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be based on the scope of protection of the claims.

Claims

1. A method for upgrading an openGauss database, characterized in that: The method comprises: Step S1: Put the upgrade package on the data node to be upgraded to prepare for the upgrade; Step S2: According to the upgrade preparation, the database is upgraded; Step S3: Verify whether the database upgrade is successful, and decide whether to submit the upgrade based on the verification result; In step S2, according to the upgrade preparation, the database is upgraded, including: Step S201: Find a master node that can execute the upgrade SQL script; Step S202: Back up the system tables and configuration files on each node; Step S203: Filter and collect the scripts to be executed, and set the master node to upgrade mode; Step S204: Update the database based on the screened and collected scripts to complete the execution upgrade; In step S203, the scripts to be executed are screened and collected, including: Use the kernel version number to manage database kernel upgrades. When a function update involves changes to the system table structure and content, write a corresponding script for the function. The script includes an upgrade script and a rollback script. The script name contains the kernel version number. Filter the scripts to be executed based on the kernel version numbers of the new and old databases. The upgrade scripts are compiled into a SQL file in ascending order of kernel version numbers; the rollback scripts are compiled into a SQL file in ascending order of kernel version numbers, and the SQL files are wrapped with transaction blocks to ensure atomicity of execution. In step S204, the database is updated based on the screened and collected scripts to complete the execution upgrade, including: Execute the pre-rollback script and pre-upgrade script for the database in sequence. The pre-rollback script and pre-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, while the otherdb script is executed in all databases except the postgres database. Manage binary files through soft links, point the soft links to the binary files of the new version of the database, update the database binary files, and restart the database management system; Execute the post-rollback script and post-upgrade script for the database in sequence. The post-rollback script and post-upgrade script each contain the maindb script and otherdb script. The maindb script is executed only in the postgres database, and the otherdb script is executed in all databases except the postgres database.

2. The openGauss database upgrade method according to claim 1, characterized in that: The method further comprises: after the database upgrade is successful, restoring all the information in the database to the old database state through manual rollback.

3. The openGauss database upgrade method according to claim 1, characterized in that: In step S201, when the database is deployed as a single node, the single node itself is used as the master node; when the database is deployed as highly available, the master database is used as the master node.

4. The openGauss database upgrade method according to claim 3, characterized in that: In step S202, the system tables and configuration files on each node are backed up, including: on each node, traversing all databases and backing up the system tables stored therein; and backing up the configuration files that depend on each node.

5. The openGauss database upgrade method according to claim 1, characterized in that: In step S2, the database is upgraded according to the upgrade preparation. If an error occurs during the database upgrade and the upgrade cannot be performed, the database is automatically rolled back to the state before the upgrade based on the upgrade process and backup information. Specifically, the following steps are performed: When the rollback script is successfully executed, the system table recovery is performed only on the master database; When the rollback script fails to execute, the system tables are restored on each node by overwriting the physical files backed up on each node; the configuration information that depends on each node is restored on each node by overwriting the physical files backed up on each node.

6. A computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements the steps of the method according to any one of claims 1 to 5.

7. A computer device, characterized in that: The method comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the steps of the method according to any one of claims 1 to 5 are implemented when the processor executes the program.

Images