## 1. 环境准备
# 节点信息
# 主节点：192.168.1.100，主机名：master
# 从节点1：192.168.1.101，主机名：slave1
# 从节点2：192.168.1.102，主机名：slave2
# 配置主机名
$ hostnamectl set-hostname master
$ hostnamectl set-hostname slave1
$ hostnamectl set-hostname slave2
# 配置hosts文件
$ vi /etc/hosts
192.168.1.100 master
192.168.1.101 slave1
192.168.1.102 slave2
# 关闭防火墙
$ systemctl stop firewalld
$ systemctl disable firewalld
# 关闭SELinux
$ setenforce 0
$ vi /etc/selinux/config
SELINUX=disabled
# 安装依赖
$ yum install -y epel-release
$ yum install -y wget net-tools ntp haproxy
# 同步时间
$ systemctl start ntpd
$ systemctl enable ntpd
## 2. 数据库安装
# 下载金仓数据库
$ wget https://www.kingbase.com.cn/download/KingbaseES_V8R6_Linux_x86_64.tar.gz
# 解压安装包
$ tar -zxvf KingbaseES_V8R6_Linux_x86_64.tar.gz
# 安装数据库
$ cd KingbaseES_V8R6_Linux_x86_64
$ ./setup.sh –install-dir=/kingbase/app
# 创建数据目录
$ mkdir -p /kingbase/fgdata
# 初始化数据库
$ /kingbase/app/bin/initdb -D /kingbase/fgdata
# 启动数据库
$ /kingbase/app/bin/sys_ctl start -D /kingbase/fgdata
## 3. 主节点配置
# 配置kingbase.conf
$ vi /kingbase/fgdata/kingbase.conf
listen_addresses = ‘*’
port = 54321
wal_level = logical
max_connections = 1000
max_wal_senders = 10
wal_keep_segments = 1024
synchronous_commit = on
synchronous_standby_names = ‘slave1,slave2’
# 配置pg_hba.conf
$ vi /kingbase/fgdata/pg_hba.conf
host replication repuser 192.168.1.101/32 md5
host replication repuser 192.168.1.102/32 md5
# 重启主节点
$ /kingbase/app/bin/sys_ctl restart -D /kingbase/fgdata
# 创建复制用户
$ /kingbase/app/bin/ksql -U system -d postgres -c “CREATE ROLE repuser REPLICATION LOGIN PASSWORD ‘repuser123’;”
## 4. 从节点配置
# 在slave1执行基础备份
$ /kingbase/app/bin/pg_basebackup -h 192.168.1.100 -U repuser -D /kingbase/fgdata -X stream -P
# 创建recovery.conf文件
$ vi /kingbase/fgdata/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=54321 user=repuser password=repuser123’
trigger_file = ‘/kingbase/fgdata/trigger’
# 启动slave1
$ /kingbase/app/bin/sys_ctl start -D /kingbase/fgdata
# 在slave2执行基础备份
$ /kingbase/app/bin/pg_basebackup -h 192.168.1.100 -U repuser -D /kingbase/fgdata -X stream -P
# 创建recovery.conf文件
$ vi /kingbase/fgdata/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=54321 user=repuser password=repuser123’
trigger_file = ‘/kingbase/fgdata/trigger’
# 启动slave2
$ /kingbase/app/bin/sys_ctl start -D /kingbase/fgdata
## 5. 验证复制状态
# 在主节点查看复制状态
$ /kingbase/app/bin/ksql -U system -d postgres -c “SELECT * FROM pg_stat_replication;”
# 在从节点查看复制状态
$ /kingbase/app/bin/ksql -U system -d postgres -c “SELECT * FROM pg_stat_wal_receiver;”
# 测试数据同步
# 在主节点创建表并插入数据
$ /kingbase/app/bin/ksql -U system -d postgres -c “CREATE TABLE fgedu_test (id SERIAL PRIMARY KEY, name VARCHAR(100));”
$ /kingbase/app/bin/ksql -U system -d postgres -c “INSERT INTO fgedu_test (name) VALUES (‘fgedu1’), (‘fgedu2’);”
# 在从节点验证数据
$ /kingbase/app/bin/ksql -U system -d postgres -c “SELECT * FROM fgedu_test;”
## 6. 配置负载均衡
# 配置HAProxy
$ vi /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
stats socket /var/lib/haproxy/stats
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.1/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000
listen postgresql
bind *:5000
mode tcp
balance roundrobin
option tcpka
option postgresql-check
server master 192.168.1.100:54321 check port 54321 inter 2s rise 2 fall 3
server slave1 192.168.1.101:54321 check port 54321 inter 2s rise 2 fall 3
server slave2 192.168.1.102:54321 check port 54321 inter 2s rise 2 fall 3
# 启动HAProxy
$ systemctl start haproxy
$ systemctl enable haproxy
# 测试负载均衡
$ /kingbase/app/bin/ksql -U system -d postgres -h fgedu.localhost -p 5000 -c “SELECT version();”