A method and system for synchronizing SQL statements based on PostgreSQL database

By using a Hook mechanism to intercept SQL statements in the PostgreSQL database and transmitting them using a one-way isolation device, the problems of complex and poor real-time performance in cross-security zone synchronization are solved, achieving efficient data synchronization and automatic structure evolution within security zones.

CN123115197APending Publication Date: 2026-11-17XUCHANG XJ SOFTWARE TECHNOLOGIES LTD +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202611102338.7
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2026-07-23
Publication Date
2026-11-17

AI Technical Summary

Technical Problem

Existing PostgreSQL databases are complex to operate when synchronizing across security zones and have poor data real-time performance, which cannot meet the high efficiency and real-time requirements of the SCADA power system.

Method used

By utilizing the Hook mechanism of the PostgreSQL database, SQL statements are intercepted and saved to a file before the data is written to disk. The file is then transmitted to a database in another security zone through a one-way isolation device, achieving near real-time synchronization across security zones. Finally, the SQL statements are written to the target database.

Benefits of technology

It achieves near real-time data synchronization across security zones, supports automatic database structure evolution, requires no manual maintenance, reduces operation and maintenance costs, and meets the requirements of SCADA systems for data efficiency and consistency.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN123115197A_ABST
    Figure CN123115197A_ABST
Patent Text Reader

Abstract

This invention relates to a SQL statement synchronization method and system based on a PostgreSQL database, belonging to the field of communication and information technology. The method involves exporting the SQL statements to be executed in a first security zone into an SQL statement file; transmitting the SQL statement file to a third security zone via a forward isolation device for unidirectional transmission; and in the third security zone, reading the SQL statements to be executed from the SQL statement file and writing them into the database of the third security zone. This achieves near real-time synchronization across zones, supports automatic database structure evolution, eliminates the need for manual maintenance of synchronization tasks, significantly reduces operation and maintenance costs while ensuring the secure partitioning and isolation of the power system, and meets the requirements of SCADA systems for data efficiency and consistency.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This invention relates to a method and system for synchronizing SQL statements based on a PostgreSQL database, belonging to the field of communication and information technology. Background Technology

[0002] For power systems, security zones are the different "areas" into which the network is divided based on business importance and security level. These zones form the foundation of the "zero-trust" architecture of power monitoring systems, completely separating "real-time monitoring" from "enterprise management," and "internal network" from "external network," ensuring the safe and stable operation of the power grid. However, business functional requirements necessitate real-time synchronization of data from Security Zone I (the core area directly related to power grid operation) to Security Zone III (for operational command, analysis, and decision-making), especially for operational data stored in databases. Currently, PostgreSQL databases lack an effective method for cross-security zone synchronization. The traditional method involves periodically writing SQL statements in Security Zone I to export SQL data files based on time conditions, and then transmitting them to Security Zone III via a forward isolation device. While this method can solve the cross-security zone synchronization problem, it is complex, requiring periodic polling of all data tables. Furthermore, when database tables undergo additions, structural modifications, or deletions, adjustments to the data synchronization task are necessary. Additionally, the real-time performance of the data is not high, failing to meet the high efficiency and real-time requirements of SCADA power systems. Summary of the Invention

[0003] The purpose of this invention is to provide a method and system for synchronizing SQL statements based on a PostgreSQL database, in order to solve the problems of complex data synchronization operations and poor data real-time performance in existing databases.

[0004] To address the aforementioned technical problems, the first aspect of this invention provides a method for synchronizing SQL statements based on a PostgreSQL database, comprising the following steps: 1) Export the SQL statements to be executed in the first security zone as an SQL statement file; 2) The SQL statement file is transferred to the third security zone through a forward isolation device for one-way transmission; in the third security zone, the SQL statements to be executed in the SQL statement file are read and written to the database of the third security zone.

[0005] In one possible implementation, the SQL statement to be executed is intercepted before it is written to disk in the database of the first security zone by calling the Hook mechanism of the PostgreSQL database.

[0006] In one possible implementation, during the export of the SQL statement file, if the number of SQL statements to be executed contained in the current SQL statement file is greater than a preset SQL statement number threshold, or the size of the current SQL statement file is greater than a preset file size threshold, or the generation time of the current SQL statement file exceeds a preset file generation time and the last SQL statement to be executed is complete, the current SQL statement file is exported, and the remaining SQL statements to be executed are exported as a new SQL statement file.

[0007] In one possible implementation, if the current SQL statement file is generated after a preset time, but the last SQL statement to be executed in the current SQL statement file is incomplete, the incomplete SQL statement to be executed is deleted from the current SQL statement file, and the deleted current SQL statement file is exported.

[0008] In one possible implementation, in the third security zone, the SQL statements to be executed in the SQL statement file are read in chronological order.

[0009] In one possible implementation, during the process of reading the SQL statement to be executed from the SQL statement file, if the SQL statement file is generated at a time longer than the preset file generation time and there is no end marker at the end of the SQL statement file, then the transmission of the SQL statement file will fail.

[0010] In one possible implementation, when the number of consecutive synchronization failures of the same SQL statement to be executed in the third security zone reaches a preset threshold, the synchronization of the SQL statement to be executed is stopped and an alarm message is generated.

[0011] One possible implementation also includes: after the SQL statement to be executed is written to the database of the third security zone, generating the write result of the SQL statement to be executed, and feeding back the write result of the SQL statement to be executed to the first security zone through the reverse isolation device.

[0012] One possible implementation method includes: filtering by database name and database table; dynamically configuring the database names that need or are allowed to be synchronized and the tables in the database that do not need to be synchronized in the configuration file; the SQL statement interception program in the first security zone reads the configuration file periodically; when intercepting SQL, the service filters and selects the database names and the tables in the database.

[0013] To address the aforementioned technical problems, a second aspect of the present invention provides an SQL statement synchronization system based on a PostgreSQL database, comprising a processor that executes a computer program to implement the steps of the method described below: 1) Export the SQL statements to be executed in the first security zone as an SQL statement file; 2) The SQL statement file is transferred to the third security zone through a forward isolation device for one-way transmission; in the third security zone, the SQL statements to be executed in the SQL statement file are read and written to the database of the third security zone.

[0014] In one possible implementation, the SQL statement to be executed is intercepted before it is written to disk in the database of the first security zone by calling the Hook mechanism of the PostgreSQL database.

[0015] In one possible implementation, during the export of the SQL statement file, if the number of SQL statements to be executed contained in the current SQL statement file is greater than a preset SQL statement number threshold, or the size of the current SQL statement file is greater than a preset file size threshold, or the generation time of the current SQL statement file exceeds a preset file generation time and the last SQL statement to be executed is complete, the current SQL statement file is exported, and the remaining SQL statements to be executed are exported as a new SQL statement file.

[0016] In one possible implementation, if the current SQL statement file is generated after a preset time, but the last SQL statement to be executed in the current SQL statement file is incomplete, the incomplete SQL statement to be executed is deleted from the current SQL statement file, and the deleted current SQL statement file is exported.

[0017] In one possible implementation, in the third security zone, the SQL statements to be executed in the SQL statement file are read in chronological order.

[0018] In one possible implementation, during the process of reading the SQL statement to be executed from the SQL statement file, if the SQL statement file is generated at a time longer than the preset file generation time and there is no end marker at the end of the SQL statement file, then the transmission of the SQL statement file will fail.

[0019] In one possible implementation, when the number of consecutive synchronization failures of the same SQL statement to be executed in the third security zone reaches a preset threshold, the synchronization of the SQL statement to be executed is stopped and an alarm message is generated.

[0020] One possible implementation also includes: after the SQL statement to be executed is written to the database of the third security zone, generating the write result of the SQL statement to be executed, and feeding back the write result of the SQL statement to be executed to the first security zone through the reverse isolation device.

[0021] One possible implementation method includes: filtering by database name and database table; dynamically configuring the database names that need or are allowed to be synchronized and the tables in the database that do not need to be synchronized in the configuration file; the SQL statement interception program in the first security zone reads the configuration file periodically; when intercepting SQL, the service filters and selects the database names and the tables in the database.

[0022] The beneficial effects of this invention are as follows: the SQL statements to be executed in the first security zone are exported as an SQL statement file; the SQL statement file is transmitted to the third security zone through a forward isolation device for unidirectional transmission; in the third security zone, the SQL statements to be executed in the SQL statement file are read and written to the database of the third security zone, realizing near real-time synchronization across zones, supporting automatic evolution of the database structure, eliminating the need for manual maintenance of synchronization tasks, and significantly reducing operation and maintenance costs while ensuring the security partitioning and isolation of the power system, thus meeting the requirements of the SCADA system for data efficiency and consistency. Attached Figure Description

[0023] Figure 1 This is a flowchart of a SQL statement synchronization method based on a PostgreSQL database proposed in this invention; Figure 2 This is a diagram illustrating the overall framework of the SQL statement synchronization method based on PostgreSQL database proposed in this invention, where PostgreSQL database synchronizes SQL statements from security zone I to zone III in a practical application scenario. Figure 3 This invention presents a flowchart illustrating the generation of SQL statements in the database security I zone using a SQL statement synchronization method based on a PostgreSQL database in a practical application scenario. Figure 4 This is a flowchart illustrating the synchronization process of SQL statement files from security zone I to security zone III in a practical application scenario using a SQL statement synchronization method based on a PostgreSQL database proposed in this invention. Figure 5 This is a structural diagram of an SQL statement synchronization system based on a PostgreSQL database proposed in this invention. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments.

[0025] The inventive concept of this invention is as follows: by utilizing the Hook mechanism of the PostgreSQL database, the SQL statements written are intercepted and saved into a file before the data is written to the PostgreSQL database. Then, by utilizing the file synchronization function of the isolation device, the SQL statement file is synchronized across security zones and written to another PostgreSQL database in another security zone, thereby realizing the synchronization of database data between security zone I and security zone III.

[0026] It should be noted that the PostgreSQL database refers to an open-source relational database management system that has the ability to store and process both relational and time-series data; this solution uses a related mechanism to unidirectionally synchronize the target data in the I-zone database to the III-zone database.

[0027] SQL statements include DML (INSERT / UPDATE / DELETE) for adding, deleting, updating, and retrieving data, and DDL (CREATE / ALTER / DROP) for defining and modifying table structures. PostgreSQL's Hook mechanism is a type of "interceptor" provided by the SQL database, allowing developers to insert custom code at critical points in SQL statement execution without affecting the original logic. Hooks are used to "copy" the original SQL statements before they are written to disk (after successful execution but before being written to the hard drive). Data persistence refers to the process of writing data from memory to the hard drive and persisting it.

[0028] The isolation device refers to a dedicated network security hardware device for power systems (forward isolation device). Its core features are: single-phase transmission, which can only be transmitted from Zone I to Zone III, and the reverse direction is absolutely prohibited, ensuring the security of the core control area.

[0029] Detailed implementation method 1: like Figure 1 The diagram shows a flowchart of a SQL statement synchronization method based on a PostgreSQL database proposed in this invention, which includes steps S11 and S12, specifically: Step S11: Export the SQL statement to be executed in the first security zone as an SQL statement file; here, the SQL statement to be executed is obtained by the following method: by calling the Hook mechanism of the PostgreSQL database, the SQL statement to be executed is intercepted before it is written to the database of the first security zone.

[0030] Step S12: The SQL statement file is transferred to the third security zone through a forward isolation device for unidirectional transmission; in the third security zone, the SQL statement to be executed in the SQL statement file is read and written to the database of the third security zone; here, the writing result of the SQL statement to be executed is also fed back to the first security zone through a reverse isolation device.

[0031] Through the above steps S11-S12, the Hook mechanism of the PostgreSQL database is used to intercept the written SQL statements and save them to a file before the data is written to the PostgreSQL database. Then, the file synchronization function of the isolation device is used to synchronize the SQL statement file across security zones and write it to the PostgreSQL database in another security zone, thereby realizing the data synchronization between the databases in security zone I and security zone III.

[0032] Method Detailed Implementation 2: Below, we will start from practical application scenarios and refer to the accompanying diagrams. Figure 2-4 This invention provides an explanation of a SQL statement synchronization method based on a PostgreSQL database.

[0033] like Figure 2 The diagram illustrates the overall framework of the SQL statement synchronization method based on a PostgreSQL database proposed in this invention. In a practical application scenario, the PostgreSQL database synchronizes SQL statements from Security Zone I (i.e., the first security zone) to Security Zone III (i.e., the third security zone). The SCADA monitoring system deploys two PostgreSQL database servers in a master-slave mode (i.e., PostgreSQL database 1 and PostgreSQL database 2). Each server deploys an application to intercept any SQL statement about to be executed, obtain the SQL statement and the client port initiating it, write the intercepted SQL statement to a file, and transmit it to the SQL statement file synchronization server. Upon receiving the complete SQL statement file, the synchronization server sends it to the forward isolation device for transmission from Security Zone I to Security Zone III. Security Zone III receives the complete SQL statement file, synchronizes it to the PostgreSQL database, and feeds back the result to the SQL statement file synchronization service in Security Zone I through the reverse isolation device.

[0034] like Figure 3The diagram shows the process of generating SQL statements in the database security zone I using the SQL statement synchronization method based on PostgreSQL proposed in this invention. Two PostgreSQL database servers are deployed in the SCADA monitoring system of security zone I, running in a master-slave configuration. The SQL client only communicates with the master PostgreSQL database server; therefore, SQL statements can only be intercepted on the master PostgreSQL database server. The specific implementation is as follows: (1) Deploy an SQL statement interception service on each of the two PostgreSQL database servers in security zone I. The interception service uses the PostgreSQL hook mechanism to call the ExecutorStart_hook and ClientAuthentication_hook functions to capture the SQL statements that the main PostgreSQL database service is about to insert into the database, including DML (INSERT / UPDATE / DELETE) and DDL (CREATE / ALTER / DROP) and the SQL client port. Write the captured SQL statements to a document named database name + client port + time, with the file name suffix temp.

[0035] (2) The interception program service detects the SQL statement file information in real time. When the number of SQL statements in the file is greater than 1000, or the file size is greater than 15M, or the SQL statement file is generated in more than 1 minute and the last SQL statement is complete, an end identifier is added to the end of the SQL statement file. Preferably, two periods are used as the end identifier. The file name suffix is ​​changed to SQL, and a new file with the suffix temp is generated.

[0036] (3) The interceptor service uses the scp command to transfer a file with the file name extension "SQL" from the database server to the synchronization server. After the transfer is completed, the SQL statement file on the database server is deleted.

[0037] (4) If the interception service detects whether the SQL statement file with the file name extension .temp has been generated for more than 1 minute and the last SQL statement is incomplete, or the client port corresponding to the file no longer exists (the connection has been interrupted), then delete the last incomplete SQL statement, add an end identifier to the end of the file, change the file name extension to .sql, transmit it to the synchronization server, and delete the file on the database server.

[0038] (5) The above process saves all SQL statements written to the PostgreSQL database on the Security Zone I database to the synchronization server of Security Zone I. As long as these SQL statements are executed in the database of Security Zone III, the database data of Security Zone III can be synchronized with that of Zone I.

[0039] like Figure 4 The diagram illustrates the synchronization flowchart of the SQL statement file from Security Zone I to Security Zone III in a practical application scenario using the SQL statement synchronization method based on a PostgreSQL database proposed in this invention. A PostgreSQL database server is deployed in Security Zone III. All data in the PostgreSQL database on this server is synchronized after being transferred from Security Zone I. A file transfer service is also deployed on this server to communicate with the forward and reverse isolation devices and synchronize the transferred SQL statement file to the PostgreSQL database in Security Zone III. The specific implementation is as follows: (1) Deploy a file transfer service that communicates with the isolation device on the synchronization server in Security Zone I. The service checks whether there is an end marker at the end of the file according to the time order in the file name in the SQL statement. If there is, the file is sent to the forward isolation device for transfer to Security Zone III. After the transfer is completed, the file is cut and archived to another file directory.

[0040] (2) Deploy a file transfer service that communicates with the isolation device in the database of the security zone III. After an SQL statement file is transferred from the security zone I to the security zone III, the service checks whether there is an end identifier at the end of the file according to the time order in the SQL statement file name. If there is, the SQL statement in the file is read out and written to the PostgreSQL database of the security zone III. The writing result is fed back to the file transfer service of the security zone I through the reverse isolation device, and the SQL statement file is deleted.

[0041] (3) When the file transfer service in Security Zone III detects that the SQL statement file was generated more than 3 minutes ago and there is no end identifier at the end of the SQL statement file, it is considered that the SQL transfer has failed. The failure result is fed back to the file transfer service in Security Zone I through the reverse isolation device, and the SQL statement file is deleted.

[0042] (4) After receiving the synchronization result of the SQL statement file in Zone III, the file transfer service in Zone I will delete the archived SQL statement file if the synchronization is successful, and transfer the archived SQL statement file to Zone III again if the synchronization fails, and record the failure event.

[0043] (5) If the same SQL statement file fails to synchronize three times in a row in Security Zone III, it will no longer be synchronized. It will be moved from the archive directory to another directory (the synchronization failure directory), and an alarm will be sent to the SCADA system in Zone I, indicating that the SQL statement file failed to synchronize in Zone III. The maintenance personnel can perform manual synchronization based on the alarm information.

[0044] Finally, based on the process of generating synchronized SQL statement files and synchronizing SQL statements from security zone I to security zone III, database synchronization filtering is also applied: Filtering is performed by database name and database table. A configuration file dynamically configures which database names need or are allowed to be synchronized, and which tables within those databases do not need to be synchronized. The SQL statement interception program in Security Zone I periodically reads this configuration file. When intercepting SQL statements, this service filters and selects database names and tables within those databases. Specifically: The system maintains a configuration file, which includes a list of databases allowed for synchronization and a list of tables within each allowed database that do not need to be synchronized. The SQL statement interceptor in Security Zone I periodically reads this configuration file during its service cycle to obtain the latest synchronization rules. When a business system generates an SQL statement, the interceptor parses the statement, extracting the corresponding database name and the names of the tables involved. If the database name does not match the list of allowed databases for synchronization, the SQL statement is not considered for cross-security zone synchronization. If the database name matches the list, but the names of the tables involved match the list of tables that do not need to be synchronized, the statement is also not considered for cross-security zone synchronization. Only when the database name is within the allowed synchronization range and the table name is not excluded is the SQL statement sent to the subsequent processing module as a synchronization target. This database-level and table-level filtering mechanism effectively reduces unnecessary data synchronization and improves the targeting and execution efficiency of cross-security zone synchronization.

[0045] System implementation details: The present invention discloses an SQL statement synchronization system based on a PostgreSQL database, the schematic diagram of which is shown below. Figure 5 As shown, the system includes a memory, a processor, a system bus, and a computer program stored in the memory. The processor and memory communicate and interact with each other via the system bus. The processor executes the computer program to implement the steps of the SQL statement synchronization method based on a PostgreSQL database according to the present invention. The processor can be a microprocessor (MCU) or other processing device; the memory can be any type of memory that stores information using electrical energy, such as non-volatile storage media (including computer programs and databases), or other types of memory.

[0046] In summary, the purpose of this invention is to enable SQL statement interception and cross-security zone synchronization for PostgreSQL databases. The beneficial effects include: (1) Using the PostgreSQL database synchronization method of this invention, the test results of a new energy centralized control station with tens of millions of data points show that the average time difference between the database data in security zone III and the data in security zone I is about 20 seconds, which meets the requirements for the timeliness of running data. (2) By using PostgreSQL's hook mechanism, all SQL statements written to the database in the security zone I can be intercepted, ensuring that the data written to the security zone I can be stored in the content format of the SQL statement, and then synchronized to the security zone III database through the isolation device, thus ensuring the integrity of the database synchronization. (3) Some data in Security Zone I is confidential and not open to the public. From a security perspective, it cannot be synchronized to Security Zone III. This invention sets up a database and database name filtering function. Only allowed data can be synchronized to Security Zone III, thus ensuring the security of data in Zone I of the SCADA system.

Claims

1. A method for synchronizing SQL statements based on a PostgreSQL database, characterized in that, Includes the following steps: 1) Export the SQL statements to be executed in the first security zone as an SQL statement file; 2) Transfer the SQL statement file to the third security zone using a forward isolation device for one-way transmission; In the third security zone, the SQL statements to be executed are read from the SQL statement file and written to the database in the third security zone.

2. The SQL statement synchronization method based on PostgreSQL database according to claim 1, characterized in that, By calling the Hook mechanism of the PostgreSQL database, the SQL statement to be executed is intercepted before it is written to the database in the first security zone.

3. The SQL statement synchronization method based on PostgreSQL database according to claim 1, characterized in that, During the export of the SQL statement file, if the number of SQL statements to be executed contained in the current SQL statement file is greater than the preset SQL statement number threshold, or the size of the current SQL statement file is greater than the preset file size threshold, or the generation time of the current SQL statement file exceeds the preset file generation time and the last SQL statement to be executed is complete, the current SQL statement file is exported, and the remaining SQL statements to be executed are exported as a new SQL statement file.

4. The SQL statement synchronization method based on PostgreSQL database according to claim 3, characterized in that, If the current SQL statement file is generated after a time exceeding the preset time, but the last SQL statement to be executed in the current SQL statement file is incomplete, delete the incomplete SQL statement to be executed from the current SQL statement file and export the current SQL statement file after deletion.

5. The SQL statement synchronization method based on a PostgreSQL database according to claim 1, characterized in that, In the third security zone, the SQL statements to be executed in the SQL statement file are read in chronological order.

6. The SQL statement synchronization method based on PostgreSQL database according to claim 1, characterized in that, If the SQL statement file is generated at a time greater than the preset file generation time and there is no end marker at the end of the SQL statement file, then the SQL statement file transfer will fail.

7. The SQL statement synchronization method based on a PostgreSQL database according to claim 6, characterized in that, When the number of consecutive synchronization failures of the same SQL statement to be executed in the third security zone reaches a preset threshold, the synchronization of the SQL statement to be executed will be stopped and an alarm message will be generated.

8. The SQL statement synchronization method based on PostgreSQL database according to claim 1, characterized in that, Also includes: After the SQL statement to be executed is written to the database in the third security zone, the write result of the SQL statement to be executed is generated, and the write result of the SQL statement to be executed is fed back to the first security zone through the reverse isolation device.

9. The SQL statement synchronization method based on PostgreSQL database according to claim 1, characterized in that, Also includes: The system filters databases by name and table. It dynamically configures which databases need or are allowed to be synchronized, and which tables do not need to be synchronized, using a configuration file. The SQL statement interception program in the first security zone periodically reads the configuration file. When intercepting SQL statements, this service filters and selects databases and their corresponding tables.

10. A SQL statement synchronization system based on a PostgreSQL database, comprising a processor, characterized in that, The processor executes a computer program to implement the steps of the method according to any one of claims 1 to 9.