PostgreSQL 跨版本迁移

PostgreSQL 跨版本迁移是指在不同 PostgreSQL 版本之间进行的数据迁移，通常用于升级到新版本以获得更好的性能、安全性和新功能。跨版本迁移比同版本迁移复杂，需要考虑版本兼容性、数据格式变化和应用兼容性等因素。本文将详细介绍 PostgreSQL 跨版本迁移的方法、步骤和最佳实践。

迁移前准备

版本兼容性检查

• 检查主要版本差异：查阅 PostgreSQL 版本发布说明，了解源版本和目标版本之间的主要差异

• 检查废弃功能：确认源数据库中是否使用了目标版本中废弃或移除的功能

  -- 检查废弃功能的使用情况
  -- 例如，检查是否使用了旧版的索引类型
  SELECT relname, indexrelid::regclass AS index_name, amname
  FROM pg_index
  JOIN pg_class ON pg_index.indexrelid = pg_class.oid
  JOIN pg_am ON pg_class.relam = pg_am.oid
  WHERE amname IN ('btree', 'hash', 'gist', 'gin', 'spgist', 'brin');

• 检查扩展兼容性：确认源数据库中使用的扩展在目标版本中是否可用

  -- 检查已安装的扩展
  SELECT extname, extversion
  FROM pg_extension;

环境准备

• 源环境检查：

  -- 检查源数据库版本
  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;

• 目标环境准备：

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

备份准备

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

  # 使用pg_basebackup进行物理备份
  pg_basebackup -h source_host -p 5432 -U repl -D /path/to/backup -F t -z -P
  
  # 或使用pg_dump进行逻辑备份
  pg_dumpall -h source_host -p 5432 -U postgres -F c -f /path/to/backup/all_databases.dump

• 增量备份：确保WAL日志正常归档，以便进行增量恢复

  -- 检查WAL归档状态
  SHOW archive_mode;
  SHOW archive_command;

• 配置备份验证：验证备份的完整性和可用性

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

应用兼容性测试

• 测试应用程序与目标版本的兼容性：

  • 在测试环境中安装目标版本的 PostgreSQL
  • 恢复备份数据到测试环境
  • 运行应用程序的功能测试和性能测试
  • 检查应用程序日志，查找兼容性问题

• 检查SQL语法兼容性：

  -- 检查是否使用了过时的SQL语法
  -- 例如，检查是否使用了隐式类型转换
  SELECT * FROM pg_stat_statements WHERE query LIKE '%::%' OR query LIKE '%CAST(%';

迁移方法

1. pg_upgrade（物理迁移）

pg_upgrade 是 PostgreSQL 官方提供的跨版本升级工具，支持快速升级，适用于相同架构和操作系统的环境。

适用场景

• 相同架构和操作系统的环境
• 数据量较大（TB级别）的数据库
• 要求迁移时间短的场景
• PostgreSQL 9.0+ 版本之间的升级

迁移步骤

1. 安装目标版本的 PostgreSQL：

   • 安装与源版本兼容的目标版本
   • 确保目标版本的二进制文件路径已加入环境变量

2. 准备目标数据库：

   # 初始化目标数据库
   initdb -D /path/to/target/data -E UTF8 --locale=en_US.utf8
   
   # 复制源数据库的配置文件到目标数据库
   cp /path/to/source/data/postgresql.conf /path/to/target/data/
   cp /path/to/source/data/pg_hba.conf /path/to/target/data/
   cp /path/to/source/data/pg_ident.conf /path/to/target/data/
   
   # 调整目标数据库的配置
   # 例如，修改端口号避免冲突
   sed -i 's/^port = 5432/port = 5433/' /path/to/target/data/postgresql.conf

3. 运行pg_upgrade检查：

   pg_upgrade -b /path/to/source/bin -B /path/to/target/bin -d /path/to/source/data -D /path/to/target/data -c

4. 停止源数据库服务：

   pg_ctl -D /path/to/source/data stop -m fast

5. 执行pg_upgrade：

   # 并行升级（推荐）
   pg_upgrade -b /path/to/source/bin -B /path/to/target/bin -d /path/to/source/data -D /path/to/target/data -j 4

6. 升级后配置：

   • 重建扩展：

     ./analyze_new_cluster.sh

   • 更新统计信息：

     ANALYZE VERBOSE;

   • 重建缺失的索引（如果有）

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

   pg_ctl -D /path/to/target/data start

8. 验证迁移结果：

   SELECT version();
   SELECT datname FROM pg_database;

9. 清理旧数据库（可选）：

   ./delete_old_cluster.sh

注意事项

• 源数据库和目标数据库必须使用相同的架构（如x86_64）
• 源数据库和目标数据库必须使用相同的操作系统或兼容的操作系统
• 迁移过程中源数据库需要停机
• 支持从 PostgreSQL 9.0+ 版本升级到更高版本
• 对于大版本跨越多代的升级（如从9.6直接升级到14），建议分步升级

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

逻辑备份恢复是通过备份和恢复的方式进行跨版本迁移，适用于不同架构或操作系统的环境。

适用场景

• 不同架构的环境迁移（如x86_64到arm）
• 不同操作系统的环境迁移（如Linux到Windows）
• 需要选择性迁移数据的场景
• 源数据库无法停机的场景（可使用在线备份）
• 所有PostgreSQL版本之间的迁移

迁移步骤

1. 备份源数据库：

   # 使用源版本的pg_dumpall备份所有数据库
   /path/to/source/bin/pg_dumpall -h source_host -p 5432 -U postgres -F c -f /path/to/backup/all_databases.dump
   
   # 或备份单个数据库
   /path/to/source/bin/pg_dump -h source_host -p 5432 -U postgres -d dbname -F d -j 4 -f /path/to/backup/dbname_dir

2. 创建目标数据库：

   CREATE DATABASE dbname;

3. 恢复数据库：

   # 使用目标版本的pg_restore恢复所有数据库
   /path/to/target/bin/pg_restore -h target_host -p 5432 -U postgres -d postgres /path/to/backup/all_databases.dump
   
   # 或恢复单个数据库
   /path/to/target/bin/pg_restore -h target_host -p 5432 -U postgres -d dbname -j 4 /path/to/backup/dbname_dir

4. 恢复后配置：

   • 重建扩展：

     CREATE EXTENSION IF NOT EXISTS extension_name;

   • 更新统计信息：

     ANALYZE VERBOSE;

   • 重建索引（如果需要）

5. 验证迁移结果：

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

注意事项

• 逻辑迁移的速度较慢，适用于数据量较小的数据库
• 迁移过程中源数据库可以继续运行（使用在线备份）
• 恢复后需要重建扩展、索引和更新统计信息
• 支持所有PostgreSQL版本之间的迁移
• 可以跳过某些不需要的对象（如表、索引、函数等）

3. 基于复制的迁移

基于复制的迁移是使用PostgreSQL的复制功能进行跨版本迁移，适用于需要最小化停机时间的场景。

适用场景

• 需要最小化停机时间的场景
• 从单机迁移到主从架构
• 硬件升级或更换
• PostgreSQL 10+ 版本之间的升级（支持逻辑复制）

迁移步骤（流复制）

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

   -- 配置wal_level
   ALTER SYSTEM SET wal_level = replica;
   
   -- 配置max_wal_senders
   ALTER SYSTEM SET max_wal_senders = 10;
   
   -- 配置wal_keep_size（PostgreSQL 13+，之前版本使用wal_keep_segments）
   ALTER SYSTEM SET wal_keep_size = 1GB;
   
   -- 重启源数据库
   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

4. 初始化目标数据库：

   # 使用pg_basebackup初始化目标数据库
   pg_basebackup -h source_host -p 5432 -U repl -D /path/to/target/data -F p -X stream -P -R

5. 升级目标数据库：

   • 停止目标数据库服务
   • 使用pg_upgrade升级目标数据库
   • 启动目标数据库服务

6. 配置逻辑复制（可选，适用于跨大版本迁移）：

   -- 在源数据库上创建发布
   CREATE PUBLICATION my_publication FOR ALL TABLES;
   
   -- 在目标数据库上创建订阅
   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. 切换主备角色：

   • 停止源数据库服务
   • 提升目标数据库为主库
   • 更新应用连接配置

注意事项

• 流复制只支持小版本升级（如从13.4升级到13.5）
• 跨大版本迁移需要使用逻辑复制
• 逻辑复制需要源数据库和目标数据库的表结构兼容
• 切换过程中会有短暂的停机时间
• 适用于PostgreSQL 10+ 版本

迁移后验证

基本功能验证

• 检查数据库服务状态：

  pg_ctl -D /path/to/target/data status

• 验证版本信息：

  SELECT version();

• 测试连接：

  psql -h target_host -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);

性能验证

• 运行基准测试：

  pgbench -i -s 10 testdb
  pgbench -c 10 -j 2 -t 1000 testdb

• 检查查询性能：

  EXPLAIN ANALYZE SELECT * FROM important_table WHERE condition;

• 检查系统资源使用：

  top -p $(pgrep -o postgres)
  free -h
  iostat -xm 1

应用兼容性验证

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

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

• 检查应用程序日志：

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

最佳实践

1. 制定详细的迁移计划

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

2. 进行充分的测试

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

3. 最小化停机时间

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

4. 确保数据安全

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

5. 监控迁移过程

• 监控源数据库的性能：确保迁移过程中源数据库的性能不受影响
• 监控迁移进度：实时监控迁移的进度和状态
• 记录迁移日志：详细记录迁移过程中的每一步操作和结果
• 及时处理迁移过程中的问题：确保迁移能够顺利完成

案例分析

案例1：pg_upgrade 跨版本迁移（从9.6到14）

背景：需要将PostgreSQL 9.6数据库升级到PostgreSQL 14，数据库大小约为500GB，要求迁移时间不超过4小时。

迁移方案：

1. 使用pg_upgrade进行物理升级
2. 提前准备目标环境
3. 执行迁移前检查
4. 执行迁移操作
5. 迁移后验证

迁移步骤：

1. 安装PostgreSQL 14：

   yum install postgresql14-server

2. 准备目标数据库：

   /usr/pgsql-14/bin/initdb -D /var/lib/pgsql/14/data -E UTF8 --locale=en_US.utf8
   cp /var/lib/pgsql/9.6/data/postgresql.conf /var/lib/pgsql/14/data/
   cp /var/lib/pgsql/9.6/data/pg_hba.conf /var/lib/pgsql/14/data/
   sed -i 's/^port = 5432/port = 5433/' /var/lib/pgsql/14/data/postgresql.conf

3. 运行pg_upgrade检查：

   /usr/pgsql-14/bin/pg_upgrade -b /usr/pgsql-9.6/bin -B /usr/pgsql-14/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/14/data -c

4. 停止PostgreSQL 9.6服务：

   systemctl stop postgresql-9.6

5. 执行pg_upgrade：

   /usr/pgsql-14/bin/pg_upgrade -b /usr/pgsql-9.6/bin -B /usr/pgsql-14/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/14/data -j 4

6. 升级后配置：

   ./analyze_new_cluster.sh

7. 启动PostgreSQL 14服务：

   systemctl start postgresql-14

8. 验证迁移结果：

   SELECT version();
   SELECT datname FROM pg_database;

迁移结果：

• 迁移时间：2小时30分钟
• 停机时间：45分钟
• 迁移后性能提升：查询响应时间减少50%，写入性能提升30%
• 应用兼容性：所有应用程序正常运行

案例2：逻辑备份恢复跨版本迁移（从10到15）

背景：需要将PostgreSQL 10数据库迁移到PostgreSQL 15，源数据库运行在x86_64架构的Linux服务器上，目标数据库运行在arm架构的Linux服务器上，要求迁移过程中源数据库可以继续运行。

迁移方案：

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

迁移步骤：

1. 备份源数据库：

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

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

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

3. 恢复数据到目标数据库：

   pg_restore -h target_host -p 5432 -U postgres -d postgres -j 4 /path/to/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小时
• 停机时间：0（源数据库继续运行）
• 迁移后性能提升：查询响应时间减少40%，写入性能提升20%
• 应用兼容性：所有应用程序正常运行

总结

PostgreSQL 跨版本迁移是数据库运维中的重要任务，选择合适的迁移方法和制定完善的迁移计划是确保迁移成功的关键。pg_upgrade 适用于相同架构和操作系统的环境，迁移速度快；逻辑备份恢复适用于不同架构和操作系统的环境，灵活性高；基于复制的迁移适用于需要最小化停机时间的场景。

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

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