利用高可用虚拟IP构建PostgreSQL集群实践

原创

我是well

发布于 2019-12-06 17:21:38

3.4K0

发布于 2019-12-06 17:21:38

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

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

私有网络

postgresql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

私有网络

postgresql

登录后参与评论

0 条评论

热度

利用高可用虚拟IP构建PostgreSQL集群实践

利用高可用虚拟IP构建PostgreSQL集群实践

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐