PostgreSQL迁移遇TOAST表损坏与缺失块编号问题求最优方案

环境信息 #

• 版本：PostgreSQL 10.21，Visual C++ build 1800编译，64位
• 平台：Windows

问题概述 #

在从PostgreSQL 10.21迁移至14.7的dump过程中，触发表损坏报错：

ERROR: could not open file "base/16510/37857": No such file or directory

经查询relfilenode对应的relkind，确认损坏对象为关联主表prrhtab的TOAST表pg_toast_37722。

已尝试操作及问题 #

• 尝试截断TOAST表，报错：ERROR: "pg_toast_37722" is not a table
• 在base/16510目录下创建空文件37857后，触发新错误：

ERROR: missing chunk number 0 for toast value 15977662 in pg_toast_37722

关键SQL操作记录 #

ProdDB=# select count(*) from prrhtab;
 count
--------
 232966
(1 row)

ProdDB=# select * from prrhtab;
ERROR:  could not open file "base/16510/37857": No such file or directory
ProdDB=# select relname, relkind from pg_class where relfilenode=37857;
    relname     | relkind
----------------+---------
 pg_toast_37722 | t
(1 row)

ProdDB=# select * from pg_toast.pg_toast_37722;
ERROR:  could not open file "base/16510/37857": No such file or directory
ProdDB=# select count(*) from pg_toast.pg_toast_37722;
ERROR:  could not open file "base/16510/37857": No such file or directory

ProdDB=# truncate table pg_toast.pg_toast_37722;
ERROR:  "pg_toast_37722" is not a table

ProdDB=# select count(*) from pg_toast.pg_toast_37722;
 count
-------
     0
(1 row)

ProdDB=# select * from prrhtab;
ERROR:  could not read block 2635 in file "base/16510/37857": read only 0 of 8192 bytes
ProdDB=# reindex table prrhtab;
REINDEX
ProdDB=# select * from prrhtab;
ERROR:  missing chunk number 0 for toast value 15977662 in pg_toast_37722

ProdDB=# select count(*) from prrhtab;
 count
--------
 232966
(1 row)


ProdDB=# select * from prrhtab order by id desc limit 1;
    id   
---------
 1177027 
(1 row)

高效修复方案（无数据损失） #

方案1：利用TOAST表自动重建机制 #

1. 调整主表大字段存储模式
   先定位主表中使用TOAST存储的字段（通常是text、bytea等类型），通过修改存储模式触发TOAST表重建：

   -- 查看主表字段的存储模式
   SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'prrhtab'::regclass AND attnum > 0;
   
   -- 假设目标大字段为`content`，先改为不使用TOAST的plain模式
   ALTER TABLE prrhtab ALTER COLUMN content SET STORAGE plain;
   -- 再改回默认的extended模式（启用TOAST）
   ALTER TABLE prrhtab ALTER COLUMN content SET STORAGE extended;
   

   此操作会自动重建关联的TOAST表，修复物理文件丢失的问题，同时保留所有有效数据。

2. 验证修复结果
   执行SELECT * FROM prrhtab;和SELECT * FROM pg_toast.pg_toast_37722;确认无报错，再重新执行迁移dump操作。

方案2：批量导出有效数据重建表 #

若方案1无效，可通过游标分批处理数据，避免逐行删除的低效：

1. 创建临时表复制原表结构

   CREATE TABLE prrhtab_temp AS SELECT * FROM prrhtab WHERE 1=0;
   

2. 分批导出并插入数据

   BEGIN;
   DECLARE data_cur CURSOR FOR SELECT * FROM prrhtab;
   DECLARE rec RECORD;
   FETCH 1000 FROM data_cur INTO rec; -- 根据服务器内存调整批次大小
   WHILE FOUND LOOP
       INSERT INTO prrhtab_temp VALUES (rec.*);
       FETCH 1000 FROM data_cur INTO rec;
   END LOOP;
   COMMIT;
   

3. 替换原表并恢复约束

   DROP TABLE prrhtab;
   ALTER TABLE prrhtab_temp RENAME TO prrhtab;
   -- 重建原表的索引、外键、触发器等约束
   

问题成因分析 #

1. 文件系统层面损坏：Windows平台下，突然断电、磁盘IO错误、杀毒软件误删PostgreSQL数据目录文件，都可能导致TOAST表的物理文件丢失或损坏。
2. 版本迁移前未做预校验：直接执行dump操作时，旧版本PostgreSQL的TOAST表管理逻辑可能存在潜在bug，在高负载或特定数据场景下触发文件关联失效。
3. 元数据与物理文件不同步：主表与TOAST表的元数据关联记录异常，导致查询时无法定位到对应的TOAST物理文件。

预防措施 #

• 定期执行pg_checksums校验数据完整性，迁移前必须完成全量备份。
• 关闭Windows系统中针对PostgreSQL数据目录的实时杀毒扫描，避免误删核心文件。
• 迁移前先在测试环境完成版本升级验证，重点检查含大字段的表的TOAST状态。

内容的提问来源于stack exchange，提问作者rootcause000