PostgreSQL在Ubuntu上如何迁移

Ubuntu 上 PostgreSQL 迁移实操指南

一、迁移方式总览与选择

• 逻辑迁移（推荐用于跨版本、跨机器、上云等）：使用 pg_dump/pg_restore 导出与导入，灵活、安全，适合大多数场景。
• 物理迁移（同版本、同平台整机搬迁）：直接拷贝数据目录或使用流复制做 热备→提升为新主，停机可缩短到秒级。
• 仅更换数据目录（同机磁盘扩容）：修改 data_directory 指向新挂载点，步骤简单、风险低。
  下文分别给出三种方式的详细步骤与要点。

二、方式一 逻辑迁移步骤（pg_dump/pg_restore）

• 准备
  • 确认源端可正常访问；记录必要的 角色/用户、权限、扩展、参数；建议先在测试环境演练。
• 导出
  • 单库（自定义归档，便于后续选择性恢复）：
    pg_dump -U your_user -h your_host -Fc your_db > your_db.dump
  • 全库（含角色与表空间，便于整实例迁移）：
    pg_dumpall -U your_user -f all.dump
• 传输
  • 使用 scp/rsync 将备份文件安全复制到目标机器。
• 目标端初始化与配置
  • 安装同版本 PostgreSQL（示例：Ubuntu 22.04 常见为 14）：
    sudo apt update && sudo apt install postgresql-14
  • 如需保持认证一致，复制并调整 postgresql.conf、pg_hba.conf，然后重启服务。
• 恢复
  • 单库：
    createdb -U your_user your_db
    pg_restore -U your_user -d your_db -Fc your_db.dump
  • 全库：
    psql -U your_user -f all.dump
• 验证
  • 校验关键表行数、约束、索引、扩展；应用端连通性回归；检查 角色权限 与 search_path。
• 适用场景
  • 跨版本升级/降级、跨操作系统/云平台、只迁移部分库或对象、对停机敏感但可接受一次性导出窗口。

三、方式二 物理迁移与最小停机方案（流复制热备→切换）

• 适用前提
  • 源与目标 PostgreSQL 小版本一致（避免版本差异导致 WAL/系统表不兼容）；网络带宽与延迟满足复制需求。
• 源端配置（编辑 /etc/postgresql//main/postgresql.conf）
  • wal_level = hot_standby
  • max_wal_senders = 3
  • max_replication_slots = 3
  • hot_standby = on
• 源端访问控制（编辑 /etc/postgresql//main/pg_hba.conf）
  • 允许复制用户从新服务器连接（示例）：
    host replication replica 新服务器IP/32 md5
• 目标端初始化与基础配置
  • 安装同版本软件；使用 pg_basebackup 从源端拉取基础备份：
    sudo -u postgres pg_basebackup -h 源端IP -U replica -D /var/lib/postgresql/14/main -P -v -R
    -R 会自动在目标端生成 standby.signal 与 primary_conninfo，便于直接进入备机回放模式。
• 启动与验证
  • 启动目标端并检查流复制状态：
    sudo systemctl start postgresql
    sudo -u postgres psql -c “SELECT * FROM pg_stat_replication;”
• 切换（计划内维护窗口）
  • 在目标端执行提升为新主：
    sudo -u postgres psql -c “SELECT pg_promote();”
  • 将应用连接切换到目标端；如需回切，可按需重建源端为备机。
• 适用场景
  • 同版本整机搬迁、对停机要求很高的业务（切换窗口可缩短至秒级）。

四、方式三 同机更换数据目录（磁盘扩容/路径迁移）

• 确认当前数据目录
  • sudo -u postgres psql -c “SHOW data_directory;”
  • 常见路径：/var/lib/postgresql/14/main
• 停止服务与拷贝数据
  • sudo systemctl stop postgresql
  • sudo rsync -av /var/lib/postgresql /mnt/volume_nyc1_01
  • 备份旧目录：sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.bak
• 修改配置并启动
  • 编辑 /etc/postgresql/14/main/postgresql.conf：
    data_directory = ‘/mnt/volume_nyc1_01/postgresql/14/main’
  • 启动并检查：
    sudo systemctl start postgresql
    sudo systemctl status postgresql
    sudo -u postgres psql -c “SHOW data_directory;”
• 适用场景
  • 同机更换存储、扩容数据盘、将数据迁移到更快的块存储。

五、常见问题与关键注意事项

• 版本与兼容性
  • 跨大版本不建议直接物理拷贝数据目录；优先使用 逻辑迁移 或按官方升级流程执行。
• 本地化与 Locale
  • 如遇错误：FATAL: database locale is incompatible with operating system，需在目标端生成缺失 locale 后重启，例如：
    sudo localedef -f UTF-8 -i zh_CN zh_CN.UTF-8
• 权限与所有权
  • 数据目录必须归 postgres:postgres，且权限为 700；使用 rsync -a 可保留属性。
• WAL 与增量迁移限制（若采用 DTS 等工具）
  • 进行增量/逻辑迁移时，源库需开启 wal_level=logical，并合理设置 WAL 保留时间（常见要求≥24小时/≥7天）；迁移期间避免大对象/超大行（>256 MB）等限制。
• 应用切换与回滚
  • 切换前在目标端完成一致性校验与冒烟测试；保留源端只读或备份一段时间，以便快速回滚。
• 扩展与依赖
  • 确认目标端安装相同 扩展（如 postgis、pgcrypto 等） 与相同版本库；必要时在恢复后执行 CREATE EXTENSION IF NOT EXISTS …。