postgresql二进制类型存取测试 -m6米乐安卓版下载

多米爸比 2020-03-04

2985

postgresql里存储二进制类型有两种：bytea类型或lo（large object）类型
bytea类型存储列要小于1gb
lo扩展类型可参考：
http://www.postgres.cn/docs/11/lo.html

创建表结构
postgres=# create table test_bytea(id int,info bytea);
生成1gb文件
postgres=# \! dd if=/dev/zero of=test_bytea bs=1k count=1048576 && sync
1048576 0 records in
1048576 0 records out
1073741824 bytes (1.1 gb) copied, 3.42339 s, 314 mb/s
文件入库
postgres=# insert into test_bytea values(1,pg_read_binary_file('/home/postgres/test_bytea'));
error:  requested length too large
生成文件稍小于1gb
postgres=# \! dd if=/dev/zero of=test_bytea bs=1k count=1048575 && sync
1048576 0 records in
1048576 0 records out
1073741824 bytes (1.1 gb) copied, 3.42339 s, 314 mb/s
入库测试
postgres=# insert into test_bytea values(1,pg_read_binary_file('/home/postgres/test_bytea'));
insert 0 1

public static void main(string[] args) throws exception{ 
		file file = new file("c:/work/movie.mkv");//文件大小不超过1g
		fileinputstream fis = new fileinputstream(file); 
		
		class.forname("org.postgresql.driver");
		connection con = drivermanager.getconnection("jdbc:postgresql://192.168.99.223:6000/postgres","xxx","xxx");
		preparedstatement ps = con.preparestatement("insert into test_bytea values (2,?)");
		
		ps.setbinarystream(1, fis, (long)file.length()); 
		ps.execute(); 
		con.close();
}

 	public static void main(string[] args) throws exception{ 
		class.forname("org.postgresql.driver");
		connection con = drivermanager.getconnection("jdbc:postgresql://192.168.99.223:6000/postgres","xxx","xxx");
		preparedstatement ps = con.preparestatement("select info from test_bytea where id=2;");
		
		resultset rs = ps.executequery();
         while (rs.next()) {
            outputstream ops = null;
            inputstream ips = null;
            file file = new file("c:/work/out.mkv");
            
            ips = rs.getbinarystream(1);
            byte[] buffer = new byte[1024*1024];
            ops = new fileoutputstream(file);
            for (int i; (i = ips.read(buffer)) > 0;)
            {
                 ops.write(buffer, 0, i);
                 ops.flush();
            }
            ops.close();
        }
        con.close();
	}

创建扩展及表结构
postgres=# create extension lo;
postgres=# create table test_lo(id int,info lo);
创建管理触发器
postgres=# create trigger test_lo before update or delete on test_lo for each row execute function lo_manage(info);
需要建立触发器使用lo_manage进行管理，以免形成孤立大对象
生成2g文件
postgres=# \! dd if=/dev/zero of=test_lo bs=1m count=2048 && sync
2048 0 records in
2048 0 records out
2147483648 bytes (2.1 gb) copied, 2.21557 s, 969 mb/s
文件入库
postgres=# insert into test_lo values(1,lo_import('/home/postgres/test_lo'));
导出lo文件
postgres=# select lo_export(test_lo.info,'/home/postgres/test_ext_lo') from test_lo where id=1;
 lo_export 
-----------
         1
(1 row)
postgres=# \! ls -lh test_ext_lo
-rw-r--r-- 1 postgres postgres 2.0g mar  3 18:22 test_ext_lo
lo类型原生表只存储一个oid作为引用指针，并不实际存数据
postgres=# \dt pg_largeobject*
                    list of relations
   schema   |          name           | type  |  owner   
------------ ------------------------- ------- ----------
 pg_catalog | pg_largeobject          | table | postgres
 pg_catalog | pg_largeobject_metadata | table | postgres
(2 rows)
pg_largeobject_metadata存储lo类型的元数据信息，包括类型的宿主和访问权限
postgres=# \d pg_largeobject_metadata
      table "pg_catalog.pg_largeobject_metadata"
  column  |   type    | collation | nullable | default 
---------- ----------- ----------- ---------- ---------
 oid      | oid       |           | not null | 
 lomowner | oid       |           | not null | 
 lomacl   | aclitem[] |           |          | 
indexes:
    "pg_largeobject_metadata_oid_index" unique, btree (oid)
pg_largeobject存储实际数据，最终使用byeta类型
postgres=# \d pg_largeobject
         table "pg_catalog.pg_largeobject"
 column |  type   | collation | nullable | default 
-------- --------- ----------- ---------- ---------
 loid   | oid     |           | not null | 
 pageno | integer |           | not null | 
 data   | bytea   |           | not null | 
indexes:
    "pg_largeobject_loid_pn_index" unique, btree (loid, pageno)