[Pivotal Greenplum® 6.4 Documentation · Pivotal Greenplum Download · Pivotal Greenplum Command Center 6.1.0 Documentation]

Pivotal Greenplum数据库是一个大规模并行处理（MPP）数据库服务器，它支持下一代数据仓库和大规模分析处理。通过自动分区数据并运行并行查询，它使服务器集群可以作为单个数据库超级计算机运行，其运行速度比传统数据库快数十倍或数百倍。它支持SQL，MapReduce并行处理以及数据量，范围从数百GB到数百TB。

修订记录

修订日期	修订人	说明
2019-12-25	taliove	新增配置修改(gpconfig))内容
2019-12-24	taliove	新增部署的前置条件
2019-12-23	taliove	新增数据库升级流程、资源组、资源队列内存管理
2019-12-13	taliove	新增批量删除外部表的方法
2019-12-12	taliove	新增与Spark的连接
2019-12-09	taliove	新增GP的部署与日常维护等
2019-04-16	taliove	初稿

常用命令

重启GP

关机完成后重新启动。

gpstop -r

使配置生效

重新加载master和segment的pg_hba.conf文件以及postgresql.conf文件的运行时参数，但不会关闭Greenplum数据库阵列。编辑postgresql.conf或pg_hba.conf后，使用此选项可以激活新的配置设置。请注意，这仅适用于指定为运行时参数的配置参数。

gpstop -u

快速关闭

快速关机。任何正在进行的事务都会中断并回滚。

gpstop -M fast

文档：
gpstop 命令参考

部署

前置条件

操作系统

Privotal Greenplum 6 可以在以下系统运行：

Red Hat Enterprise Linux 64-bit 7.x
Red Hat Enterprise Linux 64-bit 6.x
CentOS 64-bit 7.x （建议7.3以上版本）
CentOS 64-bit 6.x
Ubuntu 18.04 LTS

CentOS 7 版本下载

注意：
在RedHat 6.x和CentOS 6.x系统上启用基于资源组的工作负载管理时，发现Greenplum数据库性能显着下降。此问题是由Linux cgroup内核错误引起的。此内核错误已在CentOS 7.x和Red Hat 7.x系统中修复。
对于安装在7.3之前的Red Hat Enterprise Linux 7.x或CentOS 7.x上的Greenplum Database，操作系统问题可能会导致正在运行大型工作负载的Greenplum Database在工作负荷中挂起，该问题是由Linux内核错误引起的。RHEL 7.3和CentOS 7.3解决了该问题。
对于所有Greenplum Database主机系统，必须禁用SELinux。您还应该禁用防火墙软件，但如果出于安全目的需要，可以启用防火墙软件。

JAVA

支持 JDK 8 或者 JDK 11

硬件和网络

项目	要求说明
CPU	x86_64 CPU
最小内存	16 GB 每台
磁盘空间	主机至少 150MB 安装空间。数据节点 300MB 安装空间。数据可用磁盘的至少 70%的空间。
网络	阵列式10Gbps网络 <br/> 多网口时建议使用NIC绑定

系统准备

文档：
系统准备

禁用SELinux

对于运行RHEL或CentOS的所有Greenplum Database主机系统，必须禁用SELinux。

以下命令请使用root用户进行：

 vim /etc/selinux/config
 #修改内容并保存
SELINUX=disabled

修改 hosts

vim /etc/hosts

# 粘贴以下内容到末尾并 :wq 保存
192.168.218.87 master
192.168.218.88 data1
192.168.218.89 data2
192.168.218.90 data3

关闭防火墙

根据需要执行以下命令以关闭防火墙：

systemctl stop firewalld.service
#或者
systemctl stop iptables

如果需要启用防火墙，可以参考文档

修改`/etc/sysctl.conf`

根据实际情况，修改以下值，并使用sysctl -p进行重新加载

以下配置是以8GB内存为例：

# kernel.shmall = _PHYS_PAGES / 2 # See Shared Memory Pages
kernel.shmall = 4000000000
# kernel.shmmax = kernel.shmall * PAGE_SIZE 
kernel.shmmax = 500000000
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
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
#对于内存低于64GB，注释以下两行
#vm.dirty_background_bytes = 1610612736
#vm.dirty_bytes = 4294967296
vm.min_free_kbytes = 240307

Shared Memory Pages

要计算_PHYS_PAGES可使用以下命令：

$ echo $(expr $(getconf _PHYS_PAGES) / 2) 
$ echo $(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))

作为最佳实践，我们建议您在/etc/sysctl.conf文件中设置以下值：

kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000

System Memory

对于具有64GB以上内存的主机系统，建议以下设置：

vm.dirty_background_ratio = 0
vm.dirty_ratio = 0
 # 1.5GB
vm.dirty_background_bytes = 1610612736
# 4GB
vm.dirty_bytes = 4294967296

对于具有64GB或更少内存的主机系统，请删除vm.dirty_background_bytes和vm.dirty_bytes并将两个比率参数设置为以下值：

vm.dirty_background_ratio = 3
vm.dirty_ratio = 10

增加vm.min_free_kbytes以确保轻松满足来自网络和存储驱动程序的PF_MEMALLOC请求。这对于具有大量系统内存的系统尤其重要。在这些系统上，默认值通常太低。使用以下awk命令将vm.min_free_kbytes设置为建议的系统物理内存的3％：

awk 'BEGIN {OFMT = "%.0f";} /MemTotal/ {print "vm.min_free_kbytes =", $2 * .03;}' /proc/meminfo >> /etc/sysctl.conf

修改系统资源限制

vim /etc/security/limits.conf：

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

在每个段主机上执行ulimit-u命令，以显示每个用户可用的最大进程数。验证返回值是否为131072。

磁盘IO优化

预读值

每个磁盘设备文件的预读（blockdev）值应为16384。要验证磁盘设备的预读值，请执行以下操作：

/sbin/blockdev --getra devname

举例：

/sbin/blockdev --getra /dev/vdb

可以使用命令df -h查看所有的挂载设备。

设置blockdev（预读）

使用以下命令设置预读值：

/sbin/blockdev --setra bytes devname

举例：

/sbin/blockdev --setra 16384 /dev/vdb

注意：blockdev --setra命令不是永久性的，它需要在每次系统重新引导时运行。该命令的运行方式因系统而异，但是必须确保每次系统重新引导时都设置了预读设置。

磁盘 I/O 调度

用于磁盘访问的Linux磁盘I / O调度程序支持不同的策略，例如CFQ，AS和截止日期。

建议使用截止时间调度程序选项。要在下一次系统重新引导之前指定调度程序，请运行以下命令：

$ echo schedulername > /sys/block/devname/queue/scheduler

举例：

$ echo deadline > /sys/block/sbd/queue/scheduler

注意：使用echo命令设置磁盘I / O调度程序策略不是永久性的，因此，必须确保在系统重新引导时运行该命令。该命令的运行方式因系统而异。

SSH连接阈值

某些Greenplum数据库管理实用程序（包括gpexpand，gpinitsystem和gpaddmirror）在系统之间使用安全外壳（SSH）连接来执行其任务。在大型Greenplum数据库部署，云部署或每台主机具有大量分段的部署中，这些实用程序可能会超出主机的未认证连接的最大阈值。发生这种情况时，您会收到以下错误：ssh_exchange_identification：连接被远程主机关闭。

修改文件vim /etc/ssh/sshd_config，在末尾加下如下值：

MaxStartups 200
MaxSessions 200

如果你需要使用start:rate:full参数方式调整，可以使用以下方式修改参数：

MaxStartups 10:30:200
MaxSessions 200

更新MaxStartups和MaxSessions后，重新启动SSH守护程序。例如，在CentOS 6系统上，以root用户身份运行以下命令：

service sshd restart

安装

在每台机器上分别使用用户gpadmin进行安装：

sudo yun install ./greenplum-db-<version>.rpm

然后使用以下命令将/usr/local/gp**目录修改所有者：

sudo chown -R gpadmin:gpadmin /usr/local/greenplum*

配置SSH免密登录

使用gpadmin身份登录服务器
执行gp运行环境初始化
```
 source /usr/local/greenplum-db-<version>/greenplum_path.sh
```
注意：将上述源代码命令添加到gpadmin用户的.bashrc或其他Shell启动文件中，以便每当您以gpadmin登录时，都会设置Greenplum数据库的路径和环境变量。
使用ssh-copy-id命令将gpadmin的公钥添加到集群所有机器中。
先使用命令ssh-keygen -t rsa -b 4096生成密钥。
然后调用以下命令拷贝密钥到指定的服务器。
```
$ ssh-copy-id master
$ ssh-copy-id data1
$ ssh-copy-id data2
$ ssh-copy-id data3
```
在gpadmin主目录中，创建一个名为hostfile_exkeys的文件，该文件为计算机配置了Greenplum系统中每个主机（主机，备用主机和段主机）的主机名和主机地址（接口名称）。确保没有空白行或多余的空格。
```
master
data1
data2
data3
```
使用以下命令以启动免密登录
```
gpssh-exkeys -f hostfile_exkeys
```

创建主机与备机上创建存储区

在主机上创建数据目录位置

主目录上的数据目录位置与段上的数据目录位置不同。主服务器不存储任何用户数据，只有系统目录表和系统元数据存储在主服务器实例上，因此您无需指定与段上一样多的存储空间。

创建或选择一个目录作为您的主数据存储区域。该目录应具有足够的磁盘空间来存储您的数据，并由gpadmin用户和组拥有。例如，以root用户身份运行以下命令：

mkdir /opt/data/gp-master

将此目录所有权更改为gpadmin用户：

sudo chown gpadmin:gpadmin /opt/data/gp-master

使用gpssh，也在备用主数据库上创建主数据目录位置。例如

source /usr/local/greenplum-db/greenplum_path.sh 
gpssh -h data1 -e 'sudo mkdir -p /opt/data/gp-master'
gpssh -h data1 -e 'sudo chown gpadmin:gpadmin /opt/data/gp-master'

gpssh -h data2 -e 'sudo mkdir -p /opt/data/gp-master'
gpssh -h data2 -e 'sudo chown gpadmin:gpadmin /opt/data/gp-master'

gpssh -h data3 -e 'sudo mkdir -p /opt/data/gp-master'
gpssh -h data3 -e 'sudo chown gpadmin:gpadmin /opt/data/gp-master'

在主机上创建数据存储区

在所有主机上创建数据目录位置

在master主机上，使用gpadmin用户：
```
su gpadmin
```
创建一个名为hostfile_gpssh_segonly的文件。对于每个段主机，此文件应只有一台机器配置的主机名。例如，如果您有三个段主机：
```
data1
data2
data3
```
使用gpssh，使用刚刚创建的hostfile_gpssh_segonly文件一次性在所有段主机上创建主和镜像数据目录位置。例如：

source /usr/local/greenplum-db/greenplum_path.sh 
gpssh -f hostfile_gpssh_segonly -e 'sudo mkdir -p /opt/data/gp-data/primary'
gpssh -f hostfile_gpssh_segonly -e 'sudo mkdir -p /opt/data/gp-data/mirror'
gpssh -f hostfile_gpssh_segonly -e 'sudo chown -R gpadmin:gpadmin /opt/data/gp-data/*'

验证硬件和网络性能

Greenplum提供了一个名为gpcheckperf的管理应用工具，可用于识别Greenplum Database阵列中计算机上的硬件和系统级问题。gpcheckperf在指定的主机上启动会话并运行以下性能测试：

Network Performance (gpnetbench*)
Disk I/O Performance (dd test)
Memory Bandwidth (stream test)

验证网络性能

要测试网络性能，请使用以下网络测试运行选项之一运行gpcheckperf：并行对测试（-r N），串行对测试（-r n）或全矩阵测试（-r M）。

该实用程序运行网络基准测试程序，该程序将5秒钟的数据流从当前主机传输到测试中包括的每个远程主机。默认情况下，数据以并行方式传输到每个远程主机，最小，最大，平均和中值网络传输速率以每秒兆字节（MB）报告。如果摘要传输速率比预期的慢（小于100 MB / s），则可以使用-r n选项连续运行网络测试以获得每台主机的结果。

要运行全矩阵带宽测试，可以指定-r M，这将导致每个主机从指定的每个其他主机发送和接收数据。此测试最适合用来验证交换结构是否可以承受全矩阵工作负载。

Greenplum数据库阵列中的大多数系统都配置有多个网络接口卡（NIC），每个NIC都位于其自己的子网中。测试网络性能时，单独测试每个子网很重要。

创建文件hostfile_gpchecknet_ic1：

master
data1
data2
data3

然后，您将每个子网运行一次gpcheckperf。例如（如果要测试偶数个主机，请以并行对测试模式运行）：

$ gpcheckperf -f hostfile_gpchecknet_ic1 -r N -d /tmp > subnet1.out

网络测试结果：

-------------------
--  NETPERF TEST
-------------------

====================
==  RESULT 2019-12-03T14:40:30.737284
====================
Netperf bisection bandwidth test
data1 -> data2 = 11.100000
data3 -> data1 = 10.170000
data2 -> data1 = 10.870000
data1 -> data3 = 11.080000

Summary:
sum = 43.22 MB/sec
min = 10.17 MB/sec
max = 11.10 MB/sec
avg = 10.80 MB/sec
median = 11.08 MB/sec

如果测试失败，请注意是否打开了防火墙。

验证磁盘I / O和内存带宽

使用gpadmin用户登录master
从Greenplum安装中获取greenplum_path.sh路径文件，例：
```
source /usr/local/greenplum-db/greenplum_path.sh
```
创建名为hostfile_gpcheckperf的主机文件，其中每个段host都有一个主机名。不包括主主机。例如：

data1
data2
data3

使用您刚创建的hostfile_gpcheckperf文件运行gpcheckperf实用程序。使用-d选项指定要在每个主机上测试的文件系统（您必须具有对这些目录的写访问权）。您将要测试所有主要和镜像段数据目录位置。例如：

gpcheckperf -f hostfile_gpcheckperf -r ds -D -d /opt/data/gp-data/primary -d /opt/data/gp-data/mirror

该实用程序可能需要一段时间才能执行测试，因为它在主机之间复制了非常大的文件。完成后，您将看到“磁盘写入”，“磁盘读取”和“流”测试的摘要结果。

该测试将在备库中写入16GB左右大小的数据。写完后，再进行读的测试。

测试结果：

====================
==  RESULT 2019-12-03T14:57:38.863950
====================

 disk write avg time (sec): 307.24
 disk write tot bytes: 98429829120
 disk write tot bandwidth (MB/s): 309.69
 disk write min bandwidth (MB/s): 86.99 [data1]
 disk write max bandwidth (MB/s): 111.90 [data3]
 -- per host bandwidth --
    disk write bandwidth (MB/s): 86.99 [data1]
    disk write bandwidth (MB/s): 111.90 [data3]
    disk write bandwidth (MB/s): 110.79 [data2]


 disk read avg time (sec): 291.00
 disk read tot bytes: 98429829120
 disk read tot bandwidth (MB/s): 336.51
 disk read min bandwidth (MB/s): 82.13 [data1]
 disk read max bandwidth (MB/s): 127.54 [data3]
 -- per host bandwidth --
    disk read bandwidth (MB/s): 82.13 [data1]
    disk read bandwidth (MB/s): 127.54 [data3]
    disk read bandwidth (MB/s): 126.84 [data2]


 stream tot bandwidth (MB/s): 63295.60
 stream min bandwidth (MB/s): 21008.30 [data2]
 stream max bandwidth (MB/s): 21231.60 [data1]
 -- per host bandwidth --
    stream bandwidth (MB/s): 21231.60 [data1]
    stream bandwidth (MB/s): 21055.70 [data3]
    stream bandwidth (MB/s): 21008.30 [data2]

初始化Greenplum数据库系统

init_gpdb

创建初始化服务器文件

使用 gpadmin 账号登录
创建文件名为：hostfile_gpinitsystem：
```
data1
data2
data3
```
保存并关闭文件

配置GP数据库

使用 gpadmin 登录

拷贝gpinitsystem_config文件的副本。例如：

cp $GPHOME/docs/cli_help/gpconfigs/gpinitsystem_config /home/gpadmin/gpconfigs/gpinitsystem_config

在文本编辑器中打开您刚刚复制的文件。根据您的环境设置所有必需的参数。
有关更多信息，请参见gpinitsystem。
Greenplum数据库系统必须包含一个主实例和至少两个段实例（即使设置一个单节点系统）。DATA_DIRECTORY参数确定每个主机将创建多少个段。
如果段主机具有多个网络接口，并且在主机文件中使用了它们的接口地址名称，则段数将平均分配到可用接口数上。
要指定PORT_BASE，请查看/etc/sysctl.conf文件中net.ipv4.ip_local_port_range参数中指定的端口范围。这是gpinitsystem_config文件中必需参数的示例：
```
ARRAY_NAME="Greenplum Data Platform"
SEG_PREFIX=gpseg
PORT_BASE=6000 
declare -a DATA_DIRECTORY=(/opt/data/gp-data/primary /opt/data/gp-data/primary /opt/data/gp-data/primary /opt/data/gp-data/primary /opt/data/gp-data/primary /opt/data/gp-data/primary)
MASTER_HOSTNAME=mdw 
MASTER_DIRECTORY=/data/master 
MASTER_PORT=5432 
TRUSTED SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
```
如有需要做镜像，在该文件下方，打开镜像的配置，并设置与Primary同样数量的分片。保存并关闭文件。

配置GP信息参考文档

运行初始化程序

gpinitsystem应用工具将使用配置文件中定义的值创建Greenplum Database系统。

运行初始化程序

运行以下命令，引用初始化配置文件(Gpinitsystem_Config)和主机文件(Hostfile_Gpinitsystem)的路径和文件名。例如：

$ cd ~
$ gpinitsystem -c gpconfigs/gpinitsystem_config -h gpconfigs/hostfile_gpinitsystem

对于完全冗余的系统（具有备用主服务器和扩展镜像配置），请包括-s和-S选项。例如：

gpinitsystem -c gpconfigs/gpinitsystem_config -h gpconfigs/hostfile_gpinitsystem -s standby_master_hostname -S

该实用程序将验证您的设置信息，并确保它可以连接到每个主机并访问配置中指定的数据目录。如果所有预检查均成功，该实用程序将提示您确认配置。例如：

=> Continue with Greenplum creation? Yy/Nn

按y进行初始化。这个过程需要一些时间。

成功时出现提示：

=> Greenplum Database instance successfully created.

配置环境变量

使用 gpadmin 登录 master 主机
```
vim ~/.bashrc
```

在其后添加：

export MASTER_DATA_DIRECTORY=/opt/data/gp-master/gpseg-1/

生效
```
source ~/.bashrc
```

设置时区

# 查看时区是否为 PRC
gpconfig -s TimeZone
gpconfig -c TimeZone -v 'PRC'

更改时区后，您必须重新启动Greenplum数据库。命令gpstop -ra重新启动Greenplum数据库。目录视图pg_timezone_names提供了Greenplum数据库时区信息

配置客户端访问

打开文件$MASTER_DATA_DIRECTORY/pg_hba.conf

为您要允许的每种连接类型在文件中添加一行。记录是顺序读取的，因此记录的顺序很重要。通常，较早的记录将具有紧密的连接匹配参数和较弱的身份验证方法，而较新的记录将具有较宽松的匹配参数和较强的身份验证方法。例如：

# allow the gpadmin user local access to all databases
# using ident authentication
local   all   gpadmin   ident         sameuser
host    all   gpadmin   127.0.0.1/32  ident
host    all   gpadmin   ::1/128       ident
# allow the 'dba' role access to any database from any
# host with IP address 192.168.x.x and use md5 encrypted
# passwords to authenticate the user
# Note that to use SHA-256 encryption, replace md5 with
# password in the line below
host    all   dba   192.168.0.0/32  md5
# allow all roles access to any database from any
# host and use ldap to authenticate the user. Greenplum role
# names must match the LDAP common name.
host    all   all   192.168.0.0/32  ldap ldapserver=usldap1 ldapport=1389 ldapprefix="cn=" ldapsuffix=",ou=People,dc=company,dc=com"

# 表示218网段所有机器都是可信的，不需要密码访问
host    all   gpadmin    192.168.218.0/24 trust
# 表示218网段所有机器都可以通过密码访问
host    all   gpadmin    192.168.220.0/24 md5

保存并退出文件
使用命令gpstop -u使配置生效。

保护并限制对gpadmin系统用户的访问。Greenplum需要UNIX用户ID才能安装和初始化Greenplum数据库系统。该系统用户在Greenplum文档中称为gpadmin。
gpadmin用户是Greenplum数据库中的默认数据库超级用户，也是Greenplum安装及其基础数据文件的文件系统所有者。
默认的管理员帐户是Greenplum数据库设计的基础。没有它，系统将无法运行，并且无法限制gpadmin用户ID的访问。
gpadmin用户可以绕过Greenplum数据库的所有安全功能。
使用此用户ID登录到Greenplum主机的任何人都可以读取，更改或删除任何数据，包括系统目录数据和数据库访问权限。
因此，保护gpadmin用户ID并仅允许基本系统管理员对其进行访问非常重要。管理员仅应在执行某些系统维护任务（例如升级或扩展）时以gpadmin身份登录Greenplum。
数据库用户永远不要以gpadmin身份登录，而ETL或生产工作负载绝对不应以gpadmin身份运行。

使用

用户操作

以下操作均在psql中使用。使用gpadmin账号登录主机后：

psql postgres

查看所有用户

使用命令\du，列举所有用户。

创建用户

create user warehouse with password 'warehouse'

删除用户

DROP USER user1;

修改用户密码

ALTER USER user_name WITH PASSWORD 'new_password';

创建数据库

创建数据库并指向用户

create database warehouse owner warehouse;

将数据库的所有权限都赋予给用户：

GRANT ALL PRIVILEGES ON DATABASE warehouse TO warehouse;

配置用户访问权限：

vim /opt/data/gp-master/gpseg-1/pg_hba.conf
或者
sudo vim $MASTER_DATA_DIRECTORY/pg_hba.conf
#在最后一行新增：

host     all         warehouse       0.0.0.0/0 md5

使用命令gpstop -u使之生效。

连接

使用命令psql连接

去下载处下载 Windows 客户端并安装。
然后使用如下命令即可连接：

psql -h 192.168.218.250 -p 2345 -U gpadmin -d beacon

如果提示 psql: FATAL: no pg_hba.conf entry for host "192.168.218.105", user "pgadmin", database "beacon"，则表示需要修改pg_hba.conf。在配置文件一般存在于master节点。
vim /data/greenplum-db/gpdata/gpmaster/gpseg-1/pg_hba.conf
添加如下信息
host all gpadmin 0.0.0.0/0 md5
接着使用gpstop -u重载配置即可
注意：如果gpstop命令未找到，则需要使用source /opt/greenplum-db/greenplum_path.sh进行环境变量的写入

使用jdbc连接

[文档]

属性	描述
驱动文件名称	greenplum.jar
数据源类名	com.pivotal.jdbc.GreenplumDriver
连接地址	jdbc:pivotal:greenplum://host:port;DatabaseName=
驱动默认配置	见下方

ResultSetMetadataOptions=1
FetchTWFSasTime=true
SupportsCatalogs=true
MaxLongVarcharSize=8190
MaxNumericPrecision=28
MaxNumericScale=6

与spark的连接

下载greenplum-spark_2.11-1.6.1.jar文件在此处。查看具体的示例在此处。

定义连接配置

配置文档

val gscWriteMap = Map(
      "url" -> "jdbc:postgresql://gpmaster.domain/tutorial",
      "user" -> "user2",
      "password" -> "pivotal",
      "dbschema" -> "faa",
      "dbtable" -> "avgdelay",
      "pool.maxSize" -> "50",
      "pool.minIdle" -> "5",
      "pool.timeoutMs" -> "7500",
      "server.port" - > "12900",
      "server.nic" -> "eth1"
)

从greenplum读取表

val gpdf = spark.read.format("greenplum").options(gscOptionMap).load()

从Spark写入数据到greenplum

delaydf.write.format("greenplum").options(gscWriteMap).save()

定义数据存储对象

表空间

创建和管理表空间

表空间允许数据库管理员每台计算机拥有多个文件系统，并决定如何最好地使用物理存储来存储数据库对象。

表空间允许您为频繁使用和不经常使用的数据库对象分配不同的存储，或控制某些数据库对象的 I/O性能。例如，将常用表放在使用高性能固态驱动器（SSD）的文件系统上，并将其他表放在标准硬盘驱动器上。

表空间需要主机文件系统位置来存储其数据库文件。在Greenplum数据库中，文件系统位置必须存在于所有主机上，包括运行主服务器，备用主服务器，每个主要段和每个镜像段的主机。表空间是Greenplum数据库系统对象（全局对象），如果您具有适当的特权，则可以使用任何数据库中的表空间。

创建表空间

使用CREATE TABLESPACE命令，来创建一个表空间：

CREATE TABLESPACE fastspace LOCATION '/fastdisk/gpdb';

数据库超级用户定义表空间，并使用GRANTCREATE命令向数据库用户授予访问权限。例如：

GRANT CREATE ON TABLESPACE fastspace TO admin;

使用表空间存储数据库对象

对表空间具有CREATE权限的用户可以在该表空间中创建数据库对象，如表、索引和数据库。命令为：

CREATE TABLE tablename(options) TABLESPACE spacename

例如，以下命令在表空间space1中创建一个表：

CREATE TABLE foo(i int) TABLESPACE space1;

也可以使用DEFAULT_TABLESPACE参数为不指定表空间的CREATE TABLE和CREATE INDEX命令指定默认表空间：

SET default_tablespace = space1;
CREATE TABLE foo(i int);

查看表空间信息

SELECT oid, * FROM pg_tablespace;

  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16391 | myspace    |       10 |        |
(3 rows)

表空间myspace的OID为16391。运行gp_tablespace_location()以显示由两个段实例和主服务器组成的系统的表空间位置。

SELECT * FROM gp_tablespace_location(16391);

使用以下命令可以同时展示表空间的位置及服务器的信息：

WITH spc AS (SELECT * FROM  gp_tablespace_location(1663))
  SELECT seg.role, spc.gp_segment_id as seg_id, seg.hostname, seg.datadir, tblspc_loc 
    FROM spc, gp_segment_configuration AS seg 
    WHERE spc.gp_segment_id = seg.content ORDER BY seg_id;

删除表空间

要删除表空间，您必须是表空间所有者或超级用户。在删除使用表空间的所有数据库中的所有对象之前，不能删除表空间。

使用命令DROP TABLESPACE删除表空间。

创建和管理模式

模式在逻辑上组织数据库中的对象和数据。架构允许您在数据库中拥有多个具有相同名称的对象（例如表），如果对象位于不同架构中，则不会发生冲突。

默认的`public`模式

每个数据库都有一个名为public的默认模式。如果不创建任何模式，则将在公共架构中创建对象。所有数据库角色（用户）在公共模式中都具有CREATE和USAGE特权。创建架构时，您向用户授予特权以允许访问该模式。

创建模式

CREATE SCHEMA myschema;

要在架构中创建或访问对象，请写一个限定名称，该名称由架构名称和表名称组成，并用句点分隔。例如：

myschema.table

您可以创建其他人拥有的架构，例如，将用户的活动限制为定义明确的名称空间。语法为：

 CREATE SCHEMA schemaname AUTHORIZATION username;

查看当前模式

SELECT current_schema();

删除模式

使用DROP SCHEMA命令删除模式。例如：

 DROP SCHEMA myschema

默认情况下，架构必须为空，然后才能将其删除。要删除模式及其所有对象（表，数据，函数等），请使用：

 DROP SCHEMA myschema CASCADE;

创建表

创建ID自增表

参考文档：
序列参考 ddl sequence

创建和使用序列

创建一个名为myserial的序列，并且以101开始：

CREATE SEQUENCE myserial START 101;

检查序列

检查名为myserial的序列：

select * from myserial;

查询所有序列：

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

返回下一个序列计数

调用nextval()内置函数以返回并使用序列中的下一个值。

以下命令将名为myserial的序列的下一个值插入名为vendor的表的第一列：

INSERT INTO vendors VALUES (nextval('myserial'), 'acme');

设置计数器序列

调用setval()内置函数以设置myserial的序列值：

SELECT setval('myserial', 201);

修改序列属性

调用以下命令，使序列以105重新开始：

ALTER SEQUENCE myserial RESTART WITH 105;

删除序列

DROP SEQUENCE myserial;

将序列指定为一个列的默认值

创建表时指定：

CREATE TABLE tablename ( id INT4 DEFAULT nextval('myserial'), name text );

或者修改表的属性：

ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('myserial');

分布键

由于Greenplum是一个分布式的数据库，数据是分散存储在各个数据节点的，所以需要告诉Greenplum数据应该如何分布。默认情况下，Greenplum将使用第一个字段作为分布键。

用户可以指定分布列(允许指定多个列) ，或者使用随机分布算法。

分布键的设置原则：

分布列尽量选择需要经常JOIN的列，这类查询的并发越高，越应该考虑。
尽量选择分布均匀的列，或者多列
尽量选择高并发查询的条件列（指该查询条件产生的中间结果集小的，如果中间结果集很大，那就让所有节点都来参与运算更好，因此不选），如果有多个条件，请先权衡前面的法则
不要轻易使用随机分布

查看分布键

命令查看：

\d tablename

sql 查看表st_user_action的分布键：

select attname from pg_attribute where attrelid='st_user_action'::regclass and attnum in (SELECT unnest(distkey) FROM pg_catalog.gp_distribution_policy t where localoid='st_user_action'::regclass);

# result
#attname
#------
# amount

创建表时指定分布键

CREATE TABLE "public"."test" (
  "amount" int8,
    "id" int8,
  "business_cate" varchar(100) COLLATE "pg_catalog"."default"
) DISTRIBUTED BY (id);

修改分布键

修改表st_user_action的分布键：

ALTER TABLE st_user_action SET DISTRIBUTED BY (id)

基于JSON格式创建表

create or replace function create_table_from_json(json text, tablename text)
returns void language plpgsql
as $$begin
  execute
    replace(
      replace(
        regexp_replace(
          json,
          '("[^"]*"):("[^"]*")',
          '    \1 text', 'g'),
        '{', 
        format('create table %s (', tablename)),
      '}',
      ');');end 
$$;

create or replace function insert_from_json(json text, tablename text)
returns void language plpgsql
as $$begin
  execute
    replace(
      replace(
        regexp_replace(
          json,
          '("[^"]*"):"([^"]*)"',
          '''\2''', 'g'),
        '{', 
        format('insert into %s values (', tablename)),
      '}',
      ');');end 
$$;

调用：

create_table_from_json('jsonString', 'tableName')

一定要注意参数必须用单引号'包含。且所有字段值必须使用双引号包含。该创建方法只适合创建全字符串类型字段的表。

参考

表分区

分区的目的是尽可能的缩小QUERY需要扫描的数据量，因此必须和查询条件相关联。

分区法则：

尽量选择和查询条件相关的字段，缩小QUERY需要扫描的数据
当有多个查询条件时，可以使用子分区，进一步缩小需要扫描的数据

数据倾斜

监控数据倾斜脚本：

select gp_execution_dbid(), datname, pg_size_pretty(pg_database_size(datname)) from gp_dist_random('pg_database') order by 2,1,pg_database_size(datname) desc;

监控表级倾斜：

select gp_execution_dbid(), pg_size_pretty(pg_total_relation_size('表名')) from gp_dist_random('gp_id') ;

查询当前表类型

查询当前存储结构类型

select distinct relstorage from pg_class
 relstorage
\------------    
 a  -- 行存储AO表    
 h  -- heap堆表、索引    
 x  -- 外部表(external table)    
 v  -- 视图    
 c  -- 列存储AO表    
(5 rows)

查询当前AO表：

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a');

查询当前堆表：

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';

表类型

默认情况下，Greenplum数据库使用的是堆(Heap)存储类型。堆表存储在OLTP类型负载下一下最好。这种环境中数据会在初始化载入后被频繁地修改。

AO表

Append Only表：适合批量写入、含少量DML。因为AO表不写WAL日志，所以为了保证可靠性，必须在一个事务结束后，fsync数据块，并记录文件末端位点，并且最后一个BLOCK即使没有写满，下次也不能再写。如果单条写入，会导致IO放大很严重。因此AO表适合的是批量写入。堆表写WAL日志，不存在这个问题。

行表转AO列表，并指定对应的分布键：

CREATE TABLE app_huawei_apk_flow2 (LIKE app_huawei_apk_flow) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (data_source, package_name, tag_name);

行存

查询较多字段、输出较多记录。

列存

统计、JOIN、少量列查询。

heap row table

单步写入、含部分DML

Greenplum insert的性能(单步\批量\copy) - 暨推荐使用gpfdist、阿里云oss外部表并行导入
 Greenplum 行存、列存，堆表、AO表性能对比 - 阿里云HDB for PostgreSQL最佳实践
 Greenplum 行存、列存，堆表、AO表的原理和选择
 Greenplum 最佳实践 - 行存与列存的选择以及转换方法

压缩表

CREATE TABLE "public"."st_user_info"
(
    "id"                 int8 NOT NULL DEFAULT nextval('user_info_serial'::regclass),
    "source" jsonb
) with (ORIENTATION=column, appendoptimized=true, compresstype=zlib, compresslevel=5) DISTRIBUTED BY (id)

参考文档：
ddl-storage

roaringbitmap位图算法

roaring bitmap是一个压缩比很高同时性能不错的BIT库，被广泛使用（例如Greenplum, ES, InfluxDB......）。

参考链接：
Greenplum roaring bitmap与业务场景 (类阿里云RDS PG varbitx, 应用于海量用户实时画像和圈选、透视)
Greenplum 结合 Roaringbitmap 实现亿级会员十万级标签毫秒级查询
gpdb-roaringbitmap
惊天性能！单RDS PostgreSQL实例支撑 2000亿 - 实时标签透视案例 (含dblink异步并行调用)

roaringbitmap 安装

yum install git
git clone https://github.com/zeromax007/gpdb-roaringbitmap  

su gpadmin
make
make install

# 将插件拷贝至GP目录
scp ./roaringbitmap.so data1:/usr/local/greenplum-db-6.9.0/lib/postgresql/
scp ./roaringbitmap.so data2:/usr/local/greenplum-db-6.9.0/lib/postgresql/
scp ./roaringbitmap.so data3:/usr/local/greenplum-db-6.9.0/lib/postgresql/

scp ./roaringbitmap.control data1:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/
scp ./roaringbitmap.control data2:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/
scp ./roaringbitmap.control data3:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/

scp ./roaringbitmap--*.sql data1:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/
scp ./roaringbitmap--*.sql data2:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/
scp ./roaringbitmap--*.sql data3:/usr/local/greenplum-db-6.9.0/share/postgresql/extension/

# 载入插件
psql warehouse -c "create extension roaringbitmap;"
# 卸载插件（注意，卸载后，也会删除所有文件）
psql warehouse -c "DROP EXTENSION IF EXISTS roaringbitmap CASCADE;"
# 再次重载时
make install
scp ...（上面拷贝代码）
载入插件代码

附录：
ALTER EXTENSION

Kafak Connector

文档

当您使用Greenplum-Kafka集成将Kafka数据加载到Greenplum数据库表中时，将执行以下任务，确保您满足先决条件：

按照[Pivotal Greenplum Stream Server文档]中所述注册Pivotal Greenplum Stream Server（GPSS）扩展。
- CREATE EXTENSION gpss
确定Kafka数据的格式。
构造负载配置文件。
创建目标Greenplum数据库表。
根据需要，将Greenplum数据库角色权限分配给表，如Pivotal Greenplum Stream Server文档中所述。
运行gpkafka load命令将Kafka数据加载到Greenplum数据库中。

支持的消息格式

格式	描述
avro	gpkafka支持来自Kafka的libz，lzma和snappy压缩的Avro数据。
binary	二进制格式的数据。 gpkafka仅作为单个bytea类型的列从Kafka读取二进制数据。
csv	逗号分隔的文本格式数据。
custom	自定义格式的数据，由自定义格式器解析。
delimited	分隔符
json	gpkafka仅作为单列从Kafka读取JSON数据

构造gpkafka配置文件

您可以通过YAML格式的配置文件配置从Kafka到Greenplum数据库的数据加载操作。此配置文件包含用于标识源Kafka数据以及有关Greenplum数据库连接和目标表的信息以及该操作的错误和提交阈值的参数。

loadcfg2.yaml：

配置详情

DATABASE: warehouse
USER: warehouse
PASSWORD: warehouse
HOST: 192.168.218.87
PORT: 5432
VERSION: 2
KAFKA:
   INPUT:
      SOURCE:
         BROKERS: 192.168.220.49:9092
         TOPIC: flink-user-action
      VALUE:
         COLUMNS:
           - NAME: c1
             TYPE: json
         FORMAT: json
      KEY:
         COLUMNS:
           - NAME: key
             TYPE: json
         FORMAT: json
      FILTER: (c1->>'month')::int = 11 
      ERROR_LIMIT: 25
   OUTPUT:
      SCHEMA: payables
      TABLE: st_user_action
      MAPPING:
        - NAME: customer_id
          EXPRESSION: (c1->>'cust_id')::int
        - NAME: newcust
          EXPRESSION: ((c1->>'cust_id')::int > 5000000)::boolean
        - NAME: expenses
          EXPRESSION: (c1->>'expenses')::decimal
        - NAME: tax_due
          EXPRESSION: ((c1->>'expenses')::decimal * .075)::decimal
   METADATA:
      SCHEMA: gpkafka_internal
   COMMIT:
      MAX_ROW: 1000
      MINIMAL_INTERVAL: 30000
   POLL:
      BATCHSIZE: 100
      TIMEOUT: 3000

运行gpkafka

您运行gpkafka load命令将Kafka数据加载到Greenplum。运行命令时，请提供定义加载操作参数的配置文件的名称。
例如：

$ gpkafka load loadcfg2.yaml

gpkafka加载的默认操作模式是读取所有未决消息，然后等待，然后使用新的Kafka消息。在此模式下运行时，gpkafka加载会无限期等待；您可以使用Control-c中断并退出命令。

要以批处理方式运行该命令，请提供--quit-at-eof选项。在这种模式下，当Kafka流中没有新消息时，gpkafka加载将退出。

命令参考

检查进度

使用以下命令检查进度：

gpkafka check loadcfg2.yaml

输出内容展示：

PartitionID    StartTime    EndTime    BeginOffset    EndOffset
0    2018-07-13T16:19:11Z    2018-07-13T16:19:11Z    0    9

当您运行不带任何选项的gpkafka check时，它将显示最新的提交。要查看加载操作的完整提交历史记录，请运行带有--show-commit-history all参数的命令。

Greenplum 注册流服务

只有注册了流服务，gpkafka才会起到作用。否则会报一些函数读取不到的错误。

使用gpadmin登录master：

psql warehouse

执行：

CREATE EXTENSION gpss

权限注册：

GRANT USAGE, CREATE ON SCHEMA schema_name TO role_name;

参考

启动流服务器

使用gpadmin登录master，创建文件gpsscfg1.json：

{
    "ListenAddress": {
        "Host": "",
        "Port": 5000
    },
    "Gpfdist": {
        "Host": "",
        "Port": 8319,
        "BindAddress": ""
    }
}

使用以上命令创建流服务：

gpss gpsscfg1.json --log-dir ./gpsslogs/ &

注意事项：
GpfDist的BindAddress必须设置的空值，如果设置为127.0.0.1，将导致连接拒绝的错误

流客户端

常用命令参数列表

使用参数gpss-port以指定端口号。

使用参数gpss-host以指定服务器。

提交一个任务

gpsscli submit --name nightly_order_upload loadcfg.yaml

提供的任务处于停止状态，此时需要启动这个任务。

gpsscli start nightly_order_upload

检查作业状态，进度，历史记录

命令gpsscli list列出正在运行或所有业务：

$ gpsscli list --all
JobID                   GPHost       GPPort  DataBase    Schema     Table                    Topic         Status  
monday_summary          sys1          5432   testdb      public     monday_totals            totals        Stopped 
nightly_order_upload    sys1          5432   testdb      public     night_sync               orders        Running

命令gpsscli status显示指定任务的状态

$ gpsscli status nightly_order_upload
... -[INFO]:-Job monday_summary, status Running, errmsg [], time 2019-10-15T21:56:47.766397Z

命令gpsscli process显示任务的进度

$ gpsscli progress nightly_order_upload --partition

2019-10-15T21:56:54.80469Z 2019-10-15T21:56:54.830441Z 242000 243000 81033
2019-10-15T21:56:54.846354Z 2019-10-15T21:56:54.880517Z 243000 244000 81021
2019-10-15T21:56:54.893097Z 2019-10-15T21:56:54.904745Z 244000

命令gpsscli history显示所有历史记录

$ gpsscli history --show-commit-history all loadcfg.yaml

2019-10-15T15:58:25.707977Z 2019-10-15T15:58:26.769737Z 15224 16224
2019-10-15T15:58:26.813426Z 2019-10-15T15:58:26.824484Z 16224 17224
2019-10-15T15:58:26.832965Z 2019-10-15T15:58:26.843036Z 17224

停止与删除任务

gpsscli stop nightly_order_upload
gpsscli remove nightly_order_upload

指定任务开始的偏移量

强制从最早的数据开始：

# gpkafka
gpkafka load --force-reset-earliest loadcfg2.yaml
#gpsscli
gpsscli start user_info --force-reset-earliest

强制从最新的数据时间：

# gpkafka
gpkafka load --force-reset-latest loadcfg2.yaml
#gpsscli
gpsscli start user_info --force-reset-latest

从指定的时间戳开始

# gpkafka
gpkafka load --force-reset-timestamp 1571066212000 loadcfg2.yaml
#gpsscli
gpsscli start user_info --force-reset-timestamp 1571066212000

如果不知道启动的时间，可以在gpkafka_xxxx里面查找最新的时间，换算成时间戳即可。

参考文档

导入与导出

文件导入

\copy phone(phone,csv) from 'D:/xxx.csv' WITH csv;

CopyManager

使用copy命令，可以以非常快的速度导入文件或内存中的数据。

public class CopyInTest {
    @Autowired 
    private DataSource dataSource;

    @Test
    public void testCopyIn() throws SQLException, IOException {
        PgConnection connection = dataSource.getConnection().unwrap(PgConnection.class);
        CopyManager copyManager = new CopyManager(connection);   
        StringReader sr = new StringReader("2019-04-17 00:00:00,2019-04-17 11:00:00\n2019-04-17 00:01:00,2019-04-17 12:00:00");   
        try {        
            long count = copyManager.copyIn("COPY test_copy_in (create_time, update_time) FROM STDIN WITH CSV", sr);        
            print("handle success: " + count);   
        } catch (IOException e) {
            e.printStackTrace();   
        }
    }
}

命令主要用法：

COPY [table_name] [column_name] FROM STDIN WITH CSV
-- 或者
COPY [table_name] [column_name] FROM STDIN WITH DELIMITER ','
-- 如果需要针对 null 值字符串定义，则需要如下命令：
COPY [table_name] ([column_name]) DELIMITER '`' NULL 'null' LOG ERRORS SEGMENT REJECT LIMIT 100

其中需要注意的是，对于导入的内容，如果需要处理自增长ID，则需要将该表的ID属性的默认值设为nextval('hibernate_sequence')。此处示例使用的是 JPA 自带的序列。可以自定义一个序列。

使用LOG ERRORS时，插入失败的数据将自动记录到GP数据中。查询时使用命令：

SELECT * from gp_read_error_log('表名');

即可查到所有的失败例子。

使用SEGMENT REJECT LIMIT 100时，表示如果失败100次，则本次插入将全部失败。

-- 自定义序列
CREATE SEQUENCE myserial START 1;
-- 设置序列
select setval('myserial', 10000000)
-- 查看所有序列 
SELECT * FROM hibernate_sequence;

引用
这一篇讲的是各种导入方式的性能影响 Fast Inserts to PostgreSQL with JDBC and COPY FROM
在 JDBC 代码中使用 COPY 命令 Postgres and Postgres Plus tips and tricks
Postgres COPY 命令参考： COPY
关于Copy ERROR LOG 相关说明

错误信息解释

extra data after last expected column：

需要查看一下CSV的分隔符，是否与数据存在重合的地方。

导出

Copy命令

导出为CSV格式

-- 导出CSV格式
COPY table_name TO '/path/to/output.csv' WITH csv;

在windows下则需要使用：

\copy app_huawei_apk to 'F:/test/app_huawei_apk_2019_05_15_16_20.csv' WITH csv;

使用查询导出

\copy (select * from app_huawei_apk) to 'F:/test/app_huawei_apk_2019_05_15_16_20.csv' WITH HEADER DELIMITER '`';

如果出现错误提示为：
character 0xe280a2 of encoding "UTF8" has no equivalent in "GBK"
则使用命令：\encoding utf8
进行编码切换

自定义CSV格式导出

\COPY table_name TO '/path/to/output.csv' WITH HEADER DELIMITER '`'

导出为JSON格式

\COPY (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM foo) t) TO '/path/to/output.json';

分区表

创建分区

-- 创建临时数据表
create table app_huawei_apk_temp as select * from app_huawei_apk_flow DISTRIBUTED BY (id);

-- 创建分区表
create table app_huawei_apk_flow ( like app_huawei_apk_temp )
partition by range (create_time)
(start (date '2019-03-01') inclusive
END (date '2020-03-01') EXCLUSIVE 
every (interval '1 month'), default partition other_dates);

-- 向分区表插入数据
insert into app_huawei_apk_flow select * from app_huawei_apk_temp;

引用
[对大型表分区](
https://gp-docs-cn.github.io/docs/admin_guide/ddl/ddl-partition.html#topic81)

查看分区设计

SELECT partitionboundary, partitiontablename, partitionname, 
partitionlevel, partitionrank 
FROM pg_partitions 
WHERE tablename='sales';

修改分区名称

ALTER TABLE sales RENAME PARTITION FOR ('2016-01-01') TO jan16;

分割分区

如果分区设计具有默认分区，则必须拆分默认分区才能添加分区。

举例：

例如，要将每月分区一分为二，第一个分区包含日期1月1-15日，第二个分区包含日期1月16-31：

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

分割默认分区：

ALTER TABLE sales SPLIT DEFAULT PARTITION 
START ('2019-12-17') INCLUSIVE 
END ('2019-12-18') EXCLUSIVE 
INTO (PARTITION, default partition);

性能

模式	吞吐	tps	rows/s
单条INSERT	10 MB/s	12973	1.3 万
批量INSERT	16 MB/s	207	2.07 万
批量COPY	62 MB/s	770	7.7 万

通过扩展segment个数，可以提升INSERT性能。

线性，可到达MASTER瓶颈 (例如10GB网卡，1.25GB/s 吞吐)

索引

「GP索引文档 | Postgresql索引文档」

Bitmap索引

位图索引最适合用户查询数据而不是更新数据的数据仓库应用程序。

对于具有100到100，000个不同值的列，以及经常将索引列与其他索引列一起查询时，位图索引的性能最佳。具有少于100个不同值的列，例如具有两个不同值(男性和女性)的性别列，通常不会从任何类型的索引中获益很多。
在具有超过100，000个不同值的列上，位图索引的性能和空间效率会下降。位图索引可以提高即席查询的查询性能。在将生成的位图转换为元组ID之前，通过直接对位图执行相应的布尔操作，可以快速解析查询的WHERE子句中的AND和OR条件。如果生成的行数很少，则可以快速回答查询，而无需求助于全表扫描。

CREATE INDEX title_bmp_idx ON films USING bitmap (title);

B-Tree索引

B-树可以处理对可以按某种顺序排序的数据的相等和范围查询。特别是，每当使用以下运算符之一进行比较时，PostgreSQL查询规划器都会考虑使用B树索引：“<、<=、=、>=、>”

等同于这些运算符组合的构造（例如BETWEEN和IN）也可以使用B树索引搜索来实现。而且，索引列上的IS NULL或IS NOT NULL条件可以与B树索引一起使用。优化器还可以使用B树索引进行涉及模式匹配运算符LIKE和〜的查询，如果模式是一个常量并且锚定在字符串的开头，例如col LIKE 'foo％'或col 〜'^ foo”，但不像“％bar”一样。

B树索引还可用于按排序顺序检索数据。这并不总是比简单的扫描和排序更快，但通常是有帮助的。

CREATE INDEX name ON table USING btree (column);

哈希索引

哈希索引只能处理简单的相等比较。每当使用=运算符进行比较时，查询计划者将考虑使用哈希索引。以下命令用于创建哈希索引：

CREATE INDEX name ON table USING hash (column);

警告：
希索引操作目前尚未进行WAL记录，因此如果发生未写入的更改，则在数据库崩溃后可能需要使用REINDEX重建哈希索引。同样，哈希索引的更改不会在初始基本备份后通过流式复制或基于文件的复制进行复制，因此它们为随后使用它们的查询提供了错误的答案。由于这些原因，目前不鼓励使用哈希索引。

GiST索引

GiST索引不是单一的索引，而是可以在其中实施许多不同索引策略的基础结构。因此，可以与GiST索引一起使用的特定运算符取决于索引策略（运算符类）。例如，PostgreSQL的标准发行版包括针对几种二维几何数据类型的GiST运算符类，它们支持使用这些运算符的索引查询：“<<、<&、&>、>>、<<|、&<|、|&>、|>>、@>、<@、~=、&&”。

CREATE INDEX "idx_st_user_action_date" ON "public"."st_user_action" USING gist (
  "date"
);

GiST索引还能够优化“最近邻居”搜索，例如：

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

维护

日常维护

查看集群情况

gpstate 命令参考

查看镜像实例的状态及配置信息

使用以下命令查看服务器集群情况：

gpstate -m

返回结果示例：

xxx:gpadmin-[INFO]:--------------------------------------------------------------
xxx:gpadmin-[INFO]:--Current GPDB mirror list and status
xxx:gpadmin-[INFO]:--Type = Group
xxx:gpadmin-[INFO]:--------------------------------------------------------------
xxx:gpadmin-[INFO]:-   Mirror   Datadir                            Port   Status              Data Status    
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg0    7000   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg1    7001   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg2    7002   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg3    7003   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg4    7004   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data2    /opt/data/gp-data/mirror/gpseg5    7005   Acting as Primary   Not In Sync
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg6    7000   Passive             Synchronized
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg7    7001   Passive             Synchronized
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg8    7002   Passive             Synchronized
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg9    7003   Passive             Synchronized
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg10   7004   Passive             Synchronized
xxx:gpadmin-[INFO]:-   data3    /opt/data/gp-data/mirror/gpseg11   7005   Passive             Synchronized
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg12   7000   Failed                             <<<<<<<<
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg13   7001   Failed                             <<<<<<<<
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg14   7002   Failed                             <<<<<<<<
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg15   7003   Failed                             <<<<<<<<
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg16   7004   Failed                             <<<<<<<<
xxx:gpadmin-[WARNING]:-data1    /opt/data/gp-data/mirror/gpseg17   7005   Failed                             <<<<<<<<
xxx:gpadmin-[INFO]:--------------------------------------------------------------
xxx:gpadmin-[WARNING]:-6 segment(s) configured as mirror(s) are acting as primaries
xxx:gpadmin-[WARNING]:-6 segment(s) configured as mirror(s) have failed
xxx:gpadmin-[WARNING]:-6 mirror segment(s) acting as primaries are not synchronized

可以看到有一台机器挂了。data1机器状态为失败。

查看从与镜像的对应关系

gpstate -c

查看standby master信息

gpstate -f

查看集群综合信息

gpstate -Q

结果展示：

gpadmin-[INFO]:--Quick Greenplum database status from Master instance only
gpadmin-[INFO]:-----------------------------------------------------------
gpadmin-[INFO]:-# of up segments, from configuration table     = 24
gpadmin-[INFO]:-# of down segments, from configuration table   = 12
gpadmin-[INFO]:-   Down Segment   Datadir                            Port
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg0   6000
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg1   6001
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg2   6002
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg3   6003
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg4   6004
gpadmin-[INFO]:-   data1          /opt/data/gp-data/primary/gpseg5   6005
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg12   7000
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg13   7001
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg14   7002
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg15   7003
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg16   7004
gpadmin-[INFO]:-   data1          /opt/data/gp-data/mirror/gpseg17   7005
gpadmin-[INFO]:-----------------------------------------------------------

该内容显示了其中一台机器挂了。

启动集群

gpstart

参数：

-a：直接启动，不需要用户确定
-m：只启动master实例。

停止集群

gpstop

-a：直接停止，不需要用户确认
-m：只停止master实例
-u：不停止数据库，只加载pg_hba.conf 和postgresql.conf中运行时参数，当改动参数配置时候使用。

进程处理

查看正在处理的进程：

SELECT pid, datname, usename, client_addr, client_port, query_start, query FROM pg_stat_activity;

关闭对应进程：

SELECT pg_cancel_backend(<pid of the process>)

如果关闭不成功，可以如下语句：

SELECT pg_terminate_backend(<pid of the process>)

参考：
How to stop/kill a query in postgresql?

配置修改(gpconfig)

使用gpconfig在Greenplum数据库系统中所有的Segment上设置服务器配置参数。

概要

gpconfig -c param_name -v value [-m master_value | --masteronly]
       | -r param_name [--masteronly | -l
       [--skipvalidation] [--verbose] [--debug]

gpconfig -s param_name [--file | --file-compare] [--verbose] [--debug]

gpconfig --help

主要选项

-c 通过在postgresql.conf 文件的底部添加新的设置来改变配置参数的设置。
-v 用于由-c选项指定的配置参数的值。默认情况下，此值将应用于所有Segment及其镜像、Master和后备Master。
-m 用于由-c选项指定的配置参数的Master值。如果指定，则该值仅适用于Master和后备Master。该选项只能与-v一起使用。
--masteronly 当被指定时，gpconfig 将仅编辑Master的postgresql.conf文件。
-r 通过注释掉postgresql.conf文件中的项删除配置参数。
-l 列出所有被gpconfig工具支持的配置参数
-s 显示在Greenplum数据库系统中所有实例（Master和Segment）上使用的配置参数的值。如果实例中参数值存在差异，则工具将显示错误消息。使用-s选项运行gpconfig将直接从数据库中读取参数值，而不是从postgresql.conf文件中读取。如果用户使用gpconfig 在所有Segment中设置配置参数，然后运行gpconfig -s来验证更改，用户仍可能会看到以前的（旧）值。用户必须重新加载配置文件（gpstop -u）或重新启动系统（gpstop -r）以使更改生效。
--verbose 在gpconfig命令执行期间显示额外的日志信息。

gpconfig 示例

仅设置master参数

gpconfig -c gp_snmp_community -v testenv --masteronly

master与segment分开设置

设置所有Segment上的 max_connections 为100，而Master上为10。

gpconfig -c max_connections -v 100 -m 10

设置服务器配置参数gp_email_to和gp_email_from。这些参数需要用单引号把值括起来。在为参数指定值时，请将值用双引号（"）封闭。

$ gpconfig -c gp_email_from -v "'gpdb-server@example.com'"
$ gpconfig -c gp_email_to -v "'gpdb-admin@example.com'"

显示一个参数

gpconfig -s max_connections

异常处理

查看日志

使用以下命令查看错误信息：

gplogfilter -t

一次性查看全部机器的日志：

vim seg_host_file
# 输入文件内容：
data1
data2
data3

# 使用命令一次性获取全部日志
gpssh -f seg_host_file
=> source /usr/local/greenplum-db/greenplum_path.sh
=> gplogfilter -n 3 /opt/data/gp-data/*/*/pg_log/gpdb*.csv

文档参考：gplogfilter

实例恢复

恢复所有失效的分片

$ gprecoverseg

在使用gpstate看到有机器挂了之后，可以使用该命令进行恢复。

gpstate -e：查看恢复进度
- -s：恢复状态
- -m：查看mirror节点的状态

恢复命令参考

将所有分片恢复为原来的角色

gpstate -e命令，能够检查出segment是否是原来的角色

gprecoverseg -r

数据库升级

参考文档：Upgrading from an Earlier Greenplum 6 Release

graph TD
下载新版本-->A(上传到各服务器)
A-->B(运行gpcheckcat)
B-->C(停止数据库 gpstop -a)
C-->C1(备份主目录)
C1-->D(安装新包 sudo yum install greenplum-db-<version>-<platform>.rpm)
D-->E(更新权限 sudo chown -R gpadmin:gpadmin /usr/local/greenplum*)
E-->F(环境配置 source /usr/local/greenplum-db-<current_version>/greenplum_path.sh)
F-->G(使用gpadmin启动数据库 gpstart)

服务器扩展

数据迁移

gptransfer迁移工具从一个Greenplum数据库把元数据和数据传输到另一个Greenplum数据库，它允许迁移一个数据库的整个内容或者只是选中的表到另一个数据库。

源数据库和目标数据库可以在同一个或者不同的集群中。数据会在所有的Segment间并行地传输，使用gpfdist数据装载工具可以得到最高的传输率。

gptransfer处理数据传输的设置和执行。参与的集群必须已经存在，在两个集群的所有主机间必须有网络访问以及证书认证的ssh访问。接口包括传输一个或者多个完整数据库、一个或者多个数据库表的选项。

一次完整数据库传输包括数据库模式、表数据、索引、视图、角色、用户定义函数以及资源队列。配置文件（包括postgres.conf和pg_hba.conf）必须由管理员手工传输。用gppkg安装在数据库中的扩展（例如MADlib）必须由管理员在目标数据库中安装。gptransfer工具的完整语法和使用信息请见Greenplum数据库工具指南。

数据备份与压缩

性能优化

资源队列内存管理

Resource Queue Memory Management

gp_vmem_protect_limit

使用gp_vmem_protect_limit设置实例可以为每个段数据库中正在进行的所有工作分配的最大内存。

gp_vmem – Greenplum数据库可用的总内存

gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM)) / 1.7

max_acting_primary_segments –由于主机或段故障而激活镜像段时，主机上可以运行的主要段的最大数量

gp_vmem_protect_limit = gp_vmem / acting_primary_segments
# 或者使用这个 (SWAP + (RAM * vm.overcommit_ratio)) .9 / primary number_segments_per_server
vm.overcommit_ratio = (RAM - 0.026 * gp_vmem) / RAM

8G内存参考：

gp_vmem = ((4+8)-(7.5+0.05*8))/1.7 = 2.41
acting_primary_segments = 6
gp_vmem_protect_limit = 2.41 / 6 = 411M
vm.overcommit_ratio = (8-0.026*23431/1024)/8 = 0.925634033203125

获取当前参数值：

gpconfig -s gp_vmem_protect_limit

设置参数：

gpconfig -c gp_vmem_protect_limit -v 411

如果修改参数时出现 ssh_exchange_identification: read: Connection reset by peer 的问题时，此时大概到对应的机器上修改sshd_config的Maxstartups的值为100。然后重启sshd服务。

vim /etc/ssh/sshd_config
Maxstartups 100
service sshd restart

删除参数：

gpconfig -r gp_vmem_protect_limit

最终调用命令：gpstop -u使配置生效。

shared_buffers

8G系统配置shared_buffers 为1024M时，重启GP失败。报错：with reason:'PG_CTL failed。
仔细查看错误日志在各个分片时，在日志文件/opt/data/gp-data/mirror/gpseg15/pg_log/startup.log中找到如下信息：

could not map anonymous shared memory: Cannot allocate memory (pg_shmem.c:587)",,"This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 1260699564 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.",,,,,,"CreateAnonymousSegment","pg_shmem.c",587,1    0x9f24fc postgres errstart (elog.c:558)

todo 待续查看是什么情况

解决方案：先注释所有的该配置，再启动GP成功！

gp_fts_probe_timeout

指定故障检测进程（ftsprobe）在声明断开之前建立与段的连接的允许的超时时间：

该值默认为20s。

gpconfig -c gp_fts_probe_timeout -v 60s
gpstup -u

参考：
mirror segment always down now and then

max_connections

查看现有配置：

gpconfig -s max_connections

修改值：

最大连接数，Segment建议设置成Master的5-10倍。

gpconfig -c max_connections -v 2000 -m 500

max_prepared_transactions

它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令）。如果它的值被设为0。则将数据库将关闭prepared事务的特性。它的值通常应该和max_connections的值一样大。每个事务消耗600字节(b)共享内存。

查看现有配置：

gpconfig -s max_prepared_transactions

修改值：

gpconfig -c max_prepared_transactions  -v 500

参考文档：
greenplum error failed to acquire resources on one or more segments

gp_resqueue_priority_cpucores_per_segment

指定每个分片实例分配的CPU单元数。设置需要重启数据库。

例如，如果Greenplum数据库集群具有配置有四个分片的10核分片主机，请将分片实例的值设置为2.5。

对于主实例，该值为10。一个主主机通常仅在其上运行该主实例，因此该主实例的值应反映所有可用CPU内核的使用情况。不正确的设置可能导致CPU使用不足或查询优先级无法按设计工作。

Segment设置值=核心数/Segments数
Master设置值=核心数

8核3个分片：

gpconfig -v gp_resqueue_priority_cpucores_per_segment -m 8 -v 2.5

注意:仅当基于资源队列的资源管理处于活动状态时，才强制执行gp resqueue priority cpucores每个段服务器配置参数。

综合所有优化设置

以下配置针对8核、3子节点、3分片(8G内存)、1主实例(8G内存）：

gpconfig -c gp_vmem_protect_limit -v 961
gpconfig -c max_statement_mem  -v 2000MB
gpconfig -c statement_mem  -v 256MB
gpconfig -c work_mem  -v 128MB
gpconfig -c maintenance_work_mem  -v 256MB

gpconfig -c gp_fts_probe_timeout -v 60s
gpconfig -c max_connections -v 2000 -m 500
gpconfig -c max_prepared_transactions  -v 500
gpconfig -c gp_resqueue_priority_cpucores_per_segment -m 6 -v 2.5

以下配置针对32核、3子节点、3分片（48G内存）、1主实例（8G内存）：

gpconfig -c gp_vmem_protect_limit -v 8500
gpconfig -c max_statement_mem  -v 2000MB
gpconfig -c statement_mem  -v 256MB
gpconfig -c work_mem  -v 128MB
gpconfig -c maintenance_work_mem  -v 256MB  

gpconfig -c gp_fts_probe_timeout -v 60s
gpconfig -c max_connections -v 2000 -m 500
gpconfig -c max_prepared_transactions  -v 500
gpconfig -c gp_resqueue_priority_cpucores_per_segment -m 8 -v 10.5

#limits.conf
vm.overcommit_ratio = 95

GP参数计算器

greenplum calc

资源组

安装GPCC

通过文档安装gpcc（Greenplum命令管理中心）以管理资源组。

$ source /usr/local/greenplum-db-<version>/greenplum_path.sh
$ gpssh -f <hostfile> 'sudo mkdir -p /usr/local/greenplum-cc-6.2.0; chown -R gpadmin:gpadmin /usr/local/greenplum-cc-6.2.0'$ gpssh -f <hostfile> 'sudo ln -s /usr/local/greenplum-cc-6.2.0 /usr/local/greenplum-cc'

安装cgroup

在GP各服务器安装cgroup：

yum install libcgroup libcgroup-tools

如果安装不了，可以直接下载二进制文件。

libcgroup 下载地址
libcgroup-tools 下载地址

然后使用命令安装两个包：

yum install libcgroup-*

编辑资源组配置

创建Greenplum Database cgroup配置文件/etc/cgconfig.d/gpdb.conf。您必须是超级用户或具有sudo访问权限才能创建此文件：

sudo vim /etc/cgconfig.d/gpdb.conf

将以下配置信息添加到/etc/cgconfig.d/gpdb.conf中

group gpdb {
     perm {
         task {
             uid = gpadmin;
             gid = gpadmin;
         }
         admin {
             uid = gpadmin;
             gid = gpadmin;
         }
     }
     cpu {
     }
     cpuacct {
     }
     cpuset {
     }
     memory {
     }
}

启动资源组服务

确认资源组已启用：

grep cgroup /proc/mounts

通过运行以下命令，验证您是否正确设置了Greenplum Database cgroups配置：

ls -l /sys/fs/cgroup/cpu/gpdb
ls -l /sys/fs/cgroup/cpuacct/gpdb
ls -l /sys/fs/cgroup/cpuset/gpdb
ls -l /sys/fs/cgroup/memory/gpdb

如果这些目录存在并且归gpadmin:gpadmin所有，则您已成功为Greenplum Database CPU资源管理配置了cgroup。

设置cgroup自动启动：

sudo systemctl enable cgconfig.service

重新启动服务：

service cgconfig restart

GP启用资源组

将参数gp_resource_manager改为group

gpconfig -s gp_resource_manager
gpconfig -c gp_resource_manager -v "group"

然后重启服务：

gpstop -r

默认资源组规则

Greenplum数据库为名为的角色创建两个默认资源组 admin_group 和 default_group。启用资源组时，未明确分配资源组的任何角色都会被分配该角色功能的默认组。超级用户分配角色 admin_group，将非管理员角色分配给名为 default_group。

限制类型	admin_group	default_group
并发	10	20
CPU RATE LIMIT	10	30
内存限制	10	0

引用文档：
6.2.1 资源组
 用资源组进行工作负载管理

性能测试

AO表

列存压缩测试

8k行存不压

create table t_ao_row_8k_0(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=row, COMPRESSTYPE=NONE, CHECKSUM=false);  
  
insert into t_ao_row_8k_0 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id); 
  
explain analyze select c2,count(*) from t_ao_row_8k_0 group by c2;  
  
explain analyze select * from t_ao_row_8k_0 where c2=1;  
  
create index idx_t_ao_row_8k_0 on t_ao_row_8k_0(c2);  
  
explain analyze select * from t_ao_row_8k_0 where c2=1;

8k列存5级

type: zlib, ratio: 5, block: 8192

create table t_ao_COLUMN_8k_5(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=COLUMN, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, CHECKSUM=false);  

-- 插入千万数据
insert into t_ao_COLUMN_8k_5 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  

--分组聚合查询
explain analyze select c2,count(*) from t_ao_COLUMN_8k_5 group by c2; 

--单值多行查询
explain analyze select * from t_ao_COLUMN_8k_5 where c2=1; 

--创建索引
create index idx_t_ao_COLUMN_8k_5 on t_ao_COLUMN_8k_5(c2);

--单值多行查询（索引）
explain analyze select * from t_ao_COLUMN_8k_5 where c2=1;

nohup psql -h 192.168.218.87 -U gpadmin -d junbo_warehouse -f ./test.sql > ./log 2>&1 &

性能报告

测试线

8核，8G内存，4台机器。

存储形态	写入千万耗时	空间占用	分组聚合	单值多行查询	建索引耗时	单值多行查询（索引）
8k行存不压	60.479s	1091M	0.388s	0.451s	12.511	0.394s
8k列存5级	46.53s	954M	0.396s	4.28s	22.583s	0.511s

生产线

8核，8G内存，4台机器。

存储形态	写入千万耗时	空间占用	分组聚合	单值多行查询	建索引耗时	单值多行查询（索引）
8k列存5级	95.618s	954M	1.204s	4.070s	27.616s	0.223s

其它

效用

常用Greenplum脚本

序列相关

-- 创建序列
CREATE SEQUENCE user_action_serial START 1;

-- 查询序列
select * from user_action_serial

数据倾斜

-- 全表数据倾斜
select gp_execution_dbid(), datname, pg_size_pretty(pg_database_size(datname)) from gp_dist_random('pg_database') order by 2,1,pg_database_size(datname) desc;

-- 表级数据倾斜
select gp_execution_dbid(), pg_size_pretty(pg_total_relation_size('st_user_action')) from gp_dist_random('gp_id');

表类型

-- 查询当前表类型
-- a -- 行存储AO表 h -- heap堆表、索引 x -- 外部表(external table) v -- 视图 c -- 列存储AO表
select distinct relstorage from pg_class

-- 查询单表占用空间
select pg_size_pretty(pg_relation_size('st_user_action'));
-- 查询所有表的占用空间
SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes

自定义函数：查询分区表空间占用

CREATE OR REPLACE FUNCTION "public"."calc_partition_table"("v_schemaname" varchar, "v_tablename" varchar)
  RETURNS "pg_catalog"."int8" AS $BODY$
DECLARE
    v_calc BIGINT := 0;
    v_total BIGINT := 0;
    v_tbname VARCHAR(200);
    cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pg_partitions
   where schemaname=v_schemaname and tablename=v_tablename;
BEGIN
    OPEN cur_tbname;
    loop
        FETCH cur_tbname into v_tbname;
        if not found THEN
            exit;
        end if;
        EXECUTE 'select pg_relation_size('''||v_tbname||''')' into v_calc;
        v_total:=v_total+v_calc;        
    end loop;
    CLOSE cur_tbname;
    RETURN v_total;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION calc_partition_table(character varying, character varying) OWNER TO gpadmin;

分布

-- 查看分布键
select attname from pg_attribute 
where attrelid='st_user_action'::regclass 
and attnum in (SELECT unnest(distkey) FROM pg_catalog.gp_distribution_policy t where localoid='st_user_action'::regclass);

-- 修改分布键
ALTER TABLE st_user_action SET DISTRIBUTED BY (id)

-- 创建表时指定分布键
CREATE TABLE "public"."st_user_action2" (
  "amount" int8,
    "id" int8,
  "business_cate" varchar(100) COLLATE "pg_catalog"."default"
) DISTRIBUTED BY (id);

表管理

批量删除外部表

由于navcat不支持直接删除外部表，所以需要使用sh脚本进行删除。

删除以spark开头的所有表，将以下代码保存为文件psql.sh，并赋予可执行权限chmod +x ./psql.sh。然后执行即可删除。

psql beacon -c "copy (SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' and table_name like 'spark_%') to stdout" | while read line; do
    echo "delete table $line ..."
    psql beacon -c "drop external table $line"
done

命令格式参考：

psql [database] -c "command"

批量删除指定数据库

for dbname in $(psql -c "copy (select datname from pg_database where datname like '%june%') to stdout") ; do
    echo "$dbname"
    dropdb -i "$dbname"
done

维护日志

恢复命令参考文档

2020-08-10 测试线启动失败原因排查

在重新安装系统，并关闭数据库，重启系统后，再使用命令gpstart启动失败。
报错信息为：“PG_CTL Fail”
经过排查对应的段实例机器上的gpstart.log日志发现如下错误信息：

文件：/opt/data/gp-data/mirror/gpseg6/pg_log/startup.log

This error does *not* mean that you have run out of disk space.  It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded.  You need to raise the respective kernel parameter.  Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter.
The PostgreSQL documentation contains more information about configuring your system for PostgreSQL.

解决方案：

使用sysctl -p重新应用参数即可。

原因：

可能是由于升级系统重启后，导致配置并未生效。

参考：
PostgreSQL--信号量和shmall 和 shmmax相关设置

2020-01-21 紧急恢复

在使用insert into table select * from table_b语句时出现分段挂起的情况。该表table_b数据量级在千万级。使用gprecoverseg命令无法恢复。

使用命令gprecoverseg -F进行完整恢复时失败。
后关闭所有的数据连接，例如gpcc等，再使用gprecoverseg成功。

总结：

gp在出现故障无法恢复时，尽可能关闭所有的数据使用。当使用gprecoverseg成功后，如果此时使用gpstate -Q查询仍然发现有些分片是DOWN状态时，需要查看：

查看该分片的实例是不是启动状态。使用命令ps -ef | grep greenplum到指定的机器上查询对应的分片。
查看gpstate -s是否有分片正在同步状态，即streaming状态，如果是，可能需要等待该状态结束到同步状态为止。

备注：

关闭所有的数据进程使用以下方法：

-- 查看当前进程
select * from pg_stat_activity where state='idle'

-- kill 所有idle进程
select pg_terminate_backend(pid) from pg_stat_activity where state='idle';

gprecoverseg -F说明：

-F （完全恢复）可选。
执行活动Segment实例的完整副本以恢复出现故障的Segment。默认情况下，仅复制Segment关闭时发生的增量更改。

2019-12-24 升级服务器系统

根据文档说明，将当前服务器系统由CentOS7.2升级为CentOS7.7。

2019-12-23 修改内核参数

发现有几个内核参数设置在文件/etc/sysctl.conf不正确，主要做了如下修改：

调用kernel.shmall的值为4000000000
调用kernel.shmmax的值为500000000
将所有的设置值后面的注释去除
重新使设置生效sysctl -p

2019-12-22 由6.1.0升级至6.2.1，并启用资源组

在各个节点安装6.2.1版本
执行升级
手工安装 libcgroup及libcgroup-tools，并配置资源组
启用资源组然后重启服务器
调用命令gprecoverseg恢复数据库
在gpcc中配置默认资源组及管理资源组

2019-12-18 数据库有18个分片失败，有服务宕机

使用gprecoverseg命令进行恢复时，恢复失败
使用gpstop -r命令进行重启时，由于在其它用户目录，报无权限。
使用kill命令删除指定greenplum进程时，由于kill不干净，导致报Error occurred: non-zero rc: 1错误。

查看日志文件：/opt/data/gp-master/gpseg-1//pg_log/startup.log可以看到如下信息：

"pre-existing shared memory block (key 5432001, ID 98305) is still in use",,"Terminate any old server processes associated with data directory ""/opt/data/gp-master/gpseg-1"".",,,,,,"PGSharedMemoryCreate","pg_shmem.c",707,

使用命令killall postgres删除所有postgres进程（该命令尚未实践，实际使用的是kill - 9 <pid>，批次删除）
使用命令gpstart重启服务成功
使用命令gprecoverseg恢复失败的分片
使用命令gprecoverseg -r恢复分片角色

Greenplum使用手册

常用命令

重启GP

使配置生效

快速关闭

部署

前置条件

操作系统

JAVA

硬件和网络

系统准备

禁用SELinux

修改 hosts

关闭防火墙

修改/etc/sysctl.conf

Shared Memory Pages

System Memory

修改系统资源限制

磁盘IO优化

预读值

设置blockdev（预读）

磁盘 I/O 调度

SSH连接阈值

安装

配置SSH免密登录

创建主机与备机上创建存储区

在主机上创建数据目录位置

在主机上创建数据存储区

在所有主机上创建数据目录位置

验证硬件和网络性能

验证网络性能

验证磁盘I / O和内存带宽

初始化Greenplum数据库系统

创建初始化服务器文件

配置GP数据库

运行初始化程序

运行初始化程序

配置环境变量

设置时区

配置客户端访问

使用

用户操作

查看所有用户

创建用户

删除用户

修改用户密码

创建数据库

创建数据库并指向用户

连接

使用命令psql连接

使用jdbc连接

与spark的连接

定义连接配置

从greenplum读取表

从Spark写入数据到greenplum

定义数据存储对象

表空间

创建表空间

使用表空间存储数据库对象

查看表空间信息

删除表空间

创建和管理模式

默认的public模式

创建模式

查看当前模式

删除模式

创建表

创建ID自增表

创建和使用序列

检查序列

返回下一个序列计数

设置计数器序列

修改序列属性

删除序列

将序列指定为一个列的默认值

分布键

查看分布键

创建表时指定分布键

修改分布键

基于JSON格式创建表

修改`/etc/sysctl.conf`

默认的`public`模式