PostgreSQL的表空间

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:
  1. 表空间的概念
    PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。
  2. 表空间的作用
    官方解释

通过使用表空间,管理员可以控制一个PostgreSQL安装的磁盘布局。这么做至少有两个用处。

如果初始化集簇所在的分区或者卷用光了空间,而又不能在逻辑上扩展或者做别的什么操作,那么表空间可以被创建在一个不同的分区上,直到系统可以被重新配置。
表空间允许管理员根据数据库对象的使用模式来优化性能。例如,一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上,如一种非常贵的固态设备。同时,一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。
用一句话来讲:能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。

postgresql对表空间的说明很简单,基于两点理由:
扩展存储空间,跳出os对文件系统的大小限制,不断扩充可以空间
让数据库管理员针对系统的应用特点....优化系统的性能。
postgresql的tablespace就是让postgresq使用的专用os目录(路径)。并没有什么神秘的地方。在一个逻辑文件系统上建立多个表空间没有多少意义,因为你不能控制逻辑文件系统里的单个文件

create tablespace tbl_name location os_path;  --os_path必须是空的、postgresql帐号有权的目录。创建表空间的用户必须是superuser,创建完表空间之后,可以将表空间的create权限赋给普通用户使用!

指定表空间、默认表空间

表空间的使用对象:表、index、数据库:在创建这些对象时,可以显式的指定tablespace tals_name子句指示对象使用的表空间;
如果不指定,系统会使用当前有效的默认表空间。
类似的,临时表和临时表的index、内部临时表:也有默认的临时表空间。指定临时表空间系统参数时,可以指定多个表空间,postgresql会随机使用它们
initdb.exe初始化的两个默认表空间pg_global、pg_default
数据库默认的表空间pg_default 是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。他是模板数据库template0和template1的默认表空间
数据库默认的表空间pg_global是用来存储共享系统目录的默认空间
数据库默认表空间可以在create database是通过 tablespace tbs_anme 子句来改变
表空间是和单个数据库无关的,他被所有的数据库使用。因为,表空间只有没有任何对象使用时,才能drop掉

postgresql允许 通过符号链接 简化表空间的实施,那在不支持符号链接的os上就无法简化,只能显式的创建所需的表空间了!

  1. 表空间跟数据库关系
    在Oracle数据库中;一个表空间只属于一个数据库使用;而一个数据库可以拥有多个表空间。属于"一对多"的关系

在PostgreSQL集群中;一个表空间可以让多个数据库使用;而一个数据库可以使用多个表空间。属于"多对多"的关系。

  1. 系统自带表空间

表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/
表空间pg_global用来存放系统字典表;对应存储目录$PADATA/global/

  1. 查看表空间

postgres=# db

         List of tablespaces
Name    |  Owner   |      Location       
pg_default postgres
pg_global postgres
tp_lottu lottu /data/pg_data/lottu

(3 rows)

postgres=# select oid,* from pg_tablespace;

oid spcname spcowner spcacl spcoptions
1663 pg_default 10
1664 pg_global 10
16385 tp_lottu 16384

(3 rows)

  1. 创建表空间

Syntax:

CREATE TABLESPACE tablespace_name [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ] LOCATION 'directory'

示例如下:

postgres=# c lottu postgres
You are now connected to database "lottu" as user "postgres".
lottu=# CREATE TABLESPACE tsp01 OWNER lottu LOCATION '/data/pg_data/tsp';
CREATE TABLESPACE

目录"/data/pg_data/tsp"必须是一个已有的空目录,并且属于PostgreSQL操作系统用户

$ mkdir -p /data/pg_data/tsp
$ chown -R postgres:postgres /data/pg_data/tsp

  1. 权限分配
    表空间的创建本身必须作为一个数据库超级用户完成,但在创建完之后之后你可以允许普通数据库用户来使用它.要这样做,给数据库普通用户授予表空间上的CREATE权限。表、索引和整个数据库都可以被分配到特定的表空间.

示例用户"rax":为普通用户。

lottu=# c lottu01 rax
You are now connected to database "lottu01" as user "rax".
lottu01=> create table test_tsp(id int) tablespace tsp01;
ERROR: permission denied for tablespace tsp01
lottu01=> c lottu01 postgres
You are now connected to database "lottu01" as user "postgres".
lottu01=# GRANT CREATE ON TABLESPACE tsp01 TO rax;
GRANT
lottu01=# c lottu01 rax
You are now connected to database "lottu01" as user "rax".
lottu01=> create table test_tsp(id int) tablespace tsp01;
CREATE TABLE

  1. 为数据库指定默认表空间
    ALTER DATABASE name SET TABLESPACE new_tablespace

以数据库lottu01为例:

ALTER DATABASE lottu01 SET TABLESPACE tsp01;
lottu01=> c lottu01 lottu
You are now connected to database "lottu01" as user "lottu".

注意1:执行该操作;不能连着对应数据库操作
lottu01=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR: cannot change the tablespace of the currently open database
lottu01=# c postgres postgres
You are now connected to database "postgres" as user "postgres".
注意2:执行该操作;对应的数据库不能存在表或者索引已经指定默认的表空间

postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR: some relations of database "lottu01" are already in tablespace "tsp01"
HINT: You must move them back to the database's default tablespace before using this command.
postgres=# c lottu01
You are now connected to database "lottu01" as user "postgres".
lottu01=# drop table test_tsp ;
DROP TABLE
lottu01=# create table test_tsp(id int);
CREATE TABLE
lottu01=# c postgres postgres
You are now connected to database "postgres" as user "postgres".
注意3:执行该操作;必须是没有人连着对应的数据库

postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ERROR: database "lottu01" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# ALTER DATABASE lottu01 SET TABLESPACE tsp01;
ALTER DATABASE
查看数据库默认表空间

lottu01=# select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='lottu01' and p.oid = d.dattablespace;

datname spcname
lottu01 tsp01

(1 row)

  1. 如何将表从一个表空间移到另一个表空间。
    我们知道表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。若没指定默认表空间;表就所属的表空间就是pg_default。"当然也可以通过参数设置"。而不是数据库默认的表空间。这个时候我们可以将表移到默认的表空间

Syntax:

ALTER TABLE name SET TABLESPACE new_tablespace
将表从一个表空间移到另一个表空间

lottu01=# create table test_tsp03(id int) tablespace tp_lottu;
CREATE TABLE
lottu01=# alter table test_tsp03 set tablespace tsp01;
ALTER TABLE
注意:该操作时会锁表。

  1. 临时表空间
    PostgreSQL的临时表空间,通过参数temp_tablespaces进行配置,PostgreSQL允许用户配置多个临时表空间。配置多个临时表空间时,使用逗号隔开。如果没有配置temp_tablespaces 参数,临时表空间对应的是默认的表空间pg_default。PostgreSQL的临时表空间用来存储临时表或临时表的索引,以及执行SQL时可能产生的临时文件例如排序,聚合,哈希等。为了提高性能,一般建议将临时表空间放在SSD或者IOPS,以及吞吐量较高的分区中。

$ mkdir -p /data/pg_data/temp_tsp
$ chown -R postgres:postgres /data/pg_data/temp_tsp
postgres=# CREATE TABLESPACE temp01 LOCATION '/data/pg_data/temp_tsp';
CREATE TABLESPACE
postgres=# show temp_tablespaces ;

temp_tablespaces

(1 row)
设置临时表空间

会话级生效
postgres=# set temp_tablespaces = 'temp01';
SET
永久生效
修改参数文件postgresql.conf
执行pg_ctl reload
[postgres@Postgres201 data]$ grep "temp_tablespace" postgresql.conf
temp_tablespaces = 'temp01' # a list of tablespace names, '' uses
查看临时表空间

postgres=# show temp_tablespaces ;

temp_tablespaces

temp01
(1 row)

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
13天前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL从小白到高手教程 - 第41讲:postgres表空间备份与恢复
PostgreSQL从小白到高手教程 - 第41讲:postgres表空间备份与恢复
149 1
|
10月前
|
存储 关系型数据库 Java
postgresql清理表空间
postgresql清理表空间
209 0
|
9月前
|
存储 关系型数据库 数据库
PostgreSQL 中,表空间
PostgreSQL 中,表空间
91 1
|
9月前
|
安全 关系型数据库 数据库
创建 PostgreSQL 表空间时没有指定空间的总大小
创建 PostgreSQL 表空间时没有指定空间的总大小
99 1
|
关系型数据库 数据库 PostgreSQL
PostgreSQL的DB在表空间之间迁移
Background The  /data/01 disk space is insufficient, but /data/02 is sufficient, so we migrate some data to /data/02.
1549 0
|
存储 大数据 关系型数据库
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 22 章 管理数据库_22.6. 表空间
22.6. 表空间 PostgreSQL中的表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。一旦被创建,表空间就可以在创建数据库对象时通过名称引用。 通过使用表空间,管理员可以控制一个PostgreSQL安装的磁盘布局。
1318 0
|
关系型数据库 PostgreSQL 数据库
|
6天前
|
关系型数据库 分布式数据库 数据库
【PolarDB 开源】PolarDB 性能调优实录:提升数据库集群吞吐量的技巧
【5月更文挑战第22天】PolarDB 性能调优关键点包括硬件资源配置、数据库参数调整、索引优化、分区策略、事务优化及性能监控。创建高效索引如`CREATE INDEX idx_name ON table_name (column_name);`,根据业务场景选择分区方式,调整事务隔离级别以提升并发性能。监控 CPU、内存等指标,定期维护数据库,结合业务特点综合调优,从而提升数据库集群吞吐量。这些技巧有助于发挥PolarDB潜力,支持业务高效运行。
111 5
|
1天前
|
SQL 关系型数据库 分布式数据库
【PolarDB开源】PolarDB Proxy配置与优化:提升数据库访问效率
【5月更文挑战第27天】PolarDB Proxy是阿里云PolarDB的高性能数据库代理,负责SQL请求转发和负载均衡。其关键配置包括:连接池管理(如最大连接数、空闲超时时间),负载均衡策略(轮询、权重轮询、一致性哈希),以及SQL过滤规则。优化方面,关注监控与调优、缓存策略、网络优化。通过这些措施,可提升数据库访问效率和系统稳定性。
28 1