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 - omm
Last login: Sat Jul 11 16:09:59 CST 2020 on pts/2
[omm@openGauss ~]$
[omm@openGauss ~]$ env | grep GAUSS
GAUSS_VERSION=1.0.0
GAUSSHOME=/opt/gaussdb/install/app
GAUSS_ENV=1
GAUSSLOG=/var/log/omm/omm
[omm@openGauss ~]$ gs_install -X /opt/openGauss/clusterconfig.xml
Parsing 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 files
The sslcert will be generated in /opt/gaussdb/install/app/share/sslcert/om
Cluster 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 start
Starting 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 = 4
2020-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:4
2020-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 sockets
2020-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 memory
2020-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 per
shared_buffers = 1GB                    # min 128kB
bulk_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 per
shared_buffers = 512MB                  # min 128kB
bulk_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 per
shared_buffers = 256MB                  # min 128kB
bulk_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 = 4
2020-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:4
2020-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 sockets
2020-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" success
2020-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 = 12
2020-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 -mf
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@openGauss ~]$ ps -ef | grep gauss | egrep -v "grep"
[omm@openGauss ~]$
[omm@openGauss ~]$ gs_om -t start
Starting 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 26000
gsql ((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   : Normal
redistributing  : No
current_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 26000
gsql ((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 ROLE
postgres=#  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   : Normal
redistributing  : No
current_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             : Normal
redistributing            : No
-----------------------------------------------------------------------
node                      : 1
node_name                 : openGauss
node                      : 1
instance_id               : 6001
node_ip                   : 192.168.52.88
data_path                 : /opt/gaussdb/install/data/db1
type                      : Datanode
instance_state            : Primary
static_connections        : 0
HA_state                  : Normal
reason                    : Normal
sender_sent_location      : 0/0
sender_write_location     : 0/0
sender_flush_location     : 0/0
sender_replay_location    : 0/0
receiver_received_location: 0/0
receiver_write_location   : 0/0
receiver_flush_location   : 0/0
receiver_replay_location  : 0/0
sync_state                : Async
-----------------------------------------------------------------------
[omm@openGauss ~]$
--检查数据库性能
[omm@openGauss ~]$ gs_checkperf
Cluster 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 26000
gsql ((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=# help
You 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 quit
postgres=# 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/omm
template1 | 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=# \d
No 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 TABLE
postgres=# \d
                         List of relations
Schema |  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

以上官方文档也是学习的必读资料，建议大家收藏哦！本次学习体验就先到这里了，后期有机会在学习分享吧。写作不易，此文如果对你有帮助，请支持“在看”与转发，您的支持便是我不断写作的最大的动力，加油，让我们一起努力做更好的自己！

点亮在看，你最好看！