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

关注

原创最新推荐文章于 2026-04-12 09:58:42 发布 848 阅读 AI 写同款· GEO 优化 ›

Greenplum创建表空间 在Greenplum中，默认的表空间只有两个，分别是pg_default和pg_global，这两个表空间都放在了gp_system下。为了更有效的管理数据，需要对表进行管理，在oracle中有表空间的概念，在Greenplum中，表空间和其类似。下面介绍如何创建一个新的表空间。 1、为每个表空间创建系统目录在master和所有的segment上都要创建。 MASTER:/home/gpa 阅读详情

文件空间和目录
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初学者

收录于