LightDB分布式扩展CN节点

已有分布式环境（虚拟机环境）
1cn，2dn
cn:192.168.160.129
dn1:192.168.160.130
dn2:192.168.160.131
扩展成：
2cn，2dn
cn1:192.168.160.129
cn2:192.168.160.132
dn1:192.168.160.130
dn2:192.168.160.131

1. 先配置免密
   在132上执行(lightdb用户)

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

其他3台机器均配置，方法雷同

2. 132上安装LightDB

[lightdb@localhost lightdb-x-13.8-22.3-7953-el7.x86_64]$ ./install.sh 
Whether to use the graphical user interface (GUI, Make sure DISPLAY is configured, Such as [export DISPLAY=127.0.0.1:0.0])?(Yes or No)
no
Choice a kind of configuration mode!
1: Only install.
2: Install database and Create instance.
3: Developer
Please enter 1 2 or 3(The default is 1):
2
Choice a kind of install mode!

1: Single Mode.
2: High Availability Mode
3: Distributed Mode
Please enter 1, 2 or 3:(The default is 1)
1
Assign a port to the LightDB!
Listen Port(The default port is 5432):

Check system parameters and dependency packages!
========================================= 192.168.160.132 =========================================
NETWORK
name: sem, recommend value: 500,2048000,200,4096, current value: 500,2048000,200,4096, status: OK
name: aio_max_nr, recommend value: 1048576, current value: 1048576, status: OK
name: somaxconn, recommend value: 2000, current value: 2000, status: OK
name: tcp_max_syn_backlog, recommend value: 2000, current value: 2000, status: OK
name: tcp_tw_reuse, recommend value: 1, current value: 1, status: OK
name: tcp_syn_retries, recommend value: 3, current value: 3, status: OK
name: tcp_retries2, recommend value: 5, current value: 5, status: OK
name: tcp_slow_start_after_idle, recommend value: 0, current value: 0, status: OK
PAGE_CACHE
name: dirty_background_ratio, recommend value: 5, current value: 5, status: OK
name: dirty_ratio, recommend value: 40, current value: 40, status: OK
name: dirty_expire_centisecs, recommend value: 500, current value: 500, status: OK
name: dirty_writeback_centisecs, recommend value: 250, current value: 250, status: OK
MEMORY
name: shmmni, recommend value: 4096, current value: 4096, status: OK
name: shmmax, recommend value: 1976983552, current value: 1976979456, status: WARNING
name: shmall, recommend value: 482662, current value: 482661, status: WARNING
name: swappiness, recommend value: 5, current value: 5, status: OK
name: overcommit_memory, recommend value: 2, current value: 2, status: OK
name: overcommit_ratio, recommend value: 75, current value: 75, status: OK
FILE_HANDLER
name: file_max, recommend value: 524288, current value: 524288, status: OK
ULIMIT
name: ulimit_core, recommend value: unlimited, current value: unlimited, status: OK
name: ulimit_nofile, recommend value: 8192, current value: 524288, status: OK
Dependency Package
name: JSON-C-0.11 is existed: yes
name: C-ARES-1 is existed: yes
name: LIBNL3 is existed: yes
name: LIBPCAP-1 is existed: yes
name: LIBZSTD-1 is existed: yes
name: LZ4-1 is existed: yes
name: NCURSES-LIBS-5 is existed: yes
name: READLINE-6 is existed: yes
name: SNAPPY-1 is existed: yes
name: UUID-1.6 is existed: yes
name: LIBICU-50 is existed: yes
Choice a kind of Compatible Type!
1: LightdDB(Compatible with PostgreSQL).
2: ORACLE(Compatible with ORACLE).
3: MYSQL(Compatible with MYSQL).
Please enter 1, 2 or 3:(The default is 1)

Choice a kind of LightDB workload!
1: OLTP(On-line Transaction Processing).
2: OLAP(On-Line Analytical Processing).
Please enter 1 or 2:(The default is 1)

Specify a path for installing all LightDB software and storing configuration information.
Please enter base location(The default is /usr/local/lightdb):

Base Location: /usr/local/lightdb
Install Location: /usr/local/lightdb/lightdb-x/13.8-22.3
Please enter instance location(The default is /usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster/):

Instance location: /usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster/
Please configure memory(MB) and character set!
Please enter shared_buffers, Default value is (942):
500
Please enter effective_cache_size, Default value is (2639):

Please choice a kind of Character Set.
1. UTF8
2. GBK
3. SQL_ASCII
4. LATIN1
The default choice 1(UTF8)

Please enter LightDB password!
Please enter original password:

Please enter confirm password:

Do you want to deploy immediately?(Yes or No, The default is yes)

[>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>]100%
Install Finish

3. ssh到129上
   1）停掉129实例

[lightdb@localhost system-lib]$ lt_ctl stop
waiting for server to shut down.... done
server stopped

2. 修改配置文件

vim $LTDATA/lightdb.conf

修改配置shared_preload_libraries，添加ltcluster

shared_preload_libraries='canopy,ltcluster,lt_stat_statements,lt_stat_activity,lt_prewarm,lt_cron,ltaudit,lt_hint_plan,lt_show_plans,lt_standby_forward,lt_ope,lt_pathman'

3. 启动实例

[lightdb@localhost system-lib]$ lt_ctl start
waiting for server to start....2022-12-28 11:21:39.932500T,,,,,postmaster,,00000,2022-12-28 11:21:39 CST,0,13536,LOG:  requesting named LWLockTranch for canopy_query_stats
2022-12-28 11:21:39.935256T,,,,,postmaster,,00000,2022-12-28 11:21:39 CST,0,13536,LOG:  LightDB autoprewarm: prewarm dbnum=0
2022-12-28 11:21:39.936524T,,,,,postmaster,,00000,2022-12-28 11:21:39 CST,0,13536,LOG:  ltaudit extension initialized
...2022-12-28 11:21:43.269293T,,,,,postmaster,,00000,2022-12-28 11:21:39 CST,0,13536,LOG:  redirecting log output to logging collector process
2022-12-28 11:21:43.269293T,,,,,postmaster,,00000,2022-12-28 11:21:39 CST,0,13536,HINT:  Future log output will appear in directory "log".
 done
server started

4. 配置ltcluster
   执行命令

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

5. 修改用户认证信息，以便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

这一步129,130,131都要做一遍

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

#!/bin/base

id=129
NODE_NAME=cn129
ip=192.168.160.129
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=================='

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

[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf primary register -F
INFO: connecting to primary database...
INFO: "ltcluster" extension is already installed
NOTICE: primary node record (ID: 129) registered

[lightdb@localhost ~]$ ltclusterd -d -f $LTHOME/etc/ltcluster/ltcluster.conf -p -f $LTHOME/etc/ltcluster/ltclusterd.pid
[2022-12-28 11:32:12] [NOTICE] redirecting logging output to "/usr/local/lightdb/lightdb-x/13.8-22.3/etc/ltcluster/ltcluster2022-12-28_113212.log"

8. 检查状态

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

4. ssh到132上

1. 先停实例

[lightdb@localhost ~]$ lt_ctl stop
waiting for server to shut down.... done
server stopped

2. 执行下面脚本

#!/bin/base

id=132
NODE_NAME=cn132
ip=192.168.160.132
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=================='

3. 克隆CN primary，其中-h参数为primary IP。视数据量大小, 这可能需要几分钟到几个小时

[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf standby clone -h 192.168.160.129 -p 5432 -U lightdb -d postgres -F
NOTICE: destination directory "/usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.160.129 port=5432 user=lightdb dbname=postgres
DETAIL: current installation size is 61 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 "/usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster"
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:
  /usr/local/lightdb/lightdb-x/13.8-22.3/bin/lt_basebackup -l "ltcluster base backup"  -D /usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster -h 192.168.160.129 -p 5432 -U lightdb -X stream -S ltcluster_slot_140 
NOTICE: standby clone (using lt_basebackup) complete
NOTICE: you can now start your LightDB server
HINT: for example: lt_ctl -D /usr/local/lightdb/lightdb-x/13.8-22.3/data/defaultCluster start
HINT: after starting the server, you need to register this standby with "ltcluster standby register"

4. 启动实例

[lightdb@localhost ~]$ lt_ctl start
waiting for server to start....2022-12-28 11:42:37.027919T,,,,,postmaster,,00000,2022-12-28 11:42:37 CST,0,52338,LOG:  requesting named LWLockTranch for canopy_query_stats
2022-12-28 11:42:37.031228T,,,,,postmaster,,00000,2022-12-28 11:42:37 CST,0,52338,LOG:  LightDB autoprewarm: prewarm dbnum=0
2022-12-28 11:42:37.032163T,,,,,postmaster,,00000,2022-12-28 11:42:37 CST,0,52338,LOG:  ltaudit extension initialized
...2022-12-28 11:42:40.314655T,,,,,postmaster,,00000,2022-12-28 11:42:37 CST,0,52338,LOG:  redirecting log output to logging collector process
2022-12-28 11:42:40.314655T,,,,,postmaster,,00000,2022-12-28 11:42:37 CST,0,52338,HINT:  Future log output will appear in directory "log".
 done
server started

5. 注册standby

[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf standby register -F
INFO: connecting to local node "cn132" (ID: 132)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 129)
WARNING: node "cn132" not found in "pg_stat_replication"
WARNING: local node not attached to primary node 129
NOTICE: -F/--force supplied, continuing anyway
INFO: standby registration complete
NOTICE: standby node "cn132" (ID: 132) successfully registered
[lightdb@localhost ~]$ ltclusterd -d -f $LTHOME/etc/ltcluster/ltcluster.conf -p -f $LTHOME/etc/ltcluster/ltclusterd.pid
[2022-12-28 11:43:53] [NOTICE] redirecting logging output to "/usr/local/lightdb/lightdb-x/13.8-22.3/etc/ltcluster/ltcluster2022-12-28_114353.log"

5. 检查状态

[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf cluster show
 ID  | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                            
-----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------------------
 129 | cn129 | primary | * running |          | default  | 100      | 1        | host=192.168.160.129 port=5432 user=lightdb dbname=postgres connect_timeout=2
 132 | cn132 | standby |   running |   cn129  | default  | 100      | 1        | host=192.168.160.132 port=5432 user=lightdb dbname=postgres connect_timeout=2
 
[lightdb@localhost ~]$ ltcluster -f $LTHOME/etc/ltcluster/ltcluster.conf service status
 ID | Name  | Role    | Status    | Upstream | ltclusterd | PID   | Paused? | Upstream last seen
----+-------+---------+-----------+----------+------------+-------+---------+--------------------
 129 | cn129 | primary | * running |          | running    | 62427 | no      | n/a                
 132 | cn132 | standby |   running |   cn129  | running    | 52377 | no      | 1 second(s) ago 

6. 把canopy.writable_standby_coordinator = on添加到两个CN节点的lightdb.conf，并执行lt_ctl reload，可永久有效。

7. 实测一把
   在129上创建分布式表

[lightdb@localhost ~]$ ltsql -d postgres
ltsql (13.8-22.3)
Type "help" for help.

lightdb@postgres=# select * from pg_dist_node;
 nodeid | groupid |    nodename     | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards 
--------+---------+-----------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
      1 |       0 | 192.168.160.129 |     5432 | default  | t           | t        | primary  | default     | t              | f
      2 |       1 | 192.168.160.130 |     5432 | default  | t           | t        | primary  | default     | t              | t
      3 |       2 | 192.168.160.131 |     5432 | default  | t           | t        | primary  | default     | t              | t
(3 rows)
lightdb@postgres=# create table the_table(id int, code text, price numeric(8,2));
CREATE TABLE
lightdb@postgres=# select create_distributed_table('the_table', 'id');
 create_distributed_table 
--------------------------
 
(1 row)

lightdb@postgres=# insert into  the_table values (1, '1', 3.439);
INSERT 0 1
lightdb@postgres=# insert into  the_table values (2, '2', 6.86);
INSERT 0 1
lightdb@postgres=# select * from the_table;
 id | code | price 
----+------+-------
  1 | 1    |  3.44
  2 | 2    |  6.86
(2 rows)

在132上进行dml操作

[lightdb@localhost ~]$ ltsql -d postgres -p 5432
ltsql (13.8-22.3)
Type "help" for help.

lightdb@postgres=# select * from the_table;
 id | code | price 
----+------+-------
  2 | 2    |  6.86
  1 | 1    |  3.44
(2 rows)

lightdb@postgres=# delete from the_table where id = 1;
DELETE 1
lightdb@postgres=# delete from the_table where id = 2;
DELETE 1
lightdb@postgres=# select * from the_table;
 id | code | price 
----+------+-------
(0 rows)

会发现执行dml成功，到此扩展cn节点成功。

详细语法可参考LightDB官网查看https://www.hs.net/lightdb
更多请登录LightDB官网进行查看：https://www.hs.net/lightdb