[[toc]]
# 组件列表及版本
1.postgresql：pg 12.9
2.consul:1.11.5
3.patroni 2.02
4.haproxy 2.4.7
5.keepalived 1.3.5
postgresql | consul | patroni | haproxy | keepalive | |
172.16.166.101 | √ | √ | √ | √ | |
172.16.166.102 | √ | √ | √ | √ | |
172.16.166.103 | √ | √ | |||
172.16.166.104 | √ | ||||
172.16.166.105 | √ | ||||
172.16.166.106 | √ |
vip: 172.16.166.107
组件说明：
consul：实现分布式系统的服务发现与配置
consul优势：
1）使用 Raft 算法来保证一致性, 比复杂的 Paxos 算法更直接. 相比较而言, zookeeper 采用的是 Paxos, 而 etcd 使用的则是 Raft.
2）支持多数据中心，内外网的服务采用不同的端口进行监听。 多数据中心集群可以避免单数据中心的单点故障,而其部署则需要考虑网络延迟, 分片等情况等. zookeeper 和 etcd 均不提供多数据中心功能的支持.
3）支持健康检查. etcd 不提供此功能.
4）支持 http 和 dns 协议接口. zookeeper 的集成较为复杂, etcd 只支持 http 协议.
5）官方提供web管理界面, etcd 无此功能.
Patroni ：接管 PostgreSQL 数据库的启停，同时监控本地的 PostgreSQL 数据库，并将本地的 PostgreSQL 数据库信息写入DCS，注册到consul中
haproxy：使用C语言开发的一个开源软件，是一款具备高并发(一万以上)、高性能的TCP和HTTP负载均衡器，支持基于cookie的持久性，自动故障切换，支持正则表达式及web状态统计。使用 haproxy 设置端口区分连接主库和只读从库，且端口上有负载均衡的功能(有两个从库时候)。
keepalived：通过VRRP协议实现多台机器之间的故障转移服务
pg12一主两从安装部署（略）
一、 consul集群安装
1.下载地址：https://releases.hashicorp.com/consul/1.11.5/
2.上传安装包至服务器并解压
unzip consul_1.11.5_linux_amd64.zip
mv consul /usr/bin/
3.创建目录
mkdir /etc/consul.d
mkdir /data/consul
mkdir /data/log
4.逐个节点启动consul
nohup consul agent -server -bootstrap-expect=3 -data-dir=/data/consul -node=172.16.166.104 -bind=172.16.166.104 -enable-script-checks=true -config-dir=/etc/consul.d -rejoin -ui -log-file=/data/log/ -client=0.0.0.0 &
nohup consul agent -server -bootstrap-expect=3 -data-dir=/data/consul -node=172.16.166.105 -bind=172.16.166.105 -enable-script-checks=true -config-dir=/etc/consul.d -rejoin -ui -log-file=/data/log/ -client=0.0.0.0 &
nohup consul agent -server -bootstrap-expect=3 -data-dir=/data/consul -node=172.16.166.106 -bind=172.16.166.106 -enable-script-checks=true -config-dir=/etc/consul.d -rejoin -ui -log-file=/data/log/ -client=0.0.0.0 &
[root@consul1 data]# consul members
Node Address Status Type Build Protocol DC Partition Segment
172.16.166.104 172.16.166.104:8301 alive server 1.11.5 2 dc1 default
5.其他节点加入集群
[root@consul3 data]# consul join 172.16.166.104
Successfully joined cluster by contacting 1 nodes.
[root@consul1 log]# consul members
Node Address Status Type Build Protocol DC Partition Segment
172.16.166.104 172.16.166.104:8301 alive server 1.11.5 2 dc1 default
二、安装并配置patroni
1.安装python3
tar -xzvf patroni-2.0.2.tgz
cd /usr/local/patroni/patroni-consul
[root@postgres1 patroni-consul]# pip3 install psutil-5.8.0.tar.gz
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Processing ./psutil-5.8.0.tar.gz
Installing collected packages: psutil
Running setup.py install for psutil … done
Successfully installed psutil-5.8.0
[root@postgres1 patroni-consul]# pip3 install ydiff-1.2.tar.gz
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Processing ./ydiff-1.2.tar.gz
Installing collected packages: ydiff
Running setup.py install for ydiff … done
Successfully installed ydiff-1.2
[root@postgres1 patroni-consul]# pip3 install *.whl
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Processing ./certifi-2021.5.30-py2.py3-none-any.whl
Processing ./chardet-4.0.0-py2.py3-none-any.whl
Processing ./click-8.0.1-py3-none-any.whl
Processing ./idna-2.10-py2.py3-none-any.whl
Processing ./importlib_metadata-4.5.0-py3-none-any.whl
Processing ./patroni-2.0.2-py3-none-any.whl
Processing ./prettytable-2.1.0-py3-none-any.whl
Processing ./python_consul-1.1.0-py2.py3-none-any.whl
Processing ./python_dateutil-2.8.1-py2.py3-none-any.whl
Processing ./PyYAML-5.4.1-cp36-cp36m-manylinux1_x86_64.whl
Processing ./requests-2.25.1-py2.py3-none-any.whl
Processing ./six-1.16.0-py2.py3-none-any.whl
Processing ./typing_extensions-3.10.0.0-py3-none-any.whl
Processing ./urllib3-1.26.5-py2.py3-none-any.whl
Processing ./wcwidth-0.2.5-py2.py3-none-any.whl
Processing ./zipp-3.4.1-py3-none-any.whl
Requirement already satisfied: psutil>=2.0.0 in /usr/local/lib64/python3.6/site-packages (from patroni2.0.2)
Requirement already satisfied: ydiff>=1.2.0 in /usr/local/lib/python3.6/site-packages (from patroni2.0.2)
Installing collected packages: certifi, chardet, typing-extensions, zipp, importlib-metadata, click, idna, wcwidth, prettytable, urllib3, six, PyYAML, python-dateutil, patroni, requests, python-consul
Successfully installed PyYAML-5.4.1 certifi-2021.5.30 chardet-4.0.0 click-8.0.1 idna-2.10 importlib-metadata-4.5.0 patroni-2.0.2 prettytable-2.1.0 python-consul-1.1.0 python-dateutil-2.8.1 requests-2.25.1 six-1.16.0 typing-extensions-3.10.0.0 urllib3-1.26.5 wcwidth-0.2.5 zipp-3.4.1
2.安装必要的包
[root@postgres3 patroni-deps]# cd /usr/local/patroni/patroni-deps
[root@postgres3 patroni-deps]# yum -y localinstall *
3.修改patroni配置文件
注意：consul配置文件非常注意空格，不能用tab键
创建相应目录
mkdir -p /data/patroni/log
主库：
scope: pg_cluster
namespace: /service
name: postgres1
log:
level: INFO
traceback_level: ERROR
dir: /data/patroni/log/
file_num: 10
file_size: 104857600
restapi:
connect_address: 172.16.166.101:8008
listen: 172.16.166.101:8008
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
max_timelines_history: 0
master_start_timeout: 300
master_stop_timeout: 0
synchronous_mode: false
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
max_connections: 3000
superuser_reserved_connections: 100
max_locks_per_transaction: 64
max_worker_processes: 2
max_prepared_transactions: 0
wal_level: replica
wal_log_hints: on
track_commit_timestamp: off
max_wal_senders: 10
max_replication_slots: 10
wal_keep_size: 4096MB
hot_standby: “on”
listen_addresses: “*”
port: 5432
cluster_name: “pg_cluster”
archive_mode: on
archive_command: “test ! -f /data/pg_arch/%f && cp %p /data/pg_arch/%f”
consul:
host: 172.16.166.104:8300
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.16.166.101:5432
data_dir: /enmo/pgdata
pg_ctl_timeout: 60
use_pg_rewind: true
remove_data_directory_on_rewind_failure: false
remove_data_directory_on_diverged_timelines: true
authentication:
replication:
username: repl
password: repl
superuser:
username: postgres
password: root123
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
从库1：
[root@postgres2 patroni]# cat patroni-config.yml
scope: pg_cluster
namespace: /service
name: postgres2
log:
level: INFO
traceback_level: ERROR
dir: /data/patroni/log/
file_num: 10
file_size: 104857600
restapi:
connect_address: 172.16.166.102:8008
listen: 172.16.166.102:8008
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
max_timelines_history: 0
master_start_timeout: 300
master_stop_timeout: 0
synchronous_mode: false
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
max_connections: 3000
superuser_reserved_connections: 100
max_locks_per_transaction: 64
max_worker_processes: 2
max_prepared_transactions: 0
wal_level: replica
wal_log_hints: on
track_commit_timestamp: off
max_wal_senders: 10
max_replication_slots: 10
wal_keep_size: 4096MB
hot_standby: “on”
listen_addresses: “*”
port: 5432
cluster_name: “pg_cluster”
archive_mode: on
archive_command: “test ! -f /data/pg_arch/%f && cp %p /data/pg_arch/%f”
consul:
host: 172.16.166.104:8500
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.16.166.102:5432
data_dir: /data/pgdata
pg_ctl_timeout: 60
use_pg_rewind: true
remove_data_directory_on_rewind_failure: false
remove_data_directory_on_diverged_timelines: true
authentication:
replication:
username: repl
password: repl
superuser:
username: postgres
password: root123
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
从库2：
[root@postgres3 patroni]# cat patroni-config.yml
scope: pg_cluster
namespace: /service
name: postgres3
log:
level: INFO
traceback_level: ERROR
dir: /data/patroni/log/
file_num: 10
file_size: 104857600
restapi:
connect_address: 172.16.166.103:8008
listen: 172.16.166.103:8008
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
max_timelines_history: 0
master_start_timeout: 300
master_stop_timeout: 0
synchronous_mode: false
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
max_connections: 3000
superuser_reserved_connections: 100
max_locks_per_transaction: 64
max_worker_processes: 2
max_prepared_transactions: 0
wal_level: replica
wal_log_hints: on
track_commit_timestamp: off
max_wal_senders: 10
max_replication_slots: 10
wal_keep_size: 4096MB
hot_standby: “on”
listen_addresses: “*”
port: 5432
cluster_name: “pg_cluster”
archive_mode: on
archive_command: “test ! -f /data/pg_arch/%f && cp %p /data/pg_arch/%f”
consul:
host: 172.16.166.104:8500
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.16.166.103:5432
data_dir: /data/pgdata
pg_ctl_timeout: 60
use_pg_rewind: true
remove_data_directory_on_rewind_failure: false
remove_data_directory_on_diverged_timelines: true
authentication:
replication:
username: repl
password: repl
superuser:
username: postgres
password: root123
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
4.修改目录权限：
chown -R postgres:postgres /data/patroni/
5.逐个节点启动patroni
[root@postgres1 soft]# patroni /data/patroni/patroni-config.yml
6.检查patroni状态
cat >> /etc/profile << EOF
> alias patronictl=‘patronictl -c /data/patroni/patroni-config.yml’
> EOF
[postgres@postgres1 ~]$ patronictl list
+ Cluster: pg_cluster (7087467088752666250) -----±—±----------+
| Member | Host | Role | State | TL | Lag in MB |
±----------±---------------±--------±--------±—±----------+
| postgres1 | 172.16.166.101 | Leader | running | 2 | |
| postgres2 | 172.16.166.102 | Replica | running | 2 | 0 |
| postgres3 | 172.16.166.103 | Replica | running | 1 | 16 |
±----------±---------------±--------±--------±—±----------+
[postgres@postgres1 ~]$ patronictl list
+ Cluster: pg_cluster (7087467088752666250) -----±—±----------+
| Member | Host | Role | State | TL | Lag in MB |
±----------±---------------±--------±--------±—±----------+
| postgres1 | 172.16.166.101 | Leader | running | 2 | |
| postgres2 | 172.16.166.102 | Replica | running | 2 | 0 |
| postgres3 | 172.16.166.103 | Replica | running | 1 | 0 |
±----------±---------------±--------±--------±—±----------+
三、 安装配置haproxy
1.上传并解压
cd /data/soft/
tar -xzvf haproxy-2.4.7.tar.gz
2.安装必要的依赖包
yum -y install pcre-devel openssl-devel systemd-devel
3.安装haproxy
cd haproxy-2.4.7/
make TARGET=linux-glibc ARCH=x86_64 PREFIX=/usr/local/haproxy USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_SYSTEMD=1 USE_CPU_AFFINITY=1
make install PREFIX=/usr/local/haproxy
TARGET=linux-glibc # 内核版本
ARCH=x86_64 # 指定CPU的架构为"x86_64"
PREFIX=/enmo/app/haproxy # 指定haprpxy安装路径
USE_PCRE=1 # 开启正则表达式。
USE_OPENSSL=1 # 开启OPENSSL功能。
USE_ZLIB=1 # 开启压缩和解压缩功能。
USE_SYSTEMD=1 # 支持以"systemd"的方式启动。
USE_CPU_AFFINITY=1 # 开启CPU的亲和性。
4.配置haproxy环境变量
[root@postgres2 haproxy-2.4.7]# haproxy -v
HAProxy version 2.4.7-b5e51a5 2021/10/04 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2026.
Known bugs: http://www.haproxy.org/bugs/bugs-2.4.7.html
Running on: Linux 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64
5.配置haproxy参数
global
log 127.0.0.1 local2
chroot /usr/local/haproxy
pidfile /var/run/haproxy.pid
maxconn 1000
user root
group root
daemon
nbproc 1
defaults
mode tcp
log 127.0.0.1 local2 err
option tcplog
option dontlognull
option redispatch
retries 10
maxconn 1000
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 5s
listen status
bind *:1080
mode http
log global
stats enable
stats refresh 30s
stats uri /
stats realm Private lands
stats auth admin:admin
listen master
bind *:6000
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 pgtest1 172.16.166.101:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest2 172.16.166.102:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest3 172.16.166.103:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
listen replicas
bind *:7000
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 pgtest1 172.16.166.101:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest2 172.16.166.102:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgtest3 172.16.166.103:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
6.启动haproxy
haproxy -Ws -f /usr/local/haproxy/haproxy.cfg -p /var/run/haproxy.pid
四、 安装keepalived，并进行连接测试
1.安装keepalived
yum -y install keepalived
2.配置参数文件
主节点
global_defs {
router_id postgres1
script_user root
enable_script_security
}
vrrp_script chk_haproxy {
script “/usr/bin/killall -0 haproxy”
interval 2
weight 5
fall 3
rise 5
timeout 2
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 100
priority 100
advert_int 5
authentication {
auth_type PASS
auth_pass root123
}
virtual_ipaddress {
172.16.166.107/24 dev ens33 label ens33:1
}
track_script {
chk_haproxy
}
}
从节点：
global_defs {
router_id postgres2
script_user root
enable_script_security
}
vrrp_script chk_haproxy {
script “/usr/bin/killall -0 haproxy”
interval 2
weight 5
fall 3
rise 5
timeout 2
}
vrrp_instance VI_1 {
state BACKUP
interface ens32
virtual_router_id 100
priority 99
advert_int 5
authentication {
auth_type PASS
auth_pass root123
}
virtual_ipaddress {
172.22.166.107/24 dev ens33 label ens33:1
}
track_script {
chk_haproxy
}
}
3.启动keepalived
systemctl start keepalived
4.连接测试
