PostgreSQL二进制类型存取测试

原创多米爸比 2020-03-04

4368

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

byte类型测试

创建表结构
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

JDBC-Java文件入库

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();
}

JDBC-Java读数据输出到文件

 	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();
	}

lo类型测试

创建扩展及表结构
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)