PostgreSQL DBA(130) - Extension(pgsql-gzip)

本文简单介绍了PostgreSQL的插件：pgsql-gzip。该插件可用于对列进行加密和解密。

安装
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