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压缩级别61-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:passmysql-host:3306/source_db \ postgresql://user:passpg-host:5432/target_db转换注意事项自动处理自增ID转换为SERIAL类型MySQL的datetime精度为秒级PG的timestamp精度为微秒级需要单独处理ENGINEInnoDB等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 \N5. 性能监控与故障排查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.csvERROR: 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_HOSTproduction-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 hostsource-db userrepuser passwordsecret dbnamesource_db PUBLICATION orders_pub;这种方案相比定时导出导入能实现秒级延迟的数据同步特别适合报表库等场景。 
长河君 · 2026/11/4 9:54:43 · 阅读 1.2k PostgreSQL数据迁移实战：从基础操作到性能优化
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压缩级别61-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:passmysql-host:3306/source_db \ postgresql://user:passpg-host:5432/target_db转换注意事项自动处理自增ID转换为SERIAL类型MySQL的datetime精度为秒级PG的timestamp精度为微秒级需要单独处理ENGINEInnoDB等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 \N5. 性能监控与故障排查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.csvERROR: 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_HOSTproduction-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 hostsource-db userrepuser passwordsecret dbnamesource_db PUBLICATION orders_pub;这种方案相比定时导出导入能实现秒级延迟的数据同步特别适合报表库等场景。