Repair Corrupt database postgresql
I have multiple errors with my postgresql db, which resulted after a power surge:
I cannot access most tables from my database. When I try for example select * from ac_cash_collection, I get the foolowing error:
ERROR: missing chunk number 0 for toast value 118486855 in pg_toast_2619
when I try pg_dump I get the following error:
Error message from server: ERROR: relation "public.st_stock_item_newlist" does not exist pg_dump: The command was: LOCK TABLE public.st_stock_item_newlist IN ACCESS SHARE MODE
I went ahead and tried to run reindex of the whole database, I actually I left it runnng, went to sleep, and I found it had not done anything in the morning, so I had to cancel it.
I need some help to fix this as soon as possible, Please help.
Answer
If you have backups, just restore from them.
If not - you've just learned why you need regular backups. There's nothing PostgreSQL can do if hardware misbehaves.
In addition, if you ever find yourself in this situation again, first stop PostgreSQL and take a complete file-level backup of everything - all tablespaces, WAL etc. That way you have a known starting point.
So - if you still want to recover some data.
- Try dumping individual tables. Get what you can this way.
- Drop indexes if they cause problems
- Dump sections of tables (id=0..9999, 1000..19999 etc) - that way you can identify where some rows may be corrupted and dump ever-smaller sections to recover what's still good.
- Try dumping just certain columns - large text values are stored out-of-line (in toast tables) so avoiding them might get the rest of your data out.
- If you've got corrupted system tables then you're getting into a lot of work.
That's a lot of work, and then you'll need to go through and audit what you've recovered and try to figure out what's missing/incorrect.
There are more things you can do (creating empty blocks in some cases can let you dump partial data) but they're all more complicated and fiddly and unless the data is particularly valuable not worth the effort.
Key message to take away from this - make sure you take regular backups, and make sure they work.