greenplum集群安装

greenplum集群安装手册

系统类型

centos 7.x

机器数量

本文档使用4台机器做示例，一台master，三台worker

数据存储的分区类型和文件系统类型

数据存储目录单独挂载一个分区，并且使用linux lvm分区，方便后期扩容，格式化为xfs文件系统类型

关闭selinux和防火墙(全)

setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
systemctl stop firewalld
systemctl disable firewalld

编辑/etc/hostname给每个节点设置hostname，并执行下面命令实时生效(全)

hostname gp-master

hostname gp-sdw1

hostname gp-sdw2

hostname gp-sdw3

hostnamectl set-hostname gp-master 

编辑/etc/hosts使集群可以通过hostname互相访问，修改ip为你的实际ip(全)

192.168.10.193 gp-master
192.168.10.190 gp-sdw1
192.168.10.191 gp-sdw2
192.168.10.192 gp-sdw3

编辑/etc/sysctl.conf调整系统内核参数，编辑完执行sysctl -p使生效(全)

# echo $(expr $(getconf _PHYS_PAGES) / 2)
# echo $(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))
# kernel.shmall = _PHYS_PAGES / 2
kernel.shmall = 4097502
# kernel.shmmax = kernel.shmall * PAGE_SIZE
kernel.shmmax = 16783368192
kernel.shmmni = 4096
vm.overcommit_memory = 2
vm.overcommit_ratio = 95

net.ipv4.ip_local_port_range = 10000 65535
kernel.sem = 500 2048000 200 40960
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.swappiness = 10
vm.zone_reclaim_mode = 0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
# 内存大于64G，上面两项调0，开启下面两项
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
# vm.dirty_background_bytes = 1610612736
# vm.dirty_bytes = 4294967296

编辑/etc/security/limits.conf调整系统资源限制,编辑完重新登录系统后执行ulimit -n和ulimit -u确认生效(全)

* soft nofile 524288
* hard nofile 524288
* soft nproc 131072
* hard nproc 131072

数据存放的文件系统首选xfs，编辑/etc/fstab以挂载数据盘(全)

#先确定有无安装 XFS系统工具集
sudo yum install xfsprogs -y
#创建 XFS格式分区
#先准备一个分区来创建XFS。假设你的分区在/dev/sdb
sudo fdisk /dev/sdb
#依次输入
n,p,1,数字区间,数字区间,w
#格式化
sudo mkfs.xfs -f /dev/sdb1
创建挂载点 /data后挂载 mkdir /data
sudo mount -t xfs /dev/sdb1  /data

vim /etc/fstab
# 设备名    挂载位置    推荐参数，ubuntu没有nobarrier
#/dev/data   /data     nodev,noatime,nobarrier,inode64 0 0
#个人用
/dev/sdb1   /data  xfs  rw,nodev,noatime,nobarrier,inode64 0 0

设置磁盘IO预读值,编辑完/etc/rc.d/rc.local并执行chmod +x /etc/rc.d/rc.local以持久生效(全)

blockdev --setra 16384 /dev/sdb1
#或
echo 'blockdev --setra 16384 /dev/sdb1' >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

设置磁盘IO调度程序，关闭透明大页面，重启以生效，grubby --info=ALL可查看设置(全)

grubby --update-kernel=ALL --args="elevator=deadline"
grubby --update-kernel=ALL --args="transparent_hugepage=never"

编辑/etc/systemd/logind.conf，编辑完执行service systemd-logind restart以生效(全)

RemoveIPC=no
或
echo 'RemoveIPC=no' >> /etc/systemd/logind.conf
service systemd-logind restart

编辑/etc/ssh/sshd_config，编辑完执行service sshd restart以生效(全)

MaxStartups 10:30:200
MaxSessions 200

同步系统时钟，可使用NTP(全)

#所有服务器都要安装ntp
yum install ntp -y
#主
		vim /etc/ntp.conf
	#注释原有配置
		#server 0.rhel.pool.ntp.org iburst
		#server 1.rhel.pool.ntp.org iburst
		#server 2.rhel.pool.ntp.org iburst
		#server 3.rhel.pool.ntp.org iburst
	#添加新配置，让本机的ntpd和本地硬件时间同步：
		server 127.127.1.0
		fudge 127.127.1.0 stratum 10
	#启动ntp服务：
		systemctl start ntpd
	#开机自启
		chkconfig ntpd on
#其他服务器
	#关闭ntp服务：
		systemctl stop ntpd
		systemctl disable ntpd
	#手动同步时钟(ntp 主ip )
		ntpdate 192.168.69.20
	#定时同步，编辑 /etc/crontab 文件，追加内容：
		* */2 * * * root  /usr/sbin/ntpdate 192.168.0.100
	#然后重启crond服务：
		systemctl restart crond

创建gpadmin用户(全)

groupadd gpadmin
useradd gpadmin -r -m -g gpadmin
passwd gpadmin

编辑/etc/sudoers,授予sudo权限(全)

gpadmin    ALL=(ALL)    NOPASSWD: ALL

切换用户，并创建用户密钥(主)

su - gpadmin
ssh-keygen -t rsa -b 4096

ssh免密配置，该步骤需要每个节点都创建好gpadmin用户(主)

ssh-copy-id gp-master
ssh-copy-id gp-sdw1
ssh-copy-id gp-sdw2
ssh-copy-id gp-sdw3

下载gp安装包并安装（源码安装和rpm包安装2选1）(全)

• rpm包安装

cd /home/gpadmin
wget https://github.com/greenplum-db/gpdb/releases/download/6.7.1/greenplum-db-6.7.1-rhel7-x86_64.rpm
sudo yum install greenplum-db-6.7.1-rhel7-x86_64.rpm

• 源码安装

从https://github.com/greenplum-db/gpdb/releases/download/6.7.1/6.7.1-src-full.tar.gz下载gp源码并解压

进入解压后的目录，然后编辑src\backend\gporca\libgpos\include\gpos\utils.h

### 删掉以下四行内容
#define GPOS_ASMFP asm volatile ("movq %%rbp, %0" : "=g" (ulp));
#define GPOS_ASMSP asm volatile ("movq %%rsp, %0" : "=g" (ulp));
#define GPOS_GET_FRAME_POINTER(x) do { ULONG_PTR ulp; GPOS_ASMFP; x = ulp; } while (0)
#define GPOS_GET_STACK_POINTER(x) do { ULONG_PTR ulp; GPOS_ASMSP; x = ulp; } while (0)

### 增加以下内容
#define GPOS_GET_FRAME_POINTER(x) ((x) = (ULONG_PTR) __builtin_frame_address(0))

编辑configure文件，重新设置PACKAGE_VERSION和PACKAGE_STRING的值

PACKAGE_VERSION='6.7.1'
PACKAGE_STRING='Greenplum Database 6.7.1'

编辑/etc/ld.so.conf文件，添加以下内容

/usr/local/lib
/usr/local/lib64

然后执行以下命令编辑安装

./README.CentOS.bash

./configure --with-perl --with-python --with-libxml --with-gssapi --prefix=/usr/local/greenplum-db

make -j8
make -j8 install

编辑/home/gpadmin/.bashrc添加以下命令，编辑完成后命令行再执行一下(主)

$vim /home/gpadmin/.bashrc
source /usr/local/greenplum-db/greenplum_path.sh
export MASTER_DATA_DIRECTORY=/home/gpadmin/data/master/gpseg-1
$source /home/gpadmin/.bashrc

编辑/home/gpadmin/hostfile_exkeys(主)

gp-master
gp-sdw1
gp-sdw2
gp-sdw3

执行以下命令检测网络配置及gp安装(主)

gpssh-exkeys -f hostfile_exkeys
gpssh -f hostfile_exkeys -e 'll'

编辑/home/gpadmin/hostfile_segment(主)

gp-sdw1
gp-sdw2
gp-sdw3

编辑/home/gpadmin/init_config(主)

# FILE NAME: gpinitsystem_config

# Configuration file needed by the gpinitsystem

################################################
#### REQUIRED PARAMETERS
################################################

#### Name of this Greenplum system enclosed in quotes.
ARRAY_NAME="Greenplum Data Platform"

#### Naming convention for utility-generated data directories.
SEG_PREFIX=gpseg

#### Base number by which primary segment port numbers
#### are calculated.
PORT_BASE=6000

#### File system location(s) where primary segment data directories
#### will be created. The number of locations in the list dictate
#### the number of primary segments that will get created per
#### physical host (if multiple addresses for a host are listed in
#### the hostfile, the number of segments will be spread evenly across
#### the specified interface addresses).
declare -a DATA_DIRECTORY=(/home/gpadmin/data/primary /home/gpadmin/data/primary /home/gpadmin/data/primary /home/gpadmin/data/primary)

#### OS-configured hostname or IP address of the master host.
MASTER_HOSTNAME=gp-master

#### File system location where the master data directory
#### will be created.
MASTER_DIRECTORY=/home/gpadmin/data/master

#### Port number for the master instance.
MASTER_PORT=5432

#### Shell utility used to connect to remote hosts.
TRUSTED_SHELL=ssh

#### Maximum log file segments between automatic WAL checkpoints.
CHECK_POINT_SEGMENTS=8

#### Default server-side character set encoding.
ENCODING=UNICODE

################################################
#### OPTIONAL MIRROR PARAMETERS
################################################

#### Base number by which mirror segment port numbers
#### are calculated.
#MIRROR_PORT_BASE=7000

#### File system location(s) where mirror segment data directories
#### will be created. The number of mirror locations must equal the
#### number of primary locations as specified in the
#### DATA_DIRECTORY parameter.
#declare -a MIRROR_DATA_DIRECTORY=(/data1/mirror /data1/mirror /data1/mirror /data2/mirror /data2/mirror /data2/mirror)


################################################
#### OTHER OPTIONAL PARAMETERS
################################################

#### Create a database of this name after initialization.
#DATABASE_NAME=name_of_database

#### Specify the location of the host address file here instead of
#### with the -h option of gpinitsystem.
#MACHINE_LIST_FILE=/home/gpadmin/gpconfigs/hostfile_gpinitsystem

创建上述步骤声明的文件夹(全)

MASTER_DIRECTORY和DATA_DIRECTORY

初始化(主)

gpinitsystem -c init_config -h hostfile_segment
#初始化后就启动了可用 gpstate -s 查看状态

修改远程登录配置文件(主)

cd /home/gpadmin/data/master/gpseg-1
cp -r pg_hba.conf pg_hba.conf_bak
vim pg_hba.conf
#添加一行 ident，md5，password（明文密码），trust（无需密码），reject（拒绝认证）
host     all         all             0.0.0.0/0          md5
#重新加载配置文件
gpstop -u
#或（推荐下面的）
pg_ctl -D /home/gpadmin/data/master/gpseg-1 reload

建库建角色赋权(主)

#执行命令连接默认的数据库
psql
#建库
create database test;
#改密码
#alter user xxx with password 'xxx';
alter user gpadmin with password 'gpadmin';
#创建用户
 create role dbdream password 'dbdream' createdb login;
 #赋权
 alter role XXX with SUPERUSER; 
#退出sql
\q

其他

master 端口 5432
segment 端口 6000
#启动
gpstart -a
#停止
gpstop -a
#查看状态
gpstate -s
#回滚关闭数据库
gpstop -m fast
# 查看角色属性
\du
# 查看数据库列表
\l (小写的L)
# 查看包含template字符的数据库
\l *template*
# 切换到test数据库
\c test
# 查看当前schema中所有的表
\d
# 查看表的结构
\d [schema.]table
# 查询结果横纵显示切换
\x
# 查询当前主机名
\! hostname
# 执行SQL消耗时间开关
\timing
# 查看命令历史记录
\s
# 显示字符集
\encoding
# 命令行操作的帮助
\?
# 查看所有的sql关键字
\h