Thread: Recovery Features
I'm looking at a couple of features that are possible to add. >From what people have said before on list, one of the useful features of PITR is to recover from rogue transactions. I have a suggestion that I'd like some considered thought on. I've given it a couple of weeks thought. My first inclination is that it is cool, my second that it's pretty stupid, my third, well...? ...While recovering, it is very straightforward to simply ignore every record associated with one (or more) transactions. That gives us the ability to recover "all apart from txnid X". This great because: if you can locate the transactionId you desire (a problem in itself, but lets not dwell on those complexities for now), then you blow it away, real smooth - as if it had never existed. Neat, simple, fast. Dropped tables and removed tablespaces are a bit more of a problem, but deleted rows, accidental full table updates etc can be undone in a snap (NOT DDL, in short). This is awful because: transactions are isolated from each other, but they also provide changes of state that rely on previous committed transactions. If you change the past, you could well invalidate the future. If you blow away a transaction and a later one depends upon it, then you will have broken the recovery chain and will not be able to recover to present time. This is only useful IF i) you know for certain no later transaction has taken place ii) you know there are later transactions, but you either know they are invalid because of the rogue, or they're just not as important as the effects of the rogue. ...but could leave you up to your neck if misused. I'm thinking: undo_transactionId = X Don't flame me, just hold you're fire, think about it and get back to me. It's a slightly off the wall idea, so if you react quickly, you'll just say no - and if you don't, go think some more. Anyhow, if not, then you know who to call when it hits the fan.... This also leaves open the door for some more advanced functionality that blows away the effects of a transaction and all of its time-ordered descendants, just to make it 100% safe. Best regards, Simon Riggs
Simon Riggs <simon@2ndquadrant.com> writes:
> ...While recovering, it is very straightforward to simply ignore every
> record associated with one (or more) transactions. That gives us the
> ability to recover "all apart from txnid X".
Don't even *think* of going there.
What will happen when transaction Y comes along and wants to modify or
delete a row that was inserted by X? There's no chance of staying
consistent.
regards, tom lane
On Mon, 2004-07-05 at 22:30, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > ...While recovering, it is very straightforward to simply ignore every > > record associated with one (or more) transactions. That gives us the > > ability to recover "all apart from txnid X". > > Don't even *think* of going there. Hmmm... thinking is important, as are differing viewpoints. I value yours and those of everyone else on this list, hence the post. > What will happen when transaction Y comes along and wants to modify or > delete a row that was inserted by X? There's no chance of staying > consistent. I did point out this downside...a few sentences down. **This is awful because: transactions are isolated from each other, but they also provide changes of state that rely on previous committed transactions. If you change the past, you could well invalidate the future. If you blow away a transaction and a later one depends upon it, then you will have broken the recovery chain and will not be able to recover to present time.** Theoretically, this is a disaster area. Practically, Oracle10g provides similar-ish features... ...Nobody is shouting YES, so its a dodo... Best regards, Simon Riggs
Simon Riggs wrote: > On Mon, 2004-07-05 at 22:30, Tom Lane wrote: > > ...Nobody is shouting YES, so its a dodo... I can imagine a scenario where the junior DBA accidentally deletes all rows from some obscure table that wouldn't have logical implications for later transactions. But I suspect most people would perform recovery in a separate instance, and just hand-dump/restore the table in question. The point at which the above process becomes too complex (or less than obvious) for hand-recovery is precisely when unforeseen consequences of nixing a single transaction become too great. IMHO, Mike Mascari
On Mon, 2004-07-05 at 23:40, Mike Mascari wrote: > Simon Riggs wrote: > > > > > ...Nobody is shouting YES, so its a dodo... > > The point at which the above process becomes > too complex (or less than obvious) for hand-recovery is precisely > when unforeseen consequences of nixing a single transaction become > too great. > Agreed. The potential for unforeseen consequences is just too high, and although I'm fairly sure they would always be spotted during recovery and cause an error - I think it is something that requires proof. And I don't have that. So, lets leave that idea alone for 100 years. > ... hand-recovery ... hmmm...not sure I know what you mean. It is very-very-close-to-impossible to edit the transaction logs manually, unless some form of special-format editor were written for the purpose. Is it clear that the PITR features are completely different from pg_dump? (Which would allow a manual edit and recover). The xlogs are binary files that refer to all changes to all tables in a cluster ordered by time, rather than by table. Best regards, Simon Riggs
Simon Riggs wrote: > On Mon, 2004-07-05 at 23:40, Mike Mascari wrote: > > hmmm...not sure I know what you mean. > > It is very-very-close-to-impossible to edit the transaction logs > manually, unless some form of special-format editor were written for the > purpose. > > Is it clear that the PITR features are completely different from > pg_dump? (Which would allow a manual edit and recover). The xlogs are > binary files that refer to all changes to all tables in a cluster > ordered by time, rather than by table. What I meant by hand-restore was 1. A total backup occurrs on Monday morning 2. Transactions occur on Monday, Tuesday and Wednesday, with PITR archiving enabled 3. Intern deletes everyting from obscure_table on Thursday afternoon and wants to restore it as it was on Thursday morning 4. On some other machine, the total backup is restored into a new cluster, the transaction logs replayed to that point-in-time where intern deleted everything from obscure_table 5. The table is dumped manually and restored in the production database, because it is known that this table has no logicial implications for the consisetency of other tables. That's what I meant by hand-restore. Mike Mascari
On Tue, 2004-07-06 at 00:30, Mike Mascari wrote: > Simon Riggs wrote: > > > On Mon, 2004-07-05 at 23:40, Mike Mascari wrote: > > > > hmmm...not sure I know what you mean. > > > > It is very-very-close-to-impossible to edit the transaction logs > > manually, unless some form of special-format editor were written for the > > purpose. > > > > Is it clear that the PITR features are completely different from > > pg_dump? (Which would allow a manual edit and recover). The xlogs are > > binary files that refer to all changes to all tables in a cluster > > ordered by time, rather than by table. > > What I meant by hand-restore was > > 1. A total backup occurrs on Monday morning > > 2. Transactions occur on Monday, Tuesday and Wednesday, with PITR > archiving enabled > > 3. Intern deletes everyting from obscure_table on Thursday afternoon > and wants to restore it as it was on Thursday morning > > 4. On some other machine, the total backup is restored into a new > cluster, the transaction logs replayed to that point-in-time where > intern deleted everything from obscure_table > > 5. The table is dumped manually and restored in the production > database, because it is known that this table has no logicial > implications for the consisetency of other tables. > > That's what I meant by hand-restore. > Excellent. Thanks very much for the clarification. I was worried that I had misrepresented the functionality, but you have it spot on. The scenario you describe is going to be possible now... Best Regards, Simon Riggs
On 7/5/2004 6:16 PM, Simon Riggs wrote: > On Mon, 2004-07-05 at 22:30, Tom Lane wrote: >> Simon Riggs <simon@2ndquadrant.com> writes: >> > ...While recovering, it is very straightforward to simply ignore every >> > record associated with one (or more) transactions. That gives us the >> > ability to recover "all apart from txnid X". >> >> Don't even *think* of going there. > > Hmmm... thinking is important, as are differing viewpoints. I value > yours and those of everyone else on this list, hence the post. > >> What will happen when transaction Y comes along and wants to modify or >> delete a row that was inserted by X? There's no chance of staying >> consistent. > > I did point out this downside...a few sentences down. > **This is awful because: transactions are isolated from each other, but > they also provide changes of state that rely on previous committed > transactions. If you change the past, you could well invalidate the > future. If you blow away a transaction and a later one depends upon it, > then you will have broken the recovery chain and will not be able to > recover to present time.** > > Theoretically, this is a disaster area. > > Practically, Oracle10g provides similar-ish features... IF ... the recovery process would be primary key based, and IF the database definitions would allow for balance type field handling (the log contains value deltas for balance fields instead of overwriting them), THEN this would be a direction I would be looking into. But as things are, the whole recovery is ctid and binary block based. So you would now leave out the ctid based changes to several tuples because of belonging to said transaction. Later on, an original whole block appears in the WAL and overwrites ... so you get what ... partial transactions into the recoverd DB? > > ...Nobody is shouting YES, so its a dodo... No way! > > Best regards, Simon Riggs > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Sat, 2004-07-10 at 15:04, Jan Wieck wrote: > On 7/5/2004 6:16 PM, Simon Riggs wrote: > > > On Mon, 2004-07-05 at 22:30, Tom Lane wrote: > >> Simon Riggs <simon@2ndquadrant.com> writes: > >> > ...While recovering, it is very straightforward to simply ignore every > >> > record associated with one (or more) transactions. That gives us the > >> > ability to recover "all apart from txnid X". > >> > >> Don't even *think* of going there. > > > > Hmmm... thinking is important, as are differing viewpoints. I value > > yours and those of everyone else on this list, hence the post. > > > >> What will happen when transaction Y comes along and wants to modify or > >> delete a row that was inserted by X? There's no chance of staying > >> consistent. > > > > I did point out this downside...a few sentences down. > > **This is awful because: transactions are isolated from each other, but > > they also provide changes of state that rely on previous committed > > transactions. If you change the past, you could well invalidate the > > future. If you blow away a transaction and a later one depends upon it, > > then you will have broken the recovery chain and will not be able to > > recover to present time.** > > > > Theoretically, this is a disaster area. > > > > Practically, Oracle10g provides similar-ish features... > > IF ... the recovery process would be primary key based, and IF the > database definitions would allow for balance type field handling (the > log contains value deltas for balance fields instead of overwriting > them), THEN this would be a direction I would be looking into. > > But as things are, the whole recovery is ctid and binary block based. So > you would now leave out the ctid based changes to several tuples because > of belonging to said transaction. Later on, an original whole block > appears in the WAL and overwrites ... so you get what ... partial > transactions into the recoverd DB? > > > > > ...Nobody is shouting YES, so its a dodo... > > No way! > Sorry...I meant "this idea is dead, just like the extinct Dodo bird".- I've been trying to be succinct, but that has led to information loss. ...you've come up with an even better reason why the idea is not good. Many thanks, Best Regards, Simon Riggs
On 7/10/2004 3:21 PM, Simon Riggs wrote: > On Sat, 2004-07-10 at 15:04, Jan Wieck wrote: >> > ...Nobody is shouting YES, so its a dodo... >> >> No way! >> > > Sorry...I meant "this idea is dead, just like the extinct Dodo bird".- > I've been trying to be succinct, but that has led to information loss. I know, "preserve the Dodo way of life!" or did you mean the one from Alice in Wonderland? I was only trying to shout "NO" in a slightly more polite way :-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #