PostgreSQL 跨平台迁移

PostgreSQL 跨平台迁移是指在不同操作系统之间进行的数据迁移，通常用于从 Linux 迁移到 Windows、从 Windows 迁移到 Linux、从本地服务器迁移到云平台等场景。跨平台迁移比同平台迁移复杂，需要考虑文件系统差异、路径分隔符差异、权限模型差异等因素。本文将详细介绍 PostgreSQL 跨平台迁移的方法、步骤和最佳实践。

跨平台迁移的挑战

1. 文件系统差异

• 路径分隔符：Windows 使用反斜杠（\），Linux/macOS 使用正斜杠（/）
• 文件名大小写敏感性：Windows 不区分大小写，Linux/macOS 区分大小写
• 文件权限模型：Windows 使用 ACL，Linux/macOS 使用 Unix 权限模型
• 行结束符：Windows 使用 CRLF，Linux/macOS 使用 LF

2. 数据库配置差异

• 配置文件路径：不同操作系统的默认配置文件路径不同
• 服务管理方式：Windows 使用服务，Linux 使用 systemd 或 SysV 脚本
• 环境变量：不同操作系统的环境变量配置方式不同
• 网络配置：不同操作系统的网络配置方式不同

3. 应用兼容性差异

• 驱动程序差异：不同操作系统的 PostgreSQL 驱动程序可能存在差异
• 连接字符串格式：不同操作系统的连接字符串格式可能存在差异
• 应用依赖：应用程序可能依赖特定操作系统的库或功能

迁移前准备

环境检查

• 源环境检查：

  -- 检查源数据库版本
  SELECT version();
  
  -- 检查数据库大小
  SELECT datname, pg_database_size(datname) / 1024 / 1024 AS size_mb
  FROM pg_database;
  
  -- 检查数据库编码
  SELECT datname, encoding, pg_encoding_to_char(encoding) AS encoding_name
  FROM pg_database;
  
  -- 检查时区设置
  SHOW timezone;

• 目标环境准备：

  • 安装与源数据库版本兼容的 PostgreSQL
  • 配置适当的参数（postgresql.conf、pg_hba.conf等）
  • 确保目标环境的硬件和网络满足需求
  • 测试目标环境的性能

备份准备

• 全量备份：对源数据库进行全量备份，确保数据安全

  # 使用pg_dump进行逻辑备份
  pg_dumpall -h source_host -p 5432 -U postgres -F c -f /path/to/backup/all_databases.dump
  
  # 或备份单个数据库
  pg_dump -h source_host -p 5432 -U postgres -d dbname -F d -j 4 -f /path/to/backup/dbname_dir

• 验证备份完整性：

  # 验证pg_dump备份
  pg_restore -l /path/to/backup/all_databases.dump

应用兼容性测试

• 测试应用程序与目标平台的兼容性：

  • 在目标平台上安装应用程序
  • 测试应用程序的功能和性能
  • 检查应用程序日志，查找兼容性问题

• 检查驱动程序兼容性：

  • 确保应用程序使用的 PostgreSQL 驱动程序在目标平台上可用
  • 测试驱动程序的连接和功能

迁移方法

1. 逻辑备份恢复（pg_dump/pg_restore）

逻辑备份恢复是跨平台迁移的最常用方法，适用于所有 PostgreSQL 版本和所有操作系统。

适用场景

• 从 Linux 迁移到 Windows
• 从 Windows 迁移到 Linux
• 从本地服务器迁移到云平台
• 所有 PostgreSQL 版本之间的迁移

迁移步骤

1. 备份源数据库：

   # 在源平台上执行备份
   # Linux/macOS
   /usr/bin/pg_dumpall -h localhost -p 5432 -U postgres -F c -f /path/to/backup/all_databases.dump
   
   # Windows
   "C:\Program Files\PostgreSQL\14\bin\pg_dumpall.exe" -h localhost -p 5432 -U postgres -F c -f "C:\backup\all_databases.dump"

2. 将备份文件复制到目标平台：

   • 使用 scp、rsync 或其他文件传输工具
   • 注意文件权限和路径分隔符

3. 在目标平台上创建数据库实例：

   # Linux/macOS
   /usr/bin/initdb -D /path/to/data -E UTF8 --locale=en_US.utf8
   
   # Windows
   "C:\Program Files\PostgreSQL\14\bin\initdb.exe" -D "C:\Program Files\PostgreSQL\14\data" -E UTF8 --locale=en_US.utf8

4. 启动目标数据库服务：

   # Linux/macOS
   pg_ctl -D /path/to/data start
   
   # Windows
   net start postgresql-x64-14

5. 恢复数据库：

   # Linux/macOS
   /usr/bin/pg_restore -h localhost -p 5432 -U postgres -d postgres /path/to/backup/all_databases.dump
   
   # Windows
   "C:\Program Files\PostgreSQL\14\bin\pg_restore.exe" -h localhost -p 5432 -U postgres -d postgres "C:\backup\all_databases.dump"

6. 恢复后配置：

   • 重建扩展
   • 更新统计信息

   ANALYZE VERBOSE;

   • 调整配置文件参数

7. 验证迁移结果：

   SELECT version();
   SELECT datname FROM pg_database;

注意事项

• 使用源版本的 pg_dump 进行备份，使用目标版本的 pg_restore 进行恢复
• 确保备份文件在传输过程中没有损坏
• 注意路径分隔符和文件名大小写问题
• 恢复后需要调整配置文件参数以适应目标平台

2. 基于逻辑复制的迁移

逻辑复制是 PostgreSQL 10+ 版本支持的跨平台迁移方法，适用于需要最小化停机时间的场景。

适用场景

• 需要最小化停机时间的场景
• 从 Linux 迁移到 Windows
• 从 Windows 迁移到 Linux
• PostgreSQL 10+ 版本之间的迁移

迁移步骤

1. 配置源数据库的逻辑复制：

   -- 启用逻辑复制
   ALTER SYSTEM SET wal_level = logical;
   ALTER SYSTEM SET max_replication_slots = 10;
   ALTER SYSTEM SET max_wal_senders = 10;
   
   -- 重启源数据库
   SELECT pg_reload_conf();

2. 创建复制用户：

   CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl_password';

3. 配置 pg_hba.conf：

   # 允许复制用户从目标主机连接
   host replication repl target_host/32 md5
   host all all target_host/32 md5

4. 在目标平台上创建数据库实例：

   • 安装 PostgreSQL
   • 初始化数据库
   • 启动数据库服务

5. 在源数据库上创建发布：

   -- 创建发布
   CREATE PUBLICATION my_publication FOR ALL TABLES;
   
   -- 或创建选择性发布
   CREATE PUBLICATION my_publication FOR TABLE table1, table2;

6. 在目标数据库上创建订阅：

   -- 创建订阅
   CREATE SUBSCRIPTION my_subscription 
   CONNECTION 'host=source_host port=5432 dbname=dbname user=repl password=repl_password' 
   PUBLICATION my_publication;

7. 验证复制状态：

   -- 在源数据库上检查
   SELECT * FROM pg_stat_replication;
   
   -- 在目标数据库上检查
   SELECT * FROM pg_stat_subscription;

8. 切换应用连接：

   • 停止源数据库上的写入操作
   • 等待复制完成
   • 更新应用连接配置，指向目标数据库
   • 启动应用程序

注意事项

• 逻辑复制只支持 PostgreSQL 10+ 版本
• 源数据库和目标数据库的表结构必须兼容
• 逻辑复制不复制全局对象（如用户、角色、表空间等）
• 逻辑复制不复制 DDL 语句，需要手动同步

3. 使用第三方工具迁移

除了 PostgreSQL 内置的工具外，还可以使用第三方工具进行跨平台迁移。

适用场景

• 复杂的跨平台迁移场景
• 需要迁移大量数据的场景
• 需要自动化迁移的场景

常用第三方工具

工具名称	支持的迁移类型	特点
pgAdmin	逻辑备份恢复	图形化界面，易于使用
DBeaver	逻辑备份恢复	支持多种数据库，跨平台
Navicat	逻辑备份恢复、数据同步	图形化界面，功能丰富
AWS Database Migration Service (DMS)	逻辑复制	云原生，支持多种数据源
Azure Database Migration Service	逻辑复制	云原生，支持多种数据源
Oracle GoldenGate	逻辑复制	企业级，支持多种数据库

迁移步骤（以 pgAdmin 为例）

1. 在源平台上使用 pgAdmin 备份数据库：

   • 打开 pgAdmin，连接到源数据库
   • 右键点击数据库，选择 "备份"
   • 选择备份格式为 "自定义"
   • 点击 "备份" 按钮，生成备份文件

2. 将备份文件复制到目标平台：

   • 使用 scp、rsync 或其他文件传输工具
   • 注意文件权限和路径分隔符

3. 在目标平台上使用 pgAdmin 恢复数据库：

   • 打开 pgAdmin，连接到目标数据库
   • 右键点击 "数据库"，选择 "恢复"
   • 选择备份文件
   • 点击 "恢复" 按钮，开始恢复数据

4. 验证迁移结果：

   • 检查数据库中的表和数据
   • 测试应用程序连接

注意事项

• 确保使用的第三方工具版本与源数据库和目标数据库兼容
• 仔细阅读第三方工具的文档，了解其限制和注意事项
• 在测试环境中进行充分测试，确保迁移成功

迁移后验证

基本功能验证

• 检查数据库服务状态：

  # Linux/macOS
  pg_ctl -D /path/to/data status
  
  # Windows
  net start postgresql-x64-14

• 验证版本信息：

  SELECT version();

• 测试连接：

  # Linux/macOS
  psql -h localhost -p 5432 -U postgres -c "SELECT 1;"
  
  # Windows
  "C:\Program Files\PostgreSQL\14\bin\psql.exe" -h localhost -p 5432 -U postgres -c "SELECT 1;"

数据完整性验证

• 检查数据量：

  -- 检查数据库数量
  SELECT COUNT(*) FROM pg_database;
  
  -- 检查关键表的数据量
  SELECT COUNT(*) FROM important_table;

• 检查数据内容：

  -- 随机抽样检查数据
  SELECT * FROM important_table TABLESAMPLE SYSTEM(0.1);

• 检查约束和索引：

  -- 检查约束
  SELECT conrelid::regclass AS table_name, conname, contype
  FROM pg_constraint
  WHERE conrelid IN (SELECT oid FROM pg_class WHERE relnamespace = 'public'::regnamespace);
  
  -- 检查索引
  SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name, indisvalid
  FROM pg_index
  WHERE indrelid IN (SELECT oid FROM pg_class WHERE relnamespace = 'public'::regnamespace);

性能验证

• 运行基准测试：

  # Linux/macOS
  pgbench -i -s 10 testdb
  pgbench -c 10 -j 2 -t 1000 testdb
  
  # Windows
  "C:\Program Files\PostgreSQL\14\bin\pgbench.exe" -i -s 10 testdb
  "C:\Program Files\PostgreSQL\14\bin\pgbench.exe" -c 10 -j 2 -t 1000 testdb

• 检查查询性能：

  EXPLAIN ANALYZE SELECT * FROM important_table WHERE condition;

• 检查系统资源使用：

  # Linux/macOS
  top -p $(pgrep -o postgres)
  free -h
  iostat -xm 1
  
  # Windows
  tasklist /fi "imagename eq postgres.exe"
  wmic os get freephysicalmemory

应用兼容性验证

• 运行应用程序的功能测试：

  • 测试核心业务功能
  • 测试数据的增删改查操作
  • 测试事务处理和并发操作

• 检查应用程序日志：

  • 查找错误和警告信息
  • 检查性能问题

最佳实践

1. 制定详细的迁移计划

• 明确迁移目标和范围：确定需要迁移的数据库、表和其他对象
• 选择合适的迁移方法：根据实际情况选择逻辑备份恢复、基于复制的迁移或第三方工具迁移
• 制定详细的迁移步骤：包括迁移前准备、迁移执行和迁移后验证
• 制定回滚策略：确保迁移失败时能够快速回滚

2. 进行充分的测试

• 在测试环境中进行迁移测试：验证迁移方法的可行性和迁移时间
• 测试迁移后的应用兼容性：确保应用程序能够正常连接和使用迁移后的数据库
• 测试回滚策略：确保回滚策略的有效性
• 进行性能测试：验证迁移后的数据库性能是否满足要求

3. 注意跨平台差异

• 路径分隔符：使用正斜杠（/）作为路径分隔符，避免使用反斜杠（\）
• 文件名大小写：确保文件名的大小写在源平台和目标平台上一致
• 文件权限：在目标平台上正确设置文件权限
• 行结束符：确保配置文件和脚本使用正确的行结束符

4. 最小化停机时间

• 使用在线备份：减少源数据库的停机时间
• 使用基于复制的迁移：最小化迁移过程中的停机时间
• 选择合适的迁移时间：在业务低峰期进行迁移
• 并行执行迁移操作：使用-j参数并行执行pg_dump和pg_restore操作

5. 确保数据安全

• 进行完整备份：在迁移前对源数据库进行完整备份
• 验证备份的完整性：确保备份文件可用
• 加密敏感数据：在迁移过程中加密敏感数据
• 迁移后进行数据完整性检查：确保数据没有丢失或损坏

案例分析

案例1：从 Linux 迁移到 Windows

背景：需要将运行在 CentOS 7 上的 PostgreSQL 13 数据库迁移到 Windows Server 2019 上的 PostgreSQL 13，数据库大小约为 200GB，要求迁移时间不超过 8 小时。

迁移方案：

1. 使用 pg_dump 进行逻辑备份
2. 将备份文件复制到 Windows 服务器
3. 使用 pg_restore 恢复数据
4. 迁移后验证

迁移步骤：

1. 在 CentOS 7 上备份数据库：

   pg_dumpall -h localhost -p 5432 -U postgres -F d -j 4 -f /path/to/backup/all_databases_dir

2. 将备份文件复制到 Windows 服务器：

   rsync -avz /path/to/backup/all_databases_dir/ user@windows_server:/path/to/backup/

3. 在 Windows Server 2019 上恢复数据库：

   "C:\Program Files\PostgreSQL\13\bin\pg_restore.exe" -h localhost -p 5432 -U postgres -d postgres -j 4 "C:\backup\all_databases_dir"

4. 恢复后配置：

   -- 重建扩展
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   CREATE EXTENSION IF NOT EXISTS pgcrypto;
   
   -- 更新统计信息
   ANALYZE VERBOSE;

5. 验证迁移结果：

   SELECT version();
   SELECT COUNT(*) FROM important_table;

迁移结果：

• 迁移时间：6小时
• 停机时间：1小时（备份和恢复时间）
• 迁移后性能：查询响应时间与源数据库相当，写入性能略有提升
• 应用兼容性：所有应用程序正常运行

案例2：从 Windows 迁移到 Linux（基于逻辑复制）

背景：需要将运行在 Windows Server 2016 上的 PostgreSQL 12 数据库迁移到 Ubuntu 20.04 上的 PostgreSQL 14，要求停机时间不超过 30 分钟。

迁移方案：

1. 在 Ubuntu 20.04 上安装 PostgreSQL 14
2. 配置逻辑复制
3. 等待数据同步完成
4. 切换应用连接
5. 迁移后验证

迁移步骤：

1. 在 Windows Server 2016 上配置逻辑复制：

   ALTER SYSTEM SET wal_level = logical;
   ALTER SYSTEM SET max_replication_slots = 10;
   ALTER SYSTEM SET max_wal_senders = 10;
   SELECT pg_reload_conf();
   
   CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl_password';

2. 配置 pg_hba.conf：

   host replication repl ubuntu_server/32 md5
   host all all ubuntu_server/32 md5

3. 在 Ubuntu 20.04 上创建数据库实例：

   sudo apt-get install postgresql-14
   sudo -u postgres /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main
   sudo systemctl start postgresql@14-main

4. 在 Windows Server 2016 上创建发布：

   CREATE PUBLICATION my_publication FOR ALL TABLES;

5. 在 Ubuntu 20.04 上创建订阅：

   CREATE SUBSCRIPTION my_subscription 
   CONNECTION 'host=windows_server port=5432 dbname=dbname user=repl password=repl_password' 
   PUBLICATION my_publication;

6. 验证复制状态：

   SELECT * FROM pg_stat_subscription;

7. 切换应用连接：

   • 停止源数据库上的写入操作
   • 等待复制完成
   • 更新应用连接配置，指向 Ubuntu 服务器
   • 启动应用程序

迁移结果：

• 迁移时间：4小时（数据同步时间）
• 停机时间：15分钟（切换应用连接时间）
• 迁移后性能：查询响应时间减少30%，写入性能提升20%
• 应用兼容性：所有应用程序正常运行

总结

PostgreSQL 跨平台迁移是数据库运维中的重要任务，需要考虑文件系统差异、路径分隔符差异、权限模型差异等因素。逻辑备份恢复是跨平台迁移的最常用方法，适用于所有 PostgreSQL 版本和所有操作系统；基于复制的迁移适用于需要最小化停机时间的场景；第三方工具适用于复杂的跨平台迁移场景。

在迁移过程中，需要进行充分的准备和测试，确保数据的完整性和业务的连续性。迁移后，需要进行全面的验证，包括基本功能验证、数据完整性验证、性能验证和应用兼容性验证。通过遵循最佳实践和案例分析，可以帮助 DBA 顺利完成 PostgreSQL 跨平台迁移任务。

通过本文的介绍，希望能帮助 DBA 们掌握 PostgreSQL 跨平台迁移的方法和技巧，确保迁移工作的成功完成。