外观
PostgreSQL 大数据量迁移方案
迁移方法
并行备份恢复迁移
pg_dump/pg_restore 并行迁移
bash# 并行备份（4个并行进程） pg_dump -j 4 -h source_host -p source_port -U username -d dbname -F d -b -v -f /path/to/backup/ # 并行恢复（4个并行进程） pg_restore -j 4 -h target_host -p target_port -U username -d dbname -v /path/to/backup/分表备份恢复
bash# 获取所有表名 psql -h source_host -p source_port -U username -d dbname -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" > tables.txt # 分表备份 for table in $(cat tables.txt); do pg_dump -h source_host -p source_port -U username -d dbname -t $table -F c -v -f /path/to/backup/${table}.dump done # 分表恢复 for table in $(cat tables.txt); do pg_restore -h target_host -p target_port -U username -d dbname -v /path/to/backup/${table}.dump done
文件系统级迁移
直接文件复制
bash# 停止源数据库 pg_ctl -D /path/to/source/data stop # 使用rsync进行高效文件复制 rsync -avz --progress /path/to/source/data/ target_host:/path/to/target/data/ # 启动目标数据库 pg_ctl -D /path/to/target/data startLVM 快照迁移
bash# 创建LVM快照 lvcreate -L 100G -s -n pg_snap /dev/vg/pg_data # 挂载快照 mkdir /mnt/pg_snap mount /dev/vg/pg_snap /mnt/pg_snap # 复制数据 rsync -avz --progress /mnt/pg_snap/ target_host:/path/to/target/data/ # 卸载并删除快照 umount /mnt/pg_snap lvremove -f /dev/vg/pg_snap
增量迁移
WAL 归档增量迁移
bash# 源服务器配置WAL归档 echo "wal_level = archive" >> postgresql.conf echo "archive_mode = on" >> postgresql.conf echo "archive_command = 'cp %p /path/to/archive/%f'" >> postgresql.conf # 进行基础备份 pg_basebackup -h source_host -p source_port -D /path/to/backup -U replication -v -P # 迁移基础备份 rsync -avz /path/to/backup/ target_host:/path/to/data/ # 应用WAL增量 while true; do pg_receivewal -h source_host -p source_port -U replication -D /path/to/wal -v pg_waldump -f /path/to/wal/* > /dev/null 2>&1 if [ $? -eq 0 ]; then break fi sleep 1 done # 恢复增量WAL pg_ctl -D /path/to/target/data stop cp /path/to/wal/* /path/to/target/data/pg_wal/ pg_ctl -D /path/to/target/data start逻辑复制增量迁移
sql-- 在源数据库创建发布 CREATE PUBLICATION pub1 FOR ALL TABLES; -- 在目标数据库创建订阅 CREATE SUBSCRIPTION sub1 CONNECTION 'host=source_host port=5432 dbname=dbname user=replication password=password' PUBLICATION pub1 WITH (copy_data = false);
性能优化
源端优化
调整源数据库配置
sql-- 增加WAL缓冲区 ALTER SYSTEM SET wal_buffers = '64MB'; -- 增加检查点完成目标 ALTER SYSTEM SET checkpoint_completion_target = 0.9; -- 增加最大WAL发送器数量 ALTER SYSTEM SET max_wal_senders = 10; -- 增加WAL保留大小 ALTER SYSTEM SET wal_keep_size = '2GB';关闭不必要的服务
bash# 暂停监控服务 systemctl stop prometheus node_exporter # 暂停备份服务 systemctl stop pgbackrest
传输优化
网络优化
bash# 调整TCP缓冲区大小 sysctl -w net.core.rmem_max=16777216 sysctl -w net.core.wmem_max=16777216 sysctl -w net.ipv4.tcp_rmem="4096 87380 16777216" sysctl -w net.ipv4.tcp_wmem="4096 65536 16777216" # 使用更快的传输协议 rsync -avz --progress -e "ssh -o Compression=no" /path/to/source/ target_host:/path/to/target/压缩传输
bash# 使用xz压缩（高压缩率） tar -cJf /path/to/backup.tar.xz /path/to/source/ # 使用zstd压缩（高压缩速度） tar -c -I zstd -f /path/to/backup.tar.zst /path/to/source/
目标端优化
调整目标数据库配置
sql-- 关闭自动清理 ALTER SYSTEM SET autovacuum = off; -- 增加维护工作内存 ALTER SYSTEM SET maintenance_work_mem = '4GB'; -- 增加 checkpoint 段大小 ALTER SYSTEM SET max_wal_size = '8GB';恢复后优化
sql-- 重建统计信息 ANALYZE VERBOSE; -- 重建索引 REINDEX DATABASE dbname; -- 开启自动清理 ALTER SYSTEM SET autovacuum = on;
迁移验证
数据完整性验证
行数对比
bash# 在源端获取行数 psql -h source_host -p source_port -U username -d dbname -t -c "SELECT table_name, count(*) FROM information_schema.tables WHERE table_schema = 'public' GROUP BY table_name" > source_counts.txt # 在目标端获取行数 psql -h target_host -p target_port -U username -d dbname -t -c "SELECT table_name, count(*) FROM information_schema.tables WHERE table_schema = 'public' GROUP BY table_name" > target_counts.txt # 对比行数 diff source_counts.txt target_counts.txt数据抽样验证
sql-- 在源端获取抽样数据 SELECT * FROM important_table ORDER BY random() LIMIT 100 INTO OUTFILE '/tmp/source_sample.csv' CSV; -- 在目标端获取抽样数据 SELECT * FROM important_table ORDER BY random() LIMIT 100 INTO OUTFILE '/tmp/target_sample.csv' CSV; -- 对比抽样数据 diff /tmp/source_sample.csv /tmp/target_sample.csv校验和验证
sql-- 启用数据页校验和 ALTER SYSTEM SET data_checksums = on; -- 运行页校验 SELECT * FROM pg_stat_checksum_progress();
性能验证
查询性能对比
bash# 在源端运行性能测试 pgbench -h source_host -p source_port -U username -d dbname -t 10000 -c 10 -j 4 > source_perf.txt # 在目标端运行性能测试 pgbench -h target_host -p target_port -U username -d dbname -t 10000 -c 10 -j 4 > target_perf.txt # 对比性能结果 cat source_perf.txt | grep "tps" cat target_perf.txt | grep "tps"索引使用验证
sql-- 检查索引使用情况 SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes;
最佳实践
迁移前准备
环境评估
- 评估源端和目标端的硬件配置
- 测试网络带宽和延迟
- 评估数据量和增长趋势
数据清理
sql-- 删除过期数据 DELETE FROM log_table WHERE created_at < CURRENT_DATE - INTERVAL '30 days'; -- 清理死元组 VACUUM ANALYZE VERBOSE; -- 重建索引 REINDEX DATABASE dbname;备份策略
- 迁移前进行全量备份
- 配置WAL归档
- 测试备份恢复流程
迁移执行策略
分阶段迁移
- 第一阶段：迁移历史数据（低峰期）
- 第二阶段：迁移增量数据（低峰期）
- 第三阶段：切换应用（维护窗口）
监控与日志
bash# 监控迁移进度 tail -f /path/to/postgresql.log # 记录迁移时间 start_time=$(date +%s) # 执行迁移命令 end_time=$(date +%s) echo "迁移耗时：$((end_time - start_time))秒" >> migration.log回滚计划
- 准备回滚脚本
- 测试回滚流程
- 设定回滚决策点
迁移后优化
统计信息更新
sql-- 重建所有表统计信息 ANALYZE VERBOSE; -- 仅重建特定表 ANALYZE VERBOSE important_table;索引优化
sql-- 重建所有索引 REINDEX DATABASE dbname; -- 重建特定索引 REINDEX INDEX index_name;配置调整
sql-- 根据目标硬件调整配置 ALTER SYSTEM SET shared_buffers = '8GB'; ALTER SYSTEM SET effective_cache_size = '24GB'; ALTER SYSTEM SET maintenance_work_mem = '1GB';
常见问题（FAQ）
Q1：如何处理迁移过程中的网络中断？
A1：
- 使用支持断点续传的工具（如rsync）
- 采用增量迁移策略
- 记录迁移进度，便于恢复
Q2：迁移后数据不一致怎么办？
A2：
- 检查迁移日志，定位问题
- 使用逻辑复制同步增量数据
- 对于少量不一致，手动修复
Q3：如何加快大数据量迁移速度？
A3：
- 使用并行迁移（增加-j参数）
- 调整数据库配置参数
- 使用更快的存储介质
- 优化网络传输（压缩、调整MTU等）
Q4：迁移过程中如何减少对生产环境的影响？
A4：
- 在低峰期执行迁移
- 限制迁移资源使用（如使用--jobs参数限制并行度）
- 关闭不必要的服务
- 使用逻辑复制减少锁表时间
Q5：如何验证迁移后的数据完整性？
A5：
- 对比表行数
- 数据抽样验证
- 运行校验和检查
- 执行业务逻辑测试