cas=# select oid,rolname from pg_authid where oid in (10,327299); oid | rolname -----+---------- 10 | postgres327299| usr_cas(1 row)cas=# select lomowner,count(1) from pg_largeobject_metadata group by 1; lomowner | count ----------+-------- 10 | 292408 327299 | 382123(2 row)二、清理 需要清理两部分，postgres用户的大对象与usr_cas用户的大对象，前者是用postgres连接时创建的，需要全部删除，后者存在部分用户数据已删但大对象没删的数据，也需要删除。 1.lo_unlink删除 删除通常使用自带的lo_unlink()函数，于是使用了以下命令，但爆出问题 out of shared memory
cas=# select lo_unlink(oid) from pg_largeobject_metadata where lomowner = 10;WARNING: out of shared memoryERROR: out of shared memoryHINT: You might need to increase max_locks_per_transaction.cas=# show max_locks_per_transaction ; max_locks_per_transaction --------------------------- 64(1 row)这个提示比较明显，一个SQL把所有的大对象在一个事务里完成，但分配的内存不够，所以失败了，要增加max_locks_per_transaction参数值，这个值默认是64。其实也可以换个角度删除，不把所有的大对象在一个事务里删除，而是分批次执行，因为要删除的数据量其实也不算多，就考虑了后者。
--多执行以下命令几次就可以了，每次删2W，执行10几次就够了，也可以放脚本里写，一次执行cas=# select lo_unlink(oid) from pg_largeobject_metadata where lomowner = 10 limit 20000;2.vacuumlo删除 清理完postgres的用户数据以后，接着要清理usr_cas用户的大对象数据，要写脚本逐个比对比较麻烦，而且效率也不一定好。这可以使用自带的vacuumlo的小工具。这个工具是通过大对象的OID与用户表中的oid进行关联比对，然后逐一删除，所以在设计大对象用户表时，虽然也可以使用int类型存储oid值，但是对后期的维护不方便，推荐使用oid类型。 如果这个工具没有安装，可以在contrib/vacuumlo下面make && make install安装一下即可 简介如下：
[postgres@kenyon-primary ~]$ vacuumlo --helpvacuumlo removes unreferenced large objects from databases.Usage: vacuumlo [OPTION]... DBNAME...Options: -l LIMIT commit after removing each LIMIT large objects -n don't remove large objects, just show what would be done -v write a lot of progress messages -V, --version output version information, then exit -?, --help show this help, then exitConnection options: -h HOSTNAME database server host or socket directory -p PORT database server port -U USERNAME user name to connect as -w never prompt for password -W force password promptReport bugs to .使用:
--显示要清理的数据，不清理，只显示[postgres@kenyon-primary ~]$ vacuumlo -n cas -vConnected to database "cas"Test run: no large objects will be removed!Checking expiration_policy in public.serviceticketChecking service in public.serviceticketChecking expiration_policy in public.ticketgrantingticketChecking authentication in public.ticketgrantingticketChecking services_granted_access_to in public.ticketgrantingticketWould remove 382143 large objects from database "cas".--清理，可以加个“l”参数，每隔这个参数提交一次[postgres@kenyon-primary ~]$ vacuumlo cas -v -l 1000Connected to database "cas"Test run: no large objects will be removed!Checking expiration_policy in public.serviceticketChecking service in public.serviceticketChecking expiration_policy in public.ticketgrantingticketChecking authentication in public.ticketgrantingticketChecking services_granted_access_to in public.ticketgrantingticketWould remove 382143 large objects from database "cas".清理完毕再看一下
cas=# select pg_size_pretty(pg_database_size('cas')); pg_size_pretty ---------------- 1.3 GB(1 row)--空间还没有收缩，使用vacuum full analyzecas=# vacuum full analyze verbose pg_largeobject;INFO: vacuuming "pg_catalog.pg_largeobject"INFO: scanned index "pg_largeobject_loid_pn_index" to remove 88928 row versionsDETAIL: CPU 0.01s/0.24u sec elapsed 0.26 sec.INFO: "pg_largeobject": removed 88928 row versions in 6833 pagesDETAIL: CPU 0.00s/0.02u sec elapsed 0.02 sec.INFO: index "pg_largeobject_loid_pn_index" now contains 948117 row versions in 4120 pagesDETAIL: 88928 index row versions were removed.1516 index pages have been deleted, 1269 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: "pg_largeobject": found 88928 removable, 52 nonremovable row versions in 6891 out of 109226 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 2329 unused item pointers.0 pages are entirely empty.CPU 0.03s/0.32u sec elapsed 0.35 sec.INFO: analyzing "pg_catalog.pg_largeobject"INFO: "pg_largeobject": scanned 30000 of 109226 pages, containing 260529 live rows and 0 dead rows; 30000 rows in sample, 947568 estimated total rowsVACUUMcas=# select pg_size_pretty(pg_relation_size('pg_largeobject'));pg_size_pretty----------------8192 KB(1 row) 整个世界清静了。 写成脚本的方式，定期执行 [postgres@kenyon-primary ~]$ more cas_rm_lo.sh#!/bin/bash########################################################## purpose:Rm the cas's large object and free space## ## author :Kenyon## ## created:2014-01-22## #####################################################source /home/postgres/.bash_profilevacuumlo cas -l 1000 -vpsql -d cas -c "vacuum full analyze verbose pg_largeobject;"psql -d cas -c "vacuum full analyze verbose pg_largeobject_metadata;"三、总结 在使用开源的一些工具时，如果有使用一些大对象，需要注意一下程序清理用户数据时是否会同步删除大对象数据。