PostgreSQL在Ubuntu上如何迁移

Ubuntu 上 PostgreSQL 迁移实操指南

一、迁移方式总览与选择

二、方式一逻辑迁移步骤（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 …。

最新问答