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?
Thanks,
jason