1. 数据迁移的基础认知
PostgreSQL作为企业级开源关系数据库，数据导入导出是DBA和开发者的高频操作。不同于简单的文件拷贝，数据库迁移需要考虑数据类型映射、字符集兼容、事务完整性等专业因素。我在金融和电商系统的数据迁移中，曾因忽视这些细节导致过严重事故。
典型场景包括：跨版本升级时的表结构迁移（如从PG 9.6到PG 15）、生产环境到测试环境的数据同步、不同数据库系统间的数据交换（如Oracle到PostgreSQL）。每种场景对数据完整性的要求不同，比如金融交易数据必须保证零丢失，而用户行为日志可以容忍少量差异。
2. 标准导出工具pg_dump深度解析
2.1 基础命令结构与模式选择
pg_dump的基本命令格式如下：
pg_dump -h 主机名 -p 端口 -U 用户名 -d 数据库名 -f 输出文件关键模式选择：
- 纯数据模式（-a/--data-only）：仅导出数据，适合已有相同结构的环境
- 纯结构模式（-s/--schema-only）：仅导出DDL语句，用于初始化空库
- 自定义格式（-Fc）：二进制格式，支持并行恢复和选择性恢复
- 目录格式（-Fd）：将数据拆分为多个文件，支持多线程操作
重要提示：生产环境导出务必使用-Fc或-Fd格式，它们支持断点续传和压缩，能显著降低大型数据库的迁移风险
2.2 高级参数调优实践
针对TB级数据库的优化配置示例：
pg_dump -h db-prod-01 -p 5432 -U repl_user -d order_system \ -Fd -j 8 -Z 6 -f /mnt/backup/order_system_$(date +%Y%m%d)参数说明：
-j 8：启用8个并行worker（建议不超过CPU核心数）-Z 6：采用zlib压缩级别6（1-9，数值越高压缩率越大但CPU消耗越高）- 输出路径包含日期变量，避免覆盖历史备份
实测数据：在32核服务器上导出1.2TB的电商订单数据，单线程模式耗时4小时18分，而使用8线程仅需47分钟。
3. 批量导入技术方案对比
3.1 COPY命令的高效使用
COPY是PostgreSQL原生的高性能导入方式，语法示例：
-- 从CSV导入 COPY customers FROM '/data/migration/customers.csv' WITH (FORMAT csv, HEADER true, DELIMITER '|'); -- 导出到文件 COPY (SELECT * FROM orders WHERE create_date > '2023-01-01') TO '/data/export/recent_orders.csv' WITH CSV HEADER;性能优化技巧：
- 关闭自动提交：
BEGIN; COPY...; COMMIT;减少事务开销 - 增大work_mem：临时设置
SET work_mem TO '256MB';加速排序操作 - 对于超大型文件，先用split命令分割后并行导入
3.2 psql的\copy与\i命令
当客户端与服务端不在同一主机时，应使用psql的元命令：
# 导入SQL脚本 psql -h db-host -U admin -d target_db -f /path/to/import.sql # 交互式执行\copy psql -h db-host -U admin -d target_db \copy table_name FROM '/local/path/data.csv' WITH CSV关键区别：
COPY是SQL命令，在服务端读写文件\copy是psql命令，在客户端读写文件
4. 跨平台迁移实战案例
4.1 从MySQL迁移到PostgreSQL
使用pgloader工具实现异构数据库迁移：
pgloader \ mysql://user:pass@mysql-host:3306/source_db \ postgresql://user:pass@pg-host:5432/target_db转换注意事项：
- 自动处理自增ID转换为SERIAL类型
- MySQL的datetime精度为秒级，PG的timestamp精度为微秒级
- 需要单独处理ENGINE=InnoDB等MySQL特有语法
4.2 处理CSV文件的特殊问题
常见陷阱及解决方案：
- 编码问题：先用
file -i filename.csv确认实际编码，导入时指定ENCODING 'UTF-8' - 日期格式：使用
TO_DATE()函数显式转换，如TO_DATE('01/05/2023', 'DD/MM/YYYY') - 空值处理：CSV中用\N表示NULL，需在COPY命令中添加
NULL '\N'
5. 性能监控与故障排查
5.1 导入过程监控方法
在另一个会话中执行监控查询：
-- 查看正在运行的COPY进程 SELECT pid, query_start, state, query FROM pg_stat_activity WHERE query LIKE 'COPY%'; -- 检查锁等待情况 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid;5.2 常见错误解决方案
| 错误现象 | 可能原因 | 解决方案 |
|---|---|---|
| "ERROR: invalid byte sequence for encoding "UTF8"" | 文件编码不匹配 | 使用iconv转换编码：iconv -f GBK -t UTF-8 source.csv > fixed.csv |
| "ERROR: duplicate key value violates unique constraint" | 主键冲突 | 添加ON CONFLICT DO NOTHING子句或先清空目标表 |
| COPY hang住无响应 | 外键约束验证 | 临时禁用触发器：ALTER TABLE ... DISABLE TRIGGER ALL |
| 内存不足被OOM Killer终止 | 大事务消耗内存 | 分批次导入，每10万条提交一次 |
6. 自动化与进阶技巧
6.1 使用Shell脚本实现定时备份
#!/bin/bash DATE=$(date +%Y%m%d) PG_HOST="production-db.example.com" BACKUP_DIR="/backups/pg/${DATE}" mkdir -p ${BACKUP_DIR} # 并行备份所有数据库 for DB in $(psql -h ${PG_HOST} -U postgres -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1')" -t) do pg_dump -h ${PG_HOST} -U postgres -Fd -j 4 -Z 5 -f ${BACKUP_DIR}/${DB} ${DB} & done wait # 保留最近7天备份 find /backups/pg -type d -mtime +7 -exec rm -rf {} \;6.2 逻辑复制实现准实时同步
配置发布端：
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items; ALTER SYSTEM SET wal_level = logical; SELECT pg_reload_conf();订阅端配置：
CREATE SUBSCRIPTION orders_sub CONNECTION 'host=source-db user=repuser password=secret dbname=source_db' PUBLICATION orders_pub;这种方案相比定时导出导入，能实现秒级延迟的数据同步，特别适合报表库等场景。
