Device and method for performing full-amount and incremental synchronization on post gres data

Abstract

The invention discloses a device and method for performing full-amount and incremental synchronization on post gres data, and belongs to the technical field of database synchronization, the device comprises a pulling device, a data pipeline and a writer, the pulling device is used for pulling synchronous data from a source database according to a target task type and issuing the synchronous data to the data pipeline, and the data pipeline is used for writing the synchronous data to the writer; the synchronous data comprises total data and incremental data; the data pipeline comprises a memory queue and a plurality of concurrency devices and is used for storing the synchronous data into the memory queue, fragmenting the synchronous data and loading the concurrency device corresponding to the target task type so as to concurrently record the fragmented synchronous data into the writer; and the writer is used for storing the locally recorded synchronous data into the target database. According to the method and the device, the full-set post gres full-amount and incremental data synchronization under the rust language is realized, and the synchronous task access can be simply and quickly performed through simple configuration of the ini file.

Description

Technical Field

[0001] The present invention relates to the technical field of database synchronization, and in particular to a device and method for performing full and incremental synchronization on postgres data. Background Art

[0002] Among current database management systems, PostgreSQL is a widely used open source relational database. Its data synchronization and migration capabilities are crucial to ensuring data consistency, high availability, and business continuity. However, existing PostgreSQL data synchronization tools on the market still have the following deficiencies in terms of comprehensiveness, flexibility, and performance optimization:

[0003] 1. Lack of DDL synchronization: During the incremental data synchronization process, most existing tools focus on the synchronization of data manipulation language (DML) operations such as INSERT, UPDATE, and DELETE, but ignore the synchronization of data definition language (DDL) changes such as table structure modification and index creation. This may lead to structural inconsistencies between the target database and the source database, affecting data integrity and the correct execution of business logic.

[0004] 2. Insufficient automatic breakpoint resume function: When facing large-scale data synchronization tasks, unforeseen factors such as network fluctuations and system failures may cause the synchronization process to be interrupted. Existing tools usually lack an effective automatic breakpoint resume mechanism, which increases the complexity and time cost of operations.

[0005] 3. Limited data verification and cleaning capabilities: Data quality is the key to successful data synchronization. After data synchronization, current tools often lack a comprehensive data verification mechanism to verify data consistency and integrity. At the same time, the ability to clean dirty data is also limited, which cannot meet users' high standards for data quality.

[0006] 4. Lack of scenario customization and accelerated synchronization capabilities: Different business scenarios have different requirements for data synchronization, such as real-time performance, throughput, and resource utilization. Most existing tools use general solutions, which are difficult to customize and optimize according to specific scenarios. They also lack efficient accelerated synchronization technology, resulting in poor performance in specific scenarios.

[0007] 5. Old technology stack and high integration complexity: Many existing PostgreSQL data synchronization tools are developed in older programming languages ​​such as Java, which limits their scalability and performance in new technology environments. In addition, some tools rely on specific open source scheduling platforms such as Apache Flink and Apache Kafka for integration, which increases the complexity and operation and maintenance costs of the system, and also limits users' right to choose independently. Summary of the invention

[0008] The purpose of the present invention is to provide a device and method for full and incremental synchronization of postgres data, so as to solve the problems in the prior art of postgres data synchronization tools, such as lack of DDL synchronization support during incremental synchronization, insufficient automatic breakpoint resumption function, limited data verification and cleaning capabilities, lack of scene customization and accelerated synchronization capabilities, and outdated technology stack and high integration complexity.

[0009] To achieve the above objectives, this application adopts the following technical solutions:

[0010] A method for full and incremental synchronization of postgres data in the present application includes a puller, a data pipeline, and a writer, wherein:

[0011] The puller is used to pull synchronization data from the source database according to the target task type and send the synchronization data to the data pipeline, wherein the synchronization data includes full data and incremental data;

[0012] The data pipeline includes a memory queue and several concurrent devices, which are used to store the synchronous data in the memory queue and slice it, and load the concurrent device corresponding to the target task type to concurrently record the sliced ​​synchronous data into the writer;

[0013] The writer is used to store the synchronized data recorded locally into the target database.

[0014] Preferably, the target task types include full synchronization tasks and incremental synchronization tasks.

[0015] Preferably, the puller comprises a first pulling module and a second pulling module, wherein:

[0016] The first pulling module is used to pull the full data of all subscription tables in the source database by using a database query statement when the target task type is a full synchronization task;

[0017] The second pulling module is used to, when the target task type is an incremental synchronization task, use a start replication command of a postgres database to subscribe to a write-ahead log of a source database to obtain incremental data of all subscription tables in the source database.

[0018] Preferably, the second pulling module includes a creation unit, an insertion unit and a subscription unit, wherein:

[0019] The creation unit is used to create auxiliary tables and triggers in the source database;

[0020] The inserting unit is used to use the trigger to monitor the data definition command completion event of the source database to obtain the executed data definition language information, and insert the data definition language information into the auxiliary table;

[0021] The subscription unit is used to subscribe to the auxiliary table.

[0022] Preferably, the data pipeline further includes a first sharding unit and a second sharding unit, wherein:

[0023] The first sharding unit is configured to evenly slice the full amount of data of the current subscription table cached in the memory queue according to the number of data items when the target task type is a full synchronization task;

[0024] The second sharding unit is used to split the current incremental data in the memory queue into sub-queues corresponding to the subscription tables one by one, and convert each update data into a combination of delete data and insert data.

[0025] Preferably, the writer comprises a first writing module and a second writing module, wherein:

[0026] The first writing module is used to send the full amount of data in each slice to the target database using a structured query language for batch insertion;

[0027] The second writing module is used to send the converted data of each sub-queue to the target database by using the structured query language of batch deletion and batch insertion.

[0028] Preferably, the data pipeline further comprises a breakpoint resume module, and the breakpoint resume module is used to record the synchronization data in a breakpoint log and determine a restart point according to the breakpoint log.

[0029] Preferably, the breakpoint resume transmission module includes a first breakpoint resume transmission unit and a second breakpoint resume transmission unit, wherein:

[0030] The first breakpoint resume unit is used to periodically record the maximum key value of the synchronized full data in the breakpoint log, and determine whether to resume the breakpoint from the current progress according to the breakpoint resume configuration information;

[0031] The second breakpoint resume unit is used to periodically record the write-ahead log sequence number of the synchronized incremental data in the breakpoint log, and determine whether to restart the subscription starting from the write-ahead log sequence number according to the breakpoint resume configuration information.

[0032] Preferably, the breakpoint resume transmission configuration information includes a breakpoint resume transmission mechanism equal to true and a breakpoint resume transmission mechanism equal to false.

[0033] A method for full and incremental synchronization of postgres data includes the following steps:

[0034] In the puller, synchronized data is pulled from the source database according to the target task type, and the synchronized data is sent to the data pipeline. The synchronized data includes full data and incremental data.

[0035] After the data pipeline receives the synchronization data, the synchronization data is stored in its memory queue and sliced, and the concurrent device corresponding to the target task type is loaded to concurrently record the sliced ​​synchronization data into the writer;

[0036] The synchronized data recorded locally in the writer is stored in the target database.

[0037] This application has the following beneficial effects:

[0038] This application implements a full set of postgres full + incremental data synchronization in the rust language, and through simple configuration of the ini file, the synchronization task can be accessed simply and quickly. At the same time, this application can configure different data processing methods and concurrency algorithms for different usage scenarios and different data types, which greatly improves the synchronization performance of postgres data. In addition, it also realizes breakpoint resumption and incremental synchronization of DDL information, which can meet the high requirements of enterprises for data synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art are briefly introduced below.

[0040] Figure 1 It is a structural schematic diagram of a device for performing full and incremental synchronization of postgres data provided by an embodiment of the present application;

[0041] Figure 2 It is a flow chart of a method for full and incremental synchronization of postgres data provided by an embodiment of the present application;

[0042] Figure 3 It is a schematic diagram of an electronic device for implementing a multi-tenant alarm management method provided in an embodiment of the present application. DETAILED DESCRIPTION

[0043] To make the technical solution of the present application clearer, the present invention is further described in detail below in conjunction with the accompanying drawings and specific embodiments. The terms "first", "second", etc. in the claims and specification of the present application are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the terms used in this way can be interchangeable under appropriate circumstances. This is only to describe the distinction method used when describing objects with the same attributes in the embodiments of the present application. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, so that a process, method, system, product or device containing a series of units is not necessarily limited to those units, but may include other units that are not clearly listed or inherent to these processes, methods, products or devices.

[0044] Example 1

[0045] like Figure 1 As shown, this embodiment provides a device for full and incremental synchronization of postgres data, including a puller, a data pipeline, and a writer, wherein:

[0046] The puller is used to pull synchronization data from the source database according to the target task type and send the synchronization data to the data pipeline. The synchronization data includes full data and incremental data.

[0047] The data pipeline includes a memory queue and several concurrent devices, which are used to store and shard the synchronous data in the memory queue, and load the concurrent device corresponding to the target task type to concurrently record the sharded synchronous data into the writer;

[0048] The writer is used to store the synchronized data recorded locally into the target database.

[0049] Before executing the postgres data synchronization task, you need to configure the task in the configuration file ini. The task configuration mainly includes seven parts, namely source database configuration, target database configuration, synchronization data range configuration, routing mode configuration for library / table synchronization, concurrent configuration, pipeline configuration, and breakpoint resume configuration. The synchronization tasks here include full synchronization tasks and incremental synchronization tasks. The task configuration result needs to be determined according to the specific synchronization task type. The complete task configuration information in the ini file is as follows:

[0050] [extractor]db_type=pg; database type, pg stands for postgres

[0051] extract_type=snapshot; data extraction method, full task is snapshot, incremental task is cdc

[0052] url=postgres: / / postgres:postgres@127.0.0.1:5433 / postgres?options[statement_timeout]=10s; Source database address[sinker]db_type=pg; Database typesink_type=write; Write type, for data synchronization (full / incremental), sink_type=writeurl=postgres: / / postgres:postgres@127.0.0.1:5434 / postgres?options[statement_timeout]=10s; Target database addressbatch_size=200; When writing in batches, the maximum number of data in each batch[filter]do_dbs=; Which databases need to be synchronized, format: db_1,db_2, which means synchronizing the data of db_1 and db_2; wildcards * are also supported, such as db_*, which means synchronizing all databases starting with db_ignore_dbs=; Which databases' data are not synchronized? This is usually used in conjunction with do_dbs when wildcards are used. The configuration method is the same as do_dbs. do_tbs=db_1.*,db_2.*; Which tables' data are synchronized? The format is [database name].[table name], and wildcards * are also supported. ignore_tbs=; Which tables' data are not synchronized? The configuration is the same as do_dbs. do_events=insert; The data type to be synchronized is insert for full tasks and insert,update,delete for incremental tasks. [router] db_map=db_1:dst_db_1; Database-level routing, which means that all tables in the db_1 database are synchronized to the target database dst_db_1. tb_map=db_2.tb_1:dst_db_2.dst_tb_1; Table-level routing, which means that tb_1 in the db_2 database is synchronized to the target database dst_db_1. The table is synchronized to the dst_tb_1 table of the target database dst_db_2 [parallelizer] parallel_type = snapshot; Concurrency accelerator, the full task is snapshot parallel_size = 8; Concurrency, which represents the maximum number of threads that can be used to concurrently write to the target database at one time [pipeline] buffer_size = 16000; The maximum number of cached data in the pipeline is used to limit the resources used by the task

[0053] buffer_memory_mb=200; How many MB of data can be cached in the pipeline at most to limit the resource usage of the task. At least one checkpoint_interval_secs=10 is required to configure the task progress regularly. The task progress will be recorded in position.log and finished.log [runtime]log_level=info; Log level, default is infolog4rs_file=. / log4rs.yaml; Log configuration file addresslog_dir=. / logs; Log file address

[0054] [resumer]

[0055] resume_from_log=true; When the task is restarted after being interrupted, whether to load the current progress from the position log position.log that records the task progress and resume the data from the breakpoint. If true, data synchronization starts from the breakpoint. If false, it means starting from the beginning according to the configuration without resume.

[0056] The contents of [extractor], [sinker], [filter], [router], [parallelizer], [pipeline], and [resumer] correspond to the source database configuration, target database configuration, synchronization data range configuration, routing mode configuration for database / table synchronization, concurrent configuration, pipeline configuration, and breakpoint resume configuration. The difference between the incremental synchronization task configuration and the full synchronization task configuration lies in the source database data pulling method, subscribed data type, and concurrent configuration. Specifically, the source database data pulling method in the full synchronization task is configured to be through the database query statement (SQL SELECT), and the source database data pulling method in the incremental synchronization task is configured to pull by subscribing to the write-ahead (wal) log of the source database through the start replication (START_REPLICATION) command of the postgres database; the data type synchronized in the full synchronization task is configured as the insert operation, and the data type synchronized in the incremental synchronization task is configured as the insert operation, update operation, and delete operation, which also correspond to the add, modify, and delete operations of the source database, that is, the incremental synchronization task can support the DDL operation of synchronizing the source database; the concurrent device in the full synchronization task is configured as snapshot, and the concurrent device in the incremental synchronization task is configured as rdb_merge.

[0057] After the task configuration in the ini file is completed, the synchronization task begins to execute. In this embodiment, a complete data synchronization includes two parts: full data synchronization and incremental data synchronization. Although these two tasks are executed one after another, they are a related whole. To ensure the integrity and correctness of the data, the starting point of the incremental task will be less than or equal to the ending point of the full task.

[0058] Synchronization tasks are mainly in Figure 1 The process is completed in the device shown, which mainly includes three parts: an extractor, a data pipeline and a sinker.

[0059] Among them, the extractor is mainly used to pull synchronization data from the source database according to the target task type. The synchronization data includes incremental data and full data. Specifically, the extractor includes a first pulling module and a second pulling module. When the target task type is a full synchronization task, the first pulling module uses a database query statement such as SELECT *FROM [table_name] ORDER by id to pull the full data of all subscription tables in the source database; when the target task type is an incremental synchronization task, the second pulling module subscribes to the wal log of the source database through the START_REPLICATION command of the postgres database, and specifies the subscription starting point through start_lsn. As long as the source database is in use, incremental data will continue to flow, so there is no end point. Of course, the user can interrupt the synchronization by stopping the task. After the extractor subscribes to the wal log of the source database through the START_REPLICATION command of the postgres database, the source database will pass its change data to the subscriber. The data itself contains the change types insert, update and delete. Parsing it can obtain DDL information and DML information.

[0060] The wal log of the Postgres database only contains the DML information of the source database, but does not include the DDL information that has been executed. In order to synchronize the DDL information of the source database during incremental synchronization, the second pull module also includes a creation unit, an insertion unit and a subscription unit. First, the creation unit creates an auxiliary table ape_dts_ddl_command in the source database to record the DDL information that has been executed locally, and creates a trigger trigger to monitor the data definition command completion event ddl_command_end; then, the insertion unit determines the DDL information that has been executed by the source database through the ddl_command_end event monitored by the trigger, and then inserts the data definition language information into the ape_dts_ddl_command table; finally, the subscription unit subscribes to the ape_dts_ddl_command table to obtain the DDL information of the source database through the wal log.

[0061] The data pipeline includes a memory queue and several parallelizers. After the synchronization data is pulled from the source database by the extractor, it will be sent to the pipeline. The pipeline first stores it in the queue and then shards it according to the target task type. Specifically, the data pipeline also includes a first sharding unit and a second sharding unit. When the target task type is a full synchronization task, the first sharding unit divides the full data of the current subscription table cached in the memory queue into parallel_size slices according to the number of data items; when the target task type is an incremental synchronization task, the second sharding unit splits the current incremental data in the memory queue into sub-queues corresponding to the subscription table one by one. At the same time, because the previous value, the next value, and the primary key / unique key of each update data are different, batch update cannot be used to improve performance. The second sharding unit will also convert each update data in the sub-queue into a combination of delete data + insert data.

[0062] Then, multi-threaded concurrent write operations are performed in combination with the parallelizers corresponding to the target task type, such as the snapshotparallelizer corresponding to the full synchronization task and the rdb_merge parallelizer corresponding to the incremental synchronization task. Because in the full synchronization task, if a database insert sql statement is executed for each data, the network overhead will greatly reduce the data synchronization performance. At the same time, the snapshot parallelizer can accelerate the synchronization based on the characteristics that all full synchronization data are inserts and there are no duplicate primary keys. Specifically, the writer includes a first writing module and a second writing module. The first writing module uses the structured query statement corresponding to batch insert to write the full data in each parallel_size slice into the target database. The second writing module uses the structured query statement corresponding to batch delete and batch insert to write the incremental data in each subqueue into the target database.

[0063] If the task is interrupted abnormally, the breakpoint resume module is used to restart. The breakpoint resume module includes the first breakpoint resume unit and the second breakpoint resume unit. In the full synchronization task, for a table with a single primary key / unique key, the task will synchronize the data to the target database according to the key field from small to large. In the incremental synchronization task, the pre-write log sequence number LSN of each change data can be parsed through the subscribed WAL log. LSN is a number used to identify WAL. Therefore, the first breakpoint resume unit will record the maximum key value of the synchronized full data in the breakpoint log position.log at intervals (checkpoint_interval_secs, default 10s). If the task is interrupted abnormally and the user restarts manually, it will determine whether to restart the task from the breakpoint according to the breakpoint resume value resume_from_log configured in the ini file. When resume_from_log=true, the current progress is loaded and breakpoint resume is performed. If resume_from_log=

[0064] If false, it will start from the beginning according to the configuration without resuming the data from breakpoints. The second resuming unit will periodically record the LSN of the synchronized incremental data in position.log. After the task is interrupted, it will determine whether to restart the subscription from the recorded LSN according to resume_from_log.

[0065] This embodiment implements a full set of postgres full + incremental data synchronization under the rust language, and through simple configuration of the ini file, the synchronization task access can be simple and fast. At the same time, this embodiment can configure different data processing methods and concurrency algorithms for different usage scenarios and different data types, which greatly improves the synchronization performance of postgres data. In addition, this embodiment also realizes breakpoint resumption and incremental synchronization of DDL information, which can meet the high requirements of enterprises for data synchronization.

[0066] During the data synchronization process, the user may want to clean or process the data. For example, to filter out all the data with owner_id=100 in table_a, in this case, in this embodiment, the user can use the scripting language Lua to write a processing code and specify the location of the Lua code file lua_code_file in the configuration file. Specifically, [processor]

[0067] lua_code_file=. / dt-test / lua_code.lua, so that no matter in the full synchronization task or the incremental synchronization task, after the task is started, as long as the lua_code_file is detected, its code will be loaded. In this way, each piece of data put into the pipeline by the extractor will be passed into the lua code for processing. The user can perform processing operations on each line of code in the lua code, including but not limited to adding columns, discarding columns, modifying column values, and discarding entire lines of data. After the processing is completed, the pipeline will transmit it to the accelerator to continue data synchronization.

[0068] Multiple validators can also be set in the pipeline to implement data verification. Specifically, after the data synchronization task is completed, the user can also choose to start the data verification task. At this time, first change the configuration of sink_type in the ini file from write to check, which means that the data is verified instead of written to the target database. After the configuration modification is completed, the extractor will pull the current table data into the pipeline, and the concurrent device will split the current table data into parallel_size parts and send them to multiple validators. After each validator receives a batch of data src_data from the source database, it first determines whether the current table has a unique primary key / unique key. If so, batch operations are performed to improve performance. Preferably, a database query statement similar to SELECT * FROMtable_a WHERE id IN (1, 2, 3, 4) is constructed to pull the corresponding data dst_data of src_data in the target database from the target database at one time, and pull src_data at one time. If the corresponding data dst_data does not exist in the target database, a serial operation is performed to construct a database query statement similar to SELECT * FROM table_a WHERE id=1 AND value=2 for each data of src_data, and dst_data is pulled from the target database accordingly.

[0069] Each data in src_data is recorded as src_row. After the verifier pulls the data dst_data corresponding to src_data in the target database, if there is no dst_row corresponding to src_row in dst_data, it is determined that the data is missing and its information is recorded in the log miss.log. If there is, src_row is compared with the corresponding dst_row column by column. If the values ​​of some columns are different, the data are determined to be inconsistent, and the information of src_row is recorded in the log diff.log. Finally, the user can confirm the verification result by checking miss.log and diff.log.

[0070] This embodiment not only realizes the full and incremental synchronization of postgres data, but also realizes data cleaning and data verification, which can meet the needs of enterprises for long-term data synchronization.

[0071] Example 2

[0072] like Figure 2 As shown, a method for full and incremental synchronization of postgres data includes:

[0073] S110, in the puller, pull synchronization data from the source database according to the target task type, and send the synchronization data to the data pipeline, wherein the synchronization data includes full data and incremental data;

[0074] S120, after the data pipeline receives the synchronization data, the synchronization data is stored in its memory queue and fragmented, and a concurrent device corresponding to the target task type is loaded to concurrently record the fragmented synchronization data into a writer;

[0075] S130: Store the synchronization data recorded locally in the writer into a target database.

[0076] This embodiment is applied to the device provided by the above embodiment, and has the corresponding beneficial effects of the above device. For technical details not fully described in this embodiment, please refer to the devices provided by all the above embodiments of the present invention.

[0077] Example 3

[0078] like Figure 3 As shown, this embodiment provides a computer device, including a memory, a processor, and a computer program stored in the memory, and the processor executes the computer program to implement the steps of the above-mentioned method for full and incremental synchronization of postgres data.

[0079] The computer device can be a server or a terminal. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, the steps of a method for full and incremental synchronization of postgres data are implemented.

[0080] This embodiment also provides a computer-readable storage medium on which a computer program or instruction is stored. When the computer program or instruction is executed by a processor, the steps of the method for full and incremental synchronization of postgres data as described above are implemented.

[0081] This embodiment also provides a computer program product, including a computer program or instructions, which, when executed by a processor, implements the steps of the above-mentioned method for full and incremental synchronization of postgres data.

[0082] 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, optical fiber transmissions, wireless transmissions, 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 for storage in the computer-readable storage medium in each computing / processing device.

[0083] The computer program instructions for performing the operation of the present invention may be assembler 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++, etc., and conventional procedural programming languages, such as "C" language or similar programming languages. Computer-readable program instructions may be executed completely 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 completely 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., using 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, and the electronic circuit may execute the computer-readable program instructions, thereby realizing various aspects of the present invention.

[0084] These computer-readable program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, thereby producing a machine, so that when these instructions are executed by the processor of the computer or other programmable data processing device, a device that implements the functions / actions specified in one or more boxes in the flowchart and / or block diagram is generated. These computer-readable program instructions can also be stored in a computer-readable storage medium, and these instructions cause the computer, programmable data processing device, and / or other equipment to work in a specific manner, so that the computer-readable medium storing the instructions includes a manufactured product, which includes instructions for implementing various aspects of the functions / actions specified in one or more boxes in the flowchart and / or block diagram.

[0085] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device so that a series of operating steps are performed on the computer, other programmable data processing apparatus, or other device to produce a computer-implemented process, thereby causing the instructions executed on the computer, other programmable data processing apparatus, or other device to implement the functions / actions specified in one or more boxes in the flowchart and / or block diagram.

[0086] The above-mentioned embodiments only express several implementation methods of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the scope of the patent of the present invention. It should be pointed out that, for ordinary technicians in this field, several variations and improvements can be made without departing from the concept of the present invention, which all belong to the protection scope of the present invention. Therefore, the protection scope of the patent of the present invention shall be subject to the attached claims.

Claims

1. A device for full and incremental synchronization of postgres data, characterized in that: Includes pullers, data pipelines, and writers, where: The puller is used to pull synchronization data from the source database according to the target task type and send the synchronization data to the data pipeline, wherein the synchronization data includes full data and incremental data; The data pipeline includes a memory queue and several concurrent devices, which are used to store the synchronous data in the memory queue and slice it, and load the concurrent device corresponding to the target task type to concurrently record the sliced ​​synchronous data into the writer; The writer is used to store the synchronized data recorded locally into the target database.

2. The device for performing full and incremental synchronization of postgres data according to claim 1, characterized in that: The target task types include full synchronization tasks and incremental synchronization tasks.

3. The device for performing full and incremental synchronization of postgres data according to claim 2, characterized in that: The puller comprises a first pulling module and a second pulling module, wherein: The first pulling module is used to pull the full data of all subscription tables in the source database by using a database query statement when the target task type is a full synchronization task; The second pulling module is used to, when the target task type is an incremental synchronization task, use a start replication command of a postgres database to subscribe to a write-ahead log of a source database to obtain incremental data of all subscription tables in the source database.

4. The device for performing full and incremental synchronization of postgres data according to claim 3, characterized in that: The second pull module includes a creation unit, an insertion unit and a subscription unit, wherein: The creation unit is used to create auxiliary tables and triggers in the source database; The inserting unit is used to use the trigger to monitor the data definition command completion event of the source database to obtain the executed data definition language information, and insert the data definition language information into the auxiliary table; The subscription unit is used to subscribe to the auxiliary table.

5. The device for performing full and incremental synchronization of postgres data according to claim 3, characterized in that: The data pipeline further includes a first sharding unit and a second sharding unit, wherein: The first sharding unit is configured to evenly slice the full amount of data of the current subscription table cached in the memory queue according to the number of data items when the target task type is a full synchronization task; The second sharding unit is used to split the current incremental data in the memory queue into sub-queues corresponding to the subscription tables one by one, and convert each update data into a combination of delete data and insert data.

6. The device for performing full and incremental synchronization of postgres data according to claim 5, characterized in that: The writer comprises a first writing module and a second writing module, wherein: The first writing module is used to send the full amount of data in each slice to the target database using a structured query language for batch insertion; The second writing module is used to send the converted data of each sub-queue to the target database by using the structured query language of batch deletion and batch insertion.

7. The device for performing full and incremental synchronization of postgres data according to claim 3, characterized in that: The data pipeline also includes a breakpoint resume module, which is used to record the synchronization data in a breakpoint log and determine a restart point according to the breakpoint log.

8. The device for performing full and incremental synchronization of postgres data according to claim 7, characterized in that: The breakpoint resume transmission module includes a first breakpoint resume transmission unit and a second breakpoint resume transmission unit, wherein: The first breakpoint resume unit is used to periodically record the maximum key value of the synchronized full data in the breakpoint log, and determine whether to resume the breakpoint from the current progress according to the breakpoint resume configuration information; The second breakpoint resume unit is used to periodically record the write-ahead log sequence number of the synchronized incremental data in the breakpoint log, and determine whether to restart the subscription starting from the write-ahead log sequence number according to the breakpoint resume configuration information.

9. The device for performing full and incremental synchronization of postgres data according to claim 8, characterized in that: The breakpoint resume transmission configuration information includes the breakpoint resume transmission mechanism being equal to true and the breakpoint resume transmission mechanism being equal to false.

10. A method for full and incremental synchronization of postgres data, characterized in that: The following steps are involved: In the puller, synchronized data is pulled from the source database according to the target task type, and the synchronized data is sent to the data pipeline. The synchronized data includes full data and incremental data. After the data pipeline receives the synchronization data, the synchronization data is stored in its memory queue and sliced, and the concurrent device corresponding to the target task type is loaded to concurrently record the sliced ​​synchronization data into the writer; The synchronized data recorded locally in the writer is stored in the target database.

Images