Greenplum 并发 greenplum集群

目录

一、安装说明

1.1 软件版本

1.2 集群介绍

二、master节点GreenPlum安装

2.1 安装环境准备

2.1.1修改 /etc/hosts文件

2.1.2 修改 etc/sysconfig/network文件

2.1.3 修改 /etc/sysctl.conf文件

2.1.4 修改 /etc/security/limits.d/90-nproc.conf文件

2.1.5 修改 /etc/security/limits.conf文件

2.2 安装依赖和Greenplum

2.2.1 安装Greenplum依赖

2.2.2 安装Greenplum

三、segment节点GreenPlum安装

3.1 segment节点安装环境准备

3.2 segment节点安装Greenplum依赖

3.3 segment节点安装Greenplum

四、配置集群免密连接

4.1 创建gpadmin用户

4.1.1 所有节点创建gpadmin用户

4.1.2 所有节点修改gpadmin用户操作权限

4.2 ssh免密配置（gpadmin用户）

4.2.1 所有节点创建.ssh文件

4.2.2 所有节点生成公钥和私钥（gpadmin用户）

4.2.3 所有节点创建认证文件

4.2.4 所有节点重启ssh服务（root用户）

4.3 打通集群节点

4.3.1 master节点创建hostlist和seg_hosts文件

4.3.2 配置节点免密连接

五、初始化GreenPlum集群

5.1 环境配置

5.1.1 添加gp编译的python包

5.1.2 创建资源目录

5.1.3 master节点配置环境变量

5.1.4 NTP配置

5.2 初始化数据库

5.2.1 检查节点间连通性

5.2.2 创建Greenplum初始化配置文件

5.3 初始化和登录

5.3.1 执行初始化

5.3.2 数据库登录

六、问题记录

---

一、安装说明

 GreenPlum数据库文档：https://gp-docs-cn.github.io/docs/common/gpdb-features.html

1.1 软件版本

名称	版本
操作系统	CentOS Linux release 8.0.1905 (Core)
GreenPlum	6.12.0

1.2 集群介绍

  使用1个master，n个segment的集群示例：

服务器IP地址	节点名称
198.163.0.110	gp-master
198.163.0.111	gp-sdw1
198.163.0.112	gp-sdw2
198.163.0.113	gp-sdw3
198.163.0.114	gp-sdw4

  其中198.163.0.110为master，其余为segment。

二、master节点GreenPlum安装

2.1 安装环境准备

2.1.1修改 /etc/hosts文件

  在/etc/hosts文件添加所有节点地址和名称

[root@gp-master ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.163.0.110      gp-master
192.163.0.111      gp-sdw1　 
192.163.0.112      gp-sdw2　
192.163.0.113      gp-sdw3
192.163.0.114      gp-sdw4

2.1.2 修改 etc/sysconfig/network文件

  HOSTNAME是master节点名称

[root@gp-master ~]# vi /etc/sysconfig/network
NETWORKING=yes
HOSTNAME= gp-master

2.1.3 修改 /etc/sysctl.conf文件

[root@gp-master ~]# vi /etc/sysctl.conf 
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.defalut.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.overcommit_memory = 2

[root@gp-master ~]# sysctl -p #使配置生效

2.1.4 修改 /etc/security/limits.d/90-nproc.conf文件

[root@gp-master ~]# vi /etc/security/limits.d/90-nproc.conf
*          soft    nproc     131072
root       soft    nproc     unlimited

2.1.5 修改 /etc/security/limits.conf文件

[root@gp-master ~]# vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

2.2 安装依赖和Greenplum

2.2.1 安装Greenplum依赖

  先安装扩展源epel-release

[root@gp-master ~]# yum -y install epel-release

  批量安装依赖包 ，建议先安装gcc，再批量安装其他依赖包

[root@gp-master ~]# yum -y install apr-devel \
bison \
bzip2-devel \
cmake3 \
flex \
gcc \
gcc-c++ \
krb5-devel \
libcurl-devel \
libevent-devel \
libkadm5 \
libxml2-devel \
libzstd-devel \
openssl-devel \
perl-ExtUtils-Embed \
python3-devel \
python3-pip \
readline-devel \
xerces-c-devel \
zlib-devel \
apr-util \
perl \
curl \
libnsl

2.2.2 安装Greenplum

[root@gp-master ~]# mkdir -p /gpdb #创建GreenPlum文件夹
[root@gp-master ~]# yum -y install wget #安装wget
[root@gp-master ~]# wget -p https://github.com/greenplum-db/gpdb/releases/download/6.12.0/greenplum-db-6.12.0-ubuntu18.04-amd64.deb /gpdb #下载rpm安装包到/gpdb路径
[root@gp-master ~]# rpm -ivh --prefix=/gpdb /gpdb/greenplum-db-6.12.0-rhel7-x86_64.rpm #安装GreenPlum到/gpdb路径

三、segment节点GreenPlum安装

3.1 segment节点安装环境准备

  在master节点复制配置文件和安装包到segment节点

[root@gp-master ~]# for a in {1..4}; do scp /etc/hosts root@gp-sdw$a:/etc/hosts ; done
[root@gp-master ~]# for a in {1..4}; do scp etc/sysconfig/network root@gp-sdw$a:etc/sysconfig/network ; done
[root@gp-master ~]# for a in {1..4}; do scp /etc/sysctl.conf root@gp-sdw$a:/etc/sysctl.conf ; done
[root@gp-master ~]# for a in {1..4}; do scp /etc/security/limits.d/90-nproc.conf root@gp-sdw$a:/etc/security/limits.d/90-nproc.conf ; done
[root@gp-master ~]# for a in {1..4}; do scp /etc/security/limits.conf root@gp-sdw$a:/etc/security/limits.conf ; done
[root@gp-master ~]# for a in {1..4}; do scp /gpdb/greenplum-db-6.12.0-rhel7-x86_64.rpm root@gp-sdw$a:/gpdb ; done
[root@gp-master ~]# sysctl -p #使配置生效

3.2 segment节点安装Greenplum依赖

  与2.21小节相同

3.3 segment节点安装Greenplum

mkdir /gpdb #创建GreenPlum文件夹
rpm -ivh --prefix=/gpdb /gpdb/greenplum-db-6.12.0-rhel7-x86_64.rpm #安装GreenPlum到/gpdb路径

四、配置集群免密连接

  操作前请确保openssh版本一致，不同版本算法有差异，可能导致节点间不能完全互信

4.1 创建gpadmin用户

4.1.1 所有节点创建gpadmin用户

groupadd -g 530 gpadmin
useradd -g 530 -u 530 -m -d /home/gpadmin -s /bin/bash gpadmin
chown -R gpadmin:gpadmin /home/gpadmin
echo "gpadmin" | passwd --stdin gpadmin

4.1.2 所有节点修改gpadmin用户操作权限

chown -R gpadmin:gpadmin /gpdb

4.2 ssh免密配置（gpadmin用户）

4.2.1 所有节点创建.ssh文件

su - gpadmin #切换到gpadmin用户
ssh localhost #生成.ssh文件

4.2.2 所有节点生成公钥和私钥（gpadmin用户）

ssh-keygen -t rsa -P '' -f ~/.ssh/id_rsa

4.2.3 所有节点创建认证文件

  在master节点生成authorized_keys文件，将authorized_keys文件复制到segment节点

[root@gp-master ~]# su - gpadmin
[gpadmin@gp-master ~]# cd /home/gpadmin/.ssh
[gpadmin@gp-master .ssh]# ssh-copy-id gp-master #生成authorized_keys文件
[gpadmin@gp-master .ssh]# for a in {1..4}; do ssh gpadmin@gp-sdw$a cat /home/gpadmin/.ssh/id_rsa.pub >> /home/gpadmin/.ssh/authorized_keys; done #将segment节点公钥追加到master节点authorized_keys文件
[gpadmin@gp-master .ssh]# for a in {1..4}; do scp /home/gpadmin/.ssh/authorized_keys gpadmin@gp-sdw$a:/home/gpadmin/.ssh/authorized_keys ; done #将uthorized_keys文件复制到egment节点

4.2.4 所有节点重启ssh服务（root用户）

sudo systemctl restart sshd.service

4.3 打通集群节点

4.3.1 master节点创建hostlist和seg_hosts文件

  hostlist文件包含所有节点名称

[root@gp-master ~]# su - gpadmin
[gpadmin@gp-master ~]# mkdir -p /home/gpadmin/conf
[gpadmin@gp-master ~]# vi /home/gpadmin/conf/hostlist
gp-master
gp-sdw1
gp-sdw2
gp-sdw3
gp-sdw4

  seg_hosts文件包含所有segment节点名称

[gpadmin@gp-master ~]# vi /home/gpadmin/conf/seg_hosts
gp-sdw1
gp-sdw2
gp-sdw3
gp-sdw4

4.3.2 配置节点免密连接

[root@gp-master ~]# su - gpadmin
[gpadmin@gp-master ~]$ source /gpdb/greenplum-db/greenplum_path.sh
[gpadmin@gp-master ~]$ gpssh-exkeys -f /home/gpadmin/conf/hostlist
[STEP 1 of 5] create local ID and authorize on local host
  ... /home/gpadmin/.ssh/id_rsa file exists ... key generation skipped

[STEP 2 of 5] keyscan all hosts and update known_hosts file

[STEP 3 of 5] retrieving credentials from remote hosts
  ... send to gp-sdw1
  ... send to gp-sdw2
  ... send to gp-sdw3
  ... send to gp-sdw4

[STEP 4 of 5] determine common authentication file content

[STEP 5 of 5] copy authentication files to all remote hosts
  ... finished key exchange with gp-sdw1
  ... finished key exchange with gp-sdw2
  ... finished key exchange with gp-sdw3
  ... finished key exchange with gp-sdw4

[INFO] completed successfully

  测试免密连接

[gpadmin@gp-master ~]$ gpssh -f /home/gpadmin/conf/hostlist
=> pwd
[  gp-sdw4] /home/gpadmin
[  gp-sdw1] /home/gpadmin
[  gp-sdw2] /home/gpadmin
[gp-master] /home/gpadmin
[  gp-sdw3] /home/gpadmin
=> exit

五、初始化GreenPlum集群

5.1 环境配置

5.1.1 添加gp编译的python包

[root@gp-master ~]# echo "/gpdb/greenplum-db/lib/python" > /usr/lib64/python3.6/site-packages/greenplum.pth
[root@gp-master ~]# vi /etc/ld.so.conf
/gpdb/greenplum-db/lib
[root@gp-master ~]# ldconfig

  注意自己的python版本和系统对应的lib路径

5.1.2 创建资源目录

[root@gp-master ~]# su - gpadmin
[gpadmin@gp-master ~]$ mkdir -p /gpdb/opt/data/master #在master节点创建资源目录
 #在segment节点创建资源目录
[gpadmin@gp-master ~]$ source /gpdb/greenplum-db/greenplum_path.sh
[gpadmin@gp-master ~]$ gpssh -f /home/gpadmin/conf/seg_hosts
=> mkdir -p /gpdb/opt/data1/primary
[gp-sdw1]
[gp-sdw4]
[gp-sdw2]
[gp-sdw3]
=> mkdir -p /gpdb/opt/data2/primary
[gp-sdw1]
[gp-sdw4]
[gp-sdw2]
[gp-sdw3]
=> mkdir -p /gpdb/opt/data1/mirror
[gp-sdw1]
[gp-sdw4]
[gp-sdw2]
[gp-sdw3]
=> mkdir -p /gpdb/opt/data2/mirror
[gp-sdw1]
[gp-sdw4]
[gp-sdw2]
[gp-sdw3]
=> exit
 #需要几个创建几个，我创建了两个

5.1.3 master节点配置环境变量

  在master节点/home/gpadmin/.bash_profile添加内容

[gpadmin@gp-master ~]$ vi /home/gpadmin/.bash_profile

# .bash_profile
  
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

#以下为需添加的内容

source /gpdb/greenplum-db/greenplum_path.sh
export MASTER_DATA_DIRECTORY=/gpdb/opt/data/master/gpseg-1
export GPPORT=5432
export PGDATABASE=gp_sydb

  将.bash_profile复制到segment节点

[gpadmin@gp-master ~]$ for a in {1..4}; do scp /home/gpadmin/.bash_profile gpadmin@gp-sdw$a:/home/gpadmin/.bash_profile ; done
.bash_profile                                                            100%  290   313.7KB/s   00:00    
.bash_profile                                                            100%  290   354.1KB/s   00:00    
.bash_profile                                                            100%  290   161.9KB/s   00:00    
.bash_profile                                                            100%  290   
285.2KB/s   00:00    
=> exit

  让变量生效

=> source .bash_profile
[  gp-sdw1]
[  gp-sdw4]
[  gp-sdw2]
[gp-master]
[  gp-sdw3]
=> exit

5.1.4 NTP配置

[root@gp-master ~]# echo "server gp-master perfer" >>/etc/ntp.conf
[root@gp-master ~]# su - gpadmin
[gpadmin@gp-master ~]$ gpssh -f /home/gpadmin/conf/hostlist -v -e 'sudo ntpd'
[gpadmin@gp-master ~]$ gpssh -f /home/gpadmin/conf/hostlist -v -e 'sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on'
 #出现以下结果表示配置成功
Using delaybeforesend 0.05 and prompt_validation_timeout 1.0

[Reset ...]
[INFO] login gp-sdw1
[INFO] login gp-sdw4
[INFO] login gp-sdw2
[INFO] login gp-master
[INFO] login gp-sdw3
[  gp-sdw1] sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on
[  gp-sdw1] [sudo] password for gpadmin: 
[  gp-sdw4] sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on
[  gp-sdw4] 
[  gp-sdw4] We trust you have received the usual lecture from the local System
[  gp-sdw4] Administrator. It usually boils down to these three things:
[  gp-sdw4] 
[  gp-sdw4]     #1) Respect the privacy of others.
[  gp-sdw4]     #2) Think before you type.
[  gp-sdw4]     #3) With great power comes great responsibility.
[  gp-sdw4] 
[  gp-sdw4] [sudo] password for gpadmin: 
[  gp-sdw2] sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on
[  gp-sdw2] 
[  gp-sdw2] We trust you have received the usual lecture from the local System
[  gp-sdw2] Administrator. It usually boils down to these three things:
[  gp-sdw2] 
[  gp-sdw2]     #1) Respect the privacy of others.
[  gp-sdw2]     #2) Think before you type.
[  gp-sdw2]     #3) With great power comes great responsibility.
[  gp-sdw2] 
[  gp-sdw2] [sudo] password for gpadmin: 
[gp-master] sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on
[gp-master] sudo: /etc/init.d/ntpd: command not found
[  gp-sdw3] sudo /etc/init.d/ntpd start && sudo chkconfig --level 35 ntpd on
[  gp-sdw3] 
[  gp-sdw3] We trust you have received the usual lecture from the local System
[  gp-sdw3] Administrator. It usually boils down to these three things:
[  gp-sdw3] 
[  gp-sdw3]     #1) Respect the privacy of others.
[  gp-sdw3]     #2) Think before you type.
[  gp-sdw3]     #3) With great power comes great responsibility.
[  gp-sdw3] 
[  gp-sdw3] [sudo] password for gpadmin: 
[INFO] completed successfully

  NTP是用来使计算机时间同步化的一种协议，不配置不影响后续安装

5.2 初始化数据库

5.2.1 检查节点间连通性

[gpadmin@gp-master ~]$ cd /gpdb/greenplum-db/bin
[gpadmin@gp-master bin]$ gpcheckperf -f /home/gpadmin/conf/hostlist -r N -d /tmp

-------------------
--  NETPERF TEST
-------------------
====================
==  RESULT
====================
Netperf bisection bandwidth test
gp-master -> gp-sdw1 = 11.040000
gp-sdw2 -> gp-sdw3 = 10.950000
gp-sdw4 -> gp-master = 111.880000
gp-sdw1 -> gp-master = 10.410000
gp-sdw3 -> gp-sdw2 = 11.080000
gp-master -> gp-sdw4 = 111.920000

Summary:
sum = 267.28 MB/sec
min = 10.41 MB/sec
max = 111.92 MB/sec
avg = 44.55 MB/sec
median = 11.08 MB/sec

5.2.2 创建Greenplum初始化配置文件

[gpadmin@gp-master ~]$ cd /gpdb/greenplum-db/docs/cli_help/gpconfigs
[gpadmin@gp-master gpconfigs]$ cp gpinitsystem_config initgp_config #复制配置文件模板
[gpadmin@gp-master gpconfigs]$ vi initgp_config #修改配置文件

 #资源目录，与5.1.2章节保持一致
declare -a DATA_DIRECTORY=(/gpdb/opt/data1/primary /gpdb/opt/data2/primary)
declare -a MIRROR_DATA_DIRECTORY=(/gpdb/opt/data1/mirror /gpdb/opt/data2/mirror) #取消注释

ARRAY_NAME=”gp_sydb”                                   #5.1.3章节配置的初始化数据库名称
MASTER_HOSTNAME=gp-master                              #主节点名称
MASTER_DIRECTORY=/gpdb/opt/data/master                 #在5.1.2章节创建的资源目录
MASTER_DATA_DIRECTORY=/gpdb/opt/data/master/gpseg-1    #与5.1.2章节配置一样
MIRROR_PORT_BASE=7000                                  #取消注释    
DATABASE_NAME=gp_sydb                                  #取消注释；5.1.3章节配置的初始化数据库名称
MACHINE_LIST_FILE=/home/gpadmin/conf/seg_hosts         #取消注释；4.3.1章节创建的segment节点名称文件

  所有节点更新gpadmin用户操作权限

chown -R gpadmin:gpadmin /gpdb

5.3 初始化和登录

5.3.1 执行初始化

集群初始化

[gpadmin@gp-master ~]$ cd /gpdb/greenplum-db/docs/cli_help/gpconfigs
[gpadmin@gp-master gpconfigs]$ gpinitsystem -c initgp_config

查看集群状态

gpstate -e #查看mirror的状态
gpstate -f #查看standby master的状态
gpstate -s #查看整个GP群集的状态
gpstate -i #查看GP的版本
gpstate --help #帮助文档，可以查看gpstate更多用法

5.3.2 数据库登录

  完成初始化后登录数据库

[gpadmin@gp-master ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \l #查询数据库
                               List of databases
   Name    |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges  
-----------+---------+----------+------------+------------+---------------------
 gp_sydb   | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
(4 rows)

postgres=#

设置远程访问密码

[gpadmin@gp-master ~]$ psql postgres gpadmin
psql (9.4.24)
Type "help" for help.

postgres=# alter user gpadmin encrypted password 'password';

配置数据库白名单

[gpadmin@gp-master ~]$ vi /gpdb/opt/data/master/gpseg-1/pg_hba.conf #在master资源目录pg_hba.conf文件中添加白名单

# TYPE  DATABASE        USER            ADDRESS                 METHOD
#local   replication     gpadmin                                trust
#host    replication     gpadmin        127.0.0.1/32            trust
#host    replication     gpadmin        ::1/128                 trust
local    all         gpadmin         ident
host     all         gpadmin         127.0.0.1/28    trust
...
host     all         gpadmin         192.168.10.xx/32       password #gpadmin用户在192.168.10.xx服务器上使用密码登录

加载pg_hba.conf，使配置生效

gpadmin@gp-master ~]$ gpstop -u

六、问题记录

  执行初始化失败，查看master节点/home/gpadmin/gpAdminLogs初始化日志，发现缺少libnsl.so.1

creating template1 database in /gpdb/opt/data1/primary/gpseg2/base/1 ... /gpdb/greenplum-db-6.12.0/bin/postgres: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory
/gpdb/greenplum-db-6.12.0/bin/postgres: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory
no data was returned by command ""/gpdb/greenplum-db-6.12.0/bin/postgres" -V"

  解决办法：

[root@gp-sdw4 ~]# yum list | grep libnsl #查看libnsl依赖
libnsl.x86_64                                          2.28-127.el8                                      @System   
libnsl2.x86_64                                         1.2.0-2.20180605git4a062cf.el8                    @anaconda 
libnsl.i686                                            2.28-127.el8                                      BaseOS    
libnsl2.i686                                           1.2.0-2.20180605git4a062cf.el8                    BaseOS    

# yum list installed | grep libnsl #查看已安装依赖

# yum -y install libnsl2.x86_64 #安装缺少的依赖包，我是gp-sdw4节点缺少libnsl2