GreenPlum教程FG002-GreenPlum安装与生产集群部署实战

本文档风哥主要介绍GreenPlum数据库安装与生产集群部署，包括安装前环境准备、操作系统参数配置、软件包安装、集群初始化、安装后配置等内容，风哥教程参考GreenPlum官方文档Installation Guides、Best Practices等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 GreenPlum数据库安装概述

GreenPlum数据库安装是构建数据仓库平台的第一步，安装过程涉及操作系统配置、软件部署、集群初始化等多个环节。正确的安装配置是保障GreenPlum数据库稳定运行的基础。更多视频教程www.fgedu.net.cn

GreenPlum安装主要步骤：

环境准备：操作系统配置、网络配置、存储配置
软件安装：安装GreenPlum软件包到所有节点
集群初始化：创建数据库集群、配置Segment节点
安装后配置：参数优化、用户创建、权限配置

1.2 GreenPlum数据库安装方式对比

GreenPlum支持多种安装方式，不同方式适用于不同场景。学习交流加群风哥微信: itpux-com

1.2.1 安装方式对比表

GreenPlum安装方式对比：

1. RPM/DEB包安装
优点：安装简单、依赖自动解决
缺点：路径固定、灵活性较低
适用：快速部署、测试环境

2. 二进制包安装（推荐）
优点：路径灵活、版本管理方便
缺点：需手动配置环境变量
适用：生产环境、多版本共存

3. 源码编译安装
优点：可定制功能、性能优化
缺点：编译时间长、维护复杂
适用：特殊需求、开发测试

4. 容器化部署
优点：快速部署、环境一致
缺点：性能损耗、存储管理复杂
适用：开发测试、云环境

1.3 GreenPlum数据库安装架构规划

在安装GreenPlum之前，需要规划好集群架构，包括节点数量、角色分配、存储规划等。学习交流加群风哥QQ113257174

1.3.1 集群架构规划示例

5节点集群架构规划：

节点角色分配：
– mdw.fgedu.net.cn (192.168.1.10) Master节点
– smdw.fgedu.net.cn (192.168.1.11) Standby Master节点
– sdw1.fgedu.net.cn (192.168.1.21) Segment节点（含Primary和Mirror）
– sdw2.fgedu.net.cn (192.168.1.22) Segment节点（含Primary和Mirror）
– sdw3.fgedu.net.cn (192.168.1.23) Segment节点（含Primary和Mirror）

Segment分布规划：
– 每台Segment主机部署2个Primary Segment
– 每台Segment主机部署2个Mirror Segment
– Mirror与Primary分布在不同主机上
– 总计6个Primary Segment，6个Mirror Segment

存储目录规划：
– /GreenPlum/app 软件安装目录
– /GreenPlum/fgdata Master数据目录
– /GreenPlum/fgdata/primary Segment主数据目录
– /GreenPlum/fgdata/mirror Segment镜像数据目录

Part02-生产环境规划与建议

2.1 GreenPlum数据库安装前环境检查

2.1.1 操作系统版本检查

# 检查操作系统版本
$ cat /etc/os-release
NAME=”Oracle Linux Server”
VERSION=”9.3″
ID=”ol”
ID_LIKE=”fedora”
VERSION_ID=”9.3″
PLATFORM_ID=”platform:el9″
PRETTY_NAME=”Oracle Linux Server 9.3″

# 检查内核版本
$ uname -r
5.15.0-200.131.27.el9uek.x86_64

# 检查系统架构
$ uname -m
x86_64

2.1.2 硬件资源检查

# 检查CPU信息
$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
CPU(s): 32
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 2
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Gold 6248R CPU @ 3.00GHz

# 检查内存信息
$ free -h
total used free shared buff/cache available
Mem: 125Gi 2.1Gi 120Gi 256Mi 2.8Gi 121Gi
Swap: 8Gi 0B 8Gi

# 检查磁盘空间
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 100G 5.2G 95G 6% /
/dev/sdb1 500G 20G 480G 4% /GreenPlum

2.1.3 依赖软件包检查

# 安装缺失的依赖包
$ sudo yum install -y python3 perl readline-devel zlib-devel \
openssl-devel libxml2-devel bzip2-devel curl-devel \
apr-devel apr-util-devel libevent-devel

2.2 GreenPlum数据库操作系统参数配置

2.2.1 内核参数配置

# 编辑/etc/sysctl.conf文件
$ sudo vi /etc/sysctl.conf

# 添加以下内核参数
# 共享内存配置
kernel.shmmax = 500000000000
kernel.shmmni = 4096
kernel.shmall = 4000000000

# 信号量配置
kernel.sem = 250 32000 100 128

# 文件句柄配置
fs.file-max = 7672460

# 网络参数配置
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.netdev_max_backlog = 10000
net.ipv4.tcp_rmem = 4096 87380 2097152
net.ipv4.tcp_wmem = 4096 65536 2097152
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.ip_local_port_range = 1024 65535

# 透明大页关闭
vm.transparent_hugepage = never

# NUMA关闭
kernel.numa_balancing = 0

# 应用内核参数
$ sudo sysctl -p

2.2.2 用户限制配置

# 编辑/etc/security/limits.conf文件
$ sudo vi /etc/security/limits.conf

# 添加以下配置
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock unlimited
* hard memlock unlimited

# 检查当前限制
$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 256000
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 65536
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 131072
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

2.2.3 关闭透明大页和NUMA

# 检查透明大页状态
$ cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

# 关闭透明大页
$ sudo echo never > /sys/kernel/mm/transparent_hugepage/enabled
$ sudo echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 永久关闭透明大页（添加到/etc/rc.local）
$ sudo vi /etc/rc.local
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# 检查NUMA状态
$ numactl –hardware
available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30
node 0 size: 65536 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31
node 1 size: 65536 MB

# 在GRUB中关闭NUMA
$ sudo vi /etc/default/grub
GRUB_CMDLINE_LINUX=”… numa=off”

# 更新GRUB配置
$ sudo grub2-mkconfig -o /boot/grub2/grub.cfg

风哥提示：透明大页和NUMA对GreenPlum性能有负面影响，生产环境必须关闭。更多学习教程公众号风哥教程itpux_com

2.3 GreenPlum数据库用户与目录规划

2.3.1 创建GreenPlum用户

# 创建gpadmin用户
$ sudo groupadd -g 1000 gpadmin
$ sudo useradd -u 1000 -g gpadmin -m -d /home/gpadmin -s /bin/bash gpadmin

# 设置gpadmin用户密码
$ sudo passwd gpadmin
Changing password for user gpadmin.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

# 配置gpadmin用户sudo权限
$ sudo vi /etc/sudoers.d/gpadmin
gpadmin ALL=(ALL) NOPASSWD: ALL

# 配置gpadmin用户环境变量
$ sudo su – gpadmin
$ vi ~/.bash_profile

# 添加以下内容
export GPHOME=/GreenPlum/app
export MASTER_DATA_DIRECTORY=/GreenPlum/fgdata
export PGPORT=5432
export PGUSER=gpadmin
export PGDATABASE=fgedudb
export PATH=$GPHOME/bin:$GPHOME/ext/python3/bin:$PATH
export LD_LIBRARY_PATH=$GPHOME/lib:$LD_LIBRARY_PATH

# 使环境变量生效
$ source ~/.bash_profile

2.3.2 创建安装目录

# 创建软件安装目录
$ sudo mkdir -p /GreenPlum/app
$ sudo mkdir -p /GreenPlum/fgdata
$ sudo mkdir -p /GreenPlum/fgdata/primary
$ sudo mkdir -p /GreenPlum/fgdata/mirror

# 设置目录权限
$ sudo chown -R gpadmin:gpadmin /GreenPlum
$ sudo chmod -R 755 /GreenPlum

# 查看目录结构
$ ls -la /GreenPlum/
total 0
drwxr-xr-x. 2 gpadmin gpadmin 6 Apr 8 10:00 app
drwxr-xr-x. 2 gpadmin gpadmin 6 Apr 8 10:00 fgdata

2.4 GreenPlum数据库网络与主机配置

2.4.1 配置主机名解析

# 编辑/etc/hosts文件（所有节点执行）
$ sudo vi /etc/hosts

# 添加以下内容
192.168.1.10 mdw.fgedu.net.cn mdw
192.168.1.11 smdw.fgedu.net.cn smdw
192.168.1.21 sdw1.fgedu.net.cn sdw1
192.168.1.22 sdw2.fgedu.net.cn sdw2
192.168.1.23 sdw3.fgedu.net.cn sdw3

# 验证主机名解析
$ ping -c 2 sdw1.fgedu.net.cn
PING sdw1.fgedu.net.cn (192.168.1.21) 56(84) bytes of data.
64 bytes from sdw1.fgedu.net.cn (192.168.1.21): icmp_seq=1 ttl=64 time=0.234 ms
64 bytes from sdw1.fgedu.net.cn (192.168.1.21): icmp_seq=2 ttl=64 time=0.156 ms

2.4.2 配置SSH免密登录

# 在Master节点生成SSH密钥
$ ssh-keygen -t rsa -b 4096
Generating public/private rsa key pair.
Enter file in which to save the key (/home/gpadmin/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/gpadmin/.ssh/id_rsa.
Your public key has been saved in /home/gpadmin/.ssh/id_rsa.pub.

# 将公钥复制到所有节点（包括本机）
$ ssh-copy-id gpadmin@mdw.fgedu.net.cn
$ ssh-copy-id gpadmin@smdw.fgedu.net.cn
$ ssh-copy-id gpadmin@sdw1.fgedu.net.cn
$ ssh-copy-id gpadmin@sdw2.fgedu.net.cn
$ ssh-copy-id gpadmin@sdw3.fgedu.net.cn

# 验证SSH免密登录
$ ssh sdw1.fgedu.net.cn “hostname”
sdw1.fgedu.net.cn

# 创建主机列表文件
$ vi /home/gpadmin/hostfile_all
mdw.fgedu.net.cn
smdw.fgedu.net.cn
sdw1.fgedu.net.cn
sdw2.fgedu.net.cn
sdw3.fgedu.net.cn

# 创建Segment主机列表文件
$ vi /home/gpadmin/hostfile_segments
sdw1.fgedu.net.cn
sdw2.fgedu.net.cn
sdw3.fgedu.net.cn

2.4.3 关闭防火墙和SELinux

# 关闭防火墙
$ sudo systemctl stop firewalld
$ sudo systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

# 检查防火墙状态
$ sudo systemctl status firewalld
● firewalld.service – firewalld – dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)

# 关闭SELinux
$ sudo setenforce 0
$ sudo vi /etc/selinux/config
SELINUX=disabled

# 检查SELinux状态
$ getenforce
Disabled

Part03-生产环境项目实施方案

3.1 GreenPlum数据库软件包安装实战

3.1.1 下载GreenPlum安装包

# 创建软件存放目录
$ mkdir -p /home/gpadmin/software
$ cd /home/gpadmin/software

# 下载GreenPlum安装包（示例）
# 实际使用时从VMware官方下载
$ wget https://network.tanzu.vmware.com/products/vmware-greenplum/releases/7.0.0/greenplum-db-7.0.0-rhel9-x86_64.rpm

# 查看下载的文件
$ ls -lh
total 350M
-rw-r–r–. 1 gpadmin gpadmin 350M Apr 8 10:30 greenplum-db-7.0.0-rhel9-x86_64.rpm

3.1.2 安装GreenPlum软件

# 在Master节点安装GreenPlum软件
$ cd /home/gpadmin/software
$ sudo rpm -ivh greenplum-db-7.0.0-rhel9-x86_64.rpm
Preparing… ################################# [100%]
Updating / installing…
1:greenplum-db-7.0.0-1.el9 ################################# [100%]

# 查看安装目录
$ ls -la /usr/local/greenplum-db-7.0.0/
total 24
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 bin
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 docs
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 etc
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 ext
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 include
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 lib
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 sbin
drwxr-xr-x. 2 gpadmin gpadmin 4096 Apr 8 10:35 share

# 创建软链接
$ sudo ln -s /usr/local/greenplum-db-7.0.0 /GreenPlum/app
$ sudo chown -R gpadmin:gpadmin /GreenPlum/app

# 验证安装
$ /GreenPlum/app/bin/gpversion
gpversion:Greenplum Database 7.0.0

3.1.3 分发软件到所有节点

# 使用gpseginstall工具分发软件
$ source /GreenPlum/app/greenplum_path.sh
$ gpseginstall -f /home/gpadmin/hostfile_segments -u gpadmin -p /home/gpadmin/software/greenplum-db-7.0.0-rhel9-x86_64.rpm

20260408:10:40:15:012345 gpseginstall:mdw:gpadmin-[INFO]:-Installation in progress on host sdw1.fgedu.net.cn
20260408:10:40:25:012345 gpseginstall:mdw:gpadmin-[INFO]:-Installation in progress on host sdw2.fgedu.net.cn
20260408:10:40:35:012345 gpseginstall:mdw:gpadmin-[INFO]:-Installation in progress on host sdw3.fgedu.net.cn
20260408:10:40:45:012345 gpseginstall:mdw:gpadmin-[INFO]:-Installation in progress on host smdw.fgedu.net.cn
20260408:10:40:55:012345 gpseginstall:mdw:gpadmin-[INFO]:-Installation completed successfully on all hosts

# 验证所有节点安装
$ gpssh -f /home/gpadmin/hostfile_all -e “ls -la /GreenPlum/app/bin/gpversion”
[sdw1] ls -la /GreenPlum/app/bin/gpversion
[sdw1] -rwxr-xr-x. 1 gpadmin gpadmin 12345 Apr 8 10:40 /GreenPlum/app/bin/gpversion
[sdw2] ls -la /GreenPlum/app/bin/gpversion
[sdw2] -rwxr-xr-x. 1 gpadmin gpadmin 12345 Apr 8 10:40 /GreenPlum/app/bin/gpversion
[sdw3] ls -la /GreenPlum/app/bin/gpversion
[sdw3] -rwxr-xr-x. 1 gpadmin gpadmin 12345 Apr 8 10:40 /GreenPlum/app/bin/gpversion

3.2 GreenPlum数据库集群初始化实战

3.2.1 创建集群配置文件

# 创建集群初始化配置文件
$ vi /home/gpadmin/gpinitsystem_config

# 添加以下内容
ARRAY_NAME=”GreenPlum Data Warehouse”
CLUSTER_NAME=”fgedudb_cluster”
MACHINE_LIST_FILE=/home/gpadmin/hostfile_segments
SEG_PREFIX=gpseg
PORT_BASE=40000
declare -a DATA_DIRECTORY=(/GreenPlum/fgdata/primary /GreenPlum/fgdata/primary)
MASTER_HOSTNAME=mdw.fgedu.net.cn
MASTER_DIRECTORY=/GreenPlum/fgdata
MASTER_PORT=5432
TRUSTED_SHELL=/usr/bin/ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
MIRROR_PORT_BASE=50000
declare -a MIRROR_DATA_DIRECTORY=(/GreenPlum/fgdata/mirror /GreenPlum/fgdata/mirror)
DATABASE_NAME=fgedudb

# 创建Standby Master配置文件
$ vi /home/gpadmin/hostfile_standby
smdw.fgedu.net.cn

3.2.2 执行集群初始化

# 执行集群初始化
$ gpinitsystem -c /home/gpadmin/gpinitsystem_config -s /home/gpadmin/hostfile_standby

20260408:10:50:15:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Checking configuration parameters, please wait…
20260408:10:50:16:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Reading Greenplum configuration file /home/gpadmin/gpinitsystem_config
20260408:10:50:16:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Locale has not been set in config file, defaulting to C
20260408:10:50:17:012345 gpinitsystem:mdw:gpadmin-[INFO]:-No DATABASE_NAME specified, defaulting to ‘postgres’
20260408:10:50:17:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Checking for filesystem compatibility on segment hosts…
20260408:10:50:18:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Checking for OS compatibility on segment hosts…
20260408:10:50:19:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Configuring segment instances…
20260408:10:50:20:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Initializing the Master instance…
20260408:10:50:25:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Initializing the Standby Master instance…
20260408:10:50:30:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Starting the Master instance…
20260408:10:50:35:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Initializing the Segment instances…
20260408:10:50:45:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Starting the Segment instances…
20260408:10:50:55:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Greenplum Database instance successfully created.

Continue with Greenplum creation? Yy|Nn (default=N):
> y
20260408:10:51:00:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Building the Greenplum Database instance…
20260408:10:51:30:012345 gpinitsystem:mdw:gpadmin-[INFO]:-Greenplum Database instance created successfully.

3.2.3 验证集群状态

# 检查集群状态
$ gpstate -c

20260408:10:55:15:012345 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -c
20260408:10:55:15:012345 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: ‘postgres (Greenplum Database) 7.0.0’
20260408:10:55:15:012345 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: ‘PostgreSQL 9.4.26 (Greenplum Database 7.0.0)’
20260408:10:55:15:012345 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master…
20260408:10:55:16:012345 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments…
.
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Greenplum instance status summary
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Master instance = Active
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Standby master instance = Active
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Total segment instance count from metadata = 12
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Total primary segment count = 6
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Total mirror segment count = 6
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Total primary segment(s) with valid status = 6
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:-Total mirror segment(s) with valid status = 6
20260408:10:55:17:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–

# 连接数据库验证
$ psql -d postgres
psql (9.4.26)
Type “help” for help.

postgres=# SELECT version();
version
—————————————————————————————————————-
PostgreSQL 9.4.26 (Greenplum Database 7.0.0 build commit:4b1c74d9e0a0e0b1e0d1a1b1c1d1e1f1a1b1c1d1) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)

postgres=# \q

3.3 GreenPlum数据库安装后配置实战

3.3.1 配置远程访问

# 编辑pg_hba.conf文件
$ vi /GreenPlum/fgdata/pg_hba.conf

# 添加以下内容（允许远程访问）
host all all 192.168.1.0/24 trust
host all all 0.0.0.0/0 md5

# 编辑postgresql.conf文件
$ vi /GreenPlum/fgdata/postgresql.conf

# 修改以下参数
listen_addresses = ‘*’
port = 5432
max_connections = 500

# 重新加载配置
$ gpstop -u
20260408:11:00:15:012345 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20260408:11:00:15:012345 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating inputs…
20260408:11:00:16:012345 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information…
20260408:11:00:17:012345 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
20260408:11:00:18:012345 gpstop:mdw:gpadmin-[INFO]:-Configuration successfully reloaded on all segments

3.3.2 创建业务用户和数据库

# 连接数据库
$ psql -d postgres

# 创建数据库
postgres=# CREATE DATABASE fgedudb;
CREATE DATABASE

# 创建用户
postgres=# CREATE USER fgedu WITH PASSWORD ‘fgedu123456’;
CREATE ROLE

# 授权
postgres=# GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;
GRANT

# 创建Schema
postgres=# \c fgedudb
You are now connected to database “fgedudb” as user “gpadmin”.
fgedudb=# CREATE SCHEMA fgedu;
CREATE SCHEMA

# 授权Schema权限
fgedudb=# GRANT ALL ON SCHEMA fgedu TO fgedu;
GRANT

# 验证用户
fgedudb=# \du
List of roles
Role name | Attributes | Member of
———–+—————————————————————————+———–
fgedu | | {}
gpadmin | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}

3.3.3 配置资源队列

# 创建资源队列
fgedudb=# CREATE RESOURCE QUEUE fgedu_queue WITH (ACTIVE_STATEMENTS=10, MEMORY_LIMIT=’2GB’);
CREATE QUEUE

# 将用户绑定到资源队列
fgedudb=# ALTER USER fgedu RESOURCE QUEUE fgedu_queue;
ALTER ROLE

# 查看资源队列
fgedudb=# SELECT * FROM pg_resqueue;
rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
————-+—————+————–+—————+——————–
pg_default | 20 | -1 | 2 | 0
fgedu_queue | 10 | -1 | 2 | 0
(2 rows)

风哥提示：资源队列是GreenPlum资源管理的重要机制，可以有效控制并发查询数量和内存使用，避免资源争抢。from GreenPlum视频:www.itpux.com

Part04-生产案例与实战讲解

4.1 GreenPlum数据库5节点集群部署案例

4.1.1 案例背景

项目背景：
– 企业：某电商平台
– 数据量：日增量30GB，总数据量约100TB
– 并发用户：约100个分析用户
– 查询类型：实时报表查询为主

集群规划：
– Master节点：1台（含Standby）
– Segment节点：3台
– 每台Segment部署2个Primary + 2个Mirror
– 总计6个Primary Segment，6个Mirror Segment

硬件配置：
– Master：32核CPU、128GB内存、500GB SSD
– Segment：64核CPU、256GB内存、2TB SSD
– 网络：万兆以太网

4.1.2 部署步骤总结

部署步骤总结：

步骤1：环境准备（约30分钟）
– 操作系统参数配置
– 用户和目录创建
– 网络和主机名配置
– SSH免密登录配置

步骤2：软件安装（约20分钟）
– Master节点软件安装
– 软件分发到所有节点
– 环境变量配置

步骤3：集群初始化（约15分钟）
– 创建配置文件
– 执行gpinitsystem
– 验证集群状态

步骤4：安装后配置（约15分钟）
– 远程访问配置
– 用户和数据库创建
– 资源队列配置
– 参数优化

总耗时：约80分钟

4.2 GreenPlum数据库安装验证与测试

4.2.1 集群状态验证

# 检查Master状态
$ gpstate -f

20260408:11:10:15:012345 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -f
20260408:11:10:15:012345 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: ‘postgres (Greenplum Database) 7.0.0’
20260408:11:10:15:012345 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master…
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:–Master Configuration Manager State
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:-Configuration Manager Status: Active
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:-Configuration Manager Version: 7.0.0
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:–Standby Master Configuration
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:-Standby Master Status: Active
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:-Standby Master Host: smdw.fgedu.net.cn
20260408:11:10:16:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–

# 检查Segment状态
$ gpstate -m

20260408:11:10:20:012345 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -m
20260408:11:10:20:012345 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: ‘postgres (Greenplum Database) 7.0.0’
20260408:11:10:20:012345 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master…
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:–Mirror Segment Status
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:-Total Mirror Segments: 6
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:-Mirror Segments with Valid Status: 6
20260408:11:10:21:012345 gpstate:mdw:gpadmin-[INFO]:—————————————————–

4.2.2 功能测试

# 创建测试表
$ psql -d fgedudb -U fgedu

fgedudb=> CREATE TABLE fgedu.fgedu_test (
id SERIAL,
name VARCHAR(100),
create_time TIMESTAMP DEFAULT NOW()
) DISTRIBUTED BY (id);
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_test (name)
SELECT ‘test_’ || generate_series(1, 10000);
INSERT 0 10000

# 查询测试
fgedudb=> SELECT COUNT(*) FROM fgedu.fgedu_test;
count
——-
10000
(1 row)

# 查看数据分布
fgedudb=> SELECT gp_segment_id, COUNT(*)
FROM fgedu.fgedu_test
GROUP BY gp_segment_id
ORDER BY gp_segment_id;
gp_segment_id | count
—————+——-
0 | 1667
1 | 1667
2 | 1666
3 | 1667
4 | 1666
5 | 1667
(6 rows)

# 删除测试表
fgedudb=> DROP TABLE fgedu.fgedu_test;
DROP TABLE

4.3 GreenPlum数据库安装常见问题处理

4.3.1 SSH连接问题

问题现象：
gpssh: error: ssh to host sdw1.fgedu.net.cn failed

排查步骤：
# 1. 检查SSH服务状态
$ ssh sdw1.fgedu.net.cn “systemctl status sshd”

# 2. 检查主机名解析
$ ping sdw1.fgedu.net.cn

# 3. 检查SSH密钥
$ ssh -v gpadmin@sdw1.fgedu.net.cn

# 4. 重新配置SSH免密登录
$ ssh-copy-id gpadmin@sdw1.fgedu.net.cn

解决方案：
– 确保所有节点的sshd服务正常运行
– 确保主机名解析正确
– 确保SSH密钥正确配置

4.3.2 初始化失败问题

问题现象：
gpinitsystem:mdw:gpadmin-[ERROR]:-Failed to initialize segment instance

排查步骤：
# 1. 检查日志文件
$ cat /home/gpadmin/gpAdminLogs/gpinitsystem_*.log

# 2. 检查目录权限
$ ls -la /GreenPlum/fgdata/

# 3. 检查磁盘空间
$ df -h /GreenPlum

# 4. 检查内核参数
$ sysctl -a | grep shm

解决方案：
– 确保目录权限正确（gpadmin用户拥有）
– 确保磁盘空间充足
– 确保内核参数配置正确
– 清理失败的初始化后重试

Part05-风哥经验总结与分享

5.1 GreenPlum数据库安装最佳实践

5.1.1 安装前检查清单

安装前必须检查的项目：

1. 硬件检查
[ ] CPU核心数满足要求
[ ] 内存容量满足要求
[ ] 磁盘空间充足
[ ] 网络带宽满足要求

2. 操作系统检查
[ ] 操作系统版本兼容
[ ] 内核版本兼容
[ ] 必要软件包已安装
[ ] 内核参数已配置

3. 网络检查
[ ] 主机名解析正确
[ ] SSH免密登录配置
[ ] 防火墙已关闭
[ ] SELinux已关闭

4. 存储检查
[ ] 存储目录已创建
[ ] 目录权限正确
[ ] 磁盘性能满足要求

5.1.2 安装注意事项

风哥提示：安装GreenPlum时需要注意以下几点：

确保所有节点时间同步（NTP配置）
确保所有节点使用相同的操作系统版本和内核版本
确保所有节点的软件包版本一致
安装过程中不要中断，等待安装完成
安装完成后立即进行备份

5.2 GreenPlum数据库安装检查清单

安装后验证清单：

1. 集群状态验证
[ ] gpstate -c 显示所有Segment正常
[ ] gpstate -f 显示Standby Master正常
[ ] gpstate -m 显示所有Mirror正常

2. 连接验证
[ ] 本地连接正常
[ ] 远程连接正常
[ ] JDBC/ODBC连接正常

3. 功能验证
[ ] 创建数据库成功
[ ] 创建表成功
[ ] 插入数据成功
[ ] 查询数据成功
[ ] 数据分布均匀

4. 高可用验证
[ ] Master切换测试
[ ] Segment故障恢复测试

5. 性能验证
[ ] 基准测试完成
[ ] 性能指标达标

5.3 GreenPlum数据库自动化安装脚本

5.3.1 环境准备脚本

#!/bin/bash
# gp_env_prepare.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 配置内核参数
configure_kernel() {
echo “配置内核参数…”
cat >> /etc/sysctl.conf << EOF kernel.shmmax = 500000000000 kernel.shmmni = 4096 kernel.shmall = 4000000000 kernel.sem = 250 32000 100 128 fs.file-max = 7672460 net.core.rmem_max = 2097152 net.core.wmem_max = 2097152 vm.transparent_hugepage = never EOF sysctl -p } # 配置用户限制 configure_limits() { echo "配置用户限制..." cat >> /etc/security/limits.conf << EOF * soft nofile 65536 * hard nofile 65536 * soft nproc 131072 * hard nproc 131072 * soft core unlimited * hard core unlimited EOF } # 关闭防火墙和SELinux disable_security() { echo "关闭防火墙和SELinux..." systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config } # 创建用户和目录 create_user() { echo "创建用户和目录..." groupadd -g 1000 gpadmin useradd -u 1000 -g gpadmin -m -d /home/gpadmin -s /bin/bash gpadmin echo "gpadmin" | passwd --stdin gpadmin mkdir -p /GreenPlum/app /GreenPlum/fgdata chown -R gpadmin:gpadmin /GreenPlum } # 主函数 main() { configure_kernel configure_limits disable_security create_user echo "环境准备完成！" } main

5.3.2 集群初始化脚本

#!/bin/bash
# gp_cluster_init.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

GP_HOME=/GreenPlum/app
MASTER_DATA=/GreenPlum/fgdata

# 创建配置文件
create_config() {
echo “创建集群配置文件…”
cat > /home/gpadmin/gpinitsystem_config << EOF ARRAY_NAME="GreenPlum Data Warehouse" CLUSTER_NAME="fgedudb_cluster" MACHINE_LIST_FILE=/home/gpadmin/hostfile_segments SEG_PREFIX=gpseg PORT_BASE=40000 declare -a DATA_DIRECTORY=(/GreenPlum/fgdata/primary /GreenPlum/fgdata/primary) MASTER_HOSTNAME=mdw.fgedu.net.cn MASTER_DIRECTORY=/GreenPlum/fgdata MASTER_PORT=5432 TRUSTED_SHELL=/usr/bin/ssh CHECK_POINT_SEGMENTS=8 ENCODING=UNICODE MIRROR_PORT_BASE=50000 declare -a MIRROR_DATA_DIRECTORY=(/GreenPlum/fgdata/mirror /GreenPlum/fgdata/mirror) DATABASE_NAME=fgedudb EOF } # 初始化集群 init_cluster() { echo "初始化GreenPlum集群..." source $GP_HOME/greenplum_path.sh gpinitsystem -c /home/gpadmin/gpinitsystem_config -s /home/gpadmin/hostfile_standby -a } # 验证集群 verify_cluster() { echo "验证集群状态..." gpstate -c psql -d postgres -c "SELECT version();" } # 主函数 main() { create_config init_cluster verify_cluster echo "集群初始化完成！" } main

风哥提示：自动化脚本可以大大提高部署效率，但在生产环境使用前务必在测试环境充分验证。更多视频教程www.fgedu.net.cn

from GreenPlum视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html