修复损坏的数据库 postgresql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11030173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Repair Corrupt database postgresql
提问by Jeff Lee Ngotho
I have multiple errors with my postgresql db, which resulted after a power surge:
我的 postgresql db 有多个错误，这是在电源激增后导致的：
I cannot access most tables from my database. When I try for example select * from ac_cash_collection, I get the foolowing error:
我无法从我的数据库访问大多数表。例如select * from ac_cash_collection，当我尝试时，我收到了愚蠢的错误：
ERROR: missing chunk number 0 for toast value 118486855 in pg_toast_2619
when I try pg_dump I get the following error:
当我尝试 pg_dump 时，出现以下错误：
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.
我继续尝试运行整个数据库的reindex，实际上我让它运行，去睡觉，我发现它早上没有做任何事情，所以我不得不取消它。
I need some help to fix this as soon as possible, Please help.
我需要一些帮助来尽快解决这个问题，请帮忙。
采纳答案by wildplasser
Before you do anythingelse, http://wiki.postgresql.org/wiki/Corruptionand act on the instructions. Failure to do so risks making the problem worse.
在您执行任何其他操作之前，请访问 http://wiki.postgresql.org/wiki/Corruption并按照说明操作。不这样做可能会使问题变得更糟。
There are two configuration parameters listed in the Fine Manualthat might be of use: ignore_system_indexesand zero_damaged_pages. I have never used them, but I would if I were desparate ...
Fine Manual中列出了两个可能有用的配置参数：ignore_system_indexes和zero_damaged_pages. 我从来没有用过它们，但如果我绝望的话我会......
I don't know if they help against toast-tables. In any case, if setting them causes your database(s) to become usable again, I would {backup + drop + restore} to get all tables and catalogs into newborn shape again. Success!
我不知道它们是否有助于对抗烤面包桌。在任何情况下，如果设置它们会导致您的数据库再次可用，我会{backup + drop + restore} 再次将所有表和目录恢复到新生状态。成功！
回答by Richard Huxton
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.
如果没有 - 您刚刚了解了为什么需要定期备份。如果硬件行为异常，PostgreSQL 将无能为力。
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.
此外，如果您再次遇到这种情况，请首先停止 PostgreSQL 并对所有内容（所有表空间、WAL 等）进行完整的文件级备份。这样您就有了一个已知的起点。
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.
- 尝试转储单个表。以这种方式得到你能得到的。
- 如果索引导致问题，则删除索引
- 转储表的部分（id=0..9999、1000..19999 等） - 这样您就可以确定某些行可能损坏的位置并转储更小的部分以恢复仍然良好的部分。
- 尝试仅转储某些列 - 大文本值存储在外行（在 toast 表中），因此避免它们可能会导致其余数据丢失。
- 如果您的系统表已损坏，那么您将需要进行大量工作。
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.
从中获得的关键信息 - 确保定期备份，并确保它们有效。
回答by Craig Ringer
Before you do ANYTHING ELSE, take a complete file-system-level copy of the damaged database.
在您执行任何其他操作之前，请获取损坏数据库的完整文件系统级副本。
http://wiki.postgresql.org/wiki/Corruption
http://wiki.postgresql.org/wiki/Corruption
Failure to do so destroys evidence about what caused the corruption, and means that if your repair efforts go badly and make things worse you can't undo them.
不这样做会破坏导致损坏原因的证据，这意味着如果您的修复工作失败并使事情变得更糟，您将无法撤消它们。
Copy it now!
复制它现在！
