本文简单介绍了PostgreSQL的插件：pgsql-gzip。该插件可用于对列进行加密和解密。
创新互联公司2013年开创至今，是专业互联网技术服务公司，拥有项目成都网站建设、网站制作网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元綦江做网站,已为上家服务,为綦江各地企业和个人服务,联系电话:18980820575
安装
clone代码，编译安装
[pg12@localhost contrib]$ git clone https://github.com/pramsey/pgsql-gzip.git
Cloning into 'pgsql-gzip'...
remote: Enumerating objects: 114, done.
remote: Counting objects: 100% (114/114), done.
remote: Compressing objects: 100% (71/71), done.
remote: Total 114 (delta 63), reused 81 (delta 35), pack-reused 0
Receiving objects: 100% (114/114), 18.56 KiB | 0 bytes/s, done.
Resolving deltas: 100% (63/63), done.
[pg12@localhost contrib]$ cd pgsql-gzip/
[pg12@localhost pgsql-gzip]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/pg12/pg12.0/include/postgresql/server -I/appdb/pg12/pg12.0/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_gzip.o pg_gzip.c -MMD -MP -MF .deps/pg_gzip.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o gzip.so pg_gzip.o -L/appdb/pg12/pg12.0/lib -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.0/lib',--enable-new-dtags -lz
[pg12@localhost pgsql-gzip]$ make install
/bin/mkdir -p '/appdb/pg12/pg12.0/lib/postgresql'
/bin/mkdir -p '/appdb/pg12/pg12.0/share/postgresql/extension'
/bin/mkdir -p '/appdb/pg12/pg12.0/share/postgresql/extension'
/bin/install -c -m 755 gzip.so '/appdb/pg12/pg12.0/lib/postgresql/gzip.so'
/bin/install -c -m 644 .//gzip.control '/appdb/pg12/pg12.0/share/postgresql/extension/'
/bin/install -c -m 644 .//gzip--1.0.sql '/appdb/pg12/pg12.0/share/postgresql/extension/'
[pg12@localhost pgsql-gzip]$ make installcheck
/appdb/pg12/pg12.0/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/appdb/pg12/pg12.0/bin' --dbname=contrib_regression gzip
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test gzip ... ok 253 ms
=====================
All 1 tests passed.
=====================
[pg12@localhost pgsql-gzip]$
体验
创建扩展
[local]:5432 pg12@testdb=# create extension gzip
pg12@testdb-# ;
CREATE EXTENSION
[local]:5432 pg12@testdb=#
主要的函数包括gzip和gunzip
[local]:5432 pg12@testdb=# \df gzip
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------------------------------------------------------+------
public | gzip | bytea | uncompressed bytea, compression_level integer DEFAULT '-1'::integer | func
public | gzip | bytea | uncompressed text, compression_level integer DEFAULT '-1'::integer | func
(2 rows)
[local]:5432 pg12@testdb=# \df gunzip
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
public | gunzip | bytea | compressed bytea | func
(1 row)
压缩/解压
[local]:5432 pg12@testdb=# select gzip('测试数据123测试');
gzip
----------------------------------------------------------------------------------
\x1f8b08000000000000037bb6b5fbc5faa9cfa66e78d6bbced0c8f819980b00299ee5af15000000
(1 row)
[local]:5432 pg12@testdb=# select gunzip('\x1f8b08000000000000037bb6b5fbc5faa9cfa66e78d6bbced0c8f819980b00299ee5af15000000'::bytea);
gunzip
----------------------------------------------
\xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=# select gunzip(gzip('测试数据123测试'));
gunzip
----------------------------------------------
\xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# select '测试数据123测试'::bytea;
bytea
----------------------------------------------
\xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=#
通过pgsql-gzip可实现非透明的压缩和解压。
参考资料
pgsql-gzip
GZip in PostgreSQL
