Data replication method and device for PostgreSQL master-slave database and medium

Abstract

The embodiment of the invention discloses a data replication method and device for a PostgreSQL master-slave database and a medium, relates to the technical field of database management and is used for solving the problems that existing data replication is poor in flexibility and low in efficiency. The method comprises the steps of receiving a WAL log record sent by a PostgreSQL main library through a preset data preprocessing layer, judging a WAL log version of the WAL log record, analyzing the WAL log record according to a log format corresponding to the WAL log version, and converting analyzed data into a corresponding SQL statement; obtaining a to-be-copied database and a to-be-copied data table specified by a current user, and matching the SQL statements one by one based on a corresponding filtering rule to filter the SQL statements; and re-packaging the filtered SQL statement to obtain a reconstructed WAL log stream, and sending the reconstructed WAL log stream to the PostgreSQL slave library.

Description

Technical Field

[0001] This specification relates to the technical field of database management, and particularly to a data replication method, device, and medium for PostgreSQL master-slave databases. Background Art

[0002] PostgreSQL is a widely used open-source relational database management system, and its powerful functions and stability have been recognized in many fields. With the change of requirements to meet the needs of high availability and data redundancy, the master-slave database architecture has become a common solution. In the PostgreSQL master-slave database scenario, data replication is a crucial technology, which is used for data backup, disaster recovery, and load balancing, etc.

[0003] The current data replication methods mainly include physical streaming replication, logical replication, and third-party tool-assisted replication. Among them, physical streaming replication replicates at the data block level, focusing on the physical storage structure of data and not caring about the logical structure of data, that is, the division of databases and tables. This results in its inability to identify which data belongs to which database or table during the replication process, and it can only transmit and synchronize all data without discrimination, easily leading to problems such as waste of network bandwidth, over-occupation of storage resources, and data leakage risks. Logical replication depends on triggers and replication slots, which will significantly reduce the performance of the master database in high-concurrency write scenarios, and the configuration and management are complex, with high operation and maintenance costs. Third-party tools usually require installing specific plugins in the database or making certain configuration modifications to monitor database changes and extract data, so there are compatibility issues and may affect the database performance.

[0004] Therefore, there is a need for a flexible and efficient data replication method to meet the requirements of PostgreSQL master-slave database data replication in different application scenarios. Summary of the Invention

[0005] To solve the above technical problems, one or more embodiments of this specification provide a data replication method, device, and medium for PostgreSQL master-slave databases.

[0006] One or more embodiments of this specification adopt the following technical solutions: One or more embodiments of this specification provide a data replication method for PostgreSQL master-slave databases, the method comprising: Receiving WAL log records sent by the PostgreSQL master database through a data preprocessing layer located between the PostgreSQL master database and the PostgreSQL slave database; Determine the WAL log version corresponding to the WAL log record, parse the WAL log record according to the log format corresponding to the WAL log version, and convert the parsed data into a corresponding SQL statement; Obtain the database to be replicated and the data table to be replicated specified by the current user, determine the filtering rules corresponding to the specified database to be replicated and the data table to be replicated, and match the SQL statements one by one based on the filtering rules to filter the SQL statements; Repackage the filtered SQL statements to obtain a reconstructed WAL log stream, and send the reconstructed WAL log stream to the PostgreSQL standby database.

[0007] Optionally, in one or more embodiments of this specification, the data preprocessing layer located between the PostgreSQL primary database and the PostgreSQL standby database receives the WAL log record sent by the PostgreSQL primary database, specifically including: Configure the IP address and streaming replication port of the PostgreSQL primary database, and establish a TCP connection to the IP address and streaming replication port of the PostgreSQL primary database based on the TCP / IP protocol; Based on the TCP connection and the PostgreSQL streaming replication protocol of the PostgreSQL primary database, send a request message to the PostgreSQL primary database to confirm the system information and replication parameters of the PostgreSQL primary database, and complete the connection between the data preprocessing layer and the PostgreSQL primary database; Determine the network socket object corresponding to the connection, circularly read the WAL log record sent by the PostgreSQL primary database received by the network socket object, and store the WAL log record in the threshold memory buffer.

[0008] Optionally, in one or more embodiments of this specification, determine the WAL log version corresponding to the WAL log record, parse the WAL log record according to the log format corresponding to the WAL log version, and convert the parsed data into a corresponding SQL statement, specifically including: Compare the WAL log version corresponding to the WAL log record with the known version to determine whether the WAL log version is a known version; If it is a known version, obtain the WAL log format specification corresponding to the known version, and parse the header of the WAL log record to obtain header parsing information; wherein, the header parsing information includes: record type, log sequence number; Parse the WAL log records based on the record type, obtain the parsed data, and convert the parsed data into corresponding SQL statements; If it is not a known version, pause the parsing process of the WAL log records and record it.

[0009] Optionally, in one or more embodiments of the present specification, the parsing of the WAL log records based on the record type to obtain the parsed data specifically includes: Determine whether the record type is a transaction-related type; wherein, the transaction-related types include: transaction start, transaction commit, transaction rollback, transaction end; If it is a transaction-related type, mark the corresponding transaction boundary based on the record type, store the corresponding transaction information, and obtain the parsing process corresponding to the record type, so as to parse the data part of the WAL log records according to the corresponding parsing process to obtain the parsed data; If it is not a transaction-related type, obtain the parsing process corresponding to the record type, so as to parse the data part of the WAL log records according to the corresponding parsing process to obtain the parsed data.

[0010] Optionally, in one or more embodiments of the present specification, obtain the database to be replicated and the data table to be replicated specified by the current user, determine the filtering rules corresponding to the specified database to be replicated and the data table to be replicated, and perform one-by-one matching on the SQL statements based on the filtering rules to implement the filtering of the SQL statements, specifically including: Obtain the database to be replicated and the data table to be replicated specified by the current user, and determine the SQL parser corresponding to the SQL statement based on the programming language corresponding to the SQL; Parse the SQL statement according to the SQL parser to determine the database name and table name of each SQL statement; Check whether the database name matches the database to be replicated, and if it matches, check whether the table name matches the data table to be replicated; If the table name matches the data table to be replicated, retain the SQL statement; If the database name does not match the database to be replicated or the table name does not match the data table to be replicated, filter the SQL statement.

[0011] Optionally, in one or more embodiments of the present specification, re-encapsulate the filtered SQL statements to obtain a reconstructed WAL log stream, specifically including: Generate the header structure of the WAL log stream to be reconstructed based on the filtered SQL statements; Preprocess the filtered SQL statement to embed the processed SQL statement into the header structure to obtain a WAL log record unit; wherein, the preprocessing includes: encoding processing and verification processing; Compare the current number of existing WAL log record units with the preset WAL block quantity standard to obtain a comparison result, and determine whether to assemble the WAL log record unit based on the comparison result to obtain a WAL block; Sequentially combine the WAL blocks to obtain a reconstructed WAL log stream.

[0012] Optionally, in one or more embodiments of this specification, generating the header structure of the WAL log stream to be reconstructed based on the filtered SQL statement specifically includes: Obtain the log sequence number corresponding to the filtered SQL statement, update the log sequence number based on an increment rule, and obtain the current log sequence number; Obtain the statement type of the filtered SQL statement, and set the operation type field corresponding to the filtered SQL statement according to the statement type; Combine based on the current time, the current log sequence number, and the operation type field to generate the header structure of the WAL log stream to be reconstructed.

[0013] Optionally, in one or more embodiments of this specification, sending the reconstructed WAL log stream to the PostgreSQL standby library specifically includes: Configure the IP address and streaming replication port of the PostgreSQL standby library, and establish a TCP connection to the IP address and streaming replication port of the PostgreSQL standby library based on the TCP / IP protocol; Based on the TCP connection, use the PostgreSQL streaming replication protocol of the PostgreSQL standby library to send a request message to the PostgreSQL standby library to confirm the system information and replication parameters of the PostgreSQL standby library, and complete the connection between the data preprocessing layer and the PostgreSQL standby library; Determine the network socket corresponding to the connection, and gradually send the reconstructed WAL log stream to the PostgreSQL standby library based on the network socket, and obtain the confirmation message of the PostgreSQL standby library, and determine whether to retransmit the reconstructed WAL log stream based on the confirmation message.

[0014] One or more embodiments of this specification provide a data replication device for a PostgreSQL master-slave database, and the device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: execute any one of the above-mentioned methods.

[0015] A non-volatile computer storage medium provided by one or more embodiments of this specification, storing computer-executable instructions, and the computer-executable instructions are configured to: be capable of executing any one of the above-mentioned methods.

[0016] The above at least one technical solution adopted by the embodiments of this specification can achieve the following beneficial effects: By obtaining the database and data tables to be replicated specified by the user to determine the filtering rules and filtering the SQL statements, only the databases and tables specified by the user can be replicated, avoiding receiving a large amount of irrelevant data from the database, and greatly improving the utilization efficiency of network bandwidth and storage resources. By judging the WAL log version corresponding to the WAL log record and parsing according to the log format corresponding to the version, the log record can be correctly parsed, and it is applicable to both the newer version and the older version of the PostgreSQL database, reducing the adaptation cost and operation and maintenance difficulty caused by database version differences. And by introducing a data preprocessing layer between the PostgreSQL master database and the PostgreSQL slave database, the data replication process can be realized in a non-intrusive processing manner, and the compatibility is improved without relying on complex third-party plugins. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of this specification or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments recorded in this specification. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts. In the drawings: Figure 1 It is a schematic flowchart of a data replication method for a PostgreSQL master-slave database provided by an embodiment of this specification; Figure 2 It is a schematic diagram of a data replication architecture for a PostgreSQL master-slave database provided by an embodiment of this specification; Figure 3 It is a schematic flowchart of WAL log record parsing in an application scenario provided by an embodiment of this specification; Figure 4 It is a schematic flowchart of WAL log stream reconstruction in an application scenario provided by an embodiment of this specification; Figure 5 Schematic diagram of the structure of a data replication device for a PostgreSQL master-slave database provided by an embodiment of this specification; Figure 6 Schematic diagram of the structure of a non-volatile storage medium provided by an embodiment of this specification. Specific embodiments

[0018] Embodiments of this specification provide a data replication method, device, and medium for a PostgreSQL master-slave database.

[0019] In order to enable those skilled in the art to better understand the technical solutions in this specification, the following will clearly and completely describe the technical solutions in the embodiments of this specification with reference to the accompanying drawings in the embodiments of this specification. Obviously, the described embodiments are only a part of the embodiments of this specification, rather than all the embodiments. Based on the embodiments of this specification, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of this specification.

[0020] As Figure 1 shown, an embodiment of this specification provides a schematic flow diagram of a data replication method for a PostgreSQL master-slave database. As Figure 1 can be seen, in one or more embodiments of this specification, a data replication method for a PostgreSQL master-slave database includes the following steps: S101: Receive WAL log records sent by the PostgreSQL master database through a data preprocessing layer located between the PostgreSQL master database and the PostgreSQL slave database.

[0021] In order to be able to effectively filter data in a non-invasive filtering manner during the replication process, so as to synchronize only the specific database and table data required by the slave database, avoid the transmission and storage of irrelevant data, and avoid the compatibility problems brought by installing third-party plugins. As Figure 2 shown, in an embodiment of this specification, a data preprocessing layer is introduced between the PostgreSQL master database and the PostgreSQL slave database to complete processes such as intercepting, parsing, filtering, and reconstructing the WAL log stream based on this data preprocessing layer to achieve data replication. During this process, the data preprocessing layer will first receive the WAL log records sent by the PostgreSQL master database. Introducing this data preprocessing layer does not require modifying the PostgreSQL database kernel, nor does it depend on complex third-party plugins. Whether it is a newer version or an older version of the PostgreSQL database, it can be applied, reducing the adaptation cost and operation and maintenance difficulty brought by database version differences, and solving the problems of poor compatibility of third-party tools and intrusive deployment.

[0022] Specifically, in one or more embodiments of this specification, through a data preprocessing layer located between the PostgreSQL master database and the PostgreSQL slave database, WAL log records sent by the PostgreSQL master database are received. The specific process includes the following: First, in order to enable the data preprocessing layer to establish a reliable network connection with the master database and listen to the WAL log stream sent by the master database, in the embodiments of this specification, Figure 2 the WAL log receiving module in the data preprocessing layer as described above will configure the IP address and streaming replication port of the PostgreSQL master database to establish a TCP connection to the IP address and streaming replication port of the PostgreSQL master database based on the TCP / IP protocol, that is, actively establish a connection with the PostgreSQL master database. Then, based on the TCP connection and the PostgreSQL streaming replication protocol of the PostgreSQL master database, a request message is sent to the PostgreSQL master database to confirm the system information and replication parameters of the PostgreSQL master database, completing the connection between the data preprocessing layer and the PostgreSQL master database. That is, during the connection establishment process. Then, determine the network socket object corresponding to the connection, and cyclically read the WAL log records sent by the PostgreSQL master database received by the network socket object, and store the WAL log records in the threshold memory buffer. That is, after the connection is established, after the connection is established, through continuous cyclic reading operations, the WAL log data sent by the master database is received from the network socket. In addition, in order to improve the reception efficiency and stability, a buffer mechanism is adopted, and the received data is first stored in the memory buffer and then gradually passed to the subsequent parsing module for processing. At the same time, integrity verification is performed on the received data, for example, by calculating the checksum, etc., to ensure that there are no errors in the data during transmission.

[0023] Based on the above, it can be seen that compared with the situation where logical replication requires creating triggers for each replicated table on the master database and using replication slots, which increases the additional overhead of the master database, the above process only performs normal generation and sending of WAL logs on the master database side, without additional complex operations and resource consumption. It will not have an obvious impact on the performance of the master database. Especially in high-concurrency write scenarios, it can ensure the normal operation and high-efficiency processing ability of the master database, and maintain the performance stability of the master database.

[0024] S102: Determine the WAL log version corresponding to the WAL log record, and parse the WAL log record according to the log format corresponding to the WAL log version, and convert the parsed data into corresponding SQL statements.

[0025] After receiving the WAL log record based on step S101, in order to be able to parse the WAL log record in binary format into a highly readable SQL statement, so as to extract relevant library and table information. In the embodiments of the present specification, such as Figure 2 The WAL log parsing module in the data preprocessing layer shown will determine the WAL log version corresponding to the WAL log record, and then parse the WAL log record according to the log format corresponding to the WAL log version, and convert the parsed data into the corresponding SQL statement. In this process, by judging the WAL log version corresponding to the WAL log record and parsing according to the log format corresponding to this version, it can automatically adapt to the WAL logs generated by different versions of the PostgreSQL database. No matter which version of PostgreSQL the master database is, this data synchronization method can correctly parse the log records, ensuring the stability and reliability of data synchronization in different version environments, and reducing the risk of data synchronization failure caused by database version differences.

[0026] Specifically, as Figure 3 shown in one or more embodiments of the present specification, determining the WAL log version corresponding to the WAL log record, in order to parse the WAL log record according to the log format corresponding to the WAL log version, and convert the parsed data into the corresponding SQL statement, specifically includes the following process: First, since the WAL log formats generated by different versions of the PostgreSQL database may be different, it is necessary to analyze the received WAL log record to determine its corresponding WAL log version, so as to adopt the correct parsing rules for subsequent processing. Then compare the WAL log version corresponding to the WAL log record with the known versions to determine whether the WAL log version is a known version, that is, to determine whether this version is within the range that the system can handle. If it is a known version, then obtain the WAL log format specification corresponding to this known version to parse the header of the WAL log record to obtain the header parsing information. Among them, the header parsing information includes: for example, record types such as xl_insert indicating an insert operation and xl_update indicating an update operation, and a log sequence number used to identify the order of the log records. Then, parse the WAL log record based on the record type to obtain the parsed data, and convert the parsed data into the corresponding SQL statement. If it is not a known version, then since the system cannot accurately parse the WAL log of this version according to the existing rules, at this time the parsing module will record relevant error information, indicating that an unrecognized WAL log version is encountered, and pause the current parsing process to prevent data chaos or loss caused by incorrect parsing, and then the process ends.

[0027] Further, in one or more embodiments of the present specification, parsing the WAL log record based on the record type to obtain parsing data specifically includes: After successfully parsing the header of the WAL log record and obtaining the header parsing information as described above, key information such as the record type and log sequence number will be extracted to facilitate subsequent judgment of transaction boundaries and parsing of the data part. When parsing the WAL log record, as Figure 3 shown, it will be determined whether the record type is a transaction-related type; among them, transaction-related types include: transaction start, transaction commit, transaction rollback, transaction end, etc. For example, record types such as xl_commit are usually used to mark the end of a transaction and belong to transaction-related records. If it is a transaction-related type, then mark the corresponding transaction boundary according to the record type, store the corresponding transaction information, and obtain the parsing process corresponding to the record type to parse the data part of the WAL log record according to the corresponding parsing process to obtain parsing data. That is, as Figure 3 shown, if the record type is transaction-related, the parsing module will mark the boundaries of the transaction in the internal data structure, such as recording the start and end positions of the transaction, and store information related to the transaction, such as the operations involved in the transaction and the tables participated in, to ensure the integrity and consistency of the transaction subsequently. And regardless of whether the record type is related to the transaction or not, the parsing process corresponding to the record type will be obtained to parse the data part of the WAL log record according to the corresponding parsing process to obtain parsing data. Then it is converted into the corresponding SQL statement. For example, for the xl_insert record, the parsing module will extract information such as the table OID (object identifier) and tuple data therein, and by querying the system table pg_class of the master database (this table stores the metadata information of all tables in the database), convert the table OID into the table name, thereby generating an SQL statement in the form of INSERT INTO table_name VALUES(...).

[0028] S103: Obtain the database to be replicated and the data tables to be replicated specified by the current user to determine the filtering rules corresponding to the specified database to be replicated and the data tables to be replicated, and match the SQL statements one by one based on the filtering rules to implement filtering of the SQL statements.

[0029] In some scenarios, the slave library may only need the data of several tables related to specific business modules in the master library. However, physical streaming replication will transfer all the data of the entire database, resulting in a large amount of irrelevant data occupying precious network bandwidth and storage resources, and at the same time increasing the risk of sensitive data leakage. Therefore, in order to achieve selective filtering of databases and tables during data replication and improve resource utilization efficiency and data security. As Figure 2As shown in the figure, in the embodiment of the present specification, the filtering module in the data processing layer obtains the database to be replicated and the data table to be replicated specified by the current user, determines the filtering rules corresponding to the specified database to be replicated and the data table to be replicated, and then matches the SQL statements one by one according to the filtering rules to implement the filtering of the SQL statements.

[0030] Specifically, in one or more embodiments of the present specification, obtaining the database to be replicated and the data table to be replicated specified by the current user, determining the filtering rules corresponding to the specified database to be replicated and the data table to be replicated, and matching the SQL statements one by one based on the filtering rules to implement the filtering of the SQL statements specifically includes: Obtain the database to be replicated and the data table to be replicated specified by the current user, and based on the programming language corresponding to SQL, determine the SQL parser corresponding to the SQL statement. Then, parse the SQL statement according to the SQL parser to determine the database name and table name of each SQL statement. Then, check whether the database name matches the database to be replicated. If it matches, then check whether the table name matches the data table to be replicated. If the table name matches the data table to be replicated, then retain the SQL statement. If the database name does not match the database to be replicated or the table name does not match the data table to be replicated, then filter the SQL statement. That is, in a certain scenario, the user specifies the libraries and tables to be replicated, and only the SQL statements belonging to these libraries and tables will be retained. For example, the user sets the whitelist to ["finance_db", "orders_table"], indicating that only the data of the orders_table in the finance_db database will be replicated. When the parsed SQL statement involves this library and table, the filtering module releases it, otherwise discards the statement. In the implementation process, the filtering module matches the parsed SQL statement with the set filtering rules one by one, and decides whether to keep or discard the SQL statement according to the matching result. At the same time, to ensure the integrity of the transaction, if some SQL statements in a transaction are filtered out, then all the SQL statements related to the transaction will be discarded to avoid data inconsistency in the slave database. S104: Re-encapsulate the filtered SQL statements to obtain a reconstructed WAL log stream, and send the reconstructed WAL log stream to the PostgreSQL slave database.

[0031] In order to be able to re-encapsulate the filtered SQL statements into records that conform to the WAL log format and send them to the slave database, in the embodiment of the present specification, as Figure 2 shown, the WAL log reconstruction module in the data preprocessing layer will re-encapsulate the filtered SQL statements to obtain a reconstructed WAL log stream, and send the reconstructed WAL log stream to the PostgreSQL slave database.

[0032] Specifically, in one or more embodiments of this specification, the filtered SQL statement is repackaged to obtain a reconstructed WAL log stream, which specifically includes the following process: As Figure 4 shown, first, based on the filtered SQL statement, the header structure of the WAL log stream to be reconstructed is generated. Then, the filtered SQL statement is preprocessed, and the processed SQL statement is embedded into the header structure to obtain a WAL log record unit. Among them, the preprocessing includes: encoding processing and verification processing, that is, to ensure the integrity and accuracy of the SQL statement during subsequent transmission and application, specific encoding conversion is performed on the SQL statement text, and verification information such as checksum is calculated, so as to detect whether the data has errors or is tampered with in subsequent links. Then, compare the number of existing WAL log record units with the preset WAL block quantity standard to obtain a comparison result, and determine whether to assemble the WAL log record units into WAL blocks based on the comparison result. The WAL blocks are sequentially combined to obtain a reconstructed WAL log stream. It should be noted that comparing the number of existing WAL log record units with the preset WAL block quantity standard is to improve the transmission efficiency and conform to the storage and transmission format of the WAL log. Therefore, it will be checked whether the number of currently generated and temporarily stored XLogRecord, that is, the number of WAL log record units, reaches the preset quantity standard that can be assembled into a WAL block. If there are enough XLogRecord, then according to the WAL log block structure specification of PostgreSQL, these XLogRecord are reasonably combined into a WAL block for subsequent transmission or storage. If the quantity is insufficient and cannot be assembled into a WAL block, the currently generated XLogRecord will be temporarily stored, and the assembly judgment will be made after more XLogRecord are generated subsequently. The temporarily stored XLogRecord will continue to participate in the process later, return to the step of receiving new SQL statements, and accumulate with the newly generated XLogRecord until the quantity meets the assembly condition. After completing a WAL block assembly or XLogRecord temporary storage operation, check whether there are other SQL statements from the filtering module waiting to be processed. If there are new SQL statements, the process returns to the step of receiving the SQL statement passed by the filtering module, and continues to perform subsequent operations such as generating the XLogRecord header, encoding verification, and embedding. When all SQL statements have been processed and all eligible XLogRecord have been assembled into WAL blocks, these WAL blocks are sequentially combined into a reconstructed WAL log stream for output.

[0033] Further, in one or more embodiments of this specification, based on the filtered SQL statement, generating the header structure of the WAL log stream to be reconstructed specifically includes: Obtain the log sequence number corresponding to the filtered SQL statement to update the log sequence number based on an increment rule and obtain the current log sequence number. Obtain the statement type of the filtered SQL statement to set the operation type field corresponding to the filtered SQL statement according to the statement type. Combine the current time, the current log sequence number, and the operation type field to generate the header structure of the WAL log stream to be reconstructed. That is, for each received filtered SQL statement, generate the corresponding header structure of the WAL log stream to be reconstructed according to specific rules. This header contains important information, such as generating a new LSN according to the LSN (log sequence number) information of the previously parsed WAL log according to an increment rule, setting the operation type field according to the SQL statement type (insert, update, delete, etc.), and recording the current time as a timestamp to identify the relevant attributes and order of the log record.

[0034] Specifically, in one or more embodiments of the present specification, sending the reconstructed WAL log stream to the PostgreSQL standby library specifically includes: Configure the IP address and streaming replication port of the PostgreSQL standby to establish a TCP connection to the IP address and streaming replication port of the PostgreSQL standby based on the TCP / IP protocol. According to the TCP connection and the PostgreSQL streaming replication protocol of the PostgreSQL standby, send a request message to the PostgreSQL standby to confirm the system information and replication parameters of the PostgreSQL standby, and complete the connection between the data preprocessing layer and the PostgreSQL standby. Then, determine the network socket corresponding to the connection to gradually send the reconstructed WAL log stream to the PostgreSQL standby based on the network socket, and obtain the confirmation message from the PostgreSQL standby to determine whether to retransmit the reconstructed WAL log stream based on the confirmation message. In addition, during the sending process, an asynchronous sending mechanism is adopted to avoid data sending blockage caused by network latency and other reasons, which affects the overall performance. At the same time, to ensure reliable data transmission, a confirmation and retransmission mechanism for data sending is implemented. For example, for each WAL block sent, wait for the confirmation message from the standby. If the confirmation is not received within a certain time, resend the block to ensure that the standby can receive all WAL log data completely. It should also be noted that in each module of the above data preprocessing layer, a multi-threaded parallel processing method is adopted. For example, in the WAL log parsing module, the received WAL log stream data is divided according to certain rules such as LSN range and assigned to multiple threads for parsing. Each thread independently parses the assigned data, which greatly improves the parsing efficiency. Similarly, similar parallel processing methods are also adopted in the filtering module and the reconstruction module. By reasonably allocating tasks, the computing resources of the multi-core CPU are fully utilized, and the overall processing time is reduced. A caching mechanism is also introduced to reduce duplicate operations. For example, in the parsing module, for the system table information that is frequently queried, such as the mapping relationship between table names and OIDs in the pg_class table, the query results are cached. When the same information needs to be queried again later, it is directly obtained from the cache, avoiding repeated database queries and improving the parsing speed. In the filtering module, for some frequently matched filtering rules, the matching results are cached. When the same SQL statement is encountered next time, the judgment is directly made according to the cached results, reducing the time overhead of rule matching. And in the WAL log reconstruction and forwarding module, a batch processing method is adopted. For example, in the reconstruction module, instead of immediately assembling and sending a WAL block every time an XLogRecord is generated, a certain number of XLogRecords are first cached and then assembled into WAL blocks at one time, reducing the number of WAL block generations and the header overhead. In the forwarding module, multiple WAL blocks are sent to the standby in batches, reducing the number of network connection interactions and improving the data transmission efficiency. During the above process, the filtering rules set by the data preprocessing layer can accurately screen out the specific library and table data required by the slave library. For example, in a scenario where there are multiple business module databases, the slave library only needs the data of certain related tables in some of these business modules. Using the whitelist filtering rules of the present invention, the transmission and storage of other irrelevant data can be effectively avoided, greatly improving the utilization efficiency of network bandwidth and storage resources, which cannot be achieved by existing physical stream replication. Moreover, since a high-overhead mechanism that depends on triggers and replication slots like logical replication is not introduced, but is optimized based on physical stream replication. In a high-concurrency write scenario, the performance of the master library will not drop significantly due to replication filtering operations, and it can still operate efficiently, maintaining the high-performance advantage of physical stream replication itself, and solving the performance bottleneck problem existing in logical replication. In addition, there is no need to modify the PostgreSQL database kernel, nor does it depend on complex third-party plugins. Whether it is a relatively new version or an old version of the PostgreSQL database, it can be applied, reducing the adaptation cost and operation and maintenance difficulty caused by database version differences, and solving the problems of poor compatibility of third-party tools and intrusive deployment.

[0035] As Figure 5 shown, the embodiment of this specification provides a schematic structural diagram of a data replication device for a PostgreSQL master-slave database. It can be Figure 5 seen that in one or more embodiments of this specification, a data replication device for a PostgreSQL master-slave database, the device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can: execute any one of the above methods.

[0036] As Figure 6 shown, the embodiment of this specification provides a schematic structural diagram of a non-volatile storage medium. It can be Figure 6 seen that in one or more embodiments of this specification, a non-volatile storage medium stores computer-executable instructions 601, and the computer-executable instructions 601 can: execute any one of the above methods.

[0037] The various embodiments in this specification are all described in a progressive manner. The same or similar parts between the various embodiments can be referred to each other, and the key points of each embodiment are the differences from other embodiments. In particular, for the embodiments of the device, equipment, and non-volatile computer storage medium, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiments.

[0038] The above describes specific embodiments of this specification. In some cases, the acts or steps recited in the specification may be performed in a different order than in the embodiments and still achieve the desired results. Additionally, the processes depicted in the figures do not necessarily require the particular order or sequential order shown to achieve the desired results. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0039] The foregoing is only one or more embodiments of this specification and is not intended to limit this specification. For those skilled in the art, one or more embodiments of this specification may have various changes and modifications. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of one or more embodiments of this specification shall be included within the scope of this specification.

Claims

1. A data replication method for PostgreSQL master-slave databases, characterized in that, The method includes: Receiving WAL log records sent by the PostgreSQL primary database through a data preprocessing layer located between the PostgreSQL primary database and the PostgreSQL standby database; Judging the WAL log version corresponding to the WAL log records, parsing the WAL log records according to the log format corresponding to the WAL log version, and converting the parsed data into corresponding SQL statements; Obtaining the databases and data tables to be replicated specified by the current user, determining the filtering rules corresponding to the specified databases and data tables to be replicated, and matching the SQL statements one by one based on the filtering rules to filter the SQL statements; Repackaging the filtered SQL statements to obtain a reconstructed WAL log stream, and sending the reconstructed WAL log stream to the PostgreSQL standby database.

2. The data replication method of a PostgreSQL master-slave database according to claim 1, characterized in that, Receiving WAL log records sent by the PostgreSQL primary database through a data preprocessing layer located between the PostgreSQL primary database and the PostgreSQL standby database specifically includes: Configuring the IP address and streaming replication port of the PostgreSQL primary database, and establishing a TCP connection to the IP address and streaming replication port of the PostgreSQL primary database based on the TCP / IP protocol; Sending a request message to the PostgreSQL primary database based on the PostgreSQL streaming replication protocol of the TCP connection to confirm the system information and replication parameters of the PostgreSQL primary database, and completing the connection between the data preprocessing layer and the PostgreSQL primary database; Determining the network socket object corresponding to the connection, circularly reading the WAL log records sent by the PostgreSQL primary database received by the network socket object, and storing the WAL log records in a threshold memory buffer.

3. A method for data replication of a PostgreSQL master-slave database according to claim 1, characterized in that, Judging the WAL log version corresponding to the WAL log records, parsing the WAL log records according to the log format corresponding to the WAL log version, and converting the parsed data into corresponding SQL statements specifically includes: Comparing the WAL log version corresponding to the WAL log records with known versions to determine whether the WAL log version is a known version; If it is a known version, obtaining the WAL log format specification corresponding to the known version, and parsing the header of the WAL log records to obtain header parsing information; wherein, the header parsing information includes: record type, log sequence number; Parsing the WAL log records based on the record type to obtain parsed data, and converting the parsed data into corresponding SQL statements; If it is not a known version, pausing the parsing process of the WAL log records and making a record.

4. A method for data replication of a PostgreSQL master-slave database according to claim 3, characterized in that, Parsing the WAL log records based on the record type to obtain parsed data specifically includes: Determine whether the record type is a transaction-related type; wherein, the transaction-related types include: transaction start, transaction commit, transaction rollback, transaction end; If it is a transaction-related type, mark the corresponding transaction boundary based on the record type, store the corresponding transaction information, and obtain the parsing process corresponding to the record type, so as to parse the data part of the WAL log according to the corresponding parsing process to obtain parsed data; If it is not a transaction-related type, obtain the parsing process corresponding to the record type, so as to parse the data part of the WAL log according to the corresponding parsing process to obtain parsed data.

5. A method for replicating data of a PostgreSQL master-slave database according to claim 1, characterized in that, Obtain the database to be replicated and the data table to be replicated specified by the current user, so as to determine the filtering rules corresponding to the specified database to be replicated and the data table to be replicated, and perform one-by-one matching on the SQL statements based on the filtering rules to implement filtering of the SQL statements, specifically including: Obtain the database to be replicated and the data table to be replicated specified by the current user, and determine the SQL parser corresponding to the SQL statement based on the programming language corresponding to the SQL; Parse the SQL statement according to the SQL parser to determine the database name and table name of each SQL statement; Check whether the database name matches the database to be replicated, and if it matches, check whether the table name matches the data table to be replicated; If the table name matches the data table to be replicated, retain the SQL statement; If the database name does not match the database to be replicated or the table name does not match the data table to be replicated, filter the SQL statement.

6. A method for data replication of a PostgreSQL master-slave database according to claim 3, characterized in that, Repackage the filtered SQL statements to obtain a reconstructed WAL log stream, specifically including: Generate the header structure of the WAL log stream to be reconstructed based on the filtered SQL statements; Preprocess the filtered SQL statements to embed the processed SQL statements into the header structure to obtain WAL log record units; wherein, the preprocessing includes: encoding processing and verification processing; Compare the number of existing WAL log record units with the preset WAL block quantity standard to obtain a comparison result, and determine whether to assemble the WAL log record units based on the comparison result to obtain WAL blocks; Combine the WAL blocks sequentially to obtain a reconstructed WAL log stream.

7. A method for replicating data of a PostgreSQL master-slave database according to claim 6, characterized in that, The generating the header structure of the WAL log stream to be reconstructed based on the filtered SQL statements specifically includes: Obtain the log sequence number corresponding to the filtered SQL statement, update the log sequence number based on an increment rule, and obtain the current log sequence number; Obtain the statement type of the filtered SQL statement, and set the operation type field corresponding to the filtered SQL statement according to the statement type; Generate the header structure of the WAL log stream to be reconstructed by combining the current time, the current log sequence number, and the operation type field.

8. A method for data replication of a PostgreSQL master-slave database according to claim 1, characterized in that, Send the reconstructed WAL log stream to the PostgreSQL slave, specifically including: Configure the IP address and streaming replication port of the PostgreSQL standby to establish a TCP connection to the IP address and streaming replication port of the PostgreSQL standby based on the TCP / IP protocol; Based on the TCP connection and the PostgreSQL streaming replication protocol of the PostgreSQL standby, send a request message to the PostgreSQL standby to confirm the system information and replication parameters of the PostgreSQL standby, and complete the connection between the data preprocessing layer and the PostgreSQL standby; Determine the network socket corresponding to the connection, so as to gradually send the reconstructed WAL log stream to the PostgreSQL standby based on the network socket, and obtain the confirmation message of the PostgreSQL standby, so as to determine whether to retransmit the reconstructed WAL log stream based on the confirmation message.

9. A data replication device for PostgreSQL master-slave databases, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and when the instructions are executed by the at least one processor, the at least one processor is enabled to: execute the method according to any one of claims 1-8 above.

10. A non-volatile storage medium stores computer-executable instructions, characterized in that, The computer-executable instructions can: execute the method according to any one of claims 1-8 above.

Images