上一篇【第39篇】SQL语句优化实战——让查询快10倍的技巧下一篇【第41篇】实战搭建流复制主备环境——从零到高可用高可用High Availability是生产环境数据库的核心需求。PostgreSQL 通过Standby 数据库备库实现高可用主库挂了备库可以接管。本文深入讲解 Standby 的原理为后续实战搭建打基础。一、为什么需要 Standby没有 Standby 的风险 ┌─────────────────────────────────────────────────────────────┐ │ 单点故障SPOF │ │ │ │ ┌───────────┐ │ │ │ Primary │ → 挂了整个系统不可用 │ │ │ (主库) │ │ │ └───────────┘ │ │ │ │ 后果 │ │ ├─ 业务停摆网站/APP 无法访问数据库 │ │ ├─ 数据丢失风险如果没有 WAL 归档 │ │ └─ 恢复时间长需要从备份恢复 重放 WAL │ └─────────────────────────────────────────────────────────────┘ 有 Standby 的保护 ┌─────────────────────────────────────────────────────────────┐ │ ┌───────────┐ ┌───────────┐ │ │ │ Primary │ ───WAL──→ │ Standby │ │ │ │ (主库) │ │ (备库) │ │ │ └───────────┘ └───────────┘ │ │ ↑ │ │ │ └────────────────┘ │ │ 主库挂了备库提升为主库秒级切换 │ └─────────────────────────────────────────────────────────────┘二、PITR——WAL 归档的基础2.1 什么是 PITRPITRPoint-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_levelreplica archive_modeon archive_commandcp %p /wal_archive/%f# 2. 创建基础备份pg_basebackup-hprimary-D/backup/base-Xstream# 3. 模拟灾难主库数据丢失# 4. 恢复使用基础备份 WAL 归档# recovery.signal 文件 restore_commandrestore_commandcp /wal_archive/%f %precovery_target_time2024-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 Standby4.1 Warm Standby9.0 之前Warm Standby ┌─────────────────────────────────────────────────────────────┐ │ 备库处于持续恢复状态 │ │ → 可以接收 WAL 并应用 │ │ → 但不接受连接不能查询 │ │ │ │ 用途 │ │ └─ 灾难恢复主库挂了提升备库为主库 │ └─────────────────────────────────────────────────────────────┘4.2 Hot Standby9.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.confALTERSYSTEMSETsynchronous_standby_namesstandby1;-- standby1 是备库在 primary_conninfo 中指定的 application_name-- 同步级别控制ALTERSYSTEMSETsynchronous_commiton;-- 等待本地和备库都确认-- 或-- local只等待本地 WAL 刷盘-- remote_write等待备库接收到 WAL但未刷盘-- remote_flush等待备库 WAL 刷盘-- remote_apply等待备库应用完 WAL最严格SELECTpg_reload_conf();六、流复制的搭建前提条件6.1 主库配置-- 1. 设置 WAL 级别至少 replicaSHOWwal_level;-- 应该是 replica 或 logicalALTERSYSTEMSETwal_levelreplica;-- 2. 允许足够的 WAL 发送进程SHOWmax_wal_senders;-- 默认 10足够小型环境ALTERSYSTEMSETmax_wal_senders10;-- 3. 启用 WAL 归档可选但强烈推荐ALTERSYSTEMSETarchive_modeon;ALTERSYSTEMSETarchive_commandcp %p /wal_archive/%f;-- 注意同步复制 归档提供最高级别的数据保护-- 4. 创建用于复制的专用用户CREATEROLE replREPLICATIONLOGIN PASSWORDrepl_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;八、总结**Standby 数据库是 PostgreSQL 高可用的基石。核心原理PITR通过基础备份 WAL 归档实现时间点恢复流复制主库实时发送 WAL 到备库Hot Standby备库可读实现读写分离同步 vs 异步数据安全 vs 性能下一篇我们实战搭建流复制主备环境——从零开始完成一个生产级的高可用架构。标签PostgreSQL、Standby、流复制、PITR、Hot Standby、高可用上一篇【第39篇】SQL语句优化实战——让查询快10倍的技巧下一篇【第41篇】实战搭建流复制主备环境——从零到高可用