Skip to main content

☘️ Septvean's Documents

PostgreSQL Schema

一、Schema 的概念

定义：

PostgreSQL 中的 Schema 是数据库对象的命名空间（Namespace），用于组织数据库中的表、视图、函数、索引等对象。

可以把它理解为 数据库内的子目录。
作用：
- 避免同名对象冲突（不同 schema 可以有相同表名）
- 逻辑分组数据库对象（按模块、功能、团队划分）
- 支持多租户设计（不同 tenant 用不同 schema）
- 配合权限管理，限制对象访问
默认 Schema：
- PostgreSQL 默认有 public schema
- 如果创建对象未指定 schema，则放在 public 下

二、Schema 与数据库、表的关系

Database
 ├─ Schema 1
 │   ├─ Table a
 │   ├─ View b
 │   └─ Function f1()
 └─ Schema 2
     ├─ Table a   <-- 可以和 Schema 1 的 a 同名
     └─ Function f2()

Database: 容器级别，存放 Schema
Schema: 命名空间，存放表、视图、函数等对象
Object: 表、视图、索引、函数等

三、Schema 的操作

1. 创建 Schema

CREATE SCHEMA hr;
-- 或者指定拥有者
CREATE SCHEMA hr AUTHORIZATION hr_user;

2. 删除 Schema

DROP SCHEMA hr; -- 失败，如果里面有对象
DROP SCHEMA hr CASCADE; -- 强制删除 schema 内所有对象

3. 查看 Schema

-- 所有 schema
\dn

-- 当前数据库的对象及 schema
\dt *.*

四、指定 schema 创建对象

CREATE TABLE hr.employee (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE FUNCTION hr.get_employee_count() RETURNS INT AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM hr.employee);
END;
$$ LANGUAGE plpgsql;

对象全名：schema_name.object_name
如果不指定 schema，则默认 public

五、搜索路径（search_path）

PostgreSQL 支持 search_path 控制默认 schema 查找顺序：

SHOW search_path;

SET search_path TO hr, public;

查询时，如果对象未指定 schema，PostgreSQL 会按 search_path 顺序查找
推荐在多 schema 场景下总是显式指定 schema，避免歧义

六、权限管理

Schema 可以独立授权，控制对象访问：

-- 给用户 hr_user 使用 hr schema
GRANT USAGE ON SCHEMA hr TO hr_user;

-- 允许用户在 schema 创建对象
GRANT CREATE ON SCHEMA hr TO hr_user;

-- 收回权限
REVOKE CREATE ON SCHEMA hr FROM hr_user;

注意：

USAGE 权限：可以访问 schema 内的对象（但不包含表数据）
CREATE 权限：可以在 schema 内创建新对象
表级别权限单独管理（SELECT/INSERT/UPDATE/DELETE）

七、Schema 的最佳实践

模块化设计
- 按功能、模块、团队划分 schema，例如 sales, hr, inventory
- 避免所有对象都在 public 下
多租户设计
- 每个租户独立 schema，数据隔离，权限简单
权限控制
- 区分 USAGE 和 CREATE
- 表权限按 schema 管理，减少重复授权
命名约定
- schema 名小写，用下划线分隔
- 对象全名 schema.object 显式使用
避免 public 乱用
- 生产环境尽量不在 public schema 创建表或函数

八、查询 Schema 内对象

-- 查询 schema 内所有表
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'hr';

-- 查询 schema 内所有函数
SELECT routine_name
FROM information_schema.routines
WHERE specific_schema = 'hr';

九、Schema 与备份/迁移

pg_dump 支持按 schema 备份：
```
pg_dump -n hr mydb > hr_schema.sql
```
pg_restore 支持还原指定 schema：
```
pg_restore -n hr -d mydb hr_schema.sql
```

十、总结

Schema = 数据库内的命名空间
主要用途：逻辑分组 + 避免冲突 + 权限管理 + 多租户
使用方式：
- 创建：CREATE SCHEMA name
- 删除：DROP SCHEMA name [CASCADE]
- 权限：GRANT USAGE / CREATE
- 查询：schema_name.object_name 或设置 search_path
最佳实践：
- 按模块或租户划分 schema
- 避免乱用 public
- 显式指定 schema 名，配合权限管理