A method and system for incremental synchronization of PostgreSQL database indexes

Abstract

This invention belongs to the field of database synchronization technology, specifically relating to a method and system for incremental synchronization of PostgreSQL database indexes. A first auxiliary table is created in the source PostgreSQL database, and all index information is stored in the first auxiliary table after database queries. A second auxiliary table is created in the source PostgreSQL database, including DDL execution event information. A trigger is created in the source PostgreSQL database to listen for DDL execution events. When a DDL statement is executed, storage logic is executed according to the different DDL statement types. A data synchronization tool is used to synchronize the second auxiliary table to the target database. A trigger is configured in the target database to listen for INSERT operations and execute the DDL statements in the INSERT event statements to perform DDL synchronization of specific tables. It supports synchronizing only DDL statements of specific tables, resulting in higher synchronization efficiency, stronger configurability, less modification to the source database, and no need to add a preset .so file configuration to the source database's lib directory.

Description

Technical Field

[0001] This invention belongs to the field of database synchronization technology, specifically relating to a method and system for incremental synchronization of PostgreSQL database indexes. Background Technology

[0002] In relational databases, DDL (Data Definition Language) and DML (Data Manipulation Language) are two of the 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] Incremental synchronization of database DDL (Data Definition Language) refers to synchronizing structural changes in one database with other databases of the same structure to ensure that these databases have the same structure and definitions.

[0004] Existing PostgreSQL database DDL incremental synchronization technology utilizes triggers. When a DDL statement is executed, it is saved to an auxiliary table. Then, database synchronization technology synchronizes the data in the auxiliary table to the target database. Finally, the DDL statement from the auxiliary table is executed in the target database, thus achieving DDL synchronization. In scenarios where all tables in the entire database are synchronized, there is no need to identify the target of the DDL statement execution, making this synchronization method feasible.

[0005] However, when encountering scenarios where only the indexes of a portion of the tables need to be synchronized, for DDL statements that change PostgreSQL database indexes, such as DDL statements that delete an index, the index in the database has already changed and been deleted when the trigger is triggered. This makes it impossible to query which table the deleted index belongs to, nor can it find the statement information of the deleted index, making it impossible to determine whether the index DDL statement needs to be synchronized. Summary of the Invention

[0006] The purpose of this invention is to provide a method and system for incremental synchronization of PostgreSQL database indexes, which supports synchronizing only DDL statements of specific tables, has higher synchronization efficiency, and is more configurable, thereby solving the problems in the prior art mentioned in the background section.

[0007] To achieve the above objectives, the present invention adopts the following technical solution: 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 by querying the database; creating a second auxiliary table in the source PostgreSQL database, the second auxiliary table including DDL execution event information; creating a trigger in the source PostgreSQL database to listen to the DDL execution event information, and executing storage logic according to different DDL statement types when executing DDL statements; using a data synchronization tool to synchronize the second auxiliary table to the target database; configuring the trigger in the target database to listen to INSERT operations in the target database, executing the DDL statements in the INSERT event statements, and performing DDL synchronization of specific tables.

[0008] Preferably, the index information includes the index name, the name of the table to which it belongs, and the name of the database instance.

[0009] Preferably, 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.

[0010] Preferably, the DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX.

[0011] Preferably, the storage logic includes: when the DDL statement type is CREATE INDEX, a first storage method is used; when the DDL statement type is DROP TABLE, a second storage method is used; and when the DDL statement type is any other type, a third storage method is used.

[0012] 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 name of the DDL target database instance, the name of the DDL object, the name of the DDL parent object, the DDL execution statement, and the DDL execution time and storing them in the second auxiliary table.

[0013] Preferably, the second storage method includes: searching for the corresponding table name in the first auxiliary table based on the current index name and database instance name; and 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.

[0014] 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.

[0015] Preferably, the target database includes a third auxiliary table for storing INSERT events.

[0016] On the other hand, this invention proposes a PostgreSQL database index incremental synchronization system, comprising:

[0017] Create Module 1, which is used to create a first auxiliary table in the source PostgreSQL database and store all index information in the first auxiliary table when querying the database;

[0018] Create Module 2: Create a second auxiliary table in the source PostgreSQL database. The second auxiliary table includes DDL execution event information.

[0019] The judgment module is used to create source PostgreSQL database triggers to listen for DDL execution event information and execute storage logic according to different DDL statement types when executing DDL statements.

[0020] The storage module is used to synchronize the second auxiliary table to the target database using data synchronization tools;

[0021] The synchronization module is used to configure the trigger in the target database to listen for INSERT operations in the target database, execute the DDL statements in the INSERT event statement, and perform DDL synchronization for a specific table.

[0022] Technical effects and advantages of the present invention: The incremental synchronization method and system for PostgreSQL database indexes proposed in this invention have the following advantages compared with the prior art:

[0023] A first auxiliary table is created in the source PostgreSQL database, and all index information is stored in the first auxiliary table after database queries. A second auxiliary table is created in the source PostgreSQL database, which includes DDL execution event information. A trigger is created in the source PostgreSQL database to listen for DDL execution events. When a DDL statement is executed, storage logic is executed according to the different DDL statement types. The second auxiliary table is synchronized to the target database using a data synchronization tool. A trigger is configured in the target database to listen for INSERT operations and execute the DDL statements in the INSERT event statements to perform DDL synchronization of specific tables. It supports synchronizing only DDL statements of specific tables, resulting in higher synchronization efficiency, stronger configurability, and less modification to the source database. It does not require adding a preset .so file configuration in the source database's lib directory. The trigger configurations are all built-in functions already available in PostgreSQL, resulting in stronger compatibility and portability. Attached Figure Description

[0024] Figure 1 This is a flowchart of a PostgreSQL database index incremental synchronization method according to the present invention;

[0025] Figure 2 This is a module diagram of a PostgreSQL database index incremental synchronization system according to the present invention. Detailed Implementation

[0026] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. The specific embodiments described herein are merely used to explain the present invention and are not intended to limit the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0027] The embodiments of the present invention provide, for example Figure 1 The method shown is a PostgreSQL database index incremental synchronization method, which includes:

[0028] 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);

[0029] Specifically, the index information includes the index name, the name of the table to which it belongs, and the name of the database instance.

[0030] Step 2: Create a second auxiliary table (auxiliary table T2) in the source PostgreSQL database. The second auxiliary table includes DDL execution event information.

[0031] Specifically, 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.

[0032] Step 3: Create a source PostgreSQL database trigger to listen for the DDL execution event information, and execute the storage logic according to the different DDL statement types when executing DDL statements;

[0033] Specifically, DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROPTABLE, CREATE INDEX, and DROP INDEX.

[0034] When the DDL statement type is CREATE INDEX, the 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 database instance name 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.

[0035] When the DDL statement type is DROP TABLE, the second storage method is used; the second storage method includes: looking up the corresponding table name from the first auxiliary table based on the current index name and database instance name; and 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.

[0036] When the DDL statement type is any other type, a third storage method is used; 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 a second auxiliary table.

[0037] Step 4: Use a data synchronization tool to synchronize the second auxiliary table to the target database;

[0038] Specifically, the target database includes a third auxiliary table (auxiliary table T3), which is used to store INSERT events.

[0039] Step 5: Configure the trigger in the target database to listen for INSERT operations in the target database, execute the DDL statements in the INSERT event statement, and perform DDL synchronization for the specific table.

[0040] Specifically, by configuring a trigger in the target database to listen for the INSERT event of the third auxiliary table and executing the corresponding DDL statement, DDL index synchronization can be achieved.

[0041] In this embodiment, by performing DDL synchronization on specific tables, it is possible to synchronize only the DDL statements of specific tables, which results in higher synchronization efficiency, stronger configurability, less modification to the source database, no need to add preset .so files in the lib directory of the source database, and the trigger configurations are all existing built-in functions of PostgreSQL, which enhances compatibility and portability.

[0042] On the other hand, this invention proposes a PostgreSQL database index incremental synchronization system, such as... Figure 2 As shown, it includes: Creation Module 1, Creation Module 2, Judgment Module, Storage Module, and Synchronization Module.

[0043] Specifically, module one is used to create a first auxiliary table in the source PostgreSQL database and to store all index information in the first auxiliary table by querying the database.

[0044] Specifically, module two creates a second auxiliary table in the source PostgreSQL database, which includes DDL execution event information;

[0045] Specifically, the judgment module is used to create source PostgreSQL database triggers to listen for DDL execution event information, and execute storage logic according to different DDL statement types when executing DDL statements;

[0046] Specifically, the storage module is used to synchronize the second auxiliary table to the target database using a data synchronization tool;

[0047] Specifically, the synchronization module is used to configure the trigger in the target database to listen for INSERT operations in the target database, execute the DDL statements in the INSERT event statement, and perform DDL synchronization for a specific table.

[0048] Furthermore, the creation module one, creation module two, judgment module, storage module, and synchronization module mentioned above can also implement other functions of the aforementioned PostgreSQL database index incremental synchronization method during execution. Details are as follows.

[0049] (1) Create an auxiliary table T1 in the source PostgreSQL database, which contains at least the following: index name, table name, and database instance name;

[0050] (2) Before synchronization begins, perform a full database query to store the index names, table names, and database instance names of all indexes into the auxiliary table T1;

[0051] (3) Create an auxiliary table T2 in the source PostgreSQL database, which contains at least the following: DDL statement type, DDL target database instance name, DDL object name, DDL parent object name, DDL execution statement, and DDL execution time;

[0052] DDL statement types can be categorized as follows: CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX.

[0053] DDL object name: refers to the execution object of the DDL statement. The object is different depending on the type of DDL statement. For example, when using CREATE TABLE, ALTER TABLE, or DROP TABLE, the DDL object name is the table name; when using CREATE INDEX or DROP INDEX, it is the index name.

[0054] The parent object name of a DDL statement varies depending on the type of the statement. For example, when using CREATE TABLE, ALTER TABLE, or DROP TABLE, the parent object name is the database instance name; when using CREATE INDEX or DROP INDEX, it is the name of the table to which the index belongs.

[0055] (4) Create a source PostgreSQL database trigger to listen for DDL execution events. When a DDL statement is executed, execute the following logic based on the DDL statement type (the DDL statement type is the trigger's built-in variable tg_tag):

[0056] A. When the DDL statement type is CREATE INDEX, retrieve the current "index name", "table name", and "database instance name" and store them in the auxiliary table T1; retrieve 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 the auxiliary table T2.

[0057] B. When the DDL statement type is DROP TABLE, since this type of statement does not have information about the name of the table it belongs to, it is necessary to look up the corresponding "name of the table it belongs to" in the auxiliary table T1 based on the current "index name" and "database instance name"; then, 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 the auxiliary table T2.

[0058] C. When the DDL statement type is any other type, retrieve 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.

[0059] (5) Use a data synchronization tool to synchronize the auxiliary table T2 to the target database. Alternatively, you can determine whether the data needs to be synchronized to the target database based on the "parent object name" in the T2 table.

[0060] (6) Configure a trigger in the target database to listen for INSERT operations on the table and execute the DDL statements in the INSERT statement to achieve DDL synchronization of the specific table.

[0061] The following example uses PostgreSQL as both the source and target databases, and both database instance names are public.

[0062] 1. Create an auxiliary table T1 on the source side. The SQL for creating the table is as follows:

[0063]

[0064]

[0065] 2. Create an auxiliary table T2 in the source database. The SQL for creating the table is as follows:

[0066]

[0067] 3. Initialize the data in table T1. The initialization SQL is as follows:

[0068] INSERT INTO indexsync_ddl_indexs(SELECT concat(schemaname,'.',indexname)idx,concat(schemaname,'.',tablename)tablename FROM pg_indexes WHERESchemaname!='pg_catalog')ON CONFLICT(indexname)DO UPDATE SET tablename=excluded.tablename

[0069] 4. Create trigger function F1. The SQL for creating the function is as follows:

[0070]

[0071]

[0072]

[0073] 5. Create trigger T1, the SQL is as follows:

[0074] CREATE EVENT TRIGGER indexsync_capture_ddl_trigger ON ddl_command_end

[0075] EXECUTE FUNCTION public.indexsync_capture_ddl();

[0076] 6. Create trigger function F2, and create the SQL as follows:

[0077]

[0078]

[0079] 7. Create trigger function T2, and create the SQL as follows:

[0080] CREATE EVENT TRIGGER indexsync_capture_ddl_drop_trigger ON sql_drop

[0081] EXECUTE FUNCTION public.indexsync_capture_ddl_drop()

[0082] 8. At this point, the DDL synchronization configuration of the source database is complete. The following steps involve DDL change operations.

[0083] 9. Create the demo table employee:

[0084]

[0085] 10. Create an index for the demo table employee:

[0086] CREATE INDEX employee_name_idx ON public.employee("name",address).

[0087] 11. Write the DDL statement into auxiliary table T2;

[0088] 12. Update the created index in the auxiliary table T1;

[0089] 13. Use the data synchronization tool to synchronize auxiliary table T2 to auxiliary table T3 in the target database;

[0090] 14. Configure a trigger in the target database to listen for the INSERT event of the auxiliary table T3, and execute the corresponding DDL statement to achieve DDL index synchronization.

[0091] Before the first synchronization begins, 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 a trigger detects a DDL statement of type "create index," the created index is written back to auxiliary table T1, and the DDL statement information is saved to auxiliary table T2. When a trigger detects a DDL statement of type "delete index," the table name and database instance name to which the index belongs are retrieved from auxiliary table T1, merged into the DDL statement information, and then saved to auxiliary table T2.

[0092] By using dynamic snapshots of indexes, the drawback of trigger functions executing after DDL events are resolved. It can support synchronizing only indexes of specific tables, avoiding unnecessary DDL synchronization operations and improving synchronization efficiency.

[0093] Furthermore, this embodiment 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, laptop computer, mobile terminal or other device with display and processing functions.

[0094] Specifically, the terminal device may include a processor (e.g., CPU), a communication bus, a user interface, a network interface, and memory. The communication bus is used to enable communication between these components; the user interface may include a display screen or an input unit such as a keyboard; the network interface may optionally include a standard wired interface or a wireless interface (such as a Wi-Fi interface); the memory may be high-speed RAM or stable non-volatile memory, such as disk storage, and may also optionally be a storage device independent of the aforementioned processor.

[0095] The memory stores a readable storage medium, which in turn 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 in this embodiment of the invention.

[0096] Understandably, a readable storage medium can be a tangible device capable of holding and storing instructions for use by an instruction execution device. A computer-readable storage medium 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: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disc read-only memory (CD-ROM), digital multifunction disc (DVD), memory sticks, floppy disks, mechanical encoding devices, such as punch cards or recessed protrusions storing instructions thereon, and any suitable combination thereof. The computer-readable storage medium as used herein is not to be construed as a transient signal itself, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides or other transmission media (e.g., light pulses through fiber optic cables), or electrical signals transmitted through wires.

[0097] The computer-readable program instructions described herein can be downloaded from computer-readable storage media to various computing / processing devices, or downloaded via a network, such as the Internet, local area network, wide area network, and / or wireless network, to an external computer or external storage device. The network may include copper transmission cables, fiber optic transmission, wireless transmission, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards them to the computer-readable storage media in the respective computing / processing device.

[0098] Computer program instructions used to perform operations of this disclosure may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, status 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++, etc., and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The computer-readable program instructions may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving 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., via the Internet using an Internet service provider). In some embodiments, electronic circuitry, such as programmable logic circuitry, field-programmable gate arrays (FPGAs), or programmable logic arrays (PLAs), is personalized by utilizing the status information of the computer-readable program instructions to implement various aspects of this disclosure.

[0099] Finally, it should be noted that the above description 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 foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for incremental synchronization of PostgreSQL database indexes, 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, which includes DDL execution event information; Create a source-side PostgreSQL database trigger to listen for the DDL execution event information, and execute storage logic according to the different DDL statement types when executing DDL statements; Use a data synchronization tool to synchronize the second auxiliary table to the target database; Configure the trigger in the target database to listen for INSERT operations in the target database, execute the DDL statements in the INSERT event statement, and perform DDL synchronization for a specific table.

2. The incremental synchronization method for PostgreSQL database indexes according to claim 1, characterized in that, The index information includes the index name, the name of the table to which it belongs, and the name of the database instance.

3. The incremental synchronization method for PostgreSQL database indexes 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. The incremental synchronization method for PostgreSQL database indexes according to claim 3, characterized in that, The DDL statement types include CREATE FUNCTION, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATEINDEX, 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 any other type, the third storage method is used.

6. The incremental synchronization method for PostgreSQL database indexes 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 name of the DDL target database instance, the name of the DDL object, the name of the DDL parent object, 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 in the first auxiliary table based on the current index name and database instance name; and 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 method for incremental synchronization of PostgreSQL database indexes 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 method for incremental synchronization of PostgreSQL database indexes according to claim 1, characterized in that, The target database includes a third auxiliary table, which is used to store INSERT events.

10. A PostgreSQL database index incremental synchronization system, characterized in that, include: Create Module 1, which is used to create a first auxiliary table in the source PostgreSQL database and store all index information in the first auxiliary table when querying the database; Create Module 2: Create a second auxiliary table in the source PostgreSQL database. The second auxiliary table includes DDL execution event information. The judgment module is used to create source PostgreSQL database triggers to listen for DDL execution event information and execute storage logic according to different DDL statement types when executing DDL statements. The storage module is used to synchronize the second auxiliary table to the target database using data synchronization tools; The synchronization module is used to configure the trigger in the target database to listen for INSERT operations in the target database, execute the DDL statements in the INSERT event statement, and perform DDL synchronization for a specific table.

Images