数据库与模式的基本操作

本节讲解 PostgreSQL 中"数据库"与"模式"两级命名空间的管理，包括创建、切换、查看与删除。学完本节，你将能为一个项目规划出清晰的对象组织结构，并避免命名冲突。

前置知识

阅读本节前，建议先了解：第一个 SQL 查询

基础概念

理解 PostgreSQL 的层级结构是管理对象的前提。它比 MySQL 多了一层"模式"，这是很多初学者的困惑来源。

实例（Instance）
└── 数据库（Database）          -- 相互隔离，默认不能跨库查询
    └── 模式（Schema）          -- 命名空间，用于给对象分组
        └── 对象（Object）      -- 表、视图、索引、函数、序列、类型……

层级	术语	说明	数量关系
第一层	实例（Instance）	一个 postgres 进程树 + 一份数据目录	一台机器可有多个实例
第二层	数据库（Database）	物理隔离的数据集合，各自拥有独立系统目录	一个实例可有多个数据库
第三层	模式（Schema）	数据库内的命名空间，对象真正挂载的地方	一个数据库可有多个模式
第四层	对象（Object）	表、索引、视图、序列、函数、类型等	一个模式可有大量对象

关键术语补充：

• 系统目录（System Catalog）：PostgreSQL 存放元数据的表，位于 pg_catalog 模式，如 pg_database、pg_namespace、pg_class。
• 搜索路径（Search Path）：一个模式列表。当你写 SELECT * FROM users 而没有指定模式时，PostgreSQL 按搜索路径从左到右查找，第一个命中的生效。
• public 模式：每个新建数据库默认自带的模式，默认在搜索路径中。
• 模板数据库（Template Database）：template1 是创建新数据库的"母版"，template0 是原始只读母版。往 template1 里加东西，会影响之后创建的所有数据库。
• 所有者（Owner）：对象的属主，拥有对它的全部权限。

模式不是"权限边界"也不是"物理隔离"。跨模式访问只需写全名 模式名.表名；真正的隔离单位是数据库。

语法与用法

数据库与模式管理的核心语句如下：

操作	数据库	模式
创建	CREATE DATABASE	CREATE SCHEMA
切换	\c 数据库名（客户端命令）	SET search_path TO ...
查看	\l、pg_database	\dn、pg_namespace
删除	DROP DATABASE	DROP SCHEMA
改名	ALTER DATABASE ... RENAME TO	ALTER SCHEMA ... RENAME TO
改属主	ALTER DATABASE ... OWNER TO	ALTER SCHEMA ... OWNER TO

最小可运行示例：

-- 创建带完整选项的数据库
CREATE DATABASE demo
  WITH
    OWNER       = postgres        -- 属主
    ENCODING    = 'UTF8'          -- 字符编码
    LC_COLLATE  = 'en_US.UTF-8'   -- 排序规则
    LC_CTYPE    = 'en_US.UTF-8'   -- 字符分类
    TEMPLATE    = template0       -- 基于哪一个模板库
    CONNECTION LIMIT = 100;       -- 最大并发连接数

-- 创建模式，并指定属主
CREATE SCHEMA sales AUTHORIZATION postgres;
CREATE SCHEMA IF NOT EXISTS finance;   -- 幂等写法，已存在时不报错

详细说明

数据库的创建与选项

CREATE DATABASE 有两条硬性规则：

1. 不能在事务块中执行。BEGIN; CREATE DATABASE x; COMMIT; 会直接报错。
2. 不能连接到目标库来操作它本身。要创建 demo，必须连在别的库（如 postgres）上执行。

-- 常见完整写法
CREATE DATABASE shop
  OWNER      = app_user
  ENCODING   = 'UTF8'
  LC_COLLATE = 'zh_CN.UTF-8'
  LC_CTYPE   = 'zh_CN.UTF-8'
  TEMPLATE   = template0
  CONNECTION LIMIT = 200
  IS_TEMPLATE = false;

选项	默认值	说明
OWNER	执行者	数据库属主
ENCODING	继承模板库	字符集，中文环境必须为 UTF8
LC_COLLATE	继承模板库	字符串排序与比较规则
LC_CTYPE	继承模板库	大小写、字符分类规则
TEMPLATE	template1	指定母版库；用 template0 才能自由指定编码与 locale
CONNECTION LIMIT	-1（无限制）	该库允许的最大并发连接数
TABLESPACE	pg_default	数据存放的表空间
ALLOW_CONNECTIONS	true	是否允许连接

关于 template0 与 template1 的选择很关键：

场景	用哪个模板	原因
需要指定与当前库不同的编码/locale	template0	template1 的编码与 locale 已被固化，无法覆盖
希望继承自定义扩展、函数等	template1	template1 是"母版"，新库会复制它
创建干净的库	template0	不受既往修改影响

-- 验证新库的编码与排序规则
SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype
FROM pg_database
WHERE datname = 'shop';

 datname | encoding |  datcollate  |  datctype
---------+----------+--------------+--------------
 shop    | UTF8     | zh_CN.UTF-8  | zh_CN.UTF-8
(1 row)

连接与切换数据库

PostgreSQL 的物理连接绑定在一个数据库上，因此"切换数据库"实际是重新建立连接。

# 方式一：psql 元命令，客户端内部重连
psql -d postgres

postgres=# \c shop
You are now connected to database "shop" as user "postgres".
shop=#

-- 方式二：查询当前库名，确认自己在哪里
SELECT current_database() AS db, current_schema() AS default_schema, current_user AS "user";

-- 查看搜索路径
SHOW search_path;

   db   | default_schema |  user
--------+----------------+---------
 shop   | public         | postgres
(1 row)

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

函数 / 变量	返回内容
current_database()	当前连接的数据库名
current_schema()	搜索路径中第一个已存在的模式
current_user	当前执行语句的角色
session_user	建立连接时使用的角色
current_setting('search_path')	当前搜索路径
inet_server_addr()	服务端监听地址
version()	服务端版本

模式的作用与搜索路径

模式的核心价值是用命名空间避免重名。典型用法有三种：

1. 按业务域分组

CREATE SCHEMA sales;       -- 销售域
CREATE SCHEMA finance;     -- 财务域
CREATE SCHEMA audit;       -- 审计域

CREATE TABLE sales.orders  (...);
CREATE TABLE finance.invoices (...);
CREATE TABLE audit.login_log (...);

2. 多租户（Multi-Tenant）

-- 每个租户一个模式，表结构完全相同
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;

CREATE TABLE tenant_a.users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);
CREATE TABLE tenant_b.users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);

3. 版本化的表结构切换（灰度发布）

CREATE SCHEMA app_v1;
CREATE SCHEMA app_v2;

-- 应用通过切换 search_path 实现秒级回滚
SET search_path TO app_v2, public;

搜索路径的解析规则与 "$user" 这个特殊项有关：

默认值："$user", public
        └───────┘  └──────┘
        若存在与当前登录角色同名的模式，优先使用它

-- 演示：创建与角色同名的模式后，未加限定的表名会落到该模式
CREATE SCHEMA postgres;                   -- 与登录角色 postgres 同名

SHOW search_path;
SELECT current_schema();                  -- 返回 postgres 而不是 public

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

 current_schema
----------------
 postgres
(1 row)

修改搜索路径有两种作用域：

-- 会话级：只影响当前连接，断开即失效
SET search_path TO sales, public;

-- 数据库级：对该库所有新连接生效（需有权限）
ALTER DATABASE shop SET search_path TO sales, public, pg_catalog;

-- 查看某数据库上的数据库级配置
SELECT datname, unnest(setconfig) AS setting
FROM pg_db_role_setting s
JOIN pg_database d ON d.oid = s.setdatabase
WHERE d.datname = 'shop';

 datname |                setting
---------+----------------------------------------
 shop    | search_path=sales, public, pg_catalog
(1 row)

查看与删除

查看数据库与模式的两种途径：psql 元命令（人读）与系统目录查询（程序读）。

-- 数据库清单：关注大小、属主、连接数上限
SELECT
  datname                                          AS db_name,
  pg_get_userbyid(datdba)                          AS owner,
  pg_size_pretty(pg_database_size(datname))        AS size,
  datconnlimit                                     AS conn_limit,
  datallowconn                                     AS allow_conn
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

 db_name  |  owner   |  size   | conn_limit | allow_conn
----------+----------+---------+------------+------------
 shop     | postgres | 8421 MB |        200 | t
 demo     | postgres | 102 MB  |        100 | t
 postgres | postgres | 8621 kB |         -1 | t
 template1| postgres | 8621 kB |         -1 | t
 template0| postgres | 8525 kB |         -1 | f
(5 rows)

-- 模式清单：连同属主与对象数量
SELECT
  n.nspname                              AS schema_name,
  pg_get_userbyid(n.nspowner)            AS owner,
  count(c.oid) FILTER (WHERE c.relkind = 'r') AS table_count,
  count(c.oid) FILTER (WHERE c.relkind = 'i') AS index_count
FROM pg_namespace n
LEFT JOIN pg_class c ON c.relnamespace = n.oid
WHERE n.nspname NOT LIKE 'pg\_%'
  AND n.nspname <> 'information_schema'
GROUP BY n.nspname, n.nspowner
ORDER BY n.nspname;

 schema_name |  owner   | table_count | index_count
-------------+----------+-------------+-------------
 audit       | postgres |           1 |           1
 finance     | postgres |           2 |           3
 public      | postgres |           1 |           1
 sales       | postgres |           3 |           5
(4 rows)

删除操作必须谨慎，两者的"是否可回退"完全不同：

-- 删除数据库：不可回退，且不能删除自己正在连接的库
DROP DATABASE shop;

-- 安全写法：先判断存在性
DROP DATABASE IF EXISTS shop;

-- 强制断开其他连接后再删除（PostgreSQL 13+）
-- PostgreSQL 13+
DROP DATABASE shop WITH (FORCE);

-- 删除模式：注意 CASCADE 的破坏力
DROP SCHEMA audit;                  -- 非空模式会报错
DROP SCHEMA audit CASCADE;          -- 连同其中所有对象一起删除，不可回退
DROP SCHEMA IF EXISTS audit CASCADE;

实战示例

场景：一个 SaaS 项目需要按业务域组织对象，并为每个环境准备独立的数据库。以下是完整的落地过程。

第 1 步：规划命名结构

层级	命名	说明
数据库	saas_dev、saas_test、saas_prod	按环境隔离
模式	core	用户、组织等核心实体
模式	biz	订单、商品等业务实体
模式	report	报表视图与物化视图

第 2 步：创建开发库

-- 必须在 postgres 库（或任意其他库）中执行，不能连在目标库上
CREATE DATABASE saas_dev
  WITH
    OWNER      = postgres
    ENCODING   = 'UTF8'
    TEMPLATE   = template0
    CONNECTION LIMIT = 50;

CREATE DATABASE

第 3 步：建立模式并设置搜索路径

psql -d saas_dev

CREATE SCHEMA core;
CREATE SCHEMA biz;

-- 固化到数据库级，避免每次连接都要手动 SET
ALTER DATABASE saas_dev SET search_path TO core, biz, public;

CREATE SCHEMA
CREATE SCHEMA
ALTER DATABASE

第 4 步：在各自模式下建表

-- core 模式：租户与用户
CREATE TABLE core.tenants (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT        NOT NULL,
  plan       TEXT        NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'ent')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE core.users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT      NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  email      TEXT        NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)          -- 同一租户内邮箱唯一
);

-- biz 模式：商品与订单，通过模式名跨模式引用 core 的表
CREATE TABLE biz.products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT        NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  sku        TEXT          NOT NULL,
  name       TEXT          NOT NULL,
  price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  UNIQUE (tenant_id, sku)
);

CREATE TABLE biz.orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT        NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  product_id  BIGINT        NOT NULL REFERENCES biz.products (id),
  qty         INTEGER       NOT NULL CHECK (qty > 0),
  amount      NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT now()
);

第 5 步：验证结构与搜索路径

-- 未加限定的表名按 search_path 解析，这里会命中 core.tenants
SELECT current_schema() AS default_schema;
SELECT count(*) FROM tenants;      -- 等价于 core.tenants

 default_schema
----------------
 core
(1 row)

 count
-------
     0
(1 row)

第 6 步：查看完整的对象归属

SELECT
  n.nspname  AS schema_name,
  c.relname  AS table_name,
  a.attname  AS column_name,
  format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname IN ('core', 'biz')
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY n.nspname, c.relname, a.attnum
LIMIT 8;

 schema_name | table_name | column_name |        data_type
-------------+------------+-------------+--------------------------
 biz         | orders     | id          | bigint
 biz         | orders     | tenant_id   | bigint
 biz         | orders     | product_id  | bigint
 biz         | orders     | qty         | integer
 biz         | orders     | amount      | numeric(10,2)
 biz         | orders     | created_at  | timestamp with time zone
 biz         | products   | id          | bigint
 biz         | products   | tenant_id   | bigint
(8 rows)

第 7 步：为新库配置独立的访问控制

-- 数据库级权限：允许应用账号连接
GRANT CONNECT ON DATABASE saas_dev TO app_user;

-- 模式级权限：允许应用账号使用该模式
GRANT USAGE ON SCHEMA core, biz TO app_user;

-- 已有对象的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA core, biz TO app_user;

-- 未来新建对象的默认权限（关键：否则每次建表都要重新 GRANT）
ALTER DEFAULT PRIVILEGES IN SCHEMA core, biz
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

第 8 步：环境复制

# 用 pg_dump 只导出结构，快速搭建测试库
pg_dump -d saas_dev --schema-only -f saas_schema.sql

createdb -T template0 -E UTF8 saas_test
psql -d saas_test -f saas_schema.sql

# 验证两边对象数量一致
psql -d saas_dev  -c "SELECT count(*) FROM pg_class WHERE relkind = 'r';"
psql -d saas_test -c "SELECT count(*) FROM pg_class WHERE relkind = 'r';"

注意事项

• \c 是 psql 元命令，不是 SQL。在应用代码或 SQL 脚本里不能写 \c，需要用 SET search_path 或重新建立连接。

-- 错误！这不是 SQL，服务端会报语法错误
-- \c shop

-- 正确：改变搜索路径
SET search_path TO sales, public;

• 不要用 DROP SCHEMA ... CASCADE 清理环境。它会递归删除模式内的一切对象，且没有确认提示。

-- 危险！会连表带数据全部删除
-- DROP SCHEMA public CASCADE;

-- 安全做法：先看清有什么，再逐个删除
SELECT relname, relkind FROM pg_class WHERE relnamespace = 'sales'::regnamespace;

• 不要在事务块内创建或删除数据库。

-- 错误！CREATE DATABASE cannot run inside a transaction block
-- BEGIN;
-- CREATE DATABASE foo;
-- COMMIT;

• 不要把 public 模式当作"公用"含义。它只是一个名字，与权限无关。生产环境建议不为普通账号授予 public 模式的 CREATE 权限，避免任意建表。

-- 收紧 public 模式权限（推荐）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

• 不要依赖默认搜索路径写业务 SQL（尤其是 DDL 与函数）。搜索路径可能被连接参数、数据库级设置或 SET 改变，导致同一语句落到不同模式。

-- 不推荐：搜索路径一变，表就建到别处
CREATE TABLE orders (id BIGINT);

-- 推荐：DDL 一律写全名
CREATE TABLE IF NOT EXISTS biz.orders (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

• 注意 "$user" 在搜索路径中的隐式作用。若存在与登录角色同名的模式，未限定的表名会优先落到那里，这是一种隐蔽的"表建错地方"来源。
• 删除数据库前必须确认无活动连接。有连接时 DROP DATABASE 会失败（或需 WITH (FORCE)，会强制踢掉会话）。
• 跨库查询不支持。PostgreSQL 没有 MySQL 那样的 db.table 跨库写法；需要跨库时用 postgres_fdw 外部数据包装器或逻辑复制。
• 数据库名与模式名的长度上限是 63 字节（NAMEDATALEN - 1），超长会被静默截断。

-- 演示截断：实际只保留前 63 字节
CREATE SCHEMA this_is_a_very_long_schema_name_that_exceeds_the_sixty_three_byte_limit_for_identifiers;
SELECT length('this_is_a_very_long_schema_name_that_exceeds_the_sixty_three_byte_limit_for_identifiers');

最佳实践

• 一个应用一个数据库，不要多个应用共用同一个库。数据库才是隔离与备份的粒度单位。
• 用模式承载业务域划分（core、biz、report、audit），而不是把所有表都堆在 public。
• DDL 一律使用全限定名（模式名.表名），不依赖搜索路径；DML 可以依赖，但要在连接初始化时显式固定。
• 数据库级设置用 ALTER DATABASE ... SET，让每个库自带正确配置，避免依赖客户端拼参数。
• 创建数据库时显式指定 TEMPLATE template0 + ENCODING UTF8 + 明确的 locale，不要依赖继承，否则后期改不了。
• 给数据库设置 CONNECTION LIMIT，防止某个库占满全部连接而影响其他库。
• 为新库配置 ALTER DEFAULT PRIVILEGES，否则每次新建表都要手工 GRANT，很容易漏。
• 生产库禁止对普通账号授予 public 模式的 CREATE 权限。
• 环境命名统一前缀（saas_dev / saas_test / saas_prod），避免在生产上误操作到测试库。
• 建库建模式的操作写成幂等脚本（IF NOT EXISTS），纳入版本管理，便于新环境一键搭建。
• 删除操作前先执行查询确认范围，再执行 DROP；对模式用 RESTRICT 而非 CASCADE，让非空模式报错来提醒你。

下一节

继续学习：创建表与插入数据

参考链接

• PostgreSQL 官方文档：管理数据库（Managing Databases）
• PostgreSQL 官方文档：模式（Schemas）
• PostgreSQL 官方文档：CREATE DATABASE
• PostgreSQL 官方文档：CREATE SCHEMA