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（版本不同路径中的版本号随之变化）。

主库配置

编辑 /etc/postgresql/14/main/postgresql.conf：

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

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

host    replication     replicator     192.168.1.11/32    md5

创建复制用户并重启：

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

从库初始化与启动

清空数据目录并拉取基础备份（会自动创建 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

如需手动补充连接信息（当 -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；参数名与默认值也可能不同，建议按官方文档核对。

最新问答

相关标签