Debian 上可用的 PostgreSQL 数据同步方案与选型
| 方案 |
同步方式 |
典型用途 |
优点 |
局限 |
| 流复制（异步/同步，物理复制） |
基于 WAL 的连续流式传输 |
主从/主备、读写分离、近实时同步 |
延迟低、对应用透明、成熟稳定 |
备库只读；版本/平台需兼容；需监控复制延迟 |
| 逻辑复制 |
基于表级/库级变更复制 |
部分表跨版本/跨集群迁移、微服务解耦 |
灵活选择对象、跨版本/跨平台 |
不复制 DDL（需额外处理）、约束/触发器需评估 |
| pg_dump / pg_restore |
逻辑导出/导入 |
一次性迁移、小中型库、跨平台 |
简单易用、可选择性对象 |
停机时间长、不适合持续同步 |
| pg_dumpall + psql |
全库逻辑导出/导入 |
整实例迁移、初始化从库 |
一致性好、覆盖全局对象 |
体积大、导入慢、不适合持续同步 |
| 第三方工具（pgloader、Barman 等） |
批量导入/备份恢复 |
异构迁移、备份与时间点恢复 |
自动化程度高、功能丰富 |
需学习成本、依赖外部工具链 |
以上方案在 Debian 上均可落地，选择取决于你的一致性要求、停机容忍度、版本差异与运维能力。
流复制一主一从快速落地步骤
-
环境约定
- 主库：192.168.1.112，从库：192.168.1.113，版本：PostgreSQL 15（Debian 12）
- 数据目录：/var/lib/postgresql/15/main
- 复制用户：replicator，认证方式：scram-sha-256
-
主库配置
- 编辑配置文件（路径随版本可能为 /etc/postgresql/15/main/）
- postgresql.conf
- listen_addresses = ‘*’
- wal_level = replica
- max_wal_senders = 10
- archive_mode = on
- archive_command = ‘cp %p /var/lib/postgresql/15/main/archive/%f’
- pg_hba.conf（追加）
- host replication replicator 192.168.1.113/24 scram-sha-256
- 创建复制用户
- createuser --replication -P replicator
- 创建 WAL 归档目录并授权
- mkdir -p /var/lib/postgresql/15/main/archive
- chown postgres:postgres /var/lib/postgresql/15/main/archive
- 使配置生效
- systemctl reload postgresql 或 pg_ctl reload -D /var/lib/postgresql/15/main
-
从库初始化与启动
- 清空数据目录并拉取基础备份
- sudo systemctl stop postgresql
- sudo -u postgres rm -rf /var/lib/postgresql/15/main/*
- sudo -u postgres pg_basebackup -h 192.168.1.112 -U replicator -D /var/lib/postgresql/15/main -P -v -R -X stream -C -S slot1
- 说明：-R 会自动写入 primary_conninfo；-X stream 拉取 WAL；-C -S 创建复制槽（slot1）
- 启动从库
- sudo systemctl start postgresql
- 验证
- 主库：select * from pg_stat_replication;（应见 state=streaming、sync_state=async）
- 从库：select * from pg_stat_wal_receiver;（应见接收/回放进度）
-
可选增强
- 使用复制槽避免 WAL 被过早回收：在主库创建 slot（如上 -S slot1），并在从库 recovery 配置或 primary_conninfo 中对应设置。
- 需要同步提交时，可调整 synchronous_standby_names 与 synchronous_commit，实现同步/半同步提交（以性能换一致性）。
逻辑复制与迁移工具
-
逻辑复制要点
- 适合跨版本/跨集群、按表/库复制；不复制 DDL，需自建 DDL 变更流程。
- 发布端：CREATE PUBLICATION pub_name FOR ALL TABLES;（或指定表）
- 订阅端：CREATE SUBSCRIPTION sub_name CONNECTION ‘host=主库 port=5432 user=replicator password=xxx dbname=目标库’ PUBLICATION pub_name;
- 注意：主键/唯一约束、触发器、默认值、序列等需在主从一致或额外处理。
-
迁移与批量导入
- 整库/全实例迁移：pg_dumpall -U postgres -f backup.sql；目标库 psql -f backup.sql
- 单库/自定义格式：pg_dump -U user -d db -F c -b -v -f backup.dump；目标库 pg_restore -U user -d db -v backup.dump
- 异构/大数据量导入：pgloader 可将多源（CSV/MySQL/SQLite 等）快速导入 PostgreSQL，适合一次性迁移或 CDC 前置落地。
备份恢复与高可用扩展
-
备份与时间点恢复（PITR）
- 基础备份：pg_basebackup
- WAL 归档：archive_command 指向归档目录（如上 cp %p /var/lib/postgresql/15/main/archive/%f）
- 恢复：将基础备份恢复到目标目录，编辑 recovery.conf（或 standby.signal + postgresql.auto.conf）设置 restore_command 与（可选）recovery_target_time，启动实例自动回放至目标时间点。
-
高可用与读写分离
- 读写分离/连接池：部署 Pgpool-II，配置后端节点权重，实现读写分离与故障切换；Pgpool-II 节点可独立于数据库节点部署。
监控与常见问题
-
关键监控
- 复制状态：主库 select * from pg_stat_replication;（关注 state、sent_lsn/write_lsn/flush_lsn/replay_lsn、sync_state）
- 从库回放：select * from pg_stat_wal_receiver;（关注 receive/ replay 进度）
- 复制延迟：write_lag / flush_lag / replay_lag（单位随版本可能为 ms/µs），结合业务 SLA 设置告警。
-
常见问题与处理
- WAL 被回收导致复制中断：启用复制槽或调大 wal_keep_size/wal_keep_segments，确保备库追赶速度大于 WAL 产生速度。
- 归档失败/磁盘满：监控归档目录容量，设置合理的 archive_command 与清理策略（如按日分目录并定期清理旧文件）。
- 版本/平台不兼容：物理复制要求主从版本一致、平台位数一致；跨版本优先评估逻辑复制或导出导入方案。
