Re: [GENERAL] recovery_target_time ignored or recovery alwaysrecovers to end of WAL
On Sun, 2007-07-01 at 21:41 -0700, Jason L. Buberel wrote: I am trying to learn/practice the administrative steps that would need to be taken in a 'fat finger' scenario, and I am running into problems. I am trying to use 'recovery.conf' to set the database state to about 15 minutes ago in order to recover from accidentally deleting important data. However, each time I restart the database in recovery mode, it seems to always return me to the state it was in when I shut it down, ignoring my 'recovery_target_time' setting. For example: 1. I have a production 8.2.4 database running with WAL archiving enabled. 2. Thinking I am logged into a development database I issue the commands: start transaction; delete from billing_info; delete from customer_account; commit; 3. I suddenly realize I was logged into the production database. 4. I fall out of my chair, then regain consciousness 10 minutes later. 5. I shutdown the database, and create a 'recovery.conf' file as follows: # pretend that 2007-07-01 20:50:00 PDT was 15 minutes ago. recovery_target_time = '2007-07-01 20:50:00 PDT' restore_command = 'cp /pgdata/archive_logs/%f %p' recovery_target_inclusive = 'false' 6. I start the database, and I see the following log messages: LOG: starting archive recovery LOG: recovery_target_time = 2007-07-01 20:50:00-07 LOG: restore_command = cp /pgdata/archive_logs/%f %p LOG: recovery_target_inclusive = false LOG: checkpoint record is at F/7E0DD5A4 LOG: redo record is at F/7E0DD5A4; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/693577; next OID: 35828734 LOG: next MultiXactId: 28; next MultiXactOffset: 55 LOG: automatic recovery in progress LOG: record with zero length at F/7E0DD5EC LOG: redo is not required LOG: archive recovery complete LOG: database system is ready 7. I log back in to the database, expecting to see all of my billing_info an customer_account records in place. But instead, the tables are empty - just as they were when the db was shutdown. What have I don't wrong? Or is there some other procedure to use in these situations? Your example transactions are so large that going back 15 minutes is not enough. You'll need to go back further. recovery_target_time can only stop on a COMMIT or ABORT record. This is because it makes no sense to recover half a transaction, only whole transactions have meaning for recovery. So if the transactions are very large, you need to go back further. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] recovery_target_time ignored or recovery alwaysrecovers to end of WAL
Simon, Thanks for the tip. I had assumed that so long as I set 'recovery_target_time' to a value that occurred before the 'fatal commit' and set the 'inclusive' flag to false that I would be able to return to just before the deletion occurred. I'll play with it a bit more and see. I just want to know what to do in the future should a real emergency like this occur. Thanks, jason Simon Riggs wrote: On Sun, 2007-07-01 at 21:41 -0700, Jason L. Buberel wrote: Your example transactions are so large that going back 15 minutes is not enough. You'll need to go back further. recovery_target_time can only stop on a COMMIT or ABORT record. This is because it makes no sense to recover half a transaction, only whole transactions have meaning for recovery. So if the transactions are very large, you need to go back further. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] recovery_target_time ignored or recovery alwaysrecovers to end of WAL
Simon Riggs [EMAIL PROTECTED] writes: On Sun, 2007-07-01 at 21:41 -0700, Jason L. Buberel wrote: I am trying to learn/practice the administrative steps that would need to be taken in a 'fat finger' scenario, and I am running into problems. Your example transactions are so large that going back 15 minutes is not enough. You'll need to go back further. recovery_target_time can only stop on a COMMIT or ABORT record. This is because it makes no sense to recover half a transaction, only whole transactions have meaning for recovery. So if the transactions are very large, you need to go back further. No, that doesn't explain it. As long as he set the stop time before the commit of the unwanted transaction, it should do what he's expecting. It might uselessly replay a lot of the actions of a long-running transaction, but it will stop before the COMMIT xlog record when it reaches it, and thus the transaction will not be committed. What's actually happening according to the log output is that it's running all the way to the end of WAL. I can't really think of an explanation for that other than a mistake in choosing the stop time, ie, it's later than the commit of the unwanted transaction. Or maybe the WAL file is a stale copy that doesn't even contain the unwanted commit? Jason, if you can't figure it out you might grab xlogviewer http://pgfoundry.org/projects/xlogviewer/ and see what it says the timestamps of the commit records in your WAL files are. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] recovery_target_time ignored or recovery alwaysrecovers to end of WAL
Harrumph - I downloaded the latest xlogdump source, and built/installed it against my 8.2.4 source tree. When I execute it however, I am informed that all of my WAL files (either the 'active' copies in pg_xlog or the 'archived' copies in my /pgdata/archive_logs dir) appear to be malformed: $ /opt/postgres-8.2.4/bin/xlogdump --port 54824 --host 127.0.0.1 --user postgres ../../../archive_logs/* ../../../archive_logs/0001000F007C: Bogus page magic number D05E at offset 0 invalid record length at F/7C1C ../../../archive_logs/0001000F007C.00550700.backup: Partial page of 241 bytes ignored ../../../archive_logs/0001000F007D: Bogus page magic number D05E at offset 0 invalid record length at F/7D1C ../../../archive_logs/0001000F007D.0006C01C.backup: Partial page of 241 bytes ignored Which does not help particularly much :) I'll keep plugging away at this - perhaps my problem in setting the database state to a PITR is related to timezones or timestamp formatting? -jason Tom Lane wrote: Jason, if you can't figure it out you might grab xlogviewer http://pgfoundry.org/projects/xlogviewer/ and see what it says the timestamps of the commit records in your WAL files are. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
