PostgreSQL在Ubuntu上的复制与同步策略

PostgreSQL 在 Ubuntu 的复制与同步策略

一、策略总览与选型

• 物理流复制（Streaming Replication）：基于 WAL 的物理复制，备库是主库的近乎一致副本，支持读扩展与高可用。可配置为异步或同步，同步可确保提交事务在备库落盘后才返回，零数据丢失但会增加提交时延。适合“主库写、备库读”和容灾场景。
• 逻辑复制（Logical Replication）：基于表级变更的逻辑解码与回放，发布端/订阅端可跨版本、跨集群，仅复制指定表或子集，默认不复制 DDL。适合部分表同步、跨版本升级、零停机迁移、数据汇聚等场景。

二、物理流复制部署要点（Ubuntu 14/16/18/20/22/24 通用）

• 主库配置
  • 创建复制用户并授权：
    • CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD ‘your_password’;
  • 配置 pg_hba.conf（示例允许备库网段）：
    • host replication replicator 192.168.1.0/24 md5
  • 配置 postgresql.conf（关键项）：
    • wal_level = replica
    • max_wal_senders = 3（至少等于备库数量）
    • max_replication_slots = 5（建议≥备库数）
    • wal_keep_size = 1GB（或保留旧参数 wal_keep_segments）
    • archive_mode = on
    • archive_command = ‘test ! -f /var/lib/postgresql/14/archive/%f || cp %p /var/lib/postgresql/14/archive/%f’
    • listen_addresses = ‘*’
  • 重启主库：sudo systemctl restart postgresql
• 备库初始化与启动
  • 清空数据目录并拉取基础备份（示例）：
    • sudo -u postgres pg_basebackup -h 192.168.1.100 -U replicator -D /var/lib/postgresql/14/main -v -P --wal-method=stream
  • 配置备库
    • PostgreSQL 12+ 使用 standby.signal 机制：
      • sudo -u postgres touch /var/lib/postgresql/14/main/standby.signal
    • postgresql.conf（示例）：
      • hot_standby = on
      • primary_conninfo = ‘host=192.168.1.100 port=5432 user=replicator password=your_password’
      • max_standby_streaming_delay = 30s（可按需调整）
      • hot_standby_feedback = on（减少备库长查询与主库清理冲突）
  • 启动备库：sudo systemctl start postgresql
• 版本差异提示
  • PostgreSQL 12 起，recovery.conf 被合并到 postgresql.conf 与 standby.signal；12 之前需创建 recovery.conf 并设置 standby_mode、primary_conninfo 等。

三、同步级别与零数据丢失配置

• 异步复制（默认）：主库提交不等待备库，性能更好，极端故障可能丢失少量已提交事务。
• 同步复制（零数据丢失）：
  • 主库 postgresql.conf：
    • synchronous_commit = on（或 remote_apply 追求更强一致性）
    • synchronous_standby_names = ‘standby1’（单同步备库）
    • 多备库可设为：synchronous_standby_names = ‘FIRST 1 (standby1,standby2)’（前 1 台为同步，其余异步）
  • 验证同步状态：
    • 在主库执行：
      • SELECT client_addr, state, sync_state FROM pg_stat_replication;
      • 正常时 sync_state 为 sync/quorum；若为 async 表示仍为异步。
• 影响与取舍
  • 同步会提高提交时延并降低吞吐；建议结合业务 RPO/RTO、网络时延与备库数量选择策略，必要时采用“多数派 quorum”或多级同步。

四、逻辑复制部署要点与限制

• 发布端（主库）配置
  • postgresql.conf：
    • wal_level = logical
    • max_replication_slots ≥ 1（建议预留余量）
  • 创建发布（示例）：
    • CREATE PUBLICATION pub_all FOR ALL TABLES;（或指定表）
  • 复制标识（REPLICA IDENTITY）
    • 表需有主键或唯一索引；如无，需设置：ALTER TABLE t REPLICA IDENTITY FULL; 否则 UPDATE/DELETE 会报错。
• 订阅端（目标库）配置
  • 创建订阅（示例）：
    • CREATE SUBSCRIPTION sub_all CONNECTION ‘host=192.168.1.100 port=5432 user=replicator password=your_password dbname=postgres’ PUBLICATION pub_all;
• 重要限制
  • 仅复制表级变更，默认不复制 DDL；序列、索引重建等需手工在订阅端执行。
  • 发布端与订阅端版本需兼容（通常同大版本或相邻版本更稳妥）。
  • 建议为订阅端表建立与发布端一致的约束与索引，避免回放冲突。

五、监控、运维与故障切换

• 复制监控
  • 主库查看流复制状态：
    • SELECT client_addr, application_name, state, sent_location, flush_location, replay_location, sync_state FROM pg_stat_replication;
    • 如需字节级延迟：SELECT pg_xlog_location_diff(sent_location, replay_location) FROM pg_stat_replication;（PG 10 及更早使用 pg_xlog_location_diff；PG 12+ 使用 pg_wal_lsn_diff）
• 时间线与回放追赶
  • 备库长时间宕机后可能落后于主库时间线（timeline）。优先尝试使用 pg_rewind 对齐时间线后重启；若失败或数据差异过大，使用 pg_basebackup 重建备库。
• 高可用与自动故障转移
  • 结合分布式一致性组件与编排实现自动切换与弹性：如 Patroni + etcd/Consul（管理实例生命周期、选主、自动 failover）、Pgpool-II（连接池、读写分离、健康检查）。Patroni 配置中可开启 use_pg_rewind、use_slots、synchronous 相关参数以配合流复制与一致性策略。