瀚高数据库
目录
文档用途
详细信息
文档用途
本文主要介绍Patroni架构下单点HAProxy的安装部署，通过单点HAProxy实现数据库的负载均衡。本文为HAProxy系列文章之一，其他相关文章请点击文档下方的相关文档链接进行详细查看，文章内不在赘述。
详细信息
一、环境信息
1、服务器信息
| IP | 主机名 | 安装组件 | 操作系统 | CPU |
|---|---|---|---|---|
| 192.168.164.111 | patroni1 | etcd+patroni+pg | redhat7.6 | X86_64 |
| 192.168.164.112 | patroni2 | etcd+patroni+pg | redhat7.6 | X86_64 |
| 192.168.164.113 | patroni3 | etcd+patroni+pg | redhat7.6 | X86_64 |
| 192.168.164.114 | patroni4 | HAProxy | redhat7.6 | X86_64 |
2、软件版本信息
Python: 3.4.17
etcd：3.5.7
patroni：2.4.1
pg版本：14.6
HAProxy: 2.6.9
3、防火墙端口信息
| 组件 | 端口号 | 用途 |
|---|---|---|
| Etcd | 2379/2380 | 客户端访问/服务监听端口 |
| Patroni | 8008 | Restapi监听端口 |
| PG | 5432 | 数据库服务端口 |
| HAProxy | 10001/6432/6433 | WEB服务端口/数据库对外服务端口 |
4、目录规划
| 组件 | 路径 | 用途 |
|---|---|---|
| Etcd | /opt/etcd | /opt/etcd为etcd工作的主目录,路径内存放etcd配置文件 |
| Etcd | /opt/etcd/data | Etcd工作data目录 |
| Patroni | /opt/patroni | 存放patroni配置文件 |
| Patroni | /opt/patroni/patroni_log | 存放patroni运行日志 |
| PG | /opt/pg14.6 | 数据库安装主目录 |
| PG | /opt/pg14.6/data | 数据库data目录 |
| HAProxy | /opt/haproxy | Haproxy程序主目录，路径内存放haproxy配置文件 |
| Python | /usr/local/python3 | Python3主目录 |
5、整体安装步骤
①环境准备工作
②安装并启动etcd服务
③编译安装pg数据库
④安装Python3
⑤编译安装patroni，启动服务
⑥编译安装配置HAProxy，启动服务
注：所有安装包均上传至各节点的/opt目录下，后边不在赘述。
二、环境准备工作
以下内容如无特殊说明，需在数据库高可用三节点执行。
1、配置防火墙
本文通过停止防火墙服务的方式进行演示。
[root@patroni1 ~]# systemctl stop firewalld.service
[root@patroni1 ~]# systemctl disable firewalld.service
2、停止Networkmanager.service
[root@patroni1 ~]# systemctl stop Networkmanager.service
[root@patroni1 ~]# systemctl disable Networkmanager.service
3、时间同步
笔者使用的是虚拟机，故通过同时向四台虚拟机发送修改时间命令的方式实现时间同步
[root@patroni1 ~]# date -s'时间'
4、创建系统用户及组
只在数据库高可用相关节点执行以下命令
[root@patroni1 ~]# groupadd -g 10000 postgres
[root@patroni1 ~]# useradd -g 10000 -u 10000 -m postgres
5、创建各类工作目录
只在数据库高可用相关节点执行以下命令
[root@patroni1 ~]# mkdir /opt/etcd
[root@patroni1 ~]# mkdir /opt/patroni/patroni_log -p
[root@patroni1 ~]# chown -R postgres:postgres /opt/patroni/
三、安装ETCD
以下内容如无特殊说明，需在数据库高可用三节点执行。
1、解压缩etcd,将解压后的etcd、etcdctl命令移动至/usr/bin目录下
[root@patroni1 ~]# cd /opt
[root@patroni1 opt]# tar -zxvf etcd-v3.5.7-linux-amd64.tar.gz
[root@patroni1 opt]# cd etcd-v3.5.7-linux-amd64/
[root@patroni1 etcd-v3.5.7-linux-amd64]# cp etcd* /usr/bin/
2、编辑etcd配置文件
[root@patroni1 etcd-v3.5.7-linux-amd64]# vi /opt/etcd/etcd.yaml
##写入以下内容，各节点需要根据实际信息填写
debug: false
name: etcd01
data-dir: /opt/etcd/data
initial-advertise-peer-urls: http://192.168.164.111:2380
listen-peer-urls: http://192.168.164.111:2380
advertise-client-urls: http://192.168.164.111:2379
listen-client-urls: http://192.168.164.111:2379,http://127.0.0.1:2379
initial-cluster-token: etcd-cluster
initial-cluster: etcd01=http://192.168.164.111:2380,etcd02=http://192.168.164.112:2380,etcd03=http://192.168.164.113:2380
initial-cluster-state: new
enable-v2: true
3、编辑etcd系统服务文件
[root@patroni1 opt]# vi /usr/lib/systemd/system/etcd.service
##增加以下内容
[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target
[Service]
Type=notify
User=root
WorkingDirectory=/opt/etcd/
# set GOMAXPROCS to number of processors
ExecStart=/bin/bash -c "GOMAXPROCS=$(nproc) /usr/bin/etcd --config-file=/opt/etcd/etcd.yaml"
Restart=on-failure
LimitNOFILE=65536
[Install]
WantedBy=multi-user.target
4、三节点同时启动etcd服务
[root@patroni1 opt]# systemctl start etcd.service
[root@patroni1 opt]# systemctl status etcd.service
[root@patroni1 opt]# systemctl enable etcd.service
5、检查etcd集群工作状态
[root@patroni1 opt]# etcdctl endpoint health
127.0.0.1:2379 is healthy: successfully committed proposal: took = 3.75515ms
[root@patroni1 opt]# ETCDCTL_API=2 etcdctl cluster-health
member 5e1b8b610178ffb4 is healthy: got healthy result from http://192.168.164.111:2379
member a9810de95208a71d is healthy: got healthy result from http://192.168.164.112:2379
member ed63d339d4c7b8e4 is healthy: got healthy result from http://192.168.164.113:2379
cluster is healthy
[root@patroni1 opt]# etcdctl --endpoints=http://192.168.164.111:2379,http://192.168.164.112:2379,http://192.168.164.113:2379 endpoint status --write-out=table
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.164.111:2379 | 5e1b8b610178ffb4 | 3.5.7 | 20 kB | false | false | 6 | 180077 | 180077 | |
| http://192.168.164.112:2379 | a9810de95208a71d | 3.5.7 | 20 kB | false | false | 6 | 180077 | 180077 | |
| http://192.168.164.113:2379 | ed63d339d4c7b8e4 | 3.5.7 | 20 kB | true | false | 6 | 180077 | 180077 | |
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
四、安装数据库
1、安装环境依赖
[root@patroni1 opt]# yum install gcc gcc-c++ flex bison zlib-devel libreadline* ncurses-devel readline-devel gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl tcl-devel uuid-devel make python-devel libffi-devel sysemd-devel
2、解压缩并编译安装数据库，数据库安装完成后无需初始化
[root@patroni1 opt]# tar -zxvf postgresql-14.6.tar.gz
[root@patroni1 opt]# cd /opt/postgresql-14.6/
[root@patroni1 postgresql-14.6]#./configure --prefix=/opt/pg14.6 --with-tcl --with-tclconfig=/usr/lib64 --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety
[root@patroni1 postgresql-14.6]# make && make install
[root@patroni1 postgresql-14.6]# chown -R postgres:postgres /opt/pg14.6/
五、编译安装patroni
1、编译安装python3.7.13
[root@patroni1 opt]# tar -zxvf Python-3.7.13.tgz
[root@patroni1 opt]# cd Python-3.7.13/
[root@patroni1 Python-3.7.13]# ./configure --prefix=/usr/local/python3 --enable-shared
[root@patroni1 Python-3.7.13]# make && make install
2、修改系统默认的python、python3、pip、pip3等路径
[root@patroni1 Python-3.7.13]# ln -sf /usr/local/python3/bin/python3.7 /usr/bin/python
[root@patroni1 Python-3.7.13]# ln -sf /usr/local/python3/bin/pip3.7 /usr/bin/pip
[root@patroni1 Python-3.7.13]# ln -sf /usr/local/python3/bin/python3 /usr/bin/python3
[root@patroni1 Python-3.7.13]# ln -sf /usr/local/python3/bin/pip3 /usr/bin/pip3
3、修改YUM文件
由于redhat7版本yum命令为python2编写，修改系统默认python版本后需要修改对应的yum信息
[root@patroni1 Python-3.7.13]# sed -i "s:\<python\>:python2:g" /usr/bin/yum
[root@patroni1 Python-3.7.13]# sed -i "s:\<python\>:python2:g" /usr/libexec/urlgrabber-ext-down
4、增加python3 lib库路径并生效
[root@patroni1 Python-3.7.13]# vi /etc/ld.so.conf
##增加以下信息
/usr/local/python3/lib
[root@patroni1 Python-3.7.13]# ldconfig
5、安装patroni前输出pg及python相关的环境变量
[root@patroni1 opt]# export PGPORT=5432
[root@patroni1 opt]# export PGHOME=/opt/pg14.6
[root@patroni1 opt]# export PGDATA=/opt/pg14.6/data
[root@patroni1 opt]# export PGUSER=postgres
[root@patroni1 opt]# export PGDATABASE=postgres
[root@patroni1 opt]# export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/local/python3/lib:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:${LD_LIBRARY_PATH}
[root@patroni1 opt]# export PATH=$PGHOME/bin:$PATH
[root@patroni1 opt]# export PKG_CONFIG_PATH=/usr/local/python3/lib/pkgconfig:$PKG_CONFIG_PATH
[root@patroni1 opt]# export C_INCLUDE_PATH=/usr/local/python3/include/python3.7m:$C_INCLUDE_PATH
[root@patroni1 opt]# export MANPATH=$PGHOME/share/man:$MANPATH
6、创建pip源配置文件
[root@patroni1 ~]# mkdir ~/.pip
[root@patroni1 ~]# cat ~/.pip/pip.conf
##配置信息如下
[global]
index-url = http://mirrors.aliyun.com/pypi/simple/
[install]
trusted-host = mirrors.aliyun.com
You have new mail in /var/spool/mail/root
7、在线安装patroni
##使用pip或者pip3均可以
[root@patroni1 opt]# pip/pip3 install psycopg2
[root@patroni1 opt]# pip/pip3 install psycopg2-binary
[root@patroni1 opt]# pip/pip3 install patroni[etcd]==2.1.4
8、将patroni相关命令移动到数据库bin目录下并修改属主
[root@patroni1 opt]# cd /usr/local/python3/bin/
[root@patroni1 bin# cp patroni* /opt/pg14.6/bin
[root@patroni1 bin]# chown -R postgres:postgres /opt/pg14.6/bin
9、编辑patroni.yaml文件
[root@patroni1 opt]# su – postgres
[postgres@patroni1 ~]$ vi /opt/patroni/patroni.yaml
##配置信息如下：
#节点名称，集群中每个节点间的名称不同
name: test4
#配置在存储（例如etcd）中的路径，默认值：service。同一集群各节点使用同样的配置
namespace: postgres
#集群名称
scope: test
#配置patroni的rest api信息
restapi:
connect_address: 192.168.164.114:8008
# connect_address: 192.168.164.112:8008
#配置reset api的监听端口
listen: 0.0.0.0:8008
#配置etcd信息
etcd:
#配置etcd所有节点的访问IP及端口
hosts: 192.168.164.111:2379,192.168.164.112:2379,192.168.164.113:2379
#填写使用patroni初始化数据库配置信息
bootstrap:
#数据库初始信息
initdb:
- encoding: UTF8
- locale: en_US.UTF-8
- data-checksums
- auth: md5
dcs:
ttl: 60
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
master_stop_timeout: 60
synchronous_mode: true
max_timelines_history: 0
check_timeline: true
postgresql:
#设置是否使用复制槽，默认true
use_slots: true
#设置是否使用pg_rewind，默认false
use_pg_rewind: true
# PG初始化时使用的参数
parameters:
listen_addresses: '0.0.0.0'
port: 5432
max_connections: 200
max_locks_per_transaction: 64
wal_level: logical
wal_log_hints: 'on'
track_commit_timestamp: on
max_wal_senders: 20
max_replication_slots: 20
wal_keep_size: 10240
hot_standby: 'on'
unix_socket_directories: '/tmp'
archive_timeout: 1800s
#下面是postgresql配置信息
postgresql:
database: postgres
bin_dir: /opt/pg14.6/bin
data_dir: /opt/pg14.6/data
connect_address: 192.168.164.114:5432
listen: 0.0.0.0:5432
authentication:
superuser:
username: postgres
password: postgres
replication:
username: replicator
password: replicator
rewind:
username: replicator
password: replicator
#当前运行的数据库配置
parameters:
synchronous_standby_names: '*'
shared_buffers: '512MB'
work_mem: '8MB'
checkpoint_timeout: '10min'
checkpoint_completion_target: 0.9
logging_collector: 'on'
log_destination: csvlog
log_filename: postgresql-%d-%H.log
log_rotation_age: 1h
log_rotation_size: 100MB
log_truncate_on_rotation: 'on'
log_file_mode: '0640'
log_autovacuum_min_duration: 0
log_checkpoints: 'on'
log_lock_waits: 'on'
log_min_duration_statement: 1000
log_replication_commands: on
log_statement: ddl
log_temp_files: 0
track_functions: all
shared_preload_libraries: ' '
superuser_reserved_connections: 10
huge_pages: 'try'
pg_hba:
- local all all trust
- host all all 127.0.0.1/32 trust
- host all all 192.168.164.111/32 trust
- host all all 192.168.164.112/32 trust
- host all all 192.168.164.113/32 trust
- host all all 0.0.0.0/0 md5
- host all all ::1/128 md5
- local replication all md5
- host replication replicator 192.168.164.0/24 trust
- host replication all 0.0.0.0/0 md5
- host replication all ::1/128 md5
use_unix_socket: false
#patroni日志配置信息
log:
#设置日志级别，默认INFO。日志级别有：NOTSET、DEBUG、INFO、WARNING、ERROR、CRITICAL，从左往右，日志量依次减少
level: INFO
#日志存放位置
dir: /opt/patroni/patroni_log
#日志存放数量
file_num: 4
#每个日志文件的大小，单位bytes
file_size: 2500000
10、编写patroni系统服务文件
[root@patroni1 ~]# vi /usr/lib/systemd/system/patroni.service
##配置新如下
[Unit]
Description=patroni
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
Environment="PGHOME=/opt/pg14.6"
Environment="PGDATA=/opt/pg14.6/data"
Environment="PGPORT=5432"
Environment="LD_LIBRARY_PATH=/opt/pg14.6/lib"
Environment="PATH=/opt/pg14.6/bin"
# Start the patroni process
ExecStart=/bin/bash -c "patroni /opt/patroni/patroni.yaml"
# Send HUP to reload from patroni.yml
ExecReload=/usr/bin/kill -s HUP $MAINPID
# only kill the patroni process, not it's children, so it will gracefully stop postgres
KillMode=process
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30
# Do not restart the service if it crashes, we want to manually inspect database on failure
Restart=yes
[Install]
WantedBy=multi-user.target
11、启动patorni服务并设置开机自启
[root@patroni1 ~]# systemctl daemon-reload
[root@patroni1 ~]# systemctl start patroni.service
[root@patroni1 ~]# systemctl status patroni.service
[root@patroni1 ~]# systemctl enable patorni.service
12、集群工作状态检查
[postgres@patroni2 ~]$ patronictl -c /opt/patroni/patroni.yaml list
+ Cluster: test -----------+--------------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+-----------------+--------------+---------+----+-----------+
| test1 | 192.168.164.111 | Leader | running | 1 | |
| test2 | 192.168.164.112 | Sync Standby | running | 1 | 0 |
| test3 | 192.168.164.113 | Replica | running | 1 | 0 |
+--------+-----------------+--------------+---------+----+-----------+
至此 patroni+etcd+pg14.6高可用集群搭建完成
六、安装配置HAProxy
此部分只在第四个节点进行
1、解压缩并编译安装haproxy
[root@patroni4 ~]# cd /opt
[root@patroni4 opt]# tar -zxvf haproxy-2.6.9.tar.gz
[root@patroni4 opt]# cd haproxy-2.6.9/
[root@patroni4 haproxy-2.6.9]# make TARGET=linux-glibc ARCH=x86_64 PREFIX=/opt/haproxy USE_ZLIB=1 USE_CPU_AFFINITY=1 USE_PCRE=1 USE_OPENSSL=1 USE_SYSTEMD=1
[root@patroni1 haproxy-2.6.9]# make install PREFIX=/opt/haproxy
2、环境变量增加haproxy的可执行路径
[root@patroni4 ~]# vi ~/.bashrc
##增加以下内容（：后边的内容）
export PATH=$PATH:/opt/haproxy/sbin/
[root@patroni4 ~]# source ~/.bashrc
3、编辑haproxy配置文件
[root@patroni4 ~]# vi /opt/haproxy/haproxy.yaml
##配置新如下
global
maxconn 1000
chroot /opt/haproxy
user root
group root
daemon
# nbproc 4
# cpu-map 1 0
# cpu-map 2 1
# cpu-map 3 2
# cpu-map 4 3
pidfile /var/run/haproxy.pid
log 127.0.0.1 local1 info
defaults
option tcplog
option dontlognull
retries 3
option redispatch
option abortonclose
maxconn 1000
mode tcp
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
listen status
bind 0.0.0.0:10001
mode http
log global
stats enable
stats refresh 30s
stats uri /
stats realm Private lands
stats auth admin:admin@123
listen master
bind 0.0.0.0:6432
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server test1 192.168.164.111:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
server test2 192.168.164.112:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
server test3 192.168.164.113:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
listen replicas
bind 0.0.0.0:6433
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server test1 192.168.164.111:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
server test2 192.168.164.112:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
server test3 192.168.164.113:5432 maxconn 200 check port 8008 inter 5000 rise 2 fall 2
4、编辑HAProxy系统服务文件
[root@patroni4 ~]# vi /usr/lib/systemd/system/haproxy.service
[Unit]
Description=HAProxy
After=syslog.target network.target
[Service]
ExecStartPre=/opt/haproxy/sbin/haproxy -f /opt/haproxy/haproxy.cfg -c -q
ExecStart=/opt/haproxy/sbin/haproxy -Ws -f /opt/haproxy/haproxy.cfg -p /var/run/haproxy.pid
ExecReload=/bin/kill -USR2 $MAINPID
[Install]
WantedBy=multi-user.target
5、启动服务，设置开机自启
[root@patroni4 ~]# systemctl daemon-reload
[root@patroni4 ~]# systemctl start haproxy.service
[root@patroni4 ~]# systemctl status haproxy.service
[root@patroni4 ~]# systemctl enable haproxy.service
服务启动后可通过在网页输入haproxy地址的方式查看工作信息统计，本例中为192.168.164.114:10001
七、负载均衡场景验证
pgbench是PostgreSQL内置的一个基准测试工具。默认情况下（工具默认提供），pgbench 测试基于TPC-B场景，每个事务包括5个SELECT、UPDATE 和INSERT命令。本部分通过pgbench工具测试haproxy的负载情况,关于pgbench的参数说明，请参看support平台的另一篇文章018756704，本文不再详细介绍。
1、创建测试用户跟测试库
[postgres@patroni1 ~]$ psql -h 192.168.164.114 -p 6432
Password for user postgres:
psql (14.6)
Type "help" for help.
postgres=# create user a password'a';
postgres=# create database a owner a;
2、a库内初始化测试表
[postgres@patroni1 ~]$ pgbench -h 192.168.164.114 -U a -p 6432 -i a -u a
pgbench: invalid option -- 'u'
Try "pgbench --help" for more information.
[postgres@patroni1 ~]$ pgbench -h 192.168.164.114 -U a -p 6432 -i a -U a
Password:
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.43 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 5.85 s (drop tables 0.00 s, create tables 0.11 s, client-side generate 2.51 s, vacuum 1.67 s, primary keys 1.56 s).
3、模拟20个客户端，4个线程运行120秒
[postgres@patroni1 ~]$ pgbench -h 192.168.164.114 -U a -p 6432 -d a -r -M prepared -v -T 120 -c 20 -j 4 --aggregate-interval=5 -l
监控haproxy页面，可以看到所有的客户端连接均连接到主机
4、模拟20个客户端 4个线程只读SQL运行120秒
[postgres@patroni2 ~]$ pgbench -h 192.168.164.114 -U a -p 6433 -d a -r -M prepared -v -T 120 -c 20 -j 4 -S --aggregate-interval=5 -l
监控haproxy页面，可以看到所有的客户端平均分配到了两个备节点，如下图所示
结论：针对已做好读写分离的业务系统，此配置方法可以很好的实现负载均衡。
