从 PostgreSQL 中挽救损坏的表

~/tmp-dir.dab4fd85-8b47-4d9a-b15c-18312ef61075 pg_dump -U postgres -h locathost www_p1 > wow_p1.sql

pg_dump：错误：转储表 “page_views” 的内容失败：PQgetResult() 失败。
pg_dump：详细信息：来自服务器的错误消息：ERROR: relation base/16384/16417 的块 31869 中的页面无效
pg_dump：详细信息：命令为：COPY public.page_views (page_view_id, visited_at, hostname, ip, method, endpoint, user_id, xhr) TO stdout;

警告：千万不要这样做…… 实际上任何时候都不要，尤其是在磁盘有故障的服务器上。这里是在磁盘正常但 Postgres 块损坏的服务器上进行的操作。

在我的专业工作和家庭实验室中，我花了大量时间尝试学习和实施 “正确” 或 “可靠” 的解决方案 —— 高可用部署、自动化和经过测试的备份、基础设施即代码等等。

但这次不是。

这是一种非常粗暴、毫无顾忌、绝对疯狂的做法，如果你在任何重要的环境中工作，你应该阅读本文并聘请专业人员。

由于一些不重要的原因，我一直在处理家庭实验室中 Postgres 服务器上的数据损坏问题。服务器有几次非正常关闭，导致磁盘数据损坏。因为没有什么比临时解决方案更持久的了，所以这台服务器没有备份。

对于大多数数据，我能够使用 pg_dump 转储模式和数据，并将其重新导入到新的 Postgres 服务器中（是的，新服务器现在已经配置了备份）。

pg_dump -U postgres -h localhost my_database > my_database.sql

但是，对于有损坏表的数据库，pg_dump 会因这个令人不安的错误而失败：

pg_dump -U postgres -h localhost www_p1 > www_p1.sql
pg_dump: error: 转储表“page_views”的内容失败：PQgetResult() 失败。
pg_dump: detail: 来自服务器的错误消息：ERROR: relation base/16384/16417 的块 31869 中的页面无效
pg_dump: detail: 命令为：COPY public.page_views (page_view_id, visited_at, hostname, ip, method, endpoint, user_id, xhr) TO stdout;

（…… 是的，那是我的个人网站的数据库。👀）令我有些惊讶的是，我找不到很多关于如何 “尽力” 从损坏的 Postgres 表中恢复数据的详细信息或策略，所以就有了这篇文章。

幸运的是，由于损坏是由 Postgres 非正常退出而不是物理磁盘故障引起的，它只影响了当时频繁写入的表。在这种情况下，就是 sessions 表和 page_views 表。sessions 表完全可以丢弃 —— 我在新服务器上重新创建了一个空表，然后就不管它了。

如果我丢失了 page_views 表，也不是世界末日，但表中记录了大约 650 万条历史页面浏览量，丢失它们还是挺可惜的。所以…… 让我们做些冒险的事情。

我的目标不是恢复整个表。如果是这个目标，我就会停下来聘请专业人员了。相反，我的目标是尽可能多地恢复表中的行。

pg_dump 失败的一个原因是它试图使用游标读取数据，当 Postgres 的基本假设被违反时（例如磁盘块中的坏数据、无效索引），游标读取会失败。

我的策略是在损坏的服务器上创建一个具有相同模式的第二个表，然后逐个遍历 page_views 表中的每一行，并将它们插入到干净的表中，跳过磁盘块中有坏数据的行。要感谢这个 Stack Overflow 答案给了我这个策略的大致启发。

CREATE OR REPLACE PROCEDURE pg_recover_proc()
LANGUAGE plpgsql AS $$
DECLAREcnt BIGINT := 0;
BEGIN-- 从 page_views 表中获取最大的 page_view_idcnt := (SELECT MAX(page_view_id) FROM page_views);-- 按 page_view_id 降序遍历 page_views 表LOOPBEGIN-- 将当前 page_view_id 的行插入到 page_views_recovery 表中INSERT INTO page_views_recoverySELECT * FROM page_views WHERE page_view_id = cnt and entrypoint is not null;-- 递减计数器cnt := cnt - 1;-- 当 cnt < 1 时退出循环EXIT WHEN cnt < 1;EXCEPTIONWHEN OTHERS THEN-- 处理异常（例如数据损坏）IF POSITION('block' in SQLERRM) > 0 OR POSITION('status of transaction' in SQLERRM) > 0 OR POSITION('memory alloc' in SQLERRM) > 0 OR POSITION('data is corrupt' in SQLERRM) > 0 OR POSITION('MultiXactId' in SQLERRM) > 0 THENRAISE WARNING 'PGR_SKIP: %', cnt;cnt := cnt - 1;CONTINUE;ELSERAISE;END IF;END;IF MOD(cnt, 500) = 0 THENRAISE WARNING 'PGR_COMMIT: %', cnt;COMMIT;END IF;END LOOP;
END;
$$;

这里有一些巧妙但又很糟糕的做法。在现代版本的 Postgres 中，存储过程可以通过重复调用 COMMIT 来定期提交正在进行的顶级事务。我在这里（滥用）这个功能，以便在过程运行过程中，如果失败了，已经恢复的行能够被刷新到新表中。

我对与损坏数据相关的错误消息进行了一些粗略的字符串分析，如果是这种情况就跳过当前行。另一个有趣的边界情况：有几次，我遇到了向恢复表中插入数据失败的情况，因为对损坏表的 SELECT 查询返回了 null 值，尽管从技术上讲这是不可能的。我告诉过你我们在这里违反了 Postgres 的一些基本假设。在一个不同的非空列上添加 is not null 有助于避免这种情况。

我最初编写的这个过程是为了持续循环并跳过由磁盘损坏引起的致命错误（错误处理程序中的各种粗糙的 POSITION 检查）。

然而，很快我就遇到了一个新错误：

SQL Error [57P03]: FATAL: the database system is in recovery mode

原来，如果你一直故意迫使 Postgres 尝试从损坏的磁盘块中读取数据，最终它的内部数据结构会进入不一致状态，服务器进程会出于安全原因自动重启。

这显然是个问题，因为我们无法捕获这个情况并强制过程继续运行。所以我转而添加 IF 条件来手动跳过导致服务器进程崩溃的主键区域。（我告诉过你这很疯狂。）

每次服务器崩溃时，我都会导出到目前为止恢复的行，以防万一：

pg_dump -U postgres -h localhost --table page_views2 www_p1 > page_views2-1.sql

然后我会跳过一个新的主键区域，删除并重新创建恢复表，然后再试一次。为什么要删除并重新创建它呢？因为我发现当服务器进程崩溃时，它偶尔会向恢复表中写入坏数据，这显然是不行的：

pg_dump: error: 转储表“page_views_recovery”的内容失败：PQgetResult() 失败。
pg_dump: detail: 来自服务器的错误消息：ERROR: 无效的内存分配请求大小 18446744073709551613
pg_dump: detail: 命令为：COPY public.page_views_recovery (page_view_id, visited_at, hostname, ip, method, endpoint, user_id, xhr) TO stdout;

可以预见，手动做这些事情变得非常烦人，所以我做了任何一个优秀的 Linux 极客都会做的事情 —— 为它编写了一个脚本，你可以在这里找到它。要点如下：

./pg-recover.sh postgres localhost www_p1 page_views page_view_id entrypoint

在损坏的表中的 6,628,903 行数据中，我成功恢复了 6,444,118 行。正如人们所说 —— 如果它很愚蠢但却有效，那它仍然是愚蠢的，而你只是幸运罢了。