PostgreSQL

PostgreSQL 执行、存储与恢复参考图从客户端连接进入 backend，沿 SQL 执行、共享缓冲区、heap 与索引、MVCC、VACUUM 和 WAL 理解查询、并发、复制与恢复。高清原图

PostgreSQL 不只是“支持 SQL 的数据库”。一次更新会同时经过事务可见性、行版本、索引维护和 WAL；这些机制让它能够处理复杂的一致性工作负载，也带来了 VACUUM、连接和恢复成本。先看清这条数据路径，再学习部署和命令，许多看似独立的问题就会连在一起。

一、是什么

PostgreSQL 是怎样的一套数据库系统

PostgreSQL 是开源的对象关系型数据库。它以关系模型、SQL、事务和约束为基础，又允许用户扩展类型、函数、操作符、索引访问方法和过程语言。普通业务可以把它当作严格的关系数据库使用；需要 JSONB、数组、范围类型、全文检索、地理信息或自定义数据类型时，又不必立即把数据拆到另一种存储中。

这份能力并不意味着 PostgreSQL 应该包办所有数据问题。它不是以内存 Key-Value 访问为核心的缓存，也不是天然横向分片的日志分析引擎，更不是把任意文档塞入 JSONB 就能自动得到良好模型的“万能数据库”。PostgreSQL 最擅长的是：数据关系和约束重要，读写需要事务语义，同时查询形态又比简单主键 CRUD 更丰富的系统。

官网、文档、源码和下载入口分别是 postgresql.org、官方文档、Git 仓库与下载页面。学习机制时优先阅读与实际 major version 对应的文档；current 会随着新主版本发布而移动。

一条 SQL 从连接到结果经历什么

PostgreSQL 采用多进程架构。主服务进程通常称为 postmaster，它监听端口并为新连接建立 backend process。客户端与自己的 backend 通信；多个 backend 通过共享内存、锁和 WAL 等设施协调。连接因此不是一个几乎没有成本的逻辑编号：每条连接都会占用服务端进程、内存和调度资源，这也是 PostgreSQL 项目经常需要连接池的根本原因。

一条查询大致经过下面的路径：

Parser 把 SQL 变成语法树并解析对象名称；Rewriter 处理规则和视图；Planner 根据统计信息、可用索引、连接顺序和代价参数选择计划；Executor 按计划从 heap、索引、排序区或临时文件读取与修改数据。查询访问的数据页通常先经过 shared buffers，写事务还会生成 WAL 记录。

客户端看到的总耗时不只是 Executor 的执行时间。它还可能包含连接池等待、建立 TCP/TLS 连接、认证、等待锁、服务端排队、结果传输和应用反序列化。EXPLAIN ANALYZE、pg_stat_activity、数据库日志与应用链路指标观察的是不同部分，不能用其中一个数字解释所有延迟。

官方的 How Connections Are Established 和 Overview of PostgreSQL Internals 给出了连接与内部执行的基础模型。

cluster、database、schema、role 不是同一层概念

PostgreSQL 文档里的 database cluster 指一套由同一个服务端实例管理的数据目录和数据库集合，不等于由许多机器组成的高可用集群。一套 cluster 可以有多个 database；一个连接在建立时只能进入其中一个 database，普通 SQL 不能直接跨 database 关联。

database 内部又有 schema。表、视图、序列、函数、类型和索引属于某个 schema；同名对象可以存在于不同 schema。search_path 决定没有写 schema 前缀的名称按什么顺序查找，但它不会替 role 获得缺失的权限。

role 同时是权限主体和可选的登录身份。带 LOGIN 的 role 可以登录；不带 LOGIN 的 role 常用来汇聚权限，再授予给登录 role。对象 owner、role membership、database 的 CONNECT、schema 的 USAGE、表的 DML 权限和序列权限各自独立。

连接成功后，可以先运行下面的查询确认自己究竟进入了哪里：

SELECT current_user,
       session_user,
       current_database(),
       current_schema(),
       current_schemas(false);

SHOW search_path;

如果应用能连接却找不到表，不要立即把表复制到 public。先用限定名称查询，例如 app.orders，再检查 schema 是否存在、role 是否有 USAGE、对象 owner 是谁，以及连接参数是否修改了 search_path。对象层级清楚以后，初始化、迁移和运行账号才能真正分开。

heap、page 和 tuple 决定数据怎样存在

PostgreSQL 把普通表称为 heap。heap 文件由固定大小的数据页组成，常见页大小为 8 KiB；页内包含页头、指向行版本的 item identifier、tuple 数据和可用空间。索引条目通常保存键值与指向 heap tuple 的位置，因此一次 index scan 往往仍要访问 heap 判断可见性。

一行在内部对应 tuple。tuple header 会保存创建和失效它的事务信息、空值位图等元数据。较大的变长字段可能被压缩或移动到 TOAST 表，因此“表里只有几列”不代表每次查询都只读一小段连续数据。SELECT * 还可能把原本不必读取的大字段带回应用。

每个 heap 还会配合两类重要的旁路结构。Free Space Map 记录哪些页还有可复用空间，帮助插入或更新寻找目标页；Visibility Map 记录页面是否 all-visible 或 all-frozen，既影响 VACUUM 扫描，也决定某些 index-only scan 是否仍需回到 heap。

如果一次更新没有修改任何索引涉及的列，并且原页面还有空间，PostgreSQL 可能执行 HOT（Heap-Only Tuple）update。新行版本仍写入 heap，但相关索引不必增加指向新版本的条目。HOT 能减少索引写放大，却不能消除旧 tuple 和 VACUUM；频繁更新的表还要为页面保留适当空间，才能提高 HOT 发生概率。

这解释了一个常见现象：明明创建了覆盖索引，执行计划里仍有大量 Heap Fetches。索引包含所需列只是第一个条件；相关页面还要被判断为对所有事务可见。频繁更新会清除 all-visible 标记，后续 VACUUM 才可能重新设置。可继续阅读官方的 Database Physical Storage、Page Layout 和 Visibility Map。

MVCC 不是“没有锁”，而是用行版本减少读写阻塞

PostgreSQL 通过多版本并发控制（MVCC）让语句按照 snapshot 判断哪些 tuple 可见。UPDATE 通常不是在原位置覆盖旧值，而是创建新的 tuple 版本，并让旧版本在未来变得不可见。读事务可以继续看到符合自己 snapshot 的旧版本，写事务之间的冲突则仍要依靠行锁、表锁和唯一性检查协调。

PostgreSQL 支持 Read Committed、Repeatable Read 和 Serializable，Read Uncommitted 会按 Read Committed 处理。Read Committed 是默认级别，每条语句开始时取得新的 snapshot，同一事务中的两次查询可能看到别的事务在中间提交的变化。

Repeatable Read 在事务内维持稳定 snapshot，并阻止 PostgreSQL 允许的不可重复读和幻读；发生并发写冲突时，事务可能以 40001 失败。Serializable 使用 Serializable Snapshot Isolation 检测可能形成非串行结果的依赖关系，能提供更强语义，但应用必须准备重试整个事务。

MVCC 减少普通读写之间的阻塞，却不会消除锁。UPDATE 同一行会等待；DDL 常需更强的表锁；外键检查、唯一索引、SELECT ... FOR UPDATE 和显式 advisory lock 都可能形成等待链。死锁检测会终止其中一个事务，应用应对 SQLSTATE 40P01 重试完整且幂等的事务，而不是只重放最后一条 SQL。

官方 Concurrency Control 对隔离级别、显式锁、死锁和序列化失败有完整定义。工程上更重要的是把事务缩短：不要在事务中等待远程 HTTP 调用、人工输入或长时间批处理，否则旧 snapshot、锁和连接会一起被占住。

VACUUM 是 MVCC 的另一半

旧 tuple 不再被任何事务看见后，它占用的空间仍不会自动从文件中消失。普通 VACUUM 清理不再需要的行版本并把空间标记为可复用，同时维护 visibility map、冻结较老的事务 ID；它通常不会把文件空间归还给操作系统。VACUUM FULL 会重写整张表并获取强锁，只适合明确需要物理收缩且已有维护窗口的场景。

autovacuum 根据插入、更新、删除数量和表级阈值调度 VACUUM 与 ANALYZE。大表即使变化比例不高，绝对变化行数也可能很多；高更新的小热点表又可能在两轮任务之间快速积累 dead tuple。因此调优不能只改一组全局参数，而要观察具体表的更新速率、事务年龄、I/O、WAL 和业务延迟。

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count,
       last_autoanalyze,
       autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

dead tuple 多不一定等于 autovacuum 失效。长事务、idle in transaction 会话、长时间逻辑槽、I/O 饱和或反复被锁取消的 autovacuum 都可能延长清理窗口。先找阻止清理的原因，再决定是否调整表级 autovacuum_vacuum_scale_factor、threshold、cost limit 或 worker 数量。官方 Routine Vacuuming 是理解这部分的主入口。

索引和执行计划服务于访问模式

PostgreSQL 不只有 B-tree。不同索引访问方法解决不同的运算关系：

索引	典型访问方式	常见误区
B-tree	等值、范围、排序、前缀列	联合索引列顺序与真实过滤、排序不匹配
Hash	单列等值	很少比功能更完整的 B-tree 更有必要
GIN	JSONB 包含、数组成员、全文词项	写入和维护成本可能明显增加
GiST	范围、几何、相似度等可扩展搜索	必须结合操作符类理解，不是通用替代品
SP-GiST	可自然分区的搜索空间	适用数据结构有限，应先确认操作符类
BRIN	与物理顺序相关的超大表范围过滤	数据无相关性时会读取大量无关 block range

Planner 根据统计信息估算每个节点会返回多少行，再比较顺序扫描、索引扫描、连接算法、排序和并行路径的成本。估算行数与实际行数相差很大时，先检查统计信息是否陈旧、数据是否倾斜、多个列是否相关，以及查询参数是否代表真实分布。

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, amount
FROM app.orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

ANALYZE 会实际执行查询。对 UPDATE、DELETE、锁定读或高代价 SQL，应在可回滚事务或脱敏副本中运行。阅读计划时同时比较 estimated rows 与 actual rows、loops、shared hit/read、temporary read/write、heap fetches 和 WAL，而不是只看有没有出现 Index Scan。

类型、约束、JSONB 和扩展共同塑造数据模型

PostgreSQL 的价值很大一部分来自数据模型能够直接表达约束。NOT NULL、CHECK、UNIQUE、外键、排除约束和合适的数据类型可以在所有写入入口统一保护事实，而不是只依赖某个应用版本的校验代码。

时间点通常用 timestamptz 保存，金额和需要精确十进制的值用 numeric，机器生成的标识可以使用 identity column 或 UUID。数组、range、network address、JSONB 等类型适合表达确实属于一个关系对象的数据，不应因为“以后可能变化”就把所有列塞进一个 JSON 文档。

JSONB 会以二进制结构保存并支持 GIN 等索引。它适合可选属性、外部载荷副本、变化较快但仍与主实体同生命周期的数据；需要稳定约束、频繁连接、单字段高频更新或强类型统计的字段，通常仍应建成普通列。JSONB 索引也不是免费午餐：索引更大、写放大更高，操作符与索引表达式必须和查询一致。

extension 可以向数据库安装新类型、函数、操作符和索引能力，例如 pg_trgm 与 PostGIS。它属于 database，不是整个 cluster 自动共享；版本、schema、依赖库和升级路径都要管理。迁移账号可以在受控阶段安装批准的 extension，应用运行账号不应长期持有创建 extension 的能力。

WAL 把崩溃恢复、复制和时间点恢复连在一起

Write-Ahead Logging 的核心顺序是：描述数据变化的 WAL 必须先可靠写入，相关数据页才能随后落盘。系统崩溃后，PostgreSQL 从 checkpoint 附近重放 WAL，把数据文件恢复到一致状态。业务事务提交是否等待 WAL 刷盘由 synchronous_commit 等设置影响，但不能把降低等待简单理解成“没有可靠性代价”。

同一条 WAL 链还可以被其他用途消费。物理流复制把 WAL 变化应用到同一 major version、同一物理结构的 standby；WAL 归档配合 base backup 可以恢复到某个时间点，并产生新的 timeline；逻辑解码则从 WAL 提取表级逻辑变化，服务发布订阅、CDC 或跨版本迁移，但不会自动复制所有 DDL、role、tablespace 和 sequence 状态。

standby 正在 streaming，只说明数据面持续接收 WAL。自动判断故障、选出新主、隔离旧主、更新客户端入口和处理旧主重入，还需要 Patroni、repmgr、Operator、云平台或其他控制组件。没有 fencing 的自动 promote 可能在网络分区时形成两个可写 primary，产生无法靠普通 WAL 合并的分叉。

复制也不是备份。误删和错误更新会复制到 standby；长期保留的独立备份与 WAL 归档才能提供回到事故前时间点的机会。官方 Backup and Restore 与 Continuous Archiving and Point-in-Time Recovery 应与复制文档一起阅读。

版本与生态决定升级边界

PostgreSQL 大约每年发布一个 major version，每个 major 通常支持五年；同一 major 的 minor release 主要提供错误与安全修复。minor upgrade 一般不改变数据目录格式，major upgrade 则需要 pg_upgrade、dump/restore、逻辑复制或其他迁移方式，不能只替换容器镜像标签。

官方当前支持 18、17、16、15 和 14，当前 minor 分别为 18.6、17.11、16.15、15.19 和 14.24；14 已接近支持周期末端，不适合作为新系统基线。下面的可重复实验固定 postgres:18.6，它只是示例基线。生产环境应跟踪 Versioning Policy 和对应发行说明，在同一 major 内及时升级 minor。

驱动、extension、备份工具、连接池、Operator 和云服务都有自己的版本周期。核心采用宽松的 PostgreSQL License，不代表第三方扩展、镜像和托管服务自动采用相同许可。升级前除了核心数据格式，还要核对 extension 二进制兼容、collation、驱动、查询计划和恢复工具。

二、为什么

选择 PostgreSQL 的真正理由

如果系统同时需要严格约束、复杂事务和多样查询，PostgreSQL 往往能在一个一致的数据模型中解决问题。它不是靠单个“高级功能”取胜，而是把类型、约束、SQL、索引、执行器和扩展机制组合在一起。

例如订单系统不只按主键查询。它可能需要唯一业务号、租户隔离、金额精度、状态约束、按时间分页、后台对账、复杂聚合和审计查询。如果这些语义都留在应用代码中，不同写入入口很容易产生不同规则；PostgreSQL 可以用类型、约束和事务把关键不变量放到数据边界，再用多种索引与 SQL 处理不同读法。

另一个常见理由是系统需要在关系数据旁保留一部分半结构化属性。PostgreSQL 的 JSONB、数组、range 和全文检索能减少过早引入多种存储，但仍允许稳定字段保留关系约束。正确做法是把关系与文档作为同一模型里的两种表达，而不是用 JSONB 逃避建模。

这些场景尤其适合

交易、账务、订单和权限系统通常需要事务与数据约束，查询又包含多表连接、窗口函数、CTE、聚合或复杂过滤，这类工作负载最能发挥 PostgreSQL 的长处。约束负责守住写入边界，SQL 和多种索引负责适配不同读取方式。

当系统需要 JSONB、数组、range、全文检索、GIS 或自定义扩展，同时数据仍有清晰关系边界时，PostgreSQL 也能避免过早拆分存储。标准 SQL、丰富驱动和多种部署形态适合长期项目；逻辑复制、CDC 与扩展能力还能把关系数据接入搜索、分析和事件链路。

“适合”不等于默认选最复杂的部署。低流量内部系统完全可以从单实例与可靠备份开始；只有可用性目标、维护窗口和业务损失明确需要时，才增加 standby、自动切换和跨故障域架构。

这些场景不应只靠 PostgreSQL

数据全部能够重建、访问是极高频的简单 Key-Value、并且微秒级内存访问比关系约束更重要时，专用缓存更合适。持续写入海量日志并做大范围列式扫描时，分析型数据库往往具有更好的压缩与向量化执行路径。全文相关性、倒排索引和搜索集群扩展是核心需求时，应评估 Elasticsearch 或 OpenSearch，而不是不断扩大 PostgreSQL 全文检索的职责。

一个数据库连接只能进入一个 database。如果系统大量依赖跨 database 查询，PostgreSQL 的 database 隔离会增加复杂度；这类共享对象通常应重新考虑 schema 边界、数据平台或明确的集成链路。

PostgreSQL 也不会自动解决横向写扩展。内置分区主要是单个数据库内的数据组织和生命周期工具，不等于跨节点分片。Citus 等扩展、应用分片或分布式 SQL 产品会引入新的路由、事务和运维语义，应该作为架构变化单独评估。

与 MySQL、SQL Server 比较什么

产品比较不应停在“谁支持某条 SQL”。更有价值的是比较会长期影响开发与运维的机制：

问题	PostgreSQL 的特点	选择时需要承担的代价
对象与权限	database、schema、role、owner 和扩展体系丰富	search_path、默认权限和对象 owner 需要明确治理
并发模型	MVCC 与 snapshot 语义强，读写并发能力好	dead tuple、freeze 与 VACUUM 是长期维护工作
查询与类型	SQL、索引访问方法、JSONB、range 和扩展能力丰富	Planner 统计、extension 兼容和执行计划需要理解
连接模型	一连接一 backend，语义直接	大量短连接和空闲连接成本高，常需连接池
复制恢复	WAL 同时支撑崩溃恢复、物理复制和 PITR	自动切换、fencing、路由与备份仍需外围系统
升级	minor 升级直接，major 可用多种迁移路径	数据格式、extension、collation 和计划变化必须验证

MySQL 在互联网事务生态、复制工具和团队普及度上常有优势；SQL Server 在微软技术栈、集成管理工具和商业支持体系中有明确位置。PostgreSQL 的优势通常来自更丰富的数据模型、SQL 和扩展能力。最终选择应由已有技能、驱动与框架、运行平台、恢复目标、查询形态和退出成本共同决定。

关系列与 JSONB 怎样取舍

先问字段是否参与业务约束、连接、排序、聚合和高频过滤。如果答案是肯定的，它通常应该成为有类型的普通列。只有字段集合确实变化快、不同记录允许拥有不同属性、主要以整体读写或包含查询访问时，JSONB 才能降低模型摩擦。

一种稳健的混合模型是把身份、租户、状态、金额和时间保留为普通列，把供应商特有或版本化属性放入 JSONB：

CREATE TABLE app.product (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  sku text NOT NULL,
  name text NOT NULL,
  status text NOT NULL CHECK (status IN ('ACTIVE', 'INACTIVE')),
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, sku)
);

CREATE INDEX idx_product_attributes_gin
  ON app.product USING gin (attributes jsonb_path_ops);

这个 GIN 索引适合 attributes @> '{"color":"black"}' 一类包含查询，却不一定支持所有 JSONPath 或键存在查询。索引表达式必须从真实 SQL 反推；如果某个 JSON 字段已经成为稳定的高频过滤条件，可以把它迁移为普通列或建立有针对性的表达式索引。

复杂 SQL 的价值不是把所有计算都留在数据库

窗口函数、聚合、CTE、LATERAL、递归查询和丰富的连接能力，可以让一次查询在一致 snapshot 中完成数据关联与计算。与把数百万行先传到应用再拼接相比，数据库能够利用索引、统计信息、并行执行和临时空间，更早过滤不需要的数据，也避免应用在多个查询之间读到不同状态。

但“PostgreSQL 能写出这条 SQL”不代表“它应该承担这项长期负载”。面向单个用户或一小段时间窗口的业务聚合，通常适合留在事务库；跨多年明细、扫描大部分数据、并发生成报表或持续执行列式聚合，会与在线短事务争夺 buffer、CPU、I/O 和临时空间。此时应通过只读副本、离线抽取或分析型数据库隔离负载。

判断边界时先看读取比例和时效要求。如果查询只读取少量索引范围，并且必须看到刚提交的数据，留在 PostgreSQL 往往最简单；如果查询反复扫描大部分历史数据，允许分钟级或小时级延迟，分析链路更合适。不要因为 SQL 写起来方便，就让报表任务拖慢交易提交。

分区解决数据生命周期，不自动解决扩展

声明式分区把一张逻辑表按 range、list 或 hash 规则映射到多个物理分区。它最直接的价值是把数据生命周期变成分区级操作：按月创建新分区、快速 detach 旧分区、对不同分区执行维护，也可以在查询条件包含分区键时让 Planner 裁剪无关分区。

分区不是“大表必选项”。查询不带分区键时仍可能访问大量分区；分区过细会增加 catalog、规划、连接和维护成本；唯一约束还受到分区键限制。数据只有几千万行但索引与 SQL 合理时，普通表可能比数千个小分区更稳定。

选择分区前应回答数据是否有明确的时间或租户生命周期，主要查询是否总能限定分区键，删除归档是否真的需要分区级速度。只有这些条件成立，分区才是在降低维护成本，而不是把单表问题变成分区管理问题。

分区仍位于同一个 PostgreSQL cluster，通常共享同一组 CPU、内存、WAL 和故障域。它不能替代跨节点分片，也不会让单个 primary 获得无限写吞吐。需要独立扩缩容和故障域时，应评估实例拆分、应用路由、Citus 或分布式 SQL，并重新定义跨分片事务与查询语义。

扩展能力也会形成升级依赖

extension 是 PostgreSQL 很有吸引力的能力。pg_trgm 可以改善相似搜索，PostGIS 提供完整地理信息模型，pg_stat_statements 提供归一化 SQL 统计，其他扩展还能增加向量、时序或分布式能力。它们让数据库贴近业务，但也把 extension 版本、操作系统共享库、托管平台支持清单和 major upgrade 绑进运行链。

纯 SQL extension 通常容易迁移，依赖 C 语言共享库或外部服务的 extension 则必须在新节点安装兼容二进制。云托管服务可能只允许批准列表中的版本；逻辑备份会保存 CREATE EXTENSION 声明，却不会把操作系统库一起打包。恢复前没有安装对应包，后续对象和数据就可能失败。

因此扩展选择应由长期收益决定。核心业务确实需要地理拓扑、向量检索或特殊索引时，成熟扩展可以比自建实现可靠；只是为了一个很小的辅助函数时，普通 SQL、生成列或应用代码可能具有更低的升级成本。团队还要确认失去该 extension 后如何导出数据，避免让专用类型成为无法迁移的黑盒。

规模增长时按瓶颈选择下一步

容量上升后先确认瓶颈属于连接、CPU、内存命中、随机 I/O、WAL、锁还是单条 SQL。连接池和 PgBouncer 解决的是连接成本，不增加执行器吞吐；扩大内存可能提高工作集命中，却不能修复错误计划；读副本可以承接允许陈旧的查询，却不能增加 primary 的写能力。

表级分区适合生命周期和裁剪，垂直拆分适合把不同资源与故障目标分开，应用分片适合可以用稳定分片键独立路由的数据。每一种扩展方式都改变不同边界。过早分片会把 join、唯一性、事务、migration 和恢复推给应用；过晚拆分则可能让一个实例的维护窗口和故障半径不可接受。

一个稳健的演进顺序通常是先修 SQL、索引和事务，限制连接与批处理，再根据观测结果增加资源或只读副本；数据生命周期明确时使用分区；单 primary 的写入、容量或故障域已经成为稳定瓶颈时，才进入实例拆分或分布式方案。这里的顺序不是固定成熟度模型，任何一步都应对应已经出现或可预测的具体瓶颈。

单实例、主备、Operator 和托管服务怎么选

单实例的优势是故障面小、升级链短、成本低。只要业务允许从备份恢复，并且恢复时间经过实际测量，它并不等于“不专业”。本地开发、CI、内部工具和低损失业务都可以从单实例开始。

物理主备缩短机器故障后的恢复时间，也能提供只读副本，但会增加 WAL 保留、复制延迟、连接路由、故障切换和旧主重入问题。同步复制可以降低已确认事务的数据丢失窗口，却会把副本或网络延迟带入提交路径；异步复制延迟更低，但故障时可能丢失尚未重放的事务。

Kubernetes Operator 能自动化创建、备份、滚动维护和故障转移，但数据库可靠性仍依赖存储、调度、网络、证书、Operator 版本和集群本身。团队没有成熟 Kubernetes 数据服务能力时，Operator 不会自动降低复杂度。

托管 PostgreSQL 把硬件、补丁、备份和部分高可用责任交给云厂商，适合希望减少值守负担的团队。应用仍然要管理 schema、SQL、连接池、migration、慢查询、权限、容量和恢复后的业务校验。还要确认参数可见性、extension 清单、跨区域能力、费用和迁出路径。

一致性、延迟和可用性如何交换

数据库架构没有免费的开关。同步提交等待更多副本确认，会扩大网络和副本故障对写入延迟的影响；异步提交与异步复制降低正常写入等待，却增加故障时的数据窗口。连接池减少建连成本，但池过大只会把压力集中到数据库；读副本分担查询，但应用必须接受复制延迟和只读事务限制。

设计前至少要量化四个问题：一次故障最多允许丢失多少已提交业务数据；从发现故障到恢复写入允许多久；正常提交延迟预算是多少；团队能否维护备份、WAL、复制、切换和升级。答案决定部署形态，而不是反过来先选组件再编写理由。

PostgreSQL 的强项是让这些选择建立在同一套事务、存储和 WAL 机制上。它的代价也来自同一个地方：如果团队不理解长事务、VACUUM、连接预算和恢复链，再丰富的功能也会变成不可预测的运行成本。

三、怎么做

十分钟跑通第一条数据链

下面的实验使用 Docker 和 PostgreSQL 18.6。它适合本地学习和问题复现，不代表生产部署。先确认 Docker 可用、5432 没有被其他容器占用：

docker version
docker ps --format 'table {{.Names}}\t{{.Ports}}'

创建独立 volume 并启动容器。示例密码只用于可删除的本地实例，不要复制到共享环境：

docker volume create pg18-lab-data

docker run -d \
  --name pg18-lab \
  -e POSTGRES_PASSWORD='local_postgres_only' \
  -e POSTGRES_DB='appdb' \
  -p 127.0.0.1:5432:5432 \
  -v pg18-lab-data:/var/lib/postgresql \
  postgres:18.6

docker logs -f pg18-lab

PostgreSQL 18 的 Docker Official Image 把默认 PGDATA 攒到 /var/lib/postgresql/18/docker，声明的 volume 位于 /var/lib/postgresql；因此这里挂载父目录。17 及更早版本的旧模板常挂载 /var/lib/postgresql/data，升级 major 时不要未经检查直接沿用。容器日志出现 database system is ready to accept connections 后按 Ctrl+C 退出日志跟随，再进入 psql：

docker exec -it pg18-lab psql -X -U postgres -d appdb

先建立三个角色。app_owner 拥有 schema 和对象但不登录，app_migrator 在发布阶段执行 DDL，app_runtime 只承担应用运行所需的 DML：

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN PASSWORD 'local_migrator_only';
CREATE ROLE app_runtime LOGIN PASSWORD 'local_runtime_only';

GRANT app_owner TO app_migrator;

CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_runtime;

ALTER ROLE app_migrator IN DATABASE appdb SET search_path = app, pg_catalog;
ALTER ROLE app_runtime  IN DATABASE appdb SET search_path = app, pg_catalog;

SET ROLE app_owner;

CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  order_no text NOT NULL,
  status text NOT NULL CHECK (status IN ('CREATED', 'PAID', 'CANCELLED')),
  amount numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, order_no)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders TO app_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_runtime;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;

RESET ROLE;

默认权限只影响将来由指定 owner 创建的对象，不会补授权给既有表。如果迁移工具实际以 app_migrator 而不是 SET ROLE app_owner 创建对象，owner 和默认权限会改变；团队应固定一种模式，避免每次 migration 产生不同对象所有者。

退出管理员会话，用运行账号连接并完成写入、查询和回滚：

docker exec -it \
  -e PGPASSWORD='local_runtime_only' \
  pg18-lab \
  psql 'host=127.0.0.1 port=5432 dbname=appdb user=app_runtime' \
  -X -v ON_ERROR_STOP=1

SELECT current_user, current_database(), current_schema();

INSERT INTO app.orders (tenant_id, order_no, status, amount)
VALUES (42, 'ORD-1001', 'CREATED', 199.00)
RETURNING id, order_no, status, amount, created_at;

BEGIN;
UPDATE app.orders
SET status = 'PAID'
WHERE tenant_id = 42 AND order_no = 'ORD-1001';

SELECT order_no, status
FROM app.orders
WHERE tenant_id = 42 AND order_no = 'ORD-1001';
ROLLBACK;

SELECT order_no, status
FROM app.orders
WHERE tenant_id = 42 AND order_no = 'ORD-1001';

事务内应看到 PAID，回滚后重新变为 CREATED。再尝试 CREATE TABLE app.should_fail(id integer);，运行账号应该收到权限错误。如果 DML 成功但 DDL 被拒绝，身份分层才符合预期。

在 Linux 上以系统服务运行

容器适合统一开发入口，长期运行的 VM 或物理机通常使用发行版包和 systemd。先从 PostgreSQL 官方下载页面进入目标发行版说明，配置 PGDG 或受支持的系统仓库；不要混用多个来源的 server、client 和 extension 包。

Debian/Ubuntu 在 PGDG 仓库已经配置后安装指定 major：

sudo apt update
apt-cache policy postgresql-18 postgresql-client-18
sudo apt install -y postgresql-18 postgresql-client-18

pg_config --version
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager

apt-cache policy 应显示计划使用的仓库和候选版本，pg_lsclusters 会列出 major、cluster 名、端口、状态、owner 与数据目录。RHEL 系在配置 PGDG Yum Repository 后安装对应的 postgresql18-server 包，并先运行发行包提供的 initdb unit；服务名和二进制路径以安装包说明为准。

首次进入实例：

sudo -u postgres psql -X -d postgres

SELECT version();
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW port;

Debian/Ubuntu 的 PGDG cluster 常把配置放在 /etc/postgresql/18/main/，数据放在 /var/lib/postgresql/18/main/；源码安装、容器和其他发行版的路径不同，所以配置前先用 SHOW 查询实际位置。

修改 pg_hba.conf、日志或部分运行参数后，可以检查解析并 reload；shared_buffers、max_connections、wal_level 等需要服务器启动阶段分配或决定行为的参数则要安排 restart：

sudo -u postgres psql -X -d postgres -c \
  "select sourcefile, sourceline, name, setting, error from pg_file_settings where error is not null;"

sudo systemctl reload postgresql@18-main
sudo systemctl restart postgresql@18-main

不要连续执行 reload 与 restart 来“确保生效”。先用 pg_settings.pending_restart 判断哪些变更需要重启；可以在线生效的配置只 reload，必须重启的配置先确认连接排空、主备角色和维护窗口。服务恢复后重新运行身份查询与业务 SQL，而不是只看 systemd 状态。

用两个会话看见 snapshot 和锁

并发问题最好用两个终端观察。两个终端都以 app_runtime 连接到 appdb。

会话 A 先修改订单但不提交：

BEGIN;
UPDATE app.orders
SET amount = amount + 10
WHERE tenant_id = 42 AND order_no = 'ORD-1001';

SELECT pg_backend_pid();

会话 B 设置较短锁等待并修改同一行：

SET lock_timeout = '3s';

UPDATE app.orders
SET status = 'CANCELLED'
WHERE tenant_id = 42 AND order_no = 'ORD-1001';

B 会等待 A 持有的行锁，约三秒后返回 canceling statement due to lock timeout。这不是网络超时，也不是数据库进程卡死，而是两个事务争用同一行。此时可由管理员在第三个会话查看等待关系：

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.wait_event_type,
       blocked.wait_event,
       left(blocked.query, 120) AS blocked_query,
       left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(blocker_pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = bp.blocker_pid;

让 A 执行 ROLLBACK; 后，B 可重新执行更新。实际项目应先缩短事务、统一加锁顺序并设置语句与锁超时；不要把定期终止 blocker 当作永久方案。

再观察 snapshot。会话 A：

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM app.orders WHERE order_no = 'ORD-1001';

会话 B 修改并提交金额：

UPDATE app.orders
SET amount = amount + 20
WHERE order_no = 'ORD-1001';

会话 A 在同一事务中再次查询，仍看到原金额；COMMIT 后再查询才看到 B 的提交。把 A 改为默认 Read Committed，同一事务内第二条语句就能看到已提交的新值。这比背诵隔离级别名称更接近应用真实行为。

用真实数据分布理解索引和计划

索引实验需要足够数据和有差异的分布。下面插入五万条订单，其中租户 42 只占一小部分：

INSERT INTO app.orders (tenant_id, order_no, status, amount, created_at)
SELECT CASE WHEN n % 100 = 0 THEN 42 ELSE n % 500 END,
       'LOAD-' || n,
       CASE WHEN n % 4 = 0 THEN 'PAID' ELSE 'CREATED' END,
       (n % 10000) / 10.0,
       now() - (n || ' seconds')::interval
FROM generate_series(1, 50000) AS g(n);

先看没有专用索引时的执行路径：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM app.orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

运行账号没有创建索引、统计对象和执行 ANALYZE 的权限。完成第一次查询后退出运行会话，再以迁移账号连接并切换为对象 owner：

docker exec -it \
  -e PGPASSWORD='local_migrator_only' \
  pg18-lab \
  psql 'host=127.0.0.1 port=5432 dbname=appdb user=app_migrator' \
  -X -v ON_ERROR_STOP=1

创建与过滤、排序顺序一致的索引，并更新统计信息：

SET ROLE app_owner;

CREATE INDEX idx_orders_tenant_status_created
  ON app.orders (tenant_id, status, created_at DESC)
  INCLUDE (amount);

ANALYZE app.orders;

如果 tenant 与 status 强相关，单列统计可能估错组合选择率。仍在当前 app_migrator 会话且已经 SET ROLE app_owner 时，可以为真正长期相关的列建立 extended statistics：

CREATE STATISTICS st_orders_tenant_status (dependencies, mcv)
ON tenant_id, status
FROM app.orders;

ANALYZE app.orders;

完成索引、统计对象和 ANALYZE 后退出迁移会话，再用 app_runtime 重新连接并运行同一条 EXPLAIN，比较扫描方式、actual rows、读取 buffer 和排序节点。数据量很小或命中比例很高时，Planner 仍可能选择顺序扫描，这是代价判断，不是“索引失效”。不要用 enable_seqscan = off 固化生产配置；它最多用于诊断某条候选路径为何代价偏高。

索引优化的顺序应是：确认 SQL 与参数分布，比较估算和实际行数，检查统计，再设计索引，最后观察写入、WAL 和磁盘增加。只追求某一条查询变快，会把成本转移到每次写入和后续 VACUUM。

把本地实例整理成团队 Compose 工程

个人实验跑通后，团队需要固定镜像、端口、数据目录、初始化脚本和健康检查。下面是适合开发环境的 compose.yaml：

services:
  postgres:
    image: postgres:18.6
    container_name: blog-postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB: ${POSTGRES_DB:-appdb}
      POSTGRES_USER: ${POSTGRES_ADMIN_USER:-postgres}
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_admin_password
    ports:
      - "127.0.0.1:${POSTGRES_PORT:-5432}:5432"
    volumes:
      - postgres_data:/var/lib/postgresql
      - ./postgres/init:/docker-entrypoint-initdb.d:ro
    secrets:
      - postgres_admin_password
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
      interval: 5s
      timeout: 3s
      retries: 20
    shm_size: 256mb

volumes:
  postgres_data:

secrets:
  postgres_admin_password:
    file: ./secrets/postgres_admin_password.txt

secrets/ 和 .env 必须加入 .gitignore。Docker Compose 的本地 secrets 只是避免把密码直接写进 YAML，不等于生产秘密管理系统。共享环境应使用平台提供的秘密存储、轮换和访问审计能力。

docker-entrypoint-initdb.d 中的脚本只在数据目录为空时执行。修改初始化 SQL 后对已有 volume 重启容器，不会重新创建 role 或 schema。开发环境确实需要从零验证时，可以先导出需要的数据，再明确执行：

docker compose down
docker volume ls --filter name=postgres
docker compose down --volumes
docker compose up -d
docker compose logs -f postgres

down --volumes 会删除这个 Compose 项目管理的数据库 volume，应先确认项目名和数据是否可丢弃。共享环境绝不能用它代替迁移。

pg_isready 成功表示服务端愿意接受连接，不表示应用密码、schema、migration 和业务查询都正确。应用启动或冒烟检查还应以运行账号执行身份查询和一条只读业务 SQL。

用 psql 完成日常查询和自动化

psql 随 PostgreSQL 客户端工具发行，是交互查询、对象检查、脚本执行和故障定位的基础入口。它不是图形化管理工具的简化版：psql 更适合可复制命令、批处理和远程终端，pgAdmin 更适合图形化对象浏览、Query Tool、执行计划展示和团队管理场景。

交互连接后先掌握这些元命令：

\conninfo                 -- 当前连接地址、database、user 与 TLS
\dn+                      -- schema 与 owner
\dt+ app.*                -- app schema 中的表与大小
\d+ app.orders            -- 列、约束、索引和存储信息
\x auto                   -- 宽结果自动切换纵向显示
\timing on                -- 显示命令耗时
\pset pager off           -- 自动化终端中关闭分页器

自动化脚本使用 -X 忽略用户 .psqlrc，避免本机别名、输出格式或 ON_ERROR_STOP 设置改变结果；用变量开启错误即停，并检查退出码：

psql \
  --dbname='service=app-dev' \
  -X \
  -v ON_ERROR_STOP=1 \
  --single-transaction \
  --file=sql/backfill_order_status.sql

--single-transaction 会把脚本包进一个事务，但包含 CREATE DATABASE、VACUUM 或其他不能在事务块中执行的命令时不能使用。DDL 是否会长时间锁表仍要在同规模数据上预先观察。

psql 退出码含义不同：0 表示正常结束，1 表示 psql 自身发生致命错误，2 表示非交互会话中的服务端连接中断，3 表示脚本启用 ON_ERROR_STOP 后因 SQL 错误停止。CI 不能只保存控制台最后一行，应保留退出码和服务端错误。

连接参数可以放进 service file，避免每条命令重复 host、port、database 与 TLS：

# ~/.pg_service.conf
[app-dev]
host=db.dev.example.internal
port=5432
dbname=appdb
user=app_runtime
sslmode=verify-full
sslrootcert=/etc/app/db-ca.pem
connect_timeout=3
application_name=order-cli

默认 service file 可通过 PGSERVICEFILE 改变，目标段落用 PGSERVICE=app-dev 或 service=app-dev 选择。密码放进权限为 0600 的 passfile，路径可用 PGPASSFILE 指定：

db.dev.example.internal:5432:appdb:app_runtime:replace_from_secret_store

export PGSERVICEFILE="$PWD/config/pg_service.conf"
export PGPASSFILE="$PWD/secrets/pgpass"
chmod 600 "$PGPASSFILE"
psql 'service=app-dev' -X -c 'select current_user, current_database();'

passfile 与 service file 都不应提交真实生产凭证。需要隔离历史记录时，在 psql 中设置 HISTFILE 到项目外目录；包含敏感字面量的命令不要进入历史文件。

导入本机文件时区分 COPY 与 \copy。COPY ... FROM '/path/file.csv' 由数据库服务端读取服务端路径，需要服务端权限；\copy 由 psql 客户端读取本机文件，再通过连接传输，更适合开发者工作站：

\copy app.orders(tenant_id, order_no, status, amount, created_at) FROM './orders.csv' WITH (FORMAT csv, HEADER true)

大量导入前确认 CSV 类型、时区、约束失败策略和事务大小，导入后运行 ANALYZE app.orders;。\copy 不是绕过权限的后门，写入仍按当前 role 的表权限与约束执行。

用 migration 管理对象，不让应用自动改表

数据库结构应由版本化 migration 管理。每个变更拥有唯一顺序、可审查 SQL 和明确的前向修复策略；应用运行账号只执行业务 DML。Spring Boot 项目可以使用 Flyway：

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-database-postgresql</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>

依赖版本由 Spring Boot BOM 和项目依赖治理统一锁定。运行连接与迁移连接使用不同身份：

spring:
  datasource:
    url: jdbc:postgresql://${PG_HOST:127.0.0.1}:${PG_PORT:5432}/${PG_DATABASE:appdb}?currentSchema=app
    username: ${PG_RUNTIME_USER}
    password: ${PG_RUNTIME_PASSWORD}
    hikari:
      maximum-pool-size: ${PG_POOL_MAX:10}
      minimum-idle: ${PG_POOL_MIN:2}
      connection-timeout: 3000
      validation-timeout: 1000
      max-lifetime: 1800000
  flyway:
    enabled: true
    url: jdbc:postgresql://${PG_HOST:127.0.0.1}:${PG_PORT:5432}/${PG_DATABASE:appdb}
    user: ${PG_MIGRATOR_USER}
    password: ${PG_MIGRATOR_PASSWORD}
    default-schema: app
    init-sqls:
      - SET ROLE app_owner

init-sqls 让 Flyway 每条连接先切换到 app_owner，因此 schema history 和业务对象由稳定的 NOLOGIN role 持有，前面针对 app_owner 设置的默认权限也会生效。迁移完成后检查 owner 和运行权限：

SELECT n.nspname AS schema_name,
       c.relname,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
  AND c.relkind IN ('r', 'p', 'S')
ORDER BY c.relname;

SELECT has_table_privilege('app_runtime', 'app.orders', 'SELECT') AS can_select,
       has_table_privilege('app_runtime', 'app.orders', 'INSERT') AS can_insert,
       has_table_privilege('app_runtime', 'app.orders', 'UPDATE') AS can_update,
       has_table_privilege('app_runtime', 'app.orders', 'DELETE') AS can_delete;

业务表 owner 应为 app_owner，四个权限结果都应为 true。生产环境不要把 migration 密码长期发给每个应用副本。更稳妥的方式是由发布任务在应用放量前运行 migration，运行完成后撤销或回收该凭证。多个应用副本同时执行 DDL 会放大锁等待和失败处理难度。

业务代码应使用参数绑定、显式事务边界和受控超时。下面的类包含构造注入、结果映射、状态转换与一个主动失败的事务路径：

package com.example.orders;

import java.math.BigDecimal;
import java.time.OffsetDateTime;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderService {
    private static final RowMapper<OrderRow> ORDER_MAPPER = (rs, rowNum) ->
        new OrderRow(
            rs.getLong("id"),
            rs.getString("order_no"),
            rs.getString("status"),
            rs.getBigDecimal("amount"),
            rs.getObject("created_at", OffsetDateTime.class)
        );

    private final JdbcTemplate jdbc;

    public OrderService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional
    public OrderRow markPaid(long tenantId, String orderNo) {
        changeCreatedToPaid(tenantId, orderNo);
        return jdbc.queryForObject("""
            select id, order_no, status, amount, created_at
              from app.orders
             where tenant_id = ? and order_no = ?
            """, ORDER_MAPPER, tenantId, orderNo);
    }

    @Transactional
    public void markPaidThenFail(long tenantId, String orderNo) {
        changeCreatedToPaid(tenantId, orderNo);
        throw new IllegalStateException("simulate downstream failure");
    }

    private void changeCreatedToPaid(long tenantId, String orderNo) {
        int changed = jdbc.update("""
            update app.orders
               set status = 'PAID'
             where tenant_id = ?
               and order_no = ?
               and status = 'CREATED'
            """, tenantId, orderNo);

        if (changed != 1) {
            throw new IllegalStateException("order is missing or not payable");
        }
    }

    public record OrderRow(
        long id,
        String orderNo,
        String status,
        BigDecimal amount,
        OffsetDateTime createdAt
    ) {}
}

最小集成测试复用前面的 PostgreSQL 表。每个测试先按业务唯一键恢复同一条订单，再检查成功转换、重复转换拒绝和异常回滚：

package com.example.orders;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootTest
class OrderServiceTest {
    @Autowired OrderService service;
    @Autowired JdbcTemplate jdbc;

    @BeforeEach
    void resetOrder() {
        jdbc.update("""
            insert into app.orders(tenant_id, order_no, status, amount)
            values (42, 'ORD-TEST', 'CREATED', 199.00)
            on conflict (tenant_id, order_no)
            do update set status = 'CREATED', amount = excluded.amount
            """);
    }

    @Test
    void changesCreatedOrderOnlyOnce() {
        assertThat(service.markPaid(42, "ORD-TEST").status()).isEqualTo("PAID");
        assertThatThrownBy(() -> service.markPaid(42, "ORD-TEST"))
            .isInstanceOf(IllegalStateException.class);
    }

    @Test
    void rollsBackWhenTransactionFails() {
        assertThatThrownBy(() -> service.markPaidThenFail(42, "ORD-TEST"))
            .isInstanceOf(IllegalStateException.class);

        String status = jdbc.queryForObject(
            "select status from app.orders where tenant_id = ? and order_no = ?",
            String.class,
            42,
            "ORD-TEST"
        );
        assertThat(status).isEqualTo("CREATED");
    }
}

把远程调用放在事务提交之后，或者采用 outbox 等明确的一致性模式。对 40001 与 40P01 可以在业务操作幂等的前提下重试整个事务；唯一约束、外键、认证失败或连接中断不能一律重试。连接在提交阶段断开还可能形成“结果未知”，应用需要用业务幂等键查询最终状态，而不是直接重复扣款。

管理连接、超时和内存

应用连接池总量是所有副本、后台任务、migration、监控和管理连接的总和。假设每个应用副本池上限为 20，滚动发布时最多同时存在 8 个副本，仅应用就可能建立 160 条连接。max_connections 调大不会让 CPU、内存和锁吞吐同比增加。

先从业务并发与数据库实际吞吐推导池大小，再保留管理和故障余量。应用端至少区分：获取连接超时、建立连接超时、socket 读取超时、SQL statement timeout、lock timeout 和事务总时长。一个模糊的 30 秒总超时很难判断问题发生在哪一层。

SELECT application_name, usename, state, count(*)
FROM pg_stat_activity
GROUP BY application_name, usename, state
ORDER BY count(*) DESC;

SELECT pid, usename, application_name, state,
       xact_start, query_start,
       wait_event_type, wait_event,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

PgBouncer 的 transaction pooling 能让更多客户端共享较少服务端连接，但会改变 session 语义。依赖 session-level advisory lock、临时表、某些 prepared statement 行为或连接级 SET 的应用必须先测试；不能把 PgBouncer 当作无条件透明代理。

内存同样不能只看 shared_buffers。每个排序或哈希节点可能使用 work_mem，并行查询和多连接会放大总量；maintenance 操作使用 maintenance_work_mem；未命中内存的排序会写临时文件。排查内存和磁盘时要同时看并发计划、临时文件日志、连接数和操作系统缓存。

建立认证、TLS 和最小权限边界

远程连接同时受 listen_addresses、网络防火墙、pg_hba.conf、TLS 和 role 密码影响。端口可达只完成了网络路径，认证成功也不代表对象权限正确。

pg_hba.conf 从上到下匹配，命中第一条规则后不会继续寻找“更合适”的规则。共享环境优先使用 hostssl 与 scram-sha-256，避免 trust；MD5 密码认证已经被官方标记为弃用。示例：

# TYPE     DATABASE  USER         ADDRESS         METHOD
hostssl    appdb     app_runtime  10.30.0.0/24    scram-sha-256
hostssl    appdb     app_readonly 10.31.0.0/24    scram-sha-256
hostnossl  all       all          0.0.0.0/0       reject

修改后先检查解析错误，再重载：

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

SELECT pg_reload_conf();

客户端生产连接应使用 sslmode=verify-full，并提供可信 CA。require 只要求加密，不会完整校验服务端身份；verify-full 同时验证证书链与主机名。证书轮换需要覆盖连接池重建和副本、代理、备份程序等所有客户端。

运行 role 不应拥有 SUPERUSER、CREATEDB、CREATEROLE 或 schema CREATE。只读 role 除了表 SELECT，还要考虑默认权限、sequence、函数执行和未来新表。高权限运维操作通过短期身份完成，不把管理员凭证放进应用环境变量。

逻辑备份解决对象迁移，物理备份解决整套恢复

pg_dump 生成一致的逻辑快照，适合单个 database、schema 或表的迁移与长期可读备份。它不包含 cluster 级 role 和 tablespace 定义；这些可用 pg_dumpall --globals-only 单独导出。

mkdir -p backup
umask 077

PGPASSWORD="$PG_BACKUP_PASSWORD" pg_dump \
  --host=db.example.internal \
  --username=app_backup \
  --dbname=appdb \
  --format=custom \
  --file=backup/appdb.dump

PGPASSWORD="$PG_ADMIN_PASSWORD" pg_dumpall \
  --host=db.example.internal \
  --username=postgres \
  --globals-only \
  --no-role-passwords \
  > backup/globals.sql

umask 077 让新文件只对当前操作系统用户可读写。示例假设 role 密码由秘密系统在恢复后重新设置，所以不导出 password verifier；确实需要保留 verifier 时，globals 文件必须加密保存并限制访问。

备份文件存在不等于能够恢复。新建隔离 database，先列出内容，再执行恢复并核对 owner、权限、序列和业务数据：

pg_restore --list backup/appdb.dump | less

createdb --host=127.0.0.1 --username=postgres appdb_restore

pg_restore \
  --host=127.0.0.1 \
  --username=postgres \
  --dbname=appdb_restore \
  --clean --if-exists \
  backup/appdb.dump

大库使用 directory format 和 --jobs 可以并行 dump/restore，但会增加源库 I/O 与锁持有。逻辑备份恢复时间随对象和数据规模增长，不能用一个小型开发库的速度推算生产 RTO。

用 base backup 与 WAL 完成时间点恢复

PITR 需要一份可用的 base backup，以及从该备份起点开始连续可读的 WAL 归档。先在 primary 准备只有 postgres 操作系统用户可访问的实验目录：

sudo install -d -o postgres -g postgres -m 0700 \
  /srv/postgresql-backup \
  /srv/postgresql-backup/wal \
  /srv/postgresql-backup/base \
  /srv/postgresql-backup/base/latest

sudo install -o postgres -g postgres -m 0600 \
  /run/secrets/postgres-replication-pass \
  /srv/postgresql-backup/pgpass

复制 passfile 的一行格式为 primary-db.example.internal:5432:replication:replicator:实际密码。/run/secrets/postgres-replication-pass 代表平台提供的临时秘密文件，不进入仓库；第三个字段使用 replication，匹配物理复制连接。

primary 的基础配置可以从下面几项开始：

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /srv/postgresql-backup/wal/%f && cp %p /srv/postgresql-backup/wal/%f'
max_wal_senders = 10
max_replication_slots = 10

本地文件复制只适合实验。生产归档命令必须处理远端对象存储、重试、不可覆盖、加密、校验、监控和容量；命令返回成功但文件没有持久保存，会悄悄截断恢复链。

创建 base backup 前先确认归档正在推进：

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

SELECT pg_switch_wal();

随后用复制身份执行 pg_basebackup：

sudo -u postgres env \
  PGPASSFILE=/srv/postgresql-backup/pgpass \
  pg_basebackup \
  --host=primary-db.example.internal \
  --port=5432 \
  --username=replicator \
  --pgdata=/srv/postgresql-backup/base/latest \
  --format=plain \
  --wal-method=stream \
  --checkpoint=fast \
  --progress

sudo -u postgres pg_verifybackup /srv/postgresql-backup/base/latest

pg_verifybackup 校验 backup manifest、文件和校验和，但仍不能替代启动恢复。恢复节点必须与 primary 隔离写流量，并使用不同端口。先复制 base backup，保留原始副本不变：

sudo install -d -o postgres -g postgres -m 0700 \
  /srv/postgresql-restore \
  /srv/postgresql-restore/pgdata \
  /srv/postgresql-restore/socket

sudo -u postgres cp -a \
  /srv/postgresql-backup/base/latest/. \
  /srv/postgresql-restore/pgdata/

sudo -u postgres test -f /srv/postgresql-restore/pgdata/PG_VERSION
sudo -u postgres test -f /srv/postgresql-restore/pgdata/backup_manifest

把恢复参数写入这一个恢复副本的 postgresql.auto.conf。目标时间必须来自事故时间线，并包含明确时区：

sudo -u postgres tee -a \
  /srv/postgresql-restore/pgdata/postgresql.auto.conf \
  >/dev/null <<'CONF'
restore_command = 'cp /srv/postgresql-backup/wal/%f %p'
recovery_target_time = 'YYYY-MM-DD HH:MM:SS+08'
recovery_target_action = 'pause'
listen_addresses = '127.0.0.1'
port = 55432
unix_socket_directories = '/srv/postgresql-restore/socket'
CONF

sudo -u postgres touch /srv/postgresql-restore/pgdata/recovery.signal

PGBIN=$(pg_config --bindir)
sudo -u postgres "$PGBIN/pg_ctl" \
  -D /srv/postgresql-restore/pgdata \
  -l /srv/postgresql-restore/recovery.log \
  start

等待日志显示到达 recovery target，再从本机连接隔离端口：

psql -h 127.0.0.1 -p 55432 -U postgres -d appdb -X

SELECT pg_is_in_recovery(),
       pg_is_wal_replay_paused(),
       pg_last_wal_replay_lsn();

到达目标并暂停时，前两个布尔值都应为 true。先只读检查关键业务状态、migration、sequence、role 和 extension。目标正确再用 SELECT pg_wal_replay_resume(); 结束暂停并形成新 timeline；随后为这个新实例建立独立备份与归档路径。目标不正确就用 pg_ctl 停止隔离实例，删除这一个明确的恢复副本，再从未修改的 base backup 重新复制；不要在唯一备份副本上反复修改。

RPO 是事故后实际没有恢复的数据时间窗口，RTO 是从发现到恢复业务能力的完整时间。它们应由真实恢复演练测得，而不是由“每天备份一次”或“有一个 standby”推断。

从物理 standby 走向高可用

primary 需要复制 role 和匹配的 HBA 规则：

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'replace_from_secret_store';

hostssl replication replicator 10.40.0.0/24 scram-sha-256

在空的 standby 数据目录执行：

pg_basebackup \
  --host=primary-db.example.internal \
  --username=replicator \
  --pgdata=/var/lib/postgresql/18/main \
  --write-recovery-conf \
  --create-slot \
  --slot=standby_a \
  --wal-method=stream \
  --progress

--write-recovery-conf 会写入连接信息并创建 standby.signal。replication slot（复制槽）让 primary 保留指定消费者尚未读取的 WAL；凭证不要长期明文留在 postgresql.auto.conf，应使用受控的 passfile、证书或平台秘密注入。启动 standby 后，在 primary 查看：

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

在 standby 查看：

SELECT pg_is_in_recovery(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

复制槽会防止 primary 删除 standby 尚未消费的 WAL，但 standby 长时间离线时也可能把 primary 磁盘撑满。必须监控 slot 的 active 状态、restart_lsn 与保留字节，并为重建副本定义边界。

计划切换时，先停止或隔离旧 primary 的写入口，再确认 standby 已追平并执行 promote，最后更新服务发现、代理和连接池。真实网络分区必须依靠云平台 fencing、STONITH、watchdog 或等价机制隔离旧主；仅凭“我连不上旧主”不足以保证它不能继续接受其他客户端写入。

提升后的旧 primary 不能原样重新作为可写节点启动。先保留故障现场，再使用 pg_rewind 或新的 base backup 把它重建为新 primary 的 standby。应用还要处理连接断开、只读错误和未知提交结果；数据库完成 promote 不等于业务流量已经收敛。

升级和清理都要知道自己在改变什么

minor upgrade 在同一 major 内替换二进制并重启，通常不需要改变数据格式，但仍应阅读发行说明并检查 extension、驱动和恢复工具。容器环境应固定完整 tag，先拉取新 minor、备份并在隔离环境启动，再进入维护窗口替换镜像。

major upgrade 常见四条路径：

路径	优点	主要限制
dump/restore	逻辑清晰，可重组对象和清理历史	大库停机时间和恢复时间可能很长
pg_upgrade	速度快，可使用 link/clone 模式	需要新旧二进制与兼容 extension，回退边界严格
逻辑复制	可降低写停机窗口并跨 major	DDL、sequence、大对象和切换一致性要单独处理
托管迁移服务	自动化数据搬迁和监控	功能边界、费用、网络和厂商依赖需要确认

使用 pg_upgrade 时先运行 --check，核对 encoding、locale、data checksum、extension 和新旧配置；正式执行前停止所有 writer、migration、CDC 与定时任务。link 模式一旦用新 cluster 启动，旧数据目录不能继续当作安全回退副本。

清理本地实验前确认容器名和 volume：

docker inspect pg18-lab --format '{{json .Mounts}}'
docker stop pg18-lab
docker rm pg18-lab
docker volume inspect pg18-lab-data
docker volume rm pg18-lab-data

删除 volume 后数据无法从容器恢复。需要保留实验结果时，先用 pg_dump 导出并在另一个临时实例完成一次恢复，再删除原 volume。

四、问题处理

先判断故障发生在哪一层

PostgreSQL 故障经常以“应用超时”表现，但根因可能在完全不同的位置。定位顺序是：服务进程与监听地址 → DNS、路由、防火墙和 TLS → HBA、role、密码与证书 → database、schema、owner 和对象权限 → SQL 执行、锁与 I/O 等待 → 事务、连接、内存、临时文件、WAL 与磁盘 → standby、归档和备份链。沿这条顺序缩小范围，通常比直接重启数据库更快。

先保留当前活动、等待事件和日志时间点，再决定是否取消语句、终止会话、切流或重启。重启会清掉等待现场，并可能让连接风暴重新冲击尚未解决的瓶颈。

服务未启动或端口无法连接

客户端常见现象是 Connection refused、连接超时或容器反复重启。拒绝连接通常表示网络已经到达目标主机，但目标端口没有监听；超时更可能来自路由、防火墙、安全组或错误地址。

在 Linux 服务端先检查服务、进程、监听和近期日志：

systemctl status postgresql --no-pager
pg_lsclusters
ss -lntp | grep 5432
journalctl -u postgresql --since '-15 minutes' --no-pager

不同发行版的 systemd unit 与 cluster 管理方式不同。Debian/Ubuntu 的 pg_lsclusters 能显示 major、cluster 名、端口、状态和数据目录；不要看到总 unit 为 active 就假设目标实例正在监听。

容器环境检查容器状态、端口映射和日志：

docker ps -a --filter name=pg18-lab
docker inspect pg18-lab --format '{{json .State}}'
docker port pg18-lab
docker logs --tail 200 pg18-lab

常见根因包括端口被占用、数据目录 owner 错误、磁盘已满、配置语法错误、major version 与数据目录不兼容、容器挂载到了错误路径。日志出现 database files are incompatible with server 时，停止替换 tag；找回与数据目录匹配的旧 major，再规划 pg_upgrade 或逻辑迁移。

服务端确实监听后，从应用所在主机测试 DNS 和 TCP：

getent hosts db.example.internal
nc -vz db.example.internal 5432

不要用临时开放 0.0.0.0/0 作为长期修复。修正真实监听地址、网段和防火墙后，再从应用路径复测。

TLS、HBA 或密码导致认证失败

no pg_hba.conf entry 表示连接已经到达 PostgreSQL，但没有规则匹配给定的来源地址、database、user 和加密方式。password authentication failed 表示规则已经匹配到密码认证，role、密码或密码存储方式不一致。证书主机名错误、CA 不可信和过期证书则会在 TLS 阶段失败。

先让客户端输出明确的连接参数并使用完整校验：

PGPASSWORD="$PG_RUNTIME_PASSWORD" psql \
  'host=db.example.internal port=5432 dbname=appdb user=app_runtime sslmode=verify-full sslrootcert=/etc/app/db-ca.pem connect_timeout=3' \
  -X -c 'select current_user, current_database(), inet_server_addr(), inet_server_port();'

在服务端查看配置实际来自哪里，并检查 HBA 解析：

SHOW config_file;
SHOW hba_file;
SHOW ssl;

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
ORDER BY line_number;

HBA 从上到下命中第一条，不会在认证失败后继续尝试下一条。把一条宽泛规则放在前面，会遮住后面的精确规则。修正规则后执行 SELECT pg_reload_conf();，再建立新连接；既有连接不会因为 HBA 改变而重新认证。

改 role 密码后应用仍失败，先确认它连接的是正确实例和 database，再检查秘密系统是否更新、应用是否重启连接池、URL 是否包含旧密码。不要把密码打印到日志或命令诊断输出。

连接成功却找不到表或没有权限

常见错误包括 relation does not exist、permission denied for schema、permission denied for table 和 permission denied for sequence。它们分别可能来自对象名称解析、schema USAGE、表 DML 和 identity/serial sequence 权限。

先确认身份和路径：

SELECT current_user, session_user, current_database(), current_schema();
SHOW search_path;

SELECT n.nspname AS schema_name,
       c.relname AS object_name,
       c.relkind,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relname = 'orders';

再检查具体权限，而不是直接授予整个 database：

SELECT has_database_privilege('app_runtime', 'appdb', 'CONNECT') AS can_connect,
       has_schema_privilege('app_runtime', 'app', 'USAGE') AS can_use_schema,
       has_table_privilege('app_runtime', 'app.orders', 'SELECT') AS can_select,
       has_table_privilege('app_runtime', 'app.orders', 'INSERT') AS can_insert,
       has_table_privilege('app_runtime', 'app.orders', 'UPDATE') AS can_update,
       has_table_privilege('app_runtime', 'app.orders', 'DELETE') AS can_delete;

表由错误 role 创建时，未来默认权限也可能继续错。修复方式通常是把 owner 统一到 app_owner，由 owner 设置现有对象权限和 ALTER DEFAULT PRIVILEGES，并让后续 migration 固定使用 owner 语义。不要为了省事让运行账号成为 schema owner。

初始化脚本“没有生效”时检查数据 volume。Docker entrypoint 只在空目录执行初始化文件；已有数据应使用 migration 修改，确实要重建的本地环境才删除明确的 volume。

慢查询先区分执行、等待和结果传输

查询慢时先看它是 active 计算、等待锁、等待 I/O，还是 idle in transaction：

SELECT pid, usename, application_name, state,
       now() - query_start AS query_age,
       now() - xact_start AS transaction_age,
       wait_event_type, wait_event,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;

wait_event_type = 'Lock' 时优先找 blocker；IO 时检查读取、临时文件和存储延迟；没有等待事件且 CPU 高时再分析执行计划。应用总耗时很高但 SQL 服务端很快，可能是连接池排队、返回行太多、网络或反序列化。

对可安全执行的查询获取真实计划：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE)
SELECT ...;

重点比较估算行数和实际行数。估算错误通常来自统计信息旧、数据倾斜、相关列或参数分布；计划正确但读取量大，则要重新审视查询范围、索引、分区和返回数据量。临时文件增长可以结合日志中的 log_temp_files 与计划里的 sort/hash 节点定位。

完成索引调整后同时观察写延迟、WAL、索引大小和 autovacuum。把所有过滤列都建成索引会让读查询局部变快，却可能让写入和维护持续恶化。

锁等待、死锁和长事务相互放大

锁等待的第一步不是终止所有会话，而是找出阻塞链：

SELECT a.pid,
       a.usename,
       a.application_name,
       a.state,
       a.xact_start,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid) AS blocking_pids,
       left(a.query, 160) AS query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.xact_start;

长时间 idle in transaction 会话即使没有运行 SQL，也可能保留 snapshot 和锁，阻止 VACUUM 清理旧版本。检查它来自哪个 application_name、事务从何时开始、业务请求是否还活着。可以为应用 role 设置合理的 idle_in_transaction_session_timeout，但永久修复仍是让代码尽快提交或回滚。

死锁日志会包含相互等待的事务和 SQL。常见根因是不同代码路径以相反顺序更新多行或多表。统一加锁顺序、缩小事务批次，并对 40P01 重试整个幂等事务。仅增加 deadlock_timeout 或让应用无限重试，会延长失败和放大负载。

需要立即止损时，先取消正在执行的语句：

SELECT pg_cancel_backend(12345);

只有会话无法退出且业务允许回滚整个事务时，才终止 backend：

SELECT pg_terminate_backend(12345);

替换 12345 前确认 pid、user、application、事务和 SQL。终止关键 migration 或维护会话可能留下需要前向修复的部分工作。

连接数打满时不要只调大上限

客户端可能收到 remaining connection slots are reserved，已有请求则因为池获取连接超时而堆积。先按来源统计连接：

SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY usename, application_name, client_addr, state
ORDER BY count(*) DESC;

确认是副本数增加、连接泄漏、池参数过大、慢 SQL 占住连接，还是大量 idle in transaction。短期可以限流、缩小异常服务的副本或池、取消无价值查询；不要在没有内存与 CPU 余量判断时直接把 max_connections 翻倍。

永久修复要把总连接预算写成算式：每类应用最大副本数乘单实例池上限，再加 migration、任务、监控、DBA 和故障余量。应用关闭或滚动发布时应正确释放池。需要 PgBouncer 时先检查 transaction pooling 与 session 特性的兼容性。

若连接风暴来自数据库刚恢复后所有客户端同时重试，应在应用侧使用带抖动的退避、全局并发上限和熔断，避免每次数据库恢复都被第二波连接压垮。

dead tuple、膨胀和 autovacuum 跟不上

现象可能是表和索引不断变大、查询读取量增加、事务 ID 年龄逼近风险区，或日志反复出现 autovacuum 被取消。先同时观察表变化、长事务和 VACUUM 进度：

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count,
       last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

SELECT pid, datname, usename, state, xact_start,
       backend_xmin, left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT pid, datname, relid::regclass,
       phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

先结束或修复不合理的长事务，解除让 autovacuum 无法取得锁的 DDL，再确认磁盘和 I/O 有余量。高更新大表可以使用表级参数提前触发，而不是把整个 cluster 的 scale factor 一次降得很低：

ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

参数值必须根据表规模和更新率迭代。调整后观察 dead tuple 趋势、任务持续时间、业务延迟和 WAL，而不是只看一次手工 VACUUM 是否完成。

普通 VACUUM 后文件不缩小是预期行为，空间会供该表复用。确实需要归还操作系统空间时，可以评估 pg_repack、分区轮换或 VACUUM FULL；它们需要额外空间、锁或扩展依赖，应在维护窗口执行。

WAL 激增、归档失败或 standby 延迟

primary 磁盘中的 pg_wal 持续增长，常见原因是归档失败、复制槽长期不前进、standby 断连或大事务产生大量 WAL。先查看归档、槽和复制状态：

SELECT archived_count, failed_count,
       last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap
FROM pg_stat_replication;

归档失败先修目标存储容量、权限、网络和归档命令，再执行 SELECT pg_switch_wal(); 确认新 WAL 能归档。standby 断连则检查网络、TLS、磁盘、进程与日志；所需 WAL 已被删除时，需要从新的 base backup 重建。

不要直接删除 pg_wal 文件来腾空间，这会破坏崩溃恢复和复制。逻辑 slot 对应的消费者永久下线时，可以在确认不再需要其变更后删除明确的 slot；尚需恢复的消费者则应修复消费链或重建订阅。

同步复制下，required standby 不可用还可能让提交等待。查看 pg_stat_activity.wait_event = 'SyncRep' 和 synchronous_standby_names，按既定一致性策略恢复副本或进行受控降级；临时关闭同步等待会扩大数据丢失窗口，不能作为无影响的性能开关。

备份存在却无法恢复

逻辑恢复失败常见于缺少 role、owner 不存在、extension 未安装、目标 schema 已有冲突对象或备份版本过旧。先用 pg_restore --list 查看内容和顺序，在隔离 database 恢复；不要直接对生产 database 加 --clean 试错。

物理恢复失败则检查 base backup 与 WAL 是否属于同一 system identifier 和 timeline，WAL 是否从备份起点连续，restore_command 能否读取正确文件，目标时间是否带正确时区。恢复日志里的第一个缺失 WAL 文件比后续重复报错更有价值。

PITR 到达目标后如果关键业务状态不正确，停止该隔离实例，从原始 base backup 重新开始。不要让错误目标生成的新 timeline 混入原归档路径，也不要覆盖唯一的 base backup。

恢复完成后不仅要数表。还要检查 role 与 owner、schema 权限、extension、sequence 是否落后、关键业务聚合是否一致，以及应用能否用真实运行账号完成只读和受控写入。只有应用数据路径恢复，数据库文件恢复才真正有用。

故障切换后仍然写不进去

standby promote 成功后，应用仍可能连接旧地址、DNS 缓存未刷新、代理健康检查只测端口、连接池保留旧连接，或新 primary 的证书与 HBA 不接受应用网段。

先在新节点确认状态和 timeline：

SELECT pg_is_in_recovery(), pg_current_wal_lsn();
SELECT timeline_id FROM pg_control_checkpoint();

再从应用主机通过正式 service endpoint 连接，确认服务端地址、身份与可写性：

SELECT inet_server_addr(), inet_server_port(),
       current_user, current_database(), pg_is_in_recovery();

如果 pg_is_in_recovery() 仍为 true，连接还在 standby；如果为 false 但写入失败，继续检查 transaction read-only、role、schema 与路由层。应用池要主动清理旧连接，后台任务、CDC、只读入口和 migration 也要逐一收敛。

最危险的情况是旧 primary 仍能被一部分客户端写入。此时先停止所有写入口并完成 fencing，不要尝试让两边继续服务再“同步回来”。保留两条 timeline 的数据与 WAL，评估业务差异后选择唯一 primary；旧节点用 pg_rewind 或 base backup 重建。

major upgrade 后 extension、排序或计划异常

启动失败先核对 PG_VERSION 与服务端 major，不能用新二进制直接打开旧数据目录。pg_upgrade --check 报告 extension 缺失时，在新版本安装兼容的共享库与 extension 文件，再重新检查；不要手工删除 catalog 中的 extension 记录。

升级成功但排序或唯一约束表现变化，检查 libc/ICU 版本与 collation：

SELECT collname, collprovider, collversion,
       pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collversion IS DISTINCT FROM pg_collation_actual_version(oid);

只有理解排序变化影响并完成重复值检查后，才刷新 collation version 和重建相关索引。直接执行 ALTER COLLATION ... REFRESH VERSION 只更新元数据，不会修复旧索引顺序。

查询计划变化时重新 ANALYZE，比较升级前后真实参数的 EXPLAIN (ANALYZE, BUFFERS)，并检查配置是否遗漏、统计目标和 extension 是否变化。不要用全局禁用某种 scan 或 join 方法来掩盖单条计划回归。

升级窗口结束前，应保留旧版本可读备份和清晰的前进修复路径。已经让新 major 接受写入后，直接启动旧数据目录会丢失新写入；回退需要预先设计的逻辑同步、停写窗口或完整恢复，而不是简单换回镜像标签。

最后的判断回到数据路径

PostgreSQL 的常见问题看起来分散，最终都能回到几条基础链：连接由 postmaster 和 backend 承担；对象由 database、schema、role 与 owner 定位；查询由统计与计划驱动；并发由 snapshot、tuple 版本和锁共同决定；旧版本由 VACUUM 维护；持久化、复制和恢复由 WAL 串联。

遇到故障时先找到断在哪一条链，再选择对应工具。这样既不会把权限错误当网络问题，也不会把复制延迟误判为“数据库太慢”，更不会在尚未理解 timeline 和恢复目标时仓促切换主库。