Database master-slave switching method and device, electronic equipment and storage medium

Abstract

The invention relates to the technical field of databases, and provides a main and standby database switching method and device, electronic equipment and a storage medium. According to the method, initialization weights are set for a first node device and a second node device of a database respectively, and detection scripts are called in the first node device and the second node device in a preset period through a layer switching mechanism component respectively to detect the state of each node device. And reducing the weight value of the node equipment meeting the weight degradation condition by a third weight so as to carry out weight adjustment, then setting the node equipment with the maximum weight value after the weight adjustment as a database host, and associating the set database host with the virtual IP, so that a user can access a database through the virtual IP, and the user experience is improved. Therefore, the main and standby node devices of the database are automatically switched, and the main and standby switching efficiency of the database is improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a database master-slave switching method, device, electronic device and storage medium. Background Art

[0002] In the field of industrial automation, in order to ensure the reliability of business, usually all business applications will be deployed in dual machines to avoid single point failure. When a host node fails, the other node is required to automatically take over the business function to minimize the impact on production. As one of the core components in the software system, high availability is an indispensable capability for the database. As a very popular open source database, PostgreSQL database is also widely used in the field of industrial automation. For many small and medium-sized use scenarios, PostgreSQL database is usually deployed in dual-machine active-standby redundant mode.

[0003] However, the current master-slave configuration method for PostgreSQL databases is complex to implement and may have problems such as single point failure and "split brain" risks. Summary of the invention

[0004] In view of this, the embodiments of the present application provide a database master-slave switching method, device, electronic device and storage medium to solve the problem of low efficiency and low stability of database master-slave configuration in the prior art.

[0005] A first aspect of an embodiment of the present application provides a database master-slave switching method, including:

[0006] Set the initialization weight of the first node device of the database to the first weight, and the initialization weight of the second node device to the second weight, wherein the first weight is greater than the second weight;

[0007] In the first node device and the second node device, respectively, the layer switching mechanism component is used to call the detection script at a preset period to detect the status of each node device;

[0008] In response to determining that the state of the first node device meets the weight degradation condition, reducing the weight value of the first node device by a third weight, or in response to determining that the state of the second node device meets the weight degradation condition, reducing the weight value of the second node device by a third weight, the third weight being greater than the difference between the first weight and the second weight;

[0009] The node device with the largest weight value among the first node device and the second node device is determined to be the database host, and the database host is associated with a preset virtual Internet protocol IP so that the user can access the database through the virtual IP.

[0010] In some embodiments, in response to the target node device being a database host, the weight degradation conditions include: the target node device is unavailable; or the target node device is detected and determined to be in a database standby state; or the target node device and another node device are detected and determined to be both database hosts, and the target node device is not connected to a virtual IP; or the target node device communicates abnormally with the gateway; wherein the target node device is the first node device or the second node device.

[0011] In some embodiments, in response to the target node device being a backup database node device, the weight degradation conditions include: data stream replication of the target node device is abnormal; or detection determines that the first node device and the second node device are both database backup devices, and the target node device is not connected to the preset virtual IP; or communication between the target node device and the gateway is abnormal; wherein the target node device is the first node device or the second node device.

[0012] In some embodiments, after the layer switching mechanism component in the first node device and the second node device respectively calls the detection script in a preset period to detect the status of each node device, the method also includes: in response to the layer switching mechanism component in the database host node device calling the detection script to determine that the database is in a dual-host state, determining whether the database host node device is connected to the virtual IP; in response to determining that the database host node device is connected to the virtual IP, maintaining the weight value of the database host node device; in response to determining that the database host node device is not connected to the virtual IP, determining that the database host node device is a database standby machine, and reducing the weight value of the database host node device by a third weight; or in response to the layer switching mechanism component in the database standby node device calling the detection script to determine that the database is in a dual-standby state, determining whether the database standby node device is connected to the virtual IP; in response to determining that the database standby node device is connected to the virtual IP, determining that the database standby node device is a database host, and maintaining the weight value of the database standby node device; in response to determining that the database standby node device is not connected to the virtual IP, reducing the weight value of the database standby node device by a third weight.

[0013] In some embodiments, after the layer switching mechanism component in the first node device and the second node device respectively calls the detection script at a preset period to detect the status of each node device, the method further includes: in response to the layer switching mechanism component in the database standby node device calling the detection script to determine that a data stream replication anomaly occurs in the database standby node device, the detection script executes a data repair command to repair the data stream.

[0014] In some embodiments, after the layer switching mechanism component in the first node device and the second node device respectively calls the detection script in a preset period to detect the status of each node device, the method also includes: in response to the layer switching mechanism component in the database host node device calling the detection script to determine the communication anomaly between the database host node device and other devices, and detecting the communication status of the database host node device and the gateway; in response to determining the communication anomaly between the database host node device and the gateway, determining that the database host node device is a database standby machine, and reducing the weight value of the database host node device by a third weight, otherwise maintaining the weight of the database host node device; or in response to the layer switching mechanism component in the database standby node device calling the detection script to determine the communication anomaly between the database standby node device and other devices, and detecting the communication status of the database standby node device and the gateway; in response to determining the communication anomaly between the database standby node device and the gateway, reducing the weight value of the database standby node device by a third weight, otherwise maintaining the weight of the database standby node device.

[0015] In some embodiments, the method also includes: determining that the device with the smallest weight value between the first node device and the second node device is a database standby machine; wherein, the step of determining that the first node device or the second node device is a database host is to call a promotion script through a layer exchange mechanism component in the first node device or the second node device to determine the first node device or the second node device as a database host; the step of determining that the first node device or the second node device is a database standby machine is to call a demotion script through a layer exchange mechanism component in the first node device or the second node device to determine the first node device or the second node device as a database standby machine.

[0016] A second aspect of an embodiment of the present application provides a database master-slave switching device, including:

[0017] An initialization module is configured to set the initialization weight of the first node device of the database to a first weight, and the initialization weight of the second node device to a second weight, wherein the first weight is greater than the second weight;

[0018] A detection module is configured to call a detection script in a preset period in the first node device and the second node device respectively through a layer switching mechanism component to detect the status of each node device;

[0019] a weight adjustment module, configured to, in response to determining that the state of the first node device meets the weight degradation condition, reduce the weight value of the first node device by a third weight, or, in response to determining that the state of the second node device meets the weight degradation condition, reduce the weight value of the second node device by a third weight, wherein the third weight is greater than the difference between the first weight and the second weight;

[0020] The master-slave setting module is configured to determine the node device with the largest weight value among the first node device and the second node device as the database host, and associate the database host with a preset virtual Internet protocol IP so that users can access the database through the virtual IP.

[0021] According to a third aspect of an embodiment of the present application, an electronic device is provided, 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 above method when executing the computer program.

[0022] According to a fourth aspect of an embodiment of the present application, a computer-readable storage medium is provided, which stores a computer program, and when the computer program is executed by a processor, the steps of the above method are implemented.

[0023] Compared with the prior art, the embodiments of the present application have the following beneficial effects: the embodiments of the present application respectively set initialization weights for the first node device and the second node device of the database, and respectively call the detection script in the first node device and the second node device through the layer switching mechanism component at a preset period to detect the status of each node device, reduce the weight value of the node device that meets the weight degradation condition by a third weight to adjust the weight, and then set the node device with the largest weight value after the weight adjustment as the database host, and associate the set database host with the virtual IP so that the user can access the database through the virtual IP, thereby realizing automatic switching of the database master-slave node devices and improving the database master-slave switching efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0025] Figure 1 It is a flowchart of a database master-slave switching method provided in an embodiment of the present application.

[0026] Figure 2 It is a flowchart of another database master-slave switching method provided in an embodiment of the present application.

[0027] Figure 3 It is a flowchart of another database master-slave switching method provided in an embodiment of the present application.

[0028] Figure 4 It is a flowchart of another database master-slave switching method provided in an embodiment of the present application.

[0029] Figure 5 It is a flowchart of another database master-slave switching method provided in an embodiment of the present application.

[0030] Figure 6 It is a system architecture diagram for implementing the database master-slave switching method provided in the embodiment of the present application.

[0031] Figure 7 It is a schematic diagram of the workflow of the detection script in the database host provided in the embodiment of the present application.

[0032] Figure 8 A schematic diagram of the workflow of the detection script in the database standby machine provided in an embodiment of the present application.

[0033] Fig. 9 It is a schematic diagram of data flow in the database master-slave switching method provided in an embodiment of the present application.

[0034] Fig.10 It is a schematic diagram of a database master-slave switching device provided in an embodiment of the present application.

[0035] Fig.11 It is a schematic diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0036] In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present application. However, it should be clear to those skilled in the art that the present application may also be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, devices, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present application.

[0037] A method and device for switching between active and standby databases according to an embodiment of the present application will be described in detail below with reference to the accompanying drawings.

[0038] As mentioned above, the current master-slave configuration method for PostgreSQL database is complex to implement and may have problems such as single point failure and "split brain" risk.

[0039] In the related technology, PostgreSQL database natively supports log-based streaming replication (StreamingReplication), which is the mainstream replication solution introduced after PostgreSQL 10. The master database transmits transaction logs to the slave database in real time in a streaming manner, and the slave database applies these logs in real time to maintain data consistency. The advantage of using this method to implement PostgreSQL's master-slave redundancy is high real-time performance, but due to the fact that the slave database can only read but not write, when the master database fails, the entire system is in read-only mode. At this time, it is necessary to manually promote the slave database to the master, and when the original master database recovers from the failure, it is necessary to manually downgrade the original master database to the slave. The entire process requires manual participation, and no automatic switching is achieved, which greatly increases the risk of errors and fault recovery time, and then affects the production plan.

[0040] Furthermore, third-party tools such as Patroni and repmgr can also implement the active / standby configuration of PostgreSQL databases.

[0041] Among them, Patroni is an open source tool written in Python for managing and automating the high availability of PostgreSQL clusters. It is built on PostgreSQL's streaming replication technology, provides more advanced functions and management interfaces, supports automatic failover and switching, and ensures high service availability.

[0042] However, Patroni is a relatively complex system that relies on other components such as Kubernetes and Etcd. It is relatively troublesome to deploy and configure. For some small or simple PostgreSQL clusters, Patroni may be too complex and cumbersome. At the same time, Patroni needs to be deeply integrated with various orchestration systems (such as Kubernetes). Integration in heterogeneous environments may encounter some challenges. For environments that do not use container orchestration systems, the use of Patroni may be limited.

[0043] repmgr is another open source PostgreSQL replication management tool. Based on PostgreSQL's streaming replication technology, it provides a wealth of replication management functions, can automatically detect primary and standby nodes, implement failover, provide CLI and Web management interfaces, and support dual-machine deployment.

[0044] However, repmgr itself is also an additional component. If the repmgr service fails, the availability of the entire PostgreSQL cluster will be affected. This requires high availability protection for the repmgr service to avoid it becoming a new single point of failure. At the same time, although repmgr supports automatic failover, sometimes manual intervention by the administrator is still required, such as performing data repair and other operations.

[0045] In addition, in a PostgreSQL high-availability cluster managed by repmgr, clients usually use domain names to connect to the database. When the primary database fails, the client can automatically switch to the new primary database without any intervention, but this increases the burden of domain name management.

[0046] Furthermore, in a PostgreSQL high-availability cluster managed by repmgr, if the primary and standby nodes cannot communicate with each other, a "split brain" problem may occur, leading to serious consequences such as data inconsistency and service interruption.

[0047] This shows that although there are some third-party tools for PostgreSQL high availability, these tools still have some unavoidable problems for dual-machine high availability scenarios.

[0048] In view of this, an embodiment of the present application provides a database master-slave switching method, by setting initialization weights for the first node device and the second node device of the database respectively, and calling the detection script in the first node device and the second node device respectively through the layer switching mechanism component with a preset period to detect the status of each node device, the weight value of the node device that meets the weight degradation condition is reduced by a third weight to adjust the weight, and then the node device with the largest weight value after the weight adjustment is set as the database host, and the set database host is associated with a virtual Internet Protocol (IP) so that the user can access the database through the virtual IP, thereby realizing automatic switching of the database master-slave node devices and improving the efficiency of the database master-slave switching.

[0049] Figure 1 1 is a flow chart of a database master-slave switching method provided in an embodiment of the present application. Figure 1 As shown, the method comprises the following steps:

[0050] In step S101, the initialization weight of the first node device of the database is set to a first weight, and the initialization weight of the second node device is set to a second weight.

[0051] Among them, the first weight is greater than the second weight.

[0052] In step S102, the detection script is called at a preset period in the first node device and the second node device respectively through the layer exchange mechanism component to detect the status of each node device.

[0053] In step S103, in response to determining that the state of the first node device meets the weight degradation condition, the weight value of the first node device is reduced by a third weight, or in response to determining that the state of the second node device meets the weight degradation condition, the weight value of the second node device is reduced by a third weight.

[0054] The third weight is greater than the difference between the first weight and the second weight.

[0055] In step S104, the node device with the largest weight value among the first node device and the second node device is determined to be the database host, and the database host is associated with a preset virtual IP so that the user can access the database through the virtual IP.

[0056] In the embodiment of the present application, the method can be applied to configure the master-slave redundancy of the PostgreSQL database. Further, the method can be executed by two node devices configured as a database master and a database standby respectively.

[0057] In some embodiments, the initialization weight of the first node device of the database can be set to the first weight, and the initialization weight of the second node device can be set to the second weight. During initialization, the first node device can be set as the database host, and the second node device can be set as the database standby, that is, the first weight is set to be greater than the second weight.

[0058] In some embodiments, a layer switching mechanism component, such as a Keepalived component, may be set in the first node device and the second node device. Furthermore, a detection script may be pre-written in the first node device and the second node device, and the Keepalived component may call the detection script at a preset period to detect the status of each node device. Among them, the calling cycle may be set according to actual needs and is not limited here. It should be noted that a too fast calling cycle will increase the load of the system, and a too slow calling cycle will prolong the switching time after a failure. In one example, the calling cycle may be set to 2 seconds.

[0059] In some embodiments, if the Keepalived component calls the detection script to detect that the state of the first node device meets the weight degradation condition, the weight value of the first node device can be reduced by a third weight. Alternatively, if the Keepalived component calls the detection script to detect that the state of the second node device meets the weight degradation condition, the weight value of the second node device can be reduced by a third weight. Wherein, the third weight is greater than the difference between the first weight and the second weight. In an example, the first weight can be set to 100, the second weight can be set to 90, and the third weight can be set to 20.

[0060] In some embodiments, when the Keepalived component determines that the weight value of the node device where it is located has changed, it can compare the weight values ​​of the first node device and the second node device, and set the device with the largest weight value as the database host. Further, the Keepalived component can use the Virtual Router Redundancy Protocol (VRRP) protocol to make the first node device and the second node device share a virtual IP, and dynamically adjust the weight so that the virtual IP is always associated with the host device of the PostgreSQL database. Users use other applications to access the PostgreSQL database through the virtual IP, thereby realizing automatic switching of the database master and standby without manual intervention.

[0061] According to the technical solution provided in the embodiment of the present application, initialization weights are set for the first node device and the second node device of the database respectively, and the detection script is called in the first node device and the second node device respectively through the layer switching mechanism component with a preset period to detect the status of each node device, the weight value of the node device that meets the weight degradation condition is reduced by a third weight to adjust the weight, and then the node device with the largest weight value after the weight adjustment is set as the database host, and the set database host is associated with the virtual IP so that the user can access the database through the virtual IP, thereby realizing automatic switching of the database master and standby node devices and improving the master-standby switching efficiency of the database.

[0062] In some embodiments, in response to the target node device being a database host, the weight degradation condition may be that the target node device is unavailable; or, detection determines that the state of the target node device is a database standby device; or, detection determines that the state of the target node device and another node device are both database hosts, and the target node device is not connected to the virtual IP; or, the target node device communicates abnormally with the gateway; wherein the target node device is the first node device or the second node device.

[0063] In some embodiments, in response to the target node device being a backup database node device, the weight degradation condition may be that the data stream replication of the target node device is abnormal; or, detection determines that the first node device and the second node device are both database backup devices, and the target node device is not connected to a preset virtual IP; or, the target node device communicates abnormally with the gateway; wherein the target node device is the first node device or the second node device.

[0064] Figure 2 1 is a flow chart of another database master-slave switching method provided in an embodiment of the present application. Figure 2 Steps S201 to S204 in the embodiment shown are Figure 1Steps S101 to S104 in the illustrated embodiment are substantially the same and will not be described in detail herein. Figure 2 As shown, the method also includes the following steps:

[0065] In step S205, in response to the layer switching mechanism component in the database host node device calling the detection script to determine that the database is in a dual-host state, it is determined whether the database host node device is connected to the virtual IP.

[0066] In step S206, in response to determining that the database host node device is connected to the virtual IP, the weight value of the database host node device is maintained;

[0067] In step S207 , in response to determining that the database host node device is not connected to the virtual IP, the database host node device is determined to be a database standby device, and the weight value of the database host node device is reduced by a third weight.

[0068] In some embodiments, when the Keepalived component in the database host node device calls a detection script to determine that the database is in a dual-host state, that is, when it is determined that the states of the first node device and the second node device are both database hosts, the Keepalived component in the database host node device can determine whether the database host node device is connected to a virtual IP, and if so, keep the database host node device as a database host, and modify the state of the other node device to a database standby. Further, after modifying the state of the other node device to a database standby, the weight value of the other node device can also be reduced by a third weight.

[0069] On the other hand, if it is determined that the database host node device is not connected to the virtual IP, the database host node device can be set as a database standby machine, and the weight value of the database host node device can be reduced by a third weight. After the database host node device is subjected to weight reduction processing, since the weight value of the database host node device has changed, the Keepalived component can again determine the current weight values ​​of the first node device and the second node device, determine that the node device with the largest weight value is the database host, and connect the virtual IP to the database host.

[0070] Taking the first node device as a database host node device as an example, a detection script can be called in the Keepalived component of the first node device to detect the state of the first node device, and a detection script can be called in the Keepalived component of the second node device to detect the state of the second node device. The Keepalived component of the first node device can send the detected state of the first node device to the Keepalived component of the second node device, and the Keepalived component of the second node device can also send the detected state of the second node device to the Keepalived component of the first node device.

[0071] If the Keepalived component of the first node device determines that the states of the first node device and the second node device are both in the database host state, that is, the current database is in a dual-host state, then the Keepalived component of the first node device can further determine whether the first node device is connected to the virtual IP. If the first node device is connected to the virtual IP, the weight value of the first node device can be maintained, and a message can also be sent to the Keepalived component of the second node device to set the second node device as a database standby device and reduce the weight value of the second node device by a third weight.

[0072] After the weight value of the second node device is reduced by the third weight, the Keepalived component of the second node device can determine the current weight values ​​of the first node device and the second node device after the weight change. At this time, since the weight value of the first node device remains unchanged and the weight value of the second node device is reduced by the third weight, and the initial value of the first weight is greater than the second weight, the first node device still has the largest current weight value, so the first node device is kept as the database host.

[0073] On the contrary, if it is determined that the first node device is not connected to the virtual IP, the first node device can be first set as a database standby machine, and then the weight value of the first node device can be reduced by the third weight. Further, since the weight value of the first node device has changed, the Keepalived component of the first node device can also determine the current weight values ​​of the first node device and the second node device after the weight change, and determine the node device corresponding to the maximum value obtained as the database host, and connect the virtual IP to the database host.

[0074] Figure 3 1 is a flow chart of another method for switching between active and standby databases provided in an embodiment of the present application. Figure 3 Steps S301 to S304 in the embodiment shown are Figure 1 Steps S101 to S104 in the illustrated embodiment are substantially the same and will not be described in detail herein. Figure 3 As shown, the method also includes the following steps:

[0075] In step S305, in response to the layer switching mechanism component in the database standby node device calling the detection script to determine that the database is in a dual standby state, it is determined whether the database standby node device is connected to the virtual IP.

[0076] In step S306, in response to determining that the database standby node device is connected to the virtual IP, the database standby node device is determined to be a database host, and the weight value of the database standby node device is maintained.

[0077] In step S307, in response to determining that the database standby node device is not connected to the virtual IP, the weight value of the database standby node device is reduced by a third weight.

[0078] In some embodiments, when the Keepalived component in the database standby node device calls a detection script to determine that the database is in a dual standby state, that is, when it is determined that the states of the first node device and the second node device are both database standby devices, the Keepalived component in the database standby node device can determine whether the database standby node device is connected to the virtual IP. If so, the database standby node device is set as the database host and the weight value of the database standby node is maintained.

[0079] On the other hand, if it is determined that the database standby node device is not connected to the virtual IP, the database standby node device can be kept as a database standby, and the weight value of the database standby node device can be reduced by a third weight. After the database standby node device is subjected to weight reduction processing, since the weight value of the database standby node device has changed, the Keepalived component can again determine the current weight values ​​of the first node device and the second node device, determine that the node device with the largest weight value is the database host, and connect the virtual IP to the database host.

[0080] Taking the second node device as a database standby device as an example, a detection script can be called in the Keepalived component of the second node device to detect the status of the second node device, and a detection script can be called in the Keepalived component of the first node device to detect the status of the first node device. The Keepalived component of the first node device can send the detected status of the first node device to the Keepalived component of the second node device, and the Keepalived component of the second node device can also send the detected status of the second node device to the Keepalived component of the first node device.

[0081] If the Keepalived component of the second node device determines that the states of the first node device and the second node device are both in the database standby state, that is, the current database is in a dual standby state, then the Keepalived component of the second node device can further determine whether the second node device is connected to the virtual IP. If the second node device is connected to the virtual IP, the second node device can be set as the database host and the weight value of the second node device can be maintained.

[0082] On the contrary, if it is determined that the second node device is not connected to the virtual IP, the weight value of the second node device can be reduced by the third weight. Further, since the weight value of the second node device has changed, the Keepalived component of the second node device can also determine the current weight values ​​of the first node device and the second node device after the weight change, and determine the node device corresponding to the maximum value obtained as the database host, and connect the virtual IP to the database host.

[0083] Figure 4 : is a flowchart of another method for switching between active and standby databases provided in an embodiment of the present application. Figure 4 Steps S401 to S404 in the embodiment shown are Figure 1 Steps S101 to S104 in the illustrated embodiment are substantially the same and will not be described in detail herein. Figure 4 As shown, the method also includes the following steps:

[0084] In step S405, in response to the layer exchange mechanism component in the database standby node device calling the detection script to determine that a data stream replication anomaly occurs in the database standby node device, the detection script executes a data repair command to repair the data stream.

[0085] In some embodiments, when the Keepalived component in the database standby node device calls a detection script to determine that a data stream replication anomaly occurs in the database standby node device, the detection script can be used to execute a data repair command to repair the data stream, and the weight value of the database standby node device is reduced by a third weight. Conversely, if it is determined that the data stream replication of the database standby node device is normal, the weight value of the database standby node device is kept unchanged.

[0086] In this way, when the database standby machine detects data stream replication anomalies, data repair can be completed automatically without manual intervention.

[0087] Figure 5 It is a flowchart of another database master-slave switching method provided in an embodiment of the present application.

[0088] in, Figure 5 Steps S501 to S504 in the embodiment shown are Figure 1 Steps S101 to S104 in the illustrated embodiment are substantially the same and will not be described in detail herein. Figure 5 As shown, the method also includes the following steps:

[0089] In step S505, in response to the layer switching mechanism component in the database host node device calling the detection script to determine that the communication between the database host node device and other devices is abnormal, the communication between the database host node device and the gateway is detected.

[0090] In step S506, in response to determining that the communication between the database host node device and the gateway is abnormal, the database host node device is determined to be a database standby device, and the weight value of the database host node device is reduced by a third weight, otherwise the weight of the database host node device is maintained.

[0091] In step S507, in response to the layer exchange mechanism component in the database standby node device calling the detection script to determine that the communication between the database standby node device and other devices is abnormal, the communication between the database standby node device and the gateway is detected.

[0092] In step S508, in response to determining that the communication between the database standby node device and the gateway is abnormal, the weight value of the database standby node device is reduced by a third weight, otherwise the weight of the database standby node device is maintained.

[0093] In some embodiments, when the layer exchange mechanism component in the database host node device calls a detection script to determine that the communication between the database host node device and other devices is abnormal, the communication between the database host node device and the gateway can be further detected. If it is determined that the communication between the database host node device and the gateway is abnormal, the database host node device can be determined to be a database standby machine, and the weight value of the database host node device can be reduced by a third weight, otherwise the weight of the database host node device is maintained.

[0094] In some embodiments, when the layer exchange mechanism component in the database standby node device calls a detection script to determine that the communication between the database standby node device and other devices is abnormal, the communication between the database standby node device and the gateway can be further detected. When it is determined that the communication between the database standby node device and the gateway is abnormal, the weight value of the database standby node device can be reduced by a third weight, otherwise the weight of the database standby node device is maintained.

[0095] As described above, the Keepalived component of the first node device may send the detected state of the first node device to the Keepalived component of the second node device, and the Keepalived component of the second node device may also send the detected state of the second node device to the Keepalived component of the first node device.

[0096] Taking the first node device as the database host and the second node device as the database standby device as an example, if the Keepalived component of the first node device fails to receive the second node device status information sent by the Keepalived component of the second node device, or the received second node device status information is abnormal, the Keepalived component of the first node device can detect the communication status between the first node device and the gateway.

[0097] If it is determined that the first node device communicates normally with the gateway, the weight of the first node device can be maintained. If it is determined that the first node device communicates abnormally with the gateway, the first node device can be first set as a database backup machine, and then the weight value of the first node device can be reduced by the third weight. After reducing the weight value of the first node device by the third weight, the Keepalived component of the first node device again executes the steps of determining the weight values ​​of the first node device and the second node device, determining that the node device corresponding to the maximum weight value is the database host, and connecting the virtual IP to the database host.

[0098] On the other hand, if the Keepalived component of the second node device fails to receive the first node device status information sent by the Keepalived component of the first node device, or the received first node device status information is abnormal, the Keepalived component of the second node device can detect the communication status between the second node device and the gateway.

[0099] If it is determined that the second node device communicates normally with the gateway, the weight of the second node device can be maintained. If it is determined that the two node devices communicate abnormally with the gateway, the weight value of the second node device can be reduced by a third weight. After reducing the weight value of the second node device by the third weight, the Keepalived component of the second node device again executes the steps of determining the weight values ​​of the first node device and the second node device, determining that the node device corresponding to the maximum weight value is the database host, and connecting the virtual IP to the database host.

[0100] In this way, when the primary and backup nodes cannot communicate with each other, the "split brain" problem can be avoided by determining whether the communication between the node and the gateway is abnormal.

[0101] In some embodiments, since the technical solution provided in the embodiments of the present application is applied to the master-slave setting in a dual-machine scenario, when determining that the device with the largest weight value among the first node device and the second node device is the database host, it is also possible to determine that the device with the smallest weight value among the first node device and the second node device is the database standby machine.

[0102] Among them, determining the database host can be achieved by calling the upgrade script through the Keepalived component, that is, the step of determining the first node device or the second node device as the database host can be to call the upgrade script through the layer exchange mechanism component in the first node device or the second node device to determine the first node device or the second node device as the database host.

[0103] Furthermore, determining that the database standby machine is a standby machine can be achieved by calling a downgrade script through the Keepalived component, that is, the step of determining that the first node device or the second node device is a database standby machine can be to call the downgrade script through the layer switching mechanism component in the first node device or the second node device to determine the first node device or the second node device as a database standby machine.

[0104] Figure 6 1 is a system architecture diagram for implementing the database master-slave switching method provided in the embodiment of the present application. Figure 6 As shown, the system may include a first node device node1 and a second node device node2, wherein in the initial state node1 is a database host node device, node2 is a database standby node device, and in the initial state node1 is connected to the application via a virtual IP (VIP), and node2 is not connected to the VIP.

[0105] Furthermore, node1 and node2 include a PostgreSQL database, a Keepalived component, and a script unit, respectively. The script unit includes a detection script, a master upgrade script, and a standby downgrade script. The Keepalived components of node1 and node2 can communicate through the VRRP protocol. The PostgreSQL databases of node1 and node2 can achieve master-standby synchronization through streaming replication. The detection scripts of node1 and node2 can simultaneously perform dual-host detection, dual-standby detection, replication anomaly detection, and network disconnection detection. Each script is called by the Keepalived component and is used to control the PostgreSQL database. Keepalived enables the host node node1 and the host node node2 to share a VIP through the VRRP protocol. By dynamically adjusting the weight value, the VIP is always on the PostgreSQL master database, and the application accesses the database through the VIP.

[0106] The weight values ​​of node1 and node2 are dynamically adjusted by the return value of the detection script. When the detection script returns normally (for example, the return value is 0), the weight value of each node device remains unchanged. When the detection script returns abnormally (for example, the return value is 1), the weight value of each node device is reduced by a fixed value (assuming it is D). To realize that the Keepalived component automatically switches the master and slave states according to the weight, assuming that the default weight of the initial master node node1 is M, and the default weight of the initial slave node node2 is B, it is necessary to meet MD <B。

[0107] Furthermore, the detection script can be used to perform the following detection functions: PostgreSQL running status detection, if the local PostgreSQL is unavailable, an exception is returned (for example, the return value is 1); dual-master / dual-standby detection, when it is detected that PostgreSQL has a dual-master or dual-standby exception, it is determined whether there is a VIP on the local machine, and then the standby is automatically downgraded or promoted to the master; replication anomaly detection, when the local machine is a standby and the detection determines that the current PostgreSQL has a streaming replication anomaly, it is automatically repaired; network disconnection detection, when a communication anomaly with another machine is detected, an attempt is made to communicate with the gateway. If communication with the gateway is also impossible, it indicates a network failure on the local machine, and the local machine is downgraded to a standby machine.

[0108] When any node device is promoted from a database standby machine to a database master, the Keepalived component can call the master upgrade script to upgrade the node device and the Keepalived component of the node device to the master. In the master upgrade script, it is also necessary to upgrade PostgreSQL to the master through commands so that the host status of Keepalived and the host status of PostgreSQL are consistent.

[0109] When any node device is downgraded from the database host to the database standby machine, the Keepalived component can call the downgrade script to downgrade the node device and the Keepalived component of the node device. In the downgrade script, it is also necessary to downgrade PostgreSQL to standby through commands so that the standby status of Keepalived and PostgreSQL are consistent.

[0110] Figure 7 Schematic diagram of the workflow of the detection script in the database host provided by the embodiment of the present application. Figure 7 As shown, the detection script in the database host detects the status of the database management system in the database host node device. In an example, the database management system can be PostgreSQL, referred to as PG.

[0111] The detection script in the database host can first detect whether the local PG is available. If not, it returns a value of 1 and downgrades the weight of the local machine. If so, it further queries the master-slave status of the local machine and the other machine PG. If it is determined that the local PG is the database standby machine, it returns a value of 1 and downgrades the weight of the local machine. If it is determined that the local PG is the database host and the other machine PG is the database standby machine, it returns a value of 0 and maintains the weight of the local machine. If it is determined that the local PG is the database host and the other machine PG is also the database host, it is determined whether the local machine has a VIP. If so, it returns a value of 0 and maintains the weight of the local machine. If not, it is downgraded to a database standby machine and returns a value of 1 to downgrade the weight of the local machine. If it is determined that the local PG is the database host, but the status of the other machine PG is unknown, it further detects the communication between the local PG and the gateway. If the local PG communicates normally with the gateway, it returns a value of 0 and maintains the weight of the local machine. If the local PG communicates abnormally with the gateway, indicating that the local network has a fault, the local PG is downgraded to a database standby machine, and a value of 1 is returned to downgrade the weight of the local machine.

[0112] Figure 8 The present application provides a schematic diagram of the workflow of the detection script in the database standby machine. Figure 8 As shown, the detection script in the database standby machine detects the status of the database management system in the database standby machine node device. In an example, the database management system can be PostgreSQL, referred to as PG.

[0113] When the detection script in the database standby machine determines that the local PG is the database standby machine, it can first return a value of 1, downgrade the weight of the local machine, and then determine the status of the other machine PG; if the other machine PG is the database host, further check the local PG streaming replication status, if the status is normal, return a value of 0, maintain the local weight, otherwise execute the data repair command, and return a value of 1 to downgrade the weight of the local machine; if the other machine PG is the database standby machine, that is, a dual standby scenario occurs, further detect whether the local machine has a VIP, if not, return a value of 1, downgrade the weight of the local machine, if so, first promote the local PG to the master, and then return a value of 0 to maintain the local weight; if the status of the other machine PG is unknown, further detect the communication between the local PG and the gateway, if the communication between the local PG and the gateway is normal, return a value of 0, maintain the local weight, if the communication between the local PG and the gateway is abnormal, indicating that the local PG network is faulty, then return a value of 1, and downgrade the weight of the local machine.

[0114] Fig. 9 Schematic diagram of data flow in the database master-slave switching method provided in the embodiment of the present application. Fig. 9As shown in the figure above, during initialization, node1 is the database host with an IP of 172.21.44.21, node2 is the database standby with an IP of 172.21.44.22, and the VIP configuration is 172.21.44.200. The application connects to node1 through VIP, with the PG in node1 as the host and the PG in node2 as the standby. Fig. 9 As shown in the figure below, when a failure occurs and the database switches between the primary and secondary machines, node2 becomes the database host and node1 becomes the database backup machine. At this time, the application connects to node2 through VIP, with the PG in node2 as the host and the PG in node1 as the backup machine.

[0115] By adopting the technical solution of the embodiment of the present application, PostgreSQL dual-machine high availability is achieved based on Keepalived, and there is no need to introduce PostgreSQL third-party open source high-availability tools in the system, which reduces the system resource usage. Further, on the basis of meeting the functions of PostgreSQL third-party open source high-availability tools, the exception handling capabilities are enhanced, such as dual-master, dual-backup and automatic repair of stream replication exceptions, which enhances the robustness of the system without manual intervention, thereby reducing the number of database operation and maintenance personnel, and playing a role in reducing costs and increasing efficiency.

[0116] Furthermore, the embodiment of the present application proposes a new solution to the problem of high availability of PostgreSQL dual machines. Dual machine scenarios are almost all small and medium-sized projects in the field of industrial automation. In small and medium-sized projects, resources are generally limited. The technical solution of the embodiment of the present application can not only meet the high availability requirements, but also has the advantages of lightweight and easy maintenance.

[0117] All the above optional technical solutions can be arbitrarily combined to form optional embodiments of the present application, which will not be described one by one here.

[0118] The following is an embodiment of the device of the present application, which can be used to execute the embodiment of the method of the present application. For details not disclosed in the embodiment of the device of the present application, please refer to the embodiment of the method of the present application.

[0119] Fig.10 Schematic diagram of a database master-slave switching device provided in an embodiment of the present application. Fig.10 As shown, the device comprises:

[0120] The initialization module 1001 is configured to set the initialization weight of the first node device of the database to a first weight and the initialization weight of the second node device to a second weight, wherein the first weight is greater than the second weight.

[0121] The detection module 1002 is configured to call the detection script in a preset period in the first node device and the second node device respectively through the layer exchange mechanism component to detect the status of each node device.

[0122] The weight adjustment module 1003 is configured to reduce the weight value of the first node device by a third weight in response to determining that the state of the first node device meets the weight downgrade condition, or to reduce the weight value of the second node device by a third weight in response to determining that the state of the second node device meets the weight downgrade condition, wherein the third weight is greater than the difference between the first weight and the second weight.

[0123] The master-slave setting module 1004 is configured to determine that the node device with the largest weight value among the first node device and the second node device is the database host, and associate the database host with a preset virtual Internet interconnection protocol IP so that the user can access the database through the virtual IP.

[0124] According to the technical solution provided in the embodiment of the present application, initialization weights are set for the first node device and the second node device of the database respectively, and the detection script is called in the first node device and the second node device respectively through the layer switching mechanism component with a preset period to detect the status of each node device, the weight value of the node device that meets the weight degradation condition is reduced by a third weight to adjust the weight, and then the node device with the largest weight value after the weight adjustment is set as the database host, and the set database host is associated with the virtual IP so that the user can access the database through the virtual IP, thereby realizing automatic switching of the database master and standby node devices and improving the master-standby switching efficiency of the database.

[0125] In some embodiments, in response to the target node device being a database host, the weight degradation conditions include: the target node device is unavailable; or the target node device is detected and determined to be in a database standby state; or the target node device and another node device are detected and determined to be database hosts, and the target node device is not connected to a virtual IP; or the target node device communicates abnormally with the gateway; wherein the target node device is the first node device or the second node device.

[0126] In some embodiments, in response to the target node device being a backup database node device, the weight degradation conditions include: data stream replication of the target node device is abnormal; or detection determines that the first node device and the second node device are both database backup devices, and the target node device is not connected to the preset virtual IP; or communication between the target node device and the gateway is abnormal; wherein the target node device is the first node device or the second node device.

[0127] In some embodiments, after the layer switching mechanism component in the first node device and the second node device respectively calls the detection script in a preset period to detect the status of each node device, the method also includes: in response to the layer switching mechanism component in the database host node device calling the detection script to determine that the database is in a dual-host state, determining whether the database host node device is connected to the virtual IP; in response to determining that the database host node device is connected to the virtual IP, maintaining the weight value of the database host node device; in response to determining that the database host node device is not connected to the virtual IP, determining that the database host node device is a database standby, and reducing the weight value of the database host node device by a third weight; or in response to the layer switching mechanism component in the database standby node device calling the detection script to determine that the database is in a dual-standby state, determining whether the database standby node device is connected to the virtual IP; in response to determining that the database standby node device is connected to the virtual IP, determining that the database standby node device is a database host, and maintaining the weight value of the database standby node device; in response to determining that the database standby node device is not connected to the virtual IP, reducing the weight value of the database standby node device by a third weight.

[0128] In some embodiments, after the layer switching mechanism component in the first node device and the second node device respectively calls the detection script at a preset period to detect the status of each node device, the method also includes: in response to the layer switching mechanism component in the database standby node device calling the detection script to determine that a data stream replication anomaly occurs in the database standby node device, the detection script executes a data repair command to repair the data stream.

[0129] In some embodiments, after the detection script is called at a preset period by the layer exchange mechanism component in the first node device and the second node device respectively to detect the status of each node device, the method further includes: in response to the layer exchange mechanism component in the database host node device calling the detection script to determine the communication anomaly between the database host node device and other devices, detecting the communication status between the database host node device and the gateway; in response to determining the communication anomaly between the database host node device and the gateway, determining that the database host node device is a database standby device, and reducing the weight value of the database host node device by a third weight, otherwise maintaining the weight of the database host node device; or in response to the layer exchange mechanism component in the database standby node device calling the detection script to determine the communication anomaly between the database standby node device and other devices, detecting the communication status between the database standby node device and the gateway; in response to determining the communication anomaly between the database standby node device and the gateway, reducing the weight value of the database standby node device by a third weight, otherwise maintaining the weight of the database standby node device.

[0130] In some embodiments, the device with the smallest weight value among the first node device and the second node device is determined to be the database standby machine; wherein, the step of determining the first node device or the second node device as the database host is to call the upgrade script through the layer exchange mechanism component in the first node device or the second node device, and determine the first node device or the second node device as the database host; the step of determining the first node device or the second node device as the database standby machine is to call the downgrade script through the layer exchange mechanism component in the first node device or the second node device, and determine the first node device or the second node device as the database standby machine.

[0131] It should be understood that the size of the serial numbers of the steps in the above embodiments does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0132] Fig.11 Schematic diagram of an electronic device provided in an embodiment of the present application. Fig.11 As shown, the electronic device 11 of this embodiment includes: a processor 1101, a memory 1102, and a computer program 1103 stored in the memory 1102 and executable on the processor 1101. When the processor 1101 executes the computer program 1103, the steps in the above-mentioned method embodiments are implemented. Alternatively, when the processor 1101 executes the computer program 1103, the functions of the modules / units in the above-mentioned device embodiments are implemented.

[0133] The electronic device 11 may be a desktop computer, a notebook, a PDA, a cloud server, or other electronic device. The electronic device 11 may include, but is not limited to, a processor 1101 and a memory 1102. Those skilled in the art will appreciate that Fig.11 The electronic device 11 is merely an example and does not limit the electronic device 11 , and may include more or less components than those shown in the figure, or different components.

[0134] Processor 1101 can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

[0135] The memory 1102 may be an internal storage unit of the electronic device 11, for example, a hard disk or memory of the electronic device 11. The memory 1102 may also be an external storage device of the electronic device 11, for example, a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card (FlashCard), etc. equipped on the electronic device 11. The memory 1102 may also include both an internal storage unit of the electronic device 11 and an external storage device. The memory 1102 is used to store computer programs and other programs and data required by the electronic device.

[0136] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units.

[0137] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A database master-slave switching method, characterized in that: include: Set the initialization weight of the first node device of the database to a first weight, and the initialization weight of the second node device to a second weight, wherein the first weight is greater than the second weight; In the first node device and the second node device, respectively, the layer switching mechanism component is used to call the detection script at a preset period to detect the status of each node device; In response to determining that the state of the first node device meets the weight degradation condition, reducing the weight value of the first node device by a third weight, or in response to determining that the state of the second node device meets the weight degradation condition, reducing the weight value of the second node device by a third weight, wherein the third weight is greater than a difference between the first weight and the second weight; The node device with the largest weight value among the first node device and the second node device is determined to be a database host, and the database host is associated with a preset virtual Internet interconnection protocol IP so that a user can access the database through the virtual IP.

2. The method according to claim 1, characterized in that In response to the target node device being a database host, the weight degradation condition includes: The target node device is unavailable; or Detect and determine that the state of the target node device is a database standby machine; or Detect and determine that the states of the target node device and another node device are both database hosts, and the target node device is not connected to the virtual IP; or The target node device has abnormal communication with the gateway; The target node device is the first node device or the second node device.

3. The method according to claim 1, characterized in that In response to the target node device being a standby node device, the weight degradation condition includes: The data stream replication of the target node device is abnormal; or It is detected that both the first node device and the second node device are database standby machines, and the target node device is not connected to the preset virtual IP; or The target node device has abnormal communication with the gateway; The target node device is the first node device or the second node device.

4. The method according to claim 1, characterized in that: After the detection script is called at a preset period by the layer exchange mechanism component in the first node device and the second node device to detect the status of each node device, the method further includes: In response to the layer switching mechanism component in the database host node device calling the detection script to determine that the database is in a dual-host state, determine whether the database host node device is connected to the virtual IP; In response to determining that the database host node device is connected to the virtual IP, maintaining the weight value of the database host node device; In response to determining that the database host node device is not connected to the virtual IP, determining that the database host node device is a database standby device, and reducing the weight value of the database host node device by the third weight; or In response to the layer exchange mechanism component in the database standby node device calling the detection script to determine that the database is in a dual standby state, determine whether the database standby node device is connected to the virtual IP; In response to determining that the database standby node device is connected to the virtual IP, determining that the database standby node device is a database host, and maintaining a weight value of the database standby node device; In response to determining that the database standby node device is not connected to the virtual IP, the weight value of the database standby node device is reduced by the third weight.

5. The method according to claim 1, characterized in that In the first node device and the second node device, respectively, the detection script is called at a preset period through the layer exchange mechanism component to detect the status of each node device, and the method further includes: In response to the layer exchange mechanism component in the database standby node device calling a detection script to determine that a data stream replication anomaly occurs in the database standby node device, the detection script executes a data repair command to repair the data stream.

6. The method according to claim 1, characterized in that In the first node device and the second node device, respectively, the detection script is called at a preset period through the layer exchange mechanism component to detect the status of each node device, and the method further includes: In response to the layer exchange mechanism component in the database host node device calling the detection script to determine that the communication between the database host node device and other devices is abnormal, the communication status between the database host node device and the gateway is detected; In response to determining that the communication between the database host node device and the gateway is abnormal, determining that the database host node device is a database standby device, and reducing the weight value of the database host node device by a third weight, otherwise maintaining the weight of the database host node device; or In response to the layer exchange mechanism component in the database standby node device calling the detection script to determine that the communication between the database standby node device and other devices is abnormal, the communication status between the database standby node device and the gateway is detected; In response to determining that the communication between the database standby node device and the gateway is abnormal, the weight value of the database standby node device is reduced by a third weight; otherwise, the weight of the database standby node device is maintained.

7. The method according to any one of claims 1 to 6, characterized in that The method further comprises: Determine that a device with the smallest weight value among the first node device and the second node device is a database standby device; The step of determining the first node device or the second node device as a database host is to call a master upgrade script through a layer exchange mechanism component in the first node device or the second node device to determine the first node device or the second node device as a database host; The step of determining the first node device or the second node device as a database standby machine is to call a downgrade script through a layer exchange mechanism component in the first node device or the second node device to determine the first node device or the second node device as a database standby machine.

8. A database master-slave switching device, characterized in that: include: An initialization module is configured to set an initialization weight of a first node device of the database to a first weight, and an initialization weight of a second node device to a second weight, wherein the first weight is greater than the second weight; A detection module is configured to call a detection script in a preset period in the first node device and the second node device respectively through a layer switching mechanism component to detect the status of each node device; a weight adjustment module, configured to, in response to determining that the state of the first node device meets the weight degradation condition, reduce the weight value of the first node device by a third weight, or, in response to determining that the state of the second node device meets the weight degradation condition, reduce the weight value of the second node device by a third weight, wherein the third weight is greater than a difference between the first weight and the second weight; The master-slave setting module is configured to determine that the node device with the largest weight value among the first node device and the second node device is the database host, and associate the database host with a preset virtual Internet interconnection protocol IP so that users can access the database through the virtual IP.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images