目录

pg_tablespace

一个包含表空间信息的系统目录表

pg_tablespace 是一个包含表空间信息的系统目录表。

pg_tablespace 在 PostgreSQL 8.2 中被添加。

该表在集群的所有数据库之间共享。

psql 命令

  • \db 列出集群可用表空间的基本信息
  • \db+ 显示额外信息，例如大小。

按 PostgreSQL 版本定义

pg_tablespace (PostgreSQL 19)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 18)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 17)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 16)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 15)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 14)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 13)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 12)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null | 
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    | C         |          | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 11)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    |           |          | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 10)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default 
------------+-----------+-----------+----------+---------
 spcname    | name      |           | not null | 
 spcowner   | oid       |           | not null | 
 spcacl     | aclitem[] |           |          | 
 spcoptions | text[]    |           |          | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.6)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.5)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.4)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.3)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.2)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.1)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      | 
 spcacl      | aclitem[] | 
 spcoptions  | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 9.0)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      | 
 spcacl      | aclitem[] | 
 spcoptions  | text[]    | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 8.4)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      | 
 spcacl      | aclitem[] | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 8.3)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      | 
 spcacl      | aclitem[] | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

pg_tablespace (PostgreSQL 8.2)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      | 
 spcacl      | aclitem[] | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_tablespace

变更历史

示例

postgres=# SELECT * FROM pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions
------------+----------+--------+------------
 pg_default |       10 | ¤      | ¤
 pg_global  |       10 | ¤      | ¤
 space_2    |       10 | ¤      | ¤
 space_1    |       10 | ¤      | ¤
(4 rows)

postgres=# \db
                        List of tablespaces
    Name    |  Owner   |                  Location
------------+----------+--------------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 space_1    | postgres | /var/lib/pgsql/tblspc/tblspc_dir1
 space_2    | postgres | /var/lib/pgsql/tblspc/tblspc_dir2
(4 rows)

postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |             Location              | Access privileges | Options |  Size   | Description
------------+----------+-----------------------------------+-------------------+---------+---------+-------------
 pg_default | postgres |                                   |                   |         | 29 MB   |
 pg_global  | postgres |                                   |                   |         | 590 kB  |
 space_1    | postgres | /var/lib/pgsql/tblspc/tblspc_dir1 |                   |         | 0 bytes |
 space_2    | postgres | /var/lib/pgsql/tblspc/tblspc_dir2 |                   |         | 0 bytes |
(4 rows)

分类

集群范围表, 存储, 系统目录, 表空间

另请参阅

表空间, pg_tablespace_location(), pg_tablespace_size(), pg_tablespace_databases(), CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE

反馈

提交关于“pg_tablespace”的任何评论、建议或更正，请在此处提交。