【PostgreSQL从零到精通】第40篇：Standby数据库原理——理解PostgreSQL的高可用基础-平芜编程栈

上一篇【第39篇】SQL语句优化实战——让查询快10倍的技巧
下一篇【第41篇】实战搭建流复制主备环境——从零到高可用

高可用（High Availability）是生产环境数据库的核心需求。PostgreSQL 通过Standby 数据库（备库）实现高可用：主库挂了，备库可以接管。本文深入讲解 Standby 的原理，为后续实战搭建打基础。

一、为什么需要 Standby？

没有 Standby 的风险： ┌─────────────────────────────────────────────────────────────┐ │ 单点故障（SPOF） │ │ │ │ ┌───────────┐ │ │ │ Primary │ → 挂了！整个系统不可用 │ │ │ (主库) │ │ │ └───────────┘ │ │ │ │ 后果： │ │ ├─ 业务停摆（网站/APP 无法访问数据库） │ │ ├─ 数据丢失风险（如果没有 WAL 归档） │ │ └─ 恢复时间长（需要从备份恢复 + 重放 WAL） │ └─────────────────────────────────────────────────────────────┘ 有 Standby 的保护： ┌─────────────────────────────────────────────────────────────┐ │ ┌───────────┐ ┌───────────┐ │ │ │ Primary │ ───WAL──→ │ Standby │ │ │ │ (主库) │ │ (备库) │ │ │ └───────────┘ └───────────┘ │ │ ↑ │ │ │ └────────────────┘ │ │ 主库挂了，备库提升为主库（秒级切换） │ └─────────────────────────────────────────────────────────────┘

二、PITR——WAL 归档的基础

2.1 什么是 PITR？

PITR（Point-In-Time Recovery，时间点恢复）是 Standby 的技术基础：通过 WAL 归档，将数据库恢复到任意时间点。

PITR 原理： ┌─────────────────────────────────────────────────────────────┐ │ 基础备份（pg_basebackup） + WAL 归档 = PITR 能力 │ │ │ │ ┌──────────┐ WAL 归档 ┌──────────┐ │ │ │ 主库 │ ─────────────→ │ WAL 归档 │ │ │ │ data/ │ │ /wal_ │ │ │ └──────────┘ │ archive/ │ │ │ │ └──────────┘ │ │ │ 基础备份（pg_basebackup） │ │ ↓ │ │ ┌──────────┐ │ │ │ 备份 │ │ │ │ (全量) │ │ │ └──────────┘ │ │ │ │ 恢复时：基础备份 + WAL 归档 = 恢复到任意时间点 │ └─────────────────────────────────────────────────────────────┘

2.2 PITR 基本流程

# 1. 配置 WAL 归档# postgresql.confwal_level=replica archive_mode=on archive_command='cp %p /wal_archive/%f'# 2. 创建基础备份pg_basebackup-hprimary-D/backup/base-Xstream# 3. 模拟灾难：主库数据丢失# 4. 恢复：使用基础备份 + WAL 归档# recovery.signal 文件 + restore_commandrestore_command='cp /wal_archive/%f %p'recovery_target_time='2024-01-15 14:30:00'# 恢复到指定时间# 或 recovery_target_lsn = '0/1A000028' # 恢复到指定 LSN# 5. 启动备库，它从基础备份 + WAL 归档恢复到指定时间点

三、流复制原理

3.1 什么是流复制？

流复制（Streaming Replication）是 PostgreSQL 9.0 引入的功能，备库通过 TCP 连接实时从主库接收 WAL 数据。

流复制原理： ┌─────────────────────────────────────────────────────────────┐ │ 主库（Primary） 备库（Standby） │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ WAL 发送进程 │ ──WAL────→ │ WAL 接收进程 │ │ │ │ (walsender) │ 流复制 │ (walreceiver)│ │ │ └──────────────┘ └──────┬───────┘ │ │ │ │ │ ↓ │ │ ┌──────────────┐ │ │ │ 恢复进程 │ │ │ │ (startup) │ │ │ └──────────────┘ │ │ │ │ WAL 流复制：实时同步，延迟通常 < 1 秒 │ └─────────────────────────────────────────────────────────────┘

3.2 流复制 vs WAL 归档

流复制 vs WAL 归档： ┌──────────┬────────────────────┬────────────────────┐ │ 对比 │ 流复制 │ WAL 归档 │ ├──────────┼────────────────────┼────────────────────┤ │ 实时性 │ 实时（< 1秒） │ 延迟（WAL 文件写满）│ ├──────────┼────────────────────┼────────────────────┤ │ 网络要求 │ 需要稳定连接 │ 可以异步传输 │ ├──────────┼────────────────────┼────────────────────┤ │ 数据保证 │ 可能丢失最后几秒 │ 不丢失（归档成功） │ ├──────────┼────────────────────┼────────────────────┤ │ 备库可读性│ Hot Standby 可读 │ 归档恢复后可读 │ └──────────┴────────────────────┴────────────────────┘ 最佳实践： 同时使用流复制（实时高可用） + WAL 归档（时间点恢复）

四、Warm Standby vs Hot Standby

4.1 Warm Standby（9.0 之前）

Warm Standby： ┌─────────────────────────────────────────────────────────────┐ │ 备库处于持续恢复状态 │ │ → 可以接收 WAL 并应用 │ │ → 但不接受连接（不能查询） │ │ │ │ 用途： │ │ └─ 灾难恢复（主库挂了，提升备库为主库） │ └─────────────────────────────────────────────────────────────┘

4.2 Hot Standby（9.0+，推荐）

Hot Standby： ┌─────────────────────────────────────────────────────────────┐ │ 备库处于持续恢复状态 │ │ → 可以接收 WAL 并应用 │ │ → 同时接受只读查询连接！ │ │ │ │ 用途： │ │ ├─ 读写分离（主库写，备库读） │ │ ├─ 报表查询（不影响主库性能） │ │ └─ 灾难恢复（主库挂了，提升备库为主库） │ └─────────────────────────────────────────────────────────────┘ 配置 Hot Standby： # postgresql.conf（在备库上） hot_standby = on # 允许在恢复期间连接并查询 hot_standby_feedback = on # 备库将查询状态反馈给主库，防止 VACUUM 冲突

五、同步复制 vs 异步复制

5.1 异步复制（默认）

异步复制（Asynchronous Replication）： ┌─────────────────────────────────────────────────────────────┐ │ 主库 备库 │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ COMMIT │ │ │ │ │ │ (返回成功) │ ──WAL发送─→ │ WAL 接收 │ │ │ └──────────────┘ └──────────────┘ │ │ ↑ │ │ └── 主库 COMMIT 后立即返回，不等待备库确认 │ │ │ │ 优点： │ │ └─ 主库性能影响小，延迟低 │ │ │ │ 缺点： │ │ └─ 主库崩溃时，最后几秒的事务可能丢失（备库未接收） │ └─────────────────────────────────────────────────────────────┘

5.2 同步复制（Synchronous Replication）

同步复制（Synchronous Replication）： ┌─────────────────────────────────────────────────────────────┐ │ 主库 备库 │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ COMMIT │ │ │ │ │ │ (等待确认) │ ──WAL发送─→ │ WAL 接收 │ │ │ └──────┬─────┘ │ (写入成功) │ │ │ │ └──────┬───────┘ │ │ │ │ │ │ │ ←──确认收到─────┘ │ │ ↓ │ │ COMMIT 返回成功 │ │ │ │ 优点： │ │ └─ 数据零丢失（备库确认收到后才算 COMMIT 成功） │ │ │ │ 缺点： │ │ ├─ 主库性能下降（等待备库确认） │ │ └─ 备库挂了，主库会 hang（可配置超时） │ └─────────────────────────────────────────────────────────────┘

5.3 配置同步复制

-- 在主库上配置（postgresql.conf）ALTERSYSTEMSETsynchronous_standby_names='standby1';-- standby1 是备库在 primary_conninfo 中指定的 application_name-- 同步级别控制ALTERSYSTEMSETsynchronous_commit=on;-- 等待本地和备库都确认-- 或：-- local：只等待本地 WAL 刷盘-- remote_write：等待备库接收到 WAL（但未刷盘）-- remote_flush：等待备库 WAL 刷盘-- remote_apply：等待备库应用完 WAL（最严格）SELECTpg_reload_conf();

六、流复制的搭建前提条件

6.1 主库配置

-- 1. 设置 WAL 级别（至少 replica）SHOWwal_level;-- 应该是 replica 或 logicalALTERSYSTEMSETwal_level=replica;-- 2. 允许足够的 WAL 发送进程SHOWmax_wal_senders;-- 默认 10，足够小型环境ALTERSYSTEMSETmax_wal_senders=10;-- 3. 启用 WAL 归档（可选，但强烈推荐）ALTERSYSTEMSETarchive_mode=on;ALTERSYSTEMSETarchive_command='cp %p /wal_archive/%f';-- 注意：同步复制 + 归档，提供最高级别的数据保护-- 4. 创建用于复制的专用用户CREATEROLE replREPLICATIONLOGIN PASSWORD'repl_password';-- 5. 配置 pg_hba.conf 允许备库连接-- 添加：-- host replication repl standby_ip/32 md5SELECTpg_reload_conf();

6.2 网络要求

流复制对网络的要求： ┌─────────────────────────────────────────────────────────────┐ │ 1. 主备之间需要稳定的 TCP 连接（通常 5432 端口） │ │ │ │ 2. 延迟建议 < 1ms（同一机房） │ │ 跨机房 < 10ms（同步复制对延迟很敏感） │ │ │ │ 3. 带宽建议： │ │ → WAL 生成速率 × 2（主库到备库 + 归档） │ │ → 例如：WAL 生成 50MB/s → 需要 100MB/s 带宽 │ │ │ │ 4. 防火墙：开放 5432 端口（PostgreSQL） │ └─────────────────────────────────────────────────────────────┘

七、复制延迟监控**

-- 在主库上查看流复制状态SELECTpid,usename,application_name,client_addr,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,pg_wal_lsn_diff(sent_lsn,replay_lsn)ASdelay_bytes,pg_wal_lsn_diff(sent_lsn,replay_lsn)/1024.0/1024.0ASdelay_mbFROMpg_stat_replication;-- 字段说明：-- sent_lsn：已发送到备库的 WAL 位置-- write_lsn：备库已写入磁盘的 WAL 位置-- flush_lsn：备库已刷盘的 WAL 位置-- replay_lsn：备库已应用的 WAL 位置（此位置之前的数据已可见）-- 查看复制延迟时间（秒）SELECTapplication_name,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)ASlag_bytes,EXTRACT(EPOCHFROMnow()-pg_last_xact_replay_time())ASlag_secondsFROMpg_stat_replication;