postgresql - vacuumlo 不回收未使用的 LOB 占用的空间

Question

我有一个存储一些图像的表。最近我不得不运行一些测试，我不得不删除所述表的所有行然后再次插入新行，所以我不得不多次导入相同的图像。完成后，我注意到它占用了我驱动器中的大量空间，因此我尝试运行 vacuumlo 来回收这个空间，但没有任何反应。程序完成运行，没有错误，并显示一条消息

"0 objects found"

或类似的东西。我做错了什么？

Answer 1

docs say :

vacuumlo is a simple utility program that will remove any "orphaned" large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.

您可以手动检查孤儿，将 pg_largeobject 与您所有的 lo 表连接起来。例如:让我们创建几个 lo 表并用数据填充它们:

b=# create table l_o (n text, o oid);
CREATE TABLE
b=# create table lo (n text, p oid);
CREATE TABLE
b=# insert into l_o values('one',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1
b=# insert into l_o values('two',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1
b=#  insert into l_o values('three',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1
b=#  insert into lo values('one',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1
b=#  insert into l_o values('two',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1

现在检查孤儿:

b=# with l_o as (select o,'l_o' tname from l_o union all select p,'lo' from lo)
b-# select distinct loid, o, tname
b-# from pg_largeobject left outer join l_o on l_o.o = loid;
 loid  |   o   | tname
-------+-------+-------
 34530 | 34530 | lo
 34528 | 34528 | l_o
 34527 | 34527 | l_o
 34529 | 34529 | l_o
 34531 | 34531 | l_o
(5 rows)

没有。现在使用 vacuumlo:

b=# \! vacuumlo -v -n b
Connected to database "b"
Test run: no large objects will be removed!
Checking o in public.l_o
Checking p in public.lo
Would remove 0 large objects from database "b".

相同。现在重现您的案例 - 在 table 上放下、重新创建和重新填充一个案例:

b=# drop table lo;
DROP TABLE
b=# create table lo (n text, p oid);
CREATE TABLE
b=# insert into lo values('one',lo_import ('/tmp/wheel.PNG'));
INSERT 0 1

试运行:

b=# \! vacuumlo -v -n b
Connected to database "b"
Test run: no large objects will be removed!
Checking o in public.l_o
Checking p in public.lo
Would remove 1 large objects from database "b".

检查pg_largeobject:

b=# with l_o as (select o,'l_o' tname from l_o union all select p,'lo' from lo)
select distinct loid, o, tname
from pg_largeobject left outer join l_o on l_o.o = loid;
 loid  |   o   | tname
-------+-------+-------
 34528 | 34528 | l_o
 34531 | 34531 | l_o
 34550 | 34550 | lo
 34527 | 34527 | l_o
 34529 | 34529 | l_o
 34530 |       |
(6 rows)

是的 - 一个孤儿(“lo”表有一排，掉落和重新填充成为一排孤儿)

运行 vacuumlo 来修复它:

b=# \! vacuumlo -v b
Connected to database "b"
Checking o in public.l_o
Checking p in public.lo
Successfully removed 1 large objects from database "b".

检查结果:

b=# with l_o as (select o,'l_o' tname from l_o union all select p,'lo' from lo)
select distinct loid, o, tname
from pg_largeobject left outer join l_o on l_o.o = loid;
 loid  |   o   | tname
-------+-------+-------
 34528 | 34528 | l_o
 34531 | 34531 | l_o
 34550 | 34550 | lo
 34527 | 34527 | l_o
 34529 | 34529 | l_o
(5 rows)

是的 - 一切顺利。现在用 vacuumlo 检查:

b=# \! vacuumlo -v -n b
Connected to database "b"
Test run: no large objects will be removed!
Checking o in public.l_o
Checking p in public.lo
Would remove 0 large objects from database "b".