Ubuntu中PostgreSQL数据库迁移方法

Ubuntu环境下PostgreSQL数据库迁移的常见方法

一、逻辑备份与恢复（适用于跨版本/跨平台迁移）

逻辑备份是最常用的迁移方式，通过pg_dump导出数据库结构和数据，再用pg_restore或psql导入目标服务器，支持跨PostgreSQL版本、跨操作系统迁移。

1. 备份源数据库：使用pg_dump生成逻辑备份文件（支持自定义格式.dump或SQL格式.sql）。
   • 备份整个数据库（自定义格式，支持压缩）：

     pg_dump -U postgres -h 源服务器IP -Fc 数据库名 >
          数据库名.dump
     

   • 备份单个表（SQL格式，可直接查看）：

     pg_dump -U postgres -h 源服务器IP -t 表名 数据库名 >
          表名.sql
     

   • 压缩备份（节省空间）：

     pg_dump -U postgres -h 源服务器IP -Fc 数据库名 | gzip >
          数据库名.dump.gz
     

2. 传输备份文件到目标服务器：使用scp等工具将备份文件复制到目标Ubuntu服务器。

   scp 数据库名.dump.gz 目标服务器IP:/home/用户名/
   

3. 恢复到目标数据库：
   • 解压备份文件（若为压缩格式）：

     gunzip 数据库名.dump.gz
     

   • 创建目标数据库（若不存在）：

     createdb -U postgres 数据库名
     

   • 恢复数据（自定义格式用pg_restore，SQL格式用psql）：

     pg_restore -U postgres -d 数据库名 数据库名.dump  # 自定义格式
     psql -U postgres -d 数据库名 -f 数据库名.sql    # SQL格式
     

4. 验证数据完整性：登录目标数据库，检查表结构、数据记录是否与源数据库一致。

   psql -U postgres -d 数据库名 -c "SELECT COUNT(*) FROM 表名;
       "
   

二、物理迁移数据目录（适用于同版本/同架构迁移）

若源服务器与目标服务器的PostgreSQL版本、操作系统架构一致（如均为Ubuntu 22.04 + PostgreSQL 14），可直接迁移数据目录（默认路径为/var/lib/postgresql/< 版本> /main），步骤更快捷。

1. 准备阶段：
   • 确认源数据库状态正常：

     sudo systemctl status postgresql
     

   • 查看当前数据目录：

     sudo -u postgres psql -c "SHOW data_directory;
         "
     

2. 停止数据库服务：

   sudo systemctl stop postgresql
   

3. 迁移数据目录：
   • 使用rsync保留文件权限（避免权限问题）：

     sudo rsync -av /var/lib/postgresql/ 源数据目录路径/ /目标数据目录路径/
     

   • 备份旧数据目录（可选）：

     sudo mv /var/lib/postgresql /var/lib/postgresql.bak
     

4. 修改配置文件：
   编辑/etc/postgresql/< 版本> /main/postgresql.conf，更新data_directory参数指向新路径：

   data_directory = '/目标数据目录路径/postgresql/<
       版本>
       /main'
   

5. 启动服务并验证：

   sudo systemctl start postgresql
   sudo -u postgres psql -c "SHOW data_directory;
       "  # 确认路径已变更
   

三、热备迁移（最小化停机时间，适用于生产环境）

若需实现几乎零停机的迁移（停机时间通常在几秒内），可使用PostgreSQL的热备功能（基于WAL日志同步），适合大型数据库或对可用性要求高的场景。

1. 配置源服务器为热备模式：
   • 编辑源服务器的postgresql.conf，开启WAL日志：

     wal_level = hot_standby
     max_wal_senders = 3
     max_replication_slots = 3
     

   • 编辑pg_hba.conf，允许目标服务器连接：

     host replication replicator 源服务器IP/32 md5
     

   • 重启源服务器使配置生效：

     sudo systemctl restart postgresql
     

2. 创建热备节点（目标服务器）：
   • 在目标服务器上安装相同版本的PostgreSQL，初始化数据目录但不启动服务。
   • 编辑目标服务器的postgresql.conf，设置为热备模式：

     hot_standby = on
     

   • 使用pg_basebackup从源服务器同步数据：

     pg_basebackup -h 源服务器IP -U replicator -D /var/lib/postgresql/<
         版本>
         /main -P -R
     

3. 切换主从角色：
   • 当数据同步完成后，停止源服务器服务，提升目标服务器为主节点：

     # 在源服务器上执行
     pg_ctl stop -D /var/lib/postgresql/<
         版本>
         /main
     
     # 在目标服务器上执行（提升为主节点）
     pg_ctl promote -D /var/lib/postgresql/<
         版本>
         /main
     

4. 验证迁移：
   登录目标服务器，检查数据是否与源服务器一致，确认应用可正常连接。

四、使用pgloader迁移（适用于异构数据库迁移）

若需从MySQL、Oracle等其他数据库迁移到PostgreSQL，可使用pgloader工具，支持自动转换数据类型、处理约束和索引。

1. 安装pgloader：

   sudo apt update
   sudo apt install pgloader
   

2. 创建迁移配置文件（如migration.load）：

   LOAD DATABASE
       FROM mysql://用户名:密码@源服务器IP/数据库名
       INTO postgresql://用户名:密码@目标服务器IP/数据库名
       WITH include no drop, create tables, create indexes, reset sequences
   

3. 执行迁移：

   pgloader migration.load
   

   pgloader会自动处理表结构转换、数据同步及约束迁移。

迁移注意事项

• 备份优先：迁移前务必对源数据库进行完整备份，避免数据丢失。
• 版本兼容：跨版本迁移时，建议先升级源数据库到目标版本（或使用兼容的逻辑备份格式）。
• 权限检查：确保目标服务器的PostgreSQL用户具有足够的权限（如创建数据库、写入数据）。
• 停机时间评估：根据数据库大小选择合适的迁移方式（逻辑备份适合大型数据库，热备适合生产环境）。
• 数据验证：迁移后务必检查数据完整性（如记录数、约束有效性）。

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