05.3.2.5 高可用、连接池与运维监控
所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL / 05.3.2 索引优化与高可用运维
覆盖范围
- 高可用架构与 Patroni 选主切换
- PgBouncer 模式、配置与连接池坑
- 关键参数调优与必须监控指标
- 常见故障处理、大版本升级、权限安全
- 容量规划、在线变更、审计合规与运维清单
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.3.2.5-001 | 高可用与 Patroni 原理 | 高级 | ★★★★☆ |
| 05.3.2.5-002 | PgBouncer 配置与坑 | 进阶 | ★★★★☆ |
| 05.3.2.5-004 | 必须监控指标 | 进阶 | ★★★★☆ |
| 05.3.2.5-005 | 常见故障处理 | 高级 | ★★★★☆ |
| 05.3.2.5-006 | 大版本升级方案 | 高级 | ★★★☆☆ |
运维主线
高可用:健康检查 → 共识选主 → 提升备库 → 路由切换 → 旧主隔离
连接:PgBouncer 收敛后端连接(session/transaction/statement)
监控:连接/复制/回卷/膨胀/慢查询/锁/缓存/磁盘
变更:在线扩展、备份演练、权限最小化、审计合规
05.3.2.5-001 PostgreSQL 高可用怎么做?Patroni 原理?
频率 ★★★★☆ | 难度 高级
一句话结论
常见方案 Patroni + etcd/Consul 做分布式选主,配合 VIP/HAProxy 切流量;每节点 agent 做健康检查,通过共识存储争抢 leader key,持有者为主库;主库故障时备库提升并更新集群信息,跟随新主。替代方案有 pg_auto_failover、repmgr 与云托管。
概念解释
- 共识选主:分布式存储多数派防止脑裂。
- agent 管理:健康检查、提升、配置更新与跟随。
- 路由切换:VIP/代理指向新主。
原理流程
- 各节点 agent 上报健康状态。
- 持 leader key 节点为主库。
- 主故障后合格备库经共识提升,路由切换。
代码 / 命令
Patroni:agent + etcd/Consul 共识 → leader 选举 → 提升备库 → 刷新路由
常见误区
- 误区:高可用切换不会丢数据。正解:异步复制故障瞬间可能丢未同步事务,按 RPO 设计。
- 误区:双主同时对外写。正解:共识保证单一 leader,旧主需 fencing 隔离防回写。
追问
- 问:脑裂怎么防?答:共识多数派 + 只有持 leader key 可提升,旧主隔离。
- 问:切换后应用怎么连?答:VIP/代理更新指向新主,或客户端多地址重连。
05.3.2.5-002 PgBouncer 怎么配置?有哪些坑?
频率 ★★★★☆ | 难度 进阶
一句话结论
PgBouncer 配置数据库连接、监听、认证与池模式;transaction 模式最常用,连接事务结束归还;坑:SET、临时表、游标、advisory lock 不能跨事务保持,预编译语句需特定支持;监控池与统计,池大小按 DB 并发与 CPU 评估。
概念解释
- 池模式:session / transaction / statement。
- 会话重置:归还连接执行 DISCARD ALL 清理状态。
- 池大小:后端连接数上限,非越大越好。
原理流程
- 应用连 PgBouncer,不直连后端。
- 按模式分配/归还后端连接。
- 归还时重置会话状态。
代码 / 命令
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
server_reset_query = DISCARD ALL
常见误区
- 误区:transaction 模式会话设置跨请求保持。正解:连接归还重置,需事务内重建。
- 误区:连接池越大吞吐越高。正解:后端进程数受内存/CPU 约束,过大反降性能。
追问
- 问:为什么需要 DISCARD ALL?答:复用连接时清理会话级状态,避免泄漏给下一用户。
- 问:池化会掩盖慢查询吗?答:会,需同时监控 DB 侧语句耗时。
05.3.2.5-003 关键参数怎么调?
频率 ★★★★☆ | 难度 高级
一句话结论
核心参数:shared_buffers、effective_cache_size、work_mem、maintenance_work_mem、WAL 相关、checkpoint、随机读成本、max_connections 与 autovacuum;按 workload 基线压测调整,不照搬通用公式,work_mem 是每节点每查询需折算并发总量。
概念解释
- 内存类:共享缓冲、排序/维护内存。
- IO 类:WAL 缓冲、checkpoint 节奏。
- 并发类:连接数、并行 worker。
原理流程
- 按瓶颈指标识别相关参数。
- 小步调整灰度复测。
- 记录基线与变更效果。
代码 / 命令
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
常见误区
- 误区:shared_buffers 越大越好。正解:需给 OS page cache 留内存,过大回收成本高。
- 误区:work_mem 是全局池。正解:按会话/节点分配,并发总量相乘估算。
追问
- 问:max_connections 为什么不宜大?答:每连接一进程,内存与上下文切换随连接数增长。
- 问:参数默认值记不准怎么办?答:标注待确认,以目标版本手册为准。
05.3.2.5-004 PostgreSQL 必须监控哪些指标?
频率 ★★★★☆ | 难度 进阶
一句话结论
监控:连接(活跃/空闲/长事务)、复制延迟、XID age 防回卷、膨胀(dead tuple/表大小/vacuum)、慢查询 TopN、锁等待、缓存命中、WAL/磁盘与归档失败;按基线设阈值告警并关联业务负载。
概念解释
- 可用性:连接、复制、回卷、锁。
- 性能:慢查询、缓存命中、IO。
- 容量:磁盘、WAL、膨胀趋势。
原理流程
- 采集活动会话、复制、统计与磁盘指标。
- 按基线阈值告警。
- 异常关联慢日志/锁视图排查。
代码 / 命令
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
常见误区
- 误区:只监控 CPU/内存。正解:回卷、膨胀、锁等待、复制延迟等专项指标才反映真实风险。
- 误区:指标绝对值直接告警。正解:需结合业务时段基线判断。
追问
- 问:为什么监控 age(datfrozenxid)?答:回卷临界会拒写甚至损坏,见 05.3.1.4。
- 问:复制延迟阈值怎么定?答:按 RPO 与业务读容忍度设定。
05.3.2.5-005 常见故障怎么处理?
频率 ★★★★☆ | 难度 高级
一句话结论
按现象分流:连接耗尽查会话与池化;膨胀查 dead tuple 与 vacuum;回卷查 age 紧急 freeze;复制延迟查备库负载/网络;锁等待查阻塞链;磁盘满查 WAL/归档;启动失败评估 PITR,pg_resetwal 仅最后手段。
概念解释
- 故障分类:连接、存储、回卷、复制、锁、磁盘、启动。
- 止损优先:先恢复服务,再根治根因。
- 现场保留:处理前抓会话/锁/日志现场。
原理流程
| 故障 | 排查 | 处理 |
|---|---|---|
| 连接耗尽 | pg_stat_activity | 杀空闲泄漏、上连接池 |
| 表膨胀 | n_dead_tup/pgstattuple | 调 vacuum、在线重建 |
| XID 将回卷 | age | 尽快 VACUUM freeze |
| 复制延迟 | pg_stat_replication | 查备库 IO/CPU/网络 |
| 锁等待 | pg_locks/阻塞链 | 终止阻塞源、统一顺序 |
| 磁盘满 | WAL/归档 | 修归档、清理扩容 |
代码 / 命令
SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
常见误区
- 误区:故障先重启实例。正解:先抓现场,重启丢失阻塞与会话信息。
- 误区:pg_resetwal 是常规修复。正解:可能丢 WAL 致不一致,仅无备份最后手段。
追问
- 问:归档失败什么后果?答:pg_wal 堆积撑爆磁盘,主库停止写入。
- 问:磁盘满先清什么?答:定位增长源(WAL/大表/归档),不手工删数据文件。
05.3.2.5-006 大版本升级有哪些方案?
频率 ★★★☆☆ | 难度 高级
一句话结论
pg_upgrade 本地二进制升级停机窗口短,需兼容检查并保留旧集群回滚;逻辑复制并行同步切流,处理 DDL/序列;pg_dump/restore 小库适用但停机长;云托管提供升级工具/蓝绿切换。
概念解释
- pg_upgrade:就地升级数据目录结构。
- 逻辑复制升级:新旧并行追平后切流。
- 逻辑导出:跨版本灵活但慢。
原理流程
- 备份并核对扩展/驱动兼容。
- 选升级方案做 check 检查。
- 切流后校验数据与性能,保留回滚窗口。
代码 / 命令
pg_upgrade --old-bindir ... --new-bindir ... --check
常见误区
- 误区:升级只换二进制。正解:需结构/统计/扩展兼容检查,升级后要 analyze 与复测计划。
- 误区:逻辑复制升级无手工项。正解:DDL、序列、扩展需手工核对。
追问
- 问:升级前做什么?答:备份、扩展兼容矩阵、check 预演、应用驱动兼容验证。
- 问:升级后统计要重做吗?答:建议 analyze 更新统计,避免计划偏差。
05.3.2.5-007 权限与安全实践?
频率 ★★★☆☆ | 难度 进阶
一句话结论
角色分离(超级/运维/应用读写/只读),最小授权避免 ALL;pg_hba 限制来源与认证方式,开启 SSL;审计扩展与慢日志留痕;多租户用 RLS 隔离;密钥不落代码库并定期轮换。
概念解释
- 最小权限:账号只授所需库表操作。
- 访问控制:来源 IP + 认证方式 + SSL。
- 审计隔离:操作留痕,行级策略隔离租户。
原理流程
- 按角色建账号与权限。
- 限制连接来源与加密传输。
- 定期审计授权与敏感访问。
代码 / 命令
CREATE ROLE app_readonly NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
常见误区
- 误区:应用账号用超级用户省事。正解:误操作/提权风险极大,应最小授权。
- 误区:RLS 无性能代价。正解:策略条件参与查询,需索引配合。
追问
- 问:默认权限怎么管理?答:ALTER DEFAULT PRIVILEGES 控制新建对象授权。
- 问:敏感字段怎么保护?答:加密/脱敏展示与访问审计,按合规要求。
05.3.2.5-008 故障切换流程与仲裁?
频率 ★★☆☆☆ | 难度 高级
一句话结论
切换流程:故障检测 → 仲裁确认主库不可用 → 选数据最新合格备库提升 → 更新集群与路由 → 旧主隔离 fencing;仲裁多数派防脑裂,切换前确认追平程度,明确 RPO 预期。
概念解释
- 故障检测:心跳与健康检查。
- 仲裁选主:共识存储多数派投票。
- fencing:旧主拒绝写入,防双主。
原理流程
- 检测主库失联进入仲裁。
- 合格备库提升为主。
- 路由切换,旧主隔离待回收。
代码 / 命令
切换:检测 → 仲裁 → 提升备库 → 路由更新 → 旧主隔离 → 校验数据
常见误区
- 误区:切换自动零丢失。正解:异步复制未同步事务可能丢失,需按 RPO 评估。
- 误区:旧主恢复后自动回切。正解:需先确认状态与拓扑,避免脑裂。
追问
- 问:怎么选提升哪个备库?答:选回放位点最新、健康合格的备库。
- 问:切换时连接怎么处理?答:代理/VIP 切换,应用重连新主。
05.3.2.5-009 连接池模式 session/transaction/statement?
频率 ★★★☆☆ | 难度 进阶
一句话结论
session 绑定会话复用差、兼容最好;transaction 事务结束归还,多数应用推荐;statement 每条语句归还、限制多。模式选择影响会话状态保持能力与池化效率,需结合应用事务边界。
概念解释
- session:连接随会话占用。
- transaction:事务边界归还。
- statement:语句边界归还。
原理流程
- 按应用会话状态需求选模式。
- 后端连接按模式分配归还。
- 归还重置会话状态。
代码 / 命令
pool_mode = transaction
常见误区
- 误区:statement 模式池化效果最好无坑。正解:事务内多语句状态无法保持,限制多。
- 误区:session 模式也能高并发复用。正解:连接长期占用,池化收益低。
追问
- 问:哪些功能受 transaction 模式影响?答:临时表、SET、游标、预编译、会话级 advisory lock。
- 问:池大小怎么定?答:按 CPU 核数与压测并发,见 05.3.1.1-006。
05.3.2.5-010 慢查询日志与会话监控?
频率 ★★★☆☆ | 难度 进阶
一句话结论
慢日志按阈值记录超耗时语句,pg_stat_statements 聚合语句模板 TopN;pg_stat_activity 看当前运行语句、长事务与状态;两者结合定位历史慢 SQL 与当前阻塞会话。
概念解释
- 慢日志:完成后记录超阈值语句。
- 语句统计:模板级调用次数与总/均耗时。
- 活动会话:当前正在执行/等待会话。
原理流程
- 配置慢日志阈值与锁等待记录。
- 统计视图按总耗时排序取 Top。
- 活动会话查长运行与阻塞。
代码 / 命令
log_min_duration_statement = 1000
SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
常见误区
- 误区:慢日志能看到运行中的长语句。正解:完成后才记录,运行中查活动会话。
- 误区:只看平均耗时。正解:总耗时与调用次数结合,高频低均耗时语句也需关注。
追问
- 问:pg_stat_statements 开销?答:轻微采集开销,生产常开。
- 问:锁等待日志怎么开?答:相关日志参数记录锁等待,具体名以手册为准。
05.3.2.5-011 容量规划与膨胀趋势?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
按数据增长趋势估算表/索引/TOAST/WAL/备份空间,结合并发连接与 IO 吞吐预留余量;监控大表膨胀与死元组趋势,定期复盘水位与扩容节点,避免临满应急。
概念解释
- 空间增长:堆、索引、TOAST、WAL、备份。
- 负载增长:连接、QPS、IO 吞吐。
- 余量预留:水位预警与提前扩容。
原理流程
- 采集历史增长与负载曲线。
- 预测增长并预留空间/性能余量。
- 周期复盘大表、热点与复制延迟。
代码 / 命令
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;
常见误区
- 误区:容量只算表数据。正解:索引、TOAST、WAL、碎片与备份都需估算。
- 误区:到水位再扩容。正解:预留提前量,避免磁盘满应急。
追问
- 问:大表增长怎么预警?答:监控表/索引体积增速与 dead tuple,纳入容量周报。
- 问:写入增长快先做什么?答:先优化索引/批量与 vacuum,再评估分表/分区。
05.3.2.5-012 在线扩展与大表变更?
频率 ★★☆☆☆ | 难度 高级
一句话结论
大表加索引/改结构评估在线方式,避免长 ACCESS EXCLUSIVE 锁阻塞业务;并发建索引工具减少锁表窗口,变更安排低峰并监控 MDL/锁等待;变更前备份、评估空间与回滚方案。
概念解释
- 在线建索引:并发构建减少阻塞写。
- DDL 锁:结构变更需排他表锁。
- 回滚预案:变更失败可回退。
原理流程
- 评估变更类型、表大小与锁需求。
- 选在线工具/并发方式低峰执行。
- 监控锁等待与复制延迟,失败回滚。
代码 / 命令
CREATE INDEX CONCURRENTLY idx_xxx ON orders (col);
常见误区
- 误区:在线 DDL 全程不锁表。正解:仍有短暂排他锁阶段,长事务会卡住切换。
- 误区:建索引不占额外空间。正解:需一份索引空间余量,构建期间写需同步维护。
追问
- 问:CREATE INDEX CONCURRENTLY 失败怎么办?答:可能留无效索引,需清理后重建。
- 问:大表 ALTER 注意什么?答:锁级别与重写代价,按版本支持的在线能力评估。
05.3.2.5-013 备份演练与恢复验证?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
备份必须定期实际恢复演练:在独立环境恢复基础备份 + WAL 到目标点,校验数据完整性与可用性;记录恢复耗时是否满足 RTO,发现备份损坏/归档断档及时修复。
概念解释
- 恢复演练:非纸面备份,实际拉起验证。
- RTO 验证:恢复耗时满足业务目标。
- 完整性校验:数据与约束核对。
原理流程
- 周期取备份与归档到独立环境。
- 执行 PITR 恢复到目标点。
- 校验数据并记录恢复时长。
代码 / 命令
演练:取备份 → 配 restore_command → 恢复目标点 → 校验 → 记录 RTO
常见误区
- 误区:备份任务成功即可信。正解:未演练的备份可能损坏或缺归档。
- 误区:演练影响生产。正解:在独立恢复环境进行,不触碰生产数据。
追问
- 问:演练频率怎么定?答:按数据重要性与变更节奏周期安排。
- 问:恢复后要核对什么?答:行数、关键表、序列值与约束完整性。
05.3.2.5-014 审计日志与合规?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
合规场景开启审计扩展记录关键操作(登录、权限变更、DDL、敏感访问),日志集中留存;结合慢日志与权限审计,按合规要求保留周期;敏感数据访问脱敏与追溯。
概念解释
- 审计扩展:记录语句/对象/角色访问。
- 集中留存:审计日志独立存储防篡改。
- 敏感追溯:谁在何时访问何数据。
原理流程
- 按合规范围配置审计规则。
- 审计日志集中采集留存。
- 定期复核异常访问与权限变更。
代码 / 命令
-- 审计扩展名称与配置以目标版本生态为准(待确认)
常见误区
- 误区:业务日志能替代数据库审计。正解:直接库访问与权限变更需数据库侧审计留痕。
- 误区:全量审计无成本。正解:审计采集增加开销与存储,按范围配置。
追问
- 问:RLS 与审计配合?答:策略隔离租户,审计记录跨租户访问尝试。
- 问:审计日志保留多久?答:按合规要求与存储容量设定。
05.3.2.5-015 多租户与资源隔离?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
多租户可按 schema/库分离或共享表 + RLS 隔离;资源隔离靠连接池分组、资源队列/限额与慢查询控制;大租户负载需避免抢占共享缓存与 IO,按隔离强度选方案。
概念解释
- 隔离层级:独立库/schema vs 共享行级策略。
- 资源分组:连接池与限额按租户划分。
- 负载防抢占:限制单租户并发与大查询。
原理流程
- 按隔离要求选租户模型。
- RLS/连接池隔离数据与资源。
- 监控大租户负载影响全实例。
代码 / 命令
CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::bigint);
常见误区
- 误区:共享表 + RLS 无性能代价。正解:策略条件参与查询,需索引配合。
- 误区:多租户共享实例天然资源隔离。正解:大租户查询会抢占共享资源,需限额。
追问
- 问:强隔离选哪种?答:独立库/实例隔离强度高,成本也高。
- 问:怎么防单租户慢查询拖垮全实例?答:语句超时、连接池分组与资源限额。
05.3.2.5-016 与 MySQL 运维体系对照?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
两边运维目标相通:连接池、复制延迟、备份恢复、监控指标、参数调优与故障排查;差异在进程模型(PG 多进程更依赖池化)、清理机制(vacuum vs purge)、日志(WAL vs redo/binlog)与高可用组件生态。
概念解释
- 相通项:监控闭环、备份演练、慢查询治理。
- 差异项:进程开销、回收机制、日志结构。
- 组件生态:各自高可用与备份工具链。
原理流程
- 按各自架构理解指标含义。
- 运维流程跨库复用排查思路。
- 参数与工具不混用。
代码 / 命令
对照:PgBouncer ↔ MySQL 连接池;autovacuum ↔ purge;Patroni ↔ MGR/代理切换
常见误区
- 误区:两边监控指标一一同名同义。正解:机制不同,指标口径需分别核对。
- 误区:MySQL 备份方案直接套 PG。正解:物理备份/WAL 归档与逻辑导出路径不同。
追问
- 问:MySQL 复制与高可用见哪?答:05.2.2.5。
- 问:哪边连接池更刚需?答:PG 多进程模型连接开销大,池化更关键。
05.3.2.5-017 云托管 PG 特性差异?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
云托管 PG 通常托管备份、高可用切换、监控与扩展安装,但部分底层参数、文件访问与自定义组件受限;选型时核对可调整参数版本、升级策略、备份恢复 SLA 与监控接入(待确认,按厂商文档)。
概念解释
- 托管能力:自动备份、故障切换、监控告警。
- 受限项:内核参数、主机文件、部分扩展与组件。
- 版本策略:托管升级节奏与兼容窗口。
原理流程
- 对比自建与托管的可控项与 SLA。
- 核对参数/扩展/备份能力满足需求。
- 接入托管监控与告警体系。
代码 / 命令
选型核对:参数开放度 / 扩展支持 / 备份恢复 SLA / 升级策略 / 监控接入
常见误区
- 误区:托管 PG 与自建行为完全一致。正解:部分底层操作不可见,参数开放度受限。
- 误区:托管自动备份无需演练。正解:仍需验证恢复流程与 RTO。
追问
- 问:托管支持自定义扩展吗?答:按厂商白名单确认,不支持项需替换方案。
- 问:切换托管注意什么?答:连接地址、参数基线、复制与备份策略迁移核对。
05.3.2.5-018 运维检查清单?
频率 ★★★☆☆ | 难度 进阶
一句话结论
周期检查:连接与会话状态、复制延迟与槽、XID age、dead tuple/vacuum、慢查询 TopN、锁等待、缓存命中、WAL/磁盘与归档、备份可恢复性、权限与审计;异常按分类闭环处理并记录复盘。
概念解释
- 日检:连接、延迟、锁、磁盘、慢查询。
- 周期检:回卷年龄、膨胀趋势、备份演练、权限审计。
- 闭环:发现 → 定位 → 处理 → 复测记录。
原理流程
- 按清单采集各维度指标。
- 超阈值关联根因排查。
- 处理后复测并归档复盘。
代码 / 命令
清单:连接 / 复制 / 回卷 / 膨胀 / 慢查询 / 锁 / 缓存 / 磁盘归档 / 备份 / 权限审计
常见误区
- 误区:运维只看告警。正解:趋势性指标(膨胀、age、WAL 增长)需提前预判。
- 误区:故障处理不留记录。正解:复盘沉淀为检查项,避免重复踩坑。
追问
- 问:哪些指标必须告警?答:连接耗尽、复制延迟超阈、age 逼近阈值、磁盘紧张、锁等待堆积。
- 问:变更前检查什么?答:备份、长事务、空间余量与锁影响窗口。