1. PostgreSQL数据迁移的核心挑战第一次做PostgreSQL数据迁移时我盯着屏幕上那个30GB的生产数据库直冒冷汗。这可不是在本地开发环境随便玩玩数据丢失意味着真金白银的损失。经过这些年踩坑填坑的经历我发现数据迁移本质上是在和时间赛跑——既要保证数据完整性又要最小化业务停机时间。迁移过程中最常遇到的三大拦路虎首先是数据一致性特别是业务高峰期做迁移源数据库还在持续写入其次是性能瓶颈我见过一个简单的pg_dump把服务器内存吃光的惨案最后是环境差异开发环境的UTF-8编码遇到生产环境的Latin1时乱码问题能让人debug到怀疑人生。有次迁移电商平台的用户数据就因为漏了--exclude-table-datauser_sessions参数把2000万条废弃会话数据也打包进去硬生生让迁移时间翻了倍。2. 备份策略设计与实战2.1 物理备份 vs 逻辑备份上周帮一家金融公司做迁移时他们CTO问我为什么不用简单的pg_dump非要折腾WAL归档 这得从备份原理说起。逻辑备份就像用手机拍菜单照片——只记录菜品名称和价格表结构和数据而物理备份则是把整个厨房连带厨师都克隆一份数据文件事务日志。当你有200GB以上的数据库时用pg_dump可能要8小时而pg_basebackup配合WAL归档可能2小时就能搞定。这是我常用的物理备份组合拳# 创建基础备份 pg_basebackup -D /backup/primary -Ft -z -Xs -P -U replicator # 配置WAL归档 ALTER SYSTEM SET archive_mode on; ALTER SYSTEM SET archive_command gzip %p /backup/wal/%f.gz;2.2 增量备份的黑科技对于TB级数据库我最近迷上了page-level增量备份。PostgreSQL 13开始支持的pg_combinebackup工具可以像玩积木一样合并备份集。假设每周日做全量备份周一到周六只备份变更页恢复时自动合成完整版本。实测一个1.2TB的数据库增量备份能缩小到平均80GB/天。# 创建增量备份 pg_basebackup --incremental/backup/base -D /backup/incr1 # 合并恢复 pg_combinebackup -o /restore/data /backup/base /backup/incr1 /backup/incr23. 恢复方案的进阶玩法3.1 时间点恢复(PITR)实战去年双十一前某电商平台误删了促销价格表。多亏配置了PITR我们精确回滚到删除前5分钟的状态。关键是要掌握WAL日志的时间线魔法# 创建恢复标记文件 touch /var/lib/postgresql/12/main/recovery.signal # 配置恢复目标 echo restore_command gunzip /backup/wal/%f.gz %p postgresql.auto.conf echo recovery_target_time 2023-07-20 14:45:0008 postgresql.auto.conf3.2 逻辑恢复的智能过滤有时候只需要恢复特定表数据。最近处理的一个案例中客户误删了orders表里2023年的数据但表结构还在。这时候用pg_restore的--table和--data-only组合比全库恢复快10倍pg_restore -U postgres -d db_prod \ --jobs4 \ --tableorders \ --data-only \ --wherecreated_at2023-01-01 \ /backup/logical/orders.dump4. 生产环境迁移的黄金法则4.1 零停机迁移方案帮一家在线教育平台迁移时我们用了逻辑解码订阅的组合拳。先用pg_dump同步基础数据再通过pg_recvlogical实时捕获变更。关键步骤在源库创建逻辑复制槽SELECT * FROM pg_create_logical_replication_slot(migration_slot, pgoutput);启动变更捕获进程pg_recvlogical -d source_db -U replicator \ --slotmigration_slot \ --start \ -f - | psql -d target_db4.2 迁移后的数据校验曾经因为没做数据校验导致两个表的count(*)对不上被迫重新迁移。现在我的工具箱里必备pg_comparator# 校验源库和目标库差异 pg_comparator \ --sourcehostsrc_host dbnameprod \ --targethosttgt_host dbnameprod \ --tableproducts,customers \ --parallel85. 性能调优秘籍5.1 并行备份加速发现pg_dump速度慢试试这个参数组合能让8核服务器的备份速度提升5倍pg_dump -Fd -j 8 \ -Z 6 \ --exclude-table-data*.audit_* \ -f /backup/parallel \ my_database5.2 内存优化技巧遇到could not allocate memory错误时别急着加内存。调整这些参数可能有奇效# 限制单个连接内存 psql -c SET work_mem64MB; -f big_query.sql # 使用磁盘临时文件 pg_dump --disable-dollar-quoting \ --no-unlogged-table-data \ -T temp_* \ my_database6. 云环境迁移特别篇最近帮客户从AWS RDS迁移到自建PostgreSQL集群时发现云厂商的专有扩展是个大坑。我的解决方案是先用pg_dump的--exclude-schema排除云厂商schema使用sed预处理备份文件sed -i /^CREATE EXTENSION.*aws_/d backup.sql在目标库预装必要扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;7. 灾难恢复演练上个月参与某银行的灾备演练时我们的恢复流程经受住了考验。关键是要有清晰的checklist备份验证定期执行pg_restore --list检查备份完整性恢复测试每季度用最新备份在隔离环境做全量恢复文档更新每次架构变更后立即更新恢复手册这是我用的监控脚本片段用于检查备份有效性# 检查最近备份是否有效 LAST_BACKUP$(find /backup -name *.dump -mtime -1 | head -1) if ! pg_restore -l $LAST_BACKUP /dev/null 21; then echo 备份验证失败: $LAST_BACKUP | mail -s 备份异常 dbaexample.com fi8. 工具链推荐经过几十次迁移实战我的工具箱已经迭代到3.0版本可视化监控pgBackRest的web控制台增量备份Barman的retention policy逻辑解码Debezium for PostgreSQL数据校验pg_dumpfilter特别是对于异构迁移我常用以下命令转换MySQL到PostgreSQLpgloader \ mysql://user:passmysql_host/db \ postgresql://user:passpg_host/db \ --with create no indexes
PostgreSQL 数据迁移实战手册：高效备份与恢复的进阶技巧
1. PostgreSQL数据迁移的核心挑战第一次做PostgreSQL数据迁移时我盯着屏幕上那个30GB的生产数据库直冒冷汗。这可不是在本地开发环境随便玩玩数据丢失意味着真金白银的损失。经过这些年踩坑填坑的经历我发现数据迁移本质上是在和时间赛跑——既要保证数据完整性又要最小化业务停机时间。迁移过程中最常遇到的三大拦路虎首先是数据一致性特别是业务高峰期做迁移源数据库还在持续写入其次是性能瓶颈我见过一个简单的pg_dump把服务器内存吃光的惨案最后是环境差异开发环境的UTF-8编码遇到生产环境的Latin1时乱码问题能让人debug到怀疑人生。有次迁移电商平台的用户数据就因为漏了--exclude-table-datauser_sessions参数把2000万条废弃会话数据也打包进去硬生生让迁移时间翻了倍。2. 备份策略设计与实战2.1 物理备份 vs 逻辑备份上周帮一家金融公司做迁移时他们CTO问我为什么不用简单的pg_dump非要折腾WAL归档 这得从备份原理说起。逻辑备份就像用手机拍菜单照片——只记录菜品名称和价格表结构和数据而物理备份则是把整个厨房连带厨师都克隆一份数据文件事务日志。当你有200GB以上的数据库时用pg_dump可能要8小时而pg_basebackup配合WAL归档可能2小时就能搞定。这是我常用的物理备份组合拳# 创建基础备份 pg_basebackup -D /backup/primary -Ft -z -Xs -P -U replicator # 配置WAL归档 ALTER SYSTEM SET archive_mode on; ALTER SYSTEM SET archive_command gzip %p /backup/wal/%f.gz;2.2 增量备份的黑科技对于TB级数据库我最近迷上了page-level增量备份。PostgreSQL 13开始支持的pg_combinebackup工具可以像玩积木一样合并备份集。假设每周日做全量备份周一到周六只备份变更页恢复时自动合成完整版本。实测一个1.2TB的数据库增量备份能缩小到平均80GB/天。# 创建增量备份 pg_basebackup --incremental/backup/base -D /backup/incr1 # 合并恢复 pg_combinebackup -o /restore/data /backup/base /backup/incr1 /backup/incr23. 恢复方案的进阶玩法3.1 时间点恢复(PITR)实战去年双十一前某电商平台误删了促销价格表。多亏配置了PITR我们精确回滚到删除前5分钟的状态。关键是要掌握WAL日志的时间线魔法# 创建恢复标记文件 touch /var/lib/postgresql/12/main/recovery.signal # 配置恢复目标 echo restore_command gunzip /backup/wal/%f.gz %p postgresql.auto.conf echo recovery_target_time 2023-07-20 14:45:0008 postgresql.auto.conf3.2 逻辑恢复的智能过滤有时候只需要恢复特定表数据。最近处理的一个案例中客户误删了orders表里2023年的数据但表结构还在。这时候用pg_restore的--table和--data-only组合比全库恢复快10倍pg_restore -U postgres -d db_prod \ --jobs4 \ --tableorders \ --data-only \ --wherecreated_at2023-01-01 \ /backup/logical/orders.dump4. 生产环境迁移的黄金法则4.1 零停机迁移方案帮一家在线教育平台迁移时我们用了逻辑解码订阅的组合拳。先用pg_dump同步基础数据再通过pg_recvlogical实时捕获变更。关键步骤在源库创建逻辑复制槽SELECT * FROM pg_create_logical_replication_slot(migration_slot, pgoutput);启动变更捕获进程pg_recvlogical -d source_db -U replicator \ --slotmigration_slot \ --start \ -f - | psql -d target_db4.2 迁移后的数据校验曾经因为没做数据校验导致两个表的count(*)对不上被迫重新迁移。现在我的工具箱里必备pg_comparator# 校验源库和目标库差异 pg_comparator \ --sourcehostsrc_host dbnameprod \ --targethosttgt_host dbnameprod \ --tableproducts,customers \ --parallel85. 性能调优秘籍5.1 并行备份加速发现pg_dump速度慢试试这个参数组合能让8核服务器的备份速度提升5倍pg_dump -Fd -j 8 \ -Z 6 \ --exclude-table-data*.audit_* \ -f /backup/parallel \ my_database5.2 内存优化技巧遇到could not allocate memory错误时别急着加内存。调整这些参数可能有奇效# 限制单个连接内存 psql -c SET work_mem64MB; -f big_query.sql # 使用磁盘临时文件 pg_dump --disable-dollar-quoting \ --no-unlogged-table-data \ -T temp_* \ my_database6. 云环境迁移特别篇最近帮客户从AWS RDS迁移到自建PostgreSQL集群时发现云厂商的专有扩展是个大坑。我的解决方案是先用pg_dump的--exclude-schema排除云厂商schema使用sed预处理备份文件sed -i /^CREATE EXTENSION.*aws_/d backup.sql在目标库预装必要扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;7. 灾难恢复演练上个月参与某银行的灾备演练时我们的恢复流程经受住了考验。关键是要有清晰的checklist备份验证定期执行pg_restore --list检查备份完整性恢复测试每季度用最新备份在隔离环境做全量恢复文档更新每次架构变更后立即更新恢复手册这是我用的监控脚本片段用于检查备份有效性# 检查最近备份是否有效 LAST_BACKUP$(find /backup -name *.dump -mtime -1 | head -1) if ! pg_restore -l $LAST_BACKUP /dev/null 21; then echo 备份验证失败: $LAST_BACKUP | mail -s 备份异常 dbaexample.com fi8. 工具链推荐经过几十次迁移实战我的工具箱已经迭代到3.0版本可视化监控pgBackRest的web控制台增量备份Barman的retention policy逻辑解码Debezium for PostgreSQL数据校验pg_dumpfilter特别是对于异构迁移我常用以下命令转换MySQL到PostgreSQLpgloader \ mysql://user:passmysql_host/db \ postgresql://user:passpg_host/db \ --with create no indexes
