CN109299099A - A kind of the data flashback query and restoration methods, device of PostgreSQL database - Google Patents
A kind of the data flashback query and restoration methods, device of PostgreSQL database Download PDFInfo
- Publication number
- CN109299099A CN109299099A CN201811181663.2A CN201811181663A CN109299099A CN 109299099 A CN109299099 A CN 109299099A CN 201811181663 A CN201811181663 A CN 201811181663A CN 109299099 A CN109299099 A CN 109299099A
- Authority
- CN
- China
- Prior art keywords
- row data
- data
- query
- time
- old
- 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
Classifications
-
- 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
Abstract
The invention discloses a kind of data flashback query of PostgreSQL database and restoration methods, device, wherein data flashback query method includes: default cleaning control parameter, database table is modified in user based on MVCC mechanism or when delete operation, the old row data for retaining change according to cleaning control parameter or deleting；Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains the specified query time of flashback query；It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility judge, return to qualified flashback query data.Data reconstruction method is based on data flashback query method, using the data of return as recovery data.
Description
Technical field
The disclosure belongs to the technical field of PostgreSQL database, and the data for being related to a kind of PostgreSQL database are dodged
Return inquiry and restoration methods, device.
Background technique
Only there is provided background technical informations relevant to the disclosure for the statement of this part, it is not necessary to so constitute first skill
Art.
PostgreSQL database is state-of-the-art PostgreSQL database in the world, its development was rapider in recent years, was got over
Various industries and field are applied to come more.In the production scene that database uses, it will usually encounter loss of data, mistake
The problems such as operation, the common solution of PostgreSQL database are standby using the mathematical logic backup of well in advance or physics
Part carries out data recovery, and the strategy that the degree restored depends on backup and restores.But this method cost is high, data backup
Can not only occupy additional data space, data backup or during restoring also than relatively time-consuming, cause database performance to decline,
And it may must also suspend database service in some cases and could complete recovery operation.
When using PostgreSQL database, update has been carried out by accident there are user or has deleted the maloperation of data
Situation, if but carrying out data recovery with above-mentioned data reconstruction method seems some " wasting one's talent on a petty job ".
Flashback query technology is one of database recovery technology important mechanisms, when logic error occurs for database,
It being capable of quick and least cost recovery.However, flashback query technology can only be realized in oracle database, at present
PostgreSQL database flashes back mechanism not yet, the flashback query technology in oracle database can not directly divert to
PostgreSQL database, since the two database mechanisms are different, there are technical barriers.Therefore, user is using
When PostgreSQL database has carried out updating or deleting the maloperation of data by accident, still without effective data recovery side
Method.
Summary of the invention
For the deficiencies in the prior art, solves user and update or delete using PostgreSQL database
Except data maloperation when there is no the problem of effective data reconstruction method, one or more other embodiments of the present disclosure provide one
The data flashback query and restoration methods, device of kind PostgreSQL database, are realized under the premise of without data backup,
The fast quick-recovery to maloperation data is completed when database itself starts.
According to the one aspect of one or more other embodiments of the present disclosure, a kind of data of PostgreSQL database are provided
Flashback query method.
A kind of data flashback query method of PostgreSQL database, this method comprises:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, root
The old row data for retaining change according to cleaning control parameter or deleting；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flashback query
Specified query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flashback query data.
Further, the cleaning control parameter includes that old row data retention over time parameter and old row data query guarantee ginseng
Number.
Database table is old when the old row data retention over time is PostgreSQL database progress data flashback query
The maximum duration that row data save；
The old row data query guarantees to be PostgreSQL database whether guarantee when data flashback query described
Old row data can be arrived by flashback query.
Further, the change of the reservation or the old row data of deletion include the old row for executing automatic Clean Up Database table
The old row data of the change or deletion that retain when data, specific method step include:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, it is no
Then, flash back function is closed；
The old row data of database table are cleared up automatically, the old row data for treating Clean Up Database table carry out line by line time
It goes through, submission time is obtained according to the things number of row data, it is every according to whether submission time judges in old row data retention over time
Whether the row data of a tables of data can be cleaned；
Submission time retains the row data in old row data retention over time, otherwise clears up the row data automatically.
Further, the change of the reservation or the old row data of deletion include the old row for executing manual Clean Up Database table
The old row data of the change or deletion that retain when data, specific method step include:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, it is no
Then, flash back function is closed；
The old row data of database table are cleared up manually, are guaranteed according to the old row data query in cleaning control parameter
Value judges whether that manual cleaning operation can be carried out, if so, carrying out manual cleaning operation, otherwise enters in next step；
The old row data for treating Clean Up Database table are traversed line by line, when obtaining submission according to the things number of row data
Between, whether in old row data retention over time judge whether the row data of each tables of data can be cleaned according to submission time；
Submission time retains the row data, otherwise clears up the row data in old row data retention over time.
Further, this method further includes carrying out flash back version querying:
Flash back version querying sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, is flashed back
The specified query time of version querying；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flash back version querying data.
Further, it is described according to predefined syntax parsing rule carry out syntax parsing obtain transaction number or inquiry when
Between, when obtaining transaction number, query time is obtained according to transaction number；The query time packet obtained according to flashback query sentence
The time value for including given query in flashback query includes flash back version according to the query time that flash back version querying sentence obtains
In this inquiry specify at the beginning of and the end time.
Further, in the method, after obtaining the specified query time of flashback query, judge the query time whether
In old row data retention over time, if so, entering in next step, flashback query can not be carried out by otherwise reporting an error；
Ergodic data library table object obtains the row data of database table, and affairs when being inserted into newline are obtained from row data
Number, modification row data when transaction number and obtain the corresponding affairs submission time of different transaction numbers；
The transaction number that judgement returns to when qualified old row data are insertion newlines has been filed on, and thing when insertion newline
Business number corresponding affairs submission time is not more than the time value of given query in flashback query, and affairs when modification row data
The corresponding affairs submission time of transaction number when number having been filed on, and modifying row data is greater than the time of given query in flashback query
Value；
Qualified old row data are returned as flashback query data.
Further, in the method, judgement at the beginning of specifying in flash back version querying and after the end time is obtained
It flashes back at the beginning of specifying in version querying and whether the end time is in old row data retention over time, if so, under
One step, flashback query can not be carried out by otherwise reporting an error；
Ergodic data library table object obtains the row data of database table, and affairs when being inserted into newline are obtained from row data
Number, modification row data when transaction number and obtain the corresponding affairs submission time of different transaction numbers；
For the inquiry in the period in flash back version querying, judge that returning to qualified old row data is that insertion is new
Transaction number when row has been filed on, and the corresponding affairs submission time of transaction number when insertion newline is greater than flash back version querying middle finger
Transaction number at the beginning of fixed and no more than the end time specified in flash back version querying, and when modification row data is equal to
When transaction number when being inserted into transaction number when newline, and modifying row data is not row lock；
For existing newline data before time started period in flash back version querying, judge to return eligible
Newline data be inserted into newline when transaction number have been filed on, and be inserted into newline when the corresponding affairs submission time of transaction number not
Transaction number greater than the time value of given query in flashback query, and when modification row data has been filed on, and when modification row data
The corresponding affairs submission time of transaction number be greater than flashback query in given query time value；
Qualified old row data or newline data are returned as flash back version querying data.
According to the other side of one or more other embodiments of the present disclosure, a kind of computer-readable storage medium is also provided
Matter.
A kind of computer readable storage medium, wherein being stored with a plurality of instruction, described instruction is suitable for by terminal device equipment
Processor load and execute following processing:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, root
The old row data for retaining change according to cleaning control parameter or deleting；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flashback query
Specified query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flashback query data.
According to the other side of one or more other embodiments of the present disclosure, a kind of terminal device is also provided.
A kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor
For realizing each instruction；Computer readable storage medium is suitable for by processor load simultaneously for storing a plurality of instruction, described instruction
Execute following processing:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, root
The old row data for retaining change according to cleaning control parameter or deleting；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flashback query
Specified query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flashback query data.
According to the one aspect of one or more other embodiments of the present disclosure, a kind of data of PostgreSQL database are provided
Restoration methods.
A kind of data reconstruction method of PostgreSQL database, a kind of number of this method based on PostgreSQL database
According to flashback query method, using the data of return as recovery data.
According to the other side of one or more other embodiments of the present disclosure, a kind of computer-readable storage medium is also provided
Matter.
A kind of computer readable storage medium, wherein being stored with a plurality of instruction, described instruction is suitable for by terminal device equipment
Processor load and execute a kind of data reconstruction method of PostgreSQL database.
According to the other side of one or more other embodiments of the present disclosure, a kind of terminal device is also provided.
A kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor
For realizing each instruction；Computer readable storage medium is suitable for by processor load simultaneously for storing a plurality of instruction, described instruction
Execute a kind of data reconstruction method of PostgreSQL database.
The disclosure the utility model has the advantages that
A kind of the data flashback query and restoration methods, device of PostgreSQL database described in the disclosure, relative to
PostgreSQL carries out recovery data using conventional backup logic or physical backup, and flashback query technology only needs to pass through SQL
Sentence carries out recovery database, easy to operate, convenient and efficient.And do not need to carry out data backup, save memory space.Most
It is important that when conventional method is restored using Backup Data, it may be necessary to restore to entire library, and need to stop
Fall database service operation, and flashback query only needs the row data to mistake to restore, and start in database itself
When can complete, therefore Resuming agent is lower, more efficient.
Detailed description of the invention
The accompanying drawings constituting a part of this application is used to provide further understanding of the present application, and the application's shows
Meaning property embodiment and its explanation are not constituted an undue limitation on the present application for explaining the application.
Fig. 1 is whole according to a kind of data flashback query method of PostgreSQL database of one or more embodiments
Flow chart；
Fig. 2 is according to one or more embodiments based on MVCC mechanism flashback query architecture diagram；
Fig. 3 is the old row data flowchart in the old row data retention over time of reservation according to one or more embodiments；
Fig. 4 old row data visibility judge flow chart when being the flashback query according to one or more embodiments.
Specific embodiment:
Below in conjunction with the attached drawing in one or more other embodiments of the present disclosure, to one or more other embodiments of the present disclosure
In technical solution be clearly and completely described, it is clear that described embodiments are only a part of the embodiments of the present invention,
Instead of all the embodiments.Based on one or more other embodiments of the present disclosure, those of ordinary skill in the art are not being made
Every other embodiment obtained, shall fall within the protection scope of the present invention under the premise of creative work.
It is noted that described further below be all exemplary, it is intended to provide further instruction to the application.Unless another
It indicates, all technical and scientific terms that the present embodiment uses have and the application person of an ordinary skill in the technical field
Normally understood identical meanings.
It should be noted that term used herein above is merely to describe specific embodiment, and be not intended to restricted root
According to the illustrative embodiments of the application.As used herein, unless the context clearly indicates otherwise, otherwise singular
Also it is intended to include plural form, additionally, it should be understood that, when in the present specification using term "comprising" and/or " packet
Include " when, indicate existing characteristics, step, operation, device, component and/or their combination.
It should be noted that flowcharts and block diagrams in the drawings show according to various embodiments of the present disclosure method and
The architecture, function and operation in the cards of system.It should be noted that each box in flowchart or block diagram can represent
A part of one module, program segment or code, a part of the module, program segment or code may include one or more
A executable instruction for realizing the logic function of defined in each embodiment.It should also be noted that some alternately
Realization in, function marked in the box can also occur according to the sequence that is marked in attached drawing is different from.For example, two connect
The box even indicated can actually be basically executed in parallel or they can also be executed in a reverse order sometimes,
This depends on related function.It should also be noted that each box and flow chart in flowchart and or block diagram
And/or the combination of the box in block diagram, the dedicated hardware based system that functions or operations as defined in executing can be used are come
It realizes, or the combination of specialized hardware and computer instruction can be used to realize.
In the absence of conflict, the feature in the embodiment and embodiment in the disclosure can be combined with each other, and tie below
It closes attached drawing and embodiment is described further the disclosure.
Fig. 1 is a kind of data flashback query method overall flow of PostgreSQL database of one or more embodiments
Figure, as shown in Figure 1,
A kind of data flashback query method of PostgreSQL database, this method comprises:
Step (1): default cleaning control parameter is modified to database table or deletes behaviour in user based on MVCC mechanism
When making, the old row data of change or deletion are retained according to cleaning control parameter；
Step (2): flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, is obtained
The specified query time of flashback query；
Step (3): inquiring in database table according to query time, and the old row data of the change or deletion to reservation carry out
Visibility judge returns to qualified flashback query data.
Realize that flashback query needs to rely on its MVCC mechanism in PostgreSQL, i.e., when being altered to data line,
Old row data are not deleted, but mark upper " deleting label ", and increase data line newly on this basis as the number after change
According to；When equally deleting data line, nor actually deleting the data, but " deleting label " is put on.
As shown in Fig. 2, being the framework map based on MVCC mechanism flashback query, which is divided into two parts, wherein 1. referring to
Retain the old row data of table object by control parameter, even if the data are not yet in Autovacuum or manual Vacuum
It can be cleaned out.2. refer to by flashback query or flash back version querying table object is inquired when, specified time can be returned
Old row data.
Further, the cleaning control parameter includes old row data retention over time parameter hg_fbq_retention and old
Row data query guarantees parameter hg_fbq_guarantee,
The old row data retention over time parameter hg_fbq_retention is that PostgreSQL database carries out data sudden strain of a muscle
The maximum duration that the old row data of database table save when returning inquiry；
The old row data query guarantees that parameter hg_fbq_guarantee is that PostgreSQL database carries out data sudden strain of a muscle
Whether guarantee that the old row data can be arrived by flashback query when returning inquiry.
In the step of disclosure one or more embodiment (1), the change of the reservation or the old row data of deletion include
The old row data of the change or deletion that retain when executing the old row data of automatic Clean Up Database table, specific method step include:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, it is no
Then, flash back function is closed；
The old row data of database table are cleared up automatically, the old row data for treating Clean Up Database table carry out line by line time
It goes through, submission time is obtained according to the things number of row data, it is every according to whether submission time judges in old row data retention over time
Whether the row data of a tables of data can be cleaned；
Submission time retains the row data in old row data retention over time, otherwise clears up the row data automatically.
In the step of disclosure one or more embodiment (1), the change of the reservation or the old row data of deletion include
The old row data of the change or deletion that retain when executing the old row data of manual Clean Up Database table, specific method step include:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, it is no
Then, flash back function is closed；
The old row data of database table are cleared up manually, are guaranteed according to the old row data query in cleaning control parameter
Value judges whether that manual cleaning operation can be carried out, if so, carrying out manual cleaning operation, otherwise enters in next step；
The old row data for treating Clean Up Database table are traversed line by line, when obtaining submission according to the things number of row data
Between, whether in old row data retention over time judge whether the row data of each tables of data can be cleaned according to submission time；
Submission time retains the row data, otherwise clears up the row data in old row data retention over time.
As shown in figure 3, retaining the specific steps of the old row data in hg_fbq_retention are as follows:
1. judging whether the value of parameter hg_fbq_retention setting is greater than 0, flashback query function is opened greater than 0；
Otherwise flashback query function is closed.
2. after opening flashback query, starting to carry out vacuum cleaning operation.
3.vacuum cleaning table handling is divided into two parts: cleaning and automatic cleaning manually.It needs when cleaning according to hg_ manually
Fbq_guarantee carries out judging whether to carry out, if parameter value is set as on, not can be carried out manual cleaning operation；
If it is off, then cleaning operation can be carried out.
4. next step starts traverse the table object of vacuum line by line, to judge whether each tuple can be with
It is cleaned.
5. the processing for each tuple obtains the submission time of affairs first according to its transaction number xid.
6. then judging submission time whether within the hg_fbq_retention time of setting.
7. if the tuple can not be cleaned within current time；Otherwise it can be cleaned out.
Term in Fig. 3 is explained:
1. hg_fbq_retention: old row data retention over time, when carrying out the longest that the old row data of flashback query save
Between；
2. hg_fbq_guarantee: old row data query guarantees, after opening flashback query, if guarantees hg_fbq_
Data in the setting of retention parameter temporal can be arrived with flashback query.It is defaulted as off, i.e., does not allow autovacuum to clear up
Data in the parameter temporal scope, but manual vacuum operation is not limited；
3. vacuum: the old row data of Clean Up Database table；
4. autovacuum: the old row data of automatic Clean Up Database table；
5. tuple: the row data of database table；
6. xid: transaction number, each tuple have corresponding xid transaction number；
Further, this method specifically includes flashback query and flash back version querying, wherein flash back version querying method
Are as follows:
Step (1): default cleaning control parameter is modified to database table or deletes behaviour in user based on MVCC mechanism
When making, the old row data of change or deletion are retained according to cleaning control parameter；
Step (2): receiving flash back version querying sentence, and carry out syntax parsing according to predefined syntax parsing rule,
Obtain the specified query time of flash back version querying；
Step (3): inquiring in database table according to query time, and the old row data of the change or deletion to reservation carry out
Visibility judge returns to qualified flash back version querying data.
It is described that language is carried out according to predefined syntax parsing rule in the step of disclosure one or more embodiment (2)
Method parsing obtains transaction number or query time, when obtaining transaction number, obtains query time according to transaction number；According to flashback query
The query time that sentence obtains includes the time value of given query in flashback query, is obtained according to flash back version querying sentence
The query time include flash back version querying in specify at the beginning of and the end time.
In the step of disclosure one or more embodiment (3), after obtaining the specified query time of flashback query, judgement should
Whether query time is in old row data retention over time, if so, entering in next step, flashback query can not be carried out by otherwise reporting an error；
Ergodic data library table object obtains the row data of database table, and affairs when being inserted into newline are obtained from row data
Number, modification row data when transaction number and obtain the corresponding affairs submission time of different transaction numbers；
The transaction number that judgement returns to when qualified old row data are insertion newlines has been filed on, and thing when insertion newline
Business number corresponding affairs submission time is not more than the time value of given query in flashback query, and affairs when modification row data
The corresponding affairs submission time of transaction number when number having been filed on, and modifying row data is greater than the time of given query in flashback query
Value；
Qualified old row data are returned as flashback query data.
In the step of disclosure one or more embodiment (3), obtain flash back version querying in specify at the beginning of and
After end time, judges at the beginning of being specified in flash back version querying and whether the end time is in old row data retention over time
Interior, if so, entering in next step, flashback query can not be carried out by otherwise reporting an error；
Ergodic data library table object obtains the row data of database table, and affairs when being inserted into newline are obtained from row data
Number, modification row data when transaction number and obtain the corresponding affairs submission time of different transaction numbers；
For the inquiry in the period in flash back version querying, judge that returning to qualified old row data is that insertion is new
Transaction number when row has been filed on, and the corresponding affairs submission time of transaction number when insertion newline is greater than flash back version querying middle finger
Transaction number at the beginning of fixed and no more than the end time specified in flash back version querying, and when modification row data is equal to
When transaction number when being inserted into transaction number when newline, and modifying row data is not row lock；
For existing newline data before time started period in flash back version querying, judge to return eligible
Newline data be inserted into newline when transaction number have been filed on, and be inserted into newline when the corresponding affairs submission time of transaction number not
Transaction number greater than the time value of given query in flashback query, and when modification row data has been filed on, and when modification row data
The corresponding affairs submission time of transaction number be greater than flashback query in given query time value；
Qualified old row data or newline data are returned as flash back version querying data.
As shown in figure 4, old row data visibility judge (flashback query, flash back version querying) when flashback query, specific to walk
Suddenly are as follows:
1. obtaining flashback query sentence (including flash back version querying)
2. carrying out syntax parsing according to query statement
3. flashback query and flash back version querying querying condition can be transaction number xid or query time sql_ts, refer to
When determining transaction number inquiry, need to obtain affairs submission time according to transaction number xid.
4. whether the specified query time of judgement or the time obtained according to xid are within hg_fbq_retention
5. then continuing the logic that flashes back within the hg_fbq_retention time；Otherwise reporting an error not can be carried out flashback query
6. obtaining submission time ts respectively according to the transaction number xmin and xmax of the tuple of the inquiry table object of traversal
7. being directed to flashback query logic.
8. judgement, which returns to qualified old row data, is that xmin has been filed on, and submission time xminTs≤fbtime,
Xmax is also submitted, xmaxTs > fbtime.
9. for flash back version querying.Flash back version querying is divided into two parts content: inquiry and beginning in the period
It is inquired before time.
10. the inquiry in the period, qualified old row data are exactly: fbstartTs≤xminTs < fbendTs.
When needing to consider xmin=xmax at this time, when what xmax was indicated is not row lock, inquired in the qualified period when showing
Old row data.
11. flash back version querying existing newline data before further including time started period, the inquiry and flash back are looked into
It askes consistent.
12. returning to qualified inquiry data.
Term in Fig. 4 is explained:
1. sql_ts: the specified query time value of flashback query or the time according to specified xid acquisition,
Including 2. fbtime, fbstartTs, fbendTs in logic below.
3. xmin/xmax:xmin refers to transaction number when insertion newline；Xmax refers to transaction number when modification row data.
4. ts: affairs submission time is obtained according to transaction number, below include xminTs and xmaxTs in logic.
5. fbtime: in flashback query, the time value of given query.
6. fbstartTs/fbendTs: flash back version querying in, specify at the beginning of and the end time.
7. XminTs/xmaxTs: according to the submission time of the xmin and xmax affairs obtained.
According to the other side of one or more other embodiments of the present disclosure, a kind of computer-readable storage medium is also provided
Matter.
A kind of computer readable storage medium, wherein being stored with a plurality of instruction, described instruction is suitable for by terminal device equipment
Processor load and execute following processing:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, root
The old row data for retaining change according to cleaning control parameter or deleting；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flashback query
Specified query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flashback query data.
According to the other side of one or more other embodiments of the present disclosure, a kind of terminal device is also provided.
A kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor
For realizing each instruction；Computer readable storage medium is suitable for by processor load simultaneously for storing a plurality of instruction, described instruction
Execute following processing:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, root
The old row data for retaining change according to cleaning control parameter or deleting；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flashback query
Specified query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility and sentence
It is disconnected, return to qualified flashback query data.
According to the one aspect of one or more other embodiments of the present disclosure, a kind of data of PostgreSQL database are provided
Restoration methods.
A kind of data reconstruction method of PostgreSQL database, a kind of number of this method based on PostgreSQL database
According to flashback query method, using the data of return as recovery data.
According to the other side of one or more other embodiments of the present disclosure, a kind of computer-readable storage medium is also provided
Matter.
A kind of computer readable storage medium, wherein being stored with a plurality of instruction, described instruction is suitable for by terminal device equipment
Processor load and execute a kind of data reconstruction method of PostgreSQL database.
According to the other side of one or more other embodiments of the present disclosure, a kind of terminal device is also provided.
A kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor
For realizing each instruction；Computer readable storage medium is suitable for by processor load simultaneously for storing a plurality of instruction, described instruction
Execute a kind of data reconstruction method of PostgreSQL database.
These computer executable instructions execute the equipment according to each reality in the disclosure
Apply method or process described in example.
In the present embodiment, computer program product may include computer readable storage medium, containing for holding
The computer-readable program instructions of row various aspects of the disclosure.Computer readable storage medium, which can be, can keep and store
By the tangible device for the instruction that instruction execution equipment uses.Computer readable storage medium for example can be-- but it is unlimited
In-- storage device electric, magnetic storage apparatus, light storage device, electric magnetic storage apparatus, semiconductor memory apparatus or above-mentioned
Any appropriate combination.The more specific example (non exhaustive list) of computer readable storage medium includes: portable computing
Machine disk, hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read only memory (EPROM or
Flash memory), static random access memory (SRAM), Portable compressed disk read-only memory (CD-ROM), digital versatile disc
(DVD), memory stick, floppy disk, mechanical coding equipment, the punch card for being for example stored thereon with instruction or groove internal projection structure, with
And above-mentioned any appropriate combination.Computer readable storage medium used herein above is not interpreted instantaneous signal itself,
The electromagnetic wave of such as radio wave or other Free propagations, the electromagnetic wave propagated by waveguide or other transmission mediums (for example,
Pass through the light pulse of fiber optic cables) or pass through electric wire transmit electric signal.
Computer-readable program instructions described herein can be downloaded to from computer readable storage medium it is each calculate/
Processing equipment, or outer computer or outer is downloaded to by network, such as internet, local area network, wide area network and/or wireless network
Portion stores equipment.Network may include copper transmission cable, optical fiber transmission, wireless transmission, router, firewall, interchanger, gateway
Computer and/or Edge Server.Adapter or network interface in each calculating/processing equipment are received from network to be counted
Calculation machine readable program instructions, and the computer-readable program instructions are forwarded, for the meter being stored in each calculating/processing equipment
In calculation machine readable storage medium storing program for executing.
Computer program instructions for executing present disclosure operation can be assembly instruction, instruction set architecture (ISA)
Instruction, machine instruction, machine-dependent instructions, microcode, firmware instructions, condition setup data or with one or more programmings
The source code or object code that any combination of language is write, the programming language include the programming language-of object-oriented such as
C++ etc., and conventional procedural programming languages-such as " C " language or similar programming language.Computer-readable program refers to
Order can be executed fully on the user computer, partly be executed on the user computer, as an independent software package
Execute, part on the user computer part on the remote computer execute or completely on a remote computer or server
It executes.In situations involving remote computers, remote computer can include local area network by the network-of any kind
(LAN) or wide area network (WAN)-is connected to subscriber computer, or, it may be connected to outer computer (such as utilize internet
Service provider is connected by internet).In some embodiments, by being believed using the state of computer-readable program instructions
Breath comes personalized customization electronic circuit, such as programmable logic circuit, field programmable gate array (FPGA) or programmable logic
Array (PLA), the electronic circuit can execute computer-readable program instructions, to realize the various aspects of present disclosure.
The disclosure the utility model has the advantages that
A kind of the data flashback query and restoration methods, device of PostgreSQL database described in the disclosure, relative to
PostgreSQL carries out recovery data using conventional backup logic or physical backup, and flashback query technology only needs to pass through SQL
Sentence carries out recovery database, easy to operate, convenient and efficient.And do not need to carry out data backup, save memory space.Most
It is important that when routine is restored using Backup Data, it may be necessary to restore to entire library, and need to cut off number
According to library service operations, and flashback query only needs the row data to mistake to restore, and when database itself starts
It can complete, therefore Resuming agent is lower, it is more efficient.
The foregoing is merely preferred embodiment of the present application, are not intended to limit this application, for the skill of this field
For art personnel, various changes and changes are possible in this application.Within the spirit and principles of this application, made any to repair
Change, equivalent replacement, improvement etc., should be included within the scope of protection of this application.Therefore, the present invention is not intended to be limited to this
These embodiments shown in text, and it is to fit to the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1. a kind of data flashback query method of PostgreSQL database, which is characterized in that this method comprises:
Default cleaning control parameter, is modified database table in user based on MVCC mechanism or when delete operation, according to clear
Manage the old row data that control parameter retains change or deletes；
Flashback query sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, it is specified to obtain flashback query
Query time；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility judge,
Return to qualified flashback query data.
2. a kind of data flashback query method of PostgreSQL database as described in claim 1, which is characterized in that described
Cleaning control parameter includes that old row data retention over time parameter and old row data query guarantee parameter.
Database table is old when the old row data retention over time parameter is PostgreSQL database progress data flashback query
The maximum duration that row data save；
The old row data query guarantees that parameter is PostgreSQL database whether guarantee when data flashback query described
Old row data can be arrived by flashback query.
3. a kind of data flashback query method of PostgreSQL database as described in claim 1, which is characterized in that described
The change of reservation or the old row data of deletion include the change retained when executing the old row data of automatic Clean Up Database table or delete
The old row data removed, specific method step include:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, otherwise,
The function that flashes back is closed；
The old row data of database table to be cleared up automatically, the old row data for treating Clean Up Database table are traversed line by line,
Submission time is obtained according to the things number of row data, whether every number in old row data retention over time is judged according to submission time
Whether can be cleaned according to the row data of table；
Submission time retains the row data in old row data retention over time, otherwise clears up the row data automatically.
Further, the change of the reservation or the old row data of deletion include executing the old row data of manual Clean Up Database table
When the old row data of change or deletion that retain, specific method step includes:
Judge whether the old row data retention over time in the cleaning control parameter is greater than zero, if so, enter in next step, otherwise,
The function that flashes back is closed；
The old row data of database table are cleared up manually, are sentenced according to the old row data query guarantee value in cleaning control parameter
It is disconnected whether to carry out manual cleaning operation, if so, carrying out manual cleaning operation, otherwise enter in next step；
The old row data for treating Clean Up Database table are traversed line by line, obtain submission time, root according to the things number of row data
Whether in old row data retention over time judge whether the row data of each tables of data can be cleaned according to submission time；
Submission time retains the row data, otherwise clears up the row data in old row data retention over time.
4. a kind of data flashback query method of PostgreSQL database as described in claim 1, which is characterized in that the party
Method further includes carrying out flash back version querying:
Flash back version querying sentence is received, and syntax parsing is carried out according to predefined syntax parsing rule, obtains flash back version
The specified query time of inquiry；
It is inquired in database table according to query time, the old row data of the change or deletion to reservation carry out visibility judge,
Return to qualified flash back version querying data.
Further, described that syntax parsing acquisition transaction number or query time are carried out according to predefined syntax parsing rule, when
When obtaining transaction number, query time is obtained according to transaction number；It include dodging according to the query time that flashback query sentence obtains
The time value for returning given query in inquiry includes that flash back version is looked into according to the query time that flash back version querying sentence obtains
In inquiry specify at the beginning of and the end time.
5. a kind of data flashback query method of PostgreSQL database as claimed in claim 4, which is characterized in that at this
In method, after obtaining the specified query time of flashback query, judge the query time whether in old row data retention over time,
If so, entering in next step, flashback query can not be carried out by otherwise reporting an error；
Ergodic data library table object obtain database table row data, and from row data obtain insertion newline when transaction number,
Modify the corresponding affairs submission time of transaction number transaction number different with acquisition when row data；
The transaction number that judgement returns to when qualified old row data are insertion newlines has been filed on, and transaction number when insertion newline
Corresponding affairs submission time is not more than the time value of given query in flashback query, and transaction number when modification row data is
The corresponding affairs submission time of transaction number when submitting, and modifying row data is greater than the time value of given query in flashback query；
Qualified old row data are returned as flashback query data.
Further, in the method, it obtains at the beginning of being specified in flash back version querying and after the end time, judges to flash back
At the beginning of specifying in version querying and whether the end time is in old row data retention over time, if so, enter in next step,
Otherwise flashback query can not be carried out by reporting an error；
Ergodic data library table object obtain database table row data, and from row data obtain insertion newline when transaction number,
Modify the corresponding affairs submission time of transaction number transaction number different with acquisition when row data；
For the inquiry in the period in flash back version querying, when judging that returning to qualified old row data is insertion newline
Transaction number have been filed on, and be inserted into newline when the corresponding affairs submission time of transaction number be greater than flash back version querying in specify
Time started and no more than flash back version querying in specify end time, and modification row data when transaction number be equal to insertion
Transaction number when newline, and transaction number when modification row data is not when going lock；
For existing newline data before time started period in flash back version querying, judge to return qualified new
Row data are that transaction number when being inserted into newline has been filed on, and the corresponding affairs submission time of transaction number when insertion newline is not more than
The time value of given query in flashback query, and modification row data when transaction number have been filed on, and modify row data when thing
Business number corresponding affairs submission time is greater than the time value of given query in flashback query；
Qualified old row data or newline data are returned as flash back version querying data.
6. a kind of computer readable storage medium, wherein being stored with a plurality of instruction, which is characterized in that described instruction is suitable for by terminal
The processor of equipment loads and executes a kind of data flash back of PostgreSQL database as described in any one in claim 1-5
Querying method.
7. a kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor is used
In each instruction of realization；Computer readable storage medium is for storing a plurality of instruction, which is characterized in that described instruction is suitable for by handling
Device loads and executes a kind of data flashback query method of PostgreSQL database as described in any one in claim 1-5.
8. a kind of data reconstruction method of PostgreSQL database, which is characterized in that this method is based on claim 1-5 such as and appoints
A kind of data flashback query method of PostgreSQL database described in one, using the data of return as recovery data.
9. a kind of computer readable storage medium, wherein being stored with a plurality of instruction, which is characterized in that described instruction is suitable for by terminal
The processor of equipment loads and executes a kind of data reconstruction method of PostgreSQL database as claimed in claim 8.
10. a kind of terminal device, using internet terminal equipment, including processor and computer readable storage medium, processor
For realizing each instruction；Computer readable storage medium is for storing a plurality of instruction, which is characterized in that described instruction be suitable for by
Reason device loads and executes a kind of data reconstruction method of PostgreSQL database as claimed in claim 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811181663.2A CN109299099B (en) | 2018-10-11 | 2018-10-11 | Data flashback query and recovery method and device for PostgreSQL database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811181663.2A CN109299099B (en) | 2018-10-11 | 2018-10-11 | Data flashback query and recovery method and device for PostgreSQL database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109299099A true CN109299099A (en) | 2019-02-01 |
| CN109299099B CN109299099B (en) | 2020-11-06 |
Family
ID=65162238
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811181663.2A Active CN109299099B (en) | 2018-10-11 | 2018-10-11 | Data flashback query and recovery method and device for PostgreSQL database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109299099B (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597781A (en) * | 2019-08-09 | 2019-12-20 | 北京人大金仓信息技术股份有限公司 | Flashback deletion method and system for database |
| CN112182040A (en) * | 2020-09-30 | 2021-01-05 | 深圳前海微众银行股份有限公司 | Data query method, device, equipment and storage medium |
| CN112948366A (en) * | 2021-03-18 | 2021-06-11 | 上海新炬网络信息技术股份有限公司 | Method for realizing non-conflict between query and recovery in PostgreSQL Standard mode |
| CN117033398A (en) * | 2023-10-09 | 2023-11-10 | 之江实验室 | Data flashback query method and device based on dolt database |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070061487A1 (en) * | 2005-02-01 | 2007-03-15 | Moore James F | Systems and methods for use of structured and unstructured distributed data |
| CN102467421A (en) * | 2010-11-19 | 2012-05-23 | 深圳市金蝶友商电子商务服务有限公司 | Tenant-data-based processing method and computer |
| US20130080398A1 (en) * | 2011-09-23 | 2013-03-28 | Dataguise Inc. | Method and system for de-identification of data within a database |
| CN106547801A (en) * | 2015-09-23 | 2017-03-29 | 北京奇虎科技有限公司 | Database data flash back method and apparatus |
| CN107169108A (en) * | 2017-05-19 | 2017-09-15 | 郑州云海信息技术有限公司 | A kind of method and apparatus that the fast quick-recovery of MySQL data is realized based on Oracle |
-
2018
- 2018-10-11 CN CN201811181663.2A patent/CN109299099B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070061487A1 (en) * | 2005-02-01 | 2007-03-15 | Moore James F | Systems and methods for use of structured and unstructured distributed data |
| CN102467421A (en) * | 2010-11-19 | 2012-05-23 | 深圳市金蝶友商电子商务服务有限公司 | Tenant-data-based processing method and computer |
| US20130080398A1 (en) * | 2011-09-23 | 2013-03-28 | Dataguise Inc. | Method and system for de-identification of data within a database |
| CN106547801A (en) * | 2015-09-23 | 2017-03-29 | 北京奇虎科技有限公司 | Database data flash back method and apparatus |
| CN107169108A (en) * | 2017-05-19 | 2017-09-15 | 郑州云海信息技术有限公司 | A kind of method and apparatus that the fast quick-recovery of MySQL data is realized based on Oracle |
Non-Patent Citations (1)
| Title |
|---|
| 德哥: "PostgreSQL flashback query 闪回功能 emulate by trigger", 《云栖社区》 * |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110597781A (en) * | 2019-08-09 | 2019-12-20 | 北京人大金仓信息技术股份有限公司 | Flashback deletion method and system for database |
| CN110597781B (en) * | 2019-08-09 | 2023-05-05 | 北京人大金仓信息技术股份有限公司 | Flash deleting method and system for database |
| CN112182040A (en) * | 2020-09-30 | 2021-01-05 | 深圳前海微众银行股份有限公司 | Data query method, device, equipment and storage medium |
| CN112948366A (en) * | 2021-03-18 | 2021-06-11 | 上海新炬网络信息技术股份有限公司 | Method for realizing non-conflict between query and recovery in PostgreSQL Standard mode |
| CN112948366B (en) * | 2021-03-18 | 2023-10-27 | 上海新炬网络信息技术股份有限公司 | Method for realizing no conflict between inquiry and recovery under PostgreSQL Standby mode |
| CN117033398A (en) * | 2023-10-09 | 2023-11-10 | 之江实验室 | Data flashback query method and device based on dolt database |
| CN117033398B (en) * | 2023-10-09 | 2024-01-16 | 之江实验室 | Data flashback query method and device based on dolt database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109299099B (en) | 2020-11-06 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109299099A (en) | A kind of the data flashback query and restoration methods, device of PostgreSQL database | |
| CN106033439B (en) | A kind of distributed transaction processing method and system | |
| CN104423960A (en) | Continuous project integration method and continuous project integration system | |
| CN109218080A (en) | A kind of method, monitoring system and the terminal device of automatic drafting network topology architecture | |
| CN106777368A (en) | Data base query language adaptation method and device, distributed data-storage system | |
| CN107832448A (en) | Database operation method, device and equipment | |
| CN104881454A (en) | Updating method and system of parameter | |
| US10083070B2 (en) | Log file reduction according to problem-space network topology | |
| CN113792024A (en) | Method, device, equipment and storage medium for migrating data | |
| CN114385816A (en) | Conversation flow mining method and device, electronic equipment and computer storage medium | |
| CN109101368A (en) | A kind of data processing method and device | |
| CN109726039A (en) | Method and apparatus for managing virtual machine | |
| CN114168119B (en) | Code file editing method, device, electronic equipment and storage medium | |
| CN110895498A (en) | Data storage method and device | |
| CN109815213A (en) | It is deleted on a kind of Append-Only database and the method and system of modification data | |
| CN115794545A (en) | Automatic processing method of operation and maintenance data and related equipment thereof | |
| US10970176B2 (en) | Managing data with restoring from purging | |
| CN111475585B (en) | Data processing method, device and system | |
| US11294773B2 (en) | Method, apparatus and computer program product for managing backup system | |
| CN114466387A (en) | Method and device for updating configuration file of base station, storage medium and electronic device | |
| CN110221952B (en) | Service data processing method and device and service data processing system | |
| CN112817953A (en) | Data verification method and device, computer equipment and computer-readable storage medium | |
| US10552387B1 (en) | Managing data using a simulation of purging | |
| CN110704322B (en) | Software testing method and system | |
| CN109067572A (en) | Method, device and equipment based on order edlin SNMP configuration file |
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 |