猿创征文 | 国产数据库之openGauss的单机主备部署及快速入门
一、openGauss介绍
1.openGauss简介
1.openGauss是一款开源关系型数据库管理系统，采用木兰宽松许可证v2发行。
2.openGauss是一款支持SQL2003标准语法，支持主备部署的高可用关系型数据库。
2.openGauss特点
1.高可靠：故障切换时间RTO<10s。
2.高性能：两路鲲鹏性能150万tpmC。
3.易运维：基于AI的智能参数调优。
4.高安全：端到端全方位安全防护。
3.openGauss的逻辑架构图
二、环境检查
1.节点规划
hostnameIP地址备注master192.168.3.201极简版——一主一备节点
2.操作系统版本
[root@master ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
三、安装前环境配置
1.检查admin普通用户
[root@master simpleInstall]# id admin
uid=1000(admin) gid=1000(admin) groups=1000(admin)
2.给安装目录授权
chown -R admin:admin /data/openGauss/
chown -R admin:admin /opt/software/openGauss
3.配置/etc/sysctl.conf文件
[root@master openGauss]# vim /etc/sysctl.conf
[root@master openGauss]# cat /etc/sysctl.conf
#sysctlsettings are defined through files in
# /usr/lib/sysctl.d/,/run/sysctl.d/,and/etc/sysctl.d/.
#
#Vendors settings live in /usr/lib/sysctl.d/.#To override a whole file, create a new file with the same in
# /etc/sysctl.d/and put new settings there. To override#onlyspecific settings, add a file with a lexically later#namein /etc/sysctl.d/and put new settings there.
#
#For more information, see sysctl.conf(5)and sysctl.d(5).
net.ipv4.ip_forward=1
kernel.sem =25032000100999[root@master openGauss]# sysctl -p
net.ipv4.ip_forward =1
kernel.sem =25032000100999
4.配置/etc/hosts
[root@master soft]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.3.201 master
四、下载openGauss软件包
1.进入官网下载
2.将软件包上传到master节点
[root@master soft]# pwd
/data/openGauss/soft
[root@master soft]# ll
total 87720-rw-r--r--1 root root 89822788 Oct 317:19 openGauss-3.1.0-CentOS-64bit.tar.bz2
3.切换admin用户
[root@master openGauss]# su - admin
[admin@master ~]$ cd /data/openGauss/[admin@master openGauss]$ ls
openGauss-3.1.0-CentOS-64bit.tar.bz2
[admin@master openGauss]$ pwd
/data/openGauss
4.解压软件包
[admin@master openGauss]$ tar -xjf openGauss-3.1.0-CentOS-64bit.tar.bz2 -C /opt/software/openGauss
[admin@master openGauss]$ ls /opt/software/openGauss/
bin etc include jre lib share simpleInstall version.cfg
五、执行openGauss安装
1.进入simpleInstall目录
[admin@master openGauss]$ cd /opt/software/openGauss/simpleInstall/[admin@master simpleInstall]$ ls
finance.sql install.sh README.md school.sql
2.安装simpleInstall
sh install.sh -w Admin.123456--multinode
-w：初始化数据库密码（gs_initdb指定），安全需要必须设置。
-p：指定的openGauss主节点端口号，默认5432。备节点端口号会使用主端口号+200，默认5632。
–multinode：用来区分是单节点还是一主一备安装。
3.openGauss安装过程
[admin@master simpleInstall]$ sh install.sh -w Admin.123456--multinode
[step 1]: check parameter
[step 2]: check install env and os setting
[step 3]: change_gausshome_owner
[step 4]: set environment variables
/home/admin/.bashrc: line 16: ulimit: open files: cannot modify limit: Operation not permitted
[init primary datanode.]
The files belonging to this database system will be owned by user "admin".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
creating directory /opt/software/openGauss/data/master ... ok
creating subdirectories ... ok
selecting default max_connections ...100
selecting default shared_buffers ...32MB
creating configuration files ... ok
Begin init undo subsystem meta.[INIT UNDO] Init undo subsystem meta successfully.
creating template1 database in /opt/software/openGauss/data/master/base/1...2022-10-0320:49:33.719[unknown][unknown] localhost 1405366445395200[0:0#0][BACKEND] WARNING: macAddr is 12/691646992, sysidentifier is 796985/2987452422, randomNum is 2160584710
ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
loading PL/pgSQL server-side language ... ok
creating system views ... ok
creating performance views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
initialize global configure for bucketmap length ... ok
creating information schema ... ok
loading foreign-data wrapper for distfs access ... ok
loading foreign-data wrapper for log access ... ok
loading hstore extension ... ok
loading foreign-data wrapper for MOT access ... ok
loading security plugin ... ok
update system tables ... ok
creating snapshots catalog ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf orusing the option -A,or--auth-local and--auth-host, the next time you run gs_initdb.
Success. You can now start the database server of single node using:
gaussdb -D /opt/software/openGauss/data/master --single_node
or
gs_ctl start -D /opt/software/openGauss/data/master -Z single_node -l logfile
[init slave datanode.]
The files belonging to this database system will be owned by user "admin".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
creating directory /opt/software/openGauss/data/slave ... ok
creating subdirectories ... ok
selecting default max_connections ...100
selecting default shared_buffers ...32MB
creating configuration files ... ok
Begin init undo subsystem meta.[INIT UNDO] Init undo subsystem meta successfully.
creating template1 database in /opt/software/openGauss/data/slave/base/1...2022-10-0320:49:42.064[unknown][unknown] localhost 1402004549643520[0:0#0][BACKEND] WARNING: macAddr is 12/691646992, sysidentifier is 796985/2987423942, randomNum is 4257577158
ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
loading PL/pgSQL server-side language ... ok
creating system views ... ok
creating performance views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
initialize global configure for bucketmap length ... ok
creating information schema ... ok
loading foreign-data wrapper for distfs access ... ok
loading foreign-data wrapper for log access ... ok
loading hstore extension ... ok
loading foreign-data wrapper for MOT access ... ok
loading security plugin ... ok
update system tables ... ok
creating snapshots catalog ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf orusing the option -A,or--auth-local and--auth-host, the next time you run gs_initdb.
Success. You can now start the database server of single node using:
gaussdb -D /opt/software/openGauss/data/slave --single_node
or
gs_ctl start -D /opt/software/openGauss/data/slave -Z single_node -l logfile
[config datanode.]
remote_read_mode = non_authentication
host all all 192.168.3.201/32 trust
[start primary datanode.][2022-10-0320:49:49.982][10855][][gs_ctl]: gs_ctl started,datadir is /opt/software/openGauss/data/master
[2022-10-0320:49:50.012][10855][][gs_ctl]: waiting for server to start....0 LOG:[Alarm Module]can not read GAUSS_WARNING_TYPE env.0 LOG:[Alarm Module]Host Name: master
0 LOG:[Alarm Module]Host IP: master. Copy hostname directly in case of taking 10s to use 'gethostbyname' when /etc/hosts does not contain <HOST IP>0 LOG:[Alarm Module]Cluster Name: dbCluster
0 LOG:[Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line:570 WARNING: failed to open feature control file, please check whether it exists: FileName=gaussdb.version, Errno=2, Errmessage=No such file or directory.0 WARNING: failed to parse feature control file: gaussdb.version.0 WARNING: Failed to load the product control file, so gaussdb cannot distinguish product version.2022-10-0320:49:50.078[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: when starting as multi_standby mode, we couldn't support data replicaton.
gaussdb.state does not exist,and skipt setting since it is optional.2022-10-0320:49:50.085[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG:[Alarm Module]can not read GAUSS_WARNING_TYPE env.2022-10-0320:49:50.085[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG:[Alarm Module]Host Name: master
2022-10-0320:49:50.085[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG:[Alarm Module]Host IP: master. Copy hostname directly in case of taking 10s to use 'gethostbyname' when /etc/hosts does not contain <HOST IP>2022-10-0320:49:50.085[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG:[Alarm Module]Cluster Name: dbCluster
2022-10-0320:49:50.085[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG:[Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line:572022-10-0320:49:50.087[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: loaded library "security_plugin"2022-10-0320:49:50.088[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] WARNING: could not create any HA TCP/IP sockets
2022-10-0320:49:50.089[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: InitNuma numaNodeNum:1 numa_distribute_mode: none inheritThreadPool:0.2022-10-0320:49:50.089[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: reserved memory for backend threads is:220 MB
2022-10-0320:49:50.089[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: reserved memory for WAL buffers is:128 MB
2022-10-0320:49:50.089[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: Set max backend reserve memory is:348 MB, max dynamic memory is:11071 MB
2022-10-0320:49:50.089[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: shared memory 356 Mbytes, memory context 11419 Mbytes, max process memory 12288 Mbytes
2022-10-0320:49:50.103[unknown][unknown] localhost 1402953581374720[0:0#0]0[CACHE] LOG: set data cache size(402653184)2022-10-0320:49:50.128[unknown][unknown] localhost 1402953581374720[0:0#0]0[SEGMENT_PAGE] LOG: Segment-page constants: DF_MAP_SIZE:8156, DF_MAP_BIT_CNT:65248, DF_MAP_GROUP_EXTENTS:4175872, IPBLOCK_SIZE:8168, EXTENTS_PER_IPBLOCK:1021, IPBLOCK_GROUP_SIZE:4090, BMT_HEADER_LEVEL0_TOTAL_PAGES:8323072, BktMapEntryNumberPerBlock:2038, BktMapBlockNumber:25, BktBitMaxMapCnt:5122022-10-0320:49:50.144[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: gaussdb: fsync file "/opt/software/openGauss/data/master/gaussdb.state.temp" success
2022-10-0320:49:50.144[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: create gaussdb state file success: db state(STARTING_STATE), server mode(Primary), connection index(1)2022-10-0320:49:50.167[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: max_safe_fds =974, usable_fds =1000, already_open =162022-10-0320:49:50.168[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: user configure file is not found, it will be created.2022-10-0320:49:50.171[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: the configure file /opt/software/openGauss/etc/gscgroup_admin.cfg doesn't exist or the size of configure file has changed. Please create it by root user!2022-10-0320:49:50.171[unknown][unknown] localhost 1402953581374720[0:0#0]0[BACKEND] LOG: Failed to parse cgroup config file.2022-10-0320:49:50.192[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:50.192[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:50.192[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:50.192[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:50.194[unknown][unknown] localhost 1402953581374720[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.[2022-10-0320:49:51.020][10855][][gs_ctl]: done
[2022-10-0320:49:51.020][10855][][gs_ctl]: server started(/opt/software/openGauss/data/master)[build and start slave datanode.][2022-10-0320:49:51.028][10924][][gs_ctl]: gs_ctl full build ,datadir is /opt/software/openGauss/data/slave
[2022-10-0320:49:51.029][10924][][gs_ctl]: fopen build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
[2022-10-0320:49:51.029][10924][][gs_ctl]: fprintf build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
[2022-10-0320:49:51.029][10924][][gs_ctl]: fsync build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
[2022-10-0320:49:51.029][10924][][gs_ctl]: stop failed, killing gaussdb by force ...[2022-10-0320:49:51.029][10924][][gs_ctl]: command [ps c -eo pid,euid,cmd | grep gaussdb | grep -v grep | awk '{if($2== curuid && $1!="-n") print "/proc/"$1"/cwd"}' curuid=`id -u`| xargs ls -l | awk '{if($NF=="/opt/software/openGauss/data/slave") print $(NF-2)}' | awk -F/ '{print $3}' | xargs kill -9>/dev/null 2>&1] path:[/opt/software/openGauss/data/slave][2022-10-0320:49:51.074][10924][][gs_ctl]: server stopped
[2022-10-0320:49:51.074][10924][][gs_ctl]: current workdir is(/opt/software/openGauss/simpleInstall).[2022-10-0320:49:51.074][10924][][gs_ctl]: set gaussdb state file when full build build:db state(BUILDING_STATE), server mode(STANDBY_MODE), build mode(FULL_BUILD).[2022-10-0320:49:51.075][10924][datanode2][gs_ctl]: Get repl_auth_mode is and repl_uuid is
[2022-10-0320:49:51.079][10924][datanode2][gs_ctl]: build tryhost(192.168.3.201)port(5433) success
[2022-10-0320:49:51.079][10924][datanode2][gs_ctl]: connected to server success, build started.[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: clear old target dir success
[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: create build tag file success
[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: create build tag file again success
[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: get system identifier success
[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: receiving and unpacking files...[2022-10-0320:49:51.107][10924][datanode2][gs_ctl]: create backup label success
INFO: The starting position of the xlog copy of the full build is:0/243E7B0. The slot minimum LSN is:0/0. The disaster slot minimum LSN is:0/0. The logical slot minimum LSN is:0/0.[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: xlog start point:0/243E7B0
[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: begin build tablespace list
[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: finish build tablespace list
[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: begin get xlog by xlogstream
[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: starting background WAL receiver
[2022-10-0320:49:51.196][10924][datanode2][gs_ctl]: starting walreceiver
[2022-10-0320:49:51.197][10924][datanode2][gs_ctl]: begin receive tar files
[2022-10-0320:49:51.197][10924][datanode2][gs_ctl]: receiving and unpacking files...[2022-10-0320:49:51.206][10924][datanode2][gs_ctl]: build tryhost(192.168.3.201)port(5433) success
[2022-10-0320:49:51.207][10924][datanode2][gs_ctl]: check identify system success
[2022-10-0320:49:51.208][10924][datanode2][gs_ctl]: send START_REPLICATION 0/2000000 success
[2022-10-0320:49:51.560][10924][datanode2][gs_ctl]: finish receive tar files
[2022-10-0320:49:51.560][10924][datanode2][gs_ctl]: xlog end point:0/3000058[2022-10-0320:49:51.560][10924][datanode2][gs_ctl]: fetching MOT checkpoint
gs_ctl: no mot checkpoint exists
[2022-10-0320:49:51.561][10924][datanode2][gs_ctl]: waiting for background process to finish streaming...[2022-10-0320:49:56.254][10924][datanode2][gs_ctl]: starting fsync all files come from source.[2022-10-0320:49:56.663][10924][datanode2][gs_ctl]: finish fsync all files.[2022-10-0320:49:56.664][10924][datanode2][gs_ctl]: build dummy dw file success
[2022-10-0320:49:56.664][10924][datanode2][gs_ctl]: rename build status file success
[2022-10-0320:49:56.666][10924][datanode2][gs_ctl]: full build build completed(/opt/software/openGauss/data/slave).[2022-10-0320:49:56.699][10924][datanode2][gs_ctl]: waiting for server to start....0 LOG:[Alarm Module]can not read GAUSS_WARNING_TYPE env.0 LOG:[Alarm Module]Host Name: master
0 LOG:[Alarm Module]Host IP: master. Copy hostname directly in case of taking 10s to use 'gethostbyname' when /etc/hosts does not contain <HOST IP>0 LOG:[Alarm Module]Cluster Name: dbCluster
0 LOG:[Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line:570 WARNING: failed to open feature control file, please check whether it exists: FileName=gaussdb.version, Errno=2, Errmessage=No such file or directory.0 WARNING: failed to parse feature control file: gaussdb.version.0 WARNING: Failed to load the product control file, so gaussdb cannot distinguish product version.2022-10-0320:49:56.763[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: when starting as multi_standby mode, we couldn't support data replicaton.2022-10-0320:49:56.769[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG:[Alarm Module]can not read GAUSS_WARNING_TYPE env.2022-10-0320:49:56.769[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG:[Alarm Module]Host Name: master
2022-10-0320:49:56.769[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG:[Alarm Module]Host IP: master. Copy hostname directly in case of taking 10s to use 'gethostbyname' when /etc/hosts does not contain <HOST IP>2022-10-0320:49:56.769[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG:[Alarm Module]Cluster Name: dbCluster
2022-10-0320:49:56.769[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG:[Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line:572022-10-0320:49:56.772[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: loaded library "security_plugin"2022-10-0320:49:56.772[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] WARNING: could not create any HA TCP/IP sockets
2022-10-0320:49:56.773[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: InitNuma numaNodeNum:1 numa_distribute_mode: none inheritThreadPool:0.2022-10-0320:49:56.773[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: reserved memory for backend threads is:220 MB
2022-10-0320:49:56.773[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: reserved memory for WAL buffers is:128 MB
2022-10-0320:49:56.773[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: Set max backend reserve memory is:348 MB, max dynamic memory is:11071 MB
2022-10-0320:49:56.773[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: shared memory 356 Mbytes, memory context 11419 Mbytes, max process memory 12288 Mbytes
2022-10-0320:49:56.788[unknown][unknown] localhost 1404180536085760[0:0#0]0[CACHE] LOG: set data cache size(402653184)2022-10-0320:49:56.813[unknown][unknown] localhost 1404180536085760[0:0#0]0[SEGMENT_PAGE] LOG: Segment-page constants: DF_MAP_SIZE:8156, DF_MAP_BIT_CNT:65248, DF_MAP_GROUP_EXTENTS:4175872, IPBLOCK_SIZE:8168, EXTENTS_PER_IPBLOCK:1021, IPBLOCK_GROUP_SIZE:4090, BMT_HEADER_LEVEL0_TOTAL_PAGES:8323072, BktMapEntryNumberPerBlock:2038, BktMapBlockNumber:25, BktBitMaxMapCnt:5122022-10-0320:49:56.842[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: gaussdb: fsync file "/opt/software/openGauss/data/slave/gaussdb.state.temp" success
2022-10-0320:49:56.842[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: create gaussdb state file success: db state(STARTING_STATE), server mode(Standby), connection index(1)2022-10-0320:49:56.872[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: max_safe_fds =972, usable_fds =1000, already_open =182022-10-0320:49:56.874[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: the configure file /opt/software/openGauss/etc/gscgroup_admin.cfg doesn't exist or the size of configure file has changed. Please create it by root user!2022-10-0320:49:56.874[unknown][unknown] localhost 1404180536085760[0:0#0]0[BACKEND] LOG: Failed to parse cgroup config file.2022-10-0320:49:56.894[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:56.894[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:56.894[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:56.894[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] WARNING: Failed to obtain environment value $GAUSSLOG!2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] DETAIL: N/A
2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] CAUSE: Incorrect environment value.2022-10-0320:49:56.896[unknown][unknown] localhost 1404180536085760[0:0#0]0[EXECUTOR] ACTION: Please refer to backend log for more details.[2022-10-0320:49:57.704][10924][datanode2][gs_ctl]: done
[2022-10-0320:49:57.704][10924][datanode2][gs_ctl]: server started(/opt/software/openGauss/data/slave)[2022-10-0320:49:57.704][10924][datanode2][gs_ctl]: fopen build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
[2022-10-0320:49:57.704][10924][datanode2][gs_ctl]: fprintf build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
[2022-10-0320:49:57.705][10924][datanode2][gs_ctl]: fsync build pid file "/opt/software/openGauss/data/slave/gs_build.pid" success
importsql file
Would you like to create a demo database(yes/no)? yes
Load demoDB [school,finance] success.[complete successfully]: You can start or stop the database server using:
primary: gs_ctl start|stop|restart -D $GAUSSHOME/data/master -M primary
standby: gs_ctl start|stop|restart -D $GAUSSHOME/data/slave -M standby
4.查看openGauss进程
[admin@master simpleInstall]$ ps aux | grep gaussdb
admin 109462.813.22743360513368? Ssl 20:490:15/opt/software/openGauss/bin/gaussdb -D /opt/software/openGauss/data/slave -M standby
admin 112771.617.02980980656820? Ssl 20:520:06/opt/software/openGauss/bin/gaussdb -D /opt/software/openGauss/data/master -M primary
admin 119410.00.0110376896 pts/0 S+20:580:00 grep --color=auto gaussdb
[admin@master simpleInstall]$
5.查看Primary节点状态
[admin@master simpleInstall]$ gs_ctl query -D /opt/software/openGauss/data/master
[2022-10-0320:53:11.405][11394][][gs_ctl]: gs_ctl query ,datadir is /opt/software/openGauss/data/master
HA state:
local_role : Primary
static_connections :1
db_state : Normal
detail_information : Normal
Senders info:
sender_pid :11344
local_role : Primary
peer_role : Standby
peer_state : Normal
state : Streaming
sender_sent_location :0/403B8E8
sender_write_location :0/403B8E8
sender_flush_location :0/403B8E8
sender_replay_location :0/403B8E8
receiver_received_location :0/403B8E8
receiver_write_location :0/403B8E8
receiver_flush_location :0/403B8E8
receiver_replay_location :0/403B8E8
sync_percent :100%
sync_state : Sync
sync_priority :1
sync_most_available : Off
channel :192.168.3.201:5433-->192.168.3.201:41574
Receiver info:
No information
6.查看Standby节点状态
[admin@master simpleInstall]$ gs_ctl query -D /opt/software/openGauss/data/slave
[2022-10-0320:57:15.259][11776][][gs_ctl]: gs_ctl query ,datadir is /opt/software/openGauss/data/slave
HA state:
local_role : Standby
static_connections :1
db_state : Normal
detail_information : Normal
Senders info:
No information
Receiver info:
receiver_pid :11343
local_role : Standby
peer_role : Primary
peer_state : Normal
state : Normal
sender_sent_location :0/403BD68
sender_write_location :0/403BD68
sender_flush_location :0/403BD68
sender_replay_location :0/403BD68
receiver_received_location :0/403BD68
receiver_write_location :0/403BD68
receiver_flush_location :0/403BD68
receiver_replay_location :0/403BD68
sync_percent :100%
channel :192.168.3.201:41574<--192.168.3.201:5433
六、openGauss的服务启停
primary: gs_ctl start|stop|restart -D $GAUSSHOME/data/master -M primary
standby: gs_ctl start|stop|restart -D $GAUSSHOME/data/slave -M standby
七、openGauss基本操作——数据库的操作
1.登录openGauss
[admin@master simpleInstall]$ gsql -d postgres
gsql((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-2914:19:24 commit 0 last mr )
Non-SSL connection(SSL connection is recommended when requiring high-security)
Type "help"for help.
openGauss=#
2.查询所有数据库
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
finance | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
postgres | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
school | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
template0 | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|=c/admin +||||| admin=CTc/admin
template1 | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|=c/admin +||||| admin=CTc/admin(5 rows)
3.选择单个数据库
openGauss=# \c school;
Non-SSL connection(SSL connection is recommended when requiring high-security)
You are now connected to database "school" as user "admin".
4.创建数据库
school=# create database python;
CREATE DATABASE
5.删除数据库
school=# DROP DATABASE python;
6.修改数据库操作
school=# alter database test01 rename to test02;
ALTER DATABASE
school=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
finance | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
postgres | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
python | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
school | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|
template0 | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|=c/admin +||||| admin=CTc/admin
template1 | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|=c/admin +||||| admin=CTc/admin
test02 | admin | UTF8 | en_US.UTF-8| en_US.UTF-8|(7 rows)
school=#
八、openGauss基本操作——数据表的操作
1.创建数据表
CREATE TABLE demo(
name char(100),
age integer
);
2.查看数据表
test02=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------public| aa | table | admin |{orientation=row,compression=no}public| demo | table | admin |{orientation=row,compression=no}(2 rows)
test02=#
3.查询数据表内容
school=# select * from student;
std_id | std_name | std_sex | std_birth | std_in | std_address
--------+----------+---------+---------------------+---------------------+----------------------1| 张一 | 男 |1993-01-0100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
2| 张二 | 男 |1993-01-0200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
3| 张三 | 男 |1993-01-0300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
4| 张四 | 男 |1993-01-0400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
5| 张五 | 男 |1993-01-0500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
6| 张六 | 男 |1993-01-0600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
7| 张七 | 男 |1993-01-0700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
8| 张八 | 男 |1993-01-0800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
9| 张九 | 男 |1993-01-0900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
10| 李一 | 男 |1993-01-1000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
11| 李二 | 男 |1993-01-1100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
12| 李三 | 男 |1993-01-1200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
13| 李四 | 男 |1993-01-1300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
14| 李五 | 男 |1993-01-1400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
15| 李六 | 男 |1993-01-1500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
16| 李七 | 男 |1993-01-1600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
17| 李八 | 男 |1993-01-1700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
18| 李九 | 男 |1993-01-1800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
19| 王一 | 男 |1993-01-1900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
20| 王二 | 男 |1993-01-2000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
21| 王三 | 男 |1993-01-2100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
22| 王四 | 男 |1993-01-2200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
23| 王五 | 男 |1993-01-2300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
24| 王六 | 男 |1993-01-2400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
25| 王七 | 男 |1993-01-2500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
26| 王八 | 男 |1993-01-2600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
27| 王九 | 男 |1993-01-2700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
28| 钱一 | 男 |1993-01-2800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
29| 钱二 | 男 |1993-01-2900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
4.查询数表的结构
school=# \d student;
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer |not null
std_name | character varying(20)|not null
std_sex | character varying(6)|
std_birth |timestamp(0) without time zone |
std_in |timestamp(0) without time zone |not null
std_address | character varying(100)|
Indexes:"student_pkey" PRIMARY KEY,btree(std_id) TABLESPACE pg_default
5.更新数据
school=# update student set std_name='李梅梅' where std_name='张一';
UPDATE 1
select * from student;school=#
std_id | std_name | std_sex | std_birth | std_in | std_address
--------+----------+---------+---------------------+---------------------+----------------------2| 张二 | 男 |1993-01-0200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
3| 张三 | 男 |1993-01-0300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
4| 张四 | 男 |1993-01-0400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
5| 张五 | 男 |1993-01-0500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
6| 张六 | 男 |1993-01-0600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
7| 张七 | 男 |1993-01-0700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
8| 张八 | 男 |1993-01-0800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
9| 张九 | 男 |1993-01-0900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
10| 李一 | 男 |1993-01-1000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
11| 李二 | 男 |1993-01-1100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
12| 李三 | 男 |1993-01-1200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
13| 李四 | 男 |1993-01-1300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
14| 李五 | 男 |1993-01-1400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
15| 李六 | 男 |1993-01-1500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
16| 李七 | 男 |1993-01-1600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
17| 李八 | 男 |1993-01-1700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
18| 李九 | 男 |1993-01-1800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
19| 王一 | 男 |1993-01-1900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
20| 王二 | 男 |1993-01-2000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
21| 王三 | 男 |1993-01-2100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
22| 王四 | 男 |1993-01-2200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
23| 王五 | 男 |1993-01-2300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
24| 王六 | 男 |1993-01-2400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
25| 王七 | 男 |1993-01-2500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
26| 王八 | 男 |1993-01-2600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
27| 王九 | 男 |1993-01-2700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
28| 钱一 | 男 |1993-01-2800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
29| 钱二 | 男 |1993-01-2900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
30| 钱三 | 男 |1993-01-3000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
31| 钱四 | 男 |1993-02-0100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
32| 钱五 | 男 |1993-02-0200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
33| 钱六 | 男 |1993-02-0300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
34| 钱七 | 男 |1993-02-0400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
35| 钱八 | 男 |1993-02-0500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
36| 钱九 | 男 |1993-02-0600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
37| 吴一 | 男 |1993-02-0700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
38| 吴二 | 男 |1993-02-0800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
39| 吴三 | 男 |1993-02-0900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
40| 吴四 | 男 |1993-02-1000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
41| 吴五 | 男 |1993-02-1100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
42| 吴六 | 男 |1993-02-1200:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
43| 吴七 | 男 |1993-02-1300:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
44| 吴八 | 男 |1993-02-1400:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
45| 吴九 | 男 |1993-02-1500:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
46| 柳一 | 男 |1993-02-1600:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
47| 柳二 | 男 |1993-02-1700:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
48| 柳三 | 男 |1993-02-1800:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
49| 柳四 | 男 |1993-02-1900:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
50| 柳五 | 男 |1993-02-2000:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
1| 李梅梅 | 男 |1993-01-0100:00:00|2011-09-0100:00:00| 江苏省南京市雨花台区
6.删除表数据
delete from student where std_id=2;
7.升序和降序查看数据
# 升序
select * from student order by std_id ASC;
# 降序
select * from student order by std_id DESC;
8.去重查询
#name唯一查询
select distinct std_name from student;#name唯一查询，携带age数据
select distinct std_name,std_address from student;
九、openGauss使用总结
1.openGauss的软件架构
openGauss是单机系统，支持一主多备，最多支持八个备机。
2.openGauss的应用场景
1.交易型应用：
大并发、大数据量、以联机事务处理为主的交易型应用。例如，电商、金融、O2O、电信CRM/计费等类型的应用，应用可按需选择不同的主备部署模式。
2.物联网数据：
传感监控设备多、采样率高、数据存储为追加模型，操作和分析并重的场景。例如，工业监控、远程控制、智慧城市的延展、智能家居、车联网等物联网场景。
3.openGauss常用命令
gsql -d postgres -p 8000 #连接数据库
ALTER ROLE omm IDENTIFIED BY 'XXXXXXXX' REPLACE 'XXXXXXXX'; #修改密码
内部命令：
\copyright #查询openGauss的版本和版权信息
\h #获取各种openGauss的SQL命令的帮助语法
\l #查询openGauss中所有的数据库和描述信息
\q #退出数据库
4.数据库的逻辑结构
1.openGauss是一款关系型数据库管理系统（RDBMS）。关系型数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据。
2.openGauss的数据库节点负责存储数据，其存储介质也是磁盘。逻辑视角下，可以看到数据库节点上对象包含表空间、数据库、数据文件、表、数据块。
5.查询主库运行状态
[admin@master simpleInstall]$ gs_ctl status -D $GAUSSHOME/data/master -M primary
[2022-10-0322:50:09.008][26351][][gs_ctl]: gs_ctl status,datadir is /opt/software/openGauss/data/master
gs_ctl: server is running(PID:11277)/opt/software/openGauss/bin/gaussdb "-D""/opt/software/openGauss/data/master""-M" "primary
6.gs_ctl的option解释
本文转载自: https://blog.csdn.net/jks212454/article/details/127153482
版权归原作者 江湖有缘 所有， 如有侵权，请联系我们删除。
版权归原作者 江湖有缘 所有， 如有侵权，请联系我们删除。