如何在大型PostGIS/PostgreSQL数据库中安全无停机更新数据

针对你的场景（1亿条空间数据、250GB只读生产库，需先在副本更新校验再替换），以下是几个成熟的标准方案：

方案一：流复制备库+蓝绿部署 #

这是PostgreSQL生态里最常用的无停机切换方案，适合需要频繁更新的场景：

• 平时维护一个流复制备库，保持和生产主库的实时同步（因为生产只读，备库同步压力极低）。
• 当需要更新时，断开备库的复制链路（执行SELECT pg_stop_backup();，或修改配置文件停止复制），将备库转为独立可写实例。
• 在这个独立实例上运行更新脚本，完成数据校验：比如对比空间对象的哈希值、统计总行数、检查空间索引有效性、用ST_IsValid验证几何对象完整性。
• 校验通过后，修改应用连接池（如PgBouncer）配置，将流量切换到这个新主库。
• 切换完成后，原生产库可留作备份，或重新配置为新主库的备库，用于下一次更新。
• 优势：切换几乎无延迟，备库平时同步数据，更新前无需全量备份，节省时间。

方案二：pg_basebackup全量副本+连接池切换 #

适合更新频率不高，或不想长期维护备库的场景：

• 当需要更新时，用pg_basebackup -D /path/to/new/db -h production-host -U replication从生产库拉取全量备份，创建独立数据库实例。
• 启动新实例，运行更新脚本并完成校验。
• 通过连接池切换流量：如果用PgBouncer，只需修改pgbouncer.ini里的db_host，执行pgbouncer -R reload即可，无需重启应用。
• 确认新库稳定后，旧生产库保留7-14天作为回滚备份，之后按需销毁。
• 注意：250GB的库用pg_basebackup耗时取决于网络和存储速度，建议在业务低峰期操作。

方案三：存储快照+表空间切换（适合超大空间表） #

如果空间数据集中在少数几张大表，可进一步缩短切换时间：

• 利用存储层快照功能（如ZFS快照、LVM快照），给生产库数据目录拍即时快照。
• 基于快照创建可写克隆实例，在上面运行更新脚本并校验。
• 校验通过后，在生产库执行ALTER TABLE your_spatial_table SET TABLESPACE new_tablespace;（new_tablespace指向更新后克隆表的表空间）。该操作仅修改元数据，瞬间完成，不移动实际数据。
• 优势：切换时间控制在秒级，完全无停机。
• 注意：需要存储系统支持快照和克隆，且提前规划表空间配置。

• 数据校验必须到位：不能只看行数，要验证空间对象完整性、关键空间关系（如ST_Intersects对比核心区域），还要执行VACUUM ANALYZE确保统计信息准确。
• 回滚机制不可少：无论用哪个方案，都要保留旧生产库至少一段时间，或切换前给旧库做全量备份，万一新库出问题可快速切回。
• 必须用连接池：应用直接连数据库会导致切换时需重启应用，无法无停机。用PgBouncer等连接池，修改配置重载即可。
• 更新脚本要幂等：确保脚本可重复执行不导致数据异常，比如用UPSERT代替INSERT，或先删旧数据再插新数据（只读库无并发写入冲突）。

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