本文主要是介绍Greenplum-表空间笔记，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Greenplum表空间
环境：http://blog.csdn.net/sunziyue/article/details/49026913
Greenplum创建表空间时，需要指定filespace。filespace是服务器上指定文件目录的集合，需要提前利用gp提供的命令gpfilespace创建好。
表空间基于文件空间filespace，一个filespace可以创建多个表空间。
1创建表空间语法
| test=# \h create tablespace; Command: CREATE TABLESPACE Description: define a new tablespace Syntax: CREATE TABLESPACE tablespace_name [OWNER username] FILESPACE filespace_name |
2gpfilespace大致用法
| gpfilespace [<connection_option> ...] [-l <logfile_directory>] [-o [<output_fs_config_file>]] gpfilespace [<connection_option> ...] [-l <logfile_directory>] -c <fs_config_file> gpfilespace movetempfilespace {<filespace_name>|default} gpfilespace movetransfilespace {<filespace_name>|default} gpfilespace --showtempfilespace gpfilespace --showtransfilespace gpfilespace -v | -? [gpadmin@master1 bin]$ ./gpfilespace --showtempfilespace Error: unable to import module: No module named gppylib 解决：记得环境变量 [gpadmin@master1 bin]$ source /usr/local/greenplum-db/greenplum_path.sh |
3创建新的文件空间
初次安装好gp默认相关
| test=# \l List of databases Name | Owner | Encoding | Access privileges -----------+---------+----------+--------------------- postgres | gpadmin | UTF8 | test | gpadmin | UTF8 | template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin template1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin test=# \du List of roles Role name | Attributes | Member of -----------+-----------------------------------+----------- gpadmin | Superuser, Create role, Create DB | test=# select * from pg_tablespace; spcname|spcowner|spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid pg_default | 10 | | | | | 3052 pg_global | 10 | | | | | 3052 test=# select * from pg_filespace; fsname | fsowner -----------+--------- pg_system | 10 test=# select * from pg_filespace_entry; fsefsoid | fsedbid | fselocation ----------+---------+------------------------ 3052 | 1 | /gpmaster/gpseg-1 3052 | 2 | /gpdata/primary/gpseg0 3052 | 3 | /gpdata/primary/gpseg1 test=# select spcname, fsname,fsedbid,fselocation FROM pg_tablespace pgts, pg_filespace pgfs,pg_filespace_entry pgfse WHERE pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY 1,3; spcname | fsname | fsedbid | fselocation ------------+-----------+---------+------------------------ pg_default | pg_system | 1 | /gpmaster/gpseg-1 pg_default | pg_system | 2 | /gpdata/primary/gpseg0 pg_default | pg_system | 3 | /gpdata/primary/gpseg1 pg_global | pg_system | 1 | /gpmaster/gpseg-1 pg_global | pg_system | 2 | /gpdata/primary/gpseg0 pg_global | pg_system | 3 | /gpdata/primary/gpseg1 |
3.1生成文件空间配置文件
[gpadmin@master1 ~]$ source /usr/local/greenplum-db/greenplum_path.sh
[gpadmin@master1~]$ gpfilespace -o /gpmaster/gpfilespace_config_1
| [gpadmin@master1 ~]$ gpfilespace -o /gpmaster/gpfilespace_config_1 … > tbtest // filespace名称 Checking your configuration: Your system has 2 hosts with 1 primary and 0 mirror segments per host. Your system has 1 hosts with 0 primary and 0 mirror segments per host. Configuring hosts: [slave1, slave2] Please specify 1 locations for the primary segments, one per line: primary location 1> /newdata //primary segments节点服务器目录名(2个节点都要创建) Configuring hosts: [master1] Enter a file system location for the master master location> /newgpmaster //master节点服务器目录名 20160413:00:22:10:012846 gpfilespace:master1:gpadmin-[INFO]:-Creating configuration file... 20160413:00:22:10:012846 gpfilespace:master1:gpadmin-[INFO]:-[created] 20160413:00:22:10:012846 gpfilespace:master1:gpadmin-[INFO]:- To add this filespace to the database please run the command: gpfilespace --config /gpmaster/gpfilespace_config_1 //生成的配置文件/gpmaster/gpfilespace_config_1 |
3.2创建文件空间
[gpadmin@master1~]$gpfilespace --config /gpmaster/gpfilespace_config_1
成功之后数据库中可以查询到
test=#select * from pg_filespace;
fsname | fsowner
-----------+---------
pg_system | 10
tbtest | 10
4使用新的文件空间
4.1迁移系统的默认文件空间pg_system到新文件空间
迁移系统默认的表空间至新创建的文件空间，默认的文件空间目录建议不要删除：
[gpadmin@master1~]$ gpfilespace --showtransfilespace
[gpadmin@master1~]$ gpfilespace --showtempfilespace
[gpadmin@master1~]$ gpfilespace movetransfilespace tbtest
[gpadmin@master1~]$ gpfilespace movetempfilespace tbtest
4.2数据库内创建新的表空间
表空间使用文件空间tbtest
test=#create tablespace newtbtest filespacetbtest;
在指定表空间上创建对象：
test=#create table test(id int) tablespace newtbtest;
在会话级别设置默认表空间：
test=#set default_tablespace=newtbtest;
在数据库级别修改默认表空间：
test=#alter database test set default_tablespace=newtbtest;
这篇关于Greenplum-表空间笔记的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
