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
ranwas 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)
andthen export the data in the three tables to csv files and reimport it. With that approach, is there anything that
shouldbe 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
followingerror 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
properWAL 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
restorethe data. Is it not possible, without doing a checkpoint? Can I construct a check point manually?
John