Debian PostgreSQL数据库迁移方法 - 问答

Debian 上 PostgreSQL 数据库迁移方法

一常用迁移方法概览

逻辑迁移：使用 pg_dump/pg_restore（单库、跨版本、跨平台通用，推荐优先）。
全实例迁移：使用 pg_dumpall + psql（迁移所有数据库与全局对象，如角色、表空间）。
零停机或近零停机：使用 物理复制/流复制（基础备份 + WAL 流复制，适合大库与高可用场景）。
异构迁移：使用 pgloader（从 MySQL/Oracle/CSV 等导入到 PostgreSQL）。
文件导入：使用 COPY（批量导入 CSV/TSV 等平面文件）。

二逻辑迁移步骤 pg_dump 与 pg_restore

源库备份（自定义格式，含大对象，适合大库）：
- 命令：sudo -u postgres pg_dump -Fc -b -v -f /path/to/mydb.dump mydatabase
- 说明：-Fc 自定义归档格式；-b 包含大对象；-v 显示进度。
传输到目标服务器：
- 命令：scp /path/to/mydb.dump user@target:/path/to/
目标库准备与恢复：
- 安装 PostgreSQL（如未安装）：sudo apt update && sudo apt install postgresql postgresql-contrib
- 创建目标库：sudo -u postgres createdb mydatabase
- 恢复：sudo -u postgres pg_restore -d mydatabase /path/to/mydb.dump
验证：
- 连接并检查：sudo -u postgres psql -d mydatabase -c "SELECT COUNT(*) FROM my_table;"
适用场景：跨小中大型库、跨版本升级（遵循兼容性）、跨主机/云迁移。

三全实例迁移 pg_dumpall 与 psql

源库全量导出（含角色、表空间、全局对象）：
- 命令：sudo -u postgres pg_dumpall -U postgres -f /path/to/all.sql
目标库导入：
- 安装并启动 PostgreSQL：sudo apt update && sudo apt install postgresql postgresql-contrib && sudo systemctl start postgresql
- 导入：sudo -u postgres psql -U postgres -f /path/to/all.sql
适用场景：需要一次性迁移所有数据库及全局对象；注意目标端需预先不存在同名对象以避免冲突。

四零停机迁移物理复制与基础备份

源库配置（编辑 postgresql.conf）：
- wal_level = replica
- max_wal_senders = 10
- wal_keep_segments = 64（或根据版本使用 wal_keep_size）
- 重启：sudo systemctl restart postgresql
创建复制用户：
- CREATE USER replicator WITH REPLICATION PASSWORD 'password' LOGIN;
目标库准备（编辑 postgresql.conf）：
- hot_standby = on
- 重启：sudo systemctl restart postgresql
基础备份与 WAL 传输（示例思路）：
- 在目标端执行基础备份（如使用 pg_basebackup），并配置 recovery.conf（或 standby.signal + primary_conninfo）指向源库；
- 启动目标库进入恢复模式，追平 WAL 后提升为新主库（promote）。
适用场景：大库、要求最小化停机或需要持续同步后再切换的生产环境。

五异构与文件导入工具

pgloader（从 MySQL/Oracle/CSV 等迁移到 PostgreSQL）：

安装：sudo apt-get install pgloader

示例配置（MySQL → PostgreSQL）：

LOAD DATABASE
  FROM mysql://user:pass@host:port/src_db
  INTO postgresql:///tgt_db
  WITH include drop, create tables, create indexes, reset sequences, foreign keys
  SET maintenance_work_mem to '128MB', work_mem to '12MB';

执行：pgloader my_migration.load

COPY（批量导入 CSV/TSV）：
- 示例：
```
COPY my_table(name, age) FROM '/path/to/data.csv' WITH CSV HEADER;
```
- 或管道导入：cat data.csv | psql -U user -d dbname -c "\copy my_table FROM STDIN WITH CSV HEADER"
适用场景：从其他数据库迁移、一次性批量导入平面文件。

六注意事项与最佳实践

版本兼容：尽量保持源与目标 PostgreSQL 主版本一致；跨版本迁移先小范围演练，必要时调整数据类型与索引策略。
一致性：
- 逻辑迁移可在低峰期进行；对强一致场景，考虑短暂只读或采用物理复制实现近零停机。
- 使用 -Fc/-b（pg_dump）保留大对象与依赖关系，恢复更可靠。
配置与网络：按需调整 postgresql.conf（如 listen_addresses）与 pg_hba.conf（认证与来源网段），必要时临时开放安全网段。
验证与回滚：迁移后执行数据量与关键业务校验，保留源库备份直至新库稳定运行；准备回滚方案。
性能与资源：大库迁移时控制并发与内存参数（如 maintenance_work_mem、work_mem），分批次导入以减少长事务与锁竞争。

0 赞

0 踩