Re: 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