1、介绍
LightDB是恒生电子股份有限公司研发并将长期支持的一款同时支持在线事 务处理与在线分析处理的融合型分布式数据库产品,具备SQL兼容性高、容量弹 性伸缩、金融级高可用、现代硬件融合、纯内存计算等核心特性,主要适用于对 可用性、一致性要求较高的系统。
官网:https://www.hs.net/lightdb
2、安装配置
2.1安装包
下载地址:https://www.hs.net/lightdb
LightDB安装包名格式为:
lightdb-x-version-revision-os.arch.zip
注释:
lightdb-x:数据库名
version:数据库版本号,目前最新为13.3-22.1,13.3表示基于PostgreSQL 13.3版本,22.1表示2022年第1
个Release版本
revision:源代码提交编号
os:安装包适用的操作系统,目前支持CentOS7/RHEL7和银河麒麟(Kylin)服务器V10系列
arch:安装包适用的CPU架构,分为x86_64和aarch64
2.2创建数据库用户和组
groupadd lightdb
useradd -g lightdb -m lightdb
passwd lightdb
为lightdb用户设置sudo免密,如下图所示,在 /etc/sudoers 中新增一行 lightdb ALL=(ALL)
2.3安装依赖包
yum install -y procps-ng
yum install -y coreutils
yum install -y gtk2
yum install -y libXtst
yum install -y readline
yum install -y zlib
yum install -y libxml2
yum install -y openssl-libs
yum install -y uuid
2.4创建目录并授权
mkdir -p /usr/local/lightdb
chown -R lightdb:lightdb /usr/local/lightdb
2.5系统参数优化
echo "kernel.shmmni=4096" >> /etc/sysctl.conf
echo "kernel.shmmax=$(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))" >>
/etc/sysctl.conf
echo "kernel.shmall=$(expr $(getconf _PHYS_PAGES) / 2)" >> /etc/sysctl.conf
echo "kernel.sem=500 2048000 200 4096" >> /etc/sysctl.conf
echo "fs.aio-max-nr=1048576" >> /etc/sysctl.conf
echo "fs.file-max=524288" >> /etc/sysctl.conf
echo "vm.swappiness=5" >> /etc/sysctl.conf
echo "vm.overcommit_memory=2" >> /etc/sysctl.conf
echo "vm.overcommit_ratio=75" >> /etc/sysctl.conf
echo "vm.dirty_background_ratio=5" >> /etc/sysctl.conf
echo "vm.dirty_ratio=40" >> /etc/sysctl.conf
echo "vm.dirty_expire_centisecs=500" >> /etc/sysctl.conf
echo "vm.dirty_writeback_centisecs=250" >> /etc/sysctl.conf
echo "net.core.somaxconn=2000" >> /etc/sysctl.conf
echo "net.ipv4.tcp_max_syn_backlog=2000" >> /etc/sysctl.conf
echo "net.ipv4.tcp_tw_reuse=1" >> /etc/sysctl.conf
echo "net.ipv4.tcp_syn_retries=3" >> /etc/sysctl.conf
echo "net.ipv4.tcp_retries2=5" >> /etc/sysctl.conf
echo "net.ipv4.tcp_slow_start_after_idle=0" >> /etc/sysctl.conf
2.6环境变量配置
export LIGHTDB_PORT=5432
export PGUSER=lightdb
export LIGHTDB_HOST=192.168.19.191
export PGHOME=/usr/local/lightdb/lightdb-x/13.3-22.1
export PGDATA=/usr/local/lightdb/lightdb-x/13.3-22.1/data/defaultCluster/
export PATH=${PGHOME}/bin:${PGHOME}/tools/iftop/bin:${PGHOME}/tools/iotop/bin:${PGHOME}/tools/linux-ftools/bin:${PGHOME}/tools/vmtouch/bin:${PATH}
export LD_LIBRARY_PATH=${PGHOME}/lib:${PGHOME}/lib/ltext:${LD_LIBRARY_PATH}
2.7安装
1.解压安装包
[root@lightdb soft]# ll
-rw-r–r-- 1 root root 862566244 May 23 14:37 lightdb-x-13.3-22.1-4407-el7.x86_64.zip
[root@lightdb soft]# unzip lightdb-x-13.3-22.1-4407-el7.x86_64
2.进入解压目录,运行install.sh
[root@lightdb soft]# cd lightdb-x-13.3-22.1-4407-el7.x86_64/
[root@lightdb lightdb-x-13.3-22.1-4407-el7.x86_64]# ./install.sh
3.选择安装模式,输入2
4.选择单机版
5.指定端口,使用默认5432
6.选择OLTP
7.指定LightDB安装目录和实例目录,默认即可
8.配置shared_buffers与effective_cache_size大小,以及设置字符集
9.设置super用户密码,键入时密码不可见
10.不安装LightDB EM
11.安装最后出现如下信息即代表安装成功
3、数据库启动关闭
格式
lt_ctl start [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-K KEY-CMD] [-s]
[-o OPTIONS] [-p PATH] [-c]
lt_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
lt_ctl restart [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-K KEY-CMD] [-s]
模式选项:
Options for stop or restart:
-m, --mode=MODE MODE can be "smart", "fast", or "immediate"
Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown (default)
immediate quit without complete shutdown; will lead to recovery on restart
lt_ctl -D $PGDATA start
lt_ctl -D $PGDATA stop -mf
lt_ctl -D $PGDATA restart -mf
建议stop和restart加上参数-mf一致性关闭
4、基础管理
4.1连接管理
4.1.1 连接命令
ltsql -d <数据库> -h -U <用户> -p <端口>
示例:
ltsql -d csdb2 -h 192.168.19.191 -U lightdb1 -p 5432
4.1.2 客户端或者远程机器登陆,访问控制文件pg_hba.conf
pg_hba.conf文件为实例的防火墙文件:
配置文件分为5部分;
配置示例:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
4.2 用户
用户用来登陆数据库实例,管理数据库对象
4.2.1用户定义的方法
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
例子
create user cs1 with password '123';
create role cs2 with password '123';
create role cs3 with superuser login assword '123';
create user cs4 repl replication login encrypted password '123';
user和role的区别在于,创建时user带有连接权限,而role没有
4.3 权限管理
4.3.1 权限级别
cluster权限:实例权限通过pg_hba.conf配置
database权限:数据库权限通过grant和revoke操作schema配置
tbs权限:表空间权限通过grant和revike操作表、索引等对象配置
schema权限:模式权限通过grant和revoke操作模式下的对象配置
object权限:对象权限通过grant和revoke配置
注:tbs权限限制使用比较少
4.3.2 权限定义
database权限设置
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
grant create on database csdb to cs;
schema 权限
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
grant select,insert,update on all tables in schema crm to cs;
object 权限
grant select,insert,update on all a.b to cs;
5、常用命令
\? ##打印与\相关的一些指令
\h ##打印与sql相关的指令
\l ##显示所有的库,相当于mysql show databases;
\c ##切换到其他的库
\d ##显示数据库中的所有表,相当于mysql show tables;
\dt ##显示匹配的表
\du ##查询用户和权限的信息
\x ##把行展示的数据变成列展示