Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting error:

psql: FATAL:  could not read block 0 in file "base/18819/11700": read only 0 of 8192 bytes. 

This is one of the missing files though most of them are fine. Is there a way?

Thanks

share|improve this question

migrated from stackoverflow.com Sep 9 '13 at 2:44

This question came from our site for professional and enthusiast programmers.

2 Answers 2

I think that the only way in this case is to recover from a backup file if you have one of them ... To restore from a backup file you can use the pg_restore utility .

share|improve this answer

There is a special option: zero_damaged_pages=on that you can use on postgresql.conf, it is documented here.

This option will allow for a pg_dump (or pg_dump_all) that do not stop on critical errors and get as much data back as possible, but you will loose the data that cannot be read.:

(exceprt from documentation, I added the strong.

Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.