Dropped table, no backup, restore from file system backup or WAL files?
От | John T. Dow |
---|---|
Тема | Dropped table, no backup, restore from file system backup or WAL files? |
Дата | |
Msg-id | 201007070131.o671Vefg021862@web7.nidhog.com обсуждение исходный текст |
Ответы |
Re: Dropped table, no backup, restore from file system backup
or WAL files?
|
Список | 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 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
В списке pgsql-novice по дате отправления: