其他
openGaussDB 初体验（下）
作者 | JiekeXu
来源 | JiekeXu之路（ID: JiekeXu_IT）
转载请联系授权 | (微信ID：xxq1426321293)
大家好，我是 JiekeXu,很高兴又和大家见面了，今天分享下 openGaussDB 初体验(下)。本文首发于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我吧！
由于上篇文章较长，故分开写，点击可看上文 openGauss DB 初体验（上）看完后进入到如下接着来正式安装 openGauss。
三、正式安装
（1）切换到 omm 用户
（2）使用 gs_install -X +xml 配置文件进行安装
[root@openGauss opt]# su - ommLast login: Sat Jul 11 16:09:59 CST 2020 on pts/2[omm@openGauss ~]$[omm@openGauss ~]$ env | grep GAUSSGAUSS_VERSION=1.0.0GAUSSHOME=/opt/gaussdb/install/appGAUSS_ENV=1GAUSSLOG=/var/log/omm/omm[omm@openGauss ~]$ gs_install -X /opt/openGauss/clusterconfig.xmlParsing the configuration file.Check preinstall on every node.Successfully checked preinstall on every node.Creating the backup directory.Successfully created the backup directory.begin deploy..Installing the cluster.begin prepare Install Cluster..Checking the installation environment on all nodes.begin install Cluster..Installing applications on all nodes.Successfully installed APP.begin init Instance..encrypt cipher and rand files for database.Please enter password for database:Please repeat for database:begin to create CA cert filesThe sslcert will be generated in /opt/gaussdb/install/app/share/sslcert/omCluster installation is completed.Configuring.Deleting instances from all nodes.Successfully deleted instances from all nodes.Checking node configuration on all nodes.Initializing instances on all nodes.Updating instance configuration on all nodes.Check consistence of memCheck and coresCheck on database nodes.Configuring pg_hba on all nodes.Configuration is completed.[GAUSS-51400] : Failed to execute the command: source /home/omm/.bashrc;python3 '/opt/gaussdb/install/om/script/local/Install.py' -t start_cluster -U omm:dbgrp -X /opt/openGauss/clusterconfig.xml -R /opt/gaussdb/install/app -c opengauss -l /var/log/omm/omm/om/gs_local.log --alarm=/opt/huawei/snas/bin/snas_cm_cmd --time_out=300 .Error:Using omm:dbgrp to install database.Using installation program path : /opt/gaussdb/install/app_0bd0ce80$GAUSSHOME points to /opt/gaussdb/install/app_0bd0ce80, no need to create symbolic link.Traceback (most recent call last): File "/opt/gaussdb/install/om/script/local/Install.py", line 645, in <module> functionDict[g_opts.action]() File "/opt/gaussdb/install/om/script/local/Install.py", line 588, in startCluster dn.start(self.time_out) File "/opt/gaussdb/install/om/script/local/../gspylib/component/Kernel/Kernel.py", line 87, in start + " Error: Please check the gs_ctl log for "Exception: [GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details.[GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details.（3）如下启动报错，使用 gs_ctl 启动显示更多详细信息
[omm@openGauss ~]$ gs_om -t startStarting cluster.=========================================[GAUSS-53600]: Can not start the database, the cmd is source /home/omm/.bashrc; python3 '/opt/gaussdb/install/om/script/local/StartInstance.py' -U omm -R /opt/gaussdb/install/app -t 300 --security-mode=off, Error:[FAILURE] openGauss:[GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details..
####————> gs_ctl 启动显示更多详细信息[omm@openGauss ~]$ gs_ctl start -D /opt/gaussdb/install/data/db1[2020-07-12 00:16:01.408][65296][][gs_ctl]: gs_ctl started,datadir is -D "/opt/gaussdb/install/data/db1" [2020-07-12 00:16:01.535][65296][][gs_ctl]: waiting for server to start....0 [BACKEND] LOG: Begin to start openGauss Database.2020-07-12 00:16:01.850 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 DB001 0 [REDO] LOG: Recovery parallelism, cpu count = 4, max = 4, actual = 42020-07-12 00:16:01.850 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 DB001 0 [REDO] LOG: ConfigRecoveryParallelism, true_max_recovery_parallelism:4, max_recovery_parallelism:42020-07-12 00:16:01.850 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: gaussdb.state does not exist, and skipt setting since it is optional.2020-07-12 00:16:01.850 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: Transparent encryption disabled.2020-07-12 00:16:01.890 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets2020-07-12 00:16:01.898 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: InitNuma numaNodeNum: 1 numa_distribute_mode: none inheritThreadPool: 0.2020-07-12 00:16:01.899 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4213 Mbytes) is larger.2020-07-12 00:16:01.899 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 42809 0 [BACKEND] FATAL: could not create shared memory segment: Cannot allocate memory2020-07-12 00:16:01.899 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 42809 0 [BACKEND] DETAIL: Failed system call was shmget(key=26000001, size=4418076672, 03600).2020-07-12 00:16:01.899 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 42809 0 [BACKEND] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter. You can either reduce the request size or reconfigure the kernel with larger SHMALL. To reduce the request size (currently 4418076672 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers. The PostgreSQL documentation contains more information about shared memory configuration.2020-07-12 00:16:01.916 5f09e5c1.1 [unknown] 140308361469696 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: FiniNuma allocIndex: 0.[2020-07-12 00:16:02.537][65296][][gs_ctl]: waitpid 65306 failed, exitstatus is 256, ret is 2[2020-07-12 00:16:02.538][65296][][gs_ctl]: stopped waiting[2020-07-12 00:16:02.538][65296][][gs_ctl]: could not start server[2020-07-12 00:16:02.538][65296][][gs_ctl]: Examine the log output.[omm@openGauss ~]$
####——> 修改 pg 配置文件 postgresql.conf 关于 shared_buffers 的值[root@openGauss db1]# cd /opt/gaussdb/install/data/db1[root@openGauss db1]# more postgresql.conf | grep shared# Note: Increasing max_connections costs ~400 bytes of shared memory pershared_buffers = 1GB # min 128kBbulk_write_ring_size = 2GB # for bulkload, max shared_buffers#standby_shared_buffers_fraction = 0.3 #control shared buffers use in standby, 0.1-1.0# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory#shared_preload_libraries = '' # (change requires restart)#wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers# Note: Each lock table slot uses ~270 bytes of shared memory, and there are[root@openGauss db1]#[root@openGauss db1]# pwd/opt/gaussdb/install/data/db1[root@openGauss db1]#[root@openGauss db1]# vim postgresql.conf[root@openGauss db1]# more postgresql.conf | grep shared# Note: Increasing max_connections costs ~400 bytes of shared memory pershared_buffers = 512MB # min 128kBbulk_write_ring_size = 2GB # for bulkload, max shared_buffers#standby_shared_buffers_fraction = 0.3 #control shared buffers use in standby, 0.1-1.0# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory#shared_preload_libraries = '' # (change requires restart)#wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers# Note: Each lock table slot uses ~270 bytes of shared memory, and there are（4）当修改 pg 配置文件 postgresql.conf 关于 shared_buffers 的值为 256M 时虽有告警，但启动成功了。
[root@openGauss db1]# more postgresql.conf | grep shared# Note: Increasing max_connections costs ~400 bytes of shared memory pershared_buffers = 256MB # min 128kBbulk_write_ring_size = 2GB # for bulkload, max shared_buffers#standby_shared_buffers_fraction = 0.3 #control shared buffers use in standby, 0.1-1.0# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory#shared_preload_libraries = '' # (change requires restart)#wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
[omm@openGauss ~]$ gs_ctl start -D /opt/gaussdb/install/data/db1[2020-07-12 00:34:08.675][65571][][gs_ctl]: gs_ctl started,datadir is -D "/opt/gaussdb/install/data/db1" [2020-07-12 00:34:08.837][65571][][gs_ctl]: waiting for server to start....0 [BACKEND] LOG: Begin to start openGauss Database.2020-07-12 00:34:09.095 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 DB001 0 [REDO] LOG: Recovery parallelism, cpu count = 4, max = 4, actual = 42020-07-12 00:34:09.095 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 DB001 0 [REDO] LOG: ConfigRecoveryParallelism, true_max_recovery_parallelism:4, max_recovery_parallelism:42020-07-12 00:34:09.095 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: gaussdb.state does not exist, and skipt setting since it is optional.2020-07-12 00:34:09.095 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: Transparent encryption disabled.2020-07-12 00:34:09.136 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets2020-07-12 00:34:09.148 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: InitNuma numaNodeNum: 1 numa_distribute_mode: none inheritThreadPool: 0.2020-07-12 00:34:09.148 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (1876 Mbytes) is larger.2020-07-12 00:34:09.361 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [CACHE] LOG: set data cache size(805306368)2020-07-12 00:34:09.488 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [CACHE] LOG: set metadata cache size(268435456)2020-07-12 00:34:10.841 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: gaussdb: fsync file "/opt/gaussdb/install/data/db1/gaussdb.state.temp" success2020-07-12 00:34:10.841 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: create gaussdb state file success: db state(STARTING_STATE), server mode(Normal)2020-07-12 00:34:10.868 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: max_safe_fds = 978, usable_fds = 1000, already_open = 122020-07-12 00:34:10.871 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: user configure file is not found, it will be created.2020-07-12 00:34:10.900 5f09ea00.1 [unknown] 140315229097728 [unknown] 0 dn_6001 00000 0 [BACKEND] LOG: Success to start openGauss Database, please press any key to exit....[2020-07-12 00:34:12.107][65571][][gs_ctl]: done[2020-07-12 00:34:12.107][65571][][gs_ctl]: server started (/opt/gaussdb/install/data/db1)[omm@openGauss ~]$###----如上算是启动成功了四、正常启动关闭，连接到数据库
（1）关闭启动数据库，查看进程
[omm@openGauss ~]$ gs_om -t stop -mfStopping cluster.=========================================Successfully stopped cluster.=========================================End stop cluster.[omm@openGauss ~]$ ps -ef | grep gauss | egrep -v "grep"[omm@openGauss ~]$[omm@openGauss ~]$ gs_om -t startStarting cluster.==================================================================================Successfully started.[omm@openGauss ~]$ ps -ef | grep gauss | egrep -v "grep"omm 66764 1 20 00:41 pts/2 00:00:02 /opt/gaussdb/install/app/bin/gaussdb -D /opt/gaussdb/install/data/db1[omm@openGauss ~]$ gsql -d postgres -p 26000gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.--使用 “gs_om -t status –detail” 命令查询 openGauss 各实例情况。[omm@openGauss ~]$ gs_om -t status --detail[ Cluster State ]cluster_state : Normalredistributing : Nocurrent_az : AZ_ALL[ Datanode State ]node node_ip instance state---------------------------------------------------------------------------------------1 openGauss 192.168.52.88 6001 /opt/gaussdb/install/data/db1 P Primary Normal（2）需重置用户密码
登陆进来之后，由于安全问题还需要重置密码，而且新密码和旧密码不能一样，注意密码复杂度要求。
[omm@openGauss ~]$ gsql -d postgres -p 26000gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.postgres=# SELECT version();ERROR: Please use "ALTER ROLE user_name IDENTIFIED BY 'password' REPLACE 'old password';" to modify the initial password of user omm before operation!postgres=# ALTER ROLE omm IDENTIFIED BY 'openGauss_1' REPLACE 'openGauss_1';ERROR: New password should not equal to the old ones.postgres=# ALTER ROLE omm IDENTIFIED BY 'openGauss@12' REPLACE 'openGauss_1';ALTER ROLEpostgres=# SELECT version(); version -----------------------------------------------------------------------------------------------------------------------------------(openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 8.2.0, 64-bit(1 row)postgres=#postgres=# create user JiekeXu identified by 'openGanss_1';CREATE ROLE（3） 检查数据库状态，性能
gs_om -t status --detail
gs_om -t status -all
gs_checkperf
[omm@openGauss ~]$ gs_om -t status --detail[ Cluster State ]cluster_state : Normalredistributing : Nocurrent_az : AZ_ALL[ Datanode State ]node node_ip instance state---------------------------------------------------------------------------------------1 openGauss 192.168.52.88 6001 /opt/gaussdb/install/data/db1 P Primary Normal[omm@openGauss ~]$[omm@openGauss ~]$ gs_om -t status --all-----------------------------------------------------------------------cluster_state : Normalredistributing : No-----------------------------------------------------------------------node : 1node_name : openGaussnode : 1instance_id : 6001node_ip : 192.168.52.88data_path : /opt/gaussdb/install/data/db1type : Datanodeinstance_state : Primarystatic_connections : 0HA_state : Normalreason : Normalsender_sent_location : 0/0sender_write_location : 0/0sender_flush_location : 0/0sender_replay_location : 0/0receiver_received_location: 0/0receiver_write_location : 0/0receiver_flush_location : 0/0receiver_replay_location : 0/0sync_state : Async-----------------------------------------------------------------------[omm@openGauss ~]$--检查数据库性能[omm@openGauss ~]$ gs_checkperfCluster statistics information: Host CPU busy time ratio : 3.19 % MPPDB CPU time % in busy time : 10.73 % Shared Buffer Hit ratio : 98.80 % In-memory sort ratio : 0 Physical Reads : 377 Physical Writes : 144 DB size : 28 MB Total Physical writes : 144 Active SQL count : 3 Session count : 4[omm@openGauss ~]$( 4 ) 安装目录结构说明
[omm@openGauss ~]$ tree -d -L 3 gaussdb/gaussdb/├── corefile # core 文件存放目录，类似于 pg 中的 core 文件，用于数据库 crash 追踪├── install│ ├── app -> /opt/gaussdb/install/app_0bd0ce80 #数据库安装路径，为app_0bd0ce80的软连接│ ├── app_0bd0ce80│ │ ├── bin #数据库命令执行路径│ │ ├── etc #数据库配置路径（如kerberos路径和云上obs映射区域配置）│ │ ├── include #数据库头文件│ │ ├── lib #动态链接库│ │ └──share #共享目录库(包括pg扩展和ssl根证书文件)│ ├──data #数据目录│ │ └── db1 #数据库文件目录│ └── om #运维管理目录│ ├── lib #执行脚本使用库│ └── script #工具脚本路径└── tmp五、连接到数据库简单应用
当使用 gsql 客户端连接到数据库时，默认连接到 postgres 数据库，使用 “\l” 列出所有数据库， “\d” 列出当前数据库所有的表，“\q” 退出客户端连接。由于 openGauss 是基于 postgresql 9.2.4 开发的，很多命令都和 pg 很类似，可直接使用。
[omm@openGauss ~]$ gsql -d postgres -p 26000gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.postgres=# helpYou are using gsql, the command-line interface to gaussdb.Type: \copyright for distribution terms \h for help with SQL commands \? for help with gsql commands \g or terminate with semicolon to execute query \q to quitpostgres=# SHOW server_version;server_version----------------9.2.4(1 row)---查看数据库postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges-----------+-------+-----------+---------+-------+-------------------postgres | omm | SQL_ASCII | C | C |template0 | omm | SQL_ASCII | C | C | =c/omm + | | | | | omm=CTc/ommtemplate1 | omm | SQL_ASCII | C | C | =c/omm + | | | | | omm=CTc/omm(3 rows)---某一个参数查看，全部参数查看使用 SHOW ALL;postgres=# SELECT * FROM pg_settings WHERE NAME='server_version'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val| max_val | enumvals | boot_val | reset_val | sourcefile | sourceline----------------+---------+------+----------------+---------------------------+------------+----------+---------+---------+---------+---------+----------+----------+-----------+------------+------------server_version | 9.2.4 | | Preset Options | Shows the server version. | | internal | string | default | | | | 9.2.4 | 9.2.4 | | (1 row)-- 查看当前库下的所有表postgres=# \dNo relations found.postgres=# create table jikexu(id int primary key,name varchar(40));NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "jikexu_pkey" for table "jikexu"CREATE TABLEpostgres=# \d List of relationsSchema | Name | Type | Owner | Storage --------+--------+-------+-------+----------------------------------public | jikexu | table | omm | {orientation=row,compression=no}(1 row)以下列出比较常用的命令：
六、openGauss 管理员指南：
数据库启停，状态查看，主备切换，备份恢复，日志设置等一些管理员操作指南均可查看官方文档。如下地址，中文文档，查看很是方便。
https://opengauss.org/zh/docs/1.0.0/docs/Administratorguide/Administratorguide.html
以上官方文档也是学习的必读资料，建议大家收藏哦！本次学习体验就先到这里了，后期有机会在学习分享吧。写作不易，此文如果对你有帮助，请支持“在看”与转发，您的支持便是我不断写作的最大的动力，加油，让我们一起努力做更好的自己！
Oracle 12c 及以上版本补丁更新说明及下载方法（收藏版）
Oracle 19c 之多租户 PDB 连接与访问（三）
Oracle 12C 最新补丁下载与安装操作指北
关于 Oracle ACFS 相关知识的简单学习
Oracle 相关认证证书查询及真伪辨别
Oracle 11g ADG 快照备库切换步骤
openGaussDB 初体验（上）
点亮在看，你最好看！