PostgreSQL的表空间

1. 表空间的概念

PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。

2. 表空间的作用

官方解释


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

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

用一句话来讲:能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。

3. 表空间跟数据库关系

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

4. 系统自带表空间

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

5. 查看表空间

列出现有的表空间

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)

6. 创建表空间

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

7. 权限分配

表空间的创建本身必须作为一个数据库超级用户完成,但在创建完之后之后你可以允许普通数据库用户来使用它.要这样做,给数据库普通用户授予表空间上的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

8. 为数据库指定默认表空间

Syntax:

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)

9. 如何将表从一个表空间移到另一个表空间。

我们知道表空间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

注意:该操作时会锁表。

10. 临时表空间

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
  • 永久生效
  1. 修改参数文件postgresql.conf
  2. 执行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)
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,829评论 1 331
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,603评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,846评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,600评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,780评论 3 272
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,695评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,136评论 2 293
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,862评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,453评论 0 229
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,942评论 2 233
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,347评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,790评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,293评论 3 221
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,839评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,448评论 0 181
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,564评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,623评论 2 249

推荐阅读更多精彩内容

  • 关于Mongodb的全面总结 MongoDB的内部构造《MongoDB The Definitive Guide》...
    中v中阅读 31,766评论 2 89
  • About:PostgreSQL About 《PostgreSQL 源码分析系列》 PostgreSQL 源码分...
    ty4z2008阅读 8,020评论 1 40
  • 早起,卻沒有下床 下床就急著穿衣。 上班的路上,時刻不停的撥弄帽檐, 下了車,想了想還是要吃完早飯再出行, 吃著熱...
    魏魏yuan阅读 208评论 0 0
  • 07 当清晨的第一缕阳光撒进窗台,小真慢慢睁开惺忪的睡眼,看着窗外的蓝天白云回想起...
    佳佳的背包阅读 270评论 0 0
  • 绚烂如天的烟火, 恰似你美丽容颜。 划过天际的花火, 是我许下的心...
    风善阳阅读 178评论 0 8