postgresql 临时表_超详细的PG表空间布局设计、管理方式介绍

概述

PostgreSQL中的表空间实际上就是文件系统位置的一个目录，可以保存所有其他对象的容器，如表、索引等，有默认表空间、共享系统表表空间、自定义表空间。

一般PG DBA会规划一个存索引的表空间、存归档的表空间，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

DBA规划表空间就是合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。

---

一、Oracle VS PG 数据库的物理布局设计

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

在PostgreSQL集群中；每个数据库都会在$PGDATA/base下面生成一个子目录，文件名与数据库oid一一对应，一个表空间可以让多个数据库使用；数据库目录可以分布于多个表空间，也就是同一个数据库oid可以存在于不同表空间。属于"多对多"的关系。

---

二、Oracle VS PG系统自带表空间

1、Oracle自带系统表空间

1)system表空间

每个Oracle数据库都会有SYSTEM表空间，而且SYSTEM表空间总是要保持在联机模式下，因为其包含了数据库运行所要求的基本信息，如：数据字典、联机求助机制、所有回退段、临时段、所有的用户数据库实体、其它ORACLE软件产品要求的表等等。

2)SYSAUX表空间

作为SYSTEM表空间的辅助表空间，主要存放一些其他的 metadata 组件，如 OEM,Streams 等会默认存放在 SYSAUX 表空间里。

3)临时表空间

临时表空间是用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。

4)UNDO表空间

undo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。在rollback，实例恢复(前滚)，一致性读CR块的构造时会使用到undo信息。由于undo的引入，从而Oracle的select语句实现一致性读时，不需要任何锁。

5)USERS表空间

创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间，此用户所有信息都会放入到users表空间中。

2、PG自带系统表空间

注意PG默认没有temp表空间(或者说临时段默认是存放在pg_default表空间)和undo表空间。

1)pg_default表空间

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

(1)目录名“1”是系统数据库template1各relation的存储目录

(2)目录数字“倒数第二”是系统数据库template0各relation的存储目录

(3)其他数字目录是用户建立的数据库目录

(4)目录的命名方法就是数据库的oid值

(5)FSM的文件记录每个数据块的空闲空间

(6)VM文件让vacuum高效工作，被标为无效的记录需要用vacuum来进行清理，需要一个文件来标识哪些数据块中存在无效记录来达到高效的清理。

2)pg_global表空间

表空间pg_global用来存放系统字典表；对应存储目录$PADATA/global/

---

三、pg表空间物理布局图

---

四、PG表空间管理相关命令

1、查看表空间

dbselect * from pg_tablespace;

2、创建表空间

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

注意目录"directory"必须是一个已有的空目录，并且属于PostgreSQL操作系统用户

这里把建用户、表空间、建库一起做一个实例介绍了。

su - postgrespsql--建用户CREATE USER dbmt WITH PASSWORD 'jkc800$uTxt$9K20';--创建表空间CREATE TABLESPACE dbmt OWNER dbmt LOCATION '/data/pgdata/dbmt';--建库CREATE DATABASE dbmt with OWNER=dbmt ENCODING='UTF-8' TABLESPACE=dbmt CONNECTION LIMIT=-1;--将dbmt数据库的所有权限都赋予dbmtGRANT ALL PRIVILEGES ON DATABASE dbmt TO dbmt;q

3、删除表空间

 drop tablespace tablespace_name;

4、为数据库指定默认表空间

注意：

1)执行该操作不能连着对应数据库操作

2)对应的数据库不能存在表或者索引已经指定默认的表空间

3)执行该操作必须是没有人连着对应的数据库

 ALTER DATABASE name SET TABLESPACE new_tablespace;

5、将表从一个表空间移到另一个表空间

注意：在移动表的时候会锁表，此时对该表的所有操作都将被阻塞，包括select操作，所以请考虑在合适的实际做这个操作。

 ALTER TABLE name SET TABLESPACE new_tablespace;

6、临时表空间管理

PG的临时表空间用来存储临时表或临时表的索引，以及执行SQL时可能产生的临时文件例如排序，聚合，哈希等。为了提高性能，一般建议将临时表空间放在SSD或者IOPS，以及吞吐量较高的分区中。

PG临时表空间主要是通过参数temp_tablespaces进行配置，PostgreSQL允许用户配置多个临时表空间。配置多个临时表空间时，使用逗号隔开。如果没有配置temp_tablespaces 参数，临时表空间对应的是默认的表空间pg_default。

6.1、创建临时表空间

CREATE TABLESPACE temp01 LOCATION '/data/pgdata/temp';--查看临时表空间show temp_tablespaces;

6.2、设置临时表空间

--会话级生效set temp_tablespaces = 'temp01';--永久生效1)修改参数文件postgresql.conf2)执行pg_ctl reload

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~