Postgresql database index increment synchronization method and system
By creating auxiliary tables for index information and DDL events in PostgreSQL database and using triggers to listen for DDL events, the problem of not being able to identify and delete index DDL statements in the existing technology is solved, and efficient index incremental synchronization is achieved and synchronization is improved. Efficiency and configurability.
Patent Information
- Application Number
- PCT/CN2023/143248
- Authority / Receiving Office
- WO · WO
- Patent Type
- Applications
- Current Assignee / Owner
- Priority Date
- 2023-12-12
- Filing Date
- 2023-12-29
- Publication Date
- 2024-11-07
AI Technical Summary
The existing PostgreSQL database index incremental synchronization technology cannot effectively identify and process DDL statements that delete indexes in the scenario where only partial tables are synchronized, resulting in synchronization failure.
Create two auxiliary tables in the source PostgreSQL database, store index information and DDL execution event information, and listen to DDL events through triggers, execute different storage logic according to the DDL statement type, synchronize relevant information to the target database, and configure triggers Listen to INSERT operations to implement DDL synchronization for specific tables.
Supports DDL statements that only synchronize specific tables, improve synchronization efficiency and configurability, reduce changes to the source library, strengthen compatibility and portability, and avoid unnecessary DDL synchronization operations.
Smart Images

Figure CN2023143248_07112024_PF_FP_ABST
Abstract
Description
A PostgreSQL database index incremental synchronization method and system Technical Field
[0001] The present invention belongs to the technical field of database synchronization, and in particular relates to a PostgreSQL database index incremental synchronization method and system. Background Art
[0002] In relational databases, DDL (Data Definition Language) and DML (Data Manipulation Language) are the two most basic SQL language types. DDL is used to define and manage database objects, such as tables, indexes, triggers, views, and stored procedures; while DML is used to perform operations such as querying, inserting, modifying, and deleting data in the database.
[0003] Database DDL (Data Definition Language) incremental synchronization means that when structural changes are made in a database, these changes are synchronized to other databases with the same structure to ensure that these databases have the same structure and definition.
[0004] Existing PostgreSQL database DDL incremental synchronization technology uses triggers to save executed DDL statements in an auxiliary table. Database data synchronization technology is then used to synchronize the auxiliary table's data to the target database. The auxiliary table's DDL statements are then executed in the target database to achieve DDL synchronization. This synchronization method is feasible when all tables in the entire database are synchronized, as there is no need to identify the target of the DDL statement.
[0005] However, when encountering a scenario where only the indexes of some tables need to be synchronized, for DDL statements that change the PostgreSQL database index, such as the DDL statement to delete an index, when the trigger is triggered, the index in the database has already changed and been deleted. As a result, it is impossible to query which table the deleted index belongs to, nor can the statement information of the deleted index be found, making it impossible to determine whether the index DDL statement needs to be synchronized.
[0006] Summary of the Invention
[0007] The purpose of the present invention is to provide a PostgreSQL database index incremental synchronization method and system, which supports synchronizing only the DDL statements of specific tables, has higher synchronization efficiency and stronger configurability, and solves the problems in the prior art raised in the above background technology.
[0008] To achieve the above objectives, the present invention adopts the following technical solutions: a PostgreSQL database index incremental synchronization method, comprising: creating a first auxiliary table in the source PostgreSQL database, and storing all index information in the first auxiliary table for database queries; creating a second auxiliary table in the source PostgreSQL database, wherein the second auxiliary table includes DDL execution event information; creating a source PostgreSQL database trigger to monitor the DDL execution event information, and executing storage logic according to the type of DDL statement when executing the DDL statement; synchronizing the second auxiliary table to the target database using a data synchronization tool; configuring the trigger in the target database to monitor the INSERT operation of the target database, executing the DDL statement in the INSERT event statement, and performing DDL synchronization of a specific table.
[0009] Preferably, the index information includes the index name, the name of the table to which it belongs, and the name of the database instance.
[0010] Preferably, the DDL execution event information includes DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement and DDL execution time.
[0011] Preferably, the DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX and DROP INDEX.
[0012] Preferably, the storage logic includes: when the DDL statement type is CREATE INDEX, using a first storage method; when the DDL statement type is DROP TABLE, using a second storage method; when the DDL statement type is other types, using a third storage method.
[0013] Preferably, the first storage method includes: obtaining the current index name, the name of the table to which it belongs, and the name of the database instance and storing them in the first auxiliary table; obtaining the current DDL statement type, the DDL target database instance name, the DDL object name, the DDL parent object name, the DDL execution statement, and the DDL execution time and storing them in the second auxiliary table.
[0014] Preferably, the second storage method includes: searching for the corresponding table name from the first auxiliary table according to the current index name and database instance name; then obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
[0015] Preferably, the third storage method includes: obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
[0016] Preferably, the target database includes a third auxiliary table, and the third auxiliary table is used to store INSERT events.
[0017] On the other hand, the present invention provides a PostgreSQL database index incremental synchronization system, comprising:
[0018] Creation module 1, used to create a first auxiliary table in the source PostgreSQL database, and query the database to store all index information in the first auxiliary table;
[0019] Create module 2 to create a second auxiliary table in the source PostgreSQL database. The second auxiliary table contains DDL execution event information.
[0020] A judgment module is used to create a source-side PostgreSQL database trigger to monitor the DDL execution event information and execute storage logic according to the DDL statement type when executing the DDL statement;
[0021] A storage module, configured to synchronize the second auxiliary table to a target database using a data synchronization tool;
[0022] The synchronization module is used to configure the trigger in the target database to monitor the INSERT operation of the target database, execute the DDL statement in the statement of the INSERT event, and perform DDL synchronization of a specific table.
[0023] Technical effects and advantages of the present invention: The PostgreSQL database index incremental synchronization method and system proposed in the present invention have the following advantages over the prior art:
[0024] Create a first auxiliary table in the source PostgreSQL database and store all index information in the first auxiliary table for database queries. Create a second auxiliary table in the source PostgreSQL database, which includes DDL execution event information. Create a source PostgreSQL database trigger to monitor DDL execution event information. When executing DDL statements, execute storage logic based on the DDL statement type. Use the data synchronization tool to synchronize the second auxiliary table to the target database. Configure a trigger in the target database to monitor INSERT operations in the target database and execute the DDL statements in the INSERT event statement to synchronize DDL for specific tables. This supports synchronizing only DDL statements for specific tables, resulting in higher synchronization efficiency and greater configurability. It requires minimal changes to the source database and does not require adding preset so file configurations in the source database's lib directory. Trigger configurations are all built-in functions of PostgreSQL, offering greater compatibility and portability. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] FIG1 is a flow chart of a method for incremental synchronization of PostgreSQL database indexes according to the present invention;
[0026] FIG2 is a module diagram of a PostgreSQL database index incremental synchronization system according to the present invention. DETAILED DESCRIPTION
[0027] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. The specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.
[0028] An embodiment of the present invention provides a PostgreSQL database index incremental synchronization method as shown in FIG1 , including:
[0029] Step 1: Create a first auxiliary table in the source PostgreSQL database, and query the database to store all index information in the first auxiliary table (auxiliary table T1);
[0030] Specifically, the index information includes the index name, the name of the table to which it belongs, and the name of the database instance.
[0031] Step 2: Create a second auxiliary table (auxiliary table T2) in the source PostgreSQL database. The second auxiliary table contains DDL execution event information.
[0032] Specifically, the DDL execution event information includes the DDL statement type, the DDL target database instance name, the DDL object name, the DDL parent object name, the DDL execution statement, and the DDL execution time.
[0033] Step 3: Create a trigger in the source PostgreSQL database to monitor the DDL execution event information. When executing the DDL statement, execute the storage logic based on the DDL statement type.
[0034] Specifically, DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX.
[0035] When the DDL statement type is CREATE INDEX, a first storage method is used; the first storage method includes: obtaining the current index name, the name of the table to which it belongs, and the name of the database instance, and storing them in the first auxiliary table; obtaining the current DDL statement type, the name of the DDL target database instance, the DDL object name, the DDL parent object name, the DDL execution statement, and the DDL execution time, and storing them in the second auxiliary table.
[0036] When the DDL statement type is DROP TABLE, a second storage method is used. The second storage method includes: searching the first auxiliary table for the corresponding table name based on the current index name and database instance name; then obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
[0037] When the DDL statement type is other types, the third storage method is adopted; the third storage method includes: obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
[0038] Step 4: Use the data synchronization tool to synchronize the second auxiliary table to the target database;
[0039] Specifically, the target database includes a third auxiliary table (auxiliary table T3), and the third auxiliary table is used to store INSERT events.
[0040] Step 5: Configure the trigger in the target database to monitor the INSERT operation of the target database, execute the DDL statement in the INSERT event statement, and perform DDL synchronization of a specific table.
[0041] Specifically, a trigger is configured in the target database to monitor the INSERT event of the third auxiliary table and execute the corresponding DDL statement to achieve DDL index synchronization.
[0042] In this embodiment, by performing DDL synchronization of specific tables, it supports synchronizing only the DDL statements of specific tables, which has higher synchronization efficiency and stronger configurability. It makes little change to the source library and does not require adding preset so file configurations to the source library lib directory. The trigger configurations are all built-in functions already available in PostgreSQL, which has stronger compatibility and portability.
[0043] On the other hand, the present invention proposes a PostgreSQL database index incremental synchronization system, as shown in FIG2 , comprising: a creation module 1, a creation module 2, a judgment module, a storage module, and a synchronization module.
[0044] Specifically, the creation module 1 is used to create a first auxiliary table in the source PostgreSQL database, and query the database to store all index information in the first auxiliary table;
[0045] Specifically, the second creation module creates a second auxiliary table in the source-side PostgreSQL database, where the second auxiliary table includes DDL execution event information;
[0046] Specifically, the judgment module is used to create a source-side PostgreSQL database trigger to monitor the DDL execution event information and execute storage logic according to the DDL statement type when executing the DDL statement;
[0047] Specifically, the storage module is used to synchronize the second auxiliary table to the target database using a data synchronization tool;
[0048] Specifically, the synchronization module is used to configure the trigger in the target database to monitor the INSERT operation of the target database, execute the DDL statement in the statement of the INSERT event, and perform DDL synchronization of a specific table.
[0049] In addition, when executing the above-mentioned creation module 1, creation module 2, judgment module, storage module, and synchronization module, other functions of the above-mentioned PostgreSQL database index incremental synchronization method can also be realized, as follows.
[0050] (1) Create an auxiliary table T1 in the source PostgreSQL database, which contains at least the following columns: index name, table name, and database instance name;
[0051] (2) Before synchronization begins, perform a full database query to store the index names, table names, and database instance names of all indexes in the auxiliary table T1;
[0052] (3) Create an auxiliary table T2 in the source PostgreSQL database, which contains at least the following columns: DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time;
[0053] Among them, DDL statement types can be divided into the following types: CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.
[0054] DDL object name: refers to the execution object of the DDL statement. The object varies depending on the DDL statement type. For example, in CREATE TABLE, ALTER TABLE, and DROP TABLE, the DDL object name is the table name; in CREATE INDEX and DROP INDEX, it is the index name.
[0055] DDL parent object name: The object varies depending on the DDL statement type. For example, when using CREATE TABLE, ALTER TABLE, or DROP TABLE, the DDL parent object name is the database instance name; when using CREATE INDEX or DROP INDEX, the DDL parent object name is the name of the table to which the index belongs.
[0056] (4) Create a trigger in the source PostgreSQL database to monitor DDL execution events. When a DDL statement is executed, the following logic is executed based on the DDL statement type (the DDL statement type is the trigger's built-in variable tg_tag):
[0057] A. When the DDL statement type is CREATE INDEX, obtain the current "index name", "belonging table name", and "database instance name" and store them in auxiliary table T1; obtain the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and store them in auxiliary table T2;
[0058] B. When the DDL statement type is DROP TABLE, since this type of statement does not have information about the table name, it is necessary to search the corresponding "table name" from auxiliary table T1 based on the current "index name" and "database instance name". Then, the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time are obtained and stored in auxiliary table T2.
[0059] C. When the DDL statement type is other types, obtain the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and store them in auxiliary table T2;
[0060] (5) Use the data synchronization tool to synchronize the auxiliary table T2 to the target database. You can also determine whether the data needs to be synchronized to the target database based on the "parent object name" in the T2 table.
[0061] (6) The target database configures a trigger to monitor the INSERT operation of the table and executes the DDL statement in the INSERT statement to achieve DDL synchronization of the specific table.
[0062] In the following example, the source database is PostgreSQL, the target database is PostgreSQL, and the database instance names are both public.
[0063] 1. Create auxiliary table T1 on the source side. The table creation SQL is as follows:
[0064] 2. Create auxiliary table T2 in the source database. The table creation SQL is as follows:
[0065] 3. Initialize the T1 table data and the initialization SQL is as follows:
[0066] INSERT INTO indexsync_ddl_indexs(SELECT concat(schemaname,'.',indexname)idx,concat(schemaname,'.',tablename)tablename FROM pg_indexes WHERE schemaname!='pg_catalog')ON CONFLICT(indexname)DO UPDATE SET tablename=excluded.tablename
[0067] 4. Create a trigger function F1, and create the function SQL as follows:
[0068] 5. Create trigger T1 and create SQL as follows:
[0069] 6. Create trigger function F2 and create SQL as follows:
[0070] 7. Create trigger function T2 and create SQL as follows:
[0071] 8. At this point, the DDL synchronization configuration of the source database is complete. Now proceed with the DDL modification operation.
[0072] 9. Create a demo table employee:
[0073] 10. Create an index for the demo table employee:
[0074] CREATE INDEX employee_name_idx ON public.employee("name",address).
[0075] 11. Write the DDL statement into the auxiliary table T2;
[0076] 12. The created index is updated to the auxiliary table T1;
[0077] 13. Use the data synchronization tool to synchronize auxiliary table T2 to auxiliary table T3 in the target database;
[0078] 14. Configure a trigger in the target database to monitor the INSERT event of the auxiliary table T3 and execute the corresponding DDL statement to achieve DDL index synchronization.
[0079] Before the first synchronization is started, an auxiliary table T1 is created to store the index names, table names, and database instance names of all tables in the current database. A full database search is then performed to save all current index data to auxiliary table T1. When the trigger detects a DDL statement that creates an index, the created index is written back to auxiliary table T1 and the DDL statement information is saved to auxiliary table T2. When the trigger detects a DDL statement that deletes an index, the name of the table to which the index belongs is queried from auxiliary table T1, and the name of the database instance to which it belongs is merged into the DDL statement information and saved to auxiliary table T2.
[0080] The use of index dynamic snapshots solves the problem of trigger function execution after DDL event execution. It can support synchronizing only the indexes of specific tables, avoid unnecessary DDL synchronization operations, and improve synchronization efficiency.
[0081] In addition, this embodiment also provides the present invention also provides a terminal device. The PostgreSQL database index incremental synchronization method involved in this embodiment is mainly applied to the terminal device, which can be a PC, portable computer, mobile terminal, etc. with display and processing functions.
[0082] Specifically, a terminal device may include a processor (e.g., a CPU), a communication bus, a user interface, a network interface, and a memory. The communication bus is used to enable communication between these components; the user interface may include a display and an input unit such as a keyboard; the network interface may optionally include a standard wired interface or a wireless interface (e.g., a Wi-Fi interface); and the memory may be high-speed RAM or non-volatile memory, such as a disk drive. The memory may also be a storage device independent of the processor.
[0083] Among them, the memory stores a readable storage medium, and the readable storage medium stores an index incremental synchronization program. The processor can call the index incremental synchronization program stored in the memory and execute the PostgreSQL database index incremental synchronization method provided by an embodiment of the present invention.
[0084] It will be understood that a computer-readable storage medium can be a tangible device that can hold and store instructions used by an instruction execution device. Computer-readable storage media can be, for example, but not limited to, an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. More specific examples (a non-exhaustive list) of computer-readable storage media include: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, such as a punch card or a raised structure in a groove on which instructions are stored, and any suitable combination thereof. As used herein, a computer-readable storage medium is not to be construed as a transient signal per se, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., a light pulse through a fiber optic cable), or an electrical signal transmitted through an electrical wire.
[0085] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to each computing / processing device, or downloaded to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network can include copper transmission cables, fiber optic transmission, wireless transmission, routers, firewalls, switches, gateway computers, and / or edge servers. The network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards the computer-readable program instructions to be stored in the computer-readable storage medium in each computing / processing device.
[0086] The computer program instructions for performing the operations of the present disclosure may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, or source code or object code written in any combination of one or more programming languages, including object-oriented programming languages such as Smalltalk, C++, and conventional procedural programming languages such as "C" language or similar programming languages. Computer-readable program instructions may be executed entirely on a user's computer, partially on a user's computer, as an independent software package, partially on a user's computer, partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., utilizing an Internet service provider to connect via the Internet). In some embodiments, an electronic circuit, such as a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA), may be personalized by utilizing the state information of the computer-readable program instructions. The electronic circuit may execute the computer-readable program instructions, thereby realizing various aspects of the present disclosure.
[0087] Finally, it should be noted that the above is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art can still modify the technical solutions described in the aforementioned embodiments or make equivalent substitutions for some of the technical features therein. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.
Claims
1. A PostgreSQL database index incremental synchronization method, characterized in that: include: Create a first auxiliary table in the source PostgreSQL database, and query the database to store all index information in the first auxiliary table; Create a second auxiliary table in the source PostgreSQL database, where the second auxiliary table includes DDL execution event information; Create a source-side PostgreSQL database trigger to monitor the DDL execution event information, and execute storage logic according to the DDL statement type when executing the DDL statement; Use the data synchronization tool to synchronize the second auxiliary table to the target database; The trigger is configured in the target database to monitor the INSERT operation of the target database, execute the DDL statement in the statement of the INSERT event, and perform DDL synchronization of a specific table.
2. A PostgreSQL database index incremental synchronization method according to claim 1, characterized in that: The index information includes the index name, the table name and the database instance name.
3. A PostgreSQL database index incremental synchronization method according to claim 2, characterized in that: The DDL execution event information includes the DDL statement type, the DDL target database instance name, the DDL object name, the DDL parent object name, the DDL execution statement and the DDL execution time.
4. A PostgreSQL database index incremental synchronization method according to claim 3, characterized in that: The DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX and DROP INDEX.
5. A PostgreSQL database index incremental synchronization method according to claim 4, characterized in that: The storage logic includes: When the DDL statement type is CREATE INDEX, the first storage method is used; When the DDL statement type is DROP TABLE, the second storage method is used; When the DDL statement type is other types, the third storage method is used.
6. A PostgreSQL database index incremental synchronization method according to claim 5, characterized in that: The first storage method includes: obtaining the current index name, the name of the table to which it belongs, and the name of the database instance and storing them in the first auxiliary table; obtaining the current DDL statement type, the DDL target database instance name, the DDL object name, the DDL parent object name, the DDL execution statement, and the DDL execution time and storing them in the second auxiliary table.
7. A PostgreSQL database index incremental synchronization method according to claim 5, characterized in that: The second storage method includes: searching for the corresponding table name from the first auxiliary table according to the current index name and database instance name; then obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
8. A PostgreSQL database index incremental synchronization method according to claim 5, characterized in that: The third storage method includes: obtaining the current DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time and storing them in the second auxiliary table.
9. A PostgreSQL database index incremental synchronization method according to claim 1, characterized in that: The target database includes a third auxiliary table, and the third auxiliary table is used to store INSERT events.
10. A PostgreSQL database index incremental synchronization system, characterized in that: include: Creation module 1, used to create a first auxiliary table in the source PostgreSQL database, and query the database to store all index information in the first auxiliary table; Creating module 2, creating a second auxiliary table in the source PostgreSQL database, wherein the second auxiliary table includes DDL execution event information; A judgment module is used to create a source-side PostgreSQL database trigger to monitor the DDL execution event information and execute storage logic according to different DDL statement types when executing DDL statements; A storage module, used to synchronize the second auxiliary table to the target database using a data synchronization tool; The synchronization module is used to configure the trigger in the target database to monitor the INSERT operation of the target database, execute the DDL statement in the statement of the INSERT event, and perform DDL synchronization of a specific table.
Citation Information
Patent Citations
cascade synchronization control method and system of a DDL operation
CN109508346A
Index creation method and device, electronic equipment and storage medium
CN111752901A
DDL synchronization method and device for PostgreSQL database, equipment and medium
CN112231407A
Database flash-back method and device based on WAL log file
CN115454960A
Using a stored procedure to access index configuration data in a remote database management system
US6934701B1
Cited By
Packet function implementation method and system based on PostgreSQL database and medium
CN120353823A
Data synchronization desensitization method and device, computer equipment and readable storage medium
CN121743404A
