Re: Database Recovery from Corrupted Dump or Raw database table file.

On 07/11/2016 13:12, Albe Laurenz wrote:
> Howard News wrote:
>> I have a raid catastrophe which has effectively blitzed a cluster data
>> directory.  I have several pg_dump backups but these will not restore
>> cleanly. I assume the disk has been failing for some time and the
>> backups are of the corrupted database.
>>
>> Using a selective pg_restore on the dumps, I have restored 2/3rds of the
>> data but some tables I cannot recover directly, so I would like to see
>> if it is possible to examine the dumps (they are in compressed format)
>> to see if there are any rows which are recoverable. I do not know how or
>> if it is even possible to pick out rows from an individual table, when
>> in all likelyhood the file itself is corrupted.
>>
>> I also have some parts of the data directory, so the tables may be
>> accessible if I know the ID of the corrupt datatables. Is the ID listed
>> in the pg_dump --list file?  And can the data be extracted from the raw
>> data files without running a cluster. I am unsure if there is enough
>> data to actually start a cluster.
> A backup created with pg_dump consists of SQL statements to recreate the
> objects.
>
> You can extract the SQL statements as text with
>
>    pg_restore -f sqlfile backupfilename
>
> That should help you with restoring the data.
>
> What exactly do you mean by "do not restore cleanly"?
> Do you get error messages or is the content not ok?
>
> Yours,
> Laurenz Albe
Hi Laurenz,

Yes I get errors when trying to restore. When I try to restore to an
uncompressed file as suggested, I get the same errors - or the
pg_restore crashes.

e.g. in the console I get the following...

...
pg_restore: executing SEQUENCE SET example_seq
pg_restore: processing data for table example_table
pg_restore: [compress_io]

** crash **

If I selectively restore I have about 10 tables that do not recover, and
all of the Blobs.

Howard.