greenplum create tablespace

简介: gp表空间依赖于filespace,filespace需要自己首先设置文件路径 首先为其创建文件路径 master,slave1,slave2.
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=>


目录
相关文章
|
5月前
|
存储 索引
mysqldump got error 1812 tablespace is missing for table when using lock tables
mysqldump got error 1812 tablespace is missing for table when using lock tables
213 1
|
10月前
|
Oracle 关系型数据库
oracle no privileges on tablespace 'USERS
oracle no privileges on tablespace 'USERS
113 0
|
存储 Oracle 关系型数据库
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
531 0
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
|
SQL 关系型数据库 MySQL
MySQL8.0 - 新特性 - 通过SQL管理UNDO TABLESPACE
前言 InnoDB的undo log从5.6版本开始可以存储到单独的tablespace文件中,在5.7版本支持了在线undo文件truncate,解决了长期以来的undo膨胀问题。而到了8.0版本,对Undo tablespace做了进一步的优化:在新版本中,我们可以拥有更多的回滚段(每个Undo tablespace可以有128个回滚段,而在之前的版本中所有tablespace的回滚段不允许超过128个),减少了由于事务公用回滚段产生的锁冲突;可以在线动态的增删undo tablespace,使得undo的管理更加灵活。
3574 0
|
SQL 固态存储 关系型数据库
|
关系型数据库 数据库 PostgreSQL
|
机器学习/深度学习 C++ Go
|
SQL Oracle 关系型数据库
MANAGE TABLESPACE
一、官档 BOOK → Database SQL Language Reference → 12 SQL Statements: ALTER TABLE to ALTER TABLESPACE → ALTER TABLESPACE 二、扩容表空间 扩大数据库的第三种方法是手工增大数据文件(datafile)的容量或使表空间(tablespace)内的数据文件容量可以随需动态地增长。
1269 0