A data replication method, device and medium for PostgreSQL master-slave database

Abstract

The embodiments of this specification disclose a data replication method, device, and medium for a PostgreSQL master-slave database, relating to the field of database management technology and addressing the inflexibility and low efficiency of existing data replication. The method includes: receiving WAL log records sent by a PostgreSQL master database through a pre-set data preprocessing layer, determining the WAL log version, 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 database and data table to be replicated specified by the current user, and matching the SQL statements one by one based on corresponding 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 slave database.

Description

Technical Field

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

[0002] PostgreSQL is a widely used open-source relational database management system, recognized for its powerful functionality and stability across numerous fields. As needs evolve, master-slave database architectures have become a common solution to meet high availability and data redundancy requirements. In PostgreSQL master-slave database scenarios, data replication is a crucial technology, used for data backup, disaster recovery, and load balancing.

[0003] Current data replication methods primarily include physical stream replication, logical replication, and third-party tool-assisted replication. Physical stream replication, because it replicates at the data block level, focuses on the physical storage structure of the data and is indifferent to the logical structure of the data, namely, the division of libraries and tables. This results in the inability to identify which data belongs to which library or table during the replication process, forcing it to indiscriminately transfer and synchronize all data, which can easily lead to problems such as wasted network bandwidth, excessive storage resource usage, and the risk of data leakage. Logical replication, on the other hand, relies on triggers and replication slots, which significantly degrades the performance of the master database in high-concurrency write scenarios. It also is complex to configure and manage, resulting in high operational costs. Third-party tools typically require the installation of specific plug-ins or configuration modifications in the database to monitor database changes and extract data, resulting in compatibility issues and a potential impact on database performance.

[0004] Therefore, a flexible and efficient data replication method is needed to meet the needs 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 a PostgreSQL master-slave database.

[0006] One or more embodiments of this specification adopt the following technical solutions:

[0007] One or more embodiments of this specification provide a data replication method for a PostgreSQL master-slave database, the method comprising:

[0008] Receive WAL log records sent by the PostgreSQL master library through the data preprocessing layer located between the PostgreSQL master library and the PostgreSQL slave library;

[0009] 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;

[0010] Obtaining the database to be copied and the data table to be copied specified by the current user, determining the filtering rules corresponding to the specified database to be copied and the data table to be copied, and matching the SQL statements one by one based on the filtering rules to filter the SQL statements;

[0011] The filtered SQL statements are repackaged to obtain a reconstructed WAL log stream, and the reconstructed WAL log stream is sent to the PostgreSQL slave library.

[0012] Optionally, in one or more embodiments of the present specification, receiving the WAL log record sent by the PostgreSQL master library through a data preprocessing layer located between the PostgreSQL master library and the PostgreSQL slave library specifically includes:

[0013] Configure the IP address and stream replication port of the PostgreSQL master library to establish a TCP connection to the IP address and stream replication port of the PostgreSQL master library based on the TCP / IP protocol;

[0014] Based on the TCP connection, a request message is sent to the PostgreSQL master library using the PostgreSQL stream replication protocol of the PostgreSQL master library to confirm the system information and replication parameters of the PostgreSQL master library, thereby completing the connection between the data preprocessing layer and the PostgreSQL master library;

[0015] Determine a network socket object corresponding to the connection, cyclically read WAL log records sent by the PostgreSQL main library and received by the network socket object, and store the WAL log records in a threshold memory buffer.

[0016] Optionally, in one or more embodiments of the present specification, determining the WAL log version corresponding to the WAL log record, parsing the WAL log record according to the log format corresponding to the WAL log version, and converting the parsed data into a corresponding SQL statement specifically includes:

[0017] Comparing the WAL log version corresponding to the WAL log record with a known version to determine whether the WAL log version is a known version;

[0018] If the version is known, 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 and log sequence number;

[0019] Parsing the WAL log record based on the record type to obtain parsed data, and converting the parsed data into a corresponding SQL statement;

[0020] If it is not a known version, the parsing process of the WAL log record is paused and recorded.

[0021] Optionally, in one or more embodiments of this specification, parsing the WAL log record based on the record type to obtain parsed data specifically includes:

[0022] Determine whether the record type is a transaction-related type; wherein the transaction-related types include: transaction start, transaction commit, transaction rollback, and transaction end;

[0023] If the record type is transaction-related, the corresponding transaction information is stored based on the transaction boundary corresponding to the record type mark, and a parsing process corresponding to the record type is obtained to parse the data portion of the WAL log record according to the corresponding parsing process to obtain parsed data;

[0024] If it is not a transaction-related type, a parsing process corresponding to the record type is obtained to parse the data portion of the WAL log record according to the corresponding parsing process to obtain parsed data.

[0025] Optionally, in one or more embodiments of the present specification, obtaining a database and a data table to be copied specified by the current user, determining filtering rules corresponding to the specified database and data table to be copied, and matching the SQL statements one by one based on the filtering rules to implement filtering of the SQL statements, specifically includes:

[0026] Obtain the database and data table to be copied specified by the current user, and determine the SQL parser corresponding to the SQL statement based on the programming language corresponding to the SQL statement;

[0027] Parsing the SQL statements using the SQL parser to determine the database name and table name of each SQL statement;

[0028] Check whether the database name matches the database to be copied, and if so, check whether the table name matches the data table to be copied;

[0029] If the table name matches the data table to be copied, retain the SQL statement;

[0030] If the database name does not match the database to be copied or the table name does not match the data table to be copied, the SQL statement is filtered.

[0031] Optionally, in one or more embodiments of this specification, repackaging the filtered SQL statements to obtain a reconstructed WAL log stream specifically includes:

[0032] Generate the header structure of the WAL log stream to be reconstructed based on the filtered SQL statements;

[0033] Preprocessing the filtered SQL statements to embed the processed SQL statements into the header structure to obtain a WAL log record unit; wherein the preprocessing includes: encoding processing and verification processing;

[0034] Comparing the number of currently existing WAL log recording units with a preset WAL block number standard to obtain a comparison result, and determining whether to assemble the WAL log recording units to obtain WAL blocks based on the comparison result;

[0035] The WAL blocks are sequentially combined to obtain a reconstructed WAL log stream.

[0036] Optionally, in one or more embodiments of this specification, generating a header structure of the WAL log stream to be reconstructed based on the filtered SQL statement specifically includes:

[0037] Obtaining the log sequence number corresponding to the filtered SQL statement, updating the log sequence number based on an incrementing rule, and obtaining the current log sequence number;

[0038] Acquire 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;

[0039] Based on the current time, the current log sequence number and the operation type field, a header structure of the WAL log stream to be reconstructed is generated.

[0040] Optionally, in one or more embodiments of this specification, sending the reconstructed WAL log stream to the PostgreSQL slave library specifically includes:

[0041] Configure the IP address and stream replication port of the PostgreSQL slave library to establish a TCP connection to the IP address and stream replication port of the PostgreSQL slave library based on the TCP / IP protocol;

[0042] Sending a request message to the PostgreSQL slave library using the PostgreSQL stream replication protocol of the PostgreSQL slave library based on the TCP connection to confirm the system information and replication parameters of the PostgreSQL slave library, thereby completing the connection between the data preprocessing layer and the PostgreSQL slave library;

[0043] Determine a network socket corresponding to the connection, so as to gradually send the reconstructed WAL log stream to the PostgreSQL slave library based on the network socket, and obtain a confirmation message from the PostgreSQL slave library, so as to determine whether to retransmit the reconstructed WAL log stream based on the confirmation message.

[0044] One or more embodiments of this specification provide a data replication device for a PostgreSQL master-slave database, the device including:

[0045] at least one processor; and,

[0046] a memory communicatively connected to the at least one processor; wherein,

[0047] The memory stores instructions that can be executed 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: perform any of the above methods.

[0048] One or more embodiments of this specification provide a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured to execute any of the above-described methods.

[0049] At least one of the above technical solutions adopted in the embodiments of this specification can achieve the following beneficial effects:

[0050] By obtaining the user-specified database and table to be replicated, filtering rules are determined and SQL statements are filtered. This allows replication to be performed on only the specified database and table, avoiding the receipt of large amounts of irrelevant data from the slave database, significantly improving the utilization of network bandwidth and storage resources. By determining the WAL log version corresponding to each record and parsing it according to the log format corresponding to that version, the log record can be correctly parsed. This is applicable to both newer and older versions of the PostgreSQL database, reducing the adaptation costs and operational difficulties associated with database version differences. Furthermore, by introducing a data preprocessing layer between the PostgreSQL master and slave databases, data replication can be implemented non-invasively, eliminating the need for complex third-party plug-ins and improving compatibility. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] In order to more clearly illustrate the embodiments of this specification or the technical solutions in the prior art, the following briefly introduces the drawings required for the embodiments or the description of the prior art. Obviously, the drawings described below are only some of the embodiments described in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without inventive work. In the drawings:

[0052] Figure 1 A flowchart of a data replication method for a PostgreSQL master-slave database provided in an embodiment of this specification;

[0053] Figure 2 A schematic diagram of a data replication architecture for a PostgreSQL master-slave database provided in an embodiment of this specification;

[0054] Figure 3 A schematic diagram of the WAL log record parsing process in an application scenario provided by an embodiment of this specification;

[0055] Figure 4 A schematic diagram of the WAL log stream reconstruction process in an application scenario provided by this storytelling embodiment;

[0056] Figure 5 A schematic diagram of the structure of a data replication device for a PostgreSQL master-slave database provided in an embodiment of this specification;

[0057] Figure 6 A schematic diagram of the structure of a non-volatile storage medium provided in an embodiment of this specification. DETAILED DESCRIPTION

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

[0059] To help those skilled in the art better understand the technical solutions in this specification, the following will provide a clear and complete description of the technical solutions in the embodiments of this specification, in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of this specification, not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of this specification without creative work should fall within the scope of protection of this specification.

[0060] like Figure 1 As shown, the embodiment of this specification provides a method flow diagram of a data replication method for a PostgreSQL master-slave database. Figure 1It can be seen that in one or more embodiments of this specification, a data replication method for a PostgreSQL master-slave database includes the following steps:

[0061] S101: Receive WAL log records sent by the PostgreSQL master library through a data preprocessing layer located between the PostgreSQL master library and the PostgreSQL slave library.

[0062] In order to effectively filter data during the replication process in a non-invasive way, only the specific database and table data required by the slave database can be synchronized, avoiding the transmission and storage of irrelevant data and compatibility issues caused by installing third-party plug-ins. Figure 2 In the embodiment of this specification, a data preprocessing layer is introduced between the PostgreSQL master database and the PostgreSQL slave database. Based on the data preprocessing layer, the data preprocessing layer intercepts, parses, filters, and reconstructs the WAL log stream to achieve data replication. During this process, the data preprocessing layer first receives the WAL log records sent by the PostgreSQL master database. This process introduces the data preprocessing layer without modifying the PostgreSQL database kernel or relying on complex third-party plug-ins. It is applicable to both newer and older versions of PostgreSQL databases, 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 invasive deployment.

[0063] Specifically, in one or more embodiments of this specification, a data preprocessing layer located between the PostgreSQL master and the PostgreSQL slave receives WAL log records sent by the PostgreSQL master, which specifically includes the following process:

[0064] First, in order to enable the data preprocessing layer to establish a reliable network connection with the main database, monitor the WAL log stream sent by the main database, as shown in the embodiment of this specification. Figure 2The WAL log receiving module in the data preprocessing layer is configured with the IP address and streaming replication port of the PostgreSQL master database and establishes a TCP connection to the IP address and streaming replication port of the PostgreSQL master database using the TCP / IP protocol, proactively establishing a connection with the PostgreSQL master database. Then, over the TCP connection, a request message is sent to the PostgreSQL master database using the PostgreSQL streaming replication protocol. This confirms the system information and replication parameters of the PostgreSQL master database, completing the connection between the data preprocessing layer and the PostgreSQL master database. This is to say, during the connection establishment process, the module then determines the network socket object corresponding to the connection, cyclically reads WAL log records sent by the PostgreSQL master database received by the network socket object, and stores the WAL log records in a threshold memory buffer. This means that after the connection is established, the module receives WAL log data from the master database from the network socket through a continuous cyclic read operation. Furthermore, to improve reception efficiency and stability, a buffer mechanism is implemented, storing the received data in a memory buffer before gradually passing it to the subsequent parsing module for processing. Furthermore, the received data is integrity-checked, for example, by calculating a checksum, to ensure that no errors occurred during data transmission.

[0065] As can be seen above, compared to logical replication, which requires creating triggers and using replication slots for each replicated table on the master, adding additional overhead to the master, the above process only involves normal WAL log generation and sending on the master, without any additional complex operations or resource consumption. This process does not significantly impact the master's performance, ensuring normal operation and efficient processing of the master, especially in high-concurrency write scenarios, and maintaining stable performance.

[0066] S102: 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.

[0067] After receiving the WAL log record based on step S101, in order to be able to parse the binary format WAL log record into a highly readable SQL statement, thereby extracting the relevant library and table information. Figure 2The WAL log parsing module in the data preprocessing layer shown in the figure determines the WAL log version corresponding to the WAL log record, parses the WAL log record according to the log format corresponding to that WAL log version, and converts the parsed data into the corresponding SQL statement. This process, by determining the WAL log version corresponding to the WAL log record and parsing it according to the log format corresponding to that version, can automatically adapt to WAL logs generated by different PostgreSQL database versions. Regardless of the PostgreSQL version used by the master database, this data synchronization method can correctly parse log records, ensuring the stability and reliability of data synchronization across different versions and reducing the risk of data synchronization failure due to database version differences.

[0068] Specifically, if Figure 3 In one or more embodiments of this specification, determining the WAL log version corresponding to the WAL log record, parsing the WAL log record according to the log format corresponding to the WAL log version, and converting the parsed data into a corresponding SQL statement specifically includes the following process:

[0069] First, because the WAL log formats generated by different versions of PostgreSQL databases may differ, it is necessary to analyze the received WAL log record to determine its corresponding WAL log version so that the correct parsing rules can be used for subsequent processing. The WAL log version corresponding to the WAL log record is then compared with the known version to determine whether the WAL log version is a known version, that is, to determine whether the version is within the range that the system can handle. If it is a known version, the WAL log format specification corresponding to the known version is obtained to parse the header of the WAL log record to obtain header parsing information. The header parsing information includes: record types such as xl_insert for insert operations and xl_update for update operations, and a log sequence number used to identify the order of the log records. Then, the WAL log record is parsed based on the record type to obtain parsed data, and the parsed data is converted into the corresponding SQL statement. If it is not a known version, then because the system cannot accurately parse the WAL log of that version according to the existing rules, the parsing module will record relevant error information, indicating that an unrecognizable WAL log version was encountered, and suspend the current parsing process to prevent data confusion or loss caused by incorrect parsing. The process then ends.

[0070] Furthermore, in one or more embodiments of this specification, parsing the WAL log record based on the record type to obtain parsed data specifically includes:

[0071] After successfully parsing the WAL log record header and obtaining the header parsing information, key information such as the record type and log sequence number will be extracted to facilitate the subsequent judgment of transaction boundaries and parsing of the data part. Figure 3 As shown, it will determine whether the record type is a transaction-related type; 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 are transaction-related records. If it is a transaction-related type, then the corresponding transaction information is stored according to the transaction boundary corresponding to the record type mark, and the parsing process corresponding to the record type is obtained to parse the data part of the WAL log record according to the corresponding parsing process to obtain parsed data. That is, Figure 3 As shown, if the record type is transaction-related, the parsing module marks the transaction boundaries in an internal data structure, such as recording the transaction start and end locations, and stores transaction-related information, such as the operations involved and the tables involved, to ensure transaction integrity and consistency. Regardless of whether the record type is transaction-related, the parsing process corresponding to the record type is retrieved and used to parse the data portion of the WAL log record. Parsed data is then converted into the corresponding SQL statement. For example, for an xl_insert record, the parsing module extracts information such as the table OID (object identifier) ​​and tuple data. By querying the master database's system table pg_class (which stores metadata for all tables in the database), the table OID is converted into a table name, generating a SQL statement of the form INSERT INTO table_name VALUES(...) .

[0072] S103: Obtain the database to be copied and the data table to be copied specified by the current user to determine the filtering rules corresponding to the specified database to be copied and the data table to be copied, and match the SQL statements one by one based on the filtering rules to filter the SQL statements.

[0073] In some scenarios, the slave database may only need data from a few tables related to a specific business module in the master database. However, physical stream replication will transfer the entire database data, resulting in a large amount of irrelevant data occupying valuable network bandwidth and storage resources, while also increasing the risk of sensitive data leakage. Therefore, in order to achieve selective filtering of databases and tables during the data replication process, improve resource utilization efficiency and data security. Figure 2As shown, the filtering module of the data processing layer in the embodiment of this specification will obtain the database to be copied and the data table to be copied specified by the current user to determine the filtering rules corresponding to the specified database to be copied and the data table to be copied, so as to match the SQL statements one by one according to the filtering rules to achieve filtering of the SQL statements.

[0074] Specifically, in one or more embodiments of the present specification, obtaining a database and a data table to be copied specified by the current user, determining filtering rules corresponding to the specified database and data table to be copied, and matching SQL statements one by one based on the filtering rules to implement filtering of SQL statements, specifically includes:

[0075] The SQL parser retrieves the database and table to be replicated, as specified by the user. Based on the programming language for SQL, it determines the SQL parser for the SQL statement. The SQL parser then parses the SQL statement to determine the database and table names associated with each statement. The SQL parser then checks whether the database name matches the database to be replicated. If so, it checks whether the table name matches the table to be replicated. If the table name matches the table to be replicated, the SQL statement is retained. If the database name does not match the database or table name does not match the table to be replicated, the SQL statement is filtered. In a scenario, the user specifies the libraries and tables to be replicated, and only SQL statements belonging to those libraries and tables are retained. For example, if a user sets a whitelist of ["finance_db", "orders_table"], only the data in the orders_table table within the finance_db database will be replicated. If a parsed SQL statement involves these libraries and tables, the filtering module allows it to pass; otherwise, the statement is discarded. During the implementation process, the filtering module matches the parsed SQL statements against the configured filtering rules one by one, and decides whether to retain or discard the SQL statement based on the matching results. At the same time, to ensure transaction integrity, if some SQL statements in a transaction are filtered out, all SQL statements related to the entire transaction will be discarded, avoiding data inconsistencies in the slave database.

[0076] S104: Repackage the filtered SQL statements to obtain a reconstructed WAL log stream, and send the reconstructed WAL log stream to the PostgreSQL slave library.

[0077] In order to repackage the filtered SQL statements into records that conform to the WAL log format and send them to the slave database, in the embodiment of this specification, Figure 2The WAL log reconstruction module of the data preprocessing layer shown repackages the filtered SQL statements to obtain a reconstructed WAL log stream and sends the reconstructed WAL log stream to the PostgreSQL slave library.

[0078] Specifically, in one or more embodiments of this specification, the filtered SQL statements are repackaged to obtain a reconstructed WAL log stream, which specifically includes the following process:

[0079] like Figure 4As shown, the header structure of the WAL log stream to be reconstructed is first generated based on the filtered SQL statements. The filtered SQL statements are then preprocessed, embedding the processed SQL statements into the header structure to obtain WAL log record units. Preprocessing includes encoding and verification. Specifically, to ensure the integrity and accuracy of the SQL statements during subsequent transmission and application, the SQL statement text is subjected to a specific encoding conversion, and verification information such as a checksum is calculated to detect errors or data tampering in subsequent steps. The number of existing WAL log record units is then compared with a preset WAL block number standard to obtain a comparison result. Based on the comparison result, a determination is made as to whether the WAL log record units should be assembled to obtain a WAL block. The WAL blocks are then sequentially combined to obtain a reconstructed WAL log stream. It should be noted that the comparison of the number of existing WAL log record units with the preset WAL block number standard is intended to improve transmission efficiency and comply with the storage and transmission format of the WAL log. Therefore, a check is performed to see whether the number of currently generated and temporarily stored XLogRecords, i.e., the number of WAL log record units, meets the preset standard for assembly into a WAL block. If there are a sufficient number of XLogRecords, they are properly grouped into a WAL block according to the PostgreSQL WAL log block structure specification for subsequent transmission or storage. If there are insufficient XLogRecords to form a WAL block, the currently generated XLogRecords are temporarily stored until more XLogRecords are generated and reassembled. These temporarily stored XLogRecords will continue to participate in the process, returning to the step of receiving new SQL statements and accumulating them with newly generated XLogRecords until the number of XLogRecords meets the assembly requirements. After completing a WAL block assembly or XLogRecord temporary storage operation, the process checks 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 SQL statements from the filtering module and continues with subsequent operations such as generating XLogRecord headers, encoding verification, and embedding. Once all SQL statements have been processed and all XLogRecords that meet the requirements have been assembled into WAL blocks, these WAL blocks are sequentially combined into a reconstructed WAL log stream for output.

[0080] Furthermore, in one or more embodiments of this specification, generating a header structure of a WAL log stream to be reconstructed based on the filtered SQL statement specifically includes:

[0081] Obtain the log sequence number corresponding to the filtered SQL statement, update the log sequence number based on the incrementing 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 based on the current time, the current log sequence number, and the operation type field. That is, for each filtered SQL statement received, 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 incrementing rule based on the LSN (log sequence number) information of the previously parsed WAL log, 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.

[0082] Specifically, in one or more embodiments of this specification, sending the reconstructed WAL log stream to the PostgreSQL slave library specifically includes:

[0083] The PostgreSQL slave's IP address and streaming replication port are configured, and a TCP connection is established to the slave's IP address and streaming replication port using the TCP / IP protocol. Using the slave's PostgreSQL streaming replication protocol, a request message is sent to the slave over the TCP connection to confirm the slave's system information and replication parameters, completing the connection between the data preprocessing layer and the slave. The corresponding network socket is then determined, and the reconstructed WAL log stream is gradually sent to the PostgreSQL slave via the network socket. Acknowledgments are received from the slave, and based on these acknowledgments, the data preprocessing layer determines whether to retransmit the reconstructed WAL log stream. Furthermore, an asynchronous sending mechanism is employed during the sending process to prevent data transmission congestion caused by network latency and other factors, which could impact overall performance. Furthermore, to ensure reliable data transmission, a data transmission confirmation and retransmission mechanism is implemented. For example, after each WAL block is sent, an acknowledgment message is waited for from the slave. If no acknowledgment is received within a certain period of time, the block is resent, ensuring that the slave receives all WAL log data completely. It should also be noted that each module in the data preprocessing layer utilizes multi-threaded parallel processing. For example, in the WAL log parsing module, received WAL log stream data is divided according to specific rules, such as by LSN range, and assigned to multiple threads for parsing. Each thread independently performs parsing operations on the assigned data, significantly improving parsing efficiency. Similarly, a similar parallel processing approach is employed in the filtering and reconstruction modules. By rationally allocating tasks, the computing resources of multi-core CPUs are fully utilized, reducing overall processing time. A caching mechanism is also introduced to reduce duplication of operations. For example, in the parsing module, query results for frequently queried system table information, such as the table name-OID mapping in the pg_class table, are cached. Subsequent queries for the same information are retrieved directly from the cache, avoiding repeated database queries and improving parsing speed. In the filtering module, matching results for frequently matched filter rules are cached. The next time the same SQL statement is encountered, the query is made directly based on the cached results, reducing the time overhead of rule matching. Furthermore, batch processing is employed in the WAL log reconstruction and forwarding modules. For example, in the reconstruction module, instead of immediately assembling and sending a WAL block after each XLogRecord is generated, a certain number of XLogRecords are cached and then assembled into a WAL block all at once, reducing the number of WAL block generation times and header overhead. In the forwarding module, multiple WAL blocks are sent to slaves in batches, reducing the number of network connection interactions and improving data transmission efficiency.

[0084] In the above process, the filtering rules set by the data preprocessing layer can accurately filter out the specific library and table data required by the slave library. For example, in a scenario containing multiple business module databases, the slave library only needs the data of some of the business module related tables. The whitelist filtering rules of the present invention can effectively avoid the transmission and storage of other irrelevant data, greatly improving the utilization efficiency of network bandwidth and storage resources, which is something that existing physical stream replication cannot do. And because it does not introduce a high-overhead mechanism that relies on triggers and replication slots like logical replication, it is optimized based on physical stream replication. In high-concurrency write scenarios, the performance of the main library will not drop significantly due to the replication filtering operation, and it can still maintain efficient operation, maintain the high performance advantages of physical stream replication itself, and solve the performance bottleneck problem of logical replication. In addition, there is no need to modify the PostgreSQL database kernel, nor does it rely on complex third-party plug-ins. Both newer and older versions of PostgreSQL databases are applicable, reducing the adaptation cost and operation and maintenance difficulty caused by database version differences, and solving the problems of poor compatibility and invasive deployment of third-party tools.

[0085] like Figure 5 As shown, the embodiment of this specification provides a structural diagram of a data replication device for a PostgreSQL master-slave database. Figure 5 It can be seen that in one or more embodiments of this specification, a data replication device for a PostgreSQL master-slave database includes:

[0086] at least one processor; and,

[0087] a memory communicatively connected to the at least one processor; wherein,

[0088] The memory stores instructions that can be executed 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: perform any of the above methods.

[0089] like Figure 6 As shown in FIG, an embodiment of this specification provides a structural diagram of a non-volatile storage medium. Figure 6 It can be 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 of the methods described above.

[0090] The various embodiments in this specification are described in a progressive manner. Similar portions between the various embodiments can be referenced to each other, and each embodiment focuses on the differences from the other embodiments. In particular, the device, apparatus, and non-volatile computer storage medium embodiments are generally similar to the method embodiments, so their descriptions are relatively simplified. For relevant details, refer to the descriptions of the method embodiments.

[0091] The foregoing description is of specific embodiments of the present disclosure. In some cases, the actions or steps described in the specification may be performed in an order different from that described in the embodiments and still achieve the desired results. In addition, the processes depicted in the accompanying drawings do not necessarily require the specific order or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0092] The foregoing is merely one or more embodiments of this specification and is not intended to limit this specification. It will be apparent to those skilled in the art that various modifications and variations may be made to one or more embodiments of this specification. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of one or more embodiments of this specification are intended to be within the scope of this specification.

Claims

1. A data replication method for a PostgreSQL master-slave database, characterized in that: The method comprises: Receive WAL log records sent by the PostgreSQL master library through the data preprocessing layer located between the PostgreSQL master library and the PostgreSQL slave library; 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; Obtaining the database to be copied and the data table to be copied specified by the current user, determining the filtering rules corresponding to the specified database to be copied and the data table to be copied, and matching the SQL statements one by one based on the filtering rules to filter the SQL statements; Re-encapsulate the filtered SQL statements to obtain a reconstructed WAL log stream, and send the reconstructed WAL log stream to the PostgreSQL slave library; 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: Comparing the WAL log version corresponding to the WAL log record with a known version to determine whether the WAL log version is a known version; If the version is known, 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 and log sequence number; Parsing the WAL log record based on the record type to obtain parsed data, and converting the parsed data into a corresponding SQL statement; If it is not a known version, the parsing process of the WAL log record is suspended and recorded; Obtaining the database and data table to be copied specified by the current user to determine the filtering rules corresponding to the specified database and data table to be copied, and matching the SQL statements one by one based on the filtering rules to filter the SQL statements, specifically including: Obtain the database and data table to be copied specified by the current user, and determine the SQL parser corresponding to the SQL statement based on the programming language corresponding to the SQL statement; Parsing the SQL statements using 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 copied, and if so, check whether the table name matches the data table to be copied; If the table name matches the data table to be copied, retain the SQL statement; If the database name does not match the database to be copied or the table name does not match the data table to be copied, the SQL statement is filtered; Repackage the filtered SQL statements to obtain the reconstructed WAL log stream, which includes: Generate the header structure of the WAL log stream to be reconstructed based on the filtered SQL statements; Preprocessing the filtered SQL statements to embed the processed SQL statements into the header structure to obtain a WAL log record unit; wherein the preprocessing includes: encoding processing and verification processing; Comparing the number of currently existing WAL log recording units with a preset WAL block number standard to obtain a comparison result, and determining whether to assemble the WAL log recording units to obtain WAL blocks based on the comparison result; The WAL blocks are sequentially combined to obtain a reconstructed WAL log stream.

2. A data replication method for a PostgreSQL master-slave database according to claim 1, characterized in that The data preprocessing layer located between the PostgreSQL master and the PostgreSQL slave receives the WAL log records sent by the PostgreSQL master, specifically including: Configure the IP address and stream replication port of the PostgreSQL master library to establish a TCP connection to the IP address and stream replication port of the PostgreSQL master library based on the TCP / IP protocol; Based on the TCP connection, a request message is sent to the PostgreSQL master library using the PostgreSQL stream replication protocol of the PostgreSQL master library to confirm the system information and replication parameters of the PostgreSQL master library, thereby completing the connection between the data preprocessing layer and the PostgreSQL master library; Determine a network socket object corresponding to the connection, cyclically read WAL log records sent by the PostgreSQL main library and received by the network socket object, and store the WAL log records in a threshold memory buffer.

3. A data replication method for a PostgreSQL master-slave database according to claim 1, characterized in that Parsing the WAL log record 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, and transaction end; If the record type is transaction-related, the corresponding transaction information is stored based on the transaction boundary corresponding to the record type mark, and a parsing process corresponding to the record type is obtained to parse the data portion of the WAL log record according to the corresponding parsing process to obtain parsed data; If it is not a transaction-related type, a parsing process corresponding to the record type is obtained to parse the data portion of the WAL log record according to the corresponding parsing process to obtain parsed data.

4. A data replication method for a PostgreSQL master-slave database according to claim 1, characterized in that: The header structure of the WAL log stream to be reconstructed is generated based on the filtered SQL statement, specifically including: Obtaining the log sequence number corresponding to the filtered SQL statement, updating the log sequence number based on an incrementing rule, and obtaining the current log sequence number; Acquire 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; Based on the current time, the current log sequence number and the operation type field, a header structure of the WAL log stream to be reconstructed is generated.

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

6. A data replication device for a PostgreSQL master-slave database, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed 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 the method according to any one of claims 1 to 5.

7. A non-volatile storage medium storing computer-executable instructions, characterized in that: The computer executable instructions can execute the method according to any one of claims 1 to 5.

Images