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 ~]$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16976507/viewspace-1789064/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16976507/viewspace-1789064/