合 PG中的大对象lo介绍和pg_largeobject表的清理
Tags： PGpg_largeobjectpg_largeobject_metadatavacuumlo大对象
👉 本文共约2141个字，系统预计阅读时间或需9分钟。
目录
简介
PostgreSQL对大对象有很多的存储和使用方式，较常见的比如bytea字段存储、Toast和lo方式等。
PostgreSQL具有大对象的功能，它提供了对于存储在一个特殊大对象结构中的用户数据的流式访问。对于那些大得无法以一个整体处理的数据值 ，流式访问非常有用。
所有的大对象都存在一个名为pg_largeobject的系统表中。每一个大对象还在系统表pg_largeobject_metadata中有一个对应的项。大对象可以通过类似于标准文件操作的读/写API来进行创建、修改和删除。
大对象源于large object的翻译，简称lo，与普通的整型、时间类型等不同，它实际存储一些文件、图片等较大尺寸的对象。
通常程序或者一些开源工具(比如开源的cas单点登录系统等)，存储大对象的方式是lo，通常lo的性能会比bytea要好一点，但在删除用户表时并不会将系统表(pg_largeobject和pg_largeobject_metadata)删除，或者创建类似的表时不会创建同步的trigger，所以需要DBA维护一下，否则会导致系统的大对象表越来越大。
PostgreSQL也支持一种称为“TOAST（存储大对象数据的一种技术）”的存储系统，每一个表都有专属的二级存储区域。这使得大对象功能显得有些陈旧。但是大对象功能仍然有一个优势是能够支持高达4TB的值，而TOAST域只能支持最大1GB。此外，读写一个大对象的片段会很高效，但是大部分在TOAST域上的操作都将把整个值作为一个单元进行读或写。
麦老师曾经维护的一套系统里，pg_largeobject达到了500GB的大小，直接把磁盘撑爆了，如下：
内置的lo系统函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | db1=# \df lo_* 函数列表 架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型 ------------+---------------+--------------+---------------------------+------ pg_catalog | lo_close | integer | integer | 函数 pg_catalog | lo_creat | oid | integer | 函数 pg_catalog | lo_create | oid | oid | 函数 pg_catalog | lo_export | integer | oid, text | 函数 pg_catalog | lo_from_bytea | oid | oid, bytea | 函数 pg_catalog | lo_get | bytea | oid | 函数 pg_catalog | lo_get | bytea | oid, bigint, integer | 函数 pg_catalog | lo_import | oid | text | 函数 pg_catalog | lo_import | oid | text, oid | 函数 pg_catalog | lo_lseek | integer | integer, integer, integer | 函数 pg_catalog | lo_lseek64 | bigint | integer, bigint, integer | 函数 pg_catalog | lo_open | integer | oid, integer | 函数 pg_catalog | lo_put | void | oid, bigint, bytea | 函数 pg_catalog | lo_tell | integer | integer | 函数 pg_catalog | lo_tell64 | bigint | integer | 函数 pg_catalog | lo_truncate | integer | integer, integer | 函数 pg_catalog | lo_truncate64 | integer | integer, bigint | 函数 pg_catalog | lo_unlink | integer | oid | 函数 (18 行记录) db1=# |
测试
1.创建大对象，导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | db_big=# select lo_create(1); lo_create ----------- 1 (1 row) -- 创建一个OID是1但没数据的大对象,该值被插入pg_largeobject_metadata,OID可以随便指定，但不能与大对象元数据表的OID有重复 db_big=# select oid,* from pg_largeobject_metadata; oid | lomowner | lomacl -----+----------+-------- 1 | 10 | (1 row) -- 存储实际对象数据的表是空的 db_big=# select count(1) from pg_largeobject; count ------- 0 (1 row) db_big=# create table tab_big(vname text,souroid oid); CREATE TABLE db_big=# insert into tab_big values('passwd list',lo_import('/etc/passwd')); INSERT 0 1 db_big=# select * from tab_big ; vname | souroid -------------+--------- passwd list | 20063 (1 row) db_big=# select oid,* from pg_largeobject_metadata ; oid | lomowner | lomacl -------+----------+-------- 1 | 10 | 20063 | 10 | (2 rows) -- 实际存储的数据 db_big=# select * from pg_largeobject; loid | pageno | data ------------------------------------------------------------------------------------------------- 20063 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a -- 指定oid存储 db_big=# insert into tab_big values('passwd list',lo_import('/etc/passwd',123)); INSERT 0 1 db_big=# select oid,* from pg_largeobject_metadata ; oid | lomowner | lomacl -------+----------+-------- 1 | 10 | 20063 | 10 | 123 | 10 | db_big=# select * from pg_largeobject; loid | pageno | data 20063 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a 123 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a (2 rows) |
2.实际的存储
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | --大对象数据表，pageno是一个大对象在该表里实际存储的条数，每条大小是block_size，默认是8kb db_big=# show block_size ; block_size ------------ 8192 (1 row) db_big=# \d+ pg_largeobject Table "pg_catalog.pg_largeobject" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- loid | oid | not null | plain | | pageno | integer | not null | plain | | data | bytea | | extended | | Indexes: "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno) Has OIDs: no --大对象元数据表 db_big=# \d+ pg_largeobject_metadata，隐藏的oid是唯一约束 Table "pg_catalog.pg_largeobject_metadata" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------+-----------+----------+--------------+------------- lomowner | oid | not null | plain | | lomacl | aclitem[] | | extended | | Indexes: "pg_largeobject_metadata_oid_index" UNIQUE, btree (oid) Has OIDs: yes (1 row) |
3.大对象的导出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | db_big=# select lo_export(123,'/home/pwd.list'); ERROR: could not create server file "/home/pwd.list": Permission denied db_big=# select lo_export(123,'/home/postgres/pwd.list'); lo_export ----------- 1 (1 row) --返回1表示成功 [postgres@db1 ~]$ more /home/postgres/pwd.list root:x:0:0:root:/root:/bin/bash bin:x:1:1:bin:/bin:/sbin/nologin daemon:x:2:2:daemon:/sbin:/sbin/nologin adm:x:3:4:adm:/var/adm:/sbin/nologin lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin sync:x:5:0:sync:/sbin:/bin/sync ...... |
4.大对象的删除
自带有一个lo_unlink函数来清理大对象数据,类似于普通表的delete命令
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | db_big=# select lo_unlink(123); lo_unlink ----------- 1 (1 row) db_big=# select lo_unlink(1); lo_unlink ----------- 1 (1 row) db_big=# select oid,* from pg_largeobject_metadata ; oid | lomowner | lomacl -------+----------+-------- 20063 | 10 | (1 row) db_big=# select * from pg_largeobject; loid | pageno | data 20063 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a (1 row) -- 批量删除 db_big=# insert into tab_big values('passwd list',lo_import('/home/postgres/pg_log/postgresql-2014-01-09_000000.csv',888)); INSERT 0 1 db_big=# select lo_unlink(oid) from pg_largeobject_metadata where oid in(20063,888) ; lo_unlink ----------- 1 1 (2 rows) |
5.客户端的使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | db_big=# \lo_list Large objects ID | Owner | Description -----+----------+------------- 888 | postgres | (1 row) db_big=# \lo_export 888 '/home/postgres/123.txt' lo_export db_big=# \lo_import '/home/postgres/123.txt' 'Kenyon_bigdata' lo_import 20066 db_big=# \lo_list Large objects ID | Owner | Description -------+----------+---------------- 888 | postgres | 20066 | postgres | Kenyon_bigdata (2 rows) db_big=# \lo_unlink 888 lo_unlink 888 |
6.权限
a.超级用户使用以上命令都是正常的
b.导出时数据库用户需要有相关的创建文件的权限
大对象内容查询
1、可以使用函数做导出操作：select lo_export(123,'/home/postgres/data.dat');
2、可以使用工具DBeaver查询，会显示出data的具体存储内容，将data列直接拷贝到txt中即可查看所有内容：
1 | select data from pg_largeobject a where a.loid=26506030 ; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
