HAWQ version

postgres=# select version();
                                                                                                                    version

-------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.15 (OushuDB 4.1.0.0 build 25055 Enterprise Edition) 
 (Apache HAWQ 2.4.0.0) (Greenplum Database 4.2.0) on x86_64-unknown-linux-gnu, compiled by
GCC clang version 8.0.1 (tags/RELEASE_801/final) compiled on Sep 14 2020 04:56:54
(1 row)

实验前文件空间和表空间情况

postgres=# select oid,* from pg_filespace;
  oid  |   fsname   | fsowner | fsfsys | fsrep
-------+------------+---------+--------+-------
  3052 | pg_system  |      10 |      0 |     0
 16384 | dfs_system |      10 |  10932 |     0
(2 rows)

postgres=# select * from pg_filespace_entry ;
 fsefsoid | fsedbid |             fselocation
----------+---------+-------------------------------------
    16384 |       0 | hdfs://oushu/hawq/default_filespace
(1 row)

postgres=# select * from pg_tablespace ;
   spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid
-------------+----------+-------------+--------+-----------------+-----------------+----------
 pg_default  |       10 |             |        |                 |                 |     3052
 pg_global   |       10 |             |        |                 |                 |     3052
 dfs_default |       10 |             |        |                 |                 |    16384
(3 rows)

创建对应的文件空间存储目录

hdfs dfs -mkdir /hawq_fpc01
hdfs dfs -chown -R gpadmin:hadoop /hawq_fpc01
hdfs dfs -ls /

创建文件空间和表空间

CREATE FILESPACE hawq_fpc01 ON hdfs ('oushu/hawq_fpc01') WITH (NUMREPLICA = 3);

create TABLESPACE hawq_tpc01 FILESPACE hawq_fpc01;

实验后文件空间和表空间情况

postgres=# select oid,* from pg_filespace;select * from pg_filespace_entry ;select * from pg_tablespace ;
  oid  |   fsname   | fsowner | fsfsys | fsrep
-------+------------+---------+--------+-------
  3052 | pg_system  |      10 |      0 |     0
 16384 | dfs_system |      10 |  10932 |     0
 22813 | hawq_fpc01 |      10 |  10932 |     3
(3 rows)

 fsefsoid | fsedbid |             fselocation
----------+---------+-------------------------------------
    16384 |       0 | hdfs://oushu/hawq/default_filespace
    22813 |       0 | hdfs://{replica=3}oushu/hawq_fpc01
(2 rows)

   spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid
-------------+----------+-------------+--------+-----------------+-----------------+----------
 pg_default  |       10 |             |        |                 |                 |     3052
 pg_global   |       10 |             |        |                 |                 |     3052
 dfs_default |       10 |             |        |                 |                 |    16384
 hawq_tpc01  |       10 |             |        |                 |                 |    22813
(4 rows)

测试前表和数据准备

create table tbs_hawq_tpc01(id int) tablespace hawq_tpc01;
create table tbs_default(id int) tablespace dfs_default;

postgres=# insert into tbs_hawq_tpc01 select generate_series(1,1000);
INSERT 0 1000
postgres=# insert into tbs_default select generate_series(1,1000);
INSERT 0 1000

测试跨文件空间和表空间表的关联

postgres=# select count(*) from tbs_hawq_tpc01 a left join tbs_default b on a.id=b.id;
 count
-------
  1000
(1 row)

校验表实际的存储位置

postgres=# select oid::regclass,reltablespace from pg_class where oid in ('public.tbs_hawq_tpc01'::regclass,'public.tbs_default'::regclass);
      oid       | reltablespace
----------------+---------------
 tbs_hawq_tpc01 |         22814
 tbs_default    |             0
(2 rows)

postgres=# select oid,* from pg_tablespace ;
  oid  |   spcname   | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid
-------+-------------+----------+-------------+--------+-----------------+-----------------+----------
  1663 | pg_default  |       10 |             |        |                 |                 |     3052
  1664 | pg_global   |       10 |             |        |                 |                 |     3052
 16385 | dfs_default |       10 |             |        |                 |                 |    16384
 22814 | hawq_tpc01  |       10 |             |        |                 |                 |    22813
(4 rows)

postgres=#