How to repair corrupted PostgreSQL pg_toast error

Question

The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I try to access certain rows on my single biggest table, I get errors like:

ERROR:  could not read block 28126 in file "base/106800/107273": read only 0 of 8192 bytes

I've mostly fixed this by going through the rows and deleting the ones that were not retrievable. However, I still get errors like:

ERROR: missing chunk number 0 for toast value 151065 in pg_toast_106852

Is there any way to fix this? Googling doesn't find any suggestions. I found this similar question, but the sole answer is essentially "I think your database is corrupt", which is diagnosis but not a solution.

Other proposed solutions were to do a vacuuming or reindexing, both on the table and the entire database, but those had no effect.

How do I repair this damage? I know truncating the table would fix it, but that would destroy all remaining data, and most of the records appear to be intact.

Answer 1

I'm sorry you ran into that kind of problem, due to device failure. I suppose you don't have any backup (either physical or logical) from before the corruption.

You may find interesting things in Christophe Petus's conf talk "Corruption war stories". You'll find the slides here and the youtube video there.

What you could do is trying to copy all your uncorrupted data in another table (created with create table like for example to preserve constraints, comments and indexes), drop the other table and rename the new one...