Salvaging data from corrupt tables PostgreSQL

Question

Am in the process of salvaging very crucial data from a corrupted database.

Am getting the following error from a very crucial table ERROR: invalid page header in block 94206 of relation base/11974232/11975439

when I set zero_damaged_pages to on , I realized I end up losing alot of pages which contain data that's significant to a meaningful recovery.

so my question is:-

1.How do I maximize data recovery without loosing the pages?

1. when I do a query on some tables, the connection gets lost before the query executes. usually the query runs for few seconds before the disconnection. How do I deal with this

Answer 1

If you have a recent backup, consider using that.

Otherwise, before you do anything else, make a copy of the directories containing the data you wish to recover. You may wish to use http://www.forensicswiki.org/wiki/Ddrescue to make a very low-level copy of the partition. It works in a way that will tolerate faults in the underlying storage medium.

From there you may wish to hire data recovery experts or PostgreSQL experts. If that's not an option, I would be prepared to get your hands dirty with the documentation for PostgreSQL's on-disk storage, so you can consider hand-repairing some of the data to make it valid again (to the extent that this is possible).