罗罗编程学习记录
本页内容 展开查看

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/代理指向新主。

原理流程

  1. 各节点 agent 上报健康状态。
  2. 持 leader key 节点为主库。
  3. 主故障后合格备库经共识提升,路由切换。

代码 / 命令

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 清理状态。
  • 池大小:后端连接数上限,非越大越好。

原理流程

  1. 应用连 PgBouncer,不直连后端。
  2. 按模式分配/归还后端连接。
  3. 归还时重置会话状态。

代码 / 命令

[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。

原理流程

  1. 按瓶颈指标识别相关参数。
  2. 小步调整灰度复测。
  3. 记录基线与变更效果。

代码 / 命令

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、膨胀趋势。

原理流程

  1. 采集活动会话、复制、统计与磁盘指标。
  2. 按基线阈值告警。
  3. 异常关联慢日志/锁视图排查。

代码 / 命令

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:就地升级数据目录结构。
  • 逻辑复制升级:新旧并行追平后切流。
  • 逻辑导出:跨版本灵活但慢。

原理流程

  1. 备份并核对扩展/驱动兼容。
  2. 选升级方案做 check 检查。
  3. 切流后校验数据与性能,保留回滚窗口。

代码 / 命令

pg_upgrade --old-bindir ... --new-bindir ... --check

常见误区

  • 误区:升级只换二进制。正解:需结构/统计/扩展兼容检查,升级后要 analyze 与复测计划。
  • 误区:逻辑复制升级无手工项。正解:DDL、序列、扩展需手工核对。

追问

  • 问:升级前做什么?答:备份、扩展兼容矩阵、check 预演、应用驱动兼容验证。
  • 问:升级后统计要重做吗?答:建议 analyze 更新统计,避免计划偏差。

05.3.2.5-007 权限与安全实践?

频率 ★★★☆☆ | 难度 进阶

一句话结论

角色分离(超级/运维/应用读写/只读),最小授权避免 ALL;pg_hba 限制来源与认证方式,开启 SSL;审计扩展与慢日志留痕;多租户用 RLS 隔离;密钥不落代码库并定期轮换。

概念解释

  • 最小权限:账号只授所需库表操作。
  • 访问控制:来源 IP + 认证方式 + SSL。
  • 审计隔离:操作留痕,行级策略隔离租户。

原理流程

  1. 按角色建账号与权限。
  2. 限制连接来源与加密传输。
  3. 定期审计授权与敏感访问。

代码 / 命令

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:旧主拒绝写入,防双主。

原理流程

  1. 检测主库失联进入仲裁。
  2. 合格备库提升为主。
  3. 路由切换,旧主隔离待回收。

代码 / 命令

切换:检测 → 仲裁 → 提升备库 → 路由更新 → 旧主隔离 → 校验数据

常见误区

  • 误区:切换自动零丢失。正解:异步复制未同步事务可能丢失,需按 RPO 评估。
  • 误区:旧主恢复后自动回切。正解:需先确认状态与拓扑,避免脑裂。

追问

  • 问:怎么选提升哪个备库?答:选回放位点最新、健康合格的备库。
  • 问:切换时连接怎么处理?答:代理/VIP 切换,应用重连新主。

05.3.2.5-009 连接池模式 session/transaction/statement?

频率 ★★★☆☆ | 难度 进阶

一句话结论

session 绑定会话复用差、兼容最好;transaction 事务结束归还,多数应用推荐;statement 每条语句归还、限制多。模式选择影响会话状态保持能力与池化效率,需结合应用事务边界。

概念解释

  • session:连接随会话占用。
  • transaction:事务边界归还。
  • statement:语句边界归还。

原理流程

  1. 按应用会话状态需求选模式。
  2. 后端连接按模式分配归还。
  3. 归还重置会话状态。

代码 / 命令

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 与当前阻塞会话。

概念解释

  • 慢日志:完成后记录超阈值语句。
  • 语句统计:模板级调用次数与总/均耗时。
  • 活动会话:当前正在执行/等待会话。

原理流程

  1. 配置慢日志阈值与锁等待记录。
  2. 统计视图按总耗时排序取 Top。
  3. 活动会话查长运行与阻塞。

代码 / 命令

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 吞吐。
  • 余量预留:水位预警与提前扩容。

原理流程

  1. 采集历史增长与负载曲线。
  2. 预测增长并预留空间/性能余量。
  3. 周期复盘大表、热点与复制延迟。

代码 / 命令

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 锁:结构变更需排他表锁。
  • 回滚预案:变更失败可回退。

原理流程

  1. 评估变更类型、表大小与锁需求。
  2. 选在线工具/并发方式低峰执行。
  3. 监控锁等待与复制延迟,失败回滚。

代码 / 命令

CREATE INDEX CONCURRENTLY idx_xxx ON orders (col);

常见误区

  • 误区:在线 DDL 全程不锁表。正解:仍有短暂排他锁阶段,长事务会卡住切换。
  • 误区:建索引不占额外空间。正解:需一份索引空间余量,构建期间写需同步维护。

追问

  • 问:CREATE INDEX CONCURRENTLY 失败怎么办?答:可能留无效索引,需清理后重建。
  • 问:大表 ALTER 注意什么?答:锁级别与重写代价,按版本支持的在线能力评估。

05.3.2.5-013 备份演练与恢复验证?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

备份必须定期实际恢复演练:在独立环境恢复基础备份 + WAL 到目标点,校验数据完整性与可用性;记录恢复耗时是否满足 RTO,发现备份损坏/归档断档及时修复。

概念解释

  • 恢复演练:非纸面备份,实际拉起验证。
  • RTO 验证:恢复耗时满足业务目标。
  • 完整性校验:数据与约束核对。

原理流程

  1. 周期取备份与归档到独立环境。
  2. 执行 PITR 恢复到目标点。
  3. 校验数据并记录恢复时长。

代码 / 命令

演练:取备份 → 配 restore_command → 恢复目标点 → 校验 → 记录 RTO

常见误区

  • 误区:备份任务成功即可信。正解:未演练的备份可能损坏或缺归档。
  • 误区:演练影响生产。正解:在独立恢复环境进行,不触碰生产数据。

追问

  • 问:演练频率怎么定?答:按数据重要性与变更节奏周期安排。
  • 问:恢复后要核对什么?答:行数、关键表、序列值与约束完整性。

05.3.2.5-014 审计日志与合规?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

合规场景开启审计扩展记录关键操作(登录、权限变更、DDL、敏感访问),日志集中留存;结合慢日志与权限审计,按合规要求保留周期;敏感数据访问脱敏与追溯。

概念解释

  • 审计扩展:记录语句/对象/角色访问。
  • 集中留存:审计日志独立存储防篡改。
  • 敏感追溯:谁在何时访问何数据。

原理流程

  1. 按合规范围配置审计规则。
  2. 审计日志集中采集留存。
  3. 定期复核异常访问与权限变更。

代码 / 命令

-- 审计扩展名称与配置以目标版本生态为准(待确认)

常见误区

  • 误区:业务日志能替代数据库审计。正解:直接库访问与权限变更需数据库侧审计留痕。
  • 误区:全量审计无成本。正解:审计采集增加开销与存储,按范围配置。

追问

  • 问:RLS 与审计配合?答:策略隔离租户,审计记录跨租户访问尝试。
  • 问:审计日志保留多久?答:按合规要求与存储容量设定。

05.3.2.5-015 多租户与资源隔离?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

多租户可按 schema/库分离或共享表 + RLS 隔离;资源隔离靠连接池分组、资源队列/限额与慢查询控制;大租户负载需避免抢占共享缓存与 IO,按隔离强度选方案。

概念解释

  • 隔离层级:独立库/schema vs 共享行级策略。
  • 资源分组:连接池与限额按租户划分。
  • 负载防抢占:限制单租户并发与大查询。

原理流程

  1. 按隔离要求选租户模型。
  2. RLS/连接池隔离数据与资源。
  3. 监控大租户负载影响全实例。

代码 / 命令

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)与高可用组件生态。

概念解释

  • 相通项:监控闭环、备份演练、慢查询治理。
  • 差异项:进程开销、回收机制、日志结构。
  • 组件生态:各自高可用与备份工具链。

原理流程

  1. 按各自架构理解指标含义。
  2. 运维流程跨库复用排查思路。
  3. 参数与工具不混用。

代码 / 命令

对照:PgBouncer ↔ MySQL 连接池;autovacuum ↔ purge;Patroni ↔ MGR/代理切换

常见误区

  • 误区:两边监控指标一一同名同义。正解:机制不同,指标口径需分别核对。
  • 误区:MySQL 备份方案直接套 PG。正解:物理备份/WAL 归档与逻辑导出路径不同。

追问

  • 问:MySQL 复制与高可用见哪?答:05.2.2.5。
  • 问:哪边连接池更刚需?答:PG 多进程模型连接开销大,池化更关键。

05.3.2.5-017 云托管 PG 特性差异?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

云托管 PG 通常托管备份、高可用切换、监控与扩展安装,但部分底层参数、文件访问与自定义组件受限;选型时核对可调整参数版本、升级策略、备份恢复 SLA 与监控接入(待确认,按厂商文档)。

概念解释

  • 托管能力:自动备份、故障切换、监控告警。
  • 受限项:内核参数、主机文件、部分扩展与组件。
  • 版本策略:托管升级节奏与兼容窗口。

原理流程

  1. 对比自建与托管的可控项与 SLA。
  2. 核对参数/扩展/备份能力满足需求。
  3. 接入托管监控与告警体系。

代码 / 命令

选型核对:参数开放度 / 扩展支持 / 备份恢复 SLA / 升级策略 / 监控接入

常见误区

  • 误区:托管 PG 与自建行为完全一致。正解:部分底层操作不可见,参数开放度受限。
  • 误区:托管自动备份无需演练。正解:仍需验证恢复流程与 RTO。

追问

  • 问:托管支持自定义扩展吗?答:按厂商白名单确认,不支持项需替换方案。
  • 问:切换托管注意什么?答:连接地址、参数基线、复制与备份策略迁移核对。

05.3.2.5-018 运维检查清单?

频率 ★★★☆☆ | 难度 进阶

一句话结论

周期检查:连接与会话状态、复制延迟与槽、XID age、dead tuple/vacuum、慢查询 TopN、锁等待、缓存命中、WAL/磁盘与归档、备份可恢复性、权限与审计;异常按分类闭环处理并记录复盘。

概念解释

  • 日检:连接、延迟、锁、磁盘、慢查询。
  • 周期检:回卷年龄、膨胀趋势、备份演练、权限审计。
  • 闭环:发现 → 定位 → 处理 → 复测记录。

原理流程

  1. 按清单采集各维度指标。
  2. 超阈值关联根因排查。
  3. 处理后复测并归档复盘。

代码 / 命令

清单:连接 / 复制 / 回卷 / 膨胀 / 慢查询 / 锁 / 缓存 / 磁盘归档 / 备份 / 权限审计

常见误区

  • 误区:运维只看告警。正解:趋势性指标(膨胀、age、WAL 增长)需提前预判。
  • 误区:故障处理不留记录。正解:复盘沉淀为检查项,避免重复踩坑。

追问

  • 问:哪些指标必须告警?答:连接耗尽、复制延迟超阈、age 逼近阈值、磁盘紧张、锁等待堆积。
  • 问:变更前检查什么?答:备份、长事务、空间余量与锁影响窗口。