穷鬼玩PolarDB RAC系列 | 读写分离
digoal约 13 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
穷鬼玩PolarDB RAC系列 | 读写分离
系列请戳: 🏆《穷鬼玩PolarDB RAC系列》🏆
本篇文章介绍一下如何进行实时归档? 实验环境依赖《在Docker容器中用loop设备模拟共享存储》 , 如果没有环境, 请自行参考以上文章搭建环境.
还需要参考如下文档:
《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.6 PolarDB开源版本必学特性 - 安装与使用PostgreSQL开源插件/工具》
DEMO
下面使用实时归档机pb4容器进行测试, 在pb4中部署pgpool-II.
开源项目地址: https://www.pgpool.net/
pgpool-II 是一款开源的支持读写分离的连接池, 通过pgpool-II, 应用程序访问PolarDB 时, 可以根据SQL的特征自动路由到RW, RO节点, 实现对应用透明的读写分离功能.
1、在pb1 primary节点, 创建相应pgpool-II的映射用户
$ psql
postgres=# create user digoal superuser encrypted password 'pwd123' login;
CREATE ROLE
2、pb4, 部署pgpool-II 软件
cd /data/
wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.5.tar.gz -O pgpool-II-4.5.5.tar.gz
tar -zxvf pgpool-II-4.5.5.tar.gz
cd pgpool-II-4.5.5
./configure --prefix=/data/tools/pgpool4.5.5 --with-openssl
make -j 4
make install
配置动态库和环境变量
sudo vi /etc/ld.so.conf
# addd
/data/tools/pgpool4.5.5/lib
# 更新动态链接库
sudo ldconfig
# 设置shell环境变量初始化文件
echo"export PATH=/data/tools/pgpool4.5.5/bin:\$PATH" >> ~/.bashrc
# 设置当前环境变量
. ~/.bashrc
3、pb4, 配置pgpool-II
polardb 2节点+1个standby配置如下:
primary: 172.17.0.2:5432
replica1: 172.17.0.3:5432
standby1: 172.17.0.4:5432
polardb与aurora类似, 共享存储集群模式, 无需pgpool来管理HA.
https://www.pgpool.net/docs/latest/en/html/example-aurora.html
3.1、配置pgpool.conf common配置(开启读写分离, 但pgpool不做failover. 更多复杂配置请参考pgpool-II手册.):
echo"
listen_addresses = '0.0.0.0'
port = 9999
unix_socket_directories = '/tmp'
pcp_listen_addresses = 'localhost'
pcp_port = 9898
pcp_socket_dir = '/tmp'
log_destination = 'stderr'
logging_collector = on
log_directory = '/tmp/pgpool_logs'
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
backend_clustering_mode = 'streaming_replication'
load_balance_mode = on
sr_check_period = 0
health_check_period = 0
failover_on_backend_shutdown=off
failover_on_backend_error=off
backend_hostname0 = '172.17.0.2'
backend_port0 = '5432'
backend_weight0 = 1
backend_application_name0 = 'polardb_primray'
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_hostname1 = '172.17.0.3'
backend_port1 = '5432'
backend_weight1 = 2
backend_application_name1 = 'polardb_replica1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_hostname2 = '172.17.0.4'
backend_port2 = '5432'
backend_weight2 = 2
backend_application_name2 = 'polardb_standby1'
backend_flag2 = 'DISALLOW_TO_FAILOVER'
" > /data/tools/pgpool4.5.5/etc/pgpool.conf
3.2、配置pcp管理用户的密码文件pcp.conf
# 得到密码pwd123的md5, 你可以换成其他密码
postgres=# select md5('pwd123');
md5
----------------------------------
45cb41b32dcfb917ccd8614f1536d6da
(1 row)
echo"pcpadm:45cb41b32dcfb917ccd8614f1536d6da" > /data/tools/pgpool4.5.5/etc/pcp.conf
3.3、准备pgpool运行时pid文件目录和日志目录
sudo mkdir -p /var/run/pgpool
sudo chown postgres:postgres /var/run/pgpool
mkdir /tmp/pgpool_logs
3.4、配置pgpool.conf 认证配置. 有两种模式可选:
代理模式(适用广、配置简单、安全性较低) 双重认证模式(配置复杂、更安全)
3.4.1、代理模式配置. pgpool 不存储密码, 也不配置pool_hba.conf, 收到客户端发过来的明文密码后, 发送给给PolarDB进行认证. 这个方式好处是配置简单, 添加了数据用户后, 不需要修改pgpool的任何配置; 弊端是pgpool要知道客户端发过来的密码明文.
配置pgpool.conf
echo"
enable_pool_hba = off
allow_clear_text_frontend_auth = true
pool_passwd = ''
" >> /data/tools/pgpool4.5.5/etc/pgpool.conf
启动pgpool
# 启动pgpool
pgpool
# 停止pgpool方法如下:
pgpool -m fast stop
使用这个配置模式, 用tcpdump可以抓到客户端发送给pgpool的明文密码. 例子如下, 在pb4 pgpool容器上安装tcpdump
sudo apt update
sudo apt-get install -y tcpdump
开启一个pb4 shell会话, 抓发送到回环地址9999(pgpool 监听端口)的包:
sudo tcpdump -i lo dst port 9999 -w /tmp/9999.log
开启另一个pb4 shell会话, 连接pgpool执行sql:
psql -h 127.0.0.1 -p 9999 -U digoal -c "select client_addr from pg_stat_activity where pid=pg_backend_pid();" -W
Password: 输入密码 pwd123
client_addr
-------------
172.17.0.5
(1 row)
退出tcpdump抓包, 查看抓包文件, 可以看到明文密码:
$ strings /tmp/9999.log
<kf@
4kg@
<kh@
4ki@
user
digoal
database
postgres
application_name
psql
client_encoding
UTF8
4kk@
@kl@
pwd123 // 看到了明文密码
dgp7
4km@
4kn@
4ko@
4kp@
4kq@
4kr@
4ks@
4kt@
4ku@
4kv@
4kw@
4kx@
4ky@
4kz@
~k{@
Iselect client_addr from pg_stat_activity where pid=pg_backend_pid();
dg'T
4k|@
GHJW
dg\T
9k}@
GHJW
dgcT
4k~@
GHJ\
GHJ]
检查pgpool load balance是否生效, 每次连接分配了不一样的PolarDB instance, 说明负载均衡生效了:
export PGPASSWORD=pwd123
export PGDATABASE=postgres
postgres@cf12fcf6fd4f:/data/tools/pgpool4.5.5/etc$ psql -p 9999 -U digoal -w
psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu)
Type "help"forhelp.
postgres=# select inet_server_addr();
inet_server_addr
------------------
172.17.0.2
(1 row)
postgres=# \q
postgres@cf12fcf6fd4f:/data/tools/pgpool4.5.5/etc$ psql -p 9999 -U digoal -w
psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu)
Type "help"forhelp.
postgres=# select inet_server_addr();
inet_server_addr
------------------
172.17.0.4
(1 row)
postgres=# \q
postgres@cf12fcf6fd4f:/data/tools/pgpool4.5.5/etc$ psql -p 9999 -U digoal -w
psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu)
Type "help"forhelp.
postgres=# select inet_server_addr();
inet_server_addr
------------------
172.17.0.3
(1 row)
postgres=# \q
3.4.2、双重认证模式配置. pgpool 要存储密码. 密文是否加密、使用何种方式加密? 取决于要用什么密码认证方式(password,md5,SCRAM), 而且这个方式要和PolarDB内的配置保持一致. 我管他叫双重认证模式, 因为客户端要和pgpool 认证一次, 然后pgpool又要使用存储在pgpool服务器上的密码和PolarDB认证一次. 所以新建了数据库用户时, 也要在pgpool服务器配置存储密码的文件和pool_hba.conf, 复杂度就增加了. 安全在于支持SCRAM这个最高安全模式, 可以消除中间攻击者. md5和password都不安全, 原因可参考文末文章.
下面以最高安全模式SCRAM举例(注意一些老的client可能不兼容这个模式, 那么建议使用md5模式).
3.4.2.1、PolarDB 配置
为了兼容老的客户端, 并逐渐演变到SCRAM认证. 当pg_hba.conf配置为md5时, 可以兼容md5和SCRAM两种认证, postgresql.conf password_encryption 设置为scram-sha-256时就使用SCRAM认证, 设置为md5则使用md5.
pb1, pb2, pb3, 修改配置如下(可能默认已经是这个配置就不需要改):
vi $PGDATA/postgresql.conf
password_encryption = 'scram-sha-256'
select pg_reload_conf();
pb1, 查看PolarDB 用户 digoal 密码存储, 如果现在存储的是md5, 则需要刷新一下
postgres=# select usename,passwd from pg_shadow where usename='digoal';
usename | passwd
---------+-------------------------------------
digoal | md531a770cec82aa37e217bb6e46c3f9d55
(1 row)
# md5就是pwd+username算出来的
postgres=# select md5('pwd123digoal');
md5
----------------------------------
31a770cec82aa37e217bb6e46c3f9d55
(1 row)
pb1, 刷新PolarDB 用户 digoal 密码, 确认已使用scram存储.
postgres=# set password_encryption='scram-sha-256';
SET
postgres=# alter role digoal encrypted password 'pwd123';
ALTER ROLE
postgres=# select usename,passwd from pg_shadow where usename='digoal';
usename | passwd
---------+---------------------------------------------------------------------------------------------------------------------------------------
digoal | SCRAM-SHA-256$4096:9CZEDO6XFdcilseM1ZGSMA==$kBjbpajWAbV5mxUKdMCkCgycxBMMWC5/3XNFracq3jY=:aCrr9W1uRzRy/DOISCkGGXNjJFi9KqkRdFQDD4BELp0=
(1 row)
确认 pb1, pb2, pb3 的 pg_hba.conf 都已经配置为md5或scram-sha-256. 如有修改请记得reload配置. 如下:
host all all 0.0.0.0/0 md5
3.4.2.2、pb4, 配置pgpool scram认证.
配置pgpool.conf
$ vi /data/tools/pgpool4.5.5/etc/pgpool.conf
enable_pool_hba = on
allow_clear_text_frontend_auth = false
pool_passwd = '/data/tools/pgpool4.5.5/etc/pool_passwd'
配置pool_hba.conf
echo"
# add
host all all 0.0.0.0/0 scram-sha-256
" > /data/tools/pgpool4.5.5/etc/pool_hba.conf
创建加密密码的密文及文件, pgpool将用这个密文来加解密存储在/data/tools/pgpool4.5.5/etc/pool_passwd里的密钥.
echo"230923fsekksKLKLKEW922*&^#@" > ~/.pgpoolkey
chmod 0600 ~/.pgpoolkey
加密digoal数据库用户的密码pwd123.
$ pg_enc -m -f /data/tools/pgpool4.5.5/etc/pgpool.conf -k /home/postgres/.pgpoolkey -u digoal
db password: 输入要加密的digoal用户的密码pwd123
trying to read key from file /home/postgres/.pgpoolkey
或
$ pg_enc -m -f /data/tools/pgpool4.5.5/etc/pgpool.conf -k /home/postgres/.pgpoolkey -u digoal pwd123
密码存储在/data/tools/pgpool4.5.5/etc/pool_passwd这个文件里, 使用/home/postgres/.pgpoolkey进行加解密.
$ cat /data/tools/pgpool4.5.5/etc/pool_passwd
digoal:AESoI6ynLOHRoabseURe9Izeg==
/data/tools/pgpool4.5.5/etc/pool_passwd 文件内容说明
AES开头的 是可以加解密的密文
md5开头的 是md5加密后的密码(md5(pwd+username))
TEXT开头的 是明文密码
3.4.2.3、重启pgpool
# 停止pgpool:
pgpool -m fast stop
# 启动pgpool , 并指定用于加解密pool_passwd的密钥文件
pgpool -k /home/postgres/.pgpoolkey
使用tcpdump抓包, 现在抓不到digoal用户的密码了
$ strings /tmp/9999.log
user
digoal
database
postgres
application_name
psql
client_encoding
UTF8
6SCRAM-SHA-256
n,,n=,r=g0k2zq3Qm5f2KA45tgiur8T8
eg9#
Cu]1
Cu]1
lc=biws,r=g0k2zq3Qm5f2KA45tgiur8T8v0syIuNdStoxARpEnnXzFT2W,p=aHZPjKai8pMd/sWzpPHbVB5x8knKMmGb2RbUdGnKzOg=
Iselect client_addr from pg_stat_activity where pid=pg_backend_pid();
eg%@
eg0@
eg0C
4、使用pgbench 压测pgpool读写分离
初始化数据
pgbench -i -s 10 -h 127.0.0.1 -p 9999 -U digoal postgres
压测
pgbench -n -r -P 1 -c 8 -j 8 -T 120 -S -h 127.0.0.1 -p 9999 -U digoal postgres
通过另一会话观察压测中的连接数, 每个节点都分配了连接:
export PGPASSWORD=pwd123
$ psql -h 172.17.0.2 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';"
count
-------
8
(1 row)
$ psql -h 172.17.0.3 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';"
count
-------
8
(1 row)
$ psql -h 172.17.0.4 -p 5432 -U digoal -d postgres -c "select count(*) from pg_stat_activity where application_name='pgbench';"
count
-------
8
(1 row)
5、使用pcp管理命令查看pgpool中间件状态
pcp_node_info -U pcpadm -p 9898 -W
Password: 输入 pwd123
172.17.0.2 5432 2 0.200000 up unknown primary unknown 0 none none 2024-12-19 17:26:55
172.17.0.3 5432 2 0.400000 up unknown standby unknown 0 none none 2024-12-19 17:26:55
172.17.0.4 5432 2 0.400000 up unknown standby unknown 0 none none 2024-12-19 17:26:55
pcp_node_count -U pcpadm -p 9898 -W
Password: 输入 pwd123
3
pcp_pool_status -U pcpadm -h localhost -p 9898 -W
Password: 输入 pwd123
...
name : backend_data_directory2
value:
desc : data directory for backend #2
name : backend_status2
value: waiting
desc : status of backend #2
name : standby_delay2
value: 0
desc : standby delay of backend #2
name : backend_flag2
value: DISALLOW_TO_FAILOVER
desc : backend #2 flag
name : backend_application_name2
value: polardb_reader2
desc : application_name for backend #2
...
更多配置说明请参考《PolarDB 开源版 使用pgpool-II实现透明读写分离》
参考
《穷鬼玩PolarDB RAC一写多读集群系列 | 在Docker容器中用loop设备模拟共享存储》
《穷鬼玩PolarDB RAC一写多读集群系列 | 如何搭建PolarDB容灾(standby)节点》
《穷鬼玩PolarDB RAC一写多读集群系列 | 共享存储在线扩容》
《穷鬼玩PolarDB RAC一写多读集群系列 | 计算节点 Switchover》
《穷鬼玩PolarDB RAC一写多读集群系列 | 在线备份》
《穷鬼玩PolarDB RAC一写多读集群系列 | 在线归档》
《穷鬼玩PolarDB RAC一写多读集群系列 | 实时归档》
《穷鬼玩PolarDB RAC一写多读集群系列 | 时间点恢复(PITR)》
《开源PolarDB|PostgreSQL 应用开发者&DBA 公开课 - 5.6 PolarDB开源版本必学特性 - 安装与使用PostgreSQL开源插件/工具》
《PolarDB 开源版 使用pgpool-II实现透明读写分离》
https://www.postgresql.org/docs/current/auth-password.html
https://www.postgresql.org/docs/current/sql-createrole.html
https://www.postgresql.org/docs/current/runtime-config-connection.html
https://www.pgpool.net/docs/45/en/html/index.html
https://www.pgpool.net/docs/45/en/html/auth-aes-encrypted-password.html
https://www.pgpool.net/docs/45/en/html/pg-enc.html
https://www.cnblogs.com/pyng/p/9698723.html
《PostgreSQL md5 对比 MySQL - 秘钥认证》
文末彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航老司机开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: