PostgreSQL的大对象使用

最新推荐文章于 2024-03-12 14:23:37 发布

weixin_33719619

最新推荐文章于 2024-03-12 14:23:37 发布

阅读量1k

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/Kenyon/blog/194530

版权

为什么80%的码农都做不了架构师？>>>

PostgreSQL对大对象有很多的存储和使用方式，较常见的比如bytea字段存储、Toast和lo方式等。这里整理介绍一下lo方式存储和使用的常见场景。

一、介绍
大对象源于large object的翻译，简称lo，与普通的整型、时间类型等不同，它实际存储一些文件、图片等较大尺寸的对象。

内置的lo系统函数

db_big=# \df lo_*
                                 List of functions
   Schema   |     Name      | Result data type |    Argument data types    |  Type 
------------+---------------+------------------+---------------------------+--------
pg_catalog | lo_close      | integer          | integer                   | normal
pg_catalog | lo_creat      | oid              | integer                   | normal
pg_catalog | lo_create     | oid              | oid                       | normal
pg_catalog | lo_export     | integer          | oid, text                 | normal
pg_catalog | lo_import     | oid              | text                      | normal
pg_catalog | lo_import     | oid              | text, oid                 | normal
pg_catalog | lo_lseek      | integer          | integer, integer, integer | normal
pg_catalog | lo_lseek64    | bigint           | integer, bigint, integer  | normal
pg_catalog | lo_open       | integer          | oid, integer              | normal
pg_catalog | lo_tell       | integer          | integer                   | normal
pg_catalog | lo_tell64     | bigint           | integer                   | normal
pg_catalog | lo_truncate   | integer          | integer, integer          | normal
pg_catalog | lo_truncate64 | integer          | integer, bigint           | normal
pg_catalog | lo_unlink     | integer          | oid                       | normal
(14 rows)

二、使用
1.创建大对象，导入数据

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.实际的存储

--大对象数据表，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.大对象的导出

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命令

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.客户端的使用

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.导出时数据库用户需要有相关的创建文件的权限

三.其他

通常程序或者一些开源工具(比如cas等)在删除用户表时并不会将系统表(pg_largeobject和pg_largeobject_metadata)删除，或者创建类似的表时不会创建同步的trigger，需要DBA维护一下，否则会导致系统的大对象表越来越大。

四.参考
1.http://www.postgresql.org/docs/9.3/static/catalog-pg-largeobject.html
2.http://giswiki.hsr.ch/PostgreSQL_-_Binary_Large_Objects
3.http://my.oschina.net/Kenyon/blog/113026

转载于:https://my.oschina.net/Kenyon/blog/194530