Service state determination system and method for a postgresql database

Abstract

The application discloses a service state determination system and method of a PostgreSQL database. In the system, a first device sends a ping data packet to a target IP address of a second device, the second device receives the ping data packet sent by the first device and sends first response information to the first device. If the first response information returned by the target IP address is received, it is determined that the network of the second device is normal, a predefined executable SQL statement is sent to the target IP address, the second device receives the SQL statement sent by the first device, and if the SQL statement cannot be executed, second response information is sent to the first device. If the second response information returned by the target IP address that the SQL statement cannot be executed is received, it is determined that the PostgreSQL database is in a pseudo-death state, thereby solving the problem that whether the PostgreSQL database is in a pseudo-death state cannot be identified in the prior art.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a system and method for determining the service status of a PostgreSQL database. Background Art

[0002] PostgreSQL is an open-source relational database that meets the ACID requirements of relational databases. It is comprehensive and powerful, suitable for various production environments, supports secondary development, and enjoys a high market share. However, the official database does not provide a ready-made solution for system high availability, and most high-availability solutions on the market are provided by third parties.

[0003] Common high-availability systems in production environments include the following:

[0004] Based on dual-machine hot standby technology with shared storage devices between the primary and backup servers, this solution uses shared storage devices as the unified data storage backend. Shared storage usually uses disk arrays or storage area networks (SANs). The shared storage devices are simultaneously mounted to the primary and backup servers through dual-machine software. However, only one server can obtain read and write rights to the shared storage devices at any given time, and only one copy of the business data is actually stored. In normal working conditions, the primary server is in working status (i.e., Active status), and the backup server is in monitoring readiness status (i.e., Standby status). The two servers provide a VIP for external applications to access. When the primary server fails for some reason, the backup server is activated through software or manual means, and the backup server is given read and write rights to the shared storage to ensure that the system returns to normal in a short time. This achieves the goal of high system availability.

[0005] Log-based master-slave database replication is a classic master-slave solution. The master and slave databases each independently store a copy of business data locally. Data synchronization is achieved between the master and slave databases by transferring logs. When data changes in the master database are committed, the corresponding change log is sent to the slave database. The slave database then restores its own data based on the received change log, ensuring data consistency between the master and slave databases. If the master database fails, service continues on the slave database.

[0006] This technology is based on operating system-level data block synchronization. A typical scenario for this solution is Distributed Replication Block Device (DRBD), which can be understood as RAID 1 at the operating system level. It is a software-based, network-based block replication storage solution primarily used for data mirroring between disks, partitions, logical volumes, and other servers. When a user writes data to a local disk, the system sends the changed data blocks to the disk of another host on the network. This ensures real-time synchronization between the local host (primary node) and the remote host (backup node). If a problem occurs on the local host, the remote host still retains a copy of the same data and can continue to use it, ensuring data security.

[0007] One existing method for building high-availability PostgreSQL databases uses an open-source tool component (repmgr). All cluster management tasks rely on repmgr. While repmgr can detect network disconnections, external storage failures, and system power outages, it cannot detect when the PostgreSQL database is in a suspended state. This state refers to a situation where the database process exists but cannot execute SQL statements.

[0008] Therefore, how to identify whether the PostgreSQL database is in a suspended state becomes an urgent problem to be solved. Summary of the Invention

[0009] The present invention provides a system, method, apparatus, device and medium for determining the service status of a PostgreSQL database, which are used to solve the problem in the prior art of being unable to identify whether the PostgreSQL database is in a suspended state.

[0010] The present invention provides a service status determination system for a PostgreSQL database, the system comprising: a first device and a second device, wherein the first device and the second device are both deployed with a PostgreSQL database;

[0011] The first device is configured to obtain a pre-stored target IP address of the second device, send a ping packet to the target IP address, and if a first response message returned by the target IP address is received, send a pre-defined executable structured query language SQL statement to the target IP address; if a second response message returned by the target IP address is received that is unexecutable, determine that the PostgreSQL database deployed in the second device is in a suspended state;

[0012] The second device is configured to receive the ping data packet sent by the first device, send the first response information to the first device, receive the SQL statement sent by the first device, and send the second response information to the first device if the SQL statement cannot be executed.

[0013] Furthermore, the first device is also used to determine that the PostgreSQL database deployed in the second device is in an abnormal state due to a network anomaly of the second device if the first response information returned by the target IP address is not received within a first set time period.

[0014] Furthermore, the second device is further configured to send a third response message indicating successful execution to the first device if the SQL statement is executed successfully;

[0015] The first device is further configured to determine that the PostgreSQL database deployed in the second device is in a normal service state if the third response information returned by the target IP address is received.

[0016] Furthermore, the first device is also used to obtain its current role. If its current role is the master node role, and if the PostgreSQL database deployed in the second device is in an abnormal service state for a number of consecutive times greater than a first preset number threshold within a second set time period, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0017] Further, the first device is specifically configured to send an incremental recovery instruction to the target IP address; if first feedback information indicating a successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if second feedback information indicating a failed incremental recovery is received from the target IP address, a full recovery instruction is sent to the target IP address; if third feedback information indicating a successful full recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if fourth feedback information indicating a failed full recovery is received, it is determined that the abnormal recovery of the second device is unsuccessful.

[0018] The second device is further used to receive the incremental recovery instruction and perform incremental recovery, and send the first feedback information to the first device if the incremental recovery is successful, and send the second feedback information to the first device if the incremental recovery fails; receive the full recovery instruction and perform full recovery, and send the third feedback information to the first device if the full recovery is successful, and send the fourth feedback information to the first device if the full recovery fails.

[0019] Furthermore, the first device is also used to obtain its current role. If its current role is a slave node role, if the PostgreSQL database deployed in the second device is in an abnormal service state for a number of times greater than a second preset number threshold within a third set time period, its current role will be switched to a master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0020] Furthermore, the first device is also used to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period, and if the first response information returned by the target IP address is received within the first set time period, determine that the network of the second device has recovered and perform dual-master recovery.

[0021] Furthermore, any one of the first device and the second device is used to send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role.

[0022] Accordingly, the present invention provides a method for determining the service status of a PostgreSQL database, the method comprising:

[0023] Obtain the pre-saved target IP address of the second device.

[0024] A ping packet is sent to the target IP address. If a first response message is received from the target IP address, a predefined executable structured query language SQL statement is sent to the target IP address. If a second response message that cannot be executed is received from the target IP address, it is determined that the PostgreSQL database deployed in the second device is in a suspended state.

[0025] Further, the method further comprises:

[0026] If the first response information returned by the target IP address is not received within the first set time length, it is determined that the PostgreSQL database deployed in the second device is in an abnormal state due to the abnormal network of the second device.

[0027] Further, the method further comprises:

[0028] If the third response information returned by the target IP address is received, it is determined that the PostgreSQL database deployed in the second device is in a normal service state.

[0029] Further, the method further comprises:

[0030] Obtaining a current role of itself;

[0031] If the current role of itself is a master node role, it is judged whether the number of times that the PostgreSQL database deployed in the second device is continuously in an abnormal service state within a second set time length is greater than a first preset number threshold;

[0032] If yes, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the false death state and the abnormal state.

[0033] Further, the control of the PostgreSQL database deployed in the second device to perform abnormal recovery comprises:

[0034] Sending an incremental recovery instruction to the target IP address;

[0035] If first feedback information of successful incremental recovery returned by the target IP address is received, it is determined that the second device is successfully recovered from the exception;

[0036] If second feedback information of failed incremental recovery returned by the target IP address is received, a full recovery instruction is sent to the target IP address; if third feedback information of successful full recovery returned by the target IP address is received, it is determined that the second device is successfully recovered from the exception;

[0037] If fourth feedback information of failed full recovery is received, it is determined that the second device fails to recover from the exception.

[0038] Further, the method further comprises:

[0039] Obtaining a current role of itself;

[0040] If its current role is a slave node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time length is greater than a second preset number threshold;

[0041] If so, the current role of the node is switched to the master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0042] Furthermore, the method further comprises:

[0043] If the PostgreSQL database deployed in the second device is in the abnormal state for a number of consecutive times greater than the second preset number threshold within the third set time period, the ping data packet is sent to the target IP address. If the first response information returned by the target IP address is received within the first set time period, it is determined that the network of the second device has recovered, and dual-master recovery is performed.

[0044] Furthermore, the dual-active recovery includes:

[0045] Send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role.

[0046] Accordingly, the present invention provides a device for determining the service status of a PostgreSQL database, the device comprising:

[0047] An acquisition module is used to acquire a pre-saved target IP address of the second device;

[0048] A determination module is used to send a ping data packet to the target IP address, and if a first response information returned by the target IP address is received, a predefined executable structured query language SQL statement is sent to the target IP address; if a second response information that cannot be executed is received and returned by the target IP address, it is determined that the PostgreSQL database deployed in the second device is in a suspended state.

[0049] Furthermore, the determination module is further configured to determine that a network anomaly of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state if the first response information returned by the target IP address is not received within a first set time period.

[0050] Furthermore, the determination module is further configured to determine that the PostgreSQL database deployed in the second device is in a normal service state if the third response information returned by the target IP address is received.

[0051] Furthermore, the acquisition module is also used to obtain its own current role;

[0052] The determination module is also used to determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold if its current role is the master node role; if so, control the PostgreSQL database deployed in the second device to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0053] Furthermore, the determination module is specifically used to send an incremental recovery instruction to the target IP address; if a first feedback message indicating a successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if a second feedback message indicating a failed incremental recovery is received from the target IP address, a full recovery instruction is sent to the target IP address; if a third feedback message indicating a successful full recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if a fourth feedback message indicating a failed full recovery is received, it is determined that the abnormal recovery of the second device is unsuccessful.

[0054] Furthermore, the acquisition module is also used to obtain its own current role;

[0055] The determination module is also used to determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time period is greater than a second preset number threshold if its current role is a slave node role; if so, switch its current role to a master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0056] Furthermore, the determination module is specifically used to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period, and if the first response information returned by the target IP address is received within the first set time period, determine that the network of the second device is restored and perform dual-master recovery.

[0057] Furthermore, the determination module is specifically used to send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role.

[0058] Accordingly, the present invention provides an electronic device, comprising a processor and a memory, wherein the memory is used to store program instructions, and the processor is used to implement the steps of any of the methods described in the above-mentioned method for determining the service status of a PostgreSQL database when executing the computer program stored in the memory.

[0059] Accordingly, the present invention provides a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of any one of the methods described in the above-mentioned method for determining the service status of a PostgreSQL database.

[0060] The present invention provides a service status determination system, method, apparatus, device and medium for a PostgreSQL database. In the system, a first device obtains a pre-saved target IP address of a second device, sends a ping data packet to the target IP address, the second device receives the ping data packet sent by the first device, and sends a first response message to the first device. If the first response message returned by the target IP address is received, it is determined that the network of the second device is normal. A pre-defined executable structured query language SQL statement is sent to the target IP address, the SQL statement sent by the first device is received, and if the SQL statement cannot be executed, a second response message is sent to the first device. If the first device receives the second response message returned by the target IP address indicating that the SQL statement cannot be executed, it is determined that the PostgreSQL database deployed in the second device is in a suspended state, thereby solving the problem in the prior art of being unable to identify whether the PostgreSQL database is in a suspended state. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of 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.

[0062] Figure 1 A schematic diagram of the structure of a system for determining the service status of a PostgreSQL database provided by an embodiment of the present invention;

[0063] Figure 2 A schematic diagram of a slave node recovery provided by an embodiment of the present invention;

[0064] Figure 3 A schematic diagram of a dual-active recovery process provided by an embodiment of the present invention;

[0065] Figure 4 A schematic diagram of the overall architecture of a system for determining the service status of a PostgreSQL database provided by an embodiment of the present invention;

[0066] Figure 5 A schematic diagram of a process for determining the service status of a PostgreSQL database provided by an embodiment of the present invention;

[0067] Figure 6 A schematic diagram of the structure of a device for determining the service status of a PostgreSQL database provided by an embodiment of the present invention;

[0068] Figure 7 A schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0069] To make the objectives, technical solutions, and advantages of the present invention more apparent, the present invention will be further described in detail below with reference to the accompanying drawings. It is apparent that the embodiments described are only some, not all, of the present invention. All other embodiments derived by persons of ordinary skill in the art based on the embodiments of the present invention without creative effort are intended to fall within the scope of protection of the present invention.

[0070] In order to identify whether a PostgreSQL database is in a suspended state, embodiments of the present invention provide a system, method, apparatus, device, and medium for determining the service state of a PostgreSQL database.

[0071] Example 1:

[0072] Figure 1 A schematic diagram of a system for determining the service status of a PostgreSQL database provided in an embodiment of the present invention is provided. The system 100 comprises: a first device 101 and a second device 102, wherein both the first device 101 and the second device 102 are deployed with a PostgreSQL database;

[0073] The first device 101 is configured to obtain a pre-stored target IP address of the second device 102, send a ping packet to the target IP address, and if a first response message returned by the target IP address is received, send a pre-defined executable Structured Query Language (SQL) statement to the target IP address; if a second response message returned by the target IP address is received that is unexecutable, determine that the PostgreSQL database deployed in the second device 102 is in a suspended state;

[0074] The second device 102 is used to receive the ping data packet sent by the first device 101, send the first response information to the first device 101, receive the SQL statement sent by the first device 101, and send the second response information to the first device 101 if the SQL statement cannot be executed.

[0075] In order to identify whether a PostgreSQL database is in a suspended state, an embodiment of the present invention provides a PostgreSQL database service status determination system 100, which includes a first device 101 and a second device 102, wherein the first device 101 and the second device 102 are both deployed with a PostgreSQL database, wherein the first device 101 and the second device 102 can be PCs, tablet computers, smart terminals, servers, etc., and the current role of the first device 101 can be a master node role or a slave node role. When the current role of the first device 101 is the master node role, the previous role of the second device 102 is the slave node role. When the current role of the first device 101 is the slave node role, the previous role of the second device 102 is the master node role.

[0076] The PostgreSQL database is an open source relational database. In order to ensure the high availability of the cluster composed of the first device 101 and the second device 102, the data synchronization architecture of the PostgreSQL database deployed in the first device 101 and the PostgreSQL database deployed in the second device 102 both adopt the database master-slave log replication method. In the master-slave architecture of non-shared storage, master-slave log replication is a relatively mature and popular solution.

[0077] The first device 101 is used to obtain a pre-saved target IP address of the second device 102 and send a ping data packet to the target IP address, wherein the ping data packet is used to detect whether the network of the second device 102 is abnormal.

[0078] The second device 102 receives the ping packet sent by the first device 101 and, if the network of the second device 102 is normal, sends a first response message to the first device 101, wherein the first response message carries a data packet of the same size as the ping packet. Specifically, the second device 102 sends the first response message to the first device 101 within a set time period.

[0079] If the first device 101 receives the first response information returned by the target IP address, it determines that the process of the PostgreSQL database deployed in the second device 102 exists. However, even if the process of the PostgreSQL database exists, the service may not be improved, that is, the PostgreSQL database deployed in the second device 102 is in a suspended state.

[0080] In order to determine whether the PostgreSQL database deployed in the second device 102 is in a suspended state, a predefined executable SQL statement is stored in the first device 101, where the SQL statement is to detect data synchronization status information between the first device 101 and the second device 102. The SQL text is select pb, coalesce((pg_wal_lsn_diff(sent_lsn,replay_lsn) / 1024)::int,-1)diff from pg_is_in_recovery()pb left join pg_stat_replication rep on 1=1. The first device 101 sends the executable SQL statement to the target IP address.

[0081] The second device 102 receives the SQL statement sent by the first device 101 . If the second device 102 cannot execute the SQL statement, it sends a second response message indicating that the SQL statement cannot be executed to the first device 101 .

[0082] After receiving the second response information sent by the second device 102, the first device 101 determines that the PostgreSQL database deployed in the second device 102 is in a suspended state.

[0083] In the embodiment of the present invention, the first device in the system obtains a pre-saved target IP address of the second device, sends a ping data packet to the target IP address, the second device receives the ping data packet sent by the first device, and sends a first response information to the first device. If the first response information returned by the target IP address is received, it is determined that the network of the second device is normal, a pre-defined executable structured query language SQL statement is sent to the target IP address, and the SQL statement sent by the first device is received. If the SQL statement cannot be executed, the second response information is sent to the first device. If the first device receives the second response information returned by the target IP address indicating that it cannot be executed, it is determined that the PostgreSQL database deployed in the second device is in a suspended state, thereby solving the problem in the prior art that it is impossible to identify whether the PostgreSQL database is in a suspended state.

[0084] Example 2:

[0085] In order to identify whether the PostgreSQL database is in an abnormal state, based on the above embodiment, in an embodiment of the present invention, the first device 101 is further used to determine that the network abnormality of the second device 102 causes the PostgreSQL database deployed in the second device 102 to be in an abnormal state if the first response information returned by the target IP address is not received within a first set time period.

[0086] In order to identify whether the PostgreSQL database is in an abnormal state, in an embodiment of the present invention, when the second device 102 receives the ping data packet sent by the first device 101, if the network of the second device 102 is interrupted, the first response information will not be returned to the first device 101; if the network of the second device 102 is congested, the first response information will not be returned to the first device 101 within the first set time period.

[0087] Therefore, the first device 101 is also used to determine that the network of the second device 102 is abnormal if the first response information returned by the target IP address is not received within the first time period. Specifically, it may be that the network of the second device 102 is interrupted or blocked, and determine that the PostgreSQL database deployed in the second device 102 is in an abnormal state.

[0088] Example 3:

[0089] In order to determine whether the PostgreSQL database is in a normal service state, based on the above embodiments, in this embodiment of the present invention, the second device 102 is further configured to send a third response message indicating a successful execution to the first device 101 if the SQL statement is successfully executed;

[0090] The first device 101 is further configured to determine that the PostgreSQL database deployed in the second device 102 is in a normal service state if the third response information returned by the target IP address is received.

[0091] In order to determine whether the PostgreSQL database is in a normal service state, in an embodiment of the present invention, the second device 102 is also used to receive an SQL statement. If the SQL statement is executed successfully, it means that the PostgreSQL database process of the second device 102 itself exists and can provide services. Therefore, the second device 102 sends a third response information of successful execution to the first device 101.

[0092] When the first device 101 receives the third response information returned by the target IP address, it determines that the PostgreSQL database deployed in the second device 102 is in a normal service state.

[0093] Example 4:

[0094] In order to detect the slave node at the master node, on the basis of the above embodiments, in an embodiment of the present invention, the first device 101 is also used to obtain its current role. If its current role is the master node role, if the PostgreSQL database deployed in the second device 102 is in an abnormal service state for a number of times greater than a first preset number threshold within a second set time period, the PostgreSQL database deployed in the second device 102 is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0095] In order to detect the slave node at the master node, in an embodiment of the present invention, the first device 101 is also used to obtain the target permission of its current PostgreSQL database. If the target permission is a read-write permission, its current role is determined to be the master node role. Within the second set time, the PostgreSQL database deployed in the second device 102 is detected to see if it is in an abnormal service state, wherein the time of each rotation detection is pre-set, for example, it can be 30 seconds, 20 seconds, etc. The second set time is also pre-set, and the second set time is related to the number of rotation detections. A preset number of rotation detections can be performed within the second set time. For example, when the rotation detection time is 30 seconds, the second set time is 90 seconds, and the slave node can be subjected to three rotation detections.

[0096] The first device 101 determines the number of times that the PostgreSQL database deployed on the second device 102 is detected to be in an abnormal service state continuously within a second set time period. If the number of times is greater than a first preset threshold, the first device 101 determines that a failure has occurred in the PostgreSQL database deployed on the second device 102, and controls the second device 102 to perform abnormal recovery. The abnormal service state includes a suspended state and an abnormal state.

[0097] For example, if the first device 101 detects that the PostgreSQL database deployed on the second device 102 is in an abnormal service state three times in a row, which is greater than a first preset threshold of two times, the first device 101 determines that the PostgreSQL database deployed on the second device 102 has failed, and controls the second device 102 to perform abnormal recovery. Each detection lasts 30 seconds.

[0098] In order to control the slave node to perform abnormal recovery at the master node, based on the above embodiments, in an embodiment of the present invention, the first device 101 is specifically configured to send an incremental recovery instruction to the target IP address; if first feedback information indicating successful incremental recovery is received from the target IP address, it is determined that the second device 102 has successfully recovered from the abnormality; if second feedback information indicating failed incremental recovery is received from the target IP address, a full recovery instruction is sent to the target IP address; if third feedback information indicating successful full recovery is received from the target IP address, it is determined that the second device 102 has successfully recovered from the abnormality; if fourth feedback information indicating failed full recovery is received, it is determined that the second device 102 has failed to recover from the abnormality;

[0099] The second device 102 is further used to receive the incremental recovery instruction and perform incremental recovery, and send the first feedback information to the first device 101 if the incremental recovery is successful, and send the second feedback information to the first device 101 if the incremental recovery fails; receive the full recovery instruction and perform full recovery, and send the third feedback information to the first device 101 if the full recovery is successful, and send the fourth feedback information to the first device 101 if the full recovery fails.

[0100] To recover from an abnormality of a slave node, in an embodiment of the present invention, the first device 101 in the master node role sends an incremental recovery instruction to the target IP address. The incremental recovery uses the pg_rewind tool provided by Postgresql, that is, sending the pg_rewind instruction to the target address.

[0101] After receiving the incremental recovery instruction, the second device 102 performs incremental recovery. If the incremental recovery is successful, the first feedback information of the incremental recovery success is sent to the first device 101. If the incremental recovery fails, the second feedback information of the incremental recovery failure is sent to the first device 101.

[0102] If the first device 101 receives the first feedback information indicating that the incremental recovery is successful returned by the target IP address, it determines that the second device 102 has recovered abnormally successfully; if it receives the second feedback information indicating that the incremental recovery has failed returned by the target IP address, it determines that the incremental recovery of the second device 102 has failed.

[0103] After determining that the incremental recovery of the second device 102 has failed, the first device 101 further sends a full recovery instruction to the target IP address to restore the second device 102 abnormally. The full recovery uses the pg_basebackup tool provided by the Postgresql database, that is, sending the pg_basebackup instruction to the target address.

[0104] After receiving the full recovery instruction, the second device 102 performs a full recovery. If the full recovery is successful, the third feedback information of the full recovery success is sent to the first device 101. If the full recovery fails, the fourth feedback information of the full recovery failure is sent to the first device 101.

[0105] If the first device 101 receives the third feedback information indicating that full recovery is successful returned from the target IP address, it determines that the second device 102 has recovered successfully abnormally. If it receives the fourth feedback information indicating that full recovery has failed, it determines that the second device 102 has recovered failed abnormally.

[0106] The following describes a method for recovering a slave node according to an embodiment of the present invention through a specific embodiment. Figure 2 A schematic diagram of a slave node recovery provided by an embodiment of the present invention is shown in FIG. Figure 2 As shown, the first device in the master node role sends an incremental recovery pg_rewind command to the second device in the slave node role, and receives recovery result feedback returned by the second device. If the recovery result is that the incremental recovery fails, the full recovery pg_basebackup command is sent to the second device, and the recovery result feedback returned by the second device is received.

[0107] Example 5:

[0108] In order to detect the master node at the slave node, on the basis of the above embodiments, in an embodiment of the present invention, the first device 101 is also used to obtain its own current role. If its current role is a slave node role, if the PostgreSQL database deployed in the second device 102 is in an abnormal service state for a number of times greater than a second preset number threshold within a third set time period, its own current role is switched to a master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0109] In order to detect the master node from the slave node, in an embodiment of the present invention, the first device 101 is also used to obtain the target permission of its current PostgreSQL database. If the target permission is a readable permission, its current role is determined to be a slave node role. Within the third set time, the PostgreSQL database deployed in the second device 102 is detected to see if it is in an abnormal service state. The time for each round-robin detection is pre-set, for example, it can be 3 seconds, 4 seconds, etc. The third set time is also pre-set. The third set time is related to the number of round-robin detections. A preset number of round-robin detections can be performed within the third set time. For example, when the round-robin detection time is 3 seconds, the second set time is 60 seconds, and the slave node can be subjected to 20 round-robin detections.

[0110] The first device 101 determines the number of times that the PostgreSQL database deployed on the second device 102 is detected to be in an abnormal service state continuously within a third set time period. If the number of times is greater than a second preset threshold, the first device 101 determines that a failure has occurred in the PostgreSQL database deployed on the second device 102, and controls the second device 102 to perform abnormal recovery. The abnormal service state includes a suspended state and an abnormal state.

[0111] For example, the first device 101 detects that the PostgreSQL database deployed in the second device 102 is in an abnormal service state for 20 consecutive times, which is greater than the first preset threshold of 19 times, and then determines that the PostgreSQL database deployed in the second device 102 has a fault, so the current role of the second device 102 itself is switched to the master node role.

[0112] Example 6:

[0113] In order to perform dual-master recovery, in an embodiment of the present invention, the first device 101 is also used to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device 102 is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period. If the first response information returned by the target IP address is received within the first set time period, it is determined that the network of the second device 102 has recovered and dual-master recovery is performed.

[0114] In order to perform dual-master recovery, in an embodiment of the present invention, the first device 101 determines the number of times that the PostgreSQL database deployed in the second device 102 is detected to be in an abnormal state continuously within a third set time period. If the number is greater than the third preset time threshold, it is determined that the network anomaly of the second device 102 is determined to be faulty. In order to determine whether the network anomaly of the second device 102 is restored, the first device 101 sends a ping data packet to the target IP address. If the first response information returned by the target IP address is received within the first set time period, it is determined that the network of the second device 102 is restored and dual-master recovery is performed.

[0115] Example 7:

[0116] In order to determine the new master node, in the embodiment of the application, any one of the first device 101 and the second device 102 is configured to send the first data amount, the first startup time and the first IP address in the PostgreSQL database of the device to the other device, and receive the second data amount, the second startup time and the second IP address in the PostgreSQL database sent by the other device. If the first data amount is greater than the second data amount, it is determined that the corresponding role of the device is the master node role, and the corresponding role of the other device is the slave node role. If the first data amount is equal to the second data amount, and the first startup time is earlier than the second startup time, it is determined that the corresponding role of the device is the master node role, and the corresponding role of the other device is the slave node role. If the first data amount is equal to the second data amount, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, it is determined that the corresponding role of the device is the master node role, and the corresponding role of the other device is the slave node role.

[0117] In order to determine the new master node, in the embodiment of the application, the first device 101 is configured to send the first data amount, the first startup time and the first IP address in the PostgreSQL database of the device to the second device 102, and receive the second data amount, the second startup time and the second IP address in the PostgreSQL database sent by the second device 102.

[0118] If the first data amount is greater than the second data amount, it is determined that the corresponding role of the first device 101 is the master node role, and the corresponding role of the second device 102 is the slave node role. If the first data amount is equal to the second data amount, and the first startup time is earlier than the second startup time, it is determined that the corresponding role of the first device 101 is the master node role, and the corresponding role of the second device 102 is the slave node role. If the first data amount is equal to the second data amount, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, it is determined that the corresponding role of the first device 101 is the master node role, and the corresponding role of the second device 102 is the slave node role.

[0119] Meanwhile, the second device 102 is configured to determine that the first device 101 itself corresponds to a master node role and the second device 102 corresponds to a slave node role if the first data amount is greater than the second data amount; determine that the first device 101 itself corresponds to a master node role and the second device 102 corresponds to a slave node role if the first data amount is equal to the second data amount and the first start time is earlier than the second start time; and determine that the first device 101 itself corresponds to a master node role and the second device 102 corresponds to a slave node role if the first data amount is equal to the second data amount, the first start time is equal to the second start time, and the first IP address is less than the second IP address.

[0120] The dual-master recovery process of the embodiment of the application is described below through a specific example, Figure 3 A schematic diagram of the dual-master recovery process provided by the embodiment of the application is shown in Figure 3 as follows:

[0121] The first device is any one of the two devices in the master node role, and the second device is the other device; the first device sends a detection instruction to the second device to detect whether the second device is a device in the master node role, receives feedback of a detection result of the second device, and determines that the first device itself and the second device itself are dual masters if it is determined that the second device is a device in the master node role.

[0122] Meanwhile, the second device sends a detection instruction to the first device to detect whether the first device is a device in the master node role, receives feedback of a detection result of the first device, and determines that the second device itself and the first device itself are dual masters if it is determined that the first device is a device in the master node role.

[0123] Therefore, any one of the first device and the second device performs dual-master recovery, determines that the second device is a device in a new master node role and itself is a device in a slave node role according to a pre-stored rule for selecting dual masters. The rule for selecting dual masters is that if the first data amount is greater than the second data amount, the device with the first data amount corresponds to a master node role and the device with the second data amount corresponds to a slave node role; if the first data amount is equal to the second data amount and the first start time is earlier than the second start time, the device with the first start time corresponds to a master node role and the device with the second start time corresponds to a slave node role; and if the first data amount is equal to the second data amount, the first start time is equal to the second start time, and the first IP address is less than the second IP address, the device with the first IP address corresponds to a master node role and the device with the second IP address corresponds to a slave node role.

[0124] Example 8:

[0125] In order to ensure data security, on the basis of the above embodiments, in an embodiment of the present invention, any one of the first device 101 and the second device 102 is used to synchronously store the cluster information in its own high-availability service layer in its own Consul node, wherein the cluster information includes master-slave node information, configuration information, master-slave log synchronization information, etc. The two Consul nodes in the first device 101 and the second device 102 form a distributed storage cluster, and use the data center replication function (consul-replication) for real-time data synchronization.

[0126] Example 9:

[0127] The following describes a system for determining the service status of a PostgreSQL database according to an embodiment of the present invention through a specific embodiment. Figure 4 The overall architecture diagram of a service status determination system for a PostgreSQL database provided by an embodiment of the present invention is as follows: Figure 4 As shown, the service status determination system of the PostgreSQL database includes a master node device and a slave node device. The master node device and the slave node device are both connected to the application layer of the client. The PostgreSQL database in the master node device is the master database, and the PostgreSQL database in the slave node device is the slave database.

[0128] Both the master node device and the slave node device include a database instance layer, a distributed storage layer, and a high-availability service layer.

[0129] The database instance layer consists of two Postgresql database nodes forming a dual-machine cluster in master-slave data synchronization mode, which is constructed through the streaming replication mechanism of the Postgresql database.

[0130] The distributed storage layer consists of two data centers (Consul). At the same time, the data center replication function (consul-replication) is used between the two consul centers to synchronize data in real time to ensure data consistency.

[0131] The high-availability service layer consists of three main parts: the cluster monitoring layer, the SQL parsing layer, and the VIP service layer. The cluster monitoring layer is responsible for cluster information maintenance, cluster health checks, failover, recovery management, and data synchronization management. Cluster information includes the current master node address, slave node address, virtual IP address, external service port, and master-slave synchronization status. This information is obtained and written by the monitoring layer during health checks.

[0132] The SQL parsing layer is responsible for completing the read-write separation function. When calling the SQL parsing module, if it is only for data reading and the current status of the slave node is normal, the traffic will be directed to the slave node. Otherwise, the master node will still be accessed.

[0133] The VIP service layer regularly checks the binding status of the VIP. If the cluster status is normal, it binds the VIP to the currently normal master node, propagates the VIP packet outward, and releases the previous connection on the slave node.

[0134] Example 9:

[0135] Figure 5 A process diagram of a method for determining the service status of a PostgreSQL database provided by an embodiment of the present invention is shown as follows: Figure 4 As shown, the process includes the following steps:

[0136] S501: Acquire a pre-saved target IP address of the second device.

[0137] S502: Send a ping packet to the target IP address. If a first response message returned by the target IP address is received, send a predefined executable structured query language SQL statement to the target IP address. If a second response message that cannot be executed is received and returned by the target IP address, determine that the PostgreSQL database deployed in the second device is in a suspended state.

[0138] Furthermore, the method further comprises:

[0139] If the first response information returned by the target IP address is not received within the first set time period, it is determined that the network abnormality of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state.

[0140] Furthermore, the method further comprises:

[0141] If the third response information returned by the target IP address is received, it is determined that the PostgreSQL database deployed in the second device is in a normal service state.

[0142] Furthermore, the method further comprises:

[0143] Get your current role;

[0144] If its current role is the master node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold;

[0145] If yes, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the hang state and the abnormal state.

[0146] Further, the controlling the PostgreSQL database deployed in the second device to perform abnormal recovery includes:

[0147] sending an incremental recovery instruction to the target IP address;

[0148] If first feedback information returned by the target IP address indicates that the incremental recovery is successful, it is determined that the second device is successfully recovered from the abnormality.

[0149] If second feedback information returned by the target IP address indicates that the incremental recovery fails, a full recovery instruction is sent to the target IP address; if third feedback information returned by the target IP address indicates that the full recovery is successful, it is determined that the second device is successfully recovered from the abnormality.

[0150] If fourth feedback information indicates that the full recovery fails, it is determined that the second device fails to recover from the abnormality.

[0151] Further, the method further includes:

[0152] obtaining a current role of itself;

[0153] If the current role of itself is a slave node role, it is determined whether the number of times that the PostgreSQL database deployed in the second device is continuously in an abnormal service state within a third set time length is greater than a second preset number threshold.

[0154] If yes, the current role of itself is switched to a master node role, wherein the abnormal service state includes the hang state and the abnormal state.

[0155] Further, the method further includes:

[0156] If the number of times that the PostgreSQL database deployed in the second device is continuously in the abnormal state within the third set time length is greater than the second preset number threshold, the ping data packet is sent to the target IP address; if the first response information returned by the target IP address is received within the first set time length, it is determined that the second device is recovered from the network, and dual-master recovery is performed.

[0157] Further, the performing dual-master recovery includes:

[0158] Send the first data amount, the first start time and the first IP address in the self's PostgreSQL database to another device, and receive the second data amount, the second start time and the second IP address in the PostgreSQL database sent by another device, if the first data amount is greater than the second data amount, determine that the self's corresponding role is the master node role, and the corresponding role of another device is the slave node role; if the first data amount is equal to the second data amount, and the first start time is earlier than the second start time, determine that the self's corresponding role is the master node role, and the corresponding role of another device is the slave node role; if the first data amount is equal to the second data amount, the first start time is equal to the second start time, and the first IP address is less than the second IP address, determine that the self's corresponding role is the master node role, and the corresponding role of another device is the slave node role.

[0159] Embodiment 9:

[0160] On the basis of each of the above embodiments, Figure 6 A structural schematic diagram of a PostgreSQL database service state determination device provided by the embodiment of the application is shown in the figure, and the device comprises: Figure 6 As shown in the figure, the device comprises:

[0161] The acquisition module 601 acquires the target IP address of the second device pre-stored;

[0162] The determination module 602 is configured to send a ping data packet to the target IP address, if the first response information returned by the target IP address is received, send a pre-defined executable structured query language (SQL) statement to the target IP address, if the second response information returned by the target IP address that cannot be executed is received, determine that the PostgreSQL database deployed in the second device is in a pseudo-death state.

[0163] Further, the determination module is further configured to, if the first response information returned by the target IP address is not received within a first set time length, determine that the PostgreSQL database deployed in the second device is in an abnormal state due to network exception of the second device.

[0164] Further, the determination module is further configured to, if the third response information returned by the target IP address is received, determine that the PostgreSQL database deployed in the second device is in a normal service state.

[0165] Further, the acquisition module is further configured to acquire the current role of the self;

[0166] The determination module is also used to determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold if its current role is the master node role; if so, control the PostgreSQL database deployed in the second device to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0167] Furthermore, the determination module is specifically used to send an incremental recovery instruction to the target IP address; if a first feedback message indicating a successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if a second feedback message indicating a failed incremental recovery is received from the target IP address, a full recovery instruction is sent to the target IP address; if a third feedback message indicating a successful full recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if a fourth feedback message indicating a failed full recovery is received, it is determined that the abnormal recovery of the second device is unsuccessful.

[0168] Furthermore, the acquisition module is also used to obtain its own current role;

[0169] The determination module is also used to determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time period is greater than a second preset number threshold if its current role is a slave node role; if so, switch its current role to a master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0170] Furthermore, the determination module is specifically used to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period, and if the first response information returned by the target IP address is received within the first set time period, determine that the network of the second device is restored and perform dual-master recovery.

[0171] Furthermore, the determination module is specifically used to send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, the role corresponding to itself is determined to be the master node role, and the role corresponding to the other device is the slave node role.

[0172] Example 10:

[0173] Figure 7 FIG. 1 is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. Based on the above embodiments, an embodiment of the present invention further provides an electronic device, such as Figure 7 As shown, it includes: a processor 701 , a communication interface 702 , a memory 703 and a communication bus 704 , wherein the processor 701 , the communication interface 702 and the memory 703 communicate with each other via the communication bus 704 .

[0174] The memory 703 stores a computer program. When the program is executed by the processor 701, the processor 701 performs the following steps:

[0175] Obtain the pre-saved target IP address of the second device.

[0176] A ping packet is sent to the target IP address. If a first response message is received from the target IP address, a predefined executable structured query language SQL statement is sent to the target IP address. If a second response message that cannot be executed is received from the target IP address, it is determined that the PostgreSQL database deployed in the second device is in a suspended state.

[0177] Furthermore, the processor 701 is further configured to determine that a network anomaly of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state if the first response information returned by the target IP address is not received within a first set time period.

[0178] Furthermore, the processor 701 is further configured to determine that the PostgreSQL database deployed in the second device is in a normal service state if the third response information returned by the target IP address is received.

[0179] Furthermore, the processor 701 is further configured to obtain its current role;

[0180] If its current role is the master node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold;

[0181] If so, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0182] Furthermore, the processor 701 is specifically configured to control the PostgreSQL database deployed in the second device to perform abnormal recovery, including:

[0183] Sending an incremental recovery instruction to the target IP address;

[0184] If first feedback information indicating successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful;

[0185] If a second feedback message indicating that the incremental recovery failed is received from the target IP address, a full recovery instruction is sent to the target IP address; if a third feedback message indicating that the full recovery was successful is received from the target IP address, it is determined that the abnormal recovery of the second device is successful;

[0186] If fourth feedback information indicating full recovery failure is received, it is determined that abnormal recovery of the second device has failed.

[0187] Furthermore, the processor 701 is further configured to obtain its current role;

[0188] If its current role is a slave node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time length is greater than a second preset number threshold;

[0189] If so, the current role of the node is switched to the master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0190] Furthermore, the processor 701 is also used to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period, and if the first response information returned by the target IP address is received within the first set time period, determine that the network of the second device is restored, and perform dual-master recovery.

[0191] Furthermore, the processor 701 is specifically configured to perform dual-active recovery, including:

[0192] Send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role.

[0193] The communication bus mentioned in the electronic device mentioned above may be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus. This communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one thick line is used in the figure, but this does not mean that there is only one bus or only one type of bus.

[0194] The communication interface 702 is used for communication between the electronic device and other devices.

[0195] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk memory. Alternatively, the memory may be at least one storage device located away from the processor.

[0196] The above-mentioned processor can be a general-purpose processor, including a central processing unit, a network processor (NP), etc.; it can also be a digital signal processing processor (DSP), an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component, etc.

[0197] Example 8:

[0198] Based on the above embodiments, an embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program executable by a processor. When the program is executed on the processor, the processor implements the following steps:

[0199] Obtain the pre-saved target IP address of the second device.

[0200] A ping packet is sent to the target IP address. If a first response message is received from the target IP address, a predefined executable structured query language SQL statement is sent to the target IP address. If a second response message that cannot be executed is received from the target IP address, it is determined that the PostgreSQL database deployed in the second device is in a suspended state.

[0201] Furthermore, the method further comprises:

[0202] If the first response information returned by the target IP address is not received within the first set time period, it is determined that the network abnormality of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state.

[0203] Furthermore, the method further comprises:

[0204] If the third response information returned by the target IP address is received, it is determined that the PostgreSQL database deployed in the second device is in a normal service state.

[0205] Furthermore, the method further comprises:

[0206] Get your current role;

[0207] If its current role is the master node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold;

[0208] If so, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state.

[0209] Furthermore, controlling the PostgreSQL database deployed in the second device to perform abnormal recovery includes:

[0210] Sending an incremental recovery instruction to the target IP address;

[0211] If first feedback information indicating successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful;

[0212] If a second feedback message indicating that the incremental recovery failed is received from the target IP address, a full recovery instruction is sent to the target IP address; if a third feedback message indicating that the full recovery was successful is received from the target IP address, it is determined that the abnormal recovery of the second device is successful;

[0213] If fourth feedback information indicating full recovery failure is received, it is determined that abnormal recovery of the second device has failed.

[0214] Furthermore, the method further comprises:

[0215] Get your current role;

[0216] If its current role is a slave node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time length is greater than a second preset number threshold;

[0217] If so, the current role of the node is switched to the master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

[0218] Furthermore, the method further comprises:

[0219] If the PostgreSQL database deployed in the second device is in the abnormal state for a number of consecutive times greater than the second preset number threshold within the third set time period, the ping data packet is sent to the target IP address. If the first response information returned by the target IP address is received within the first set time period, it is determined that the network of the second device has recovered, and dual-master recovery is performed.

[0220] Furthermore, the dual-active recovery includes:

[0221] Send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role.

[0222] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0223] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the present application. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0224] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0225] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0226] Obviously, those skilled in the art may make various changes and modifications to this application without departing from the spirit and scope of this application. Thus, if these modifications and variations of this application fall within the scope of the claims of this application and their equivalents, this application is intended to include these modifications and variations.

Claims

1. A service status determination system for a PostgreSQL database, characterized in that: The system includes: a first device and a second device, wherein the first device and the second device are both deployed with a PostgreSQL database; The first device is configured to obtain a pre-stored target IP address of the second device, send a ping packet to the target IP address, and if a first response message returned by the target IP address is received, send a pre-defined executable structured query language SQL statement to the target IP address; if a second response message returned by the target IP address is received that is unexecutable, determine that the PostgreSQL database deployed in the second device is in a suspended state; The second device is configured to receive the ping data packet sent by the first device, send the first response information to the first device, receive the SQL statement sent by the first device, and send the second response information to the first device if the SQL statement cannot be executed; The first device is further configured to determine that a network anomaly of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state if the first response information returned by the target IP address is not received within a first set time period; The first device is also used to obtain its own current role. If its current role is a master node role, if the PostgreSQL database deployed in the second device is in an abnormal service state for a number of times greater than a first preset number threshold within a second set time period, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state, wherein the process of obtaining its own current role is to obtain the target permission of its own current PostgreSQL database. If the target permission is a readable and writable permission, its current role is determined to be a master node role.

2. The system according to claim 1, wherein: The second device is further configured to send a third response message indicating successful execution to the first device if the SQL statement is successfully executed; The first device is further configured to determine that the PostgreSQL database deployed in the second device is in a normal service state if the third response information returned by the target IP address is received.

3. The system according to claim 1, wherein: The first device is specifically configured to send an incremental recovery instruction to the target IP address; if first feedback information indicating a successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if second feedback information indicating a failed incremental recovery is received from the target IP address, a full recovery instruction is sent to the target IP address; if third feedback information indicating a successful full recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; if fourth feedback information indicating a failed full recovery is received, it is determined that the abnormal recovery of the second device is unsuccessful; The second device is further configured to receive the incremental recovery instruction and perform incremental recovery, and send the first feedback information to the first device if the incremental recovery is successful, and send the second feedback information to the first device if the incremental recovery fails; Receive the full recovery instruction and perform a full recovery. If the full recovery is successful, send the third feedback information to the first device; if the full recovery fails, send the fourth feedback information to the first device.

4. The system according to claim 1, wherein: The first device is also used to obtain its own current role. If its current role is a slave node role, if the PostgreSQL database deployed in the second device is in an abnormal service state for a number of times greater than a second preset number threshold within a third set time period, its own current role will be switched to a master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

5. The system according to claim 4, characterized in that The first device is further configured to send the ping data packet to the target IP address if the PostgreSQL database deployed in the second device is in the abnormal state continuously for a number of times greater than the second preset number threshold within the third set time period, and determine that the network of the second device has recovered and perform dual-master recovery if the first response information returned by the target IP address is received within the first set time period.

6. The system according to claim 5, characterized in that Any one of the first device and the second device is configured to send a first data volume, a first startup time, and a first IP address in its own PostgreSQL database to the other device, and receive a second data volume, a second startup time, and a second IP address in the PostgreSQL database sent by the other device, and if the first data volume is greater than the second data volume, determine that its corresponding role is a master node role and the corresponding role of the other device is a slave node role; If the first data volume is equal to the second data volume and the first startup time is earlier than the second startup time, determining that the role corresponding to itself is the master node role and the role corresponding to the other device is the slave node role; If the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is smaller than the second IP address, then determine that the role corresponding to itself is the master node role, and the role corresponding to the other device is the slave node role.

7. A method for determining the service status of a PostgreSQL database, characterized in that: The method comprises: Obtain a pre-saved target IP address of the second device; Sending a ping packet to the target IP address, and if a first response information returned by the target IP address is received, sending a predefined executable structured query language SQL statement to the target IP address, and if a second response information that cannot be executed is received from the target IP address, determining that the PostgreSQL database deployed in the second device is in a suspended state; If the first response information returned by the target IP address is not received within the first set time period, it is determined that the network abnormality of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state; The method further comprises: Get your current role; If its current role is the master node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold; If so, the PostgreSQL database deployed in the second device is controlled to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state, wherein the process of obtaining one's current role is to obtain the target permissions of one's current PostgreSQL database. If the target permissions are readable and writable permissions, then one's current role is determined to be the master node role.

8. The method according to claim 7, characterized in that The method further comprises: If a third response message returned by the target IP address is received, it is determined that the PostgreSQL database deployed in the second device is in a normal service state, wherein the third response message is returned after a predefined executable structured query language SQL statement is sent to the target IP address and the SQL statement is successfully executed.

9. The method according to claim 7, characterized in that The controlling the PostgreSQL database deployed in the second device to perform abnormal recovery includes: Sending an incremental recovery instruction to the target IP address; If first feedback information indicating successful incremental recovery is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; If a second feedback message indicating that the incremental recovery failed is received from the target IP address, a full recovery instruction is sent to the target IP address; if a third feedback message indicating that the full recovery was successful is received from the target IP address, it is determined that the abnormal recovery of the second device is successful; If fourth feedback information indicating full recovery failure is received, it is determined that abnormal recovery of the second device has failed.

10. The method according to claim 7, characterized in that The method further comprises: Get your current role; If its current role is a slave node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a third set time length is greater than a second preset number threshold; If so, the current role of the node is switched to the master node role, wherein the abnormal service state includes the suspended state and the abnormal state.

11. The method according to claim 10, characterized in that The method further comprises: If the PostgreSQL database deployed in the second device is in the abnormal state for a number of consecutive times greater than the second preset number threshold within the third set time period, the ping data packet is sent to the target IP address. If the first response information returned by the target IP address is received within the first set time period, it is determined that the network of the second device has recovered, and dual-master recovery is performed.

12. The method according to claim 11, characterized in that The dual-active recovery includes: Send the first data volume, first startup time and first IP address in its own PostgreSQL database to another device, and receive the second data volume, second startup time and second IP address in the PostgreSQL database sent by the other device. If the first data volume is greater than the second data volume, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, and the first startup time is earlier than the second startup time, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role; if the first data volume is equal to the second data volume, the first startup time is equal to the second startup time, and the first IP address is less than the second IP address, determine that its corresponding role is the master node role, and the role corresponding to the other device is the slave node role.

13. A device for determining the service status of a PostgreSQL database, characterized in that: The device comprises: An acquisition module, configured to acquire a pre-saved target IP address of a second device; A determination module is configured to send a ping data packet to the target IP address, and if a first response message returned by the target IP address is received, send a predefined executable structured query language SQL statement to the target IP address; if a second response message that cannot be executed is received and returned by the target IP address, determine that the PostgreSQL database deployed in the second device is in a suspended state; if the first response message returned by the target IP address is not received within a first set time period, determine that the network abnormality of the second device causes the PostgreSQL database deployed in the second device to be in an abnormal state; obtain its own current role; if its own current role is a master node role, determine whether the number of times the PostgreSQL database deployed in the second device is in an abnormal service state continuously within a second set time period is greater than a first preset number threshold; if so, control the PostgreSQL database deployed in the second device to perform abnormal recovery, wherein the abnormal service state includes the suspended state and the abnormal state, wherein the process of obtaining its own current role is to obtain the target permission of its own current PostgreSQL database, and if the target permission is a readable and writable permission, determine that its own current role is a master node role.

14. An electronic device, characterized in that: include: A processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other via the communication bus; The memory stores a computer program, which, when executed by the processor, causes the processor to perform the steps of the method for determining the service status of a PostgreSQL database according to any one of claims 7 to 12.

15. A computer-readable storage medium, characterized in that It stores a computer program executable by a processor. When the program runs on the processor, it enables the processor to perform the steps of the service status determination method of the PostgreSQL database according to any one of claims 7 to 12.

Images