多台Windows IoT设备本地Postgres数据库同步至中心库方案咨询

首先直接排除流复制、传统主从复制方案，原因如下：

• 传统流复制是物理整实例复制，要求主从节点的Postgres版本、操作系统架构完全一致，你当前场景下IoT端为Windows、中心端可能为Linux，架构不匹配无法使用
• 流复制要求网络长期稳定，偏远点位网络波动导致断连过久的话，本地WAL日志会被覆盖，重连后需要完全重搭同步链路，完全不适应你的部署场景
• 流复制是一主多从的架构，不支持多台边缘节点同时往同一个中心库做多对一同步

推荐选择 基于增量数据捕获的逻辑单向同步方案，适配性最高：

• 仅同步业务层数据，不需要整实例复制，跨平台、跨小版本Postgres的兼容性好
• 天然支持多节点向同一个中心库同步，可通过独立标识区分不同IoT节点的数据避免冲突
• 断连后支持断点续传，不会因为短期网络波动丢失数据，匹配偏远点位的网络特征
• 同步粒度可控，可仅同步需要上传的监控业务表，不需要同步本地临时表、系统表，节省带宽资源

可以根据你的部署规模、技术栈灵活选择：

1. 中小规模场景首选 pglogical

• 是PostgreSQL官方维护的逻辑复制扩展，兼容性强，完美支持Windows/Linux跨平台同步
• 支持单向、多对一同步，支持表级、行级过滤，可自定义仅同步需要上传的监控数据表
• 断连后自动续传，同步延迟可控制在秒级，完全满足10秒/次的同步频率要求
• 配置门槛低，仅需要在IoT端调整wal_level = logical、安装扩展、创建发布，中心库创建订阅即可完成链路搭建

2. 表结构简单、需要高度自定义的场景：pg_dump + 自定义调度脚本

• 编写批处理/Python脚本，每10秒在IoT端执行pg_dump增量导出新增数据（用自增ID、数据采集时间戳作为增量过滤条件），同步到中心库执行导入
• 不需要修改数据库默认配置，灵活性极高，可自定义冲突处理、数据补传逻辑
• 网络中断时可在本地缓存导出的SQL文件，网络恢复后自动补传，不会丢失数据

3. 节点超过50台、数据量大的大规模场景：Debezium + 消息队列

• 通过Debezium捕获IoT端Postgres的增量数据变更，上报到中心端消息队列后消费写入中心库
• 支持同步链路监控、数据校验、告警，但是部署复杂度相对较高

• 所有同步方案都需要提前给每个IoT节点的业务表增加唯一设备标识字段，避免多个节点同步同一张表时出现主键冲突
• 调整IoT端Postgres的WAL日志保留时间，至少保留24小时以上，避免断网时间过长导致增量日志丢失，需要全量重传数据
• 提前配置同步冲突规则，比如中心库已存在的数据默认跳过，或以IoT端最新数据覆盖，可根据业务需求自行选择

内容的提问来源于stack exchange，提问作者Zoner