PostgreSQL-模式（Schema）

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL

PostgreSQL（本章节）

MongoDB

Redis

Etcd

前面我在介绍数据库的增删改查（crud），和用户和权限的时候都遇到一个概念：模式（Schema），今天这个小节我来详细介绍它。

1. 什么是模式？

在 PostgreSQL 中，模式 是一个命名空间（Namespace），它包含数据库对象，如表、视图、索引、数据类型、函数、操作符等。

可以把它想象成操作系统中的目录（文件夹），而数据库中的表、视图等对象就像是目录中的文件。

• 一个数据库 包含一个或多个模式。
• 一个模式 包含各种数据库对象。
• 同一个数据库内，模式名必须是唯一的。
• 不同模式中，可以有同名的对象（例如，schema_a.products 表和 schema_b.products 表）。

2. 为什么需要使用模式？（模式的主要作用）

模式的存在提供了多种好处，是现代数据库设计中不可或缺的一部分。

1. 实现多租户架构
   • 这是模式最经典的应用场景。在一个数据库中，可以为每个租户创建一个独立的模式（如 tenant_1, tenant_2）。
   • 每个租户的模式中都有完全相同的一套表结构（如 users, orders），但数据是完全隔离的。
   • 这样做比每个租户一个数据库更轻量，管理起来也更方便（备份、升级在一个数据库内完成），同时又比所有租户共享一套表（用一个 tenant_id 字段区分）在数据隔离和性能上更有优势。
2. 逻辑组织和管理数据库对象
   • 当一个应用非常庞大，拥有数百张表时，可以把它们按功能模块划分到不同的模式中。
   • 例如：将核心业务表放在 core 模式，用户相关表放在 user 模式，财务相关表放在 finance 模式，日志表放在 log 模式。
   • 这使得数据库结构更加清晰，易于管理和维护。
3. 解决命名冲突
   • 不同的团队或应用可能会使用相同的表名。通过将它们放入不同的模式，可以避免冲突。
   • 例如，一个数据分析团队可能需要一个 reporting 模式来存放他们的中间表和视图，而不会影响主应用 public 模式中的表。
4. 方便权限管理
   • 可以对模式级别进行权限控制，这比逐个对象授权要高效得多。
   • 例如：可以授权某个用户组只能访问 reporting 模式下的所有视图，而不能访问 core 模式下的任何表。

3. 默认模式：public

• 每个新数据库在创建时，都会自动包含一个名为 public 的模式。
• 如果没有指定任何模式，所有的数据库对象（表、视图等）默认都会创建在 public 模式下。
• 在 SQL 语句中，一个对象的全限定名（完全名称）是：模式名.对象名。例如 public.users。
• 如果不写模式名，如 users，PostgreSQL 会使用一个名为 search_path 的配置变量来确定去哪些模式里寻找这个 users 对象。

4. 关键概念：search_path

search_path 是一个决定对象名称解析顺序的关键配置参数。

• 定义：它是一个由模式名组成的列表，例如 "$user", public。
• 作用：当你执行 SELECT * FROM users; 时，PostgreSQL 会按照 search_path 中列出的顺序，依次在每个模式中查找名为 users 的表。找到第一个匹配的即停止。
• 默认值：通常是 "$user", public。
  • "$user"：表示一个与当前用户名同名的模式。如果存在，则优先查找。
  • public：然后查找 public 模式。

5. 模式的常用操作（SQL命令）

创建模式

CREATE SCHEMA my_schema;
-- 或者，为指定用户创建模式
CREATE SCHEMA schema_name AUTHORIZATION user_name;

abcd=# \dn
     List of schemas
     Name     |   Owner   
--------------+-----------
 public       | postgres
 schema_name  | postgres
 schema_name1 | role_name

在模式中创建表

#如果不指定就模模式在public模式下 
CREATE TABLE my_schema.my_table (
    id serial PRIMARY KEY, 
    name varchar(100)
);

查询模式中的对象

-- 查询特定模式下的所有表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'my_schema';

-- 使用全限定名查询数据
SELECT * FROM my_schema.my_table;

-- 或者使用模式通配符
\dt *.*

修改/删除模式

-- 重命名模式
ALTER SCHEMA my_schema RENAME TO new_schema_name;

-- 删除一个空模式
DROP SCHEMA my_schema;

-- 删除模式及其包含的所有对象（危险！）
DROP SCHEMA my_schema CASCADE;

6.权限管理

简单点来说，这个授权必须先配置模式的权限（USAGE），然后才是对模式下表的增删改查权限（CURD）。

-- 授权用户可以在模式中创建对象
GRANT CREATE ON SCHEMA my_schema TO some_user;

-- 授权用户使用模式（允许其查询模式内的对象）
GRANT USAGE ON SCHEMA my_schema TO some_user;

-- 授权用户对模式下的所有现有表有 SELECT 权限
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO some_user;