从0到1搭建PostgreSQL：数据迁移 | 维护成本降低

▌ 技术引导
我之前在部署PostgreSQL数据库时，决定从0到1搭建一个高可用且维护成本低的架构，结果踩了不少坑。通过实战总结，这个架构必须用逻辑复制+pgpool-II+监控工具组合，才能实现数据迁移和长期维护成本的平衡。用逻辑复制来迁移数据，比物理复制更快，而且能保证数据一致性。pgpool-II负责负载均衡和连接池，避免了直接连接主库带来的压力。监控方面，我用了pg_stat_statements和pgBadger，这两个工具能帮我看清查询瓶颈和日志分析。在迁移数据时，遇到了主从延迟导致的数据不一致问题，硬着头皮改成了异步复制+定期校验，成本反而更低。重点是用docker部署，配合kubernetes管理，这样能快速迭代和回滚。整个过程中，配置文件的细节最关键，不要偷懒，每个参数都要踩实。

▌ 技术参考

一 用逻辑复制迁移数据
逻辑复制是PostgreSQL 10引入的特性，通过slot机制实现数据同步。迁移时，我通常用pg_basebackup做初始拷贝，再手动同步数据。但逻辑复制需要在源库和目标库都开启wal_level=logical，这会占用更多磁盘空间，影响性能。数据迁移时，我直接通过pg_dump并行导出表，再用psql导入，但发现这种方案在迁移大数据量时速度太慢。后来改用pg_repack工具做在线表重组，既能避免锁表，又能压缩数据。迁移后，我通过pg_restore加--data-only参数过滤掉DDL语句，减少回滚风险。

二 使用docker部署PostgreSQL
我用docker部署PostgreSQL时，必须指定--shm-size=512m参数，否则会因为共享内存不足导致主从同步中断。镜像选的是官方的14版本，加上了POSTGRES_USER和POSTGRES_DB环境变量，避免每次启动都用默认用户。部署时，我会把data目录挂载到宿主机，这样容器重启时可以保留数据。同时用docker-compose定义多个容器，主从节点分别运行，通过bind方式挂载配置文件，这样修改配置不需要重启容器。

三 pgpool-II的配置与优化
pgpool-II需要在从库开启流复制，并配置好pg_hba.conf允许从池连接。配置文件里，我设定了backend connect timeout=1000，这样能减少连接超时问题。同时，把num_init_children设成和CPU核心数一致，比如4个核心就设4，这样能提升并发处理能力。我在监控时用pgpool的stat页面看连接数，但发现默认日志级别不够，手动调整了log_level=debug1。还配置了pgpool的query cache，这样重复查询能被缓存，降低主库压力。

四 数据一致性校验与主从延迟处理
逻辑复制虽然方便，但主从延迟是常态。我用pgbench压测发现，延迟超过5秒时，会严重影响查询性能。解决办法是定期用pg_basebackup做快照，再用pg_rewind修复数据差异，这样比等待同步更高效。但pg_rewind需要主从节点停机，我最后决定用异步复制+手动校验的方式，每次迁移后执行vacuum analyze，再用pg_stat_statements的query_count字段判断是否稳定。另外，开启wal_level=logical后，必须定期清理wal文件，否则磁盘会爆掉。

五 使用pgBadger分析日志
pgBadger是个强大的日志分析工具，我用它分析了迁移到新架构后的日志，发现很多慢查询是全表扫描，直接修改了查询语句，加了索引。配置时，我用了--output=csv参数生成CSV文件，再用Excel做数据透视分析，效率提升明显。还通过--analyze=1参数让pgBadger自动识别频繁查询，这比自己手动看日志省事多了。不过它对日志格式要求严格，必须用log_line_prefix配置，否则无法解析。

六 定期维护与清理
我的维护流程包括每周一次vacuum full和analyze，但发现这会锁表，影响业务。后来改用pg_repack在线重组表，既避免了锁表，又能回收空间。另外，用pg_stat_statements监控top 100的查询，发现有30%是不合理的，直接和业务沟通优化。还定期清理旧的wal文件和日志，用find命令加-mtime参数删除超过7天的文件，这样磁盘空间不会被撑爆。

七 配置连接池与负载均衡
在pgpool-II里，我设置了max_pool=10，这样能有效控制连接数。使用pgpool的load balancing功能时，直接在客户端配置host=pool，即可自动分配到主从节点。但要注意，如果主库负载过高，pgpool会自动切换到从库，这会导致事务失败，所以要配置failover的策略，比如使用--replication=on参数让从库参与事务。

八 使用docker网络和端口映射
在docker里，我用host网络模式避免端口映射的问题，直接把5432端口暴露给宿主机。如果用bridge模式，必须设置--publish参数，否则客户端无法连接。同时，我使用自定义网络，让主从容器在同一子网，这样内部通信更快，也不用依赖宿主机IP。

九 定期备份与恢复演练
我用pg_dumpall做全局备份，每次执行后会生成一个tar包，存储在对象存储里。恢复时，先用pg_restore还原数据，再用pg_repack修复索引。还设置了cron定时任务，每天凌晨备份一次，避免业务高峰期。但发现有时候备份文件太大，用了--exclude-table=public.table_name参数，只备份关键表，节省时间和空间。

十 持续集成与自动化部署
用Jenkins做CI/CD时，我写了shell脚本自动构建docker镜像，然后push到私有仓库。部署时，用kubectl apply命令启动服务，再用helm做版本管理。这样能快速回滚到旧版本，而且不用手动操作。还配置了Prometheus监控PostgreSQL的性能指标，比如checkpoints_timed和checkpoint_segments，这样能提前发现潜在问题。

十一 使用pg_stat_statements优化查询
在pg_stat_statements里，我设置了track_event_types=‘SELECT,UPDATE,DELETE’，这样能精确记录查询类型。通过查看query_count和rows_sent，发现有查询在无索引的情况下执行，就手动加索引。还用plan_cache_mode=‘shared’让多个会话共享查询计划，减少资源浪费。

十二 配置主从同步的延迟阈值
在主从同步中，我用pg_stat_replication监控复制延迟，设置了一个脚本自动判断延迟是否超过10秒，超过就触发告警。不过有时候延迟是正常的，比如写入量大时，所以需要结合业务情况判断。

十三 分析pg_rewind的适用场景
pg_rewind适用于逻辑复制中断后，快速修复数据差异，但必须确保主从节点版本一致，否则会报错。我通常在迁移完成后执行一次pg_rewind，避免残留数据影响后续查询。

十四 优化wal_level和max_wal_senders
在生产环境里，我将wal_level设为logical，max_wal_senders设为5，这样能支持多个从库。不过这样会占用更多磁盘空间，所以必须配置wal_keep_segments=1000，避免wal文件被自动删除。

十五 定期更新PostgreSQL版本
我每季度更新一次PostgreSQL版本，用docker的tag管理，比如14.5-alpine。更新前会做一次pg_repack和pg_dumpall，确保数据安全。这样能利用新版本的性能优化，比如并行查询和索引优化。