本文簡單介紹了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=#

```
壓縮/解壓
```
[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](https://github.com/pramsey/pgsql-gzip/)
[GZip in PostgreSQL](http://blog.cleverelephant.ca/2019/11/pgsql-gzip.html)

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

相關文章