pg_dump无bytea列时加-B与不加-B备份差异原因咨询

背景信息 #

近期我们将PostgreSQL数据库从一家云服务商迁移至另一家，使用pg_dump执行备份，带-B选项的命令如下：

pg_dump -F t -h HOST -U USER -d DB -f C:\Users\Shariq\Downloads\Prod-DB\noblobswithschema.tar -Z0 -v -B

该命令仅耗时约10分钟生成备份文件，且通过pg_restore恢复无异常。

补充细节 #

• 数据库总大小（通过pg_database_size查询）为128GB，但所有表总大小仅约2GB，符合实际数据量；执行常规VACUUM后数据库大小无变化，未执行VACUUM FULL。
• 数据库中无bytea类型列，仅存在text类型列，不确定text是否属于大对象。

异常现象 #

未添加-B选项时，pg_dump备份耗时极久，备份文件大小甚至达到300GB；尝试使用-j选项的目录式备份也同样耗时很久。但使用-B选项时，默认text类型不属于大对象，且恢复后所有数据（含text列）均正常。

原因分析 #

1. text类型不属于PostgreSQL的大对象（LOB） #

PostgreSQL里的大对象特指用lo_create这类接口创建的独立存储的大型对象（对应系统表pg_largeobject），而text、bytea类型的数据是直接存在表行里（或者行外的TOAST存储，但不属于大对象范畴）。-B选项只是排除pg_largeobject里的数据，和text、bytea完全无关，这也是你用-B后text数据能正常恢复的原因。

2. 不加-B时备份异常的核心：数据库存了大量废弃大对象 #

虽然你的业务表没用到大对象，但数据库的pg_largeobject系统表极可能堆了大量遗留的废弃大对象——大概率是之前业务逻辑用过大对象但没清理干净，或是某些第三方工具、扩展留下的垃圾数据。

当pg_dump不带-B时，会遍历并备份pg_largeobject里的所有数据。如果这个表有几百GB的废弃数据，自然会让备份耗时暴增、文件体积异常大。

3. 数据库总大小128GB但表仅2GB的关联解释 #

pg_database_size统计的是整个数据库的磁盘占用，包含系统表、索引、TOAST数据、大对象等。你的业务表仅占2GB，但pg_largeobject占了剩下的126GB左右。常规VACUUM管不了大对象的废弃数据（它主要处理表行的死元组），大对象的清理得手动调用lo_unlink或者用vacuumlo工具，所以你跑了常规VACUUM后数据库大小没变化。

验证与解决建议 #

• 验证废弃大对象的存在：执行以下SQL查询大对象的数量和总大小：

  SELECT count(*) AS lob_count, pg_size_pretty(sum(pg_relation_size(loid::regclass))) AS lob_total_size
  FROM pg_largeobject_metadata;
  

• 清理废弃大对象：用PostgreSQL官方的vacuumlo工具，它会自动清理没有被任何表引用的大对象，命令如下：

  vacuumlo -h HOST -U USER DB
  

  清理完再跑不带-B的pg_dump，备份速度和文件大小应该就能恢复正常。

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