7.PostgreSQL的表空间

DBA随笔记 2024-10-30

494

在 PostgreSQL 中，表空间（Tablespace）是一个可以用于存储数据库对象（如表、索引）的文件系统位置。PostgreSQL的表空间让你可以将数据库对象分散存储在不同的系统目录中，以优化磁盘I/O、管理数据存放、更好地进行性能调优，以及灵活地管理磁盘空间。

创建表空间

使用 CREATE TABLESPACE 命令来创建一个新的表空间。你需要指定表空间的名称以及其对应的操作系统目录。目录必须由数据库超级用户所有，并且 PostgreSQL 服务器进程必须对该目录具有写权限。

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]
postgres=# create tablespace test1 location '/postgresql/tblspc';
CREATE TABLESPACE
postgres=#

对于用户创建的表空间，相当于一个对应的目录，在创建完一个表空间后，会在表空间的根目录下生成带有“PG_Major version_Catalog version”的子目录

[postgres@pgserver tblspc]$ ls -l postgresql/tblspc
total 0
drwx------ 2 postgres postgres 6 Oct 31 20:28 PG_16_202307071


Catalog version可以通过pg_controldata命令查询
[postgres@pgserver tblspc]$ pg_controldata |grep "Catalog version number"
Catalog version number:               202307071

查看创建好的表空间

postgres=# \db
            List of tablespaces
    Name    |  Owner   |      Location
------------+----------+--------------------
 pg_default | postgres |
 pg_global  | postgres |
 test1      | postgres | postgresql/tblspc
(3 rows)


postgres=# \d pg_TABLESPACE
            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"


postgres=# SELECT oid,spcname AS tablespace_name, pg_catalog.pg_tablespace_location(oid) AS location FROM pg_catalog.pg_tablespace WHERE spcname = 'test1';
  oid  | tablespace_name |      location
-------+-----------------+--------------------
 16391 | test1           | postgresql/tblspc
(1 row)

同时会在PGDATA下pg_tblspc 生成一个软链接

[postgres@pgserver tblspc]$ cd $PGDATA/pg_tblspc
[postgres@pgserver pg_tblspc]$ ll
total 0
lrwxrwxrwx 1 postgres postgres 18 Oct 31 20:28 16391 -> /postgresql/tblspc

使用表空间

创建表空间之后，可以在创建表或索引时指定表空间，或者将现有的表或索引迁移到新的表空间。

创建表t1，并指定表空间test1
postgres=# create table t1 (id int) tablespace test1;
CREATE TABLE
postgres=# select tablename,tablespace from pg_catalog.pg_tables where tablename='t1';
 tablename | tablespace
-----------+------------
 t1        | test1
(1 row)


postgres=# insert into t1(id) values('1');
INSERT 0 1
postgres=# insert into t1(id) values('2');
INSERT 0 1
postgres=# insert into t1(id) values('3');
INSERT 0 1
postgres=# select * from t1;
 id
----
  1
  2
  3
(3 rows)


创建索引idx_t1，并指定表空间test1
postgres=# create index idx_t1 on t1 (id) tablespace test1;
CREATE INDEX


postgres=# create tablespace new_sx location '/postgresql/tblspc_sx';
CREATE TABLESPACE
postgres=#


移动现有表t1到新表空间new_sx 
postgres=# alter table t1 set tablespace new_sx;
ALTER TABLE
postgres=# select tablename,tablespace from pg_catalog.pg_tables where tablename='t1';
 tablename | tablespace
-----------+------------
 t1        | new_sx
(1 row)


移动现有索引idx_t1到新表空间new_sx
postgres=# alter index idx_t1 set tablespace new_sx;
ALTER INDEX
postgres=# select tablename,indexname,tablespace from pg_catalog.pg_indexes where tablename='t1';
 tablename | indexname | tablespace
-----------+-----------+------------
 t1        | idx_t1    | new_sx
(1 row)


postgres=# drop tablespace test1;
DROP TABLESPACE

更多关于PostgreSQL 系列的学习文章后期持续更新中，下期见。

表空间 postgresql

文章转载自DBA随笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

7.PostgreSQL的表空间

评论