利用高可用虚拟IP构建PostgreSQL集群实践
- 2019 年 12 月 6 日
- 筆記
PostgreSQL是一个非常流行的使用面非常广的关系数据库，有很多种构建Postgres HA集群的方式，例如PAF,pgool等，以下将以CentOS7系统和PostgreSQL9.6版本为例，结合高可用虚拟IP（以下简称vip），patroni，haproxy，etcd等组件介绍一种pg 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镜像仓库安装：
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
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启动
systemctl start etcd
patroni配置：/etc/patroni/patroni.yml
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: false
patroni启动
systemctl start patroni
pgbouncer配置：/etc/pgbouncer/pgbouncer.ini
[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.html
pgbouncer启动
systemctl start pgbouncer
haproxy配置：/etc/haproxy/haproxy.cfg
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 8008
haproxy启动
systemctl start haproxy
keepalived配置：/etc/keepalived/keepalived.conf
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内容参考：
#!/bin/bash /bin/kill -0 `cat /var/run/haproxy.pid`
keepalived启动
systemctl start keepalived
以上是一个节点的配置示例，另外两个节点可根据以上配置略作调整即可。当所有节点组件均完成配置并成功启动后，即可通过vip:5000端口访问postgres服务了。我们可以通过patroni命令行管理工具对pg实例进行状态查看/切换等操作，示例如下：
执行sql查询：
查看节点状态：
可访问haproxy.cfg中定义的stats端口查看haproxy实时状态：
参考文档链接：
- https://github.com/vitabaks/postgresql_cluster/blob/master/README.md
- https://cloud.tencent.com/document/product/215/20186
