PostgreSQL LOB迁移后pg_largeobject为空的原因排查

我们有一个基于Spring Boot的应用，使用PostgreSQL数据库，原本将部分数据映射为CLOB和BLOB类型。为弃用大对象（LO），我们执行了数据库迁移，同时创建备份表以防出错（迁移Liquibase脚本见下文）。迁移完成后删除了所有备份表，后端启动后运行正常，但执行vacuumlo工具时显示无大对象可清理，查看pg_catalog.pg_largeobject和pg_largeobject_metadata发现两者已完全为空。原本以为需要先解除大对象关联或手动删除，再用vacuumlo清理，现在疑惑操作是否有误，以及pg_largeobject是何时被清空的？

迁移Liquibase脚本 #

<changeSet id="240123909061" author="zero" dbms="postgresql">
    <sql>
      CREATE TABLE data_files_backup (LIKE data_files );
      INSERT INTO data_files_backup SELECT * FROM data_files ;
    </sql>
  </changeSet>

  <changeSet id="240123909062" author="zero" dbms="postgresql">
    <addColumn tableName="data_files">
      <column name="context_new" type="BYTEA"/>
    </addColumn>
  </changeSet>

  <changeSet id="240123909063" author="zero" dbms="postgresql">
    <sql>
      UPDATE data_files SET context_new = lo_get(data_files.context::BIGINT) where data_files.context is not null;
    </sql>
  </changeSet>

  <changeSet id="240123909064" author="zero"  dbms="postgresql">
    <dropColumn tableName="data_files" columnName="context"/>
  </changeSet>

  <changeSet id="tm240909065" author="zero"  dbms="postgresql">
    <renameColumn tableName="data_files" oldColumnName="context_new" newColumnName="context"/>
  </changeSet>

删除备份表的Liquibase脚本 #

<changeSet id="migration-backup-delete" author="me me" dbms="postgresql">
    <sql>
      DROP TABLE IF EXISTS data_files_backup;
     ..... 
     other tables
     .....
    </sql>
  </changeSet>
</databaseChangeLog>

vacuumlo执行结果 #

$ vacuumlo -n -U MYNAME -p 5438 CENTRALDB
Password: 
Connected to database "CENTRALDB"
Test run: no large objects will be removed!
Would remove 0 large objects from database "CENTRALDB".

解答 #

你的操作没有问题，pg_largeobject被清空的时机是删除备份表之后，核心原因是PostgreSQL的大对象引用机制和自动清理逻辑：

1. 大对象的引用计数机制：PostgreSQL的大对象依赖引用关系存在——只要数据库中任何表的字段持有大对象的OID，该大对象就会被保留。只有当所有指向它的OID引用都被移除后，大对象才会被标记为"孤立"，等待清理。

2. 迁移流程中的引用变化：

   • 备份表创建后，data_files_backup和原表data_files都持有大对象的OID引用，此时大对象的引用计数至少为2。
   • 迁移时将大对象数据读取到BYTEA字段并删除原表的context字段后，备份表仍然持有唯一的OID引用，因此大对象不会被标记为孤立。
   • 执行DROP TABLE data_files_backup后，所有指向大对象的引用被彻底清除，PostgreSQL的自动清理（autovacuum）进程会立即或在定期扫描时识别到这些孤立大对象，并将其从pg_largeobject和pg_largeobject_metadata中删除。

3. vacuumlo的作用逻辑：vacuumlo的功能是扫描数据库中所有表的字段，找出没有任何引用的孤立大对象并删除。但在你运行vacuumlo之前，autovacuum已经完成了清理工作，因此工具显示无对象可清理是正常结果。

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