CN112948366A - Method for realizing non-conflict between query and recovery in PostgreSQL Standard mode - Google Patents
Method for realizing non-conflict between query and recovery in PostgreSQL Standard mode Download PDFInfo
- Publication number
- CN112948366A CN112948366A CN202110293086.1A CN202110293086A CN112948366A CN 112948366 A CN112948366 A CN 112948366A CN 202110293086 A CN202110293086 A CN 202110293086A CN 112948366 A CN112948366 A CN 112948366A
- Authority
- CN
- China
- Prior art keywords
- query
- undo
- data
- postgresql
- sql
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 90
- 238000011084 recovery Methods 0.000 title claims abstract description 34
- 238000004140 cleaning Methods 0.000 claims abstract description 23
- 230000001360 synchronised effect Effects 0.000 claims description 3
- 230000010076 replication Effects 0.000 claims description 2
- 238000012544 monitoring process Methods 0.000 description 3
- 238000011161 development Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 238000012545 processing Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000000926 separation method Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1458—Management of the backup or restore process
- G06F11/1469—Backup restoration techniques
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/219—Managing data history or versioning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Abstract
The invention discloses a method for realizing non-conflict between query and recovery in a PostgreSQL Standby mode, wherein an Undo Monitor process is operated in a PostgreSQL Standby database, when the conflict between the query SQL which is operated and a data block which needs to be recovered or cleaned is monitored, the Undo Worker process is awakened, and the context information of the query SQL and the row data in the data block which needs to be recovered or cleaned are copied into an Undo log; and the PostgreSQL standby database recovers or cleans the data blocks, and the query SQL queries the data in the row data copied by the Undo log, so that the query and recovery and the query and cleaning do not conflict with each other. The invention realizes the concurrent operation of inquiry and recovery and inquiry and cleaning based on Undo, and ensures the work of non-conflict between inquiry and recovery and inquiry and cleaning.
Description
Technical Field
The invention relates to the field of PostgreSQL databases, in particular to a method for realizing non-conflict query and recovery in a PostgreSQL Standby mode.
Background
With the rapid development of information technology, the coming of the DT era and the rapid development of mobile internet have led to a rapid increase in data processing volume of telecommunications operators, and data volume of communications enterprises has all been increasing explosively, and while the traffic volume is increasing, the access volume and data volume of databases have been increasing rapidly, and the requirements on database processing capacity and computing strength have been increasing correspondingly. The postgreSQL cluster is largely used by an OLTP type service system, and the conventional postgreSQL main database and standby database usually adopt self stream replication to carry out main and standby data synchronization and support asynchronization and synchronization. In the read-write separation mode, running queries on the standby database may cause conflicts with data blocks to be recovered, and further cause the queries to be cancelled, thereby affecting services. When the main library needs to clean the old version data, if the query running on the standby library still needs the old version data, the operation of cleaning the old version data by the main library is prevented, so that the table of the main library expands, and the execution efficiency of the SQL is influenced. Therefore, the conflict between query and block recovery in the STANDBY mode is solved, a more flexible and highly available scheme is further provided, and the response efficiency of the service is ensured.
Disclosure of Invention
The invention aims to solve the technical problem of providing a method for realizing non-conflict query and recovery in a PostgreSQL Standard mode.
The technical scheme adopted by the invention for solving the technical problems is to provide a method for realizing non-conflict between query and recovery in a PostgreSQL Standby mode, which comprises the following steps: s1: running the Undo Monitor process in a PostgreSQL standby database; s2: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be recovered; s3: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be cleaned; s4: if the conflict exists in the step S2 or S3, the Undo Monitor process wakes up the Undo Worker process; s5: starting the Undo Worker process, copying the context information of the query SQL and the row data in the data block needing to be recovered or cleaned into an Undo log; s6: and the PostgreSQL standby database recovers or cleans the data blocks, and the query SQL queries the data in the row data copied by the Undo log, so that the query and recovery and the query and cleaning do not conflict with each other.
Further, the step S2 specifically includes: s21: the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database; s22: in the SQL query operation process, the Undo Monitor process monitors a data block which is sent from the PostgreSQL main database to the PostgreSQL standby database and needs to be restored; s23: and comparing the query content of the query SQL with the data of the transmitted data block by the Undo Monitor process, and if the data block contains data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the transmitted data block which needs to be recovered.
Further, the step S3 specifically includes: s31: the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database; s32: in the SQL query operation process, when an old version data cleaning process AUTO VACUUM of a PostgreSQL main database cleans old version data, an Undo Monitor process monitors old version data cleaning operation of a PostgreSQL standby database synchronous main database; s33: the Undo Monitor process monitors the data blocks of the PostgreSQL standby database which need to be cleaned; s34: and comparing the query content of the query SQL with the data of the data block needing to be cleaned by the Undo Monitor process, and if the data block has the data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the data block needing to be cleaned.
Further, the step S5 further includes that the Undo Worker records Undo position information copied to Undo log line data, updates the header information of the data block to Undo position information of the line data, and modifies context information of the query SQL so that the query SQL reads the copied line data in the Undo log for query.
Further, when the line data in the data block is copied to the Undo log in step S5, the line data in the data block is written to the WAL log at the same time, so as to check the consistency of the Undo log.
Further, the method also comprises the step of discarding the data copied in the Undo log after the query SQL operation is finished.
Further, when the PostgreSQL standby database and the PostgreSQL main database stream are copied and disconnected, the Undo Monitor process and the Undo Worker process are in a dormant state.
Compared with the prior art, the invention has the following beneficial effects: the method for realizing the non-conflict of the query and the recovery in the PostgreSQL Standby mode provided by the invention has the advantages that based on Undo, when the conflict of the query and the data recovery or the cleaning is monitored through the Undo Monitor process, the Undo Worker process is awakened to copy the data to the Undo log, the concurrent operation of the query and the recovery and the query and the cleaning is realized, and the non-conflict work of the query and the recovery and the query and the cleaning is ensured; when the PostgreSQL standby database and the PostgreSQL main database stream are copied and disconnected, the Undo Monitor process and the Undo Worker process are in a dormant state, resources are not occupied, flexibility is improved, high availability is guaranteed, and the response efficiency of services is guaranteed.
Drawings
FIG. 1 is a flowchart of a method for implementing non-conflict between query and recovery in a PostgreSQL Standard mode according to an embodiment of the present invention;
FIG. 2 is a schematic diagram of a method for implementing non-conflict between query and recovery in a PostgreSQL Standard mode according to an embodiment of the present invention;
FIG. 3 is a schematic diagram of Undo Monitor process monitoring in the embodiment of the present invention.
Detailed Description
The invention is further described below with reference to the figures and examples.
Fig. 1 is a flowchart of a method for implementing non-conflict between query and recovery in the PostgreSQL standard mode in the embodiment of the present invention.
Referring to fig. 1, a method for implementing query and recovery in a PostgreSQL Standby mode without conflict in an embodiment of the present invention includes the following steps:
s1: running the Undo Monitor process in a PostgreSQL standby database;
s2: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be recovered;
s3: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be cleaned;
s4: if the conflict exists in the step S2 or S3, the Undo Monitor process wakes up the Undo Worker process;
s5: starting the Undo Worker process, copying the context information of the query SQL and the row data in the data block needing to be recovered or cleaned into an Undo log;
and the Undo Worker records are copied to Undo position information of Undo log line data, the head information of the data block is updated to be the position information of the line data in the Undo, and the Undo Worker modifies the context information of the query SQL so that the query SQL reads the copied line data in the Undo log for query. And when the line data in the data block is copied into the Undo log, simultaneously writing the line data in the data block into the WAL log for checking the consistency of the Undo log.
S6: and the PostgreSQL standby database recovers or cleans the data blocks, and the query SQL queries the data in the row data copied by the Undo log, so that the query and recovery and the query and cleaning do not conflict with each other. And discarding the data of the row copied in the Undo log after the query SQL is operated.
The Undo Monitor process mainly functions to Monitor SQL running on PostgreSQL standby database and blocks needing to be restored, so as to detect SQL and data blocks with conflicts. If there is no conflict in response, there is no need to wake the UNDO WORKER process, and no conflict will occur by performing SQL and block recovery.
Referring to fig. 2 and fig. 3, in the method for implementing query and recovery without conflict in the PostgreSQL standard mode according to the embodiment of the present invention, monitoring query SQL and data recovery:
the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database;
in the SQL query operation process, the Undo Monitor process monitors a data block which is sent from the PostgreSQL main database to the PostgreSQL standby database and needs to be restored;
and comparing the query content of the query SQL with the data of the transmitted data block by the Undo Monitor process, and if the data block contains data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the transmitted data block which needs to be recovered.
When the old version data cleaning process AUTO VACUUM of the PostgreSQL primary database cleans the old version data, the query SQL of the PostgreSQL backup database may also need the old version data, and the old version data cleaning operation of the PostgreSQL backup database synchronization primary database may cause the query SQL to conflict with the data cleaning.
Monitoring query SQL and data cleaning:
the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database;
in the SQL query operation process, when an old version data cleaning process AUTO VACUUM of a PostgreSQL main database cleans old version data, an Undo Monitor process monitors old version data cleaning operation of a PostgreSQL standby database synchronous main database;
the Undo Monitor process monitors the data blocks of the PostgreSQL standby database which need to be cleaned;
and comparing the query content of the query SQL with the data of the data block needing to be cleaned by the Undo Monitor process, and if the data block has the data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the data block needing to be cleaned.
When the PostgreSQL standby database and the PostgreSQL main database stream are copied and disconnected, or the PostgreSQL standby database is only used as a disaster tolerance library, the Undo Monitor process and the Undo Worker process are in a dormant state, resources are not occupied, the method is more flexible, and high availability is guaranteed.
In summary, in the method for implementing non-conflict between query and recovery in the PostgreSQL Standby mode according to the embodiment of the present invention, based on Undo, when a conflict occurs between query and data recovery or cleaning is monitored by the Undo Monitor process, the Undo Worker process is woken up to copy data to the Undo log, so as to implement concurrent execution of query and recovery and query and cleaning, and ensure that the query and recovery and query and cleaning do not conflict with each other; when the PostgreSQL standby database and the PostgreSQL main database stream are copied and disconnected, the Undo Monitor process and the Undo Worker process are in a dormant state, resources are not occupied, flexibility is improved, high availability is guaranteed, and the response efficiency of services is guaranteed.
Although the present invention has been described with respect to the preferred embodiments, it will be understood by those skilled in the art that various changes in form and details may be made therein without departing from the spirit and scope of the invention as defined by the appended claims.
Claims (7)
1. A method for realizing non-conflict query and recovery in a PostgreSQL Standard mode is characterized by comprising the following steps:
s1: running the Undo Monitor process in a PostgreSQL standby database;
s2: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be recovered;
s3: the Undo Monitor process monitors whether the running query SQL conflicts with the data block needing to be cleaned;
s4: if the conflict exists in the step S2 or S3, the Undo Monitor process wakes up the Undo Worker process;
s5: starting the Undo Worker process, copying the context information of the query SQL and the row data in the data block needing to be recovered or cleaned into an Undo log;
s6: and the PostgreSQL standby database recovers or cleans the data blocks, and the query SQL queries the data in the row data copied by the Undo log, so that the query and recovery and the query and cleaning do not conflict with each other.
2. The method for implementing query and recovery without conflict in the PostgreSQL Standby mode of claim 1, wherein the step S2 specifically includes:
s21: the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database;
s22: in the SQL query operation process, the Undo Monitor process monitors a data block which is sent from the PostgreSQL main database to the PostgreSQL standby database and needs to be restored;
s23: and comparing the query content of the query SQL with the data of the transmitted data block by the Undo Monitor process, and if the data block contains data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the transmitted data block which needs to be recovered.
3. The method for implementing query and recovery without conflict in the PostgreSQL Standby mode of claim 1, wherein the step S3 specifically includes:
s31: the Undo Monitor process monitors the running query SQL in the PostgreSQL standby database;
s32: in the SQL query operation process, when an old version data cleaning process AUTO VACUUM of a PostgreSQL main database cleans old version data, an Undo Monitor process monitors old version data cleaning operation of a PostgreSQL standby database synchronous main database;
s33: the Undo Monitor process monitors the data blocks of the PostgreSQL standby database which need to be cleaned;
s34: and comparing the query content of the query SQL with the data of the data block needing to be cleaned by the Undo Monitor process, and if the data block has the data which needs to be queried by the query SQL, indicating that the running query SQL conflicts with the data block needing to be cleaned.
4. The method for implementing query and recovery in PostgreSQL Standard mode without conflict, as set forth in claim 1, wherein the step S5 further includes copying the Undo Worker record to Undo position information of Undo log line data, updating the header information of the data block to be the Undo position information of the line data, and modifying the context information of query SQL so that SQL query reads the line data copied in Undo log for query.
5. The method for implementing query and recovery without conflict in PostgreSQL Standby mode according to claim 1, wherein the data in the data block is written into the WAL log at the same time when the data in the data block is copied into the Undo log in step S5, so as to check consistency of the Undo log.
6. The method for implementing query and recovery in PostgreSQL Standby mode according to claim 1, further comprising discarding the row data replicated in the Undo log after the query SQL runs.
7. The method of claim 1, wherein the Undo Monitor process and Undo Worker process are in a dormant state when the PostgreSQL Standby database is disconnected from the PostgreSQL primary database stream replication.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110293086.1A CN112948366B (en) | 2021-03-18 | 2021-03-18 | Method for realizing no conflict between inquiry and recovery under PostgreSQL Standby mode |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110293086.1A CN112948366B (en) | 2021-03-18 | 2021-03-18 | Method for realizing no conflict between inquiry and recovery under PostgreSQL Standby mode |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112948366A true CN112948366A (en) | 2021-06-11 |
| CN112948366B CN112948366B (en) | 2023-10-27 |
Family
ID=76227001
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110293086.1A Active CN112948366B (en) | 2021-03-18 | 2021-03-18 | Method for realizing no conflict between inquiry and recovery under PostgreSQL Standby mode |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112948366B (en) |
Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6618822B1 (en) * | 2000-01-03 | 2003-09-09 | Oracle International Corporation | Method and mechanism for relational access of recovery logs in a database system |
| KR20100134355A (en) * | 2009-06-15 | 2010-12-23 | 대성홀딩스 주식회사 | A method for recovering logical data errors in database |
| CN103729442A (en) * | 2013-12-30 | 2014-04-16 | 华为技术有限公司 | Method for recording event logs and database engine |
| CN105930500A (en) * | 2016-05-06 | 2016-09-07 | 华为技术有限公司 | Transaction recovery method in database system, and database management system |
| CN106815097A (en) * | 2017-01-18 | 2017-06-09 | 北京许继电气有限公司 | Database disaster tolerance system and method |
| CN109299099A (en) * | 2018-10-11 | 2019-02-01 | 瀚高基础软件股份有限公司 | A kind of the data flashback query and restoration methods, device of PostgreSQL database |
| CN111858501A (en) * | 2020-06-02 | 2020-10-30 | 武汉达梦数据库有限公司 | Log reading method and data synchronization system based on log analysis synchronization |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
-
2021
- 2021-03-18 CN CN202110293086.1A patent/CN112948366B/en active Active
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6618822B1 (en) * | 2000-01-03 | 2003-09-09 | Oracle International Corporation | Method and mechanism for relational access of recovery logs in a database system |
| KR20100134355A (en) * | 2009-06-15 | 2010-12-23 | 대성홀딩스 주식회사 | A method for recovering logical data errors in database |
| CN103729442A (en) * | 2013-12-30 | 2014-04-16 | 华为技术有限公司 | Method for recording event logs and database engine |
| CN105930500A (en) * | 2016-05-06 | 2016-09-07 | 华为技术有限公司 | Transaction recovery method in database system, and database management system |
| CN106815097A (en) * | 2017-01-18 | 2017-06-09 | 北京许继电气有限公司 | Database disaster tolerance system and method |
| CN109299099A (en) * | 2018-10-11 | 2019-02-01 | 瀚高基础软件股份有限公司 | A kind of the data flashback query and restoration methods, device of PostgreSQL database |
| CN111858501A (en) * | 2020-06-02 | 2020-10-30 | 武汉达梦数据库有限公司 | Log reading method and data synchronization system based on log analysis synchronization |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
Non-Patent Citations (2)
| Title |
|---|
| SHARIQ ALI KHAN ET AL.: "Critical role of a Database Administrator: Designing recovery solutions to combat database failures", 《ICAICT 2014》, pages 1 - 7 * |
| 陈敬炜: "嵌入式数据库恢复算法研究", 《中国优秀硕士学位论文全文数据库 信息科技辑》, pages 138 - 126 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112948366B (en) | 2023-10-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| JP7271670B2 (en) | Data replication method, device, computer equipment and computer program | |
| CN101334797B (en) | Distributed file systems and its data block consistency managing method | |
| CN109992628B (en) | Data synchronization method, device, server and computer readable storage medium | |
| CN107291787B (en) | Main and standby database switching method and device | |
| US7428657B2 (en) | Method for rolling back from snapshot with log | |
| US7953710B2 (en) | Multi-master database synchronization without loss of convergence | |
| US7882286B1 (en) | Synchronizing volumes for replication | |
| CN110990432B (en) | Device and method for synchronizing distributed cache clusters across machine room | |
| US20100274768A1 (en) | De-duplication and completeness in multi-log based replication | |
| CN111078667B (en) | Data migration method and related device | |
| US7457830B1 (en) | Method and system of replicating data using a recovery data change log | |
| CN1653427A (en) | Method and apparatus for creating a virtual data copy | |
| CN105739924A (en) | Cache cluster-based cache method and system | |
| JP2004334574A (en) | Operation managing program and method of storage, and managing computer | |
| CN108153804B (en) | Metadata log updating method for symmetric distributed file system | |
| KR20060117505A (en) | A recovery method using extendible hashing based cluster log in a shared-nothing spatial database cluster | |
| JP2007241486A (en) | Memory system | |
| CN107423390B (en) | Real-time data synchronization method based on OLTP-OLAP mixed relational database system | |
| CN116348865A (en) | Highly available, high performance, persistent memory optimized extended database | |
| CN116348863A (en) | System and method for extending transaction continuity across faults in a database | |
| CN116680256A (en) | Database node upgrading method and device and computer equipment | |
| CN108038163B (en) | Master and backup control center database synchronization system | |
| CN111352766A (en) | Database double-activity implementation method and device | |
| CN112948366B (en) | Method for realizing no conflict between inquiry and recovery under PostgreSQL Standby mode | |
| CN114564458B (en) | Method, device, equipment and storage medium for synchronizing data among clusters |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |