recovery_target_time ignored or recovery always recovers to end of WAL

From: "Jason L(dot) Buberel" <jason(at)buberel(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: recovery_target_time ignored or recovery always recovers to end of WAL
Date: 2007-07-02 04:41:46
Message-ID: 4688820A.9020705@buberel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ashish Karalkar 2007-07-02 05:11:36 assigning password from script
Previous Message Nick Johnson 2007-07-02 04:16:01 'Session local' variables