LightDB基于源码搭建高可用环境

环境信息

两台虚拟机

1. 192.168.153.140 计划作为primary
2. 192.168.153.145 计划作为standby

操作步骤

配置ssh免密
在140上执行如下命令

ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa
ssh-copy-id lightdb@192.168.153.145

在145上也同样执行类似命令

源码编译安装LightDB
在140和145上都要执行

cd /home/lightdb/code/Sources/make
./build.sh --lightdb-only

ssh到140上面
初始化数据库

[lightdb@localhost ~]$ lt_initdb 

修改配置文件

[lightdb@localhost ~]$ vim /home/lightdb/stage/lightdb-x/data/lightdb.conf

修改配置shared_preload_libraries，添加ltcluster

shared_preload_libraries='ltcluster,lt_stat_statements,lt_stat_activity,lt_prewarm,lt_cron,lt_hint_plan,lt_show_plans,lt_sql_inspect'

启动实例

[lightdb@localhost ~]$ lt_ctl start
2023-03-08 23:28:45.164502T,,,,,postmaster,,00000,2023-03-08 23:28:45 CST,0,26819,LOG:  LightDB autoprewarm: prewarm dbnum=0
2023-03-08 23:28:45.170023T,,,,,postmaster,,00000,2023-03-08 23:28:45 CST,0,26819,INFO:  invoking IpcMemoryCreate(size=1034657792)
waiting for server to start....2023-03-08 23:28:45.547502T,,,,,postmaster,,00000,2023-03-08 23:28:45 CST,0,26819,LOG:  redirecting log output to logging collector process
2023-03-08 23:28:45.547502T,,,,,postmaster,,00000,2023-03-08 23:28:45 CST,0,26819,HINT:  Future log output will appear in directory "log".
 done
server started
[lightdb@localhost ~]$ 

配置ltcluster

[lightdb@localhost ~]$ ltsql -p 5432 -dpostgres -c"create extension ltcluster;"
CREATE EXTENSION
[lightdb@localhost ~]$ 

修改用户认证信息，以便standby有权限从primary复制数据

# IPv4 local connections:
host    all             all             0.0.0.0/0            trust
# replication privilege.
host    replication     all             0.0.0.0/0            trust
lt_ctl reload

执行下面sh脚本，生成高可用配置文件ltcluster.conf

id=140
NODE_NAME=node140
ip=192.168.153.140
port=5432

ltclusterconf=$LTHOME/etc/ltcluster/ltcluster.conf

echo "
node_id=$id
node_name='$NODE_NAME'
conninfo='host=$ip port=$port user=lightdb dbname=postgres connect_timeout=2'
data_directory='$LTDATA'
pg_bindir='$LTHOME/bin'
failover='automatic'
promote_command='$LTHOME/bin/ltcluster standby promote -f $ltclusterconf'
follow_command='$LTHOME/bin/ltcluster standby follow -f $ltclusterconf  --upstream-node-id=%n'
restore_command='cp $LTHOME/archive/%f %p'
monitoring_history=true #(Enable monitoring parameters)
monitor_interval_secs=2 #(Define monitoring data interval write time parameter)
connection_check_type='ping'
reconnect_attempts=3 #(before failover,Number of attempts to reconnect to primary before failover(default 6))
reconnect_interval=5
standby_disconnect_on_failover =true
log_level=INFO
log_facility=STDERR
log_file='$LTHOME/etc/ltcluster/ltcluster.log'
failover_validation_command='$LTHOME/etc/ltcluster/ltcluster_failover.sh "$LTHOME" "$LTDATA"'
shutdown_check_timeout=1800
use_replication_slots=true
check_lightdb_command='$LTHOME/etc/ltcluster/check_lightdb.sh'
check_lightdb_interval=10
" > $ltclusterconf
echo '===================success=================='

使用如下命令注册主节点（primary）

[lightdb@localhost ~]$ ltcluster -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf primary register -F
INFO: connecting to primary database...
INFO: "ltcluster" extension is already installed
NOTICE: primary node record (ID: 140) registered
[lightdb@localhost ~]$ 
[lightdb@localhost ~]$ ltclusterd -d -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf -p -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltclusterd.pid
[2023-03-08 23:37:32] [NOTICE] redirecting logging output to "/home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster2023-03-08_233732.log"

检查状态

[lightdb@localhost ~]$ ltcluster -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf cluster show
 ID  | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                            
-----+---------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
 140 | node140 | primary | * running |          | default  | 100      | 1        | host=192.168.153.140 port=5432 user=lightdb dbname=postgres connect_timeout=2
[lightdb@localhost ~]$ ltcluster -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf service status
 ID | Name    | Role    | Status    | Upstream | ltclusterd | PID   | Paused? | Upstream last seen
----+---------+---------+-----------+----------+------------+-------+---------+--------------------
 140 | node140 | primary | * running |          | running    | 27556 | no      | n/a                
[lightdb@localhost ~]$ 

ssh到145上面
初始化数据库

[lightdb@localhost ~]$ lt_initdb 

执行下面脚本

id=145
NODE_NAME=node145
ip=192.168.153.145
port=5432

ltclusterconf=$LTHOME/etc/ltcluster/ltcluster.conf

echo "
node_id=$id
node_name='$NODE_NAME'
conninfo='host=$ip port=$port user=lightdb dbname=postgres connect_timeout=2'
data_directory='$LTDATA'
pg_bindir='$LTHOME/bin'
failover='automatic'
promote_command='$LTHOME/bin/ltcluster standby promote -f $ltclusterconf'
follow_command='$LTHOME/bin/ltcluster standby follow -f $ltclusterconf  --upstream-node-id=%n'
restore_command='cp $LTHOME/archive/%f %p'
monitoring_history=true #(Enable monitoring parameters)
monitor_interval_secs=2 #(Define monitoring data interval write time parameter)
connection_check_type='ping'
reconnect_attempts=3 #(before failover,Number of attempts to reconnect to primary before failover(default 6))
reconnect_interval=5
standby_disconnect_on_failover =true
log_level=INFO
log_facility=STDERR
log_file='$LTHOME/etc/ltcluster/ltcluster.log'
failover_validation_command='$LTHOME/etc/ltcluster/ltcluster_failover.sh "$LTHOME" "$LTDATA"'
shutdown_check_timeout=1800
use_replication_slots=true
check_lightdb_command='$LTHOME/etc/ltcluster/check_lightdb.sh'
check_lightdb_interval=10
" > $ltclusterconf
echo '===================success=================='

克隆primary，其中-h参数为primary IP

[lightdb@localhost ~]$ ltcluster -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf standby clone -h 192.168.153.140 -p 5432 -U lightdb -d postgres -F
NOTICE: destination directory "/home/lightdb/stage/lightdb-x/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.153.140 port=5432 user=lightdb dbname=postgres
DETAIL: current installation size is 54 MB
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: directory "/home/lightdb/stage/lightdb-x/data" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/home/lightdb/stage/lightdb-x/data"
INFO: creating replication slot as user "lightdb"
NOTICE: starting backup (using lt_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  /home/lightdb/stage/lightdb-x/bin/lt_basebackup -l "ltcluster base backup"  -D /home/lightdb/stage/lightdb-x/data -h 192.168.153.140 -p 5432 -U lightdb -X stream -S ltcluster_slot_145 
NOTICE: standby clone (using lt_basebackup) complete
NOTICE: you can now start your LightDB server
HINT: for example: lt_ctl -D /home/lightdb/stage/lightdb-x/data start
HINT: after starting the server, you need to register this standby with "ltcluster standby register"
[lightdb@localhost ~]$ 

启动实例

[lightdb@localhost ~]$ lt_ctl start
2023-03-08 23:49:03.670841T,,,,,postmaster,,00000,2023-03-08 23:49:03 CST,0,28849,LOG:  LightDB autoprewarm: prewarm dbnum=0
2023-03-08 23:49:03.675522T,,,,,postmaster,,00000,2023-03-08 23:49:03 CST,0,28849,INFO:  invoking IpcMemoryCreate(size=1034657792)
waiting for server to start......2023-03-08 23:49:06.720254T,,,,,postmaster,,00000,2023-03-08 23:49:03 CST,0,28849,LOG:  redirecting log output to logging collector process
2023-03-08 23:49:06.720254T,,,,,postmaster,,00000,2023-03-08 23:49:03 CST,0,28849,HINT:  Future log output will appear in directory "log".
. done
server started
[lightdb@localhost ~]$ 

注册standby

[lightdb@localhost ~]$ ltcluster -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf standby register -F
INFO: connecting to local node "node145" (ID: 145)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 140)
INFO: standby registration complete
NOTICE: standby node "node145" (ID: 145) successfully registered
[lightdb@localhost ~]$ 
[lightdb@localhost ~]$ ltclusterd -d -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster.conf -p -f /home/lightdb/stage/lightdb-x/etc/ltcluster/ltclusterd.pid
[2023-03-08 23:51:22] [NOTICE] redirecting logging output to "/home/lightdb/stage/lightdb-x/etc/ltcluster/ltcluster2023-03-08_235122.log"
[lightdb@localhost ~]$ 

检查状态

[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf cluster show
 ID  | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                            
-----+---------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
 140 | node140 | primary | * running |          | default  | 100      | 1        | host=192.168.153.140 port=5432 user=lightdb dbname=postgres connect_timeout=2
 145 | node145 | standby |   running | node140  | default  | 100      | 1        | host=192.168.153.145 port=5432 user=lightdb dbname=postgres connect_timeout=2
[lightdb@localhost ~]$ 
[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf service status
 ID | Name    | Role    | Status    | Upstream | ltclusterd | PID   | Paused? | Upstream last seen
----+---------+---------+-----------+----------+------------+-------+---------+--------------------
 140 | node140 | primary | * running |          | running    | 27556 | no      | n/a                
 145 | node145 | standby |   running | node140  | running    | 28955 | no      | 1 second(s) ago    
[lightdb@localhost ~]$