PostgreSQL导入异常：大表显示0行却占用大量磁盘空间

兄弟，我来帮你捋捋这个问题——你的test表在从Postgres 9.6迁移到11之后，出现了显示0行但占了近4.5GB磁盘空间的怪事，结合你给的操作步骤和表统计数据，我分析了可能的原因，还有不用重新全量导入的修复办法：

从新旧表的统计数据对比就能看出关键：

• 原表：有524万行数据，表本身占4.8GB，索引占13GB
• 新表：行估计为0，表仅16kB，但索引占了4.5GB

这说明数据行根本没导入成功，但索引的构建过程已经跑了一部分，或者导入时数据被回滚但索引的磁盘空间没被自动释放。结合你的操作流程（先导结构再导数据），大概率是这几个原因：

1. 导入时的错误被静默跳过：psql默认遇到约束冲突（比如主键重复、外键关联失败）只会报错但不终止整个导入，如果test表的大部分数据都触发了错误，最后就会出现表空但索引占空间的情况。
2. 跨版本兼容性问题：Postgres 9.6到11有一些内部存储格式的变化，比如某些数据类型、表结构的细微差异，导致数据行无法被正确解析，但索引构建仍在执行。
3. 导入过程意外中断：如果数据导入时突然断网、服务器重启，PostgreSQL会回滚数据插入，但已经建好的索引碎片可能没被清理，占着磁盘空间。

我们只针对test表单独处理就行，步骤如下：

第一步：先确认真实状态 #

先跑几个SQL命令，确认表真的没数据，以及索引的具体情况：

-- 查实际行数（比row_estimate准确）
SELECT COUNT(*) FROM public.test;

-- 列出test表的所有索引
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'test' AND schemaname = 'public';

-- 查看索引和表的大小、状态
SELECT relname, pg_size_pretty(pg_relation_size(relname::regclass)) AS size, relkind
FROM pg_class 
WHERE relname IN (SELECT indexname FROM pg_indexes WHERE tablename = 'test')
UNION ALL
SELECT relname, pg_size_pretty(pg_relation_size(relname::regclass)) AS size, relkind
FROM pg_class WHERE relname = 'test';

第二步：清理无效索引，单独导入test表数据 #

1. 在生产环境（9.6）单独导出test表的数据 #

只导数据不导结构，这样文件小，传输快：

/usr/bin/pg_dump -t public.test --data-only mydb | /bin/gzip | /usr/bin/ssh root@1.2.3.4 "cat > /root/test_data.sql.gz"

2. 在测试环境（11）清理无效资源 #

先把那些占空间的无效索引删掉：

-- 批量删除test表的所有索引（不用一个个手动写）
DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT indexname FROM pg_indexes WHERE tablename = 'test' AND schemaname = 'public'
  LOOP
    EXECUTE 'DROP INDEX IF EXISTS public.' || quote_ident(r.indexname);
  END LOOP;
END$$;

然后清理表的磁盘空间（确保释放残留的碎片）：

VACUUM FULL public.test;

3. 导入test表的数据 #

现在导入单独导出的数据，加--set ON_ERROR_STOP=1是为了如果再遇到错误，直接终止，方便排查问题：

time zcat /root/test_data.sql.gz | psql --set ON_ERROR_STOP=1 mydb

4. 重新创建索引 #

刚才我们把索引删了，需要从生产环境导出索引定义再创建：

-- 生产环境导出test表的索引创建语句
/usr/bin/pg_dump -t public.test --schema-only mydb | grep -E 'CREATE INDEX' | /usr/bin/ssh root@1.2.3.4 "cat > /root/test_indexes.sql"

-- 测试环境执行创建索引
psql mydb < /root/test_indexes.sql

第三步：验证修复结果 #

跑下面的命令确认数据和空间都正常了：

-- 确认行数
SELECT COUNT(*) FROM public.test;

-- 查看表的统计信息
SELECT 
  table_schema, 
  table_name, 
  row_estimate, 
  pg_size_pretty(total) AS total, 
  pg_size_pretty(index) AS index, 
  pg_size_pretty(toast) AS toast, 
  pg_size_pretty(table) AS table
FROM pg_stat_user_tables WHERE table_name = 'test';

为了避免再踩坑，给你两个建议：

1. 用pg_dump的自定义格式（-Fc）导出，然后用pg_restore恢复，跨版本兼容性更好，还支持并行恢复，速度更快：

-- 生产环境导出
/usr/bin/pg_dump -Fc mydb | /bin/gzip | /usr/bin/ssh root@1.2.3.4 "cat > /root/20210130.dump.gz"

-- 测试环境导入（-j 4是用4个并行进程，根据服务器CPU调整）
time zcat /root/20210130.dump.gz | pg_restore -d mydb -j 4

2. 任何时候用psql导入数据，都要加--set ON_ERROR_STOP=1，遇到错误直接终止，不会让你等到最后才发现数据没导进去。

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