Dropped table, no backup, restore from file system backup or WAL files?

From: "John T(dot) Dow" <john(at)johntdow(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Dropped table, no backup, restore from file system backup or WAL files?
Date: 2010-07-07 01:24:56
Message-ID: 201007070131.o671Vefg021862@web7.nidhog.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Less than a week ago we installed a database and new software in a production environment.

Today someone was attempting to install the same software in a test environment, and realized too late that a script he ran was run against the production database. It dropped three tables.

We need to recover those tables. This is what we have to work with.

1) We have a file system backup from 3AM.

2) We have not yet instituted a daily postgres backup with pg_dump.

3) We have all the WAL files since going into production (unarchived, only 6 files needed to cover the period).

There are two ways I can think of to try to recover the data.

1) I presume that we can restore the entire directory from the 3am backup (to a different physical location of course) and then export the data in the three tables to csv files and reimport it. With that approach, is there anything that should be done to test the integrity of the data?

2) I should think that I could also restore the data from the WAL files, but when I create a recovery.conf file and use pg_resetxlog.exe, I can get it to do its thing without complaint (renames recovery.conf to recovery.done) or I get the following error in pg_log:

LOG: database system was shut down at 2010-07-06 13:27:42 EDT
LOG: starting archive recovery
LOG: restore_command = 'donothing.bat'
LOG: invalid magic number 0000 in log file 0, segment 31, offset 0
LOG: invalid primary checkpoint record.
LOG: invalid magic number 0000 in log file 0, segment 31, offset 0
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record

In neither case does it restore any data.

Note: for the recovery procedure, I have a BAT file that literally does nothing. The reason is that I've copied the proper WAL files into the pg_xlog directory, so there is no archiving per se.

I have looked at the options pg_resetxlog.exe has and can't figure out if there's some parameter I can set to make it restore the data. Is it not possible, without doing a checkpoint? Can I construct a check point manually?

John

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jesper Krogh 2010-07-07 04:53:59 Re: Dropped table, no backup, restore from file system backup or WAL files?
Previous Message Thom Brown 2010-07-06 22:15:37 Re: Data type OIDs