greenplum创建表空间脚本-待更新

文件空间和目录
testdb=# select oid,* from pg_filespace;
 oid  |  fsname   | fsowner 
------+-----------+---------
 3052 | pg_system |      10
(1 row)


testdb=# select * from pg_filespace_entry;
 fsefsoid | fsedbid |      fselocation      
----------+---------+-----------------------
     3052 |       1 | /data/master/gpseg-1
     3052 |       2 | /data1/primary/gpseg0
(2 rows)


                                                             ^
testdb=# SELECT  spcname as tblspc, fsname as filespc, fsedbid as seg_dbid, fselocation as datadir FROM  pg_tablespace pgts, pg_filespace pgfs, pg_filespace_entry pgfse WHERE  pgts.spcfsoid=pgfse.fsefsoid AND pgfse.fsefsoid=pgfs.oid ORDER BY tblspc, seg_dbid;
   tblspc   |  filespc  | seg_dbid |        datadir        
------------+-----------+----------+-----------------------
 pg_default | pg_system |        1 | /data/master/gpseg-1
 pg_default | pg_system |        2 | /data1/primary/gpseg0
 pg_global  | pg_system |        1 | /data/master/gpseg-1
 pg_global  | pg_system |        2 | /data1/primary/gpseg0
(4 rows)


创建新的文件空间及表空间
多节点可以使用这个命令去创建
[gpadmin@mdw data]$ gpssh -f /home/gpadmin/seg_host
先创建文件目录：
[gpadmin@mdw /]$ mkdir /data/user_spc
[gpadmin@mdw /]$ mkdir /data1/user_spc
[gpadmin@mdw /]$ mkdir /data/user_spc/master/
[gpadmin@mdw /]$ mkdir /data1/user_spc/primary/


创建一个配置文件：
[gpadmin@mdw data]$ gpfilespace -o gpfilespace_test
20160807:02:10:24:003377 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.




20160807:02:10:24:003377 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Enter a name for this filespace
> gpfilespace_test


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


Configuring hosts: [localhost.localdomain]


Please specify 1 locations for the primary segments, one per line:
primary location 1> /data1/user_spc/primary/


Enter a file system location for the master
master location> /data/user_spc/master/
20160807:02:11:12:003377 gpfilespace:mdw:gpadmin-[INFO]:-Creating configuration file...
20160807:02:11:12:003377 gpfilespace:mdw:gpadmin-[INFO]:-[created]
20160807:02:11:12:003377 gpfilespace:mdw:gpadmin-[INFO]:-
To add this filespace to the database please run the command:
   gpfilespace --config /data/gpfilespace_test
 
配置文件空间：
[gpadmin@mdw /]$ gpfilespace -c /data/gpfilespace_test
20160807:02:21:35:003736 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.




20160807:02:21:35:003736 gpfilespace:mdw:gpadmin-[INFO]:-getting config
Reading Configuration file: '/data/gpfilespace_test'
20160807:02:21:35:003736 gpfilespace:mdw:gpadmin-[INFO]:-Performing validation on paths
..............................................................................


20160807:02:21:36:003736 gpfilespace:mdw:gpadmin-[INFO]:-Connecting to database
20160807:02:21:36:003736 gpfilespace:mdw:gpadmin-[INFO]:-Filespace "gpfilespace_test" successfully created


查询新创建的文件空间：
testdb=# select oid,* from pg_filespace;
  oid  |      fsname      | fsowner 
-------+------------------+---------
  3052 | pg_system        |      10
 17127 | gpfilespace_test |      10
(2 rows)


testdb=# select * from pg_filespace_entry;                                                                                                                               fsefsoid | fsedbid |          fselocation           
----------+---------+--------------------------------
     3052 |       1 | /data/master/gpseg-1
     3052 |       2 | /data1/primary/gpseg0
    17127 |       1 | /data/user_spc/master/gpseg-1
    17127 |       2 | /data1/user_spc/primary/gpseg0
(4 rows)
17127 为新创建的文件空间


表空间是基于文件空间创建的
创建表空间
testdb=# CREATE TABLESPACE tablespace_test FILESPACE gpfilespace_test;
CREATE TABLESPACE


testdb=# \db
             List of tablespaces
      Name       |  Owner  |  Filespae Name   
-----------------+---------+------------------
 pg_default      | gpadmin | pg_system
 pg_global       | gpadmin | pg_system
 tablespace_test | gpadmin | gpfilespace_test
 
postgres=# select procpid from pg_stat_activity where DATNAME = 'template1';
 procpid 
---------
    3168
    3319
(2 rows)


postgres=# \q
[gpadmin@mdw /]$ kill 3168
[gpadmin@mdw /]$ kill 3319
[gpadmin@mdw /]$ psql
psql (8.2.15)
Type "help" for help.


testdb=# create database gpload_testDB Tablespace tablespace_test;
CREATE DATABASE
testdb=# \l+
                                               List of databases
     Name      |  Owner  | Encoding |  Access privileges  | Size  |   Tablespace    |        Description        
---------------+---------+----------+---------------------+-------+-----------------+---------------------------
 gpload_testdb | gpadmin | UTF8     |                     | 19 MB | tablespace_test | 
 postgres      | gpadmin | UTF8     |                     | 19 MB | pg_default      | 
 template0     | gpadmin | UTF8     | =c/gpadmin          | 18 MB | pg_default      | 
                                    : gpadmin=CTc/gpadmin                             
 template1     | gpadmin | UTF8     | =c/gpadmin          | 19 MB | pg_default      | Default template database
                                    : gpadmin=CTc/gpadmin                             
 testdb        | gpadmin | UTF8     |                     | 19 MB | pg_default      | 
(5 rows)
testdb=# \c gpload_testdb
You are now connected to database "gpload_testdb" as user "gpadmin".
gpload_testdb=# create schema test;
CREATE SCHEMA
gpload_testdb=# create table test.t_date(dateid varchar(8),datename varchar(32),monthid varchar(6),yearid varchar(4))distributed by (dateid);
CREATE TABLE


gpload_testdb=# create tablespace tspc_1 filespace gpfilespace_test;
CREATE TABLESPACE
gpload_testdb=# \db+
                              List of tablespaces
      Name       |  Owner  |  Filespae Name   | Access privileges | Description 
-----------------+---------+------------------+-------------------+-------------
 pg_default      | gpadmin | pg_system        |                   | 
 pg_global       | gpadmin | pg_system        |                   | 
 tablespace_test | gpadmin | gpfilespace_test |                   | 
 tspc_1          | gpadmin | gpfilespace_test |                   | 
(4 rows)
gpload_testdb=# \d
No relations found.
gpload_testdb=# set search_path to public,test;
SET
gpload_testdb=# \d
              List of relations
 Schema |  Name  | Type  |  Owner  | Storage 
--------+--------+-------+---------+---------
 test   | t_date | table | gpadmin | heap
(1 row)


创建表使用另一表空间
gpload_testdb=# create table test.test1 (col1 int,col2 varchar(32),col3 varchar(64)) tablespace tspc_1 distributed by(col1);
CREATE TABLE




CREATE TABLE test.T_PARTITION_TEST(ID VARCHAR(16),MONTHID INT,COMPANYID VARCHAR(32),AMOUNT DECIMAL(18,2)) 
TABLESPACE TSPC_1 DISTRIBUTED BY(ID)
PARTITION BY LIST(MONTHID)(
  PARTITION T_PARTITION_TEST_PR_201601 VALUES(201601),
  PARTITION T_PARTITION_TEST_PR_201602 VALUES(201602),
  PARTITION T_PARTITION_TEST_PR_201603 VALUES(201603),
  PARTITION T_PARTITION_TEST_PR_201604 VALUES(201604),
  PARTITION T_PARTITION_TEST_PR_201605 VALUES(201605),
  PARTITION T_PARTITION_TEST_PR_201606 VALUES(201606),
  PARTITION T_PARTITION_TEST_PR_201607 VALUES(201607),
  PARTITION T_PARTITION_TEST_PR_201608 VALUES(201608),
  Default PARTITION OTHER TABLESPACE tablespace_test
);


创建分区表使用不同表空间
gpload_testdb=# CREATE TABLE test.T_PARTITION_TEST(ID VARCHAR(16),MONTHID INT,COMPANYID VARCHAR(32),AMOUNT DECIMAL(18,2)) 
gpload_testdb-# TABLESPACE TSPC_1 DISTRIBUTED BY(ID)
gpload_testdb-# PARTITION BY LIST(MONTHID)(
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201601 VALUES(201601),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201602 VALUES(201602),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201603 VALUES(201603),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201604 VALUES(201604),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201605 VALUES(201605),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201606 VALUES(201606),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201607 VALUES(201607),
gpload_testdb(#   PARTITION T_PARTITION_TEST_PR_201608 VALUES(201608),
gpload_testdb(#   Default PARTITION OTHER TABLESPACE tablespace_test
gpload_testdb(# );
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201601" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201602" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201603" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201604" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201605" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201606" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201607" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_t_partition_test_pr_201608" for table "t_partition_test"
NOTICE:  CREATE TABLE will create partition "t_partition_test_1_prt_other" for table "t_partition_test"
CREATE TABLE


修改表空间名称
gpload_testdb=# alter tablespace tspc_1 rename to  tpsc_2;
ALTER TABLESPACE


gpload_testdb=# select * from pg_filespace;
      fsname      | fsowner 
------------------+---------
 pg_system        |      10
 gpfilespace_test |      10
(2 rows)


修改文件空间名称
gpload_testdb=# alter filespace gpfilespace_test rename to filespace_test;
ALTER FILESPACE
gpload_testdb=# select * from pg_filespace;
     fsname     | fsowner 
----------------+---------
 pg_system      |      10
 filespace_test |      10
(2 rows)


删除表空间时先移除依赖的对象
gpload_testdb=# drop tablespace tpsc_2;

ERROR:  tablespace "tpsc_2" is not empty

以上未整理完成，待更新，若有错误，请指正，谢谢

---------------------------------------------------------------------------------------------

gp初学者