操作系统 (本次以RHEL7为例）
Greenplum Database 6支持以下操作系统平台:
Red Hat Enterprise Linux 64-bit 8.x (as of Greenplum Database version 6.20)
Red Hat Enterprise Linux 64-bit 7.x (See the following Note.)
Red Hat Enterprise Linux 64-bit 6.x
CentOS 64-bit 7.x
CentOS 64-bit 6.x
Ubuntu 18.04 LTS
Oracle Linux 64-bit 7, using the Red Hat Compatible Kernel (RHCK)
注意:请勿在Greenplum 数据库主机上安装杀毒软件，因为该软件可能会造成额外的CPU和IO负载，干扰数据库的操作。
文件系统
XFS是Greenplum数据库数据目录的最佳实践文件系统，XFS应该用下列选项挂载：
例：/etc/fstab 下添加如下挂载
[root@gpmaster ~]# cat >>/etc/fstab
/dev/sdb /data xfs rw,nodev,noatime,nobarrier,inode64 0 0I/O配置
在含有数据目录的设备上，设置blockdev预读尺寸为16384。 下列命令设置/dev/sdb的预读值大小
[root@gpmaster ~]# /sbin/blockdev --setra 16384 /dev/sdb应该为所有数据目录设备设置deadline IO调度器。
[root@gpmaster ~] # echo "deadline" >/sys/block/sdb/queue/scheduler
[root@gpmaster ~] # cat /sys/block/sdb/queue/scheduler
noop anticipatory [deadline] cfq在/etc/security/limits.conf文件中增加OS文件和进程的最大数量
[root@gpmaster ~] # cat >>/etc/security/limits.conf
* soft nofile 524288
* hard nofile 524288
* soft nproc 131072
* hard nproc 131072依赖软件（需先配置yum源）
readline readline-devel libxmal-devel python-devel perl perl-devel krb5 krb5-devel apr apr-devel libcurl libcurl-devel
libxml2 libyaml zlib zlib-devel openssl openssl-devel openssl-libs bzip2 bzip2-devel postgresql-devel ntp防火墙设置
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalldSELinux设置
cp /etc/sysconfig/selinux /etc/sysconfig/selinux_bak
sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config内核参数设置
shmall=`echo $(expr $(getconf _PHYS_PAGES) / 2)`
shmmax=`echo $(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))`
min_free_kbytes=`awk 'BEGIN {OFMT = "%.0f";} /MemTotal/ {print "vm.min_free_kbytes =",$2 * .03;}' /proc/meminfo`
vim /etc/sysctl.conf
kernel.shmall = $shmall
kernel.shmmax = $shmmax
kernel.shmmni = 4096
vm.overcommit_memory = 2
vm.overcommit_ratio = 95
net.ipv4.ip_local_port_range = 1025 65535
net.ipv4.ip_local_reserved_ports=65330
kernel.sem = 500 2048000 200 4096
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
net.core.somaxconn = 32768
vm.swappiness = 10
vm.zone_reclaim_mode = 0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.dirty_background_ratio = 0
vm.dirty_ratio = 0
vm.dirty_background_bytes = 1610612736
vm.dirty_bytes = 4294967296
vm.vfs_cache_pressure = 1024
echo "${min_free_kbytes}" >>/etc/sysctl.conf透明大页设置
cp /etc/default/grub /etc/default/grub_bak
sed -i 's/GRUB_CMDLINE_LINUX="/GRUB_CMDLINE_LINUX="elevator=deadline transparent_hugepage=never /' /etc/default/grub
sed -i 's/#RemoveIPC/RemoveIPC/' /etc/systemd/logind.conf主机名及/etc/hosts文件设置，例如
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
#::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.126.201 master mdw GPFHOST
192.168.126.202 host1 sdw
192.168.126.203 host2 seg01创建用户gpadmin,设置密码（所有节点，密码自定义）
useradd gpadmin
passwd gpadmin创建数据目录
mkdir /data/master --（只有主节点，其他为所有节点）
mkdir /data/primary
mkdir /data/mirror
chown -R gpadmin:gpadmin /data安装软件（所有节点）
rpm -ivh open-source-greenplum-db-6.13.0-rhel7-x86_64.rpm
--可以安装到默认安装目录（/usr/local/greenplum-db-6.13.0），也可以自定义(需先创建）
rpm -ivh open-source-greenplum-db-6.13.0-rhel7-x86_64.rpm --prefix=/opt/gpdb
并将安装目录赋权给gpadmin用户
chown -R gpadmin:gpadmin /opt/gpdb以上步骤使用root用户执行，可以将以上步骤整理到脚本，更方便快捷安装。
以下步骤用gpadmin用户执行
配置环境变量(根据具体情况设置）
su - gpadmin
vim .bashrc
export GPHOME=/opt/gpdb
export MASTER_DATA_DIRECTORY=/data/master/gpseg-1
export PGDATABASE=postgres
export PGPORT=5432
source ${GPHOME}/greenplum_path.shgpadmin用户配置免密（master 节点执行）
ssh-keygen -t rsa
ssh-copy-id host1
ssh-copy-id host2
cat >host.list
master
host1
host2
gpssh-exkeys -f host.list检查网络(例子为测试环境，如果是万兆网络，应该在1000MB/sec以上)
gpcheckperf -f host.list -r n -d /tmp >net_test.out
cat net_test.out
/opt/gp4/bin/gpcheckperf -f host.list -r n -d /tmp
-------------------
-- NETPERF TEST
-------------------
====================
== RESULT
====================
Netperf bisection bandwidth test
master -> host1 = 324.970000
host1-> master = 379.360000
host2 -> master = 389.390000
master -> host2 = 397.100000
Summary:
sum = 1490.82 MB/sec
min = 324.97 MB/sec
max = 397.10 MB/sec
avg = 372.71 MB/sec
median = 389.39 MB/sec
[Warning] connection between gp-master and gp-host1 is no good检查I/O(例子为测试环境，不同磁盘不同读写速度)
gpcheckperf -f host.list -r ds -D -d /data -v 2048000000 >io.out
cat io.out
[Info] sh -c 'cat /proc/meminfo | grep MemTotal'
MemTotal: 3861364 kB
/opt/gpdb/greenplum-db-6.13.0/bin/gpcheckperf -f host.list -r ds -D -d /data -v 2048000000
--------------------
SETUP 2022-08-06T20:41:26.505312
--------------------
[Info] verify python interpreter exists
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'python -c print'
[Info] making gpcheckperf directory on all hosts ...
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'rm -rf /data/gpcheckperf_$USER ; mkdir -p /data/gpcheckperf_$USER'
[Info] copy local /opt/gpdb/greenplum-db-6.13.0/bin/lib/multidd to remote /data/gpcheckperf_$USER/multidd
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpscp -f host.list /opt/gpdb/greenplum-db-6.13.0/bin/lib/multidd =:/data/gpcheckperf_$USER/multidd
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'chmod a+rx /data/gpcheckperf_$USER/multidd'
--------------------
-- DISK WRITE TEST
--------------------
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'time -p /data/gpcheckperf_$USER/multidd -i /dev/zero -o /data/gpcheckperf_$USER/ddfile -B 32768'
--------------------
-- DISK READ TEST
--------------------
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'time -p /data/gpcheckperf_$USER/multidd -o /dev/null -i /data/gpcheckperf_$USER/ddfile -B 32768'
--------------------
-- STREAM TEST
--------------------
[Info] copy local /opt/gpdb/greenplum-db-6.13.0/bin/lib/stream to remote /data/gpcheckperf_$USER/stream
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpscp -f host.list /opt/gpdb/greenplum-db-6.13.0/bin/lib/stream =:/data/gpcheckperf_$USER/stream
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'chmod a+rx /data/gpcheckperf_$USER/stream'
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list /data/gpcheckperf_$USER/stream
--------------------
TEARDOWN
--------------------
[Info] /opt/gpdb/greenplum-db-6.13.0/bin/gpssh -f host.list 'rm -rf /data/gpcheckperf_$USER'
====================
== RESULT 2022-08-06T20:42:28.559488
====================
disk write avg time (sec): 25.51
disk write tot bytes: 23724195840
disk write tot bandwidth (MB/s): 925.60
disk write min bandwidth (MB/s): 225.80 [ host1]
disk write max bandwidth (MB/s): 355.74 [master]
-- per host bandwidth --
disk write bandwidth (MB/s): 355.74 [master]
disk write bandwidth (MB/s): 225.80 [ host1]
disk write bandwidth (MB/s): 344.06 [host2]
disk read avg time (sec): 7.18
disk read tot bytes: 23724195840
disk read tot bandwidth (MB/s): 3160.14
disk read min bandwidth (MB/s): 981.99 [master]
disk read max bandwidth (MB/s): 1093.00 [host2]
-- per host bandwidth --
disk read bandwidth (MB/s): 981.99 [master]
disk read bandwidth (MB/s): 1085.14 [ host1]
disk read bandwidth (MB/s): 1093.00 [host2]
stream tot bandwidth (MB/s): 27224.50
stream min bandwidth (MB/s): 7223.90 [master]
stream max bandwidth (MB/s): 11090.40 [host2]
-- per host bandwidth --
stream bandwidth (MB/s): 7223.90 [master]
stream bandwidth (MB/s): 8910.20 [ host1]
stream bandwidth (MB/s): 11090.40 [host2]gpinitsystem_config文件配置（端口和实例可以自定义（
cp /opt/gpdb/docs/cli_help/gpconfigs/gpinitsystem_config /home/gpadmin/gpinitsystem_config
vim gpinitsystem_config
ARRAY_NAME="EMC Greenplum DW"
SEG_PREFIX=gpseg
PORT_BASE=60000
declare -a DATA_DIRECTORY=(/data/primary /data/primary )
MASTER_HOSTNAME=master
MASTER_DIRECTORY=/data/master
MASTER_PORT=5432
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UTF8
MIRROR_PORT_BASE=70000
declare -a MIRROR_DATA_DIRECTORY=(/data/mirror /data/mirror )初始化集群
gpinitsystem -c gpinitsystem_config -h host.list
根据服务器配置设置数据库相关参数
gp_vmem_protect_limit
shared_buffers
statement_mem
max_statement_mem
maintenance_work_mem
effective_cache_size
例如：
gpconfig -c shared_buffers -v 1000MB添加standby节点
备节点创建目录
mkdir /data/master
主节点添加（端口默认可以省略不写）
gpinitstandby -s host1 -P 5432至此，安装完成
升级
准备安装软件
open-source-greenplum-db-6.18.2-rhel7-x86_64.rpm
创建安装目录
mkdir /opt/gpdb_6182
rpm -ivh open-source-greenplum-db-6.18.2-rhel7-x86_64.rpm --prefix=/opt/gpdb_6182
修改目录权限
chown -R gpadmin:gpadmin /opt/gpdb_6182
复制Oracle兼容函数及其他需要使用的插件的so文件到
/opt/gpdb_6182/greenplum-db-6.18.2/lib/postgresql目录下
还有相应的SQL文件复制到/opt/gpdb_6182/greenplum-db-6.18.2/share/postgresql/extension目录下面
修改环境变量
vim /home/gpadmin/.bashrc
export GPHOME=/opt/gpdb_6182
export MASTER_DATA_DIRECTORY=/data/master/gpseg-1
source ${GPHOME}/greenplum_path.sh
重启数据库
gpstop -M fast -a
source /home/gpadmin/.bashrc
gpstart -a
查看数据库版本
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.24 (Greenplum Database 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Nov 10 2021 00:17:26
(1 row)扩容
在需要扩容的服务器上安装greenplum
安装步骤如文档中6.1-10
新服务器清单文件
cat >new_host.list
host3
host4
host5
使用gpexpand工具执行系统扩展，Segment初始化，生成新节点配置文件
gpexpand -f new_host.list
20220608:15:31:43:032527 gpexpand:gpsrv147:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.20.3 build commit:24b949d2585cdbe8a157062fb756013e7c2874ab Open Source'
20220608:15:31:43:032527 gpexpand:gpsrv147:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.20.3 build commit:24b949d2585cdbe8a157062fb756013e7c2874ab Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Apr 6 2022 19:59:57'
20220608:15:31:43:032527 gpexpand:gpsrv147:gpadmin-[INFO]:-Querying gpexpand schema for current expansion state
System Expansion is used to add segments to an existing GPDB array.
gpexpand did not detect a System Expansion that is in progress.
Before initiating a System Expansion, you need to provision and burn-in
the new hardware. Please be sure to run gpcheckperf to make sure the
new hardware is working properly.
Please refer to the Admin Guide for more information.
Would you like to initiate a new System Expansion Yy|Nn (default=N):
y --输入yes
You must now specify a mirroring strategy for the new hosts. Spread mirroring places
a given hosts mirrored segments each on a separate host. You must be
adding more hosts than the number of segments per host to use this.
Grouped mirroring places all of a given hosts segments on a single
mirrored host. You must be adding at least 2 hosts in order to use this.
What type of mirroring strategy would you like?
spread|grouped (default=grouped): ---默认即可
By default, new hosts are configured with the same number of primary
segments as existing hosts. Optionally, you can increase the number
of segments per host.
For example, if existing hosts have two primary segments, entering a value
of 2 will initialize two additional segments on existing hosts, and four
segments on new hosts. In addition, mirror segments will be added for
these new primary segments if mirroring is enabled.
How many new primary segments per host do you want to add? (default=0):
--是否增加新的主段，和现有集群一致即可，默认不添加
Generating configuration file... 20220608:15:31:52:032527 gpexpand:gpsrv147:gpadmin-[INFO]:-Generating input file...
Input configuration file was written to 'gpexpand_inputfile_20220608_153152'.
Please review the file and make sure that it is correct then re-run
with: gpexpand -i gpexpand_inputfile_20220608_153152
20220608:15:31:52:032527 gpexpand:gpsrv147:gpadmin-[INFO]:-Exiting...
执行配置文件，增加新节点，并在postgres数据库中创建扩展模式
gpexpand -i gpexpand_inputfile_20220608_153152
表重新分布
gpexpand -d 60:00:00 -a
运行gpexpand最长持续60小时，以将表重新分布给新的Segment,要根据实际业务情况，尽量在业务空闲时段进行，还可以根据参数选项增加并发和进行analyze等操作，常用参数选项如下：
gpexpand :
-a | --analyze
在扩展后运行ANALYZE更新表的统计信息，默认是不运行ANALYZE。
-B batch_size
在暂停一秒钟之前发送给给定主机的远程命令的批量大小。默认值是16， 有效值是1-128。
-c | --clean
删除扩展模式。
-d | --duration hh:mm:ss
扩展会话的持续时间。
-e | --end 'YYYY-MM-DD hh:mm:ss'
扩展会话的结束日期及时间。
-f | --hosts-file filename
指定包含用于系统扩展的新主机列表的文件的名称。文件的每一行都必须包含一个主机名。
-i | --input input_file
指定扩展配置文件的名称，其中为每个要添加的Segment包含一行，格式为：
hostname:address:port:datadir:dbid:content:preferred_role
-n parallel_processes
要同时重新分布的表的数量。有效值是1 - 96。
-r | --rollback
回滚失败的扩展设置操作。
-s | --silent
以静默模式运行。在警告时，不提示确认就可继续。
-S | --simple-progress
如果指定，gpexpand工具仅在Greenplum数据库表 gpexpand.expansion_progress中记录最少的进度信息。该工具不在表 gpexpand.status_detail中记录关系大小信息和状态信息。
[-t | --tardir] directory
Segment主机上一个目录的完全限定directory，gpexpand 工具会在其中拷贝一个临时的tar文件。该文件包含用于创建Segment实例的Greenplum数据库文件。 默认目录是用户主目录。
-v | --verbose
详细调试输出。使用此选项，该工具将输出用于扩展数据库的所有DDL和DML。
--version
显示工具的版本号并退出。
重分布情况查看及分布完成后清理
命令行查看：
gpstate -x
数据库查看未重分布表总数：
psql postgres
select * from gpexpand.expansion_progress;
数据库查看未重分布表明细：
select * from gpexpand_status_detail where status='NOT STARTED';
清理
gpexpand -c
将gpexpand schema和下面关于扩容的表清理掉日常维护
集群状态查询
正常状态如下
gpstate
20220806:21:32:14:069919 gpstate:master:gpadmin6-[INFO]:-Starting gpstate with args:
20220806:21:32:14:069919 gpstate:master:gpadmin6-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source'
20220806:21:32:14:069919 gpstate:master:gpadmin6-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.24 (Greenplum Database 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Nov 10 2021 00:17:26'
20220806:21:32:14:069919 gpstate:master:gpadmin6-[INFO]:-Obtaining Segment details from master...
20220806:21:32:14:069919 gpstate:master:gpadmin6-[INFO]:-Gathering data from segments...
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-Greenplum instance status summary
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Master instance = Active
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Master standby = No master standby configured
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total segment instance count from metadata = 12
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Primary Segment Status
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total primary segments = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total primary segment valid (at master) = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total primary segment failures (at master) = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Mirror Segment Status
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total mirror segments = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total mirror segment valid (at master) = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total mirror segment failures (at master) = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes missing = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes found = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number mirror segments acting as primary segments = 0
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:- Total number mirror segments acting as mirror segments = 6
20220806:20:32:15:069919 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------异常状态示例
gpstate
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-Starting gpstate with args:
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source'
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.24 (Greenplum Database 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Nov 10 2021 00:17:26'
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-Obtaining Segment details from master...
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-Gathering data from segments...
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-Greenplum instance status summary
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Master instance = Active
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Master standby = No master standby configured
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total segment instance count from metadata = 12
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Primary Segment Status
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total primary segments = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total primary segment valid (at master) = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total primary segment failures (at master) = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Mirror Segment Status
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total mirror segments = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total mirror segment valid (at master) = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total mirror segment failures (at master) = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid files found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of postmaster.pid PIDs found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number of /tmp lock files found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes missing = 0
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number postmaster processes found = 6
20220806:21:30:41:068693 gpstate:master:gpadmin6-[WARNING]:-Total number mirror segments acting as primary segments = 2 <<<<<<<<
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:- Total number mirror segments acting as mirror segments = 4
20220806:21:30:41:068693 gpstate:master:gpadmin6-[INFO]:-----------------------------------------------------查看集群主实例与镜像实例同步状态
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:-Starting gpstate with args: -m
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source'
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.24 (Greenplum Database 6.18.2 build commit:1242aadf0137d3b26ee42c80e579e78bd7a805c7 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Nov 10 2021 00:17:26'
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:-Obtaining Segment details from master...
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:--------------------------------------------------------------
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:--Current GPDB mirror list and status
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:--Type = Group
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:--------------------------------------------------------------
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- Mirror Datadir Port Status Data Status
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- host2 /data/mirror/gpseg0 7000 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- host2 /data/mirror/gpseg1 7001 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- host1 /data/mirror/gpseg2 7000 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- host1 /data/mirror/gpseg3 7001 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- master /data/mirror/gpseg4 7000 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:- master /data/mirror/gpseg5 7001 Passive Synchronized
20220806:21:35:56:071127 gpstate:master:gpadmin6-[INFO]:--------------------------------------------------------------负载查询
vmstat 2 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 1787448 124 1719424 0 0 128 81 41 35 0 1 99 0 0
0 0 0 1789164 124 1719432 0 0 0 0 181 200 0 0 100 0 0
0 0 0 1788444 124 1719464 0 0 0 39 228 251 0 0 100 0 0
0 0 0 1787504 124 1719472 0 0 0 0 247 220 1 0 99 0 0
0 0 0 1789016 124 1719476 0 0 0 0 191 253 0 0 100 0 0会话查询
select datid, --数据库OID
datname, --数据库名
pid, --后台服务器进程号(即对应该会话在master节点操作系统进程ID号)
sess_id, --会话线程号
usesysid, --role ID号
usename, --role名称
client_addr, --客户端地址
client_port, --客户端端口
application_name, --客户端应用名称
substr(query,1,80) --请求SQL，较长SQL截取部分
query_start, --请求开始时间
backend_start, --backend进程开始时间，即用户连接数据库时建立会话的时间
xact_start, --事务开始时间
waiting, --是否等待True if waiting on a lock, false if not waiting
waiting_reason --等待原因. The value can be:lock, replication, or resgroup
from pg_stat_activity where state <> 'idle';磁盘空间查询
数据库查看使用量
select * from gp_toolkit.gp_disk_free;
系统命令行查看
gpssh -f host.list -e “df -h |grep data”集群异常修复
修复前负载判定(是否需要kill会话,是否需要重启)，查看是否存在超过一个小时以上的会话，
select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start;查看节点负载，可以使用vmstat命令，也可以使用nmon监控工具（需安装）
节点日志备份([可选]判定实例>判定路径>备份日志，在恢复前备份日志)
select time,hostname,status,datadir,content from gp_configuration_history h,gp_segment_configuration c where h.dbid=c.dbid and "desc" like '%d, and%' order by time desc limit 10;
time | hostname | status | datadir | content
-------------------------------+-------------+--------+-----------------------+---------
2022-08-06 21:31:49.003661+08 | master | u | /data/mirror/gpseg5 | 5
2022-08-06 21:31:49.000486+08 | master | u | /data/mirror/gpseg4 | 4
2022-08-06 17:31:47.073316+08 | host1 | u | /data/mirror/gpseg0 | 0
2022-08-06 17:31:47.07221+08 | host1 | u | /data/mirror/gpseg1 | 1
2022-08-06 17:30:47.007735+08 | host2 | u | /data/primary/gpseg5 | 5
2022-08-06 17:30:47.004557+08 | host2 | u | /data/primary/gpseg4 | 4
根据hostname和datadir，以及time备份对应实例pg_log目录下对应日期的日志常规修复方式(后台运行)
nohup gprecoverseg -a &若存在主备切换，则需要在修复完成后进行实例切回
nohup gprecoverseg -ra &查看修复进度
gpstate -e全量修复方式(后台运行)
nohup gprecoverseg -Fa &若存在主备切换，则需要在修复完成后进行实例切回
nohup gprecoverseg -ra &用户管理
创建role/schema
创建用户可以使用CREATE USER 或者CREATE ROLE命令，唯一区别是CREATE USER默认情况下是LOGIN， 而CREATE ROLE默认情况下是NOLOGIN.
如创建test用户可以登陆资源队列为 pg_default，密码为passwd：
create role test with login resource queue pg_default password 'qazwsx';更改用户密码
alter role test with password 'wsxedc';更改用户连接数
alter role test connection limit 30;锁定用户
alter role test with nologin;删除role
DROP ROLE [IF EXISTS] test;
如果存在有依赖的表和视图的权限，需要回收权限以后再删除创建模式
create schema test;如果是为角色创建一个同名模式：如
create schema authorization test;删除schema
DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT];授权与回收权限
根据需要授权用户使用相应权限
GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES |
TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }
ON { [TABLE] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ]
GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
……例如：
授权用户使用模式权限
GRANT USAGE ON SCHEMA schema to user;授权用户查询表权限
grant select on tablename to user;回收权限则是revoke命令，后面to 改为from即可，如回收用户使用模式权限
REVOKE USAGE ON SCHEMA schema from user;postgresql 9.0版本以后特性：使用这个语句后，如果用户jack再建新表时，用户scott就会自动有对这些新表的select权限。
alter default privileges for user jack in schema public grant select on tables to scott;收回上面的权限的语句为：
alter default privileges for user jack in schema public revoke select on tables from scott;注意执行这个语句之前，alter default privileges … grant语句之后，jack用户的建的表，scott用户已经有select权限的，这个语句不会收回这些select权限。
简单总结说alter default privileges并不会改变已有用户的权限，只有在有对象创建时，才会根据alter default privileges定义的情况给指定的用户加权限。
查看哪些用户对具体表有哪些权限
select * from information_schema.table_privileges where table_name ='test';查看具体用户是哪些表的owner或者有哪些表的哪些权限
select * from information_schema.table_privileges where grantee='test' or grantor='test';会话管理
会话查询
select pid,usename,query,waiting,query_start,xact_start from pg_stat_activity where state ='active' order by query_start;锁跟踪查询
SELECT pg_locks.pid as pid, --进程ID
transactionid as transaction_id, --事务ID
nspname as schemaname, --schema名
relname as object_name, --对象名
locktype as lock_type, --锁类型
mode lock_mode, --锁模式
CASE
WHEN granted = 'f' THEN
'get_lock'
WHEN granted = 't' THEN
'wait_lock'
END lock_satus, --锁状态：持有锁|等待锁
CASE
WHEN waiting = 'false' THEN
'already get lock,sql executing'
WHEN waiting = 'true' THEN
'waiting get lock,sql waiting execute'
END waitting_satus, --当前会话状态：执行中|等待中
least(query_start, xact_start) AS query_start, --query请求开始时间
substr(query, 1, 25) AS query_text --当前SQL语句
FROM pg_locks
LEFT OUTER JOIN pg_class
ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace
ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
WHERE NOT pg_locks.pid = pg_backend_pid()
AND pg_locks.pid = pg_stat_activity.pid
ORDER BY query_start;会话kill
停止会话
当报Too many sessions会话数满或数据库无响应或事务被锁需要中断锁源头时，可以使用杀会话方式临时解决。优先建议使pg_cancel_backend()函数(长事务会话需要等待较长时间中断)，在无法等待较长时间等待时，可pg_terminate_backend()函数(该方式杀会话有极小概率导致系统元数据不一致)。在数据库响应等极端情况可，方可考虑使用kill -9 pid进行强制会话进程中断。
正常取消会话
select pg_cancel_backend(pid);强制中断会话
select pg_terminate_backend(pid);其中：pid为pg_stat_activity视图中的pid查询结果，如：
gp=# select pg_cancel_backend(2243);
pg_cancel_backend
-------------------
t
(1 row)
gp=# select pg_terminate_backend(2243);
pg_terminate_backend
----------------------
t
(1 row)查询空闲会话ID并生成语句，执行语句进行会话资源释放。
select 'select pg_terminate_backend('||pid||');' from pg_stat_activity where state ='idle';–查询某时间段之后的会话
select 'select pg_terminate_backend('||pid||');' from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP);–查询30分钟内会话
select * from pg_stat_activity where backend_start>(now() - interval '30 min');如果无法进入数据库进行会话中断停止时，可以在系统层进行查询并Kill进程
kill 空闲会话
ps -ef |grep -i postgres |grep -i idle|grep -v grep | awk '{print $2}' |xargs kill -9访问策略管理
策略文件说明
Greenplum数据库连接访问及验证机制由pg_hba.conf配置文件控制，在Greenplum数据库系统中，master主机上的pg_hba.conf文件控制客户端的访问及验证，而segment节点上的pg_hba.conf文件已经默认只与master主机连接，不接受其他外部客户端的连接。pg_hba.conf目录为$MASTER_DATA_DIRECTORY。更新该策略文件后，使用gpstop -u生效。
pg_hba.conf格式说明
该文件有如下5列，列间为空格，不区分大小写。
登陆客户端类型 允许访问数据库 数据库角色名 允许连接客户端IP(段) 验证方式
登陆客户端类型：host|local --远程登录|本地登录
允许访问数据库：dbname|all --具体数据库名|所有数据库
数据库角色名：rolename|all --具体用户名|所有用户允许连接客户端IP(段) --设置IP地址或IP地址段,格式如下：
192.168.126.100/32 --指定IP地址192.168.126.100
192.168.126.0/24 --指定192.168.126.0网段
192.168.0.0/16 --指定192.168.0.0网段
192.0.0.0/8 --指定192.0.0.0网段验证方式：ident|md5|password|trust|reject --说明如下：
ident：本地验证，即使用操作系统gpadmin用户验证。
md5：密码验证
password：发送明文密码至数据库验证(不建议使用)
trust:免密登录(不建议使用)
reject:拒绝登录，即黑名单。
建议黑名单放在pg_hba.conf文件最末例如：
host all test 192.168.126.0/24 md5更新统计信息可以使用ANALYZE语句或者analyzedb命令行工具
对一个模式进行更新
analyzedb -s schemaname;对一个表进行更新
ANALYZE tablename;
analyzedb -t schemaname.tablename;对一个表的某些列进行更新
ANALYZE tablename（column1,column2,……）
analyzedb -t schemaname.tablename -i <column1>,<column2>,……查看排队语句和资源队列状态
SELECT * FROM gp_toolkit.gp_resqueue_status;查看分配到资源队列的ROLE
SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;查看资源队列中等待的语句
SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';清除资源队列中等待的语句
SELECT ro.rolname, re.rsqname, ac.pid, lo.granted, ac.query, ac.datname FROM pg_roles ro, gp_toolkit.gp_resqueue_status re, pg_locks lo, pg_stat_activity ac WHERE ro.rolresqueue=lo.objid AND lo.objid=re.queueid AND ac.pid=lo.pid
AND ac.usename=ro.rolname;目前先整理这些，还有一些在这过程中出现的报错信息及其解决方法会重新整理一个文档。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
