Repairing a damaged postgresql database

Question

I have a few errors with my postgresql db that occurred after the surge:

I cannot access many tables from my database. When I try, for example, select * from ac_cash_collection , I get a cheating 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 entire database, I actually left it runnng, went to sleep, and I found that I did nothing in the morning, so I had to cancel it.

I need help to fix this as soon as possible, please help.

Answer 1

Before doing anything else, http://wiki.postgresql.org/wiki/Corruption and follow the instructions. Failure to do so makes the problem difficult.

---

Fine Manual has two configuration options: ignore_system_indexes and zero_damaged_pages . I never used them, but if I were desperate ...

I don’t know if they help against toasts. In any case, if installing them makes your database usable again, I would do {backup + drop + restore} to return all tables and directories to their newborn form again. Success!

Answer 2

If you have backups, simply restore them.

If not, you just found out why regular backups are needed. PostgreSQL cannot do anything there if the hardware is incorrect.

In addition, if you ever find yourself in this situation again, first stop PostgreSQL and take a full backup of the entire level - all table spaces, WAL, etc. So you have a famous starting point.

So - if you still want to recover some data.

• Try resetting individual tables. Get what you can this way.
• Drop indexes if they cause problems.
• Dump table partitions (id = 0..9999, 1000..19999, etc.) - this way you can determine where some rows may get corrupted and reset all smaller partitions to restore what is still good.
• Try to reset only certain columns - large text values ​​are stored out of turn (in tables with toasts), so you can avoid extracting the rest of the data.
• If you have corrupted system tables, you get a lot of work.

This is a lot of work, and then you will need to go through and check what you have restored, and try to find out what is missing / incorrect.

There are many things you can do (creating empty blocks in some cases may allow you to dump partial data), but they are more and more complicated and if the data is not particularly valuable, it’s not worth the effort.

The key message to take away is to make sure you make regular backups and make sure they work.

Answer 3

Before doing anything, make a full copy of the damaged database at the file system level.

http://wiki.postgresql.org/wiki/Corruption

Failure to do so destroys the evidence of what caused the corruption, and means that if your recovery efforts are getting worse and worse, you cannot cancel it.

Copy it now!