一、数据迁移的应用场景
在我参与的某电商平台架构升级项目中，需将运行在CentOS 7的PostgreSQL 9.6集群整体迁移至Ubuntu 22.04上的PostgreSQL 14.9版本。这个典型场景涉及跨操作系统版本（CentOS→Ubuntu）、数据库版本跨度大（9.6→14.9）、数据规模超5TB的三重挑战，考验着数据迁移策略的设计能力。
同类场景还包括：
- 本地机房迁移至公有云（如AWS RDS）
- 从x86架构服务器迁移到ARM架构服务器
- 海外业务合规要求的区域化数据迁移
二、技术方案选择与优缺点对比
2.1 物理复制方案
使用工具：pg_basebackup + pg_upgrade
优点：
- 秒级停机切换
- 完美保留表结构、索引状态
缺点：
- 必须保持主版本号一致（如不能直接从9.6升级到14）
- 跨平台可能产生二进制不兼容
# 适用场景：小版本升级（例如13.3→13.8）
pg_upgrade \
-b /old/postgres/bin \
-B /new/postgres/bin \
-d /old/data \
-D /new/data \
--link
2.2 逻辑导出方案
使用工具：pg_dump + pg_restore
优点：
- 天然解决跨架构迁移问题
- 可选择性迁移特定schema
缺点：
- 大表导出时容易OOM
- 事务完整性依赖导出顺序
# 并行导出示例（使用24个线程）
pg_dump -Fd -j 24 -f /data/dump_dir mydb
# 并行恢复（需关闭触发器）
pg_restore -j 16 --disable-triggers -d newdb /data/dump_dir
2.3 逻辑复制方案
使用工具：PUBLICATION/SUBSCRIPTION
优点：
- 支持增量同步
- 最小化停机时间
缺点：
- 要求主备库均使用10+版本
- 需要处理DDL变更
-- 在源库创建发布
CREATE PUBLICATION migration_pub FOR ALL TABLES;
-- 在目标库创建订阅
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=source_host dbname=mydb'
PUBLICATION migration_pub
WITH (copy_data = true);
三、跨平台迁移的关键技术点
3.1 文件编码与排序规则
Linux与Windows的文件路径大小写敏感差异可能导致SQL脚本执行失败：
-- 错误示例（Linux系统区分大小写）
CREATE TABLE "OrderHistory" (...); -- 双引号强制保留大小写
-- 正确写法（统一使用小写命名）
CREATE TABLE order_history (...);
3.2 特殊类型兼容处理
迁移jsonb字段时注意版本差异：
-- PostgreSQL 9.6不支持jsonb_path_exists
SELECT data->'user'->>'name'
FROM logs
WHERE data @> '{"status": "success"}';
-- PostgreSQL 14支持路径查询
SELECT jsonb_path_query(data, '$.user.name')
FROM logs
WHERE jsonb_path_exists(data, '$.status ? (@ == "success")');
3.3 扩展模块的移植方案
处理不同平台的PostGIS扩展：
# 在目标集群预装相同版本的PostGIS
sudo apt install postgresql-14-postgis-3
# 使用pg_dump单独导出扩展
pg_dump -Fc -n public -T spatial_ref_sys mydb > custom.dump
四、实战案例：跨境数据迁移完整流程
4.1 迁移前检查清单
- 确认目标库字符集为UTF8：
SHOW server_encoding;
- 核对时区配置：
SELECT current_setting('TIMEZONE');
4.2 混合迁移策略实施
结合物理复制和逻辑导出的混合方案：
# 第一步：全量物理复制
pg_basebackup -h source_host -D /var/lib/pgsql/14/data -Xs -P
# 第二步：增量差异同步
psql -c "SELECT pg_create_restore_point('before_final_switch')"
rsync -avz --delete source_host:/pg_wal/ /pg_wal_archive/
4.3 验证数据一致性的Python脚本
# compare_md5.py （Python 3.8+）
import psycopg2
def get_table_md5(conn, table):
cur = conn.cursor()
cur.execute(f"SELECT md5(array_agg(t)::text) FROM {table} t")
return cur.fetchone()[0]
source_conn = psycopg2.connect("host=src_host dbname=mydb")
dest_conn = psycopg2.connect("host=dest_host dbname=mydb")
tables = ['users', 'orders', 'products']
for tbl in tables:
src_md5 = get_table_md5(source_conn, tbl)
dst_md5 = get_table_md5(dest_conn, tbl)
assert src_md5 == dst_md5, f"{tbl} 数据不一致"
五、必须避开的陷阱
5.1 序列值不同步
迁移后务必同步序列：
-- 获取当前序列值
SELECT last_value FROM user_id_seq;
-- 重设序列起始值
ALTER SEQUENCE user_id_seq RESTART WITH 1000000;
5.2 忽略物化视图依赖
处理物化视图的正确顺序：
-- 错误的直接刷新
REFRESH MATERIALIZED VIEW sales_summary; -- 可能缺少基础表
-- 正确的顺序化刷新
BEGIN;
REFRESH MATERIALIZED VIEW product_stats;
REFRESH MATERIALIZED VIEW sales_summary;
COMMIT;
六、迁移后的性能调优技巧
6.1 统计信息更新
强制刷新全库统计信息：
vacuumdb --analyze-in-stages -j 8 mydb
6.2 索引重建策略
避免直接REINDEX导致锁表：
-- 使用CONCURRENTLY选项
REINDEX INDEX CONCURRENTLY idx_order_date;
七、特别注意事项手册
7.1 外键约束的处理
迁移期间禁用外键检查：
SET session_replication_role = replica; -- 禁用触发器与外键
-- 执行数据导入操作
SET session_replication_role DEFAULT;
7.2 大对象迁移方案
单独处理超过2GB的PDF文件：
pg_dump -Fc -t large_objects mydb > lobs.dump
pg_restore -d newdb lobs.dump
八、技术演进趋势观察
云原生时代的数据迁移呈现以下变化：
- 物理卷快照技术的应用（如AWS EBS快照）
- 逻辑复制支持双向同步
- PG15新增的mergetsdb扩展支持多主架构
评论