A method for collecting full data from Postgres tables

Abstract

The present invention particularly relates to a method for full data collection of Postgres tables. The method for full data collection of Postgres tables uses incremental logs to capture data changes and quickly locates a record in a table through ctid; obtains sharding intervals through data sampling tablesample, and the sampling function uses the bernoulli function provided by the programmer PG; the source-end database and the collection-end program use synchronization auxiliary tables to record the conditions and execution status of each shard, and the synchronization auxiliary tables include Tableinfos and Tableshards; based on the status data in the two synchronization auxiliary tables, they are updated in time during synchronization, and the shards of the table to be synchronized are obtained cyclically to complete the full data synchronization of all tables in the entire task. The method for full data collection of Postgres tables effectively solves the problem of uneven data sharding of large tables, and is applicable to tables without primary keys. It achieves consistent sharding collection based on the database incremental log, laying a good foundation for the accuracy of incremental data synchronization. At the same time, during the collection period, the table can still provide data modification services normally without affecting the business.

Description

Technical Field

[0001] The present invention relates to the technical field of database data transmission, and in particular to a method for collecting full data from a Postgres table. Background Art

[0002] With the prevalence of cloud computing and big data technologies, most data operations require the cloud. Database synchronization, especially between heterogeneous databases, is essential for big data operations on the cloud. Traditional relational databases like Postgres often contain a variety of large tables due to long-term data accumulation. To ensure data security, these tables need to be synchronized to a disaster recovery database.

[0003] The first data synchronization requires a full synchronization. Early data synchronization for large tables often used a single select query to obtain a table snapshot. However, this placed load on the server, easily causing query interruptions due to timeouts and full data collection failures. Therefore, full database table synchronization now often uses sharding. To prevent data skew, evenly sharding and maintaining the consistency of snapshots collected across shards is a technical challenge.

[0004] Traditional sharding technology often uses a select statement to find the maximum and minimum values of the source table's primary key field, then determines the number of shards based on the total number of entries in the table, and finally uses a uniform step size between the maximum and minimum values to complete the sharding interval determination. For primary keys of numeric types, sharding is performed directly according to division; for other types, such as string types, they need to be converted to large integers according to certain rules, then the integer interval is sharded, and finally the large integer at the sharding point is converted back to a string to complete the sharding. The main drawback of this method is that sharding that relies on primary key values may result in uneven sharding due to data skew; for composite primary keys, there is also the issue of which column to use for sharding, which may also cause data skew; it does not support tables without primary keys. Of course, users can also manually select a column as a sharding column, but this requires users to be familiar with the business, and may even be unable to pick out a suitable sharding column from the table, which can easily lead to implementation difficulties.

[0005] In addition, traditional sharding technology often requires that database tables be offline during full data collection or that write locks be placed on the entire table to prevent users from modifying data in order to maintain snapshot consistency. However, this will result in the table being unable to provide table modification services during full data collection, causing interruptions to related business programs.

[0006] In order to solve the above problems and maintain consistency in the sharding collection of large Postgres tables, the present invention proposes a method for collecting full data from Postgres tables. Summary of the Invention

[0007] In order to overcome the deficiencies of the prior art, the present invention provides a simple and efficient method for collecting full data from a Postgres table.

[0008] The present invention is achieved through the following technical solutions:

[0009] A method for collecting full data from a Postgres table, comprising the following steps:

[0010] Step S1: Full sharding based on the Postgres table ctid;

[0011] Capture data changes through incremental logs to ensure data is not lost; quickly locate a record in a table using ctid; obtain the sharding interval using data sampling (tablesample). The sampling function uses the bernoulli function provided by the programmer PG.

[0012] Step S2: Use the database incremental log to ensure the eventual consistency of all data;

[0013] Before full data collection, create an incremental collection slot to record the location of the current log sequence number (LSN).

[0014] After the full data collection is completed, obtain the incremental log and analyze the log to obtain the incremental changes;

[0015] The source database (postgres or sqlite) and the collection program use synchronization auxiliary tables to record the conditions and execution status of each shard. The synchronization auxiliary tables include the Tableinfos table and the Tableshards table.

[0016] The Tableinfos table is used to record all tables that require full synchronization and whether the synchronization is complete. The record includes the table unique identifier Table_id, schema name Shema_name, table name Table_name, whether sharding is complete Shard_completed, whether full synchronization is complete Completed, and incremental point Checkpoint. If complete, the Shard_completed and Completed values are set to 1; if not, they are set to 0.

[0017] The Tableshards table is used to record the unique table identifiers of all fully synchronized tables, the execution conditions of all shards, and whether the synchronization is complete. The record includes the unique table identifiers of all fully synchronized tables (Table_id), the shard sequence number (Shard_id), whether the sharding is completed (Completed), and the condition. If completed, the Completed flag is set to 1; if not, it is set to 0.

[0018] Based on the status data in the two synchronization auxiliary tables, they are updated in a timely manner during synchronization, and the shards of the table to be synchronized are obtained cyclically to complete the full data synchronization of all tables in the entire task.

[0019] In step S1, the physical location of the row in the table is recorded using ctid, which is represented by a tuple including a block number and a row number within the block.

[0020] In step S1, when the full amount of data transmission is collected, the automatic shrinking of the database table is stopped and restored after the collection is completed to avoid duplication of the full amount of data.

[0021] In step S2, to achieve consistency in full shard collection, set the programmer PG's wal_level parameter to logical (reasonable) to generate incremental logs; set the identity full parameter of the collected table to record complete record rows in the log, which is especially important for tables without primary keys; the database user performing the collection has the replica permission to obtain incremental data;

[0022] To ensure eventual data consistency, when newly inserted data is collected during full data collection, insert data records are added to the incremental log and processed using one of the following methods:

[0023] (1) Ignore primary key conflicts caused by newly inserted data;

[0024] (2) Delete the incremental data first and then insert it to ensure the final consistency of the data;

[0025] (3) By caching data in the message queue MQ or locally, incremental data conflicts are eliminated before sending it to the storage end.

[0026] In step S2, the Tableinfos table is first populated. The information of the synchronized tables comes from the synchronization task configuration file. Initially, the "Completed" flag for all tables is 0, indicating that full synchronization is not completed; the "Shard_completed" flag for all tables is 0, indicating that no shard information has been generated.

[0027] Then, SQL statements are used to sample all synchronized tables recorded in the Tableinfos table, obtain ctid information, and cover all region generation conditions according to the open-close interval principle. The information is recorded in the Tableshard table. The initial mark in the table indicating whether the sharding is complete is 0. The data is collected from each shard in the Tableshard table.

[0028] During collection, the collection task is executed serially for each shard, or in parallel for each shard.

[0029] When synchronizing a shard, SQL statements are generated according to the conditions in the Tableshards table to connect to the database through the JDBC or ODBC interface to obtain data and send it to the message queue MQ.

[0030] Before obtaining the first shard of the Tableshards table, the incremental log point is obtained and recorded in the Tableinfos table as the incremental log checkpoint. After a shard is synchronized, the sharding completion flag in the Tableshards table is set to 1. After all shards of a table are synchronized, the sharding completion flag in the Tableinfos table is updated to 1.

[0031] In step S2, based on the status data in the two synchronization auxiliary tables, the full data synchronization of all tables in the entire task is completed. The implementation steps are as follows:

[0032] Step S2.1: Read the record marked "Completed" as 0 in the Tableinfos table;

[0033] If no record with the value "Completed" set to 0 is found in the Tableinfos table, the full synchronization is complete.

[0034] If a record marked "Completed" as 0 is read from the Tableinfos table, the corresponding table unique identifier "Table_id" in the Tableshards table is read, and the record marked "Completed" as 0 is marked as 0.

[0035] Step S2.2: If no record with the corresponding table unique identifier Table_id and the sharding completion flag set to 0 is found in the Tableshards table, update the "Completed" flag in the Tableinfos table to 1.

[0036] If the corresponding table unique identifier Table_id is read from the Tableshards table, and the Completed parameter indicating whether the sharding is complete is 0, the table unique identifier Table_id and shard sequence number Shard_id of the current shard are read from the Tableshards table;

[0037] Step S2.3: Update the record of the Tableshards table in the corresponding shard table unique identifier Table_id and shard sequence number Shard_id, and set the flag "Completed" to 1 to indicate whether the sharding is complete.

[0038] A Postgres table full data collection device, characterized by comprising a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the above-mentioned method steps when executing the computer program.

[0039] A readable storage medium is characterized in that: a computer program is stored on the readable storage medium, and when the computer program is executed by a processor, the method steps described above are implemented.

[0040] The beneficial effects of the present invention are as follows: the full data collection method for Postgres tables effectively solves the problem of uneven sharding caused by uneven data distribution in large tables, is applicable to tables without primary keys, and achieves consistent sharding collection based on database incremental logs, laying a good foundation for the accuracy of subsequent incremental data synchronization. At the same time, during the collection period, the table can still provide data modification services normally without affecting the business. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0042] Attachment Figure 1 This is a schematic diagram of the process of completing full data synchronization of all tables based on the synchronization auxiliary table in the present invention. DETAILED DESCRIPTION

[0043] In order to enable those skilled in the art to better understand the technical solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the embodiments described are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work should fall within the scope of protection of the present invention.

[0044] Data transfer services are a specialized business scenario for synchronizing table structures, full data, and incremental data to homogeneous or heterogeneous databases. This process requires consideration of data volume, impact on the source, data filtering, and incremental data. Simply exporting and importing data using database tools is not an option. The typical approach is to query the source table to obtain full data and use database logs to obtain incremental data.

[0045] Postgres database table synchronization includes table structure synchronization, full data synchronization, and incremental synchronization. Full synchronization is performed when the original table is first collected. When the source table has a small amount of data, synchronous collection is relatively simple, requiring only a single select query. When the table data volume is very large, using a single select query can easily cause the execution time to be too long and interrupted, making it impossible to continue execution, resulting in a waste of time and resources. The long lock time of a single table may affect business usage. Therefore, sharding large Postgres tables is an inevitable implementation method. Different sharding methods have their own considerations and focuses. How to achieve reasonable sharding is a technical difficulty.

[0046] Traditional large table sharding is mainly based on even sharding according to the step size of the interval, such as by primary key, time, serial number, etc. However, these methods have requirements for the source table, requiring specific types of fields or specific business logic data. They are not easy to implement and are prone to uneven sharding.

[0047] In addition, after the introduction of shard collection technology, since the shard query operations are not performed at the same time, there may be inconsistent query summary results.

[0048] The full data collection method for the Postgres table includes the following steps:

[0049] Step S1: Full sharding based on the Postgres table ctid;

[0050] The physical location of a row in a table is recorded using ctid, which is a tuple consisting of a block number and a row number within the block. For example, ctid(12,55) represents block number 12 and row number 55 within the block.

[0051] The ctid allows you to quickly locate rows. The ctid changes when data changes or the table shrinks, so it cannot uniquely identify a record for a long time. When collecting full data for data transmission, stop the database table from automatically shrinking and resume it after the collection is complete to avoid duplication of full data.

[0052] After shrinking a table, the physical location of the table is changed to eliminate holes in the data blocks. Data changes are captured in incremental logs to ensure that data is not lost.

[0053] The number of records in each file block is roughly consistent. Statistical analysis shows that a randomly selected file block yields approximately 100 rows. Because MVCC (Multi-Version Concurrency Control) is used, the ctid is incremented and potentially discontinuous. This means that the next ctid number may not correspond to a record, and the number of records in a data block may not be fixed. Because the number of records in a single block is so small and variable, using file blocks as the sharding unit is not appropriate.

[0054] Quickly locate a record in the table through ctid; obtain a relatively balanced sharding interval through data sampling tablesample. The sampling function uses the bernoulli function provided by the programmer PG.

[0055] For example, bernoulli(0.1) indicates sampling at 1 / 1000, and bernoulli(0.01) indicates sampling at 1 / 10,000. In this way, the number of row records in the shard generated by the collected ctid is approximately 1,000 or 10,000 rows.

[0056] Step S2: Use the database incremental log to ensure the eventual consistency of all data;

[0057] To achieve consistency in full-shard collection, set the programmer's PG parameter wal_level to logical to generate incremental logs. Set the identity full parameter of the collected table to record complete rows in the log, which is especially important for tables without primary keys. The database user performing the collection must have the replica permission to obtain incremental data.

[0058] Before full data collection, create an incremental collection slot to record the position of the current log sequence number LSN; the SQL example is:

[0059] postgres=#select pg_create_logical_replication_slot('test_slot','test_decoding');

[0060] slot_name|lsn

[0061] -----------+-----------

[0062] test_slot|0 / 163B360

[0063] (1 row)

[0064] After the full data collection is completed, obtain the incremental logs for this period and parse the logs to obtain the incremental changes during this period. The SQL example is:

[0065] postgres=#select pg_logical_slot_peek_changes('test_slot',null,null);

[0066] pg_logical_slot_peek_changes

[0067] ---------------------------------------------------------------------

[0068] (0 / 1678490,505,"BEGIN 505")

[0069] (0 / 1678490,505,"table public.test1:INSERT:uid[integer]:3uname[text]:'fa'")

[0070] (0 / 1678540,505,"COMMIT 505")

[0071] (3 rows)

[0072] To ensure eventual data consistency, when newly inserted data is collected during full data collection, insert data records are added to the incremental log and processed using one of the following methods:

[0073] (1) Ignore primary key conflicts caused by newly inserted data;

[0074] (2) Delete the incremental data first and then insert it to ensure the final consistency of the data;

[0075] (3) By caching data in the message queue MQ or locally, incremental data conflicts are eliminated before sending it to the storage end.

[0076] The source database (postgres or sqlite) and the collection program use synchronization auxiliary tables to record the conditions and execution status of each shard. The synchronization auxiliary tables include the Tableinfos table and the Tableshards table.

[0077] The Tableinfos table is used to record all tables that require full synchronization and whether the synchronization is complete. The record includes the table unique identifier Table_id, schema name Shema_name, table name Table_name, whether sharding is complete Shard_completed, whether full synchronization is complete Completed, and incremental point Checkpoint. If complete, the Shard_completed and Completed values are set to 1; if not, they are set to 0.

[0078] The Tableshards table is used to record the unique table identifiers of all fully synchronized tables, the execution conditions of all shards, and whether the synchronization is complete. The record includes the unique table identifiers (Table_id) of all fully synchronized tables, the shard sequence numbers (Shard_id), whether the sharding is completed (Completed), and the condition. If completed, the Completed flag is set to 1; if not, it is set to 0.

[0079] Table 1 Tableinfos (records all synchronized tables)

[0080]

[0081] Table 2 Tableshards (records table sharding information and execution status)

[0082]

[0083]

[0084] First, populate the Tableinfos table. The information of the synchronized tables comes from the synchronization task configuration file. Initially, the Completed flag for all tables is 0, indicating that full synchronization is not complete. The Shard_completed flag for all tables is 0, indicating that no sharding information has been generated.

[0085] Then, for all synchronized tables recorded in the Tableinfos table, use the aforementioned SQL statement (TablesampleBernoulli(0.01)) to sample each table, obtain ctid information, and cover all region generation conditions according to the open-close interval principle. The information is recorded in the Tableshard table. The initial mark in the table indicating whether sharding is complete is 0. After the above is completed, the shards in the Tableshard table are collected one by one.

[0086] Taking into account constraints such as collection and storage speed, when collecting, the collection task is executed serially for each shard, or in parallel for each shard;

[0087] Serial execution of each shard can reduce the CPU / network resources occupied by the server and the collection end program, but it takes a longer time;

[0088] Parallel execution of each shard consumes more CPU / network resources on the server and collection end, but the synchronization speed is fast.

[0089] During the implementation process, you can configure the number of parallel execution acquisition threads. If the parallel number is set to 1, the first strategy is used, otherwise the second strategy is used.

[0090] The process of synchronizing a shard is to generate a similar SQL statement (select * from tablename where ctid>='(1,28)' and ctid<'(98,96)') according to the conditions in the Tableshards table. It connects to the database through the JDBC (Java DataBase Connectivity standard) or ODBC (Open Database Connectivity) interface to obtain data and send it to the message queue MQ.

[0091] Before obtaining the first shard of the Tableshards table, the incremental log point is obtained and recorded in the Tableinfos table as the incremental log checkpoint. After a shard is synchronized, the sharding completion flag in the Tableshards table is set to 1. After all shards of a table are synchronized, the sharding completion flag in the Tableinfos table is updated to 1.

[0092] Based on the status data in the two synchronization auxiliary tables, they are updated in a timely manner during synchronization, and the shards of the table to be synchronized are obtained cyclically to complete the full data synchronization of all tables in the entire task.

[0093] In step S2, based on the status data in the two synchronization auxiliary tables, the full data synchronization of all tables in the entire task is completed. The implementation steps are as follows:

[0094] Step S2.1: Read the record marked "Completed" as 0 in the Tableinfos table;

[0095] If no record with the value "Completed" set to 0 is found in the Tableinfos table, the full synchronization is complete.

[0096] If a record marked "Completed" as 0 is read from the Tableinfos table, the corresponding table unique identifier "Table_id" in the Tableshards table is read, and the record marked "Completed" as 0 is marked as 0.

[0097] Step S2.2: If no record with the corresponding table unique identifier Table_id and the sharding completion flag set to 0 is found in the Tableshards table, update the "Completed" flag in the Tableinfos table to 1.

[0098] If the corresponding table unique identifier Table_id is read from the Tableshards table, and the Completed parameter indicating whether the sharding is complete is 0, the table unique identifier Table_id and shard sequence number Shard_id of the current shard are read from the Tableshards table;

[0099] Step S2.3: Update the record of the Tableshards table in the corresponding shard table unique identifier Table_id and shard sequence number Shard_id, and set the flag "Completed" to 1 to indicate whether the sharding is complete.

[0100] The Postgres table full data collection device includes a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the above-mentioned method steps when executing the computer program.

[0101] The readable storage medium stores a computer program, and when the computer program is executed by a processor, the method steps described above are implemented.

[0102] This method for collecting full data from Postgres tables does not pose a high load pressure risk to the original Postgres database, and the sharding is uniform, with good full sharding effect. It can implement concurrent data collection to improve collection efficiency and effectively ensure the eventual consistency of full data collection.

[0103] The embodiment described above is only one specific implementation of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for collecting full data from a Postgres table, characterized by: The following steps are involved: Step S1: Full sharding based on the Postgres table ctid; Capture data changes through incremental logs and quickly locate a record in the table using ctid. Obtain the sharding interval using data sampling (tablesample). The sampling function uses the bernoulli function provided by the programmer PG. Step S2: Use the database incremental log to ensure the eventual consistency of all data; Before full data collection, create an incremental collection slot to record the location of the current log sequence number (LSN). After the full data collection is completed, obtain the incremental log and analyze the log to obtain the incremental changes; The source database and the collection end program use synchronization auxiliary tables to record the conditions and execution status of each shard. The synchronization auxiliary tables include the Tableinfos table and the Tableshards table. The Tableinfos table is used to record all tables that require full synchronization and whether the synchronization is complete. The record includes the table unique identifier Table_id, schema name Shema_name, table name Table_name, whether sharding is complete (Shard_completed), whether full synchronization is complete (Completed), and incremental point Checkpoint. If complete, the Shard_completed and Completed values are set to 1; otherwise, they are set to 0. The Tableshards table is used to record the unique table identifiers of all fully synchronized tables, the execution conditions of all shards, and whether the synchronization is complete. The record includes the unique table identifiers of all fully synchronized tables (Table_id), the shard sequence number (Shard_id), whether the sharding is completed (Completed), and the condition. If completed, the Completed flag is set to 1; if not, it is set to 0. Based on the status data in the two synchronization auxiliary tables, they are updated in a timely manner during synchronization, and the shards of the table to be synchronized are obtained cyclically to complete the full data synchronization of all tables in the entire task.

2. The method for collecting full data from a Postgres table according to claim 1, wherein: In step S1, the physical location of the row in the table is recorded using ctid, which is represented by a tuple including a block number and a row number within the block.

3. The method for collecting full data from a Postgres table according to claim 1, wherein: In step S1, when the full amount of data transmission is collected, the automatic shrinking of the database table is stopped and then resumed after the collection is completed.

4. The method for collecting full data from a Postgres table according to claim 1, wherein: In step S2, the wal_level parameter of the programmer PG is set to logical to generate incremental logs; the identity full parameter of the collected table is set to record complete record rows in the log; the database user who performs the collection has the replica permission to obtain incremental data.

5. The method for collecting full data from a Postgres table according to claim 1, wherein: When newly inserted data is collected during full data collection, the inserted data record is added to the incremental log and processed using one of the following methods: (1) Ignore primary key conflicts caused by newly inserted data; (2) Delete the incremental data first and then insert it to ensure the final consistency of the data; (3) By caching data in the message queue MQ or locally, incremental data conflicts are eliminated before sending it to the storage end.

6. The method for collecting full data from a Postgres table according to claim 1, wherein: In step S2, the Tableinfos table is first populated. The information of the synchronized tables comes from the configuration file of the synchronization task. Initially, the "Completed" flag of all tables is 0, indicating that the full synchronization is not completed. Mark whether the sharding is completed. Shard_completed is always 0, indicating that no sharding information has been generated. Then, SQL statements are used to sample all synchronized tables recorded in the Tableinfos table, obtain ctid information, and cover all region generation conditions according to the open-close interval principle. The data is recorded in the Tableshard table. The table initially marks whether the sharding is completed as 0. The data is collected from each shard in the Tableshard table. When synchronizing a shard, SQL statements are generated according to the conditions in the Tableshards table to connect to the database through the JDBC or ODBC interface to obtain data and send it to the message queue MQ; Get the incremental log point before getting the first shard of the Tableshards table and record it in the incremental log checkpoint of the Tableinfos table. After synchronization of a shard is completed, the corresponding shard's mark in the Tableshards table is updated to 1. After synchronization of all shards of a table is completed, the corresponding mark in the Tableinfos table is updated to 1.

7. The method for collecting full data from a Postgres table according to claim 6, wherein: During collection, the collection task is executed serially for each shard, or in parallel for each shard.

8. The method for collecting full data from a Postgres table according to claim 6, wherein: In step S2, based on the status data in the two synchronization auxiliary tables, the full data synchronization of all tables in the entire task is completed. The implementation steps are as follows: Step S2.1: Read the records in the Tableinfos table marked as "Completed" with a value of 0. If no record with the value "Completed" set to 0 is found in the Tableinfos table, the full synchronization is complete. If a record marked "Completed" as 0 is read from the Tableinfos table, the corresponding table unique identifier "Table_id" in the Tableshards table is read, and the record marked "Completed" as 0 is marked as 0. Step S2.2: If no record with the corresponding table unique identifier Table_id and the sharding completion flag set to 0 is found in the Tableshards table, update the "Completed" flag in the Tableinfos table to 1. If the corresponding table unique identifier Table_id is read from the Tableshards table, and the Completed parameter indicating whether the sharding is complete is 0, the table unique identifier Table_id and shard sequence number Shard_id of the current shard are read from the Tableshards table; Step S2.3: Update the record of the Tableshards table in the corresponding shard table unique identifier Table_id and shard sequence number Shard_id, and set the flag "Completed" to 1 to indicate whether the sharding is complete.

9. A Postgres table full data collection device, characterized by: The method comprises a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the method steps according to any one of claims 1 to 8 when executing the computer program.

10. A readable storage medium, characterized in that: The readable storage medium stores a computer program, which, when executed by a processor, implements the method steps according to any one of claims 1 to 8.

Images