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
