DDL logical replication method and system for OpenGauss database

Abstract

This invention relates to the field of database management technology, and provides a method and system for DDL logical replication of OpenGauss databases. The method includes: writing DDL logs to the publishr's WAL log during the creation of a publication and execution of DDL operations on the publishr; decoding and reading the DDL logs written to the WAL log during the subscription process on the subscriber; and modifying the corresponding data tables by applying the decoded and read DDL logs. This invention's DDL logical replication method and system for OpenGauss databases can avoid logical replication anomalies caused by DDL operations, requires no user intervention, reduces manual table maintenance during logical replication, lowers user management costs, and is easy to operate.

Description

Technical Field

[0001] This invention relates to the field of database management technology, and in particular to a DDL logical replication method and system for OpenGauss database. Background Technology

[0002] In today's digital age, data creates value across various application scenarios. As data applications deepen, maximizing data value requires continuously building data migration pipelines—from homogeneous to heterogeneous, from relational to non-relational, from on-premises to cloud, and from data warehouses to data lakes—to unlock the value of data in diverse contexts. Within this intricate data network, logical replication plays a crucial role. Logical replication is a method that replicates data and its changes based on replication identifiers. Unlike physical replication, which describes page operations, logical replication describes transactions and data tuples. By decoding WAL logs, it synchronizes data changes downstream, achieving data synchronization between upstream and downstream databases.

[0003] OpenGauss is an open-source relational database management system evolved from the open-source database PostgreSQL. In practical applications, because OpenGauss cannot parse DDL statements generated during database execution from the WAL log, logical replication does not support DDL. If a DDL operation causes a change in the table structure, logical replication will fail to capture this change, leading to logical synchronization anomalies. That is, currently, OpenGauss logical replication only supports DML data replication of subscribed tables. When a DDL operation on a source table causes a change in the table structure, DML data replication may be terminated. To avoid this problem, the upstream user on the publishing end needs to ensure that all incremental data on the downstream user side has been synchronized before executing the DDL operation. Then, after the upstream and downstream perform the DDL operation to modify the table structure to a consistent state, logical replication data synchronization continues, significantly increasing the user's management costs and reducing management efficiency.

[0004] Therefore, how to solve the logical replication anomalies caused by DDL operations while reducing management costs and improving management efficiency has become an urgent technical problem to be solved. Summary of the Invention

[0005] In view of this, in order to overcome the shortcomings of the prior art, the present invention aims to provide a DDL logical replication method and system for OpenGauss database.

[0006] According to a first aspect of the present invention, a method for DDL logical replication of an OpenGauss database is provided, comprising:

[0007] During the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the WAL log on the publishing side.

[0008] During the subscription and publication process on the subscription side, the DDL logs written to the WAL log are decoded and read.

[0009] The corresponding data tables are modified by using the DDL logs read through application decoding.

[0010] Preferably, the DDL logical replication method of the present invention for OpenGauss database includes writing DDL logs to the WAL log of the publisher during the creation of the publication and execution of DDL operations on the publisher side, including:

[0011] Create a publication on the publishing side, specifying the data table to be published; create a subscription on the subscription side, specifying that the subscription will be made to the publication created on the publishing side; and connect the subscription side to the publishing side.

[0012] When the worker thread on the publishing end makes changes to the data table, it captures the changes and writes the data table change record and DDL log to the WAL log on the publishing end.

[0013] Preferably, the DDL logical replication method of the present invention for OpenGauss database includes writing DDL logs to the WAL log of the publisher during the creation of the publication and execution of DDL operations on the publisher side, including:

[0014] When creating a publication on the publishing end, an event trigger is created on the publishing end. The event trigger is used to set a callback hook function after the DDL statement is executed. A DDL type log is added to the WAL log. The DDL type log is used to record the user information of the DDL operation execution and the SQL statement information.

[0015] During the execution and parsing of SQL statements for DDL operations by the worker thread on the publishing side, the corresponding SQL statement parsing information is recorded through event triggers;

[0016] When the DDL operation finishes execution, the event trigger is triggered, the callback hook function is executed, and the parsing information of the next SQL statement recorded during this DDL operation is retrieved from the event trigger's linked list;

[0017] The type of the SQL statement is determined based on the extracted SQL statement parsing information. A SQL statement is constructed and filled based on the type of the SQL statement. The object information is then retrieved from the database metadata table based on the address in the SQL statement parsing information to obtain the restored SQL statement. The object information includes the schema, relname name, and constraint information of the object being operated on.

[0018] The restored SQL statement and the user information of the current DDL operation are recorded in JSON format, and the recorded information is encapsulated into a DDL type log and written to the WAL log of the publishing end.

[0019] Preferably, the DDL logical replication method of the present invention for OpenGauss database includes writing DDL logs to the WAL log of the publisher during the creation of the publication and execution of DDL operations on the publisher side, including:

[0020] If the SQL statement parsing information still exists in the event trigger's linked list, the event trigger is triggered, the callback hook function is executed, the SQL statement parsing information is retrieved from the event trigger's linked list, the SQL statement is restored based on the retrieved SQL statement parsing information, and the restored SQL statement and the execution user information of the current DDL operation are recorded in JSON format, and the recorded information is encapsulated into a DDL type log and written to the WAL log of the publishing end.

[0021] If no SQL statement parsing information is found in the event trigger's linked list, the DDL type log will be written to the WAL log on the publishing end.

[0022] Preferably, the present invention provides a DDL logical replication method for OpenGauss databases, which decodes and reads DDL logs written in the WAL log during the subscription and publication process on the subscriber side. This includes: creating a WalSender thread on the publisher side to perform logical decoding on the WAL logs written in the DDL log, filtering out changes to the data tables that need to be published, encapsulating the changes to the data tables into change messages, and sending them to the subscriber side.

[0023] Preferably, the present invention provides a DDL logical replication method for OpenGauss databases, which decodes and reads DDL logs written in the WAL log during the subscription and publication process on the subscriber side. This includes: creating a replication slot and a WalSender thread on the publisher side; when the WalSender thread receives a logical replication request, starting logical replication according to the replication slot; reading the WAL log file; when decoding a DDL type log, reading its recorded information; encapsulating the changes to the decoded data table into a DLL type change message; and publishing it to the subscriber side through a communication protocol facility.

[0024] Preferably, the DDL logical replication method of the present invention for OpenGauss database modifies the corresponding data tables by applying the DDL logs read through application decoding, including: creating an ApplyWorker thread on the subscriber side to read the DLL type change message sent by the publisher side, and modifying the corresponding data tables according to the change type.

[0025] Preferably, the DDL logical replication method of the present invention for OpenGauss database modifies the corresponding data tables by applying DDL logs read through decoding, including:

[0026] On the subscription side, an ApplyWorker thread is created. When the ApplyWorker thread receives a DDL type change message, it reads the execution user information of the DDL operation in the DDL type change message and switches the current user to the execution user corresponding to the execution user information of the DDL operation.

[0027] Read the SQL statement information from the DDL type change message, parse and execute the corresponding SQL statement based on the read SQL statement information, and modify the corresponding data table.

[0028] According to a second aspect of the present invention, a DDL logical replication system for an OpenGauss database is provided, comprising a DDL logical replication server, wherein the DDL logical replication server comprises:

[0029] The DDL log writing module is used to write DDL logs to the WAL log of the publishing end during the creation of publishing and execution of DDL operations.

[0030] The DDL log decoding and reading module is used to decode and read DDL logs written to the WAL log during the subscription and publication process on the subscriber side.

[0031] The DDL log application module is used to modify the corresponding data tables by decoding and reading the DDL logs.

[0032] Preferably, the DDL logical replication server of the DDL logical replication system for OpenGauss database of the present invention includes:

[0033] The DDL log writing module is used to create publications on the publishing end, specifying the data tables to be published, and to create subscriptions on the subscription end, specifying that the subscription should be made to the publications created on the publishing end, and connecting the subscription end to the publishing end; when the worker threads on the publishing end change the data tables, the changes are captured and the records of the data table changes and the DDL logs are written to the WAL log of the publishing end.

[0034] The DDL log decoding and reading module is used to create a WalSender thread on the publishing end to logically decode the WAL logs written to the DDL logs, filter out the changes of the data tables that need to be published, encapsulate the changes of the data tables into change messages and send them to the subscription end.

[0035] The DDL log application module is used to create an ApplyWorker thread on the subscriber side to read DLL-type change messages sent by the publisher side, and modify the corresponding data tables according to the change type.

[0036] According to a third aspect of the present invention, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first aspect of the present invention.

[0037] This invention relates to a DDL logical replication method and system for OpenGauss databases. Based on event triggers, it captures DDL operations and records them in the WAL log. During logical replication, the DDL operations are decoded, and the DDL operations along with data changes are synchronized downstream. After modifying the table structure, the table data continues to be replicated. This avoids logical replication anomalies caused by DDL operations, requires no user intervention, reduces manual table maintenance during logical replication, lowers user management costs, and is easy to operate. Attached Figure Description

[0038] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0039] Figure 1 This is a schematic diagram of a system for a DDL logical replication method for an OpenGauss database, applicable to embodiments of the present invention.

[0040] Figure 2 This is a schematic diagram of the architecture of the DDL logical replication server 101 in the DDL logical replication system for OpenGauss database of the present invention.

[0041] Figure 3 This is a specific application scenario example diagram of the DDL logical replication system for OpenGauss database according to the present invention.

[0042] Figure 4 This is a flowchart of a DDL logical replication method for an OpenGauss database according to an embodiment of the present invention.

[0043] Figure 5 This is a schematic diagram of the structure of the device provided by the present invention. Detailed Implementation

[0044] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0045] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0046] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0047] The technical principles of the present invention are as follows:

[0048] This invention builds upon openGauss's existing publish-subscribe-based logical replication implementation by adding a DDL-type WAL log to record DDL operations in the database. It reuses the support for DDL events in event triggers, encapsulating DDL operations into WAL logs and writing them at appropriate times. When the publisher reads the DDL operation's WAL log through the WALSender thread, it decodes it and places it in the change queue. Upon decoding the commit log, it publishes it to the subscriber. The subscriber reads it and executes the corresponding DDL statement to apply the changes, completing the replication.

[0049] This invention uses event triggers to capture DDL operations and record them in the WAL log. During logical replication, the DDL operations are decoded and synchronized downstream along with the data changes. After modifying the table structure, the table data continues to be replicated. This avoids logical replication anomalies caused by DDL operations, requires no user intervention, reduces manual table maintenance during logical replication, lowers user management costs, and is easy to operate.

[0050] Figure 1 An exemplary system for a DDL logical replication method for an OpenGauss database, applicable to embodiments of the present invention, is shown. For example... Figure 1 As shown, the system may include a DDL logical replication server 101, a communication network 102, and / or one or more DDL logical replication clients 103. Figure 1 The example in the text is a client 103 with multiple DDL logical replications.

[0051] The DDL logical replication server 101 can be any suitable server used to store information, data, programs, and / or any other suitable type of content. In some embodiments, the DDL logical replication server 101 can perform appropriate functions. For example, in some embodiments, the DDL logical replication server 101 can be used for DDL logical replication. As an optional example, in some embodiments, the DDL logical replication server 101 can be used to implement DDL logical replication by writing DDL logs to the publisher and decoding, reading, and applying the written DDL logs on the subscriber side. For example, the DDL logical replication server 101 can be used to write DDL logs to the publisher's WAL log during the creation of a publication and execution of DDL operations on the publisher side; decode and read the DDL logs written to the WAL log during the subscription process on the subscriber side; and modify the corresponding data tables by applying the decoded and read DDL logs.

[0052] As another example, in some embodiments, the DDL logical replication server 101 may send the DDL logical replication method for the OpenGauss database to the DDL logical replication client 103 for user use, based on a request from the DDL logical replication client 103.

[0053] As an optional example, in some embodiments, the DDL logical copy client 103 is used to provide a visual logical copy interface, which is used to receive a user's DDL logical copy selection input operation, and to obtain from the DDL logical copy server 101 the logical copy interface corresponding to the option selected by the selection input operation and display the logical copy interface in response to the selection input operation. The logical copy interface displays at least DDL logical copy information and operation options for the DDL logical copy information.

[0054] In some embodiments, communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, communication network 102 can include any one or more of the following: the Internet, intranet, wide area network (WAN), local area network (LAN), wireless network, digital subscriber line (DSL) network, frame relay network, asynchronous transfer mode (ATM) network, virtual private network (VPN), and / or any other suitable communication network. DDL logical replication client 103 can be connected to communication network 102 via one or more communication links (e.g., communication link 104), which can be linked to DDL logical replication server 101 via one or more communication links (e.g., communication link 105). Communication links can be any communication link suitable for transmitting data between DDL logical replication client 103 and DDL logical replication server 101, such as network links, dial-up links, wireless links, hardwired links, any other suitable communication links, or any suitable combination of such links.

[0055] DDL logical replication client 103 may include any one or more clients that present an interface related to DDL logical replication in a suitable form for user use and operation. In some embodiments, DDL logical replication client 103 may include any suitable type of device. For example, in some embodiments, DDL logical replication client 103 may include a mobile device, tablet computer, laptop computer, desktop computer, and / or any other suitable type of client device.

[0056] Although the DDL logical replication server 101 is illustrated as a single device, in some embodiments, any suitable number of devices may be used to perform the functions performed by the DDL logical replication server 101. For example, in some embodiments, multiple devices may be used to implement the functions performed by the DDL logical replication server 101. Alternatively, cloud services may be used to implement the functions of the DDL logical replication server 101.

[0057] As an example, Figure 2 The diagram shows the architecture of the DDL logical replication server 101 in the DDL logical replication system for OpenGauss database according to the present invention.

[0058] like Figure 2As shown, the DDL logical replication server 101 in the DDL logical replication system for OpenGauss database of the present invention includes a DDL log writing module, a DDL log decoding and reading module, and a DDL log application module. The DDL log writing module is used to write DDL logs into the WAL log of the publisher during the creation of publications and the execution of DDL operations on the publisher side. The DDL log decoding and reading module is used to decode and read the DDL logs written in the WAL log during the subscription and publication process on the subscriber side. The DDL log application module is used to modify the corresponding data tables by applying the decoded and read DDL logs.

[0059] The logical replication server of the present invention will be described in detail below in a specific application scenario. Figure 3 This paper illustrates a specific application scenario of the DDL logical replication system for the OpenGauss database according to the present invention. In this specific application scenario, the DDL logical replication server of the DDL logical replication system for the OpenGauss database according to the present invention includes:

[0060] The DDL log writing module is used to create publications on the publishing end, specifying the data tables to be published, and to create subscriptions on the subscription end, specifying that the subscription should be made to the publications created on the publishing end, and connecting the subscription end to the publishing end; when the worker threads on the publishing end change the data tables, the changes are captured and the records of the data table changes and the DDL logs are written to the WAL log of the publishing end.

[0061] The DDL log decoding and reading module is used to create a WalSender thread on the publishing end to logically decode the WAL logs written to the DDL logs, filter out the changes of the data tables that need to be published, encapsulate the changes of the data tables into change messages and send them to the subscription end.

[0062] The DDL log application module is used to create an ApplyWorker thread on the subscriber side to read DLL-type change messages sent by the publisher side, and modify the corresponding data tables according to the change type.

[0063] Based on the above system, this invention provides a DDL logical replication method for OpenGauss database, which is described below through the following embodiments.

[0064] Reference Figure 4 The diagram illustrates a flowchart of a DDL logical replication method for an OpenGauss database according to an embodiment of the present invention.

[0065] The DDL logical replication method for OpenGauss database in this embodiment can be executed on a DDL logical replication server. The DDL logical replication method for OpenGauss database includes the following steps:

[0066] Step S201: During the creation of the publication and execution of DDL operations on the publishing end, the DDL log is written to the WAL log of the publishing end.

[0067] DDL (Data Definition Language) is a language within SQL that is responsible for defining data structures and database objects. It consists of three syntaxes: CREATE, ALTER, and DROP. It is mainly used when creating tables to define or change the structure, data types, links between tables, and constraints, among other initialization tasks.

[0068] WAL stands for Write Ahead Log, an online redo log in PostgreSQL. It was introduced in PostgreSQL 7 to ensure data consistency and transaction integrity in the database. Its core principle is "write the log first, then write the data," meaning modifications to database files should only be written to the log after these modifications have been written to the log. Furthermore, PostgreSQL 8.3 and later added the WalWriter log writer process, which ensures that transaction commit records are not written to disk synchronously upon commit, but asynchronously, greatly reducing I / O pressure.

[0069] PostgreSQL's WAL log files are located in the pg_xlog directory. Typically, each file is 16MB in size: 000000010000000000000010. The filename consists of 24 hexadecimal characters, grouped into sets of 8 characters each.

[0070] As an example, during the creation of a release and the execution of DDL operations on the publishing side, DDL logs are written to the publishing side's WAL log, implemented as follows:

[0071] Create a publication on the publishing side, specifying the data table to be published; create a subscription on the subscription side, specifying that the subscription will be made to the publication created on the publishing side; and connect the subscription side to the publishing side.

[0072] When the worker thread on the publishing end makes changes to the data table, it captures the changes and writes the data table change record and DDL log to the WAL log on the publishing end.

[0073] For example, a user creates a publication on the publishing database server using the SQL statement "CREATE PUBLICATION", specifying the data tables to be published. When the database worker thread modifies the data tables during its operations, the database records these changes in the WAL log, capturing and recording changes to the data table structure, thus also recording DDL operations in the WAL log.

[0074] As another example, during the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the publishing side's WAL log, implemented as follows:

[0075] When creating a publication on the publishing end, an event trigger is created on the publishing end. The event trigger is used to set a callback hook function after the DDL statement is executed. A DDL type log is added to the WAL log. The DDL type log is used to record the user information of the DDL operation execution and the SQL statement information.

[0076] During the execution and parsing of SQL statements for DDL operations by the worker thread on the publishing side, the corresponding SQL statement parsing information is recorded through event triggers;

[0077] When the DDL operation finishes execution, the event trigger is triggered, the callback hook function is executed, and the parsing information of the next SQL statement recorded during this DDL operation is retrieved from the event trigger's linked list;

[0078] The type of the SQL statement is determined based on the extracted SQL statement parsing information. A SQL statement is constructed and filled based on the type of the SQL statement. The object information is then retrieved from the database metadata table based on the address in the SQL statement parsing information to obtain the restored SQL statement. The object information includes the schema, relname name, and constraint information of the object being operated on.

[0079] The restored SQL statement and the user information of the current DDL operation are recorded in JSON format, and the recorded information is encapsulated into a DDL type log and written to the WAL log of the publishing end.

[0080] In this example method, if the SQL statement parsing information still exists in the event trigger's linked list, the event trigger is triggered, the callback hook function is executed, the SQL statement parsing information is retrieved from the event trigger's linked list, the SQL statement is reconstructed based on the retrieved SQL statement parsing information, and the reconstructed SQL statement and the user information executing the current DDL operation are recorded in JSON format. The recorded information is then encapsulated into a DDL type log and written to the WAL log on the publishing end. If the SQL statement parsing information does not exist in the event trigger's linked list, the writing of the DDL type log to the WAL log on the publishing end ends.

[0081] Step S202: During the subscription and publication process on the subscriber side, decode and read the DDL logs written in the WAL log.

[0082] As an example, decoding and reading DDL logs written to the WAL log during the subscription and publication process on the subscriber side is implemented as follows:

[0083] On the publishing side, a WalSender thread is created to logically decode the WAL logs written to the DDL logs, filter out the changes to the data tables that need to be published, encapsulate the changes to the data tables into change messages, and send them to the subscriber side.

[0084] As another example, decoding and reading DDL logs written to the WAL log during the subscription and publication process on the subscriber side is implemented as follows:

[0085] On the publishing side, a replication slot and a WalSender thread are created. When the WalSender thread receives a logical replication request, it starts logical replication according to the replication slot, reads the WAL log file, and when it decodes a DDL type log, it reads the recorded information, encapsulates the changes of the decoded data table into a DLL type change message, and publishes it to the subscriber side through the communication protocol facility.

[0086] For example, a user creates a subscription on the subscription-side database server using the SQL statement "CREATE SUBSCRIBTION", specifying that the subscription will be published on the publishing-side database server. The subscription-side database server will connect to the publishing-side database server based on the connection information in the subscription. After a successful connection, the publishing-side database server will create a replication slot and a WalSender thread to perform logical replication. The WalSender thread will read the WAL log and perform logical decoding, filtering out changes to the data tables that need to be published. These changes will be encapsulated into specific messages and sent to the subscription-side database server via the pgoutput module.

[0087] Step S203: Modify the corresponding data tables by using the DDL logs read through application decoding.

[0088] As an example, modifying the corresponding data table by applying the DDL logs read through application decoding is implemented as follows:

[0089] On the subscription side, an ApplyWorker thread is created to read the DLL-type change message sent by the publisher and modify the corresponding data table according to the change type.

[0090] For example, the subscriber-side database server creates an ApplyWorker thread to read the specific change messages sent by the publisher-side database server and modifies the corresponding data tables according to the change type. This synchronizes changes to the data tables on the publisher-side database server to the subscriber-side database server. For instance, if data is added, updated, or deleted in a data table on the publisher-side database server, the corresponding data will be added, updated, or deleted in the corresponding data table on the subscriber-side database server.

[0091] This example provides a method for applying changes to DDL operations. When the data table structure on the publishing database server changes, the corresponding data table on the subscribing database server will also change accordingly, completing the logical replication operation.

[0092] As another example, modifying the corresponding data table by applying the DDL logs read through application decoding is implemented as follows:

[0093] On the subscription side, an ApplyWorker thread is created. When the ApplyWorker thread receives a DDL type change message, it reads the execution user information of the DDL operation in the DDL type change message and switches the current user to the execution user corresponding to the execution user information of the DDL operation.

[0094] Read the SQL statement information from the DDL type change message, parse and execute the corresponding SQL statement based on the read SQL statement information, and modify the corresponding data table.

[0095] like Figure 5 As shown, the present invention also provides a device including a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other via the communication bus 340. The processor 310 implements the aforementioned DDL logical copying method for the OpenGauss database by running the executable computer program.

[0096] The computer program in memory 330, when implemented as a software functional unit and sold or used as an independent product, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0097] The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected based on actual needs to achieve the purpose of this embodiment. Those skilled in the art can understand and implement this without any creative effort.

[0098] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods of various embodiments or some parts of embodiments.

[0099] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A DDL logical replication method for OpenGauss database, characterized in that, The method includes: During the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the WAL log on the publishing side. During the subscription and publication process on the subscription side, the DDL logs written to the WAL log are decoded and read. Modify the corresponding data tables by using the DDL logs read through application decoding; During the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the publishing side's WAL log, including: When creating a publication on the publishing end, an event trigger is created on the publishing end. The event trigger is used to set a callback hook function after the DDL statement is executed. A DDL type log is added to the WAL log. The DDL type log is used to record the user information of the DDL operation execution and the SQL statement information. During the execution and parsing of SQL statements for DDL operations by the worker thread on the publishing side, the corresponding SQL statement parsing information is recorded through event triggers; When the DDL operation finishes execution, the event trigger is triggered, the callback hook function is executed, and the parsing information of the next SQL statement recorded during this DDL operation is retrieved from the event trigger's linked list; The type of the SQL statement is determined based on the extracted SQL statement parsing information. A SQL statement is constructed and filled based on the type of the SQL statement. The object information is then retrieved from the database metadata table based on the address in the SQL statement parsing information to obtain the restored SQL statement. The object information includes the schema, relname name, and constraint information of the operation object. The restored SQL statement and the user information of the current DDL operation execution are recorded in JSON format, and the recorded information is encapsulated into DDL type logs and written to the WAL log of the publishing end.

2. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, During the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the publishing side's WAL log, including: Create a publication on the publishing side, specifying the data table to be published; create a subscription on the subscription side, specifying that the subscription will be made to the publication created on the publishing side; and connect the subscription side to the publishing side. When the worker thread on the publishing end makes changes to the data table, it captures the changes and writes the data table change record and DDL log to the WAL log on the publishing end.

3. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, During the creation of a publication and the execution of DDL operations on the publishing side, DDL logs are written to the publishing side's WAL log, including: If the SQL statement parsing information still exists in the event trigger's linked list, the event trigger is triggered, the callback hook function is executed, the SQL statement parsing information is retrieved from the event trigger's linked list, the SQL statement is restored based on the retrieved SQL statement parsing information, and the restored SQL statement and the execution user information of the current DDL operation are recorded in JSON format, and the recorded information is encapsulated into a DDL type log and written to the WAL log of the publishing end. If no SQL statement parsing information is found in the event trigger's linked list, the DDL type log will be written to the WAL log on the publishing end.

4. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, During the subscription and publication process on the subscriber side, the DDL logs written in the WAL log are decoded and read, including: creating a WalSender thread on the publisher side to logically decode the WAL logs written in the DDL logs, filtering out the changes of the data tables that need to be published, encapsulating the changes of the data tables into change messages and sending them to the subscriber side.

5. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, During the subscription and publication process on the subscriber side, the DDL logs written in the WAL log are decoded and read. This includes: creating a replication slot and a WalSender thread on the publisher side; when the WalSender thread receives a logical replication request, it starts logical replication according to the replication slot, reads the WAL log file, and when a DDL type log is decoded, it reads its recorded information, encapsulates the changes of the decoded data table into a DLL type change message, and publishes it to the subscriber side through the communication protocol facility.

6. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, The corresponding data tables are modified by using the DDL logs read through application decoding, including: creating an ApplyWorker thread on the subscription side to read the DLL type change messages sent by the publisher, and modifying the corresponding data tables according to the change type.

7. The DDL logical replication method for OpenGauss database according to claim 1, characterized in that, Modify the corresponding data tables by using the DDL logs read through application decoding, including: On the subscription side, an ApplyWorker thread is created. When the ApplyWorker thread receives a DDL type change message, it reads the execution user information of the DDL operation in the DDL type change message and switches the current user to the execution user corresponding to the execution user information of the DDL operation. Read the SQL statement information from the DDL type change message, parse and execute the corresponding SQL statement based on the read SQL statement information, and modify the corresponding data table.

8. A DDL logical replication system for OpenGauss database, characterized in that, The system includes a DDL logical replication server, wherein the DDL logical replication server includes: The DDL log writing module is used to write DDL logs to the WAL log of the publishing end during the creation of a publication and the execution of DDL operations. This includes: When creating a publication on the publishing end, an event trigger is created on the publishing end. The event trigger is used to set a callback hook function after the DDL statement is executed. A DDL type log is added to the WAL log. The DDL type log is used to record the user information of the DDL operation execution and the SQL statement information. During the execution and parsing of SQL statements for DDL operations by the worker thread on the publishing side, the corresponding SQL statement parsing information is recorded through event triggers; When the DDL operation finishes execution, the event trigger is triggered, the callback hook function is executed, and the parsing information of the next SQL statement recorded during this DDL operation is retrieved from the event trigger's linked list; The type of the SQL statement is determined based on the extracted SQL statement parsing information. A SQL statement is constructed and filled based on the type of the SQL statement. The object information is then retrieved from the database metadata table based on the address in the SQL statement parsing information to obtain the restored SQL statement. The object information includes the schema, relname name, and constraint information of the operation object. The restored SQL statement and the execution user information of the current DDL operation are recorded in JSON format, and the recorded information is encapsulated into DDL type logs and written to the WAL log of the publishing end. The DDL log decoding and reading module is used to decode and read DDL logs written to the WAL log during the subscription and publication process on the subscriber side. The DDL log application module is used to modify the corresponding data tables by decoding and reading the DDL logs.

9. The DDL logical replication system for OpenGauss database according to claim 8, characterized in that, The system's DDL logical replication server includes: The DDL log writing module is used to create publications on the publishing end, specifying the data tables to be published, and to create subscriptions on the subscription end, specifying that the subscription should be made to the publications created on the publishing end, and connecting the subscription end to the publishing end; when the worker threads on the publishing end change the data tables, the changes are captured and the records of the data table changes and the DDL logs are written to the WAL log of the publishing end. The DDL log decoding and reading module is used to create a WalSender thread on the publishing end to logically decode the WAL logs written to the DDL logs, filter out the changes of the data tables that need to be published, encapsulate the changes of the data tables into change messages and send them to the subscription end. The DDL log application module is used to create an ApplyWorker thread on the subscriber side to read DLL-type change messages sent by the publisher side, and modify the corresponding data tables according to the change type.

Images