PostgreSQL 备份链路sslcompression压缩 (openssl)
背景
通过链路压缩，提高窄带网络PostgreSQL数据库的备份性能。
需要用到PostgreSQL的SSL支持，用法请参考
《PostgreSQL 如何实现网络压缩传输或加密传输(openssl)》
流复制协议，pg_dump都支持ssl，因为它们都走libpq的调用，libpq是支持ssl的。
http://www.postgresql.org/docs/9.3/static/libpq-envars.html
PGSSLCOMPRESSION behaves the same as the sslcompression connection parameter.
http://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNECT-SSLCOMPRESSION
sslcompression
If set to 1 (default), data sent over SSL connections will be compressed (this requires OpenSSL version 0.9.8 or later). If set to 0, compression will be disabled (this requires OpenSSL 1.0.0 or later). This parameter is ignored if a connection without SSL is made, or if the version of OpenSSL used does not support it.
Compression uses CPU time, but can improve throughput if the network is the bottleneck. Disabling compression can improve response time and throughput if CPU performance is the limiting factor.
注意如果你用的是linux，可能会遇到LINUX的一个BUG，使用时，数据库服务端和客户端都必须先设置以下环境变量
export OPENSSL_DEFAULT_ZLIB=1
然后启动数据库，和客户端。
psql postgresql://xxx.xxx.xxx.xxx:1921/postgres?user=postgres\&sslcompression=1\&application_name=myapp\&password=postgres\&sslmode=require
psql (9.5.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.
postgres=# select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+-----------------------------+------+-------------+----------
9091 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | t |
9106 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | t |
(2 rows)
逻辑备份使用ssl压缩
pg_dump postgresql://xxx.xxx.xxx.xxx:1921/postgres?user=postgres\&sslcompression=1\&application_name=myapp\&password=postgres\&sslmode=require -F c -f ./test.dmp
流复制使用SSL压缩
primary_conninfo = 'host=xxx.xxx.xxx.xxx port=xxxx user=xxx password=xxx sslmode=require sslcompression=1'