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

本文簡(jiǎn)單介紹了PostgreSQL的插件：pgsql-gzip。該插件可用于對(duì)列進(jìn)行加密和解密。

安裝
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]$

體驗(yàn)
創(chuàng)建擴(kuò)展

[local]:5432 pg12@testdb=# create extension gzip
pg12@testdb-# ;
CREATE EXTENSION
[local]:5432 pg12@testdb=#

主要的函數(shù)包括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('測(cè)試數(shù)據(jù)123測(cè)試');
                                       gzip                                       
----------------------------------------------------------------------------------
 \x1f8b08000000000000037bb6b5fbc5faa9cfa66e78d6bbced0c8f819980b00299ee5af15000000
(1 row)
[local]:5432 pg12@testdb=# select gunzip('\x1f8b08000000000000037bb6b5fbc5faa9cfa66e78d6bbced0c8f819980b00299ee5af15000000'::bytea);
                    gunzip                    
----------------------------------------------
 \xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=# select gunzip(gzip('測(cè)試數(shù)據(jù)123測(cè)試'));
                    gunzip                    
----------------------------------------------
 \xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select '測(cè)試數(shù)據(jù)123測(cè)試'::bytea;
                    bytea                     
----------------------------------------------
 \xe6b58be8af95e695b0e68dae313233e6b58be8af95
(1 row)
[local]:5432 pg12@testdb=#

通過pgsql-gzip可實(shí)現(xiàn)非透明的壓縮和解壓。

參考資料
pgsql-gzip
GZip in PostgreSQL