ITPub博客

首页 > 大数据 > 数据分析 > greenplum扩展segment

greenplum扩展segment

原创 数据分析 作者:panpong 时间:2015-09-02 08:42:28 0 删除 编辑

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并创建扩展schemagpexpand -i input_file -D db_name

C.   重新分布表:gpexpand -d duration

D.   移除扩展schemaexpansion 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_detailrank列值,决定优先级大小,越小越优先;

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
数据库工程师,熟悉oracle,postgresql,mysql,DB2等主流关系型数据库运维,调优,一直活跃在运维一线。了解Python,django,简单自动化运维系统开发。

注册时间:2009-03-06

  • 博文量
    138
  • 访问量
    269002