谈谈PostgreSQL数据库中的模式

发表于 2021-11-10 更新于 2024-08-29 分类于 PostgreSQL数据库

最近在用 Greenplum 集群做数据处理，从而接触到了 PostgreSQL 数据库。由于之前主要使用的是 MySQL，转到 PostgreSQL 之后发现有很多不一样的地方，其中之一就是 模式（Schema）。

在 MySQL 中，模式跟数据库并没有什么区别，根据 MySQL 官方文档对 schema 的解释，MySQL 的模式（schema）和数据库（database）是同义词，创建数据库的语句 CREATE DATABASE 也可以写成 CREATE SCHEMA。但是，在 PostgreSQL 中模式却别有洞天。

1. PostgreSQL的模式（Schema）是什么？

先说一个看似难懂但其实很精辟的解释：数据库是数据库对象的物理集合，而模式（schema）则是数据库内部用于组织管理数据库对象的逻辑集合，主要作用是确保使用同一个数据库的每个用户都有属于自己的空间，一不互相干扰，二可授权访问。

怎么理解呢？这得从数据库对象说起。

数据库对象指的就是表、视图、索引、存储过程、序列、数据类型等数据库中的元素。数据库干的其中一件事就是如何更好地把这些数据库对象组织和管理起来。试想，如果一个数据库有多个用户，每个用户都想在数据库下创建一张自己的 student 表，该怎么办呢？于是就有了模式。

从概念上说，模式是一组相互关联的数据库对象的集合，包含表、视图、索引、存储过程、序列、数据类型等。一个PostgreSQL数据库集簇可以有一个或多个数据库，一个数据库可以有一个或多个模式。每个模式下包含了一组数据库对象的集合。不同的模式可以使用相同的对象名称而不会出现冲突。

PostgreSQL Hierarchy

举个例子。数据库就好比一个学校，而模式就像是每一个班级，学生就是数据库对象，学校通过每个班级把学生管理起来。每个班级的学生名字不能相同，不然班主任点名的时候会同时有好几个同名的学生站起来，但是不同班级之间学生重名却没有冲突，比如三年二班的班主任找三年二班的周杰伦，指的就是三年二班那个会音乐的周杰伦。这就是模式主要的作用。

2. 如何创建和访问模式下的对象？

从上面可以看出来，创建或访问模式下的对象，需要使用由数据库名和表名构成的限定名，中间用 . 分割，如 schema.table。很多情况下，由于我们创建一个用户的时候会为该用户在数据库下创建一个与用户名同名的模式，所以创建或访问模式下的对象时也写作 用户名.模式名。

或许你会说，我使用 PostgreSQL 从来没用过模式名，直接用表名也可以创建和访问某张表啊。这是因为公共模式和模式搜索路径在暗处发挥了隐形的作用。

3. 公共模式和搜索路径

什么是公共模式？

当我们在 PostgreSQL 中新建一个数据库的时候，新数据库会默认生成一个 public 模式（究其根源在于模版数据库 template1 中存在 public 模式），这个模式被称为 公共模式。新建表时如果没有指定模式名，表将被保存在该模式中，访问表时如果没有指定模式名，默认也是从该模式中搜索表。

或许你有疑问：不对啊，我创建或访问某张表时直接用表名，但是这张表直接保存在跟用户名同名的模式名中，并没有保存在 public 模式中啊。这跟模式的搜索路径有关。

详细来说，当我们创建或访问某张表时如果只指定表名而没有指定模式名，PostgreSQL 将按照一条特定的模式名顺序来搜索该表名指的是哪个模式下的表，这个顺序就是 模式搜索路径。如果搜索路径的第一个模式名下存在该表，则认为搜索该表成功，否则再看第二个模式名，以此类推。如果在搜索路径下没有找到该表，则会报错。

当前数据库的搜索路径可以使用以下命令查看：

1	SHOW search_path;

该命令默认返回：

  search_path   
-----------------
 "$user", public
(1 row)

其中，"$user" 指的就是跟当前用户名同名的模式，如果该模式存在，则第一个匹配该模式，否则匹配第二个模式 —— public 模式。也就是说在默认情况下，其实公共模式 public 并不是第一顺位模式，只是因为当前数据库没有跟当前用户名同名的模式，PostgreSQL 才在 public 模式中搜索表。

当我们在该数据库下创建用户名同名模式后，只使用表名新建表，那么这个表就会默认保存在当前用户名的模式下，如下例子：

-- 1. 创建一个新的数据库 
CREATE DATABASE dev;

-- 2. 切换进 dev 数据库
\c dev;  -- 返回【You are now connected to database "dev" as user "alfred"】，说明当前用户为 alfred

-- 3. 查看当前数据库所有的模式
\dn;  -- 可以看到只有一个 public 模式

-- 4. 新建一张新表 student
CREATE TABLE student(id int, name varchar(10));

-- 5. 查看 student 表信息，可发现新表被保存在 public 模式下
\d+;

-- 6. 新建当前用户名同名模式 alfred
CREATE SCHEMA alfred;

-- 7. 新建一张新表 teacher
CREATE TABLE teacher(id int, name varchar(10));

-- 8. 查看数据库表信息，可发现 teacher 表被保存在 alfred 模式下
\d+;

当然，默认的搜索路径 "$user", public 也是可以更改的，比如：

1	SET search_path TO public,"$user"; -- 这里就把 public 模式作为第一个搜索模式

4. 模式的用法

结合上述关于模式的机制，模式最常见的用法就是在数据库中为每个用户创建一个同名模式，并把用户限制在其自己的模式中，用户只能访问同名模式下的对象，如要访问其它模式需要具有相关的权限才行。这相当于在数据库下以每个用户为单位对数据库对象做一层逻辑的划分，而每个 PostgreSQL 数据库存放在单独的表空间做物理划分，以此来组织和管理数据库对象。

该用法的实现方式是，新建一个 PostgreSQL 数据库后：

首先使用 REVOKE CREATE ON SCHEMA public FROM PUBLIC; 回收每一个用户在 public 模式的权限（或者直接使用 DROP SCHEMA public; 把公共模式删掉）；
在数据库中为每一个用户创建一个与用户名同名的模式；
使用 ALTER ROLE ALL SET search_path ="$user" 把每个用户的模式搜索路径修改为其同名模式；

这样，用户连接数据库之后，默认的所有操作都在其同名的模式内进行，起到了逻辑分隔的作用，做到用户之间互不干扰。当然，当用户获得同一数据库内其它模式的访问权限之后，也可以访问其它模式的对象。

PostgreSQL 便是提供了数据库和模式相结合的这种方法，方便我们管理数据库。

6. 关于模式的其它知识

另外还有一些关于模式的知识点，也在这里一并记录一下。

首先是关于模式的一些常用操作：

-- 1. 创建模式
CREATE SCHEMA schema_name;

-- 2. 删除空模式
DROP SCHEMA schema_name;

-- 3. 删除一个不为空的模式
DROP SCHEMA schema_name CASCADE;

-- 4. 为某个用户创建一个同名模式
CREATE SCHEMA schema_name AUTHORIZATION user_name;

-- 5. 显示关于模式的信息
\dn;
\dn+;  -- 显示详细信息

另外，其实除了使用 \dn 所列出来的模式之外，每个数据库还有一个
pg_catalog 模式——系统目录模式。根据 PostgreSQL官方文档，它包含了系统表和所有内建的数据类型、函数以及操作符。如果没有在路径中显式地包括该模式，它将在路径中的模式之前被搜索。所以我们查询系统表的时候，即使不写 pg_catalog 模式名，该系统表也可以被查询到。