PostgreSQL 表空间

数据库集群初始化时会自动创建两个表空间：

1. pg_global ：用于共享系统目录。
2. pg_default ：是 template1 和 template0 数据库的默认表空间，如果没有给出 TABLESPACE 子句，则用于在数据库内创建的表、索引和临时文件的默认表空间。每当您在创建表/数据库而未在创建表语句中指定表空间时，它将转到 pg_default 表空间。

表空间创建的语法：

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

在创建表空间之前，您必须创建操作系统级目录，并且必须将目录所有者权限更改为 postgres 用户

1.creating tbs1 directory:
cd /tab1/
mkdir tbs1

2.changing tbs1 permission to postgres user:
chown -R postgres:postgres tbs1

3.creating tablespace with name of  tbs1 ,tbs1 is a logical name you can change  whatever you want:
CREATE TABLESPACE tbs1 LOCATION '/tab1/tbs1';  #创建表空间

4.Listing postgresql tablespace:
postgres=# \db+                    #列出表空间
                                   List of tablespaces
    Name    |  Owner   |  Location  | Access privileges | Options |  Size   | Description
------------+----------+------------+-------------------+---------+---------+-------------
 pg_default | postgres |            |                   |         | 23 MB   |
 pg_global  | postgres |            |                   |         | 573 kB  |
 tbs1       | postgres | /tab1/tbs1 |                   |         | 0 bytes |
(3 rows)

创建有/无表空间的表：
大多数“CREATE”SQL 命令都带有“TABLESPACE”选项，您可以使用该选项指定要在其中创建该 SQL 对象的表空间。让我们尝试几个：

postgres=# create database dbname2 tablespace tbs1;   #创建数据库dbname2,指定默认表空间
CREATE DATABASE
postgres=# \c dbname2
You are now connected to database "dbname2" as user "nijam".
dbname2=# create table t1 (a int);
CREATE TABLE
dbname2=# create table t2 (a int) tablespace tbs1;
CREATE TABLE
dbname2=# create table t3 (a int) tablespace tbs2;
CREATE TABLE

 如果您想知道组成表的文件的确切位置，可以使用 oid2name：
oid2name是一个实用程序，可帮助管理员检查 PostgreSQL 使用的文件结构。

$ oid2name -t t4
From database "postgres":
  Filenode  Table Name
----------------------
     24592          t4

$ find $PGDATA -name 2459*
/u02/pgdata/PG961/base/13322/24592

此外，oid2name 还会告诉您有关数据库及其关联的默认表空间的更多信息：

$ oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

There are three main patterns paths in Tablespace:

• 1.For files in the default tablespace: base/database_oid/table_and_index_files_oid
• 2.For files in Non-default tablespace:The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid /table_and_index_files_oid
• 3.For shared relations (see below): global/table_and_index_files_oid

postgres=#\! ls -l /u02/tbs1/
total 0
drwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131

表空间的基本命令：
确定现有表空间的集合：
select oid,spcname from pg_tablespace;

下面的元命令对于列出现有表空间也很有用：
 \db+

表空间重命名：
alter tablespace tbs1 rename to tbs3;

更改表空间所有权：
alter tablespace tbs1 Owner to scott;

表空间重置：
alter tablepace tbs1 reset default_tablespace;

表空间删除：
drop tablespace tbs1;
注意：直到所有使用表空间的数据库中的所有对象都被删除后，才能删除表空间。

为特定用户分配默认表空间：
ALTER ROLE someuser SET default_tablespace = tbs1;

表空间占用的磁盘空间：
select pg_size_pretty(pg_tablespace_size('tbs1'));
              （或）
/u02/tbs1/du -c -h

在使用创建一批表时暂时用于当前会话

 SET default_tablespace = tbs2;

更改整个实例的默认表空间：
所有新创建的对象都进入新的表空间。

postgres=#  alter system set default_tablespace='tbs3';
ALTER SYSTEM
 
postgres=#  select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
 
postgres=#  show default_tablespace ;
 default_tablespace 
--------------------
 tbs3
(1 row)

How to find what tablespace a table/index is in on PostgreSQL?
For table:

SELECT tablespace FROM pg_tables WHERE tablename = 't1' AND schemaname = 'schema1';