Database logic replication slot updating method, product and equipment

Abstract

The invention provides a database logic replication slot updating method, a product and equipment. The method comprises the steps of obtaining a to-be-processed target pre-writing log and determining a log processing check point; determining a validity judgment log serial number and a reference log serial number of a target node corresponding to the target pre-writing log at the log processing check point; responding to standby machine confirmation information in the target pre-writing log, and generating a transaction submission point corresponding to the standby machine confirmation information; judging whether the logic copy slot meets a preset updating condition or not according to the validity judgment log serial number in the transaction submission point; and if yes, updating the restart log serial number corresponding to the logic copy slot into the reference log serial number of the log processing check point corresponding to the transaction submission point. Through the method, each submission operation can be replaced by analyzing the log serial number corresponding to the oldest activity transaction, so that the updating efficiency of the database logic replication slot is greatly improved, and the resource consumption of the database is reduced.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, product, and device for updating database logical replication slots. Background Technology

[0002] In current common database technologies, a logical slot only issues each change once. The current position of each slot is only persisted at checkpoints. Therefore, if a crash occurs, a slot might revert to an earlier log sequence number, causing the server to resend the most recent changes upon restart. The logical decoding client is responsible for avoiding side effects caused by processing the same message multiple times. Clients expect to record the latest log sequence number they see during decoding and skip any duplicate data decoded from that sequence number. The log sequence number of a logical replication slot is only advanced when the standby database confirms a transaction. Standby confirmation means that all transactions committed before this transaction have been persisted to disk and will not require the database to perform logical decoding again. Therefore, it is only necessary to obtain the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction at the time of the transaction's commit (the oldest active transaction). Advancing to this log sequence number is safe. However, recording the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction at every transaction commit would severely impact efficiency. Summary of the Invention

[0003] In view of the above problems, the present invention proposes a method, product and device for updating database logical replication slots that overcomes or at least partially solves the above problems.

[0004] One objective of this invention is to improve the update efficiency of database logical replication slots.

[0005] A further objective of this invention is to reduce the resource consumption of the database.

[0006] Specifically, the present invention provides a method for updating a database logical replication slot, comprising:

[0007] Obtain the target write-ahead logs to be processed and determine the log processing checkpoints;

[0008] At the log processing checkpoint, the validity decision log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log are determined. The validity decision log sequence number is used to determine whether to update the logical replication slot corresponding to the target node. The baseline log sequence number is the log sequence number corresponding to the metadata snapshot that the oldest active transaction in the target node depends on.

[0009] In response to the standby confirmation information in the target write-ahead log, a transaction commit point corresponding to the standby confirmation information is generated. The standby confirmation information is used to instruct the standby to complete the disk write of the corresponding active transaction.

[0010] At the transaction commit point, the logical replication slot is determined based on the validity check log sequence number to see if it meets the preset update conditions.

[0011] If so, the restart log sequence number corresponding to the logical replication slot will be updated to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point. The restart log sequence number is the replication starting point when the replication process restarts.

[0012] Optionally, when the database contains only a single target node, the steps for determining whether the logical replication slot meets the preset update conditions based on the validity determination log sequence number at the transaction commit point include:

[0013] Determine the transactions that have been written to disk corresponding to the standby machine confirmation information;

[0014] Determine if the log sequence number of a transaction that has been written to disk is greater than the validity check log sequence number;

[0015] If the log sequence number of a transaction that has been written to disk is greater than the validity determination log sequence number, the determination logic replication slot meets the preset update conditions.

[0016] Optionally, when the database is a cluster, the database cluster includes multiple nodes, and each node corresponds to a logical replication slot;

[0017] When the database is clustered, the steps for determining whether the logical replication slot meets the preset update conditions based on the validity check log sequence number at the transaction commit point include:

[0018] Determine the transactions that have been written to disk corresponding to the standby machine confirmation information;

[0019] Determine if the log sequence number of a transaction that has been written to disk is greater than the validity check log sequence number;

[0020] If the log sequence number of a transaction that has been committed to disk is greater than the validity determination log sequence number, determine whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster.

[0021] If the baseline log sequence number of the target node is less than the baseline log sequence number of other nodes in the database cluster, the logical replication slot is determined to meet the preset update conditions.

[0022] Optionally, the steps to determine whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster include:

[0023] Obtain the first global sequence number of the baseline log sequence number of the log processing checkpoint in the target write-ahead log, and the second global sequence number of the baseline log sequence number of other nodes in the database cluster in the target write-ahead log;

[0024] Determine if the first global sequence number is less than the second global sequence number;

[0025] If so, the baseline log sequence number of the target node is determined to be less than the baseline log sequence number of other nodes in the database cluster.

[0026] Optionally, the steps for determining the validity check log sequence number and baseline log sequence number of the target node corresponding to the target write-ahead log at the log processing checkpoint include:

[0027] Determine if any active transactions that have not been written to disk exist at the log processing checkpoint;

[0028] If there are no active transactions that have not been written to disk at the log processing checkpoint, the log sequence number corresponding to the log processing checkpoint is used as both the validity determination log sequence number and the baseline log sequence number.

[0029] Optionally, after determining whether there are any active transactions that have not been written to disk at the log processing checkpoint, the following steps may also be included:

[0030] If there are no active transactions that have not been written to disk at the log processing checkpoint, the current restart decoding log sequence number in the preset reorder buffer is set to the log sequence number corresponding to the log processing checkpoint.

[0031] Optionally, after determining whether there are any active transactions that have not been written to disk at the log processing checkpoint, the following steps may also be included:

[0032] If there are active transactions that have not been written to disk at the log processing checkpoint, the log sequence number corresponding to the log processing checkpoint is used as the validity judgment log sequence number, and the log sequence number corresponding to the metadata snapshot on which the oldest active transaction in the target node depends is used as the base log sequence number.

[0033] Optionally, after updating the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point, the method further includes:

[0034] Clear the validity judgment log sequence number and the baseline log sequence number corresponding to the log processing checkpoint.

[0035] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the database logical replication slot update method described above.

[0036] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the steps of the database logical replication slot update method of any of the above.

[0037] The database logical replication slot update method of the present invention first obtains the target write-ahead log to be processed and determines the log processing checkpoint; then, at the log processing checkpoint, it determines the validity judgment log sequence number and the base log sequence number of the target node corresponding to the target write-ahead log. The validity judgment log sequence number is used to determine whether to update the logical replication slot corresponding to the target node, and the base log sequence number is the log sequence number corresponding to the metadata snapshot on which the oldest active transaction in the target node depends; next, in response to the standby confirmation information in the target write-ahead log, it generates a transaction commit point corresponding to the standby confirmation information, which is used to instruct the standby to complete the disk write of the corresponding active transaction; at the transaction commit point, it determines whether the logical replication slot meets the preset update conditions based on the validity judgment log sequence number; if so, it updates the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point, and the restart log sequence number is the replication start point when the replication process restarts. This method can replace each commit operation by analyzing the log sequence number corresponding to the oldest active transaction, thereby greatly increasing the update efficiency of the database logical replication slot and reducing database resource consumption.

[0038] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0039] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0040] Figure 1 This is a flowchart illustrating a method for updating a database logical replication slot according to an embodiment of the present invention;

[0041] Figure 2 This is a flowchart illustrating a method for updating a single-machine database logical replication slot according to an embodiment of the present invention.

[0042] Figure 3 This is a schematic diagram illustrating the execution process of an update method for a single-machine database logical replication slot according to an embodiment of the present invention;

[0043] Figure 4This is a flowchart illustrating a method for updating a logical replication slot in a clustered database according to an embodiment of the present invention.

[0044] Figure 5 This is a schematic diagram illustrating the execution process of an update method for a logical replication slot in a clustered database according to an embodiment of the present invention.

[0045] Figure 6 This is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0046] Figure 7 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0047] Figure 8 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0048] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0049] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0050] In a logical replication environment, a slot represents a stream of changes that can be replayed on the client in the order they were generated on the origin server. Each stream transmits a sequence of changes from a single database.

[0051] In normal operation, a logical slot only issues each change once. The current position of each slot is only persisted at checkpoints; therefore, in the event of a crash, a slot might revert to an earlier Log Sequence Number (LSN), causing the server to resend the most recent changes upon restart. Logical decoding clients are responsible for avoiding side effects caused by processing the same message multiple times. Clients may want to record the latest LSN they see during decoding and skip any duplicate data decoded from that LSN or requests using replication protocols, rather than letting the server determine the starting point.

[0052] To facilitate the implementation of logical replication solutions for logical decoding, this invention constructs a replication solution in which a crucial component is the safe tracking of replay progress. When the logical replication process or the entire database crashes, it's necessary to determine where the data was successfully replicated. For logical replication slots to safely track replication progress, it's essential to ensure that dependent historical data versions are not reclaimed, i.e., that dependent write-ahead logging (WAL) is not recycled.

[0053] Specifically, the principle of WAL log advancement in this invention is generally that the LSN of the logical replication slot can only be advanced when the standby database confirms the transaction. The standby database confirming the transaction means that the transaction has been committed to disk on the standby database, and the database does not need to perform logical decoding of this transaction. Therefore, it only needs to obtain the LSN corresponding to the metadata snapshot relied upon by the oldest active transaction at the time of the transaction's commit. Advancing only to this LSN is safe. However, if the LSN corresponding to the metadata snapshot relied upon by the oldest active transaction is recorded every time an active transaction is committed, it will severely impact efficiency. Furthermore, when decoding the WAL corresponding to an active transaction, the metadata snapshot and LSN at that time are saved. Therefore, the metadata snapshot and LSN of the running transaction can be used step by step to obtain the metadata snapshot and log sequence number relied upon by the oldest active transaction.

[0054] Figure 1 This is a flowchart illustrating a method for updating a database logical replication slot according to an embodiment of the present invention, as shown below. Figure 1 As shown, the update method for the logical replication slot of the database includes at least the following steps S101 to S105.

[0055] Step S101: Obtain the target write-ahead log to be processed and determine the log processing checkpoints. The target processing checkpoints refer to the checkpoints determined during the processing of a write-ahead log in the database, based on pre-defined rules. At these checkpoints, information prior to the current checkpoint is updated. The rules for setting log processing checkpoints can be triggered periodically by the system or set through specific commands.

[0056] Step S102: At the log processing checkpoint, determine the validity decision log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log. The validity decision log sequence number is used to determine whether to update the logical replication slot corresponding to the target node, and the baseline log sequence number is the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction in the target node.

[0057] Step S103: In response to the standby confirmation information in the target write-ahead log, generate the transaction commit point corresponding to the standby confirmation information. The standby confirmation information instructs the standby server to complete the write-to-disk write of the corresponding active transaction. The standby confirmation information is generally sent when the standby server confirms the transaction. Through this information, the database can determine that the corresponding active transaction has been written to disk on the standby server. After the standby server completes the write-to-disk write, it does not require the database to perform logical decoding again. Therefore, it only needs to obtain the LSN corresponding to the oldest metadata snapshot relied upon by the active transaction at the time of the transaction commit. Only by advancing to this LSN is it safe to proceed.

[0058] Step S104: Determine whether the logical replication slot meets the preset update conditions at the transaction commit point.

[0059] Step S105: If the determination in step S104 is yes, update the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point. The restart log sequence number is the replication starting point when the replication process restarts.

[0060] It's important to note that the specific content of the preset update conditions varies depending on the database type, as the database may be a single machine or a cluster. In a logical replication environment, a slot represents a change stream, which can be replayed on the client in the order they were generated on the origin server. Each stream transmits the sequence of changes from a single database. However, in a database cluster, because each transaction can perform read and write operations, and each node has its own independent transaction segment, all of these affect metadata. Metadata snapshots are snapshots of the metadata from each RAC node, and each replication slot has a unique identifier within a database cluster. That is, a logical replication slot corresponds one-to-one with a database node. Logical replication slots remain independent between the database connections using them and are crash-safe.

[0061] Therefore, in some optional embodiments, when the database contains only a single target node, the step of determining whether the logical replication slot meets the preset update conditions based on the validity determination log sequence number at the transaction commit point can generally include: determining the transaction that has been committed to disk corresponding to the standby confirmation information; determining whether the log sequence number of the transaction that has been committed to disk is greater than the validity determination log sequence number; and determining that the logical replication slot meets the preset update conditions if the log sequence number of the transaction that has been committed to disk is greater than the validity determination log sequence number. The validity check log sequence number indicates that the metadata prior to this log sequence number has been safely synchronized. The standby server sends an acknowledgment message indicating that a transaction has been successfully written to disk. Subsequently, by parsing the standby server's acknowledgment message, the corresponding transaction that has been written to disk is located. If the log sequence number corresponding to the transaction that has been written to disk is greater than the validity check log sequence number, it means that the transactions prior to this transaction have been fully synchronized. At this point, the restart log sequence number corresponding to the logical replication slot can be advanced. The recommended approach is to update the restart log sequence number corresponding to the logical replication slot to the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point. Since the baseline log sequence number is the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction in the target node, if it is determined that advancement is possible, the database prior to the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction in the target node must have been written to disk. Therefore, it is assigned to the restart log sequence number corresponding to the logical replication slot.

[0062] If the log sequence number corresponding to a transaction that has been written to disk is less than the validity determination log sequence number, even if it can be determined that the transaction has been written to disk, there may still be some transactions that have not been written to disk between the log sequence number corresponding to the transaction that has been written to disk and the validity determination log sequence number. Therefore, it cannot be decided to advance the logical replication slot.

[0063] In some alternative embodiments, when the database is a cluster, the cluster consists of multiple nodes, each corresponding to a logical replication slot. In this case, the advancement of the logical replication slot must ensure both the relatively independent maintenance mechanism of the logical replication slots for each node in the database cluster and the correctness of the write-ahead log and global metadata during the advancement process. Because nodes may be in different transaction processing stages due to uneven load or network latency, if each node advances the replication slot independently, some nodes' replication processes may rely on metadata that has been cleaned up by other nodes. Therefore, it is necessary to ensure that the replication processes of all nodes use the same metadata baseline to avoid parsing errors caused by differences in node states. That is, logical replication slots are only allowed to advance when the oldest active transaction of all nodes does not depend on metadata prior to a certain LSN.

[0064] Specifically, when the database is a cluster, the steps for determining whether a logical replication slot meets the preset update conditions based on the validity check log sequence number at the transaction commit point generally include: identifying the transaction that has been committed to disk corresponding to the standby confirmation information; determining whether the log sequence number of the committed transaction is greater than the validity check log sequence number; if the log sequence number of the committed transaction is greater than the validity check log sequence number, determining whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster; and if the baseline log sequence number of the target node is less than the baseline log sequence number of other nodes in the database cluster, determining that the logical replication slot meets the preset update conditions.

[0065] Optionally, the step of determining whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence numbers of other nodes in the database cluster can generally include: obtaining the first global sequence number of the baseline log sequence number of the log processing checkpoint in the target write-ahead log and the second global sequence number of the baseline log sequence number of other nodes in the database cluster in the target write-ahead log; determining whether the first global sequence number is less than the second global sequence number; if so, then determining that the baseline log sequence number of the target node is less than the baseline log sequence number of other nodes in the database cluster. In this way, in a cluster scenario, through the dual mechanism of "transaction confirmation verification + cross-node baseline comparison", a balance is achieved between the safe advancement of logical replication slots and the consistency of global metadata, thereby significantly improving the reliability of logical replication in a distributed environment.

[0066] This method replaces each commit operation with the log sequence number corresponding to the oldest active transaction, significantly increasing the update efficiency of logical replication slots and reducing database resource consumption. Furthermore, it extends the logical replication slot propagation logic from a single machine to a database cluster, ensuring the correctness and security of logical replication slot propagation while maintaining minimal changes to the overall propagation logic. It also ensures that historical data versions dependent on logical decoding are not reclaimed, and dependent write-ahead log files are not reclaimed.

[0067] In some alternative embodiments, the step of determining the validity judgment log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log at the log processing checkpoint generally includes: determining whether there are any active transactions that have not been written to disk at the log processing checkpoint; if there are no active transactions that have not been written to disk at the log processing checkpoint, using the log sequence number corresponding to the log processing checkpoint as both the validity judgment log sequence number and the baseline log sequence number. If there are no active transactions that have not been written to disk at the log processing checkpoint, it indicates that all active transactions before this checkpoint have been written to disk, and there is no need to use the data before the log processing checkpoint during subsequent restarts. Therefore, the log sequence number corresponding to the log processing checkpoint can be directly used as both the validity judgment log sequence number and the baseline log sequence number, thereby reducing unnecessary operations.

[0068] Optionally, after determining whether there are any unwritten active transactions at the log processing checkpoint, the process may further include: if there are no unwritten active transactions at the log processing checkpoint, setting the current restart decoding log sequence number in the preset reordering buffer to the log sequence number corresponding to the log processing checkpoint. The current restart decoding log sequence number is the starting point of the metadata snapshot when the replication process decodes the WAL log. Setting it to the log sequence number corresponding to a log processing checkpoint without active transactions means that the decoding logic of all subsequent transactions is based on the metadata snapshot (such as table structure, permissions, etc.) of that checkpoint. Even if the source database undergoes metadata changes (such as adding fields), the replication process can still load consistent metadata from the log sequence number of that checkpoint after restarting, ensuring decoding correctness. This reduces redundant metadata caching in memory, lowers system overhead, simplifies metadata management logic, and fundamentally guarantees decoding consistency when the replication process restarts.

[0069] Optionally, after determining whether there are any unwritten active transactions at the log processing checkpoint, the process may further include: if there are unwritten active transactions at the log processing checkpoint, using the log sequence number corresponding to the log processing checkpoint as the validity determination log sequence number, and using the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction in the target node as the base log sequence number. If there are unwritten active transactions at the log processing checkpoint, it indicates that at least one local transaction is in progress, and the database has incomplete read / write operations, potentially involving metadata changes. Therefore, the base log sequence number cannot be the log sequence number corresponding to the current log processing checkpoint; instead, the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction in the target node is used as the base log sequence number, thus avoiding the reclamation of unwritten data. This ensures normal replication during subsequent restarts.

[0070] Optionally, after updating the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point, the process further includes: clearing the validity determination log sequence number and the base log sequence number corresponding to the log processing checkpoint. Since the validity determination log sequence number and the base log sequence number are temporary parameters within a single log processing checkpoint cycle, used only to determine whether the current log processing checkpoint can serve as the replication starting point, clearing them will cause the system to regenerate the parameters at the next log processing checkpoint, preventing old parameters from affecting the judgment logic of the new checkpoint.

[0071] In summary, the method of the present invention follows the theorem:

[0072] Theorem 1: The oldest active transaction at the time of transaction commit is older than the oldest active transaction of the preceding running transactions, which means that the corresponding metadata snapshot is older. Therefore, we can analyze the oldest active transaction corresponding to the running transactions instead of the actual transaction commit point.

[0073] Theorem 2: Transactions started after a running transaction can be constructed by adding the metadata snapshot of the running transaction point to the metadata changes committed later. Therefore, the LSN of the metadata snapshot that each transaction depends on is the LSN of the previously active transaction.

[0074] When decoding to an active transaction at each log processing checkpoint, the tasks that need to be completed generally include: recording the LSN of this point as the LSN of the metadata snapshot corresponding to the newly created transaction; recording the validity judgment log sequence number as the LSN of this point, and recording the base log sequence number as the LSN of the metadata snapshot that the oldest active transaction at this time depends on; setting the LSN of the metadata snapshot that the newly created transaction depends on to the LSN of the previous active transaction and saving it in the corresponding transaction.

[0075] When the standby machine confirms a transaction, it compares the LSN of the corresponding transaction commit point with the validity check log sequence number. If the validity check log sequence number is smaller, it updates the LSN of the replication slot using the base log sequence number.

[0076] To clearly illustrate the method of the present invention, two examples under different conditions are presented: one is the update method of logical replication slots in a single-machine database, and the other is the update method of logical replication slots in a database cluster.

[0077] Figure 2 This is a flowchart illustrating a method for updating a single-machine database logical replication slot according to an embodiment of the present invention, as shown below. Figure 2 As shown, the update method for the logical replication slot of the database includes at least the following steps S201 to S206.

[0078] Step S201: Obtain the target write-ahead log to be processed and determine the log processing checkpoint. For example, the system triggers a checkpoint periodically (e.g., every 50ms), obtains the currently pending WAL log, and marks the checkpoint position. At this time, the system scans for active transactions; if none exist, it records the checkpoint LSN as the reference for subsequent parameters.

[0079] Step S202: At the log processing checkpoint, determine the validity judgment log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log.

[0080] Step S203: In response to the standby confirmation information in the target write-ahead log, generate the transaction commit point corresponding to the standby confirmation information.

[0081] Step S204: Determine the transactions that have been written to disk corresponding to the standby machine confirmation information. By establishing a mapping relationship between confirmation information and specific transactions, the accuracy of status updates is ensured, thereby providing a clear judgment object for subsequent threshold comparisons.

[0082] Step S205: Determine whether the log sequence number of the transaction that has been written to disk is greater than the validity determination log sequence number.

[0083] Step S206: If the determination in step S205 is yes, update the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point. Replication slot advancement is only allowed when the transaction LSN exceeds the threshold of the validity determination log sequence number, avoiding decoding failures caused by premature metadata cleanup.

[0084] Optionally, after the logical replication slot is updated, the validity judgment log sequence number and the baseline log sequence number of the log processing checkpoint can be cleared to prevent old checkpoint parameters from interfering with the judgment logic of the new cycle, improve system stability, and prepare for the next checkpoint.

[0085] This method ensures the correctness of WAL logs and global metadata during the implementation process in a single-machine database.

[0086] To more clearly illustrate the update method for logical replication slots in a single-machine database, a specific implementation example is provided. Figure 3 As shown, Figure 3 This is a schematic diagram illustrating the execution process of an update method for a single-machine database logical replication slot according to an embodiment of the present invention. The horizontal arrow at the bottom indicates the processing of the target write-ahead log. During the sequential processing of the target write-ahead log, log processing checkpoints P1 and P3 are determined by preset rules. Furthermore, within this portion of the target write-ahead log, there are four active transactions, whose lifecycles are as follows... Figure 3As shown, E2, E4, and E5 are transaction commit points corresponding to the standby server confirmation information generated in response to different transactions. Point E2 is the transaction commit point generated upon receiving the standby server confirmation information of active transaction 2, point E4 is the transaction commit point generated upon receiving the standby server confirmation information of active transaction 3, and point E5 is the transaction commit point generated upon receiving the standby server confirmation information of active transaction 4. The transaction commit point generated by the standby server confirmation information of active transaction 1 is... Figure 3 This is not yet reflected in the data, indicating that Activity Transaction 1 only completed the backup disk transfer after point E5.

[0087] for Figure 3 In the write-ahead log, when processing to point P1, the method of this invention needs to first determine the validity judgment log sequence number and the baseline log sequence number corresponding to point P1. Since there are no active transactions before point P1, or all active transactions have been completed and written to disk, the log sequence number of point P1 is used as both the validity judgment log sequence number and the baseline log sequence number. At the same time, the current restart decoding log sequence number of the preset reordering buffer is set as the log sequence number corresponding to the log processing checkpoint as the basis for subsequent operations.

[0088] Subsequently, during processing, the standby server confirmation information for active transaction 2 was received, generating point E2. At this point, since the log sequence number of point E2 is greater than the validity determination log sequence number of point P1 (the log sequence number of point P1), it meets the preset update rules. The restart log sequence number of the logical replication slot is updated to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point, which is the log sequence number of point P1. At the same time, the validity determination log sequence number and the base log sequence number of point P1 are cleared.

[0089] As the target write-ahead log is processed, when the second log processing checkpoint P3 is reached, the validity judgment log sequence number and the baseline log sequence number are reassigned. Since there are still active transactions 1 and 3 that have not been written to disk at point P3, the log sequence number at point P3 is used as the validity judgment log sequence number. At the same time, the log sequence number corresponding to the metadata snapshot on which the oldest active transaction (i.e., active transaction 1) depends (i.e., the log sequence number at point P1) is used as the baseline log sequence number.

[0090] During subsequent processing, the standby confirmation information for active transaction 3 was received, and point E4 was generated. Figure 3 As can be seen, the starting point of activity transaction 3 is before point P3. Therefore, the log sequence number of point E4 is less than the validity judgment log sequence number of point P3 (the log sequence number of point P3), so it does not meet the preset update rules. The current validity judgment log sequence number and the baseline log sequence number remain unchanged.

[0091] During subsequent processing, the standby machine confirmation information of active transaction 4 was received, and point E5 was generated. At this time, since the log sequence number of point E5 is greater than the validity judgment log sequence number of point P3 (the log sequence number of point P3), it meets the preset update rules. The restart log sequence number of the logical replication slot is updated to the base log sequence number of the log processing checkpoint (point P3) corresponding to the transaction commit point, which is also the log sequence number of point P1. At the same time, the validity judgment log sequence number and the base log sequence number are cleared.

[0092] In this way, during the processing of the target write-ahead log, whenever replication needs to be restarted, the corresponding starting point can be found based on the restart log sequence number of the logical replication slot. Furthermore, it is not necessary to record the log sequence number corresponding to the metadata snapshot relied upon by the oldest active transaction at each transaction commit. The method of this invention only updates the restart log sequence number of the logical replication slot when the update conditions are met during the processing of the target write-ahead log. This can greatly increase the update efficiency of the database logical replication slot and ensure the correctness of the target write-ahead log and global metadata during the process.

[0093] For database clusters, during cluster-level logical replication, a single node relies on metadata snapshots from other nodes for logical decoding. However, since each node maintains its own logical replication slot, the association of metadata snapshots across nodes is ignored. Therefore, it is necessary to consider whether the advancement of the restart log sequence number of this node will affect the global metadata snapshots that other cluster nodes' transactions depend on.

[0094] Figure 4 This is a flowchart illustrating a method for updating logical replication slots in a clustered database according to an embodiment of the present invention. Figure 4 As shown, the update method for the logical replication slot of the database includes at least the following steps S401 to S407.

[0095] Step S401: Obtain the target write-ahead log to be processed and determine the log processing checkpoint.

[0096] Step S402: At the log processing checkpoint, determine the validity judgment log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log.

[0097] Step S403: In response to the standby confirmation information in the target write-ahead log, generate the transaction commit point corresponding to the standby confirmation information.

[0098] Step S404: Determine the transactions that have been written to disk corresponding to the standby machine confirmation information.

[0099] Step S405: Determine if the log sequence number of the transactions already written to disk is greater than the validity judgment log sequence number. Through threshold verification within a single node, transactions that do not meet the local update conditions are filtered out to prevent erroneous updates to logical replication slots.

[0100] Step S406: If the determination in step S405 is yes, determine whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster. This is achieved by further comparing the baseline log sequence number of this node with the baseline log sequence numbers of other nodes in the cluster globally. For example, using a global sequence number mechanism, determine whether the baseline of this node is the oldest among all nodes.

[0101] Step S407: If the determination in step S406 is yes, update the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point.

[0102] This method ensures that logical replication slots are only allowed to advance if the metadata dependency of this node is the oldest among all nodes (i.e., no other nodes depend on older metadata), thus ensuring global metadata consistency and avoiding anomalies caused by premature log cleanup.

[0103] To more clearly illustrate the update method for logical replication slots in a database cluster, a specific implementation example is provided. Figure 5 As shown, Figure 5 This is a schematic diagram illustrating the execution process of an update method for a logical replication slot in a clustered database according to an embodiment of the present invention.

[0104] In a single-machine database, the log sequence number of the Write-Ahead Log (WAL) is used to represent the order of global operations. However, in a clustered database, a similar representation of global order is required, such as a global sequence number. Therefore, determining which node has the oldest baseline log sequence number relies on comparing global sequence numbers.

[0105] Specifically, such as Figure 5As shown, the horizontal arrows below indicate the processing procedure of the target write-ahead log. During the sequential processing of the target write-ahead log, it is determined that the target write-ahead log records log information from two database nodes: node 1 (i.e., the master node) and node 2. Subsequently, based on preset rules, the log processing checkpoint in node 1 is determined to be P1, and the log processing checkpoint in node 2 is determined to be P2. In this part of the target write-ahead log, there are two active transactions: active transaction 11 is an active transaction in node 1, and active transaction 21 is an active transaction in node 2. Active transaction 11 includes data definition language (DDL) transactions and other transactions. Figure 5 It can be seen that the sequence number of the DDL transaction precedes P2. Point E3 is the transaction commit point generated upon receiving the standby confirmation information of active transaction 21, and point E4 is the transaction commit point generated upon receiving the standby confirmation information of active transaction 11.

[0106] for Figure 5 In the write-ahead log, when processing up to point P1, the method of this invention needs to first determine the validity judgment log sequence number and the baseline log sequence number corresponding to point P1. Since there are no active transactions before point P1, or all active transactions have been written to disk, the log sequence number of point P1 is used as both the validity judgment log sequence number and the baseline log sequence number of node 1. Similarly, transactions before point P2 have also been written to disk on the standby machine, so the log sequence number of point P2 is used as both the validity judgment log sequence number and the baseline log sequence number of node 2.

[0107] As the target write-ahead log is processed, the standby confirmation information from active transaction 21 is received, generating point E3. Figure 5 As can be seen, the starting point of active transaction 21 is after point P2. Therefore, the log sequence number of point E3 is greater than the validity judgment log sequence number of point P3 (the log sequence number of point P3). Then, it continues to determine whether the baseline log sequence number of the current node 2 is less than the baseline log sequence number of other nodes in the database cluster. In this embodiment, the baseline log sequence number of node 2 is the log sequence number of P2, and the baseline log sequence number of node 1 is the log sequence number of P1. The log sequence number of P2 is greater than the log sequence number of P1. Therefore, the baseline log sequence number of node 2 is not less than the baseline log sequence number of other nodes in the database cluster, so it does not meet the update conditions and the logical replication slot of node 2 cannot be advanced.

[0108] If the logical replication slot of node 2 is pushed to the log sequence number of P2, the database may reclaim and clean up the log files according to the logical replication slot of node 2, thereby cleaning up the DDL transactions in the target log file, which will affect the operation of active transaction 11 in node 1. After restarting, the metadata snapshot corresponding to active transaction 11 cannot be obtained.

[0109] Upon receiving the standby confirmation message from active transaction 11, point E4 was generated. Figure 5 As can be seen, the starting point of active transaction 11 is after point P1. Therefore, the log sequence number of point E4 is greater than the validity judgment log sequence number of point P1 (the log sequence number of point P1). Then, it is further determined whether the baseline log sequence number of the current node 1 is less than the baseline log sequence number of other nodes in the database cluster. In this embodiment, the baseline log sequence number of node 1 is the log sequence number of P1, and the baseline log sequence number of node 2 is the log sequence number of P2. The log sequence number of P1 is less than the log sequence number of P2. Therefore, the baseline log sequence number of node 1 is less than the baseline log sequence number of other nodes in the database cluster, so it meets the update condition. The restart log sequence number corresponding to the logical replication slot of node 1 is updated to the baseline log sequence number of node 1, which is the log sequence number of point P1.

[0110] This method ensures that logical replication slots are only allowed to advance if the metadata dependency of this node is the oldest among all nodes (i.e., no other nodes depend on older metadata), thus ensuring global metadata consistency and avoiding anomalies caused by premature log cleanup.

[0111] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0112] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0113] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 6 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 7 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 8This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the database logical replication slot update method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database logical replication slot update method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0114] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions using status information from computer-readable program instructions to personalize the electronic circuits.

[0115] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0116] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0117] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0118] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0119] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for updating a database logical replication slot, comprising: Obtain the target write-ahead logs to be processed and determine the log processing checkpoints; At the log processing checkpoint, the validity determination log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log are determined. The validity determination log sequence number is used to determine whether to update the logical replication slot corresponding to the target node. The baseline log sequence number is the log sequence number corresponding to the metadata snapshot on which the oldest active transaction in the target node depends. In response to the standby confirmation information in the target write-ahead log, a transaction commit point corresponding to the standby confirmation information is generated, and the standby confirmation information is used to instruct the standby machine to complete the disk write of the corresponding active transaction; At the transaction commit point, the validity determination log sequence number is used to determine whether the logical replication slot meets the preset update conditions. If so, the restart log sequence number corresponding to the logical replication slot is updated to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point, where the restart log sequence number is the replication start point when the replication process restarts.

2. The method for updating database logical replication slots according to claim 1, wherein, When the database contains only a single target node, the step of determining whether the logical replication slot meets the preset update conditions based on the validity determination log sequence number at the transaction commit point includes: Determine the transaction that has been written to disk corresponding to the standby machine confirmation information; Determine whether the log sequence number of the transaction that has been written to disk is greater than the validity determination log sequence number; If the log sequence number of the transaction that has been written to disk is greater than the validity determination log sequence number, the logical replication slot is determined to meet the preset update condition.

3. The method for updating a database logical replication slot according to claim 1, wherein, When the database is a cluster, the database cluster includes multiple nodes, and each node corresponds to one logical replication slot; When the database is a cluster, the step of determining whether the logical replication slot meets the preset update conditions based on the validity determination log sequence number at the transaction commit point includes: Determine the transaction that has been written to disk corresponding to the standby machine confirmation information; Determine whether the log sequence number of the transaction that has been written to disk is greater than the validity determination log sequence number; If the log sequence number of the transaction that has been committed to disk is greater than the validity determination log sequence number, determine whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster. If the baseline log sequence number of the target node is less than the baseline log sequence number of other nodes in the database cluster, the logical replication slot is determined to meet the preset update condition.

4. The method for updating a database logical replication slot according to claim 3, wherein, The step of determining whether the baseline log sequence number of the log processing checkpoint corresponding to the transaction commit point is less than the baseline log sequence number of other nodes in the database cluster includes: Obtain the first global sequence number of the baseline log sequence number of the log processing checkpoint in the target write-ahead log, and the second global sequence number of the baseline log sequence number of other nodes in the database cluster in the target write-ahead log; Determine whether the first global sequence number is less than the second global sequence number; If so, the baseline log sequence number of the target node is determined to be less than the baseline log sequence number of other nodes in the database cluster.

5. The method for updating a database logical replication slot according to claim 1, wherein, The step of determining the validity judgment log sequence number and the baseline log sequence number of the target node corresponding to the target write-ahead log at the log processing checkpoint includes: Determine whether there are any active transactions that have not been written to disk at the log processing checkpoint; If there are no active transactions that have not been written to disk at the log processing checkpoint, the log sequence number corresponding to the log processing checkpoint is used as both the validity determination log sequence number and the baseline log sequence number.

6. The method for updating a database logical replication slot according to claim 5, wherein, Following the step of determining whether there are active transactions that have not been written to disk at the log processing checkpoint, the following is also included: If there are no active transactions that have not been written to disk at the log processing checkpoint, the current restart decoding log sequence number of the preset reorder buffer is set to the log sequence number corresponding to the log processing checkpoint.

7. The method for updating a database logical replication slot according to claim 5, wherein, Following the step of determining whether there are active transactions that have not been written to disk at the log processing checkpoint, the following is also included: If there are active transactions that have not been written to disk at the log processing checkpoint, the log sequence number corresponding to the log processing checkpoint shall be used as the validity determination log sequence number, and the log sequence number corresponding to the metadata snapshot on which the oldest active transaction in the target node depends shall be used as the baseline log sequence number.

8. The method for updating a database logical replication slot according to claim 1, wherein, The step of updating the restart log sequence number corresponding to the logical replication slot to the base log sequence number of the log processing checkpoint corresponding to the transaction commit point further includes: Clear the validity determination log sequence number and the baseline log sequence number corresponding to the log processing checkpoint.

9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for updating a database logical replication slot as described in any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the steps of the update method for a database logical replication slot according to any one of claims 1 to 8.

Images