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
[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=>
