Debian 下 PostgreSQL 数据库迁移步骤
一 准备与环境检查
- 确认两端版本与兼容性：尽量保持源与目标 PostgreSQL 主版本一致；如存在差异，先评估兼容性再执行迁移。
- 安装软件：在两端安装同版本组件（Debian 常见为 postgresql 与 postgresql-contrib）。
- 规划维护窗口：为降低业务影响，建议在低峰期操作，必要时短暂停写。
- 权限与网络：确保用于迁移的数据库用户具备足够权限；如需远程传输或访问，按需配置 /etc/postgresql//main/postgresql.conf 与 pg_hba.conf（如设置 listen_addresses 与 pg_hba 规则）。
二 方法一 使用 pg_dump 与 pg_restore（同版本或兼容升级推荐）
- 源库备份（自定义格式，含大对象，详细输出）：
sudo -u postgres pg_dump -Fc -b -v -f /path/to/backup/mydatabase.dump mydatabase
- 传输备份到目标服务器：
scp /path/to/backup/mydatabase.dump user@target:/path/to/backup/
- 目标库准备：
sudo -u postgres createdb mydatabase
- 目标库恢复：
sudo -u postgres pg_restore -d mydatabase /path/to/backup/mydatabase.dump
- 验证：
sudo -u postgres psql -d mydatabase -c “SELECT COUNT(*) FROM my_table;”
- 说明：自定义格式 -F c 便于跨版本与选择性恢复；-b 包含大对象（如 BLOB/LO），-v 输出详细过程。
三 方法二 使用 SQL 脚本全量迁移（跨版本或异构到 PG 的通用方案）
- 源库导出为 SQL 脚本：
sudo -u postgres pg_dumpall -f backup_all.sql
- 目标库执行脚本：
sudo -u postgres psql -f backup_all.sql
- 适用场景：需要迁移全部数据库与全局对象（角色、表空间等），或希望以纯 SQL 方式审计与回放；大数据量时导入时间可能较长。
四 方法三 使用 pgloader 从其他数据源迁入 PostgreSQL
- 安装：
sudo apt-get install pgloader
- 编写迁移配置 my_migration.load（示例为从 MySQL 迁入）：
LOAD DATABASE
FROM mysql://user:password@host:port/source_db
INTO postgresql:///target_db
WITH include drop, create tables, create indexes, reset sequences, foreign keys
SET maintenance_work_mem to ‘128MB’, work_mem to ‘12MB’, search_path to ‘public’;
- 执行：
pgloader my_migration.load
- 适用场景：从 MySQL/Oracle/CSV 等迁移到 PostgreSQL，具备自动类型映射、并发加载与转换能力。
五 验证与常见问题
- 数据一致性校验：在目标库抽样核对表行数、关键聚合、主外键约束、触发器/视图执行结果；必要时对比校验和。
- 版本与兼容性：跨小版本通常可用 pg_dump/pg_restore 直接迁移；跨大版本或涉及系统目录变更时，先小范围演练并评估风险。
- 大对象与扩展：使用 -b 确保大对象迁移完整；如源库使用扩展（如 postgis、hstore），目标库需提前 CREATE EXTENSION。
- 性能与资源：大数据量时建议分批恢复、调大 work_mem/maintenance_work_mem，并在低峰期执行；必要时使用压缩与并行选项。
- 权限与连接：迁移后核对角色、权限、默认搜索路径与 owner；如启用远程访问，复核 pg_hba.conf 与防火墙策略。
