一.简介
openGauss是一款极致性能、安全、可靠的关系型（OLTP）开源数据库，基于postgresql开发，LTS(长期支持版本)包括3.0.0/3.0.3/3.0.5(LTS)，5.0.0/5.0.1/5.0.2(LTS)，可兼容MySQL包括数据类型、操作符、系统函数、DDL、DML、DCL、PL/SQL等在内的绝大部分常用语法，使用MySQL一站式迁移工具gs_rep_portal实现应用近似零修改迁移。典型应用场景，包裹物联网行业（发布/订阅逻辑复制实现）和交易性应用（高并发、大数据量、以联机事务处理OLTP）的行业。官网地址
openGauss核心技术包括：高性能、高可用、易维护性、数据库安全（访问控制、加密、脱敏、审计）和AI能力。
二.部署前准备
0.逻辑架构图
1.openGauss支持运行在ARM服务器和通用的x86服务器上，要求支持python3.7(centos python3.6.x），文件系统格式首选ext4。
ARM架构 | x86架构 |
openEuler 20.03LTS 推荐 | openEuler 20.03LTS 推荐 |
openEuler22.03LTS | openEuler22.03LTS |
麒麟V10 | centos7.6 |
2.支持单机部署和一主多备部署两种部署形态。
3.资源规划
资源 2C 8G 100G+200G | centos7.6/openEuler20.0.3+ | 192.168.77.161 master |
资源 2C 8G 100G+200G | centos7.6/openEuler20.0.3+ | 192.168.77.162 slave1 |
资源 2C 8G 100G+200G | centos7.6/openEuler20.0.3+ | 192.168.77.163 slave2 |
4.安装前准备，以下master slave1 slave2都需要配置
#master slave1 slave2配置hosts
cat >>/etc/hosts<<'EOF'
192.168.77.161 master
192.168.77.162 slave1
192.168.77.163 slave2
EOF
#开机激活网络配置
cat >>/etc/sysconfig/network<<'EOF'
NETWORKING=yes
EOF
#增加数据盘，LVM创建并挂载
pvcreate /dev/sdb
vgcreate datavg /dev/sdb
lvcreate -n datalv -L 200000M datavg
mkfs.xfs /dev/datavg/datalv
#xfs ext4的挂载参数defaults,noatime,nodiratime,nobarrier
mkdir -p /gauss
cat>>/etc/fstab<<'EOF'
/dev/datavg/datalv /gauss xfs defaults,noatime,nodiratime,nobarrier
EOF
mount -a
#创建目录、用户和组,官方文档omm用户
mkdir -p /gauss/{gaussdb,archive,script,backup,soft}
groupadd gauss
useradd -g gauss -d /home/gauss -m -s /bin/bash gauss
echo "123456" | passwd --stdin gauss
chown -R gauss:gauss /gauss
#环境变量配置,opengauss安装也会自动加入环境变量，在gauss用户~/.bashrc文件内
#环境变量配置
su - gauss
cat >>~/.bash_profile<<'EOF'
export LANG=en_US.UTF8
export PS1="[`whoami`@`hostname`:"'$PWD]$'
export GAUSSHOME=/gauss/gaussdb/
export LD_LIBRARY_PATH=/gauss/gaussdb/lib
export PATH=/gauss/gaussdb/bin:$PATH:$HOME/.local/bin:$HOME/bin
EOF
source ~/.bash_profile
退出gauss用户
exit
#修改启动级别
systemctl set-default multi-user.target
#修改系统资源限制参数
cat >>/etc/security/limits.conf<<'EOF'
gauss soft nproc 65535
gauss hard nproc 65535
gauss soft nofile 65535
gauss hard nofile 65535
gauss soft stack 65535
gauss hard stack 65535
gauss soft memlock unlimited
gauss hard memlock unlimited
EOF
#修改内核参数，优化 参考官方文档
cat >>/etc/sysctl.conf<<'EOF'
vm.swappiness = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.tcp_retries1 = 5
net.ipv4.tcp_syn_retries = 5
net.ipv4.tcp_synack_retries = 5
net.ipv4.tcp_retries2 = 12
net.ipv4.ip_local_port_range = 26000 65535
net.ipv4.tcp_fin_timeout = 60
net.ipv4.tcp_sack = 1
net.ipv4.tcp_timestamps = 1
vm.min_free_kbytes = 512000
vm.extfrag_threshold = 500
vm.overcommit_ratio = 90
kernel.sem = 250 85000 250 330 #不配置安装时会报错
EOF
sysctl -p
echo "* -nproc 65535" >/etc/security/limits.d/90-nproc.conf
#修改时区
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
hwclock
#关闭SELINUX和防护墙
systemctl disable firewalld
systemctl stop firewalld
sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config
setenforce 0
#关闭内存大页，NUMA安装数据库时会有内置优化
cp /etc/default/grub /etc/default/grub_bak
line_num=`cat -n /etc/default/grub | grep 'GRUB_CMDLINE_LINUX'| awk '{print $1}' | head -n 1`
sed -i --follow-symlinks 's/transparent_hugepage=never//g' /etc/default/grub
sed -i --follow-symlinks ""${line_num}" s/\"$/ transparent_hugepage=never\"/g" /etc/default/grub
grub2-mkconfig -o /boot/grub2/grub.cfg
#禁用远程调用IPC RemoveIPC
sed -i '/^RemovelPc/d' /etc/systemd/logind.conf
sed -i '/^RemovelPC/d' /usr/lib/systemd/system/systemd-logind.service
echo "RemovelPC=no" >>/etc/systemd/logind.conf
echo "RemovelPC=no" >>/usr/lib/systemd/system/systemd-logind.service
systemctl daemon-reload
systemctl restart systemd-logind
#确认是否关闭,上一步已删除，查询不到才对
loginctl show-session | grep RemovelPC
systemctl show systemd-logind | grep RemovelPC
#使用系统镜像包 配置yum源
mount /dev/cdrom /mnt
cd /etc/yum.repos.d/
gzip CentOS-*
cat >centos.repo<<'EOF'
[centos7]
name=centos7
baseurl=file:///mnt
gpgcheck=0
enable=1
EOF
#安装依赖
yum -y install libaio-devel libnsl flex bison ncurses-devel qlibc-devel patch readline-devel
yum -y install net-tools tar bzip2 python3 cmake make gcc zlib gcc-c++ perl zlib-devel tcl openssl openldap pam三.单机部署
0.单机安装
#slave1单机安装
su - gauss
cd /gauss/soft
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/3.0.0/x86/openGauss-3.0.0-CentOS-64bit-all.tar.gz
tar xf openGauss-3.0.0-CentOS-64bit-all.tar.gz
tar xf openGauss-3.0.0-CentOS-64bit.tar.bz2 -C /gauss/gaussdb/
cd /gauss/gaussdb/simpleInstall
#指定gauss数据库密码安装-w Alibaby@007
sh install.sh -w Alibaby@007
#提示Would you like to create a demo database (yes/no)? 输入yes
#安装完成提示 gs_ctl start|stop|restart -D $GAUSSHOME/data/single_node -Z single_node
#检查
ps ux | grep gaussdb
netstat -tlunp | grep 5432
gs_ctl query -D /$GAUSSHOME/data/single_node报错：
On systemwide basis, the maximum number of SEMMNI is not correct. the current SEMMNI value is: 128. Please check it.
The required value should be greater than 321. You can modify it in file '/etc/sysctl.conf'.
解决:
su - root
cat >>/etc/sysctl.conf<<'EOF'
kernel.sem = 250 85000 250 330
EOF
sysctl -p
exit
sh install.sh -w Alibaby@007报错 :
-bash: ulimit: open files: 无法修改 limit 值: 不允许的操作
解决：
#切换到初始化gauss用户，注释掉限制
su - gauss
sed -i 's/ulimit -n 1000000/#ulimit -n 1000000/g' ~/.bashrc
source ~/.bashrc
#退出gauss用户
exit
#重新登陆
su - gauss1.启停
#启停操作 指定模式-Z single_node
gs_ctl status -D /gauss/gaussdb/data/single_node -Z single_node
gs_ctl restart -D /gauss/gaussdb/data/single_node -Z single_node
gs_ctl stop -D /gauss/gaussdb/data/single_node -Z single_node
gs_ctl start -D /gauss/gaussdb/data/single_node -Z single_node2.登录，授权，访问控制
#登录数据库，默认端口5432与postgre一样，-d指定数据库，-W指定口令，-U指定用户
gsql -Ugauss -d postgres
gsql -Ugauss -WAlibaby@008 -d postgres
gsql -h 127.0.0.1 -p 5432 -U gauss -d postgres
#更改数据库密码
alter user gauss with password 'Alibaby@008';
#创建用户并授权
create user alibaby with password 'Alibaby@007';
grant all privileges to alibaby;
create database alibabydb owner alibaby;
grant all privileges on all tables in schema public to alibaby;
grant all privileges on database alibabydb to alibaby;
#访问控制
gs_guc reload -I all -N all -h "host alibabydb alibaby 192.168.0.0/16 sha256"
#配置文件修改访问控制
cat >>/gauss/gaussdb/data/single_node/pg_hba.conf<<'EOF'
host alibabydb alibaby 192.168.0.0/16 sha256
EOF
#监听地址配置
show listen_addresses;
alter system set listen_addresses='0.0.0.0'; #其实是修改的配置文件postgresql.conf
gs_ctl restart -D /gauss/gaussdb/data/single_node -Z single_node
#登录,输入用户alibaby的口令
gsql -h 192.168.77.162 -p 5432 -U alibaby -d alibabydb
#退出
\q3.SQL
#登录数据库
gsql -U alibaby -d alibabydb
#创建表
CREATE TABLE student
(
std_id INT PRIMARY KEY,
std_name VARCHAR(20) NOT NULL,
std_sex VARCHAR(6),
std_birth DATE,
std_in DATE NOT NULL,
std_address VARCHAR(100)
);
#插入数据
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (1,'张一','男','1993-01-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (2,'张二','男','1993-01-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (3,'张三','男','1993-01-03','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (4,'张四','男','1993-01-04','2011-09-01','江苏省南京市雨花台区');
#查询
select * from student;
#退出
\q
#常用元命令
#查看帮助
\h
#退出数据库
\q
#查看数据库、所有者、字符集
\l
#查看版本
\copyright
#列出当前表视图
\d
#查看可用表空间
\db
#查看表
\dt
#查看用户
\du
......4.其他
#可以登录
gsql -h 127.0.0.1 -d postgres -U gauss
#不能登录，162本机IP,报错TCP/IP connections on port 5432?
gsql -h 192.168.77.162 -d postgres -U gauss
#原因
#初始化gauss用户不能远程登录
cd /gauss/gaussdb/data/single_node/
#pg_hba.conf此配置文件可限制除初始化用户gauss以外的其他用户报错： Port 5432 is used,
排查5432没有被占用，原因listen_addresses修改错误，修改为listen_addresses = '0.0.0.0'
[BACKEND] LOG: loaded library "security_plugin"
[BACKEND] LOG: could not bind IPv4 socket at the 0 time: Cannot assign requested address
[BACKEND] HINT: Port 5432 is used, run 'netstat -anop|grep 5432' or 'lsof -i:5432'(need root) to see who is using this port.
[BACKEND] LOG: exec cmd: lsof -i:5432
[BACKEND] LOG: exec cmd: netstat -anp | grep 5432
(No info could be read for "-p": geteuid()=1001 but you should be root.)解决：
#修改配置文件
cd /gauss/gaussdb/data/single_node
vim postgresql.conf
listen_addresses = '0.0.0.0'
#重启数据库
gs_ctl restart -D /gauss/gaussdb/data/single_node -Z single_node
