Debian 环境下 PostgreSQL 数据迁移方法
一 常用方法速览
- 逻辑迁移：使用 pg_dump/pg_restore 导出为自定义格式或 tar 包，跨版本、跨主机、跨平台通用，适合大多数迁移与升级场景。
- 全库脚本迁移：使用 pg_dumpall 生成 SQL 脚本，便于在新实例上重建全局对象（角色、表空间等）及全部数据库。
- 文件级导入导出：使用 COPY 批量导入/导出 CSV/TSV，适合从外部系统批量灌数或做中间交换。
- 异构迁移：使用 pgloader 从 MySQL/Oracle/CSV 等源迁移到 PostgreSQL，支持自动建表、类型映射与转换。
- 物理迁移/复制：通过 WAL 流复制或文件系统拷贝实现近零停机迁移，要求版本兼容、停机时间极短或需搭建备库切换。
二 标准流程与命令示例
- 1）安装与版本确认（两端）
- 安装：sudo apt update && sudo apt install postgresql postgresql-contrib
- 查看版本：psql -V 与 sudo -u postgres psql -c “SELECT version();”
- 2）源库备份（单库，推荐自定义格式）
- pg_dump -U username -h source_host -p 5432 -d mydb -F c -b -v -f /path/mydb.dump
- 3）传输到目标
- scp /path/mydb.dump user@target:/path/
- 4）目标库准备
- 创建空库：sudo -u postgres createdb mydb
- 5）目标库恢复
- pg_restore -U username -h target_host -p 5432 -d mydb -v /path/mydb.dump
- 6）校验
- psql -U username -d mydb -c “SELECT COUNT(*) FROM my_table;”
- 7）清理
- 8）全库脚本方式（含角色/表空间）
- 备份：sudo -u postgres pg_dumpall -U username -f /path/all.sql
- 恢复：sudo -u postgres psql -U username -f /path/all.sql
- 9）文件级导入（CSV 示例）
- 导出：COPY mytable TO ‘/path/data.csv’ WITH CSV HEADER;
- 导入：COPY mytable FROM ‘/path/data.csv’ WITH CSV HEADER;
- 10）异构迁移（pgloader 示例）
- 安装：sudo apt-get install pgloader
- 配置 my.load：
- LOAD DATABASE
- FROM mysql://user:pass@host:port/src
- INTO postgresql:///tgt
- WITH include drop, create tables, create indexes, reset sequences, foreign keys
- 执行：pgloader my.load
三 方法选择建议
| 场景 |
推荐方法 |
主要优点 |
注意点 |
| 同版本或相近版本、跨主机迁移 |
pg_dump/pg_restore |
通用、灵活、可并行、可选择性恢复 |
大对象需加 -b；目标库先建好 |
| 迁移全部数据库与全局对象 |
pg_dumpall |
一次脚本重建全局对象与所有库 |
恢复顺序与权限、依赖需校验 |
| 外部系统批量数据导入 |
COPY/CSV |
高效、可断点续传、易审计 |
字符集、分隔符、Header、NULL 处理 |
| MySQL/Oracle 到 PostgreSQL |
pgloader |
自动建表、类型映射、转换规则 |
复杂类型需自定义 CAST/规则 |
| 近零停机升级/迁移 |
WAL 流复制/物理迁移 |
停机时间极短、一致性好 |
版本兼容、网络与权限配置复杂 |
四 关键注意事项
- 版本与兼容性：尽量保持 源/目标 PostgreSQL 主版本一致；跨大版本时先在测试环境演练，必要时调整数据类型与索引策略。
- 配置与连通性：按需开启远程访问，编辑 /etc/postgresql//main/postgresql.conf 与 pg_hba.conf，例如 listen_addresses、认证方式等，并重启服务。
- 权限与安全：执行迁移的数据库用户需具备相应权限；备份文件与传输链路注意 权限最小化与加密。
- 大对象与特殊类型：使用 -b 保留大对象；检查 序列、默认值、外键、触发器、视图 依赖顺序。
- 校验与回滚：迁移后做 行数、主键/唯一性、抽样校验；保留回滚方案（源库只读或延迟切换窗口）。
五 常见问题与排错
- 角色不存在或权限不足：用 pg_dumpall 迁移全局对象，或在目标端预先创建角色并授予权限。
- 版本不兼容报错：优先选择与目标版本一致的备份工具/参数；必要时在测试环境验证再执行。
- 大对象丢失：导出时务必加 -b，恢复时使用 pg_restore 指定相同数据库。
- 字符集/换行/CSV 问题：统一 客户端/服务器编码，CSV 使用 HEADER、DELIMITER、QUOTE 一致，并处理好 NULL。
- 长时间运行与中断：对大库使用 自定义格式 便于断点续传与并行恢复；在 低峰时段 执行并设置维护窗口。