greenplum擴充套件segment

1)環境

三臺主機gp1，gp2，gp3，每個主機有4個網路卡；現有master、standby和2個segment、mirror；mdw代表master主機

#cat /etc/hosts

192.168.12.38 gp1

192.168.12.35 mdw    

192.168.12.37 sdw4m      #新增segment 映象

192.168.12.26 sdw3        #新增segment

 

192.168.12.65 gp2

192.168.12.62 sdw1

192.168.12.72 smdw

192.168.12.63 sdw2m

 

192.168.12.53 sdw4 gp3    #新增segment

192.168.12.58 sdw2

192.168.12.59 sdw1m

192.168.12.61 sdw3m     #新增segment 映象

 

2）擴充套件步驟介紹

系統擴充套件的幾個階段：

i.增加並測試新硬體--安裝OS系統、準備greenplum環境、安裝greenplum軟體；

  ii.初始化新segment--gpexpand

iii.重新分佈表--重新分佈表及資料，gpexpand工具；

gpexpand的典型操作步驟如下：

A.   建立擴充套件檔案：gpexpand -f hosts_file

B.   初始化segment並建立擴充套件schema：gpexpand -i input_file -D db_name

C.   重新分佈表：gpexpand -d duration

D.   移除擴充套件schema（expansion schema）：gpexpand -c

3）操作例項

A.     建立擴充套件檔案

準備host_file:將新增主機名寫入檔案，一個主機名一行；

[gpadmin@gp1 ~]$ cat expand_host.file

sdw3

sdw4

 

[gpadmin@gp1 ~]$ gpexpand –f expand_host.file

執行完畢後，生成2個input file，即擴充套件的配置檔案；

[gpadmin@gp1 ~]$ cat gpexpand_inputfile_20150825_164606

sdw3:sdw3:42000:/data/primary/gpseg2:7:2:p:43000

sdw4:sdw4:52000:/data/mirror/gpseg2:10:2:m:53000

sdw4:sdw4:42000:/data/primary/gpseg3:8:3:p:43000

sdw3:sdw3:52000:/data/mirror/gpseg3:9:3:m:53000

對應格式：<hostname>:<address>:<port>:<fselocation>:<dbid>:<content>:<preferred_role>:<replication_port>

[gpadmin@gp1 ~]$ cat gpexpand_inputfile_20150825_164606.fs

filespaceOrder=nf_tab_tbs:smdw_fs

7:/data/primary/nf_tab_tbs/gpseg2:/data/gpseg2

10:/data/mirror/nf_tab_tbs/gpseg2:/data/gpseg2

8:/data/primary/nf_tab_tbs/gpseg3:/data/gpseg3

9:/data/mirror/nf_tab_tbs/gpseg3:/data/gpseg3

對應格式：

filespaceOrder=<filespace1_name>:<filespace2_name>: ...

  dbid:</path/for/filespace1>:</path/for/filespace2>: ...

  dbid:</path/for/filespace1>:</path/for/filespace2>: ...

         檔案生成後，安裝配置檔案中的資訊，在對應主機上建立相應目錄（fselocation和/path/for/filespace1），不然，初始化新segment階段，報錯退出；

 

B.      初始化segment

 [gpadmin@gp1 ~]$ gpexpand -i gpexpand_inputfile_20150825_164606 -D db014

20150827:09:07:43:015707 gpexpand:gp1:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.5.1 build 1'

20150827:09:07:43:015707 gpexpand:gp1:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.5.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 14 2015 14:07:14'

20150827:09:07:43:015707 gpexpand:gp1:gpadmin-[INFO]:-Querying gpexpand schema for current expansion state

20150827:09:07:43:015707 gpexpand:gp1:gpadmin-[INFO]:-Readying Greenplum Database for a new expansion

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database db014 for unalterable tables...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database postgres for unalterable tables...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database db013 for unalterable tables...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database template1 for unalterable tables...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database db014 for tables with unique indexes...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database postgres for tables with unique indexes...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database db013 for tables with unique indexes...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking database template1 for tables with unique indexes...

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Syncing Greenplum Database extensions

20150827:09:08:13:015707 gpexpand:gp1:gpadmin-[INFO]:-The packages on sdw4 are consistent.

20150827:09:08:14:015707 gpexpand:gp1:gpadmin-[INFO]:-The packages on sdw3 are consistent.

20150827:09:08:14:015707 gpexpand:gp1:gpadmin-[INFO]:-Creating segment template

20150827:09:08:14:015707 gpexpand:gp1:gpadmin-[INFO]:-VACUUM FULL on the catalog tables

20150827:09:08:17:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting copy of segment dbid 1 to location /data/master/gpexpand_08272015_15707

20150827:09:08:28:015707 gpexpand:gp1:gpadmin-[INFO]:-Cleaning up catalog for schema only copy on destination

20150827:09:08:28:015707 gpexpand:gp1:gpadmin-[INFO]:-Copying postgresql.conf from existing segment into template

20150827:09:08:29:015707 gpexpand:gp1:gpadmin-[INFO]:-Copying pg_hba.conf from existing segment into template

20150827:09:08:29:015707 gpexpand:gp1:gpadmin-[INFO]:-Adding new segments into template pg_hba.conf

20150827:09:08:29:015707 gpexpand:gp1:gpadmin-[INFO]:-Creating schema tar file

20150827:09:08:47:015707 gpexpand:gp1:gpadmin-[INFO]:-Distributing template tar file to new hosts

20150827:09:09:18:015707 gpexpand:gp1:gpadmin-[INFO]:-Configuring new segments (primary)

20150827:09:10:05:015707 gpexpand:gp1:gpadmin-[INFO]:-Configuring new segments (mirror)

20150827:09:10:08:015707 gpexpand:gp1:gpadmin-[INFO]:-Backing up pg_hba.conf file on original segments

20150827:09:10:08:015707 gpexpand:gp1:gpadmin-[INFO]:-Copying new pg_hba.conf file to original segments

20150827:09:10:09:015707 gpexpand:gp1:gpadmin-[INFO]:-Configuring original segments

20150827:09:10:09:015707 gpexpand:gp1:gpadmin-[INFO]:-Cleaning up temporary template files

20150827:09:10:09:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting Greenplum Database in restricted mode

20150827:09:10:23:015707 gpexpand:gp1:gpadmin-[INFO]:-Stopping database

20150827:09:11:12:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking if Transaction filespace was moved

20150827:09:11:12:015707 gpexpand:gp1:gpadmin-[INFO]:-Checking if Temporary filespace was moved

20150827:09:11:12:015707 gpexpand:gp1:gpadmin-[INFO]:-Configuring new segment filespaces

20150827:09:11:14:015707 gpexpand:gp1:gpadmin-[INFO]:-Cleaning up databases in new segments.

20150827:09:11:14:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting master in utility mode

20150827:09:11:15:015707 gpexpand:gp1:gpadmin-[INFO]:-Stopping master in utility mode

20150827:09:11:21:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting Greenplum Database in restricted mode

20150827:09:11:32:015707 gpexpand:gp1:gpadmin-[INFO]:-Creating expansion schema

20150827:09:11:37:015707 gpexpand:gp1:gpadmin-[INFO]:-Populating gpexpand.status_detail with data from database db014

20150827:09:11:39:015707 gpexpand:gp1:gpadmin-[INFO]:-Populating gpexpand.status_detail with data from database postgres

20150827:09:11:40:015707 gpexpand:gp1:gpadmin-[INFO]:-Populating gpexpand.status_detail with data from database db013

20150827:09:11:43:015707 gpexpand:gp1:gpadmin-[INFO]:-Populating gpexpand.status_detail with data from database template1

20150827:09:11:44:015707 gpexpand:gp1:gpadmin-[INFO]:-Stopping Greenplum Database

20150827:09:12:13:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting Greenplum Database

20150827:09:12:22:015707 gpexpand:gp1:gpadmin-[INFO]:-Starting new mirror segment synchronization

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-************************************************

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-Initialization of the system expansion complete.

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-To begin table expansion onto the new segments

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-rerun gpexpand

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-************************************************

20150827:09:12:46:015707 gpexpand:gp1:gpadmin-[INFO]:-Exiting...

這個階段主要，注意：1.建立對應目錄；2.執行命令時，指定-D db——name，用於建立擴充套件schema；

 

C.      重新分佈GPDB中資料

[gpadmin@gp1 ~]$ gpexpand -d 10:10:00 -D db014

20150827:09:55:55:021843 gpexpand:gp1:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.5.1 build 1'

20150827:09:55:56:021843 gpexpand:gp1:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.5.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 14 2015 14:07:14'

20150827:09:55:56:021843 gpexpand:gp1:gpadmin-[INFO]:-Querying gpexpand schema for current expansion state

20150827:09:56:00:021843 gpexpand:gp1:gpadmin-[INFO]:-Distribution policy for table yduser.rtmobilegsm_1_prt_2 is 'None'

20150827:09:56:00:021843 gpexpand:gp1:gpadmin-[INFO]:-Expanding db013.yduser.rtmobilegsm_1_prt_2

20150827:09:56:08:021843 gpexpand:gp1:gpadmin-[INFO]:-Finished expanding db013.yduser.rtmobilegsm_1_prt_2

20150827:09:56:08:021843 gpexpand:gp1:gpadmin-[INFO]:-Distribution policy for table yduser.rtmobilegsm_1_prt_1 is 'None'

20150827:09:56:08:021843 gpexpand:gp1:gpadmin-[INFO]:-Expanding db013.yduser.rtmobilegsm_1_prt_1

20150827:09:58:28:021843 gpexpand:gp1:gpadmin-[INFO]:-Finished expanding db013.yduser.rtmobilegsm_1_prt_1

20150827:09:58:31:021843 gpexpand:gp1:gpadmin-[INFO]:-Distribution policy for table yduser.rtmobilegsm_1_prt_8 is 'None'

20150827:09:58:31:021843 gpexpand:gp1:gpadmin-[INFO]:-Expanding db013.yduser.rtmobilegsm_1_prt_8

20150827:09:58:46:021843 gpexpand:gp1:gpadmin-[INFO]:-Finished expanding db013.yduser.rtmobilegsm_1_prt_8

20150827:09:58:50:021843 gpexpand:gp1:gpadmin-[INFO]:-Distribution policy for table yduser.rtmobilegsm_1_prt_3 is 'None'

20150827:09:58:50:021843 gpexpand:gp1:gpadmin-[INFO]:-Expanding db013.yduser.rtmobilegsm_1_prt_3

20150827:10:09:12:021843 gpexpand:gp1:gpadmin-[INFO]:-Finished expanding db013.yduser.rtmobilegsm_1_prt_3

20150827:10:09:15:021843 gpexpand:gp1:gpadmin-[INFO]:-Distribution policy for table yduser.histmobilegsm is 'None'

20150827:10:09:15:021843 gpexpand:gp1:gpadmin-[INFO]:-Expanding db013.yduser.histmobilegsm

20150827:10:09:18:021843 gpexpand:gp1:gpadmin-[INFO]:-Finished expanding db013.yduser.histmobilegsm

20150827:10:09:23:021843 gpexpand:gp1:gpadmin-[INFO]:-EXPANSION COMPLETED SUCCESSFULLY

20150827:10:09:23:021843 gpexpand:gp1:gpadmin-[INFO]:-Exiting...

[gpadmin@gp1 ~]$

注意事項：1.重新分佈表資料儘量選擇業務少的時段

                   2.重新分佈表時會對錶進行鎖表，要考慮業務對該表的訪問；

                   3.重新分佈資料不一定要一次做完，可以分段做；資料量大的話可計劃逐步完成；

                   4.考慮表的優先順序，按需求指定優先順序；

下面兩個表，一個檢視包含了，表資料重新分佈的各種資訊；其中gpexpand.status_detail的rank列值，決定優先順序大小，越小越優先；

select * from gpexpand.status;

select * from gpexpand.status_detail;

select * from gpexpand.expansion_progress;

 

D.      移除擴充套件schema

[gpadmin@gp1 ~]$ gpexpand -c -D db014

20150827:10:15:47:022838 gpexpand:gp1:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.5.1 build 1'

20150827:10:15:47:022838 gpexpand:gp1:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.5.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 14 2015 14:07:14'

20150827:10:15:47:022838 gpexpand:gp1:gpadmin-[INFO]:-Querying gpexpand schema for current expansion state

 

Do you want to dump the gpexpand.status_detail table to file? Yy|Nn (default=Y):

> y

20150827:10:15:51:022838 gpexpand:gp1:gpadmin-[INFO]:-Dumping gpexpand.status_detail to /data/master/gpseg-1/gpexpand.status_detail

20150827:10:15:52:022838 gpexpand:gp1:gpadmin-[INFO]:-Removing gpexpand schema

20150827:10:15:53:022838 gpexpand:gp1:gpadmin-[INFO]:-Cleanup Finished.  exiting...

[gpadmin@gp1 ~]$