PostgreSQL 模式（SCHEMA）
PostgreSQL 模式（Schema）完全指南
Schema 是 PostgreSQL 中 数据库内的命名空间，用于 组织表、视图、函数、索引 等对象，避免命名冲突，提升权限管理与安全性。
一、什么是 Schema？
| 概念 | 说明 |
|---|---|
| 数据库（Database） | 最高层级，隔离连接 |
| 模式（Schema） | 数据库内的子命名空间 |
| 表（Table） | 属于某个 Schema |
数据库 myapp
├── Schema: public ← 默认
│ ├── users
│ └── orders
├── Schema: analytics ← 自定义
│ └── user_stats
└── Schema: audit ← 审计
└── login_logs二、为什么使用 Schema？
| 优势 | 说明 |
|---|---|
| 命名隔离 | 不同团队用同名表（如 users） |
| 权限控制 | 给不同角色分配不同 Schema |
| 逻辑分组 | 按模块划分（crm, billing, logs） |
| 多租户支持 | 每个租户一个 Schema |
| 安全隔离 | 隐藏敏感表 |
三、创建 Schema
1. SQL 命令（推荐）
CREATE SCHEMA analytics;
CREATE SCHEMA IF NOT EXISTS billing;2. 指定所有者
CREATE SCHEMA crm AUTHORIZATION crm_user;3. psql 命令行
CREATE SCHEMA reporting;四、在 Schema 中创建表
-- 方法 1：显式指定
CREATE TABLE analytics.daily_active_users (
date DATE PRIMARY KEY,
cnt INTEGER
);
-- 方法 2：设置搜索路径（推荐）
SET search_path TO analytics, public;
-- 之后直接写表名
CREATE TABLE monthly_revenue (
month DATE,
revenue NUMERIC(12,2)
);五、搜索路径（search_path）—— 关键！
-- 查看当前搜索路径
SHOW search_path;
-- 输出: "$user", public
-- 设置搜索路径
SET search_path TO billing, public;
-- 现在 CREATE TABLE users ... 会创建在 billing.users最佳实践：每个应用连接时设置
search_path，避免混淆。
六、查询 Schema 中的对象
-- 列出所有 Schema
\dn
-- 列出某个 Schema 的表
\dt analytics.*
-- SQL 查询
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'analytics';七、跨 Schema 查询
-- 显式指定
SELECT * FROM analytics.users;
-- 别名
SELECT a.id, b.name
FROM crm.customers a
JOIN billing.invoices b ON a.id = b.customer_id;八、权限管理（Schema 级）
-- 创建角色
CREATE ROLE analyst LOGIN PASSWORD 'pass123';
-- 授予 Schema 使用权
GRANT USAGE ON SCHEMA analytics TO analyst;
-- 授予表权限
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst;
-- 未来表自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO analyst;九、多租户架构示例
-- 为每个租户创建 Schema
CREATE SCHEMA tenant_1001 AUTHORIZATION tenant_1001_user;
CREATE SCHEMA tenant_1002 AUTHORIZATION tenant_1002_user;
-- 每个租户有相同表结构
CREATE TABLE tenant_1001.users ( ... );
CREATE TABLE tenant_1002.users ( ... );
-- 应用连接时动态设置
SET search_path TO tenant_1001, public;十、移动表到其他 Schema
ALTER TABLE public.temp_data SET SCHEMA analytics;十一、删除 Schema
-- 删除空 Schema
DROP SCHEMA analytics;
-- 删除含对象的 Schema（级联）
DROP SCHEMA analytics CASCADE;
-- 安全删除
DROP SCHEMA IF EXISTS old_schema CASCADE;十二、pgAdmin 图形化操作
- 右键
Schemas→Create→Schema - 填写名称 → Save
- 右键新 Schema →
Create→Table建表 - 拖拽表 → 移动到其他 Schema
十三、一键创建多模块 Schema 脚本
-- init_schemas.sql
-- 运行：psql -U postgres -d myapp -f init_schemas.sql
-- 创建 Schema
CREATE SCHEMA IF NOT EXISTS crm AUTHORIZATION crm_user;
CREATE SCHEMA IF NOT EXISTS billing AUTHORIZATION billing_user;
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS audit;
-- 设置默认搜索路径
ALTER DATABASE myapp SET search_path TO public;
-- 示例表
SET search_path TO crm;
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
SET search_path TO billing;
CREATE TABLE invoices (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT,
amount NUMERIC(12,2),
issued_at DATE
);十四、常见错误与解决
| 错误 | 原因 | 解决 |
|---|---|---|
schema "analytics" does not exist | Schema 未创建 | CREATE SCHEMA analytics; |
permission denied for schema analytics | 无 USAGE 权限 | GRANT USAGE ON SCHEMA ... |
relation "users" does not exist | 搜索路径不对 | SET search_path 或用 schema.table |
cannot drop schema because other objects depend on it | 有表 | 用 CASCADE |
十五、最佳实践 Checklist
| 项目 | 建议 |
|---|---|
| 命名 | 小写 + 下划线，如 crm, billing_v2 |
| 默认 Schema | 保留 public 给扩展 |
| 搜索路径 | 连接时 SET search_path |
| 权限 | GRANT USAGE ON SCHEMA + GRANT ON TABLES |
| 清理 | 定期 DROP SCHEMA old_... CASCADE |
| 多租户 | tenant_{id} + 动态 search_path |
十六、快速上手：3 分钟创建并使用 Schema
-- 1. 创建 Schema
CREATE SCHEMA shop;
-- 2. 设置搜索路径
SET search_path TO shop, public;
-- 3. 建表
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT
);
-- 4. 插入数据
INSERT INTO products (name) VALUES ('Phone');
-- 5. 查询
SELECT * FROM products; -- 自动找 shop.products小结：Schema 使用 5 步法
1. CREATE SCHEMA name;
2. GRANT USAGE ON SCHEMA name TO role;
3. SET search_path TO name, public;
4. CREATE TABLE ... （自动归属）
5. 查询时用 schema.table 或依赖 search_path现在就动手：
创建一个 blog Schema，建 posts 和 comments 表，设置搜索路径，然后插入一条数据。
需要我生成：
- 多租户动态切换 Schema 的 Python 代码？回复
Python 多租户 - 自动创建 12 个月分区 Schema 脚本？回复
分区 Schema - pgAdmin 批量管理 Schema？回复
pgAdmin Schema
随时告诉我！