PG-表空间管理

2.5 表空间

PostgreSQL中的表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。一旦被创建，表空间就可以在创建数据库对象时通过名称引用。

表空间也是数据库集簇的一部分 并且不能被视作数据文件的一个自治集合。 它们依赖于包含在主数据目录中的元数据，并且因此不能被附加到一个不同的数据库集簇或者单独备份。

当初始化数据库集簇（$PGDATA）时，会自动创建两个表空间。pg_global表空间被用于共享系统目录。pg_default表空间是template1和template0数据库的默认表空间。$PGDATA/pg_tblspc目录包含指向集簇中定义的每个非内建表空间的符号连接。

2.5.1 创建表空间

表空间创建步骤

• 在服务器上创建文件系统目录
• 修改目录权限
• 数据库上创建表空间

-- 格式
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

位置必须是一个已有的空目录，并且属于PostgreSQL操作系统用户

# 1. FS上创建目录
mkdir -p /ups/data/pgdata/devtbs
chown postgres:postgres /ups/data/pgdata/devtbs
chmod 700 /ups/data/pgdata/devtbs

# 2. psql上创建表空间
create tablespace "devtbs" location '/ups/data/pgdata/devtbs';

# 3. create dbs
create database "devdb" with owner = postgres encoding = 'UTF8' tablespace = devtbs;

# 4. show info
select oid, datname from pg_database;

2.5.2 临时表空间

temp_tablespaces参数决定临时表和索引的位置，以及用于大数据集排序、聚合等目的的临时文件的位置。 这可以是一个表空间名的列表，使用逗号隔开。因此，与临时对象有关的负载可以散布在多个表空间上。每次要创建一个临时对象时，将从列表中随机取一个成员来存放它。

创建表空间目录路径

mkdir -p /ups/data/pgdata/12/pg_tbs_tmp
chown postgres:postgres /ups/data/pgdata/12/pg_tbs_tmp
chmod 700 /ups/data/pgdata/12/pg_tbs_tmp

创建表空间

-- 创建表空间
CREATE TABLESPACE tbs_tmp LOCATION '/ups/data/pgdata/12/pg_tbs_tmp';

设置临时表空间

在session 级别设置临时表空间

set session temp_tablespaces='tbs_tmp';

修改配置文件参数 temp_tablespaces （永久）

vi $PGDATA/postgresql.conf
#temp_tablespaces = ''			# a list of tablespace names, '' uses


pg_ctl reload

查看临时表空间

show temp_tablespaces

2.5.3 删除表空间

语法

10:40:01 [local]:5432 postgres@postgres=# \h DROP TABLESPACE
Command:     DROP TABLESPACE
Description: remove a tablespace
Syntax:
DROP TABLESPACE [ IF EXISTS ] name

URL: https://www.postgresql.org/docs/12/sql-droptablespace.html

示例

drop tablespace if exists tbs_test;

查看表空间信息

元命令 \db[+]

10:19:58 [local]:5432 postgres@postgres=# \db+
                                           List of tablespaces
    Name    |  Owner   |          Location          | Access privileges | Options |  Size  | Description 
------------+----------+----------------------------+-------------------+---------+--------+-------------
 devtbs     | postgres | /ups/data/pgdata/12/pg_usr |                   |         | 53 MB  | 
 pg_default | postgres |                            |                   |         | 46 MB  | 
 pg_global  | postgres |                            |                   |         | 863 kB | 
(3 rows)

SQL 语句

select * from pg_catalog.pg_tablespace;

创建数据库对象到指定表空间

-- 1.1 
CREATE TABLE foo(i int) TABLESPACE sp1;

-- 1.2 指定默认表空间
SET default_tablespace = sp1;
CREATE TABLE foo(i int);