Greenplum创建表空间

在Greenplum中，默认的表空间只有两个，分别是pg_default和pg_global，这两个表空间都放在了gp_system下。为了更有效的管理数据，需要对表进行管理，在oracle中有表空间的概念，在Greenplum中，表空间和其类似。下面介绍如何创建一个新的表空间。

1、为每个表空间创建系统目录

在master和所有的segment上都要创建。

MASTER:/home/gpadmin/gpdata/master_fspc

Primary Segment： /home/gpadmin/gpdata/primary_fspc

Mirror Segment：  /home/gpadmin/gpdata/mirror_fspc

2、执行gpfilespace脚本

$ gpfilespace

20170515:17:04:46:017345 gpfilespace:gpdb-sandbox:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.




20170515:17:04:46:017345 gpfilespace:gpdb-sandbox:gpadmin-[INFO]:-getting config
Enter a name for this filespace
> fast_fs


Checking your configuration:
Your system has 1 hosts with 2 primary and 0 mirror segments per host.


Configuring hosts: [mdw]


Please specify 2 locations for the primary segments, one per line:
primary location 1> /home/gpadmin/gpdata/primary_fspc/fs1
primary location 2> /home/gpadmin/gpdata/primary_fspc/fs2


Enter a file system location for the master
master location> /home/gpadmin/gpdata/master_fspc
20170515:17:05:57:017345 gpfilespace:mdw:gpadmin-[INFO]:-Creating configuration file...
20170515:17:05:57:017345 gpfilespace:mdw:gpadmin-[INFO]:-[created]
20170515:17:05:57:017345 gpfilespace:mdw:gpadmin-[INFO]:-
To add this filespace to the database please run the command:
   gpfilespace --config /home/gpadmin/gpdata/gpfilespace_config_20170515_170446

这样就生成了一个gp_filespace_config脚本文件，这个文件中保存了每个segment对应的数据目录（也可以手工编辑这个脚本）

vi gpfilespace_config_20170515_170446 
filespace:fast_fs
mdw:1:/home/gpadmin/gpdata/master_fspc/gpseg-1
mdw:2:/home/gpadmin/gpdata/primary_fspc/fs1/gpseg0
mdw:3:/home/gpadmin/gpdata/primary_fspc/fs2/gpseg1

3、执行gpfilespace创建文件系统

$ gpfilespace --config /home/gpadmin/gpdata/gpfilespace_config_20170515_170446
20170515:17:06:18:021203 gpfilespace:mdw:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.



20170515:17:06:18:021203 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Reading Configuration file: '/home/gpadmin/gpdata/gpfilespace_config_20170515_170446'
20170515:17:06:18:021203 gpfilespace:mdw:gpadmin-[INFO]:-Performing validation on paths
..............................................................................


20170515:17:06:18:021203 gpfilespace:mdw:gpadmin-[INFO]:-Connecting to database

20170515:17:06:18:021203 gpfilespace:mdw:gpadmin-[INFO]:-Filespace "fast_fs" successfully created

4、文件系统创建完毕，就可以在上面创建表空间了

 create tablespace tbs_fast filespace fast_fs;

 

--Query returned successfully with noresult in 277 msec.

5、创建表

CREATE TABLE dev.test1
(
  id integer
)
WITH (APPENDONLY=true, COMPRESSLEVEL=5, ORIENTATION=column, COMPRESSTYPE=zlib, 
  OIDS=FALSE
)
TABLESPACE fast_fs
DISTRIBUTED BY (id);

6、查看文件系统

[gpadmin@mdw gpdata]$ cd primary_fspc/
[gpadmin@mdw primary_fspc]$ ll
total 8
drwxrwxr-x 3 gpadmin gpadmin 4096 May 15 17:06 fs1
drwxrwxr-x 3 gpadmin gpadmin 4096 May 15 17:06 fs2
[gpadmin@mdw primary_fspc]$ cd fs1
$  tree
.
└── gpseg0 --Segment名字
    └── 101411 --表空间的oid
        └── 36856 --数据库oid
            ├── 101424       --数据文件名，对应pg_class的relfilenode
            ├── 101429
            ├── 101430
            ├── 101435
            ├── 101436
            └── PG_VERSION


3 directories, 6 files

从pg_class查看相关的信息

select relname,relfilenode,reltablespace,reloptions from pg_class
where relname = 'test1'

relnamerelfilenodereltablespacereloptionstest1101424101411{appendonly=true,compresslevel=5,orientation=column,compresstype=zlib}

从pg_class可以确认：

 1)、表已成功创建

 2）、相关信息一致