greenplum分布式集群（數據倉庫）實戰

1.準備環境

1.1集群介紹

系統環境：centos6.5

數據庫版本：greenplum-db-4.3.3.1-build-1-RHEL5-x86_64.zip

greenplum集群中，4臺機器IP分別是

[root@dw-greenplum-1 ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.96.101        dw-greenplum-1      mdw

192.168.96.102        dw-greenplum-2      sdw1

192.168.96.103        dw-greenplum-3      sdw2

192.168.96.104        dw-greenplum-4      sdw3

每臺/etc/hosts都要增加如上

1.架構圖：

1.2創建用戶及用戶組（每臺機器）

[root@dw-greenplum-1 ~]# groupadd -g 530 gpadmin

[root@dw-greenplum-1 ~]# useradd -g 530 -u530 -m -d /home/gpadmin -s /bin/bash gpadmin

[root@dw-greenplum-1 ~]# passwd gpadmin

Changing password for user gpadmin.

New password:

BAD PASSWORD: it is too simplistic/systematic

BAD PASSWORD: is too simple

Retype new password:

passwd: all authentication tokens updated successfully.

1.3修改系統內核（每臺機器）

注意一定要修改否則會出錯

[root@dw-greenplum-1 ~]# vi /etc/sysctl.conf

kernel.shmmax = 500000000

kernel.shmmni = 4096

kernel.shmall = 4000000000

kernel.sem = 250 512000 100 2048

kernel.sysrq = 1

kernel.core_uses_pid = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.msgmni = 2048

net.ipv4.tcp_syncookies = 1

net.ipv4.ip_forward = 0

net.ipv4.conf.default.accept_source_route = 0

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_max_syn_backlog = 4096

net.ipv4.conf.all.arp_filter = 1

net.ipv4.ip_local_port_range = 1025 65535

net.core.netdev_max_backlog = 10000

net.core.rmem_max = 2097152

net.core.wmem_max = 2097152

vm.overcommit_memory = 2

根據自己服務器去修改參數

讓參數生效

[root@dw-greenplum-1~]# sysctl -p

1.4修改文件打開數等限制（每臺機器）

注意一定要修改否則會出錯

[root@dw-greenplum-1 ~]# vi /etc/security/limits.conf

# End of file

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

2.greenplum安裝

為greenplum軟件創建安裝目錄，并且賦給gpadmin用戶權限（每臺操作）

[root@dw-greenplum-1 ~]# mkdir /opt/greenplum

[root@dw-greenplum-1 ~]# chown -R gpadmin:gpadmin /opt/greenplum

首先準備好安裝文件（在MASTER 192.168.96.101上操作）

greenplum-db-4.3.3.1-build-1-RHEL5-x86_64.zip

執行unzip命令解壓安裝文件：

[root@dw-greenplum-1 ~]# unzip greenplum-db-4.3.3.1-build-1-RHEL5-x86_64.zip

執行開始安裝軟件：

[root@dw-greenplum-1 ~]# chmod +x greenplum-db-4.3.3.1-build-1-RHEL5-x86_64.bin

[root@dw-greenplum-1 ~]# ./greenplum-db-4.3.3.1-build-1-RHEL5-x86_64.bin

安裝greenplum的License的信息

是否接受License

選擇安裝目錄

安裝以上步驟后，軟件開始怎么安裝，最后顯示軟件安裝成功。

配置系統環境變量（master,master standy兩臺配置）

[root@dw-greenplum-1 ~]# su - gpadmin

[gpadmin@dw-greenplum-1 ~]$ vi .bash_profile

source /opt/greenplum/greenplum-db/greenplum_path.sh

export MASTER_DATA_DIRECTORY=/home/gpadmin/gpdata/gpmaster/gpseg-1

export PGPORT=5432

export PGDATABASE=trjDB

讓以上配置生效

[gpadmin@dw-greenplum-1 ~]$ source .bash_profile

配置hostlist

配置文件，將所有的服務器名記在里面

[gpadmin@dw-greenplum-1 ~]$ mkdir conf

[gpadmin@dw-greenplum-1 ~]$ cd conf/

[gpadmin@dw-greenplum-1 conf]$ vi hostlist

mdw

sdw1

sdw2

sdw3

[gpadmin@dw-greenplum-1 conf]$ vi seg_hosts

sdw1

sdw2

sdw3

[gpadmin@dw-greenplum-1 conf]$ gpssh-exkeys -f hostlist

[STEP 1 of 5] create local ID and authorize on local host

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

[STEP 3 of 5] authorize current user on remote hosts

  ... send to sdw1

  ***

  *** Enter password for sdw1:

  ... send to sdw2

  ... send to sdw3

[STEP 4 of 5] determine common authentication file content

[STEP 5 of 5] copy authentication files to all remote hosts

  ... finished key exchange with sdw1

  ... finished key exchange with sdw2

  ... finished key exchange with sdw3

[INFO] completed successfully

在打通所有機器通道之后，我們就可以使用gpssh命令對所有機器進行批量操作了

[gpadmin@dw-greenplum-1 conf]$ gpssh -f hostlist

Note: command history unsupported on this machine ...

=> pwd

[sdw3] /home/gpadmin

[sdw1] /home/gpadmin

[sdw2] /home/gpadmin

[ mdw] /home/gpadmin

=>

將軟件分發到每一臺機器上

接下來將安裝后的文件打包

[gpadmin@dw-greenplum-1 conf]$ cd /opt/greenplum/

[gpadmin@dw-greenplum-1 greenplum]$ tar -cf gp.4.3.3.1.tar greenplum-db-4.3.3.1/

然后利用gpscp命令將這個文件復制到每一臺機器上：

[gpadmin@dw-greenplum-1 greenplum]$ gpscp -f /home/gpadmin/conf/hostlist gp.4.3.3.1.tar  =:/opt/greenplum/

使用gpssh命令批量解壓文件包：

[gpadmin@dw-greenplum-1 greenplum]$ cd /home/gpadmin/conf/

[gpadmin@dw-greenplum-1 conf]$ gpssh -f hostlist

=> cd /opt/greenplum

[sdw3]

[sdw1]

[sdw2]

[ mdw]

=> tar -xf gp.4.3.3.1.tar

[sdw3]

[sdw1]

[sdw2]

[ mdw]

建立軟件連接

=> ln -s  greenplum-db-4.3.3.1 greenplum-db

[sdw3]

[sdw1]

[sdw2]

[ mdw]

=> ll

[sdw3] total 397060

[sdw3] -rw-rw-r--  1 gpadmin gpadmin 406579200 Apr 22 23:32 gp.4.3.3.1.tar

[sdw3] lrwxrwxrwx  1 gpadmin gpadmin        20 Apr 22 23:53 greenplum-db -> greenplum-db-4.3.3.1

[sdw3] drwxr-xr-x 11 gpadmin gpadmin      4096 Apr 22 23:00 greenplum-db-4.3.3.1

[sdw1] total 397056

[sdw1] -rw-rw-r--  1 gpadmin gpadmin 406579200 Apr 22 23:32 gp.4.3.3.1.tar

[sdw1] lrwxrwxrwx  1 gpadmin gpadmin        20 Apr 22 23:53 greenplum-db -> greenplum-db-4.3.3.1

[sdw1] drwxr-xr-x 11 gpadmin gpadmin      4096 Apr 22 23:00 greenplum-db-4.3.3.1

[sdw2] total 397060

[sdw2] -rw-rw-r--  1 gpadmin gpadmin 406579200 Apr 22 23:32 gp.4.3.3.1.tar

[sdw2] lrwxrwxrwx  1 gpadmin gpadmin        20 Apr 22 23:53 greenplum-db -> greenplum-db-4.3.3.1

[sdw2] drwxr-xr-x 11 gpadmin gpadmin      4096 Apr 22 23:00 greenplum-db-4.3.3.1

[ mdw] total 397056

[ mdw] -rw-rw-r--  1 gpadmin gpadmin 406579200 Apr 22 23:31 gp.4.3.3.1.tar

[ mdw] lrwxrwxrwx  1 gpadmin gpadmin        22 Apr 22 23:00 greenplum-db

下面創建數據庫數據目錄

MASTER目錄：

=> mkdir -p /home/gpadmin/gpdata/gpmaster

primary節點目錄：

=> mkdir -p /home/gpadmin/gpdata/gpdatap1

=> mkdir -p /home/gpadmin/gpdata/gpdatap2

mirror節點目錄：

=> mkdir -p /home/gpadmin/gpdata/gpdatam1

=> mkdir -p /home/gpadmin/gpdata/gpdatam2

讓其他節點環境生效

[root@dw-greenplum-2 greenplum]# su - gpadmin

[gpadmin@dw-greenplum-2 ~]$ source .bash_profile

初始化greenplum的配置文件

[gpadmin@dw-greenplum-1 conf]$ cd $GPHOME/docs/cli_help/gpconfigs

[gpadmin@dw-greenplum-1 gpconfigs]$ cp gpinitsystem_config /home/gpadmin/conf/

[gpadmin@dw-greenplum-1 gpconfigs]$ cd /home/gpadmin/conf/

[gpadmin@dw-greenplum-1 conf]$ chmod u+w gpinitsystem_config

ARRAY_NAME="Greenplum"

SEG_PREFIX=gpseg

PORT_BASE=33000

declare -a DATA_DIRECTORY=(/home/gpadmin/gpdata/gpdatap1  /home/gpadmin/gpdata/gpdatap2)

DATABASE_NAME=trjDB

MASTER_HOSTNAME=mdw

MASTER_DIRECTORY=/home/gpadmin/gpdata/gpmaster

MASTER_PORT=5432

TRUSTED_SHELL=/usr/bin/ssh

MIRROR_PORT_BASE=43000

REPLICATION_PORT_BASE=34000

MIRROR_REPLICATION_PORT_BASE=44000

declare -a MIRROR_DATA_DIRECTORY=(/home/gpadmin/gpdata/gpdatam1 /home/gpadmin/gpdata/gpdatam2)

MACHINE_LIST_FILE=/home/gpadmin/conf/seg_hosts

初始化數據庫

使用gpinitsystem腳本來初始化數據庫，命令如下：

[gpadmin@dw-greenplum-1 conf]$ gpinitsystem -c gpinitsystem_config -h seg_hosts -s sdw3

看到上面圖說明初始化成功，嘗試登錄greenplum默認的數據庫postgres

[gpadmin@dw-greenplum-1 conf]$ psql -d postgres

psql (8.2.15)

Type "help" for help.

postgres=# \l

                  List of databases

   Name    |  Owner  | Encoding |  Access privileges 

-----------+---------+----------+---------------------

 postgres  | gpadmin | UTF8     |

 template0 | gpadmin | UTF8     | =c/gpadmin         

                                : gpadmin=CTc/gpadmin

 template1 | gpadmin | UTF8     | =c/gpadmin         

                                : gpadmin=CTc/gpadmin

 trjDB     | gpadmin | UTF8     |

(4 rows)

postgres=#

3.故障處理

3.1激活standby

[gpadmin@dw-greenplum-4 conf]$ gpactivatestandby

3.2恢復所有失效的segment

[gpadmin@dw-greenplum-4 gpseg-1]$ gprecoverseg

3.3還原所有segment角色

[gpadmin@dw-greenplum-4 gpseg-1]$ gprecoverseg -r

如果要新建standby，但是原來已有standby，首先要刪除它。

gpinitstandby -r

3.4把原來master 變成standby

[gpadmin@dw-greenplum-1 gpmaster]$ mv gpseg-1 gpseg-1.bak

在新的主操作如下命令：

[gpadmin@dw-greenplum-4 ~]$ gpinitstandby -F pg_system:/home/gpadmin/gpdata/gpmaster/gpseg-1 -s mdw

啟動standby

[gpadmin@dw-greenplum-4 ~]$ gpinitstandby -n

20160424:06:17:19:003594 gpinitstandby:dw-greenplum-4:gpadmin-[INFO]:-Standy master is already up and running.

查看集群狀態

select a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation from gp_segment_configuration a,pg_filespace b,pg_filespace_entry c where a.dbid=c.fsedbid and b.oid=c.fsefsoid order by content;

select * from gp_segment_configuration where content='-1';

查看standby延遲，查看pg_stat_replication 視圖即可。

select pg_switch_xlog();

select * from pg_stat_replication ;