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

On Wed, 07 Jul 2010 22:18:13 +0200, Jesper Krogh wrote:

>On 2010-07-07 20:54, John T. Dow wrote:
>> I changed the recovery.conf file so it has one statement:
>>
>> restore_command = 'copy D:\devj\WCSD\received\pg_xlog\%f %p'
>>
>>
>>
>> This is what I now get in pg_log:
>>
>> LOG: database system was shut down at 2010-07-07 14:48:34 EDT
>> LOG: starting archive recovery
>> LOG: restore_command = 'copy D:\devj\WCSD\received\pg_xlog\%f %p'
>> LOG: could not open file "pg_xlog/0000000700000000000000BE" (log file 0, segment 190): No such file or directory
>> LOG: invalid primary checkpoint record
>> LOG: could not open file "pg_xlog/0000000700000000000000BE" (log file 0, segment 190): No such file or directory
>> LOG: invalid secondary checkpoint record
>> PANIC: could not locate a valid checkpoint record
>>
>>
>>
>> And as before, nothing is restored.
>>
>> These are the "archived" files:
>>
>> 0000000100000000000000B6
>> 0000000100000000000000B7
>> 0000000100000000000000B8
>> 0000000100000000000000B9
>> 0000000100000000000000BA
>> 0000000100000000000000BB
>> 0000000100000000000000BC
>>
>> It's looking for timeline 7 but the timeline of the archived files is 1. (Big deal, I can rename them.)
>>
>> It's also looking for file BE, but the files are B6 through BC.
>>
>>
>> I also tried with this: restore_command = 'copy D:\devj\WCSD\received\%f %p'
>>
>>
>then you either have a wrong base backup (did you run
>pg_start_backup/pg_stop_backup)? or
>the wrong set of WAL-files for your database.
>
>--
>Jesper

We did not do pg_start_backup/pg_stop_backup.

I was naively hoping that the WAL files would work on a "similar" database (ie same tables, but created independently).

I understand that WAL files can only be applied to the database which they are based on, or an exact copy of the database. Therefore we need to copy the existing database files (file copy) and then try this technique on the copy. That will take some doing, as people are not in the same city, don't work the same hours, etc.

What if we never did pg_start_backup? Will it work anyway?

Also, it is unclear from the documentation whether you must have archived wal files to replay the wal files. There seems to be no way to replay them other than with recovery.conf, and recovery.conf seems to require a restore_command that actually does something. Is it necessary to clear any wal files from the pg_xlog directory, and is it necessary that all wal files to be replayed must be copied into pg_xlog using the restore_command? This things aren't really stated very clearly.

John