Greenplum表空间管理

最新推荐文章于 2024-01-21 21:49:30 发布

Murkey学习之旅

最新推荐文章于 2024-01-21 21:49:30 发布

阅读量788

点赞数

分类专栏： # 数据库管理 Greenplum 文章标签： postgresql

本文链接：https://blog.csdn.net/murkey/article/details/105919937

版权

Greenplum 同时被 2 个专栏收录

35 篇文章 6 订阅

订阅专栏

数据库管理

8 篇文章 0 订阅

订阅专栏

本文介绍如何创建表空间，如何在表空间上创建数据库，如何查询表空间和database；

1 表空间的概述

表空间允许数据库管理员在每台机器上拥有多个文件系统并且决定如何最好地使用物理存储来存放数据库对象。表空间允许用户为频繁使用和不频繁使用的数据库对象分配不同的存储，或者在特定的数据库对象上控制I/O性能。例如，把频繁使用的表放在使用高性能固态驱动器（SSD）的文件系统上，而把其他表放在标准的磁盘驱动器上。

表空间需要一个主机文件系统位置来存储其数据库文件。在Greenplum数据库中，文件系统位置必须存在于包括运行master,standby master和每个primary和mirror的所有主机上。

默认系统的文件系统filespace：pg_system在初始化时候建立的
所有的system对象都存放的此文件系统
所有用户的数据也是存放在此文件系统

表空间位于与底层文件系统交互的文件空间之上
一个文件系统可以创建多个表空间
两个默认的表空间： pg_default and pg_global

2 创建filespace

2.1 检查当前的配置情况

 1 检查filespace
  select oid,* from pg_filespace;
 archdata=#  select oid,* from pg_filespace;
  oid  |  fsname   | fsowner 
 ------+-----------+---------
  3052 | pg_system |      10
 (1 row) 
  
  
  2 检查表空间
  
  select * from pg_tablespace;
  archdata=#  select * from pg_tablespace;
   spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid 
 ------------+----------+-------------+--------+-----------------+-----------------+----------
  pg_default |       10 |             |        |                 |                 |     3052
  pg_global  |       10 |             |        |                 |                 |     3052
 (2 rows)
 
  
  3 检查TEMPORARY_FILES和TRANSACTION_FILES对应的filespace
  
  gpfilespace --showtempfilespace
  [gpadmin@mdw ~]$  gpfilespace --showtempfilespace
 20200504:16:08:30:023211 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.
 
 
 20200504:16:08:30:023211 gpfilespace:mdw:gpadmin-[INFO]:-Getting filespace information for TEMPORARY_FILES
 20200504:16:08:30:023211 gpfilespace:mdw:gpadmin-[INFO]:-Checking for filespace consistency
 20200504:16:08:30:023211 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES OIDs are consistent for pg_system filespace
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES entries are consistent for pg_system filespace
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-Current Filespace for TEMPORARY_FILES is pg_system
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-1    /greenplum/gpdata/master/gpseg-1
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-14    /greenplum/gpdata/master/gpseg-1
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-2    /greenplum/gpdata/primary1/gpseg0
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-8    /greenplum/gpdata/mirror1/gpseg0
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-3    /greenplum/gpdata/primary2/gpseg1
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-9    /greenplum/gpdata/mirror2/gpseg1
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-4    /greenplum/gpdata/primary1/gpseg2
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-10    /greenplum/gpdata/mirror1/gpseg2
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-5    /greenplum/gpdata/primary2/gpseg3
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-11    /greenplum/gpdata/mirror2/gpseg3
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-6    /greenplum/gpdata/primary1/gpseg4
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-12    /greenplum/gpdata/mirror1/gpseg4
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-7    /greenplum/gpdata/primary2/gpseg5
 20200504:16:08:31:023211 gpfilespace:mdw:gpadmin-[INFO]:-13    /greenplum/gpdata/mirror2/gpseg5
  
  gpfilespace --showtransfilespace
  
  [gpadmin@mdw ~]$  gpfilespace --showtransfilespace
 20200504:16:09:09:023363 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.
 
 
 20200504:16:09:09:023363 gpfilespace:mdw:gpadmin-[INFO]:-Getting filespace information for TRANSACTION_FILES
 20200504:16:09:09:023363 gpfilespace:mdw:gpadmin-[INFO]:-Checking for filespace consistency
 20200504:16:09:09:023363 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-TRANSACTION_FILES OIDs are consistent for pg_system filespace
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-TRANSACTION_FILES entries are consistent for pg_system filespace
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-Current Filespace for TRANSACTION_FILES is pg_system
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-1    /greenplum/gpdata/master/gpseg-1
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-14    /greenplum/gpdata/master/gpseg-1
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-2    /greenplum/gpdata/primary1/gpseg0
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-8    /greenplum/gpdata/mirror1/gpseg0
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-3    /greenplum/gpdata/primary2/gpseg1
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-9    /greenplum/gpdata/mirror2/gpseg1
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-4    /greenplum/gpdata/primary1/gpseg2
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-10    /greenplum/gpdata/mirror1/gpseg2
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-5    /greenplum/gpdata/primary2/gpseg3
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-11    /greenplum/gpdata/mirror2/gpseg3
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-6    /greenplum/gpdata/primary1/gpseg4
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-12    /greenplum/gpdata/mirror1/gpseg4
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-7    /greenplum/gpdata/primary2/gpseg5
 20200504:16:09:10:023363 gpfilespace:mdw:gpadmin-[INFO]:-13    /greenplum/gpdata/mirror2/gpseg5
 [gpadmin@mdw ~]$ 
 
 
 archdata=# select a.spcname,b.fsname from pg_tablespace a,pg_filespace b where spcfsoid=b.oid;
   spcname   |  fsname   
 ------------+-----------
  pg_default | pg_system
  pg_global  | pg_system

2.2 合理利用高速盘的方案

如果我们的greenplum集群中，有SSD硬盘，又有SATA硬盘。怎样更好的利用这些空间呢？方法1. 用flashcache或bcache，通过device mapper技术，将SSD和SATA绑定，做成块设备。再通过逻辑卷管理或者软RAID 或者 brtfs or zfs管理起来，做成大的文件系统。（还有一种方法是用RHEL 7提供的LVM，可以将SSD作为二级缓存）这种方法对GP来说，是混合动力，可以创建一个或多个文件系统（都具备混合动力）。所以建议只需要一个pg_system filespace就够了(除非容量到了文件系统管理的极限，那样的话可以分成多个文件系统)。用多个文件系统的情况下，就需要对每个文件系统，创建对应的目录，以及filespace。

方法2. SSD和SATA分开，各自创建各自的文件系统。对每个文件系统，创建对应的目录，以及filespace。

2.3 创建文件系统目录

当前数据库只有pg_segment一个文件空间，上面有pg_default和pg_global两个表空间。

master 节点目录

 mkdir -p /ssd1/gpdata/master_p
 
 chown gpadmin:gpadmin /ssd1/gpdata/master_p
 [root@mdw ~]# mkdir -p /ssd1/gpdata/master_p
 [root@mdw ~]# chown gpadmin:gpadmin /ssd1/gpdata/master_p

master standby节点目录

 $ mkdir -p /ssd1/gpdata/master_s
 $ chown gpadmin:gpadmin /ssd1/gpdata/master_s
 
 Last login: Fri Apr 24 17:57:20 2020 from 192.168.2.69
 [root@sdw3 ~]#  mkdir -p /ssd1/gpdata/master_s
 [root@sdw3 ~]# chown gpadmin:gpadmin /ssd1/gpdata/master_s
 [root@sdw3 ~]#

segment节点目录

 
 [root@mdw ~]# source /usr/local/greenplum-db/greenplum_path.sh 
 [root@mdw ~]#  gpssh -f /home/gpadmin/gpconfig/all_segment 
 => mkdir -p /ssd1/gpdata/segdata_p1
 chown gpadmin:gpadmin /ssd1/gpdata/segdata_p1
 
 mkdir -p /ssd1/gpdata/segdata_p2
 
 chown gpadmin:gpadmin /ssd1/gpdata/segdata_p2

segment mirror节点目录

 
 [root@mdw ~]# source /usr/local/greenplum-db/greenplum_path.sh 
 [root@mdw ~]#  gpssh -f /home/gpadmin/gpconfig/all_segment 
 =>  mkdir -p /ssd1/gpdata/segdata_m1
 chown gpadmin:gpadmin /ssd1/gpdata/segdata_m1
 
 mkdir -p /ssd1/gpdata/segdata_m2
 
 chown gpadmin:gpadmin /ssd1/gpdata/segdata_m2

2.4 创建配置文件

 使用gpfilespace -o gpfilespace_config
 
 [gpadmin@mdw ~]$ gpfilespace -o gpfilespace_config
 20200504:16:25:02:024112 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.
 
 
 20200504:16:25:02:024112 gpfilespace:mdw:gpadmin-[INFO]:-getting config
 Enter a name for this filespace
 > /ssd1/gpdata/master_p
 [Error] invalid identifier
  [Hint] non-alphanumeric identifers should be double-quoted
 
 Enter a name for this filespace
 > ssd1
 
 Checking your configuration:
 Your system has 3 hosts with 2 primary and 2 mirror segments per host.
 Your system has 2 hosts with 0 primary and 0 mirror segments per host.
 
 Configuring hosts: [sdw1, sdw2, sdw3]
 
 Please specify 2 locations for the primary segments, one per line:
 primary location 1> /ssd1/gpdata/segdata_p1
 Warning: the RSA host key for 'sdw3' differs from the key for the IP address '10.102.254.26'
 Offending key for IP in /home/gpadmin/.ssh/known_hosts:10
 Matching host key in /home/gpadmin/.ssh/known_hosts:6
 Are you sure you want to continue connecting (yes/no)? yes
 primary location 2> /ssd1/gpdata/segdata_p2               
 Warning: the RSA host key for 'sdw3' differs from the key for the IP address '10.102.254.26'
 Offending key for IP in /home/gpadmin/.ssh/known_hosts:10
 Matching host key in /home/gpadmin/.ssh/known_hosts:6
 Are you sure you want to continue connecting (yes/no)? yes
 
 Please specify 2 locations for the mirror segments, one per line:
 mirror location 1> /ssd1/gpdata/segdata_m1
 Warning: the RSA host key for 'sdw3' differs from the key for the IP address '10.102.254.26'
 Offending key for IP in /home/gpadmin/.ssh/known_hosts:10
 Matching host key in /home/gpadmin/.ssh/known_hosts:6
 Are you sure you want to continue connecting (yes/no)? yes
 mirror location 2> /ssd1/gpdata/segdata_m2
 Warning: the RSA host key for 'sdw3' differs from the key for the IP address '10.102.254.26'
 Offending key for IP in /home/gpadmin/.ssh/known_hosts:10
 Matching host key in /home/gpadmin/.ssh/known_hosts:6
 Are you sure you want to continue connecting (yes/no)? yes
 
 Configuring hosts: [smdw, mdw]
 
 Enter a file system location for the master
 master location> /ssd1/gpdata/master_p
 20200504:16:33:30:024112 gpfilespace:mdw:gpadmin-[INFO]:-Creating configuration file...
 20200504:16:33:30:024112 gpfilespace:mdw:gpadmin-[INFO]:-[created]
 20200504:16:33:30:024112 gpfilespace:mdw:gpadmin-[INFO]:-
 To add this filespace to the database please run the command:
    gpfilespace --config /home/gpadmin/gpfilespace_config
 
 [gpadmin@mdw ~]$

2.5 按照相关命令进行创建文件目录

 [gpadmin@mdw ~]$ more /home/gpadmin/gpfilespace_config
 filespace:ssd1
 mdw:1:/ssd1/gpdata/master_p/gpseg-1
 smdw:14:/ssd1/gpdata/master_p/gpseg-1
 sdw1:2:/ssd1/gpdata/segdata_p1/gpseg0
 sdw1:3:/ssd1/gpdata/segdata_p2/gpseg1
 sdw1:12:/ssd1/gpdata/segdata_m1/gpseg4
 sdw1:13:/ssd1/gpdata/segdata_m2/gpseg5
 sdw2:4:/ssd1/gpdata/segdata_p1/gpseg2
 sdw2:5:/ssd1/gpdata/segdata_p2/gpseg3
 sdw2:8:/ssd1/gpdata/segdata_m1/gpseg0
 sdw2:9:/ssd1/gpdata/segdata_m2/gpseg1
 sdw3:6:/ssd1/gpdata/segdata_p1/gpseg4
 sdw3:7:/ssd1/gpdata/segdata_p2/gpseg5
 sdw3:10:/ssd1/gpdata/segdata_m1/gpseg2
 sdw3:11:/ssd1/gpdata/segdata_m2/gpseg3
 
 [gpadmin@mdw ~]$  gpfilespace --config /home/gpadmin/gpfilespace_config
 20200504:16:37:30:024649 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.
 
 
 20200504:16:37:30:024649 gpfilespace:mdw:gpadmin-[INFO]:-getting config
 Reading Configuration file: '/home/gpadmin/gpfilespace_config'
 20200504:16:37:30:024649 gpfilespace:mdw:gpadmin-[INFO]:-Performing validation on paths
 ...............................................................Warning: the RSA host key for 'sdw3' differs from the key for the IP address '10.102.254.26'
 Offending key for IP in /home/gpadmin/.ssh/known_hosts:10
 Matching host key in /home/gpadmin/.ssh/known_hosts:6
 Are you sure you want to continue connecting (yes/no)? yes
 ...............
 
 20200504:16:37:34:024649 gpfilespace:mdw:gpadmin-[INFO]:-Connecting to database
 20200504:16:37:34:024649 gpfilespace:mdw:gpadmin-[INFO]:-Filespace "ssd1" successfully created
 archdata=# select * from pg_filespace;
   fsname   | fsowner 
 -----------+---------
  pg_system |      10
  ssd1      |      10
 (2 rows)
 
 archdata=#

3 创建表空间

 archdata=# select * from pg_filespace;
   fsname   | fsowner 
 -----------+---------
  pg_system |      10
  ssd1      |      10
 (2 rows)
 
 archdata=# 
 
 
 =# CREATE TABLESPACE myspace FILESPACE myfilespace;
 超级用户(gpadmin)定义一个表空间，使用GRANT CREATE命令授权给普通的数据库用户，比如：
 
 =# GRANT CREATE ON TABLESPACE myspace TO admin;
 
 
 
 
 3.1 创建表空间
 
 create tablespace tbs1 filespace ssd1;
 
 3.2 修改role的默认表空间
 
  alter role yanwei set default_tablespace='tbs1';
  
 3.3 制定表空间来创建数据库
 archdata=# create database testdb tablespace tbs1;
 CREATE DATABASE
 archdata=# 
 
 3.4 查询select datname,datdba,dattablespace from pg_database;
 
 archdata=# select datname,datdba,dattablespace from pg_database;
   datname  | datdba | dattablespace 
 -----------+--------+---------------
  template1 |     10 |          1663
  template0 |     10 |          1663
  postgres  |     10 |          1663
  archdata  |     10 |          1663
  gpperfmon |     10 |          1663
  testdb    |     10 |         17554
 (6 rows)
 
 archdata=#

4 相关查询

 查询database的表空间
 archdata=# select datname,datdba,dattablespace from pg_database;
   datname  | datdba | dattablespace 
 -----------+--------+---------------
  template1 |     10 |          1663
  template0 |     10 |          1663
  postgres  |     10 |          1663
  archdata  |     10 |          1663
  gpperfmon |     10 |          1663
  testdb    |     10 |         17554
 (6 rows)
 
 
 查询表空间
 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;
 archdata=# SELECT spcname as tblspc, fsname as filespc,
 archdata-# fsedbid as seg_dbid, fselocation as datadir
 archdata-# FROM pg_tablespace pgts, pg_filespace pgfs,
 archdata-# pg_filespace_entry pgfse
 archdata-# WHERE pgts.spcfsoid=pgfse.fsefsoid
 archdata-# AND pgfse.fsefsoid=pgfs.oid
 archdata-# ORDER BY tblspc, seg_dbid;
    tblspc   |  filespc  | seg_dbid |              datadir              
 ------------+-----------+----------+-----------------------------------
  pg_default | pg_system |        1 | /greenplum/gpdata/master/gpseg-1
  pg_default | pg_system |        2 | /greenplum/gpdata/primary1/gpseg0
  pg_default | pg_system |        3 | /greenplum/gpdata/primary2/gpseg1
  pg_default | pg_system |        4 | /greenplum/gpdata/primary1/gpseg2
  pg_default | pg_system |        5 | /greenplum/gpdata/primary2/gpseg3
  pg_default | pg_system |        6 | /greenplum/gpdata/primary1/gpseg4
  pg_default | pg_system |        7 | /greenplum/gpdata/primary2/gpseg5
  pg_default | pg_system |        8 | /greenplum/gpdata/mirror1/gpseg0
  pg_default | pg_system |        9 | /greenplum/gpdata/mirror2/gpseg1
  pg_default | pg_system |       10 | /greenplum/gpdata/mirror1/gpseg2
  pg_default | pg_system |       11 | /greenplum/gpdata/mirror2/gpseg3
  pg_default | pg_system |       12 | /greenplum/gpdata/mirror1/gpseg4
  pg_default | pg_system |       13 | /greenplum/gpdata/mirror2/gpseg5
  pg_default | pg_system |       14 | /greenplum/gpdata/master/gpseg-1
  pg_global  | pg_system |        1 | /greenplum/gpdata/master/gpseg-1
  pg_global  | pg_system |        2 | /greenplum/gpdata/primary1/gpseg0
  pg_global  | pg_system |        3 | /greenplum/gpdata/primary2/gpseg1
  pg_global  | pg_system |        4 | /greenplum/gpdata/primary1/gpseg2
  pg_global  | pg_system |        5 | /greenplum/gpdata/primary2/gpseg3
  pg_global  | pg_system |        6 | /greenplum/gpdata/primary1/gpseg4
  pg_global  | pg_system |        7 | /greenplum/gpdata/primary2/gpseg5
  pg_global  | pg_system |        8 | /greenplum/gpdata/mirror1/gpseg0
  pg_global  | pg_system |        9 | /greenplum/gpdata/mirror2/gpseg1
  pg_global  | pg_system |       10 | /greenplum/gpdata/mirror1/gpseg2
  pg_global  | pg_system |       11 | /greenplum/gpdata/mirror2/gpseg3
  pg_global  | pg_system |       12 | /greenplum/gpdata/mirror1/gpseg4
  pg_global  | pg_system |       13 | /greenplum/gpdata/mirror2/gpseg5
  pg_global  | pg_system |       14 | /greenplum/gpdata/master/gpseg-1
  tbs1       | ssd1      |        1 | /ssd1/gpdata/master_p/gpseg-1
  tbs1       | ssd1      |        2 | /ssd1/gpdata/segdata_p1/gpseg0
  tbs1       | ssd1      |        3 | /ssd1/gpdata/segdata_p2/gpseg1
  tbs1       | ssd1      |        4 | /ssd1/gpdata/segdata_p1/gpseg2
  tbs1       | ssd1      |        5 | /ssd1/gpdata/segdata_p2/gpseg3
  tbs1       | ssd1      |        6 | /ssd1/gpdata/segdata_p1/gpseg4
  tbs1       | ssd1      |        7 | /ssd1/gpdata/segdata_p2/gpseg5
  tbs1       | ssd1      |        8 | /ssd1/gpdata/segdata_m1/gpseg0
  tbs1       | ssd1      |        9 | /ssd1/gpdata/segdata_m2/gpseg1
  tbs1       | ssd1      |       10 | /ssd1/gpdata/segdata_m1/gpseg2
  tbs1       | ssd1      |       11 | /ssd1/gpdata/segdata_m2/gpseg3
  tbs1       | ssd1      |       12 | /ssd1/gpdata/segdata_m1/gpseg4
  tbs1       | ssd1      |       13 | /ssd1/gpdata/segdata_m2/gpseg5
  tbs1       | ssd1      |       14 | /ssd1/gpdata/master_s/gpseg-1
  
  
  
  select * from pg_tablespace ;
  
  select * from pg_filespace;
  
  archdata=# 
 archdata=#  select * from pg_tablespace ;
   spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid 
 ------------+----------+-------------+--------+-----------------+-----------------+----------
  pg_default |       10 |             |        |                 |                 |     3052
  pg_global  |       10 |             |        |                 |                 |     3052
  tbs1       |       10 |             |        |                 |                 |    17553
 (3 rows)
 
 archdata=#  select * from pg_filespace;
   fsname   | fsowner 
 -----------+---------
  pg_system |      10
  ssd1      |      10
 (2 rows)
  
  
  
  
  
  使用DROP TABLESPACE tablespacename 命令删除一个空的表空间
 
 DROP TABLESPACE tbs1
 
 archdata=# DROP TABLESPACE tbs1;
 ERROR:  tablespace "tbs1" is not empty
 select oid,datname from pg_database where datname = 'testdb'
 archdata=# select oid,datname from pg_database where datname = 'testdb';
   oid  | datname 
 -------+---------
  17555 | testdb
 (1 row)
 
 archdata=# 
 
 /ssd1/gpdata/master_p/gpseg-1
 [gpadmin@mdw gpseg-1]$ ls
 17554
 [gpadmin@mdw gpseg-1]$ 
 
 archdata=# drop database testdb;
 DROP DATABASE
 archdata=# DROP TABLESPACE tbs1;
 DROP TABLESPACE
 archdata=# 
 
 使用DROP FILESPACE filespacename 删除一个空的文件空间
 
 DROP FILESPACE myfilespace

5 临时文件和事务文件的迁移

 1. 将temp , trans移动到这个新的filespace.
 
  gpfilespace --showtransfilespace
 
  gpfilespace --showtempfilespace 
 
 $gpfilespace --movetempfilespace ssd1
 
 
 20200504:16:56:26:025483 gpfilespace:mdw:gpadmin-[INFO]:-Database might already be stopped.
 20200504:16:56:26:025483 gpfilespace:mdw:gpadmin-[INFO]:-Starting Greenplum Database in master only mode
 20200504:16:56:28:025483 gpfilespace:mdw:gpadmin-[INFO]:-Checking if filespace ssd1 exists
 20200504:16:56:28:025483 gpfilespace:mdw:gpadmin-[INFO]:-Checking if filespace is same as current filespace
 20200504:16:56:28:025483 gpfilespace:mdw:gpadmin-[INFO]:-Stopping Greenplum Database in master only mode
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Checking for connectivity
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace information
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining segment information ...
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Creating RemoteOperations list
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Moving TEMPORARY_FILES filespace from pg_system to ssd1 ...
 20200504:16:56:29:025483 gpfilespace:mdw:gpadmin-[INFO]:-Starting Greenplum Database
 
 
 [gpadmin@mdw gpseg-1]$  gpfilespace --showtempfilespace 
 20200504:16:57:20:026062 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.
 
 
 20200504:16:57:20:026062 gpfilespace:mdw:gpadmin-[INFO]:-Getting filespace information for TEMPORARY_FILES
 20200504:16:57:20:026062 gpfilespace:mdw:gpadmin-[INFO]:-Checking for filespace consistency
 20200504:16:57:20:026062 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:57:20:026062 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES OIDs are consistent for ssd1 filespace
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES entries are consistent for ssd1 filespace
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-Current Filespace for TEMPORARY_FILES is ssd1
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-1    /ssd1/gpdata/master_p/gpseg-1
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-14    /ssd1/gpdata/master_s/gpseg-1
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-2    /ssd1/gpdata/segdata_p1/gpseg0
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-8    /ssd1/gpdata/segdata_m1/gpseg0
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-3    /ssd1/gpdata/segdata_p2/gpseg1
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-9    /ssd1/gpdata/segdata_m2/gpseg1
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-4    /ssd1/gpdata/segdata_p1/gpseg2
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-10    /ssd1/gpdata/segdata_m1/gpseg2
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-5    /ssd1/gpdata/segdata_p2/gpseg3
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-11    /ssd1/gpdata/segdata_m2/gpseg3
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-6    /ssd1/gpdata/segdata_p1/gpseg4
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-12    /ssd1/gpdata/segdata_m1/gpseg4
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-7    /ssd1/gpdata/segdata_p2/gpseg5
 20200504:16:57:21:026062 gpfilespace:mdw:gpadmin-[INFO]:-13    /ssd1/gpdata/segdata_m2/gpseg5
 [gpadmin@mdw gpseg-1]$ 
 
 
 
 
 
 在迁移回来pg_system
 gpfilespace --movetempfilespace pg_system 
 [gpadmin@mdw gpseg-1]$ gpfilespace --movetempfilespace pg_system 
 20200504:16:57:46:026230 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.
 
 
 20200504:16:57:46:026230 gpfilespace:mdw:gpadmin-[INFO]:-Database was started in NORMAL mode
 20200504:16:57:46:026230 gpfilespace:mdw:gpadmin-[INFO]:-Stopping Greenplum Database
 20200504:16:57:53:026230 gpfilespace:mdw:gpadmin-[INFO]:-Starting Greenplum Database in master only mode
 20200504:16:57:54:026230 gpfilespace:mdw:gpadmin-[INFO]:-Checking if filespace pg_system exists
 20200504:16:57:54:026230 gpfilespace:mdw:gpadmin-[INFO]:-Checking if filespace is same as current filespace
 20200504:16:57:54:026230 gpfilespace:mdw:gpadmin-[INFO]:-Stopping Greenplum Database in master only mode
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Checking for connectivity
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace information
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining segment information ...
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Creating RemoteOperations list
 20200504:16:57:55:026230 gpfilespace:mdw:gpadmin-[INFO]:-Moving TEMPORARY_FILES filespace from ssd1 to pg_system ...
 20200504:16:57:56:026230 gpfilespace:mdw:gpadmin-[INFO]:-Starting Greenplum Database
 [gpadmin@mdw gpseg-1]$ 
 
 
 
 
 [gpadmin@mdw gpseg-1]$ gpfilespace --showtempfilespace 
 20200504:16:58:20:026698 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.
 
 
 20200504:16:58:20:026698 gpfilespace:mdw:gpadmin-[INFO]:-Getting filespace information for TEMPORARY_FILES
 20200504:16:58:20:026698 gpfilespace:mdw:gpadmin-[INFO]:-Checking for filespace consistency
 20200504:16:58:20:026698 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:58:20:026698 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES OIDs are consistent for pg_system filespace
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-TEMPORARY_FILES entries are consistent for pg_system filespace
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-Current Filespace for TEMPORARY_FILES is pg_system
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-1    /greenplum/gpdata/master/gpseg-1
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-14    /greenplum/gpdata/master/gpseg-1
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-2    /greenplum/gpdata/primary1/gpseg0
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-8    /greenplum/gpdata/mirror1/gpseg0
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-3    /greenplum/gpdata/primary2/gpseg1
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-9    /greenplum/gpdata/mirror2/gpseg1
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-4    /greenplum/gpdata/primary1/gpseg2
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-10    /greenplum/gpdata/mirror1/gpseg2
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-5    /greenplum/gpdata/primary2/gpseg3
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-11    /greenplum/gpdata/mirror2/gpseg3
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-6    /greenplum/gpdata/primary1/gpseg4
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-12    /greenplum/gpdata/mirror1/gpseg4
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-7    /greenplum/gpdata/primary2/gpseg5
 20200504:16:58:21:026698 gpfilespace:mdw:gpadmin-[INFO]:-13    /greenplum/gpdata/mirror2/gpseg5
 [gpadmin@mdw gpseg-1]$ 
 
 同样的道理来进行迁移事务文件测试即可
 
 2.$gpfilespace --movetransfilespace ssd1