Debian与PostgreSQL数据迁移方法

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）清理
  • rm /path/mydb.dump
• 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

三 方法选择建议

四 关键注意事项

• 版本与兼容性：尽量保持 源/目标 PostgreSQL 主版本一致；跨大版本时先在测试环境演练，必要时调整数据类型与索引策略。
• 配置与连通性：按需开启远程访问，编辑 /etc/postgresql//main/postgresql.conf 与 pg_hba.conf，例如 listen_addresses、认证方式等，并重启服务。
• 权限与安全：执行迁移的数据库用户需具备相应权限；备份文件与传输链路注意 权限最小化与加密。
• 大对象与特殊类型：使用 -b 保留大对象；检查 序列、默认值、外键、触发器、视图 依赖顺序。
• 校验与回滚：迁移后做 行数、主键/唯一性、抽样校验；保留回滚方案（源库只读或延迟切换窗口）。

五 常见问题与排错

• 角色不存在或权限不足：用 pg_dumpall 迁移全局对象，或在目标端预先创建角色并授予权限。
• 版本不兼容报错：优先选择与目标版本一致的备份工具/参数；必要时在测试环境验证再执行。
• 大对象丢失：导出时务必加 -b，恢复时使用 pg_restore 指定相同数据库。
• 字符集/换行/CSV 问题：统一 客户端/服务器编码，CSV 使用 HEADER、DELIMITER、QUOTE 一致，并处理好 NULL。
• 长时间运行与中断：对大库使用 自定义格式 便于断点续传与并行恢复；在 低峰时段 执行并设置维护窗口。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！