greenplum create tablespace

gp表空间依赖于filespace,filespace需要自己首先设置文件路径

首先为其创建文件路径

master,slave1,slave2...

第二授权用户gpadmin 访问修改文件的权限

第三按照文档执行

表空间创建语句

tutorial=> \h create tablespace

Command:     CREATE TABLESPACE

Description: define a new tablespace

Syntax:

CREATE TABLESPACE tablespace_name [OWNER username] FILESPACE filespace_name

[gpadmin@master ~]$ psql -U gpadmin template1

psql (8.2.15)

Type "help" for help.

template1=# select oid,* from pg_filespace;

oid  |  fsname   | fsowner

------+-----------+---------

3052 | pg_system |      10

(1 row)

template1=# select * from pg_tablespace;

  spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid

------------+----------+-------------+--------+-----------------+-----------------+----------

pg_default |       10 |             |        |                 |                 |     3052

pg_global  |       10 |             |        |                 |                 |     3052

(2 rows)

tutorial=>  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 | /data/master/gpseg-1

pg_default | pg_system |       2 | /data/primary/gpseg0

pg_default | pg_system |       3 | /data/primary/gpseg1

pg_global  | pg_system |       1 | /data/master/gpseg-1

pg_global  | pg_system |       2 | /data/primary/gpseg0

pg_global  | pg_system |       3 | /data/primary/gpseg1

(6 rows)

--将文件路径创建并授权

[root@slave2 ~]# cd /

[root@slave2 /]# mkdir newdata

[root@slave2 /]# chown -R gpadmin /newdata

[root@slave1 ~]# cd /

[root@slave1 /]# mkdir newdata

[root@slave1 /]# chown -R gpadmin /newdata

[root@master /]# mkdir gpmaster

[root@master /]# cd gpmaster/

[root@master gpmaster]# ls

[root@master gpmaster]# mkdir gpfilespace_config_1

[root@master gpmaster]# chown -R gpadmin /gpmaster/gpfilespace_config_1/

[gpadmin@master ~]$ export PGDATABASE=tutorial;

[gpadmin@master ~]$ gpfilespace -o /gpmaster/gpfilespace_config_1

20161108:07:37:02:014040 gpfilespace:master: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.

20161108:07:37:02:014040 gpfilespace:master:gpadmin-[INFO]:-getting config

Enter a name for this filespace

> tbtest   

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

[Error] slave1: /newdata : No write permissions

primary location 1> /newdata

Configuring hosts: [master]

Enter a file system location for the master

master location> /newgpmaster

20161108:07:39:18:014040 gpfilespace:master:gpadmin-[INFO]:-Creating configuration file...

20161108:07:39:18:014040 gpfilespace:master:gpadmin-[INFO]:-[created]

20161108:07:39:18:014040 gpfilespace:master:gpadmin-[INFO]:-

To add this filespace to the database please run the command:

   gpfilespace --config /gpmaster/gpfilespace_config_1/gpfilespace_config_20161108_073703

[gpadmin@master ~]$ gpfilespace --config  /gpmaster/gpfilespace_config_1/gpfilespace_config_20161

108_073703 20161108:07:43:49:014288 gpfilespace:master: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.

20161108:07:43:49:014288 gpfilespace:master:gpadmin-[INFO]:-getting config

Reading Configuration file: '/gpmaster/gpfilespace_config_1/gpfilespace_config_20161108_073703'

20161108:07:43:49:014288 gpfilespace:master:gpadmin-[INFO]:-Performing validation on paths

..............................................................................

20161108:07:43:49:014288 gpfilespace:master:gpadmin-[INFO]:-Connecting to database

20161108:07:43:49:014288 gpfilespace:master:gpadmin-[INFO]:-Filespace "tbtest" successfully creat ed




[gpadmin@master ~]$ psql -U user1 tutorial

Password for user user1:

psql (8.2.15)

Type "help" for help.

tutorial=> select * from pg_filespace;

  fsname   | fsowner

-----------+---------

pg_system |      10

tbtest    |      10

(2 rows)

tutorial=>

[gpadmin@master ~]$ psql -U gpadmin tutorial;

psql (8.2.15)

Type "help" for help.

tutorial=# create tablespace ts_gh filespace tbtest;

CREATE TABLESPACE

tutorial=# \q

[gpadmin@master ~]$ psql -U gpadmin tutorial

psql (8.2.15)

Type "help" for help.

tutorial=# create table testtb(id int) tablespace ts_gh;

NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum D

atabase data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chose

n are the optimal data distribution key to minimize skew.CREATE TABLE

tutorial=# set default_tablespace = ts_gh;

SET

tutorial=# alter database tutorial set default_tablespace = ts_gh;

ALTER DATABASE

tutorial=# \q

将表空间权限移动给user1

[gpadmin@master ~]$ psql -U gpadmin tutorial

psql (8.2.15)

Type "help" for help.

tutorial=# alter tablespace ts_gh owner to user1;

ALTER TABLESPACE

tutorial=# select * from pg_tablespace;

  spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid

------------+----------+-------------+--------+-----------------+-----------------+----------

pg_default |       10 |             |        |                 |                 |     3052

pg_global  |       10 |             |        |                 |                 |     3052

ts_gh      |    24576 |             |        |                 |                 |    61178

(3 rows)

tutorial=# \q

[gpadmin@master ~]$ psql -U user1 tutorial

Password for user user1:

psql (8.2.15)

Type "help" for help.

tutorial=> create table test01_ts(id int) tablespace ts_gh;

NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum D

atabase data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chose

n are the optimal data distribution key to minimize skew.CREATE TABLE

tutorial=>

参考文档： http://blog.csdn.net/sunziyue/article/details/51170341

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

转载于:http://blog.itpub.net/29162273/viewspace-2128099/