数据库系列之openGauss主备环境部署

openGauss是衍生自PostgreSQL可以对标Oracle的国产化开源数据库系统，在最近一次技术交流论坛上接触到openGauss，本文将部署一套openGauss主备环境以实验。

---

1、OpenGauss基本概念

openGauss是衍生自PostgreSQL-XC的单机系统，在这样的系统架构中，业务数据存储在单个物理节点上，数据访问任务被推送到服务节点执行，通过服务器的高并发，实现对数据处理的快速响应。同时通过日志复制可以把数据复制到备机，提供数据的高可靠和读扩展。

1.1 OpenGauss逻辑架构

如下图所示为OpenGauss一主一备的逻辑架构：

• OM：运维管理模块（Operation Manager）。提供数据库日常运维、配置管理的管理接口、工具。
• 客户端驱动：负责接收来自应用的访问请求，并向应用返回执行结果。客户端驱动负责与openGauss实例通信，发送应用的SQL命令，接收openGauss实例的执行结果。
• openGauss（主备）：负责存储业务数据、执行数据查询任务以及向客户端返回执行结果。openGauss实例包含主、备两种类型，支持一主多备。
• Storage：服务器的本地存储资源，持久化存储数据。

1.2 OpenGauss和PostgreSQL差别

OpenGauss和PostgreSQL的差别主要在几个方面：

• 运行时模型：OpenGauss是线程池模型、PostgreSQL是进程模型
• 事务处理机制：OpenGauss是64位事务ID，有增量checkpoint机制
• 数据存储与组织：OpenGauss是行存、列存以及内存引擎
• 查询优化器：在优化器和SQL解析层做了优化

1.3 OpenGauss逻辑模块

OpenGauss的逻辑模块主要分为：

• 线程管理：包括业务处理线程、数据写线程、日志写线程、检查点线程等
• 通信管理：分为控制协议处理和控制命令信号处理
• SQL引擎：包括SQL解析、SQL优化、SQL执行等
• 存储引擎：分为行存、列存和内存引擎以及空间管理、索引管理和检查点管理等
• 安全管理：包括用户身份和访问控制等
• 客户端驱动：支持CLI、ODBC、JDBC和LIBpq
• 通用组件：包括数据字典、内存管理、数据类型和内置函数
• OM&CM工具：包括客户端命令行工具、数据库实例管理工具、备份恢复工具、OM管理和CM监控工具等

2、OpenGauss主备环境部署

2.1 环境准备

1）服务器列表

Hostname	IP	实例	系统
tango-rac01	192.168.112.135	Primary	Centos_7.4
tango-rac02	192.168.112.136	Standby	Centos_7.4

2）从openGauss开源社区下载对应平台的安装包

下载地址为https://opengauss.org/zh/download.html，选择openGauss_2.0.0企业版

3）python 3.6环境安装

[omm@tango-rac01 gauss_xlog]$ python3 -V
Python 3.6.10

需要使用Python 3.6环境，安装参考https://www.cnblogs.com/lemon-feng/p/11208435.html

4）关闭防火墙

[root@tango-rac01 ~]$ systemctl stop firewalld.service
[root@tango-rac01 ~]$ systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

2.3 配置XML文件

安装openGauss前需要创建cluster_config.xml文件，cluster_config.xml文件包含部署openGauss的服务器信息、安装路径、IP地址以及端口号等。

1）配置数据库名称及各项目录

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
  <!-- 整体信息 -->
  <CLUSTER>
  <!-- 数据库名称 -->
    <PARAM name="clusterName" value="openGauss01" />  
  <!-- 数据库节点名称(hostname) -->
    <PARAM name="nodeNames" value="tango-rac01,tango-rac02" />
  <!-- 数据库安装目录-->
    <PARAM name="gaussdbAppPath" value="/usr/local/openGauss/app" />
  <!-- 日志目录-->
    <PARAM name="gaussdbLogPath" value="/usr/local/openGauss/log/ " />
  <!-- 临时文件目录-->
    <PARAM name="tmpMppdbPath" value="/usr/local/openGauss/tmp" />
  <!--数据库工具目录-->
    <PARAM name="gaussdbToolPath" value="/usr/local/openGauss/om" />
  <!--数据库core文件目录-->
    <PARAM name="corePath" value="/usr/local/openGauss/corefile"/>
  <!-- 节点IP，与数据库节点名称列表一一对应 -->
    <PARAM name="backIp1s" value="192.168.112.135,192.168.112.136"/>
  </CLUSTER>

2）配置Host基本信息

<!-- 每台服务器上的节点部署信息 -->
<DEVICELIST>
<!-- 节点1上的部署信息 -->
<DEVICE sn="tango-rac01">
<!-- 节点1的主机名称 -->
<PARAM name="name" value=" tango-rac01"/>
<!-- 节点1所在的AZ及AZ优先级 -->
<PARAM name="azName" value="AZ1"/>
<PARAM name="azPriority" value="1"/>
<!-- 节点1的IP，如果服务器只有一个网卡可用，将backIP1和sshIP1配置成同一个IP -->
<PARAM name="backIp1" value="192.168.112.135"/>
<PARAM name="sshIp1" value="192.168.112.135"/>

3）配置数据库主节点信息

<!--DBnode-->
<PARAM name="dataNum" value="1"/>
<!--数据库端口号-->
<PARAM name="dataPortBase" value="15400"/>
<!--数据库主节点上的数据目录，及备机数据目录-->
<PARAM name="dataNode1" value="/usr/local/openGauss/data/dn,tango-rac02,/usr/local/openGauss/data/dn"/>
<!--数据库主节点上的xlog目录，及备机xlog目录-->
<PARAM name="dataNodeXlogPath1" value="/home/omm/gauss_xlog,/home/omm/gauss_xlog "/>
<!--数据库节点上设定同步模式的节点数-->
<PARAM name="dataNode1_syncNum" value="0"/>

4）一主一备的配置文件如下所示：

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
    <!-- openGauss整体信息 -->
    <CLUSTER>
  		<!-- 数据库名称 -->
    	<PARAM name="clusterName" value="openGauss01" />  
  		<!-- 数据库节点名称(hostname) -->
    	<PARAM name="nodeNames" value="tango-rac01,tango-rac02" />
  		<!-- 数据库安装目录-->
    	<PARAM name="gaussdbAppPath" value="/usr/local/openGauss/app" />
  		<!-- 日志目录-->
    	<PARAM name="gaussdbLogPath" value="/usr/local/openGauss/log " />
  		<!-- 临时文件目录-->
    	<PARAM name="tmpMppdbPath" value="/usr/local/openGauss/tmp" />
  		<!--数据库工具目录-->
    	<PARAM name="gaussdbToolPath" value="/usr/local/openGauss/om" />
  		<!--数据库core文件目录-->
    	<PARAM name="corePath" value="/usr/local/openGauss/corefile"/>
  		<!-- 节点IP，与数据库节点名称列表一一对应 -->
    	<PARAM name="backIp1s" value="192.168.112.135,192.168.112.136"/>
    </CLUSTER>
    <!-- 每台服务器上的节点部署信息 -->
    <DEVICELIST>
			<!-- 节点1上的部署信息 -->
			<DEVICE sn="tango-rac01">
				<!-- 节点1的主机名称 -->
				<PARAM name="name" value="tango-rac01"/>
				<!-- 节点1所在的AZ及AZ优先级 -->
				<PARAM name="azName" value="AZ1"/>
				<PARAM name="azPriority" value="1"/>
				<!-- 节点1的IP，如果服务器只有一个网卡可用，将backIP1和sshIP1配置成同一个IP -->
				<PARAM name="backIp1" value="192.168.112.135"/>
				<PARAM name="sshIp1" value="192.168.112.135"/>      
				      
	    	<!--DBnode-->
				<PARAM name="dataNum" value="1"/>
				<!--数据库端口号-->
				<PARAM name="dataPortBase" value="15400"/>
				<!--数据库主节点上的数据目录，及备机数据目录-->
				<PARAM name="dataNode1" value="/usr/local/openGauss/data/dn,tango-rac02,/usr/local/openGauss/data/dn"/>
				<!--数据库主节点上的xlog目录，及备机xlog目录-->
				<PARAM name="dataNodeXlogPath1" value="/home/omm/gauss_xlog,/home/omm/gauss_xlog"/>
				<!--数据库节点上设定同步模式的节点数-->
				<PARAM name="dataNode1_syncNum" value="0"/>
      </DEVICE>

      <!-- 节点2上的节点部署信息，其中“name”的值配置为主机名称 -->
      <DEVICE sn="tango-rac02">
          <!-- 节点2的主机名称 -->
          <PARAM name="name" value="tango-rac02"/>
          <!-- 节点2所在的AZ及AZ优先级 -->
          <PARAM name="azName" value="AZ1"/>
          <PARAM name="azPriority" value="1"/>
          <!-- 节点2的IP，如果服务器只有一个网卡可用，将backIP1和sshIP1配置成同一个IP -->
          <PARAM name="backIp1" value="192.168.112.136"/>
          <PARAM name="sshIp1" value="192.168.112.136"/>
			</DEVICE>
    </DEVICELIST>
</ROOT>

2.4 初始化安装环境

2.4.1 准备安装用户及环境

1）以root用户登录待安装openGauss的任意主机，并按规划创建存放安装包的目录

[root@tango-rac01 src]# mkdir /usr/local/openGauss
[root@tango-rac01 src]# chmod 755 -R /usr/local/openGauss
[root@tango-rac01 src]# chown omm:dbgrp -R /usr/local/openGauss

2）将安装包“openGauss-2.0.0-CentOS-64bit-all.tar.gz”和配置文件“cluster_config.xml”都上传至/usr/local/openGauss目录中。

3）在安装包所在的目录下，解压安装包openGauss-2.0.0-CentOS-64bit-all.tar.gz。安装包解压后，会有OM安装包和Server安装包。继续解压OM安装包，会在/usr/local/openGauss目路径下自动生成script子目录，并且在script目录下生成gs_preinstall等各种OM工具脚本。

[root@tango-rac01 openGauss]# tar -xzvf openGauss-2.0.0-CentOS-64bit-all.tar.gz
[root@tango-rac01 openGauss]# tar -xzvf openGauss-2.0.0-CentOS-64bit-om.tar.gz
[root@tango-rac01 openGauss]# ll
total 198996
drwxr-xr-x. 14 root root      4096 Mar 31 21:16 lib
-rw-r--r--.  1 root root 101382908 Apr  5 22:16 openGauss-2.0.0-CentOS-64bit-all.tar.gz
-rw-r--r--.  1 root root        65 Mar 31 21:16 openGauss-2.0.0-CentOS-64bit-om.sha256
-rw-r--r--.  1 root root  12646330 Mar 31 21:16 openGauss-2.0.0-CentOS-64bit-om.tar.gz
-rw-r--r--.  1 root root        65 Mar 31 21:16 openGauss-2.0.0-CentOS-64bit.sha256
-rw-r--r--.  1 root root  89573052 Mar 31 21:16 openGauss-2.0.0-CentOS-64bit.tar.bz2
drwxr-xr-x.  6 root root      4096 Mar 31 21:16 script
drwxr-xr-x.  2 root root      4096 Mar 31 21:16 simpleInstall
-rw-------.  1 root root        65 Mar 31 21:15 upgrade_sql.sha256
-rw-------.  1 root root    133704 Mar 31 21:15 upgrade_sql.tar.gz
-rw-r--r--.  1 root root        32 Mar 31 21:16 version.cfg

4）进入到工具脚本存放目录下

cd /usr/local/openGauss/script

5）预安装过程中，会对hostname进行检查。

[omm@tango-rac01 gauss_xlog]$ cat /etc/hosts
192.168.112.135  tango-rac01  #Gauss OM IP Hosts Mapping
192.168.112.136  tango-rac02  #Gauss OM IP Hosts Mapping

6）创建用户omm和用户组dbgrp并设置互信访问

[root@tango-rac01 script]# groupadd dbgrp
[root@tango-rac01 script]# adduser omm -G dbgrp
[root@tango-rac01 script]# passwd omm

7）使用gs_preinstall准备安装环境，执行以下命令安装

[root@tango-rac01 openGauss]# cd /usr/local/openGauss/script
[root@tango-rac01 script]# [root@tango-rac01 script]# ./gs_preinstall -U omm -G dbgrp -X /usr/local/openGauss/cluster_config.xml --non-interactive
Parsing the configuration file.
Successfully parsed the configuration file.
Installing the tools on the local node.
Successfully installed the tools on the local node.
Setting pssh path
Successfully set core path.
Distributing package.
Begin to distribute package to tool path.
Successfully distribute package to tool path.
Begin to distribute package to package path.
Successfully distribute package to package path.
Successfully distributed package.
Preparing SSH service.
Successfully prepared SSH service.
Installing the tools in the cluster.
Successfully installed the tools in the cluster.
Checking hostname mapping.
Successfully checked hostname mapping.
Checking OS software.
Successfully check os software.
Checking OS version.
Successfully checked OS version.
Creating cluster's path.
Successfully created cluster's path.
Setting SCTP service.
Successfully set SCTP service.
Set and check OS parameter.
Setting OS parameters.
Successfully set OS parameters.
Warning: Installation environment contains some warning messages.
Please get more details by "/usr/local/openGauss/script/gs_checkos -i A -h tango-rac01,tango-rac02 --detail".
Set and check OS parameter completed.
Preparing CRON service.
Successfully prepared CRON service.
Setting user environmental variables.
Successfully set user environmental variables.
Setting the dynamic link library.
Successfully set the dynamic link library.
Setting Core file
Successfully set core path.
Setting pssh path
Successfully set pssh path.
Set ARM Optimization.
No need to set ARM Optimization.
Fixing server package owner.
Setting finish flag.
Successfully set finish flag.
Preinstallation succeeded.

此模式要求用户确保在执行前，已经建立了各节点root用户互信和openGauss用户互信

2.4.2 手动建立互信

1）在tango-rac01生成公钥对

[root@tango-rac01 ~]# ssh-keygen -t rsa
[root@tango-rac01 .ssh]# ll
total 16
-rw-------. 1 root root  398 Apr  6 14:21 authorized_keys
-rw-------. 1 root root 1675 Apr  6 14:14 id_rsa
-rw-r--r--. 1 root root  398 Apr  6 14:14 id_rsa.pub
-rw-r--r--. 1 root root  539 Apr  6 14:19 known_hosts

2）将生成的公钥私钥对id_rsa.pub发送到其他的服务器上

[root@tango-rac01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub tango-rac01
[root@tango-rac01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub tango-rac02

3）ssh登录服务器进行验证

[root@tango-rac01 ~]# ssh tango-rac01
Last login: Tue Apr  6 14:26:26 2021
[root@tango-rac01 ~]# ssh tango-rac02
Last login: Tue Apr  6 14:16:09 2021 from 192.168.112.135

2.5 安装验证

以root用户执行脚本，检查操作系统参数：

[root@tango-rac01 script]# ./gs_checkos -i A -h tango-rac01,tango-rac02 --detail
  791  history|grep gs_checkosChecking items:
    A1. [ OS version status ]                                   : Normal     
        [tango-rac01]
        centos_7.4.1708_64bit
        [tango-rac02]
        centos_7.4.1708_64bit

    A2. [ Kernel version status ]                               : Normal     
        The names about all kernel versions are same. The value is "3.10.0-693.el7.x86_64".
    A3. [ Unicode status ]                                      : Normal     
        The values of all unicode are same. The value is "LANG=en_US.UTF.8".
    A4. [ Time zone status ]                                    : Normal     
        The informations about all timezones are same. The value is "+0800".
    A5. [ Swap memory status ]                                  : Warning    
        [tango-rac02]
SwapMemory 2147479552 TotalMemory 1911832576

        [tango-rac01]
SwapMemory 2147479552 TotalMemory 1911832576


    A6. [ System control parameters status ]                    : Warning    
        [tango-rac02]
        Warning reason: variable 'net.ipv4.tcp_retries1' RealValue '3' ExpectedValue '5'.
        Warning reason: variable 'net.ipv4.tcp_syn_retries' RealValue '6' ExpectedValue '5'.
        Warning reason: variable 'net.sctp.path_max_retrans' RealValue '5' ExpectedValue '10'.
        Warning reason: variable 'net.sctp.max_init_retransmits' RealValue '8' ExpectedValue '10'.
        Check_SysCtl_Parameter warning.

        [tango-rac01]
        Warning reason: variable 'net.ipv4.tcp_retries1' RealValue '3' ExpectedValue '5'.
        Warning reason: variable 'net.ipv4.tcp_syn_retries' RealValue '6' ExpectedValue '5'.
        Warning reason: variable 'net.sctp.path_max_retrans' RealValue '5' ExpectedValue '10'.
        Warning reason: variable 'net.sctp.max_init_retransmits' RealValue '8' ExpectedValue '10'.
        Check_SysCtl_Parameter warning.


    A7. [ File system configuration status ]                    : Normal     
        Both soft nofile and hard nofile are correct.      
    A8. [ Disk configuration status ]                           : Normal     
        The value about XFS mount parameters is correct.   
    A9. [ Pre-read block size status ]                          : Normal     
        The value about Logical block size is correct.     
    A10.[ IO scheduler status ]                                 : Normal     
        The value of IO scheduler is correct.              
    A11.[ Network card configuration status ]                   : Warning    
        [tango-rac01]
BondMode Null
        Warning reason: network 'ens33' 'mtu' RealValue '1500' ExpectedValue '8192'

        [tango-rac02]
BondMode Null
        Warning reason: network 'ens33' 'mtu' RealValue '1500' ExpectedValue '8192'


    A12.[ Time consistency status ]                             : Warning    
        [tango-rac01]
        The NTPD not detected on machine and local time is "2021-04-06 17:03:14".
        [tango-rac02]
        The NTPD not detected on machine and local time is "2021-04-06 17:03:14".

    A13.[ Firewall service status ]                             : Warning    
        [tango-rac01]
The firewall service status RealVaue 'enabled' ExpectedValue 'disabled'

        [tango-rac02]
The firewall service status RealVaue 'enabled' ExpectedValue 'disabled'


    A14.[ THP service status ]                                  : Normal     
        The THP service is stopped.                        
Total numbers:14. Abnormal numbers:0. Warning numbers:5.

2.6 执行安装

1）登录到openGauss的主机，并切换到omm用户

su - omm

注：安装脚本gs_install必须以前置脚本中指定的omm执行，否则，脚本执行会报错

2）使用gs_install安装openGauss

[omm@tango-rac01 script]$ ./gs_install  -X /usr/local/openGauss/cluster_config.xml --gsinit-parameter="--encoding=UTF8"
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 /usr/local/openGauss/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.
Successful check consistence of memCheck and coresCheck on all nodes.
Configuring pg_hba on all nodes.
Configuration is completed.

5）安装生成的目录

2.7 初始化数据库

1）执行如下命令检查数据库状态是否正常，“cluster_state ”显示“Normal”表示数据库可正常使用

[omm@tango-rac01~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Normal
redistributing  : No
current_az      : AZ_ALL
[  Datanode State   ]

node           node_ip         instance                             state            
---------------------------------------------------------------------------------------------------------------------------------
1	tango-rac01 192.168.112.135 6001 /usr/local/openGauss/data/dn P Primary Normal | 
2	tango-rac02 192.168.112.136 6002 /usr/local/openGauss/data/dn S Standby Normal

postgres=# show replconninfo1;
                                                                                  replconninfo1                                                                            
---------------------------------------------------------------------------------------------------------------------------------
localhost=192.168.112.135 localport=15401 localheartbeatport=15405 localservice=15404 remotehost=192.168.112.136 remoteport=15401 remoteheartbeatport=15405 remoteservice=
15404
(1 row)

postgres=# \pset expanded
Expanded display is on.
postgres=# select * from pg_stat_get_wal_senders();
-[ RECORD 1 ]--------------+----------------------------------------------
pid                        | 140337787827968
sender_pid                 | 2089
local_role                 | Primary
peer_role                  | Standby
peer_state                 | Normal
state                      | Streaming
catchup_start              | 2021-04-06 20:24:29.998587+08
catchup_end                | 2021-04-06 20:24:29.998793+08
sender_sent_location       | 0/4001840
sender_write_location      | 0/4001840
sender_flush_location      | 0/4001840
sender_replay_location     | 0/4001840
receiver_received_location | 0/4001840
receiver_write_location    | 0/4001840
receiver_flush_location    | 0/4001840
receiver_replay_location   | 0/4001840
sync_percent               | 100%
sync_state                 | Async
sync_priority              | 0
sync_most_available        | Off
channel                    | 192.168.112.135:15401-->192.168.112.136:48956

2）使用SQL语句创建数据库时，指定数据库的字符集为GBK

[omm@tango-rac01 ~]$ gsql -d postgres -p 15400
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# select version(); 
                                                                                version                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 (openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

postgres=# CREATE DATABASE mydb WITH ENCODING 'GBK' template = template0;
CREATE DATABASE

3、环境清理

1）以omm用户卸载数据库

[omm@tango-rac01 script]$ gs_uninstall --delete-data
Checking uninstallation.
Successfully checked uninstallation.
Stopping the cluster.
Successfully stopped cluster.
Successfully deleted instances.
Uninstalling application.
Successfully uninstalled application.
Uninstallation succeeded.

2）以root用户清理环境

[root@tango-rac01 script]# cd /usr/local/openGauss/script
[root@tango-rac01 script]# ./gs_postuninstall -U omm -X /soft/openGauss/cluster_config.xml --delete-user --delete-group

3）手动清理残留信息

## 手动删除残留的安装目录
rm -rf /usr/local/openGauss/

至此完成openGauss主备环境的部署，过程比较曲折，着实不易。

---

参考资料：

1. https://opengauss.org/zh/docs/2.0.0/docs/installation/企业版安装.html
2. https://blog.opengauss.org/zh/post/jiajunfeng/浅聊opengauss逻辑架构/
3. https://blog.opengauss.org/zh/post/jiajunfeng/opengauss1-1-0主备部署/
4. https://www.cnblogs.com/lemon-feng/p/11208435.html

---

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119747669
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！