二、部署过程
配置主节点:
在系统数据库用户家目录上传db.zip文件。
scp db.zip kes86@192.168.57.10:~
1. 将上传的db.zip解压并初始化数据库
解压上传的db.zip文件
unzip db.zip -d 路径
初始化数据库,只初始化主节点,备节点无需做此操作
initdb -Usystem -Eutf-8 -mpg -D /home/kes86/data -A scram-sha-256 -x system --data-checksums
[kes86@node1 ~]$ initdb -Usystem -Eutf-8 -mpg -D /home/kes86/data -A scram-sha-256 -x system --data-checksums
属于此数据库系统的文件宿主为用户 "kes86".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "zh_CN.UTF-8"进行初始化.
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
缺省的文本搜索配置将会被设置到"simple"
The comparision of strings is case-sensitive.
允许生成数据页校验和.
创建目录 /home/kes86/data ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......posix
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
selecting default time zone ... Asia/Shanghai
创建配置文件 ... 成功
Begin setup encrypt device
initializing the encrypt device ... 成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
create security database ... 成功
load security database ... 成功
同步数据到磁盘...成功
成功。您现在可以用下面的命令开启数据库服务器:
sys_ctl -D /home/kes86/data -l 日志文件 start
2. 修改数据库kingbase.conf配置文件内容
shared_preload_libraries='repmgr'
listen_addresses = '*'
port = 54322
full_page_writes = on
wal_log_hints = on
max_wal_senders = 32
wal_keep_segments = 512
max_connections = 100
wal_level = replica
archive_mode = on
archive_command = '/bin/cp -f %p /home/kes86/data/archive/%f'
control_file_copy = '/home/kes86/data/copy_file_bak'
max_replication_slots = 32
hot_standby = on
hot_standby_feedback = on
logging_collector = on
log_destination = 'csvlog'
log_checkpoints = on
log_replication_commands = on
wal_compression = on
synchronous_commit = remote_write
max_prepared_transactions = 100
#shared_buffers = 512MB
fsync = on
#synchronous_standby_names='ANY 1(node2)'
备注:
archive_command参数配置的目录需提前创建,或者已经存在的目录。
mkdir -p /home/kes86/data/archive
synchronous_standby_names此参数暂时先不配置,可以考虑注释掉。如果配置此参数会导致做任何操作都需等待备节点的响应。
3. 修改sys_hba.conf文件:
在sys_hba.conf末尾添加以下内容(考虑密码安全性,可以考虑复制角色不验证密码,或者通过对密码加密)
# add replication priv
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust
host replication all ::0/0 trust
4. 启动数据库,最好使用绝对路径启动数据库。
备注:后期进行数据库备份使用相对路径启动的话,备份会有问题
[kes86@node1 ~]$ sys_ctl -D /home/kes86/data/ start
waiting for server to start....2022-10-30 15:38:45.726 CST [7921] 日志: 正在启动 KingbaseES V008R006C006B0021 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-10-30 15:38:45.726 CST [7921] 日志: 正在监听IPv4地址"0.0.0.0",端口 54322
2022-10-30 15:38:45.726 CST [7921] 日志: 正在监听IPv6地址"::",端口 54322
2022-10-30 15:38:45.731 CST [7921] 日志: 在Unix套接字 "/tmp/.s.KINGBASE.54322"上侦听
2022-10-30 15:38:45.759 CST [7921] 日志: 日志输出重定向到日志收集进程
2022-10-30 15:38:45.759 CST [7921] 提示: 后续的日志输出将出现在目录 "sys_log"中.
done
server started
5. 创建replication用户并授权
create user repmgr replication login;
alter user repmgr password 'repmgr';
alter user repmgr superuser createdb createrole;
6. 创建repmgr数据库,储存repmgr相关的信息
create database repmgr encoding UTF8;
alter database repmgr owner to repmgr ;
至此主节点数据库已配置完成。
7. 配置repmgr.conf文件,默认路径跟软件bin目录同级路径
备注:use_scmd='off' 未使用securecmdd必须显式设置为off。注释或者默认都是on
[kes86@node1 etc]$ cat repmgr.conf
node_id=1
node_name='node1'
promote_command='/home/kes86/kes86/cluster/bin/repmgr standby promote -f /home/kes86/kes86/cluster/etc/repmgr.conf'
follow_command='/home/kes86/kes86/cluster/bin/repmgr standby follow -f /home/kes86/kes86/cluster/etc/repmgr.conf --upstream-node=%n'
conninfo='host=node1 user=repmgr dbname=repmgr port=54322 connect_timeout=10 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3'
log_file='/home/kes86/kes86/cluster/log/hamgr.log'
log_level=info
#kbha_log_file='/home/kes86/kes86/cluster/log/kbha.log'
data_directory='/home/kes86/data'
sys_bindir='/home/kes86/kes86/cluster/bin'
#scmd_options='-q -o ConnectTimeout=10 -o StrictHostKeyChecking=no -o ServerAliveInterval=2 -o ServerAliveCountMax=5 -p 8890'
reconnect_attempts=10
reconnect_interval=6
failover='manual'
recovery='standby'
monitoring_history='no'
#trusted_servers='192.168.57.1'
#virtual_ip='192.168.57.32/24'
#net_device='enp0s17'
#net_device_ip='192.168.57.40'
#ipaddr_path='/sbin'
#arping_path='/home/kes86/kes86/cluster/bin'
synchronous='quorum'
#repmgrd_pid_file='/home/kes86/kes86/cluster/etc/hamgrd.pid'
#kbha_pid_file='/home/kes86/kes86/cluster/etc/kbha.pid'
ping_path='/usr/bin'
auto_cluster_recovery_level=1
use_check_disk=off
use_scmd='off'
#running_under_failure_trusted_servers=on
connection_check_type='mix'
location='location1'
priority=100
如果未部署securecmdd节点通信工具,以下参数无需配置
#scmd_options='-q -o ConnectTimeout=10 -o StrictHostKeyChecking=no -o ServerAliveInterval=2 -o ServerAliveCountMax=5 -p 8890'
此参数未部署securecmdd必须配置为off
use_scmd='off'
手动switchover无需配置kbha
kbha工具选项
[kes86@node1 ~]$ kbha --help
kbha: replication management daemon for Kingbase
kbha starts the repmgrd and do auto-recovery for Kingbase.
Usage:
kbha [OPTIONS]
General options:
-f, --config-file=PATH path to the repmgr configuration file
-A, --action={rejoin|register|follow|daemon|loadvip|unloadvip|arping|stopdb|startdb|updateinfo|removeinfo|check_ip}
what to do for program, default is 'daemon'
--dev=device name, if -A is check_ip, could input net device name
--ip=ip address, if -A is check_ip, must be input ip which will be check, support IPV4 and IPV6
--upstream-node-id=NODE_ID, if -A is rejoin or follow, this node will follow the node of NODE_ID
Database connection options:
-d, --dbname=DBNAME database to connect to (default: "kes86")
-h, --host=HOSTNAME database server host
-p, --port=PORT database server port (default: "54322")
-U, --username=USERNAME database user name to connect as (default: "kes86")
Other options:
-?, --help show this help, then exit
-V, --version output version information, then exit
-v, --verbose display additional log output (useful for debugging)
通过kbha工具添加虚拟ip
添加vip命令
kbha -A 192.168.57.32 kbha -A arping删除vip命令
kbha -A unloadvip
8. repmgr注册primary节点
[kes86@node1 ~]$ repmgr primary register
[INFO] connecting to primary database...
[DEBUG] connecting to: "user=repmgr connect_timeout=10 dbname=repmgr host=node1 port=54322 keepalives=1 keepalives_idle=10 keepalives_interval=1 keepalives_count=3 fallback_application_name=repmgr options=-csearch_path="
[NOTICE] attempting to install extension "repmgr"
[NOTICE] "repmgr" extension successfully installed
[INFO] primary registration complete
[NOTICE] primary node record (ID: 1) registered
查看ip是否正常
[kes86@node1 ~]$ ifconfig
enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.1.30 netmask 255.255.255.0 broadcast 192.168.1.255
inet6 fe80::a00:27ff:fede:9422 prefixlen 64 scopeid 0x20<link>
ether 08:00:27:de:94:22 txqueuelen 1000 (Ethernet)
RX packets 24 bytes 2976 (2.9 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 32 bytes 3616 (3.5 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
enp0s9: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.1.61 netmask 255.255.255.0 broadcast 192.168.1.255
inet6 fe80::a00:27ff:fef0:68ca prefixlen 64 scopeid 0x20<link>
ether 08:00:27:f0:68:ca txqueuelen 1000 (Ethernet)
RX packets 255 bytes 33730 (32.9 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 29 bytes 4466 (4.3 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
enp0s17: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.57.40 netmask 255.255.255.0 broadcast 192.168.57.255
inet6 fe80::a00:27ff:fe59:efcf prefixlen 64 scopeid 0x20<link>
ether 08:00:27:59:ef:cf txqueuelen 1000 (Ethernet)
RX packets 1718 bytes 150972 (147.4 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 997 bytes 138337 (135.0 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 756 bytes 280777 (274.1 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 756 bytes 280777 (274.1 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
virbr0: flags=4099<UP,BROADCAST,MULTICAST> mtu 1500
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255
ether 52:54:00:68:17:f3 txqueuelen 1000 (Ethernet)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
9. 登陆主节点数据库执行以下命令:
alter system set synchronous_standby_names='ANY 1(node2)';
10. 重新加载配置文件
select pg_reload_conf();