PostgreSQL是一个非常流行的使用面非常广的关系数据库，有很多种构建Postgres HA集群的方式，例如PAF,pgool等，以下将以CentOS7系统和PostgreSQL9.6版本为例，结合高可用虚拟IP（以下简称vip），patroni，haproxy，etcd等组件介绍一种pg HA方案。
整个集群架构如下：
postgresql HA其中三个pg节点一主两从，使用patroni管理pg节点状态，使用etcd集群存储patroni元数据，每个节点通过pgbouncer管理本机的pg连接池，每个节点的haproxy配置一致，都会代理三个pg实例，客户端请求通过keepalived管理的vip来访问pg，通过haproxy与patroni rest api配合，可以提供以下两个端口的服务：
- 5000端口，仅代理master实例，可提供读和写
- 5001端口，仅代理replica实例， 提供只读
业务可以根据实际需求来选择读写端口，例如只读的业务场景可以走5001端口获取更高的并发能力。
一 实验环境
- 3个cvm虚拟机节点
- 一个高可用虚拟IP，172.27.16.47，云上申请地址：https://console.cloud.tencent.com/vpc/havip
二 安装postgresql等组件
本文使用postgres9.6版本，三个节点均安装，可通过腾讯云yum镜像仓库安装：
代码语言：javascript复制rpm -ivh http://mirrors.cloud.tencent.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install postgresql96-server也可手工下载rpm安装，rpm下载地址：http://mirrors.cloud.tencent.com/postgresql/repos/yum/9.6/redhat/rhel-7-x86_64/
安装patroni，可从第三方获取现成的rpm：https://github.com/cybertec-postgresql/patroni-packaging/releases
安装pgbouncer，下载地址：http://mirrors.cloud.tencent.com/postgresql/repos/yum/9.6/redhat/rhel-7-x86_64/pgbouncer-1.12.0-1.rhel7.x86_64.rpm
安装haproxy，可通过以下仓库自行build rpm，https://github.com/itxx00/haproxy
安装keepalived，系统自带os repo里面有，直接yum install keepalived即可。
安装etcd，etcd rpm在系统自带extras repo里面有，直接yum install etcd即可。
三 服务和组件配置
etcd配置：/etc/etcd/etcd.conf
代码语言：javascript复制ETCD_NAME=node1 #每个节点不一样
ETCD_DATA_DIR="/data/etcd"
ETCD_LISTEN_PEER_URLS="http://172.27.16.22:2380"
ETCD_LISTEN_CLIENT_URLS="http://172.27.16.22:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.27.16.22:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://172.27.16.22:2379"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="change-your-token"
ETCD_INITIAL_CLUSTER="node1=http://172.27.16.22:2380,node2=http://172.27.16.23:2380,node3=http://172.27.16.28:2380"
ETCD_ENABLE_V2="true"etcd启动
代码语言：javascript复制 systemctl start etcdpatroni配置：/etc/patroni/patroni.yml
代码语言：javascript复制scope: pgcluster
name: node1
namespace: /service/
log:
level: INFO
max_queue_size: 1000
dir: /var/log/postgresql
file_num: 7
file_size: 10485760
restapi:
listen: 172.27.16.22:8008
connect_address: 172.27.16.22:8008
authentication:
username: root
password: yourpasswordhere
etcd:
hosts: 172.27.16.22:2379,172.27.16.23:2379,172.27.16.28:2379
username: root
password: yourpasswordhere
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
synchronous_mode: false
synchronous_mode_strict: false
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
max_connections: 100
superuser_reserved_connections: 5
max_locks_per_transaction: 64
max_prepared_transactions: 0
huge_pages: try
shared_buffers: 512MB
work_mem: 128MB
maintenance_work_mem: 256MB
effective_cache_size: 4GB
checkpoint_timeout: 15min
checkpoint_completion_target: 0.9
min_wal_size: 2GB
max_wal_size: 4GB
wal_buffers: 32MB
default_statistics_target: 1000
seq_page_cost: 1
random_page_cost: 4
effective_io_concurrency: 2
synchronous_commit: on
autovacuum: on
autovacuum_max_workers: 5
autovacuum_vacuum_scale_factor: 0.01
autovacuum_analyze_scale_factor: 0.02
autovacuum_vacuum_cost_limit: 200
autovacuum_vacuum_cost_delay: 20
autovacuum_naptime: 1s
max_files_per_process: 4096
log_rotation_age: 1d
log_rotation_size: 0
log_line_prefix: '%t [%p-%l] %r %q%u@%d '
log_filename: 'postgresql-%a.log'
log_directory: /var/log/postgresql
initdb:
- encoding: UTF8
- locale: en_US.UTF-8
- data-checksums
pg_hba:
- host replication replicator 127.0.0.1/32 md5
- host all all 0.0.0.0/0 md5
postgresql:
listen: 172.27.16.22,127.0.0.1:5432
connect_address: 172.27.16.22:5432
use_unix_socket: true
data_dir: /var/lib/pgsql/9.6/data
bin_dir: /usr/pgsql-9.6/bin
config_dir: /var/lib/pgsql/9.6/data
pgpass: /var/lib/pgsql/.pgpass
authentication:
replication:
username: replicator
password: yourpasswordhere
superuser:
username: postgres
password: yourpasswordhere
parameters:
unix_socket_directories: /var/run/postgresql
stats_temp_directory: /var/lib/pgsql_stats_tmp
create_replica_methods:
- basebackup
basebackup:
max-rate: '100M'
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: falsepatroni启动
代码语言：javascript复制 systemctl start patronipgbouncer配置：/etc/pgbouncer/pgbouncer.ini
代码语言：javascript复制[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres
testdb = host=127.0.0.1 port=5432 dbname=testdb pool_size=20 pool_mode=transaction
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 172.27.16.22
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 10
reserve_pool_size = 1
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192
client_tls_sslmode = disable
server_tls_sslmode = disable
# Documentation https://pgbouncer.github.io/config.htmlpgbouncer启动
代码语言：javascript复制systemctl start pgbouncerhaproxy配置：/etc/haproxy/haproxy.cfg
代码语言：javascript复制global
maxconn 100000
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
daemon
defaults
mode tcp
log global
retries 2
timeout queue 5s
timeout connect 5s
timeout client 60m
timeout server 60m
timeout check 15s
listen stats
mode http
bind 172.27.16.22:7000
stats enable
stats uri /stats
listen master
bind 172.27.16.47:5000
mode tcp
maxconn 2000
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server db01 172.27.16.22:6432 check port 8008
server db02 172.27.16.23:6432 check port 8008
server db03 172.27.16.28:6432 check port 8008
listen replicas
bind 172.27.16.47:5001
mode tcp
maxconn 6000
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server db01 172.27.16.22:6432 check port 8008
server db02 172.27.16.23:6432 check port 8008
server db03 172.27.16.28:6432 check port 8008haproxy启动
代码语言：javascript复制systemctl start haproxykeepalived配置：/etc/keepalived/keepalived.conf
代码语言：javascript复制vrrp_script pg_haproxy_check {
script "/usr/libexec/keepalived/haproxy_check.sh"
interval 2
weight 2
}
vrrp_instance pgcluster_1 {
interface eth0
virtual_router_id 61
nopreempt
priority 100
advert_int 2
state BACKUP
# 如果是云上vip，如未开通组播的话可使用单播，需设置unicast_peer
unicast_src_ip 172.27.16.22
unicast_peer {
172.27.16.23
172.27.16.28
}
virtual_ipaddress {
172.27.16.47
}
garp_master_delay 1
garp_master_refresh 5
track_interface {
eth0
}
track_script {
pg_haproxy_check
}
authentication {
auth_type PASS
auth_pass yourpasswordhere
}
}haproxy_check.sh内容参考：
代码语言：javascript复制#!/bin/bash
/bin/kill -0 `cat /var/run/haproxy.pid`keepalived启动
代码语言：javascript复制 systemctl start keepalived以上是一个节点的配置示例，另外两个节点可根据以上配置略作调整即可。当所有节点组件均完成配置并成功启动后，即可通过vip:5000端口访问postgres服务了。我们可以通过patroni命令行管理工具对pg实例进行状态查看/切换等操作，示例如下：
执行sql查询：
patronictl查看节点状态：
patronictl可访问haproxy.cfg中定义的stats端口查看haproxy实时状态：
haproxy stats参考文档链接：
- https://github.com/vitabaks/postgresql_cluster/blob/master/README.md
- https://cloud.tencent.com/document/product/215/20186
