挨踢而思

Menu

Administrator
Published on 2025-10-29 / 1 Visits
0

PostgreSQL数据库迁移

#AI

PostgreSQL数据库迁移是将数据从一个PostgreSQL实例（或其他数据库）转移到另一个实例的过程，需根据场景（如版本、平台、源数据库类型）选择合适的方案。以下是详细的迁移方法、步骤及注意事项：

一、迁移场景分类

迁移场景主要分为两类，核心区别在于灵活性和适用范围：

类型适用场景优点缺点
物理迁移同版本、同架构（如Linux→Linux）速度快（直接复制文件）限制多（版本/平台/配置需一致）
逻辑迁移跨版本、跨平台（如Linux→Windows）灵活（兼容不同环境）速度较慢（需解析/重建数据）

二、PostgreSQL之间的迁移（重点）

1. 逻辑迁移（推荐，兼容性强）

通过导出数据结构和内容（SQL或二进制格式），再导入目标库，适用于大多数场景（尤其是跨版本/跨平台）。

核心工具：pg_dump（导出）和pg_restore（导入），PostgreSQL自带，支持细粒度控制（单表、单库、全集群）。

步骤：单库迁移

  1. 准备目标环境

    • 在目标服务器安装与源库兼容的PostgreSQL（建议高版本兼容低版本，避免低版本导入高版本数据）。
    • 配置postgresql.conf（如max_connections、shared_buffers）和pg_hba.conf（允许源/目标服务器连接）。
    • 创建目标数据库（名称建议与源库一致）： 
      CREATE DATABASE 目标库名;

  2. 导出源库数据
    在源服务器执行pg_dump，推荐使用自定义格式（压缩率高，支持并行恢复）：

    # 导出单个数据库（自定义格式，-Fc；-f 指定输出文件）
pg_dump -U 源库用户名 -d 源库名 -Fc -f /tmp/backup.dump

# 可选：并行导出（-j 指定并行进程数，适合大库）
pg_dump -U 源库用户名 -d 源库名 -Fc -j 4 -f /tmp/backup.dump
    • 若需迁移全集群（含角色、表空间），用pg_dumpall： 
      pg_dumpall -U 超级用户 -f /tmp/full_backup.sql  # 导出为SQL文本

  3. 传输备份文件
    将导出的backup.dump或full_backup.sql传输到目标服务器（如用scp）：

    scp /tmp/backup.dump 目标服务器用户名@目标服务器IP:/tmp/

  4. 导入目标库
    在目标服务器用pg_restore导入（自定义格式）：

    # 导入到目标库（-d 指定目标库；-j 并行恢复，需与导出时一致）
pg_restore -U 目标库用户名 -d 目标库名 -j 4 /tmp/backup.dump

# 若导出为SQL文本（如pg_dumpall的结果），直接执行SQL：
psql -U 超级用户 -d 目标库名 -f /tmp/full_backup.sql
步骤：跨版本迁移（如9.6→16）
  • 原则：用低版本pg_dump导出，高版本pg_restore导入（高版本工具可能不兼容低版本数据文件）。
  • 例如：从9.6迁移到16，需在9.6环境执行pg_dump，然后在16环境用pg_restore导入。

2. 物理迁移（快速，限制多）

直接复制PostgreSQL的数据目录（$PGDATA），适合同版本、同操作系统架构（如Linux x86_64→Linux x86_64）。

步骤：

  1. 停止源库（确保数据一致性）：

    pg_ctl -D $PGDATA stop

  2. 复制数据目录
    将源库的$PGDATA（默认/var/lib/postgresql/版本号/main）复制到目标服务器：

    # 打包源数据目录
tar -czvf pgdata.tar.gz $PGDATA
# 传输到目标服务器
scp pgdata.tar.gz 目标服务器:/tmp/
# 在目标服务器解压（确保目录权限为postgres用户）
sudo tar -xzvf /tmp/pgdata.tar.gz -C /var/lib/postgresql/版本号/
sudo chown -R postgres:postgres /var/lib/postgresql/版本号/main

  3. 启动目标库：

    pg_ctl -D $PGDATA start

限制：

  • 源库和目标库版本必须完全一致（如14.5→14.5），跨小版本（如14.4→14.5）可能可行，但不推荐。
  • 操作系统架构需一致（如不能从ARM迁移到x86）。
  • 配置文件（postgresql.conf）中的路径（如log_directory）需适配目标服务器。

三、从其他数据库迁移到PostgreSQL

若源数据库是MySQL、Oracle等，需处理语法和数据类型差异，常用工具如下：

源数据库推荐工具/方法核心注意点
MySQLpgLoader（开源工具）自动映射数据类型（如MySQL的INT→PostgreSQL的INTEGER），处理索引/约束。
Oracleora2pg（专用迁移工具）转换PL/SQL到PL/pgSQL，处理LOB大对象。
SQL Servermssql2pgsql（脚本工具）或DMS服务处理日期类型（如datetime→timestamp）。

示例：MySQL→PostgreSQL（pgLoader）

  1. 安装pgLoader：sudo apt install pgloader（Ubuntu）。
  2. 编写迁移脚本（load.mysql）： 
    LOAD DATABASE
  FROM mysql://mysql用户名:密码@mysql主机:3306/源库名
  INTO postgresql://postgres用户名:密码@pg主机:5432/目标库名
WITH include drop, create tables, create indexes, reset sequences
SET work_mem to '16MB', maintenance_work_mem to '512MB';
  3. 执行迁移：pgloader load.mysql。

四、迁移后验证

迁移完成后必须验证数据完整性，避免遗漏或错误：

  1. 基础校验：

    • 对比源库和目标库的表数量、记录数（如SELECT count(*) FROM 表名;）。
    • 检查索引、约束（主键、外键）是否存在（\d 表名在psql中查看）。

  2. 权限校验：

    • 确认角色（用户）和权限是否同步（\du查看角色，\dp 表名查看表权限）。

  3. 应用测试：

    • 连接目标库运行应用，验证读写、事务、查询是否正常。

五、注意事项

  1. 备份优先：迁移前务必备份源库（如pg_dump全量备份），防止操作失误。
  2. 停机时间：逻辑迁移耗时较长（尤其大库），建议在业务低峰执行；物理迁移需停机（时间取决于数据量）。
  3. 大对象（LOB）：pg_dump默认处理LOB，但需确保目标库max_locks_per_transaction等参数足够大。
  4. 表空间：若源库使用自定义表空间，需在目标库创建相同路径的目录（如/data/tbs1），并设置权限。

通过以上方法，可根据实际场景选择合适的迁移方案，确保数据安全、完整地迁移到目标库。

docker 修改默认网段