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
- 先配置免密
在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台机器均配置，方法雷同
- 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
- ssh到129上
1）停掉129实例
[lightdb@localhost system-lib]$ lt_ctl stop
waiting for server to shut down.... done
server stopped
- 修改配置文件
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'
- 启动实例
[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
- 配置ltcluster
执行命令
[lightdb@localhost ~]$ ltsql -p 5432 -h 192.168.160.129 -dpostgres -c"create extension ltcluster;"
CREATE EXTENSION
- 修改用户认证信息，以便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都要做一遍
- 执行下面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=================='
- 使用如下命令注册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"
- 检查状态
[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
- ssh到132上
- 先停实例
[lightdb@localhost ~]$ lt_ctl stop
waiting for server to shut down.... done
server stopped
- 执行下面脚本
#!/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=================='
- 克隆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"
- 启动实例
[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
- 注册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"
- 检查状态
[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
-
把canopy.writable_standby_coordinator = on添加到两个CN节点的lightdb.conf，并执行lt_ctl reload，可永久有效。
-
实测一把
在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
类似资料：