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

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

安装
clone代码，编译安装

[pg12@localhost contrib]$ git clone https://github.com/pramsey/pgsql-gzip.gitCloning 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 0Receiving 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]$ makegcc -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.Pogcc -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 DATABASEALTER DATABASE============== running regression test queries        ==============test gzip                         ... ok          253 ms===================== All 1 tests passed. =====================[pg12@localhost pgsql-gzip]$

体验
创建扩展

[local]:5432 pg12@testdb=# create extension gzippg12@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