Ubuntu PostgreSQL数据同步方法

Ubuntu 上常用的 PostgreSQL 数据同步方案与选型

• 物理流复制（Streaming Replication）：基于 WAL 的物理复制，备库为近乎实时的数据副本，适合高可用与读写分离。版本建议一致，备库默认只读。
• 逻辑复制（Logical Replication）：基于表级变更（Publication/Subscription），可跨版本/跨集群，选择性复制，适合部分表同步与零停机升级。
• 备份恢复与工具同步：使用 pg_dump/pg_restore 或 pgAdmin 备份/恢复做一次性迁移或低频同步；也可用第三方工具实现更复杂的场景。
• 高可用与集群：基于 Patroni + etcd/Consul、pgpool-II、Citus 等构建自动故障切换与读写分离。

物理流复制 一主一从实操步骤 Ubuntu 20.04/22.04

• 环境约定：主库 192.168.1.10，从库 192.168.1.11；PostgreSQL 14/15；数据目录 /var/lib/postgresql/14/main（版本不同路径中的版本号随之变化）。
• 主库配置
  1. 编辑 /etc/postgresql/14/main/postgresql.conf：

  listen_addresses = '*'
  wal_level = replica
  max_wal_senders = 10
  wal_keep_size = 128MB
  hot_standby = on
  

  1. 编辑 /etc/postgresql/14/main/pg_hba.conf，允许从库复制连接：

  host    replication     replicator     192.168.1.11/32    md5
  

  1. 创建复制用户并重启：

  sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'YourStrongPwd';
      "
  sudo systemctl restart postgresql
  

• 从库初始化与启动
  1. 清空数据目录并拉取基础备份（会自动创建 standby.signal 并写入 primary_conninfo）：

  sudo systemctl stop postgresql
  sudo -u postgres rm -rf /var/lib/postgresql/14/main/*
  sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator -D /var/lib/postgresql/14/main -P -R --wal-method=stream
  sudo systemctl start postgresql
  

  1. 如需手动补充连接信息（当 -R 未写入或需调整时），在 postgresql.conf 加入：

  primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=YourStrongPwd'
  

• 验证

  # 主库
  sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;
      "
  
  # 从库
  sudo -u postgres psql -c "SELECT pg_is_in_recovery();
      "  -- 返回 t 表示处于备库恢复状态
  

  提示：PostgreSQL 12+ 使用 standby.signal 与 primary_conninfo；9.6/10/11 使用 recovery.conf 的 standby_mode = on 与 primary_conninfo。

逻辑复制 选择性表同步

• 适用：跨版本/跨集群、只同步部分表、DDL 不自动复制（需手工在两端执行）。
• 主库（发布端）

  -- 创建发布（示例：发布 public 模式下所有表）
  CREATE PUBLICATION pub_all FOR ALL TABLES;
      
  
  -- 或仅发布指定表
  CREATE PUBLICATION pub_sales FOR TABLE public.orders, public.customers;
      
  

• 从库（订阅端）

  -- 在目标库创建与源库一致的结构（表、索引、约束等）
  -- 创建订阅（示例）
  CREATE SUBSCRIPTION sub_from_prod
  CONNECTION 'host=192.168.1.10 port=5432 user=replicator password=YourStrongPwd dbname=yourdb'
  PUBLICATION pub_all;
      
  

• 常用检查

  -- 发布端
  \dRp
  
  -- 订阅端
  \dRs
  

  注意：逻辑复制以表为单位，DDL 需单独处理；冲突与回放需业务侧评估。

备份恢复与工具同步

• pg_dump/pg_restore（一次性迁移/低频同步）

  # 导出
  pg_dump -h 192.168.1.10 -U postgres -F c -b -v -f db.dump yourdb
  
  # 导入
  pg_restore -h 192.168.1.11 -U postgres -d yourdb -v db.dump
  

• pgAdmin 备份/恢复：在 pgAdmin 中执行数据库“备份”与“恢复”，适合图形化管理与小规模迁移。
• 第三方工具：如基于事件驱动的 multi-region-replicator（MRR） 等，用于跨地域实时同步与冲突协调（需按产品文档部署）。

高可用与故障切换方案

• Patroni + etcd/Consul：管理 PostgreSQL 实例生命周期、选主与健康检查，支持 pg_rewind、slots 等特性，便于自动化运维。
• pgpool-II：连接池与读写分离中间件，支持负载均衡、健康检查与故障转移策略。
• Citus：用于分布式横向扩展，适合大规模并行查询与分片场景。
• 典型组合：Patroni 负责 HA，pgpool-II 负责读写分离与连接管理。

常见问题与排查要点

• pg_stat_replication 为空：检查主库 pg_hba.conf 是否允许复制用户、网络与防火墙、复制用户权限、主库 wal_level/replicas 配置。
• 复制延迟或中断：关注 WAL 生成与传输速度、磁盘 I/O、网络抖动；必要时调整 max_wal_senders、WAL 保留策略（如 wal_keep_size 或 WAL 归档）。
• 备库写入报错：备库处于恢复模式默认只读，属正常行为；如需写入请改为级联复制或调整拓扑。
• 版本差异：PG 12+ 使用 standby.signal；PG 9.6–11 使用 recovery.conf；参数名与默认值也可能不同，建议按官方文档核对。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！