卷 VI · 现场CH 23深度 23/24

生产里的 PostgreSQL：不只是个库

前面讲的是 PostgreSQL 作为「引擎」的内功。这一章走到台前，看它作为一个生产平台该怎么用好：谁能碰数据（权限）、多个连接怎么扛（连接池）、怎么长出新能力（扩展）、怎么不丢数据（备份）。这些不是可选项，是上线前必须想清楚的。

角色权限连接池扩展生态备份恢复

角色与权限：别让应用用超级用户

PostgreSQL 把「用户」和「组」统一成了一个概念：角色（role）。一个角色可以能登录（像用户）、可以拥有其他角色（像组）。权限靠 GRANT / REVOKE 精细控制：

CREATE ROLE app LOGIN PASSWORD 'secret';        -- 应用专用角色
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app;
-- 注意：没给 app 建表 / 删表 / DROP 的权限

最小权限原则：应用连库用的角色，只该有它业务需要的那点权限，绝不用超级用户（postgres）。这样即使应用被攻破、或有 SQL 注入，破坏面也被限制住——这是「当真」在安全上的延伸：把「谁能做什么」也钉死。

Schema：一个库里的命名空间

第 2 章提过 PostgreSQL 的层级里，数据库和表之间还有一层 schema（默认 public）。它是个命名空间，用途不少：

  • 分模块：把不同子系统的表放进不同 schema（billing.invoices、auth.users），清爽。
  • 多租户：给每个租户一个 schema，表结构相同、数据隔离——一种轻量的多租户方案。
  • 权限边界：schema 本身能授权，控制谁能访问哪一块。

查表时找哪个 schema，由 search_path 决定（默认先找 public）。这也是为什么 PG 里不能像 MySQL 那样随意 otherdb.table 跨库查——跨的是 schema（同库内），不是 database（要用 postgres_fdw 外部数据封装器才能跨库，下面就是）。

◆ 连接池：PostgreSQL 的进程模型让它成了刚需

这是从 MySQL 过来最需要注意的运维差异。PostgreSQL 每个连接是一个独立的操作系统进程（不是线程），开一个连接开销不小、占内存。几百个连接还好，几千个连接会把服务器压垮。

而现代应用（尤其是 serverless、微服务、每请求一连接的框架）动辄要成百上千个连接。解法是连接池——在应用和数据库之间放一个 PgBouncer（最常用），它维护少量到 PostgreSQL 的真实连接，让成千的应用连接复用这几十个真实连接。

在 PostgreSQL 生产部署里，连接池几乎是必备，不是「优化项」而是「基础设施」。这一点 MySQL（线程模型，连接更轻）没这么紧迫，是个容易被忽略、上线才踩的坑。

扩展：PostgreSQL 最被低估的超能力

PostgreSQL 不只是个数据库，是个可扩展的平台。一句 CREATE EXTENSION 就能给它装上新能力——新类型、新函数、新索引方法，深入到内核级别。这是它区别于几乎所有对手的杀手锏：

扩展给你什么
pg_stat_statements查询性能统计（第 22 章的找慢查询神器）
PostGIS完整的地理空间数据库——业界地理信息事实标准
pgvector向量类型 + 相似度检索，让 PostgreSQL 变身 AI 时代的向量库
TimescaleDB时序数据的自动分区与压缩
pgcrypto加密 / 哈希函数
postgres_fdw外部数据封装器：把另一个 PostgreSQL（甚至别的库）的表当本地表查

pgvector 尤其值得一提：AI 应用需要存 embedding 向量做相似检索，很多人以为得单独上一个专门的向量数据库——其实一句 CREATE EXTENSION vector，PostgreSQL 就能干，还顺带享受它的事务、约束、备份。「一个库解决大部分问题」，正是 PostgreSQL 生态的魅力。

◆ 备份与恢复：两条路

数据无价，备份策略必须想清楚。PostgreSQL 有两条路：

  • 逻辑备份：pg_dump 把库导成一份 SQL（或归档格式）。灵活、可跨版本、可只导某几张表，适合中小库和迁移。pg_restore 恢复。
  • 物理备份 + PITR：pg_basebackup 拷贝整个数据目录，配合持续归档 WAL（还记得卷 IV 的预写日志吗），就能做时间点恢复（PITR）——恢复到「昨天下午 3:47 那一刻」，把误删数据前的状态精确捞回来。大库、严肃生产的标配。

同一套 WAL 归档机制，还支撑流复制（streaming replication）——一主多从、读写分离、高可用。这些是 DBA 的深水区，本书点到为止，但你要知道它们都建立在你已经理解的 WAL 之上。

数据库迁移：让 schema 变更可控

生产库的 schema 要随业务演进（加列、加表、改约束）。别手动敲——用迁移工具（Flyway、Liquibase、sqitch，或你框架自带的 migration），把每次结构变更写成有版本号、可重放、可回退的脚本，纳入代码仓库和 CI。

这里卷 IV 的可回滚 DDL 帮了大忙：一个迁移脚本用事务包住，中途失败就干净回滚，不留半残 schema。加列时还要注意不要长时间锁表——加一个带默认值的列、加约束，在大表上可能锁很久（PostgreSQL 近几个版本一直在优化这块，比如加 NOT NULL 列不再重写整表），上线前务必在类生产数据上验证迁移耗时。

⇄ 对照 MySQL
  • 连接模型：这是最实际的差异。MySQL 是线程每连接（较轻），PostgreSQL 是进程每连接（较重）。所以 PG 的连接池（PgBouncer）几乎是刚需，而 MySQL 侧不这么紧迫。从 MySQL 迁来时，这是最容易被忽略的一课。
  • 扩展生态：PostgreSQL 的 CREATE EXTENSION 体系（PostGIS、pgvector、TimescaleDB……）是它相对 MySQL 的代差级优势，很多能力 MySQL 要靠外部系统或根本没有。
  • 权限模型：概念相通（都有用户 / 授权），但 PG 的「角色」把用户和组统一，模型更简洁；细节语法不同。
  • 备份 / 复制：MySQL 有 mysqldump（对应 pg_dump）、binlog（对应 WAL）、主从复制。思路平行，工具和配置各不相同。

这一章的一句话

PostgreSQL 是个平台：用最小权限的角色连库、用 schema 划分命名空间、用 PgBouncer 扛住它进程模型下的连接压力、用扩展（PostGIS/pgvector…）长出新能力、用 pg_dump 或 WAL 归档 + PITR 守住数据。

最后一章，我们把全书对 MySQL 的所有对照汇成一张能直接抄的迁移速查表，送你带着 MySQL 的直觉，稳稳上路 PostgreSQL。