背景
citus构建的postgresql集群只是实现了postgresql的横向分片扩展，自身的cn节点和worker节点并不具备系统高可用，因此需要借助其他插件。
方案一：repmgr+keepalived实现cn节点和worker节点直接通过vip互连，可以实现高可用，但是发生故障转移后，需要手动干预将故障节点重新加入集群。当然也可以通过脚本实现自动化。
方案二：当前patroni已经官方支持与citus的集成，并且能够实现cn节点和worker节点的自动故障转移和服务拉起，安装过程也比较简单，通过patroni直接进行集群初始化操作。
环境准备
提前准备好6台已经安装了postgresql的服务器，数据库不用初始化
操作系统：openeuler 22 LTS SP3
数据库：postgresql 16.3
etcd Version：3.4.14
patroni：3.3.0
citus：12.1.3
vip-manager：2.5.0
因资源受限，在测试环境仅有6台服务器，故etcd与cn1节点部署在同一台服务器上，且单实例运行，生产环境需采用3节点集群方式部署。
| hostname | ip | role |
|---|---|---|
| citus_cn1 | 10.11.110.45 | cn1/etcd/vip-manager |
| citus_cn2 | 10.11.110.48 | cn2/vip-manager |
| citus_wk_g1_1 | 10.11.110.49 | work1-1 |
| citus_wk_g1_2 | 10.11.110.50 | work1-2 |
| citus_wk_g2_1 | 10.11.110.51 | work2-1 |
| citus_wk_g2_2 | 10.11.110.52 | work2-2 |
etcd安装
这里直接采用dnf安装在citus_cn1服务器
dnf install etcd -y
配置单节点etcd并启动
cp /etc/etcd/etcd.conf{,_bak}
cat << EOF > /etc/etcd/etcd.conf
ETCD_NAME=default
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.11.110.45:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.11.110.45:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.11.110.45:2379"
EOF
systemctl enable etcd --now
注意etcd在3.4版本开始默认禁用了v2 API，故下面patroni配置的时候要使用v3 API
citus安装
下载源码：https://github.com/citusdata/citus/archive/refs/tags/v12.1.3.tar.gz
在每台服务器上编译安装
tar zxf citus-12.1.3.tar.gz && cd citus-12.1.3
export PG_CONFIG=/opt/postgresql/bin/pg_config
./configure
make && make install
patroni安装
程序安装
在每一台服务器上使用pip方式安装
dnf install python3-psycopg2 -y
pip install patroni[etcd]
vi ~/.bash_profile
export PATH=$PATH:/usr/local/bin
配置启动文件/lib/systemd/system/patroni.service
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
#ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
#ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=on-failure
[Install]
WantedBy=multi-user.target
若要启用watchdog功能，则需要配置postgresql的sudo权限，取消上面ExecStartPre部分注释
echo "postgres ALL = (root) NOPASSWD:/sbin/modprobe softdog,/bin/chown postgres /dev/watchdog" | sudo tee /etc/sudoers.d/postgres
chmod 0440 /etc/sudoers.d/postgres
visudo -c
CN节点配置
citus_cn1节点配置文件/etc/patroni.yml如下
scope: citus
namespace: /service/
name: cn1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.11.110.45:8008
citus:
database: testdb
group: 0 # coordinator
etcd3:
#Provide host to do the initial discovery of the cluster topology:
host: 10.11.110.45:2379
#Or use "hosts" to provide multiple endpoints,Could be a comma separated string: hosts: host1:port1,host2:port2
# The bootstrap configuration. Works only when the cluster is not yet initialized.
# If the cluster is already initialized, all changes in the `bootstrap` section are ignored!
bootstrap:
# This section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
# and all other cluster members will use it as a `global configuration`.
# WARNING! If you want to change any of the parameters that were set up
# via `bootstrap.dcs` section, please use `patronictl edit-config`!
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
pg_hba:
- host replication repl 10.11.0.0/16 md5
- host replication repl 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
use_slots: true
parameters:
wal_level: hot_standby
hot_standby: "on"
max_connections: 100
max_worker_processes: 8
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
max_prepared_transactions: 0
max_locks_per_transaction: 64
wal_log_hints: "on"
# track_commit_timestamp: "off"
# archive_mode: "on"
# archive_timeout: 1800s
# archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
# recovery_conf:
# restore_command: cp ../wal_archive/%f %p
# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.11.110.45:5432
data_dir: /opt/postgresql/data
bin_dir: /opt/postgresql/bin
# config_dir:
pgpass: /tmp/pgpass0
authentication:
replication:
username: repl
password: repl123456
superuser:
username: postgres
password: postgres123456
rewind: # Has no effect on postgres 10 and lower
username: rewind
password: rewind123465
parameters:
# Fully qualified kerberos ticket file for the running user
# same as KRB5CCNAME used by the GSS
# krb_server_keyfile: /var/spool/keytabs/postgres
unix_socket_directories: '..' # parent directory of data_dir
# Additional fencing script executed after acquiring the leader lock but before promoting the replica
#pre_promote: /path/to/pre_promote.sh
watchdog:
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
tags:
noloadbalance: false
clonefrom: false
nosync: false
nostream: false
配置项几个注意点：
- group参数项0表示协调节点，1、2、3等等表示工作节点
- pg_hba要开通流复制权限的网络策略，且配置的账号与下面postgresql部分中配置的复制账号一致
- scope项在整个集群所有节点上必须一致
- etcd3因为3.5版本的etcd默认禁用了V2 API，故需要配置成etcd3，否则patroni会连不上etcd
样例配置文件：https://github.com/zalando/patroni/blob/master/postgres0.yml
citus_cn2节点patroni配置只需修改如下部分的内容即可
name: cn2
restapi:
connect_address: 10.11.110.45:8008
postgresql:
connect_address: 10.11.110.45:5432
WK节点配置
citus_wk_g1_1节点配置如下：
scope: citus
namespace: /service/
name: wk1-1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.11.110.49:8008
citus:
database: testdb
group: 1 # work group1
etcd3:
#Provide host to do the initial discovery of the cluster topology:
host: 10.11.110.45:2379
#Or use "hosts" to provide multiple endpoints,Could be a comma separated string: hosts: host1:port1,host2:port2
# The bootstrap configuration. Works only when the cluster is not yet initialized.
# If the cluster is already initialized, all changes in the `bootstrap` section are ignored!
bootstrap:
# This section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
# and all other cluster members will use it as a `global configuration`.
# WARNING! If you want to change any of the parameters that were set up
# via `bootstrap.dcs` section, please use `patronictl edit-config`!
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
pg_hba:
- host replication repl 10.11.0.0/16 md5
- host replication repl 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
use_slots: true
parameters:
wal_level: hot_standby
hot_standby: "on"
max_connections: 100
max_worker_processes: 8
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
max_prepared_transactions: 0
max_locks_per_transaction: 64
wal_log_hints: "on"
# track_commit_timestamp: "off"
# archive_mode: "on"
# archive_timeout: 1800s
# archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
# recovery_conf:
# restore_command: cp ../wal_archive/%f %p
# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
- encoding: UTF8
- data-checksums
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.11.110.49:5432
data_dir: /opt/postgresql/data
bin_dir: /opt/postgresql/bin
# config_dir:
pgpass: /tmp/pgpass0
authentication:
replication:
username: repl
password: repl123456
superuser:
username: postgres
password: postgres123456
rewind: # Has no effect on postgres 10 and lower
username: rewind
password: rewind123465
parameters:
# Fully qualified kerberos ticket file for the running user
# same as KRB5CCNAME used by the GSS
# krb_server_keyfile: /var/spool/keytabs/postgres
unix_socket_directories: '..' # parent directory of data_dir
# Additional fencing script executed after acquiring the leader lock but before promoting the replica
#pre_promote: /path/to/pre_promote.sh
watchdog:
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
tags:
noloadbalance: false
clonefrom: false
nosync: false
nostream: false
citus_wk_g1_2节点配置仅需在citus_wk_g1_1的基础上做少量修改：
name: wk1-2
restapi:
connect_address: 10.11.110.50:8008
postgresql:
connect_address: 10.11.110.50:5432
citus_wk_g2_1和citus_wk_g2_2的配置变动内容如下
name: wk2-1 #wk2-2节点就自行修改为wk2-2即可
citus:
group: 2 # work group2
restapi:
connect_address: 10.11.110.50:8008
postgresql:
connect_address: 10.11.110.50:5432
查看集群状态
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml topology citus
+ Citus cluster: citus ----------+--------------+-----------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+---------+--------------+--------------+-----------+----+-----------+
| 0 | cn1 | 10.11.110.45 | Leader | running | 3 | |
| 0 | + cn2 | 10.11.110.48 | Sync Standby | streaming | 3 | 0 |
| 1 | wk1-2 | 10.11.110.50 | Leader | running | 3 | |
| 1 | + wk1-1 | 10.11.110.49 | Sync Standby | streaming | 3 | 0 |
| 2 | wk2-1 | 10.11.110.51 | Leader | running | 1 | |
| 2 | + wk2-2 | 10.11.110.52 | Sync Standby | streaming | 1 | 0 |
+-------+---------+--------------+--------------+-----------+----+-----------+
激活开机自启动
systemctl enable patroni.service
切换测试
手动切换switchover
将cn节点的leader从cn1切换至cn2上
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml switchover citus --group 0 --leader cn1 --candidate cn2 --force
Current cluster topology
+ Citus cluster: citus (group: 0, 7375060050244660283) -+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+--------------+--------------+-----------+----+-----------+
| cn1 | 10.11.110.45 | Leader | running | 3 | |
| cn2 | 10.11.110.48 | Sync Standby | streaming | 3 | 0 |
+--------+--------------+--------------+-----------+----+-----------+
2024-05-31 20:19:34.10060 Successfully switched over to "cn2"
+ Citus cluster: citus (group: 0, 7375060050244660283) ------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+--------------+---------+---------+----+-----------+
| cn1 | 10.11.110.45 | Replica | stopped | | unknown |
| cn2 | 10.11.110.48 | Leader | running | 3 | |
+--------+--------------+---------+---------+----+-----------+
--group 0 就是指cn节点组
--force 是为了不进行交互式的信息确认
--candidate 需要提升为主节点的候选节点
只有在patroni集群处于健康状态时才能使用switchover进行切换，否则只能使用failover操作。
此时cn2节点已经作为leader节点运行，cn1节点变为从节点
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml topology citus
+ Citus cluster: citus ----------+--------------+-----------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+---------+--------------+--------------+-----------+----+-----------+
| 0 | cn2 | 10.11.110.48 | Leader | running | 4 | |
| 0 | + cn1 | 10.11.110.45 | Sync Standby | streaming | 4 | 0 |
| 1 | wk1-2 | 10.11.110.50 | Leader | running | 3 | |
| 1 | + wk1-1 | 10.11.110.49 | Sync Standby | streaming | 3 | 0 |
| 2 | wk2-1 | 10.11.110.51 | Leader | running | 1 | |
| 2 | + wk2-2 | 10.11.110.52 | Sync Standby | streaming | 1 | 0 |
+-------+---------+--------------+--------------+-----------+----+-----------+
强制杀进程
模拟postgresql故障，手动kill -9 cn2节点上postgresql的进程，patroni服务自动拉起postgresql，cn2仍然作为leader节点运行。
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml topology citus
+ Citus cluster: citus ----------+--------------+-----------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+---------+--------------+--------------+-----------+----+-----------+
| 0 | cn2 | 10.11.110.48 | Leader | running | 4 | |
| 0 | + cn1 | 10.11.110.45 | Replica | running | 4 | 0 |
| 1 | wk1-2 | 10.11.110.50 | Leader | running | 3 | |
| 1 | + wk1-1 | 10.11.110.49 | Sync Standby | streaming | 3 | 0 |
| 2 | wk2-1 | 10.11.110.51 | Leader | running | 1 | |
| 2 | + wk2-2 | 10.11.110.52 | Sync Standby | streaming | 1 | 0 |
+-------+---------+--------------+--------------+-----------+----+-----------+
[postgres@citus_cn1 ~]$
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml topology citus
+ Citus cluster: citus ----------+--------------+-----------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+---------+--------------+--------------+-----------+----+-----------+
| 0 | cn2 | 10.11.110.48 | Leader | running | 4 | |
| 0 | + cn1 | 10.11.110.45 | Sync Standby | streaming | 4 | 0 |
| 1 | wk1-2 | 10.11.110.50 | Leader | running | 3 | |
| 1 | + wk1-1 | 10.11.110.49 | Sync Standby | streaming | 3 | 0 |
| 2 | wk2-1 | 10.11.110.51 | Leader | running | 1 | |
| 2 | + wk2-2 | 10.11.110.52 | Sync Standby | streaming | 1 | 0 |
+-------+---------+--------------+--------------+-----------+----+-----------+
停止patroni服务
手动停掉cn2节点patroni服务，主从发生切换
[postgres@citus_cn1 ~]$ patronictl -c /etc/patroni.yml topology citus
+ Citus cluster: citus ----------+--------------+-----------+----+-----------+
| Group | Member | Host | Role | State | TL | Lag in MB |
+-------+---------+--------------+--------------+-----------+----+-----------+
| 0 | cn1 | 10.11.110.45 | Leader | running | 5 | |
| 0 | + cn2 | 10.11.110.48 | Replica | stopped | | unknown |
| 1 | wk1-2 | 10.11.110.50 | Leader | running | 3 | |
| 1 | + wk1-1 | 10.11.110.49 | Sync Standby | streaming | 3 | 0 |
| 2 | wk2-1 | 10.11.110.51 | Leader | running | 1 | |
| 2 | + wk2-2 | 10.11.110.52 | Sync Standby | streaming | 1 | 0 |
+-------+---------+--------------+--------------+-----------+----+-----------+
启动cn2节点的patroni服务后，cn2节点作为备节点加入集群。
WK节点状态
实际上cn节点当前连接的是哪个worker节点也可以通过citus的表查看
testdb=# select * from pg_dist_node;
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+--------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
1 | 0 | 10.11.110.45 | 5432 | default | t | t | primary | default | t | f
2 | 1 | 10.11.110.50 | 5432 | default | t | t | primary | default | t | t
3 | 2 | 10.11.110.51 | 5432 | default | t | t | primary | default | t | t
(3 rows)
注意citus扩展当前只作用于testdb这个数据库，因为patroni当前只支持自动初始化时在数据库级别创建扩展。
vip-manager安装
当前集群实现了故障自动转移，但是对外服务没有提供统一的vip，当cn节点发生故障转移后，客户端不能访问到正确的cn节点，因此需要使用vip来统一对外服务。
这里采用vip-manager来进行vip管理，该服务会自动读取etcd中patroni集群leader节点的信息，当leader发生变化会根据配置的数据进行vip自动切换，若没有正常的leader节点，则vip会自动删除。
安装
tar zxf vip-manager_2.5.0_Linux_x86_64.tar.gz
cd vip-manager_2.5.0_Linux_x86_64
cp vip-manager /usr/local/bin/
配置文件cn1节点上/etc/vip-manager.yml
# time (in milliseconds) after which vip-manager wakes up and checks if it needs to register or release ip addresses.
interval: 1000
# the etcd or consul key which vip-manager will regularly poll.
trigger-key: "/service/citus/0/leader"
# if the value of the above key matches the trigger-value (often the hostname of this host), vip-manager will try to add the virtual ip address to the interface specified in Iface
trigger-value: "cn1"
ip: 10.11.88.44 # the virtual ip address to manage
netmask: 16 # netmask for the virtual ip
interface: ens33 #interface to which the virtual ip will be added
# how the virtual ip should be managed. we currently support "ip addr add/remove" through shell commands or the Hetzner api
hosting-type: basic # possible values: basic, or hetzner.
dcs-type: etcd # etcd or consul
# a list that contains all DCS endpoints to which vip-manager could talk.
dcs-endpoints:
- http://127.0.0.1:2379
- http://10.11.110.45:2379
trigger-key 填etcd中cn主节点对应的key名称，不知道的情况下可以用如下命令在etcd中查看
#etcdctl get --prefix "/service"
/service/citus/0/leader
cn1
/service/citus/0/members/cn1
{"conn_url":"postgres://10.11.110.45:5432/postgres","api_url":"http://10.11.110.45:8008/patroni","state":"running","role":"master","version":"3.3.0","xlog_location":50415192,"timeline":5}
/service/citus/0/members/cn2
{"conn_url":"postgres://10.11.110.48:5432/postgres","api_url":"http://10.11.110.48:8008/patroni","state":"running","role":"replica","version":"3.3.0","xlog_location":50415192,"replication_state":"streaming","timeline":5}
/service/citus/0/status
{"optime":50415192}
/service/citus/0/sync
{"leader":"cn1","sync_standby":"cn2"}
trigger-value 填当key返回的值为所配置的值时进行vip切换，也就是要运行vip-manager服务的主机名或在patroni注册的节点名称。
cn2节点上的vip-manager配置与cn1节点保持一致，只修改一项配置即可
trigger-value: "cn2"
配置启动服务/lib/systemd/system/vip-manager.service
[Unit]
Description=Manages Virtual IP for Patroni
After=network-online.target
Before=patroni.service
[Service]
Type=simple
ExecStart=/usr/local/bin/vip-manager --config=/etc/vip-manager.yml
Restart=on-failure
[Install]
WantedBy=multi-user.target
配置开机启动
systemctl enable vip-manager.service --now
其他扩展
pgbouncer
若系统有高并发的业务需求，则可以考虑结合pgbouncer进行数据库连接池管理，提升系统并发效率，减少资源占用。
pg_stat_statements
为了方便后续集群查询性能调优，建议安装pg_stat_statements扩展，在patroni下变更配置操作如下
patronictl -c /etc/patroni.yml edit-config citus -p shared_preload_libraries="citus,pg_stat_statements"
上面的操作需要分别在3个复制组上的主节点执行一次，通过命令patronictl -c /etc/patroni.yml restart --force重启所有节点postgresql，连接citus，执行create extension pg_stat_statements;。
Pgpool-II
利用Pgpool-II实现读写分离和负载均衡功能
下载源码并安装
wget https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.2.tar.gz
tar zxf pgpool-II-4.5.2.tar.gz
cd pgpool-II-4.5.2
./configure --prefix=/opt/pgpool --with-openssl
make && make install
#配置环境变量
vim ~/.bashrc
export PGPOOL_HOME=/opt/pgpool
export PATH=$PGPOOL_HOME/bin:$PATH
配置如下几个配置文件
pgpool/etc/pcp.conf #用于访问管理接口执行管理操作的鉴权信息文件
pgpool/etc/pgpool.conf #主配置文件，启动时需使用-f指定配置文件
pgpool/etc/pool_hba.conf #用于pgpool客户端的验证，类似与postgresql的pg_hba.conf作用
pcp.conf
#使用bin目录下的pg_md5加密密码
/opt/pgpool/bin/pg_md5 admin123465
ce3fcc5aeab048f1f8ba1821d029b375
cat << EOF >> pcp.conf
pgpool:ce3fcc5aeab048f1f8ba1821d029b375
EOF
pgpool.conf
# ----------------------------
# pgPool-II configuration file
# ----------------------------
backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
# - Backend Connection Settings -
backend_hostname0 = 'citus_cn1'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'citus_cn2'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
process_management_mode = dynamic
num_init_children = 32
min_spare_children = 5
max_spare_children = 10
max_pool = 4
#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
log_destination = 'syslog'
log_connections = on
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/opt/pgpool/pgpool.pid'
logdir = '/tmp'
#log_connections = on
#log_hostname = on
#log_statement = on
#log_per_node_statement = on
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
statement_level_load_balance = on
#------------------------------------------------------------------------------
# STREAMING REPLICATION MODE
#------------------------------------------------------------------------------
# - Streaming -
sr_check_period = 10
sr_check_user = 'nobody'
sr_check_password = ''
sr_check_database = 'postgres'
配置pool_passwd文件，鉴权采用的是scram-sha-256加密方式，因为后端postgresql也是默认该方式
#将pgpoolkey保存至 ~/.pgpoolkey
echo '123' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
pg_enc -m -k ~/.pgpoolkey -u bill -p
pg_enc -m -k ~/.pgpoolkey -u nobody -p
创建pgpool检测后端数据库使用的账号
create role nobody login encrypted password 'nobody123456';
pool_hba.conf
cat << EOF >> pool_hba.conf
host all all 0.0.0.0/0 scram-sha-256
EOF
启动文件/lib/systemd/system/pgpool.service
[Unit]
Description=Pgpool-II
After=syslog.target network.target
[Service]
User=postgres
Group=postgres
EnvironmentFile=-/etc/sysconfig/pgpool
ExecStart=/opt/pgpool/bin/pgpool -f /opt/pgpool/etc/pgpool.conf -n
ExecStop=/opt/pgpool/bin/pgpool -f /opt/pgpool/etc/pgpool.conf -m fast stop
ExecReload=/opt/pgpool/bin/pgpool -f /opt/pgpool/etc/pgpool.conf reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
[Install]
WantedBy=multi-user.target
启动pgpool
systemctl start pgpool
连接测试
psql -h 10.11.110.48 -Ubill -p 9999 -d testdb
读写分离测试
#初始化测试数据
pgbench -i -h 10.11.110.48 -p 9999 -U bill -d testdb
#20 客户端连接， 每个客户端 10个线程，运行时间 10s
pgbench -c 20 -j 10 -n -T 10 -h 10.11.110.48 -p 9999 -Ubill -d testdb
#20 客户端连接， 每个客户端 10个线程，只进行查询操作，运行时间 10s
pgbench -c 20 -j 10 -n -S -T 10 -h 10.11.110.48 -p 9999 -Ubill -d testdb
负载均衡结果可以通过如下方式查看
testdb=> show pool_nodes;
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+--------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.11.110.45 | 5432 | up | unknown | 0.500000 | primary | unknown | 38155 | false | 0 | | | 2024-06-11 11:37:30
1 | 10.11.110.48 | 5432 | up | unknown | 0.500000 | standby | unknown | 31662 | true | 0 | | | 2024-06-11 11:37:30
(2 rows)
备注：上面第一个测试结果会看到所有的查询操作都跑到一个节点上去了，这是因为pgbench压测语句里面使用了begin关键字，这会影响pgpool的负载均衡策略。第二个只对查询进行测试时就可以从结果看到所有的查询操作被正常负载到两个节点上了。
问题
权限问题
在初始集群的过程中可能会遇到启动报错目录权限的情况，类似如下：
data directory "/opt/postgresql/data" has invalid permissions
这个是因为之前手动创建过这个目录，要么修改目录权限为0700，要么删除data目录，让patroni自动创建。
密码问题
在citus集群中协调节点访问工作节点也是需要用到建立集群是配置的账号密码，若参考上面使用patroni建立citus集群后，使用配置文件中的postgres账号访问数据库不会出现权限问题。如果后续根据业务需要，需建立不同的数据库以及访问用户，此时在pgbouncer或者pgpool等连接池中间件中配置的数据库账号密码与后端citus协调节点和工作节点数据库中创建的账号一致。使用新建立的数据库账号访问分布式表则会出现如下类似报错：
ERROR: connection to the remote node bill@10.11.110.49:5432 failed with the following error: fe_sendauth: no password supplied
这是因为协调节点连接工作节点使用的认证信息默认是保存在~/.pgpass文件中，因此需要将新建的账号及数据库信息加入该认证信息配置文件，文件权限 600。
文件格式参考：hostname:port:database:username:password
浙公网安备 33010602011771号