PostgreSQL 同版本迁移

PostgreSQL 同版本迁移是指在相同 PostgreSQL 版本之间进行的数据迁移，通常用于硬件升级、存储更换、操作系统迁移或架构调整等场景。同版本迁移相对简单，但仍需制定完善的迁移计划和回滚策略，确保数据的完整性和业务的连续性。本文将详细介绍 PostgreSQL 同版本迁移的方法、步骤和最佳实践。

迁移前准备

迁移评估

• 确定迁移目标：明确迁移的原因和目标，如硬件升级、存储更换、架构调整等
• 评估迁移范围：确定需要迁移的数据库、表、索引和其他对象
• 评估数据量：估算迁移的数据量，确定迁移时间窗口
• 评估业务影响：分析迁移对业务的影响，确定最佳迁移时间

环境准备

• 源环境检查：

  -- 检查源数据库版本
  SELECT version();
  
  -- 检查数据库大小
  SELECT datname, pg_database_size(datname) / 1024 / 1024 AS size_mb
  FROM pg_database;
  
  -- 检查表空间
  SELECT spcname, pg_tablespace_size(spcname) / 1024 / 1024 AS size_mb
  FROM pg_tablespace;

• 目标环境准备：

  • 安装与源数据库相同版本的 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_basebackup备份
  pg_restore -l /path/to/backup/all_databases.dump
  
  # 或使用pg_verifybackup工具（PostgreSQL 12+）
  pg_verifybackup /path/to/backup

迁移方法

1. 物理迁移（文件复制）

物理迁移是直接复制数据库文件的迁移方式，适用于相同架构和操作系统的环境。

迁移步骤

1. 停止源数据库服务：

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

2. 复制数据文件：

   # 使用rsync复制数据文件（推荐）
   rsync -avz /path/to/source/data/ user@target_host:/path/to/target/data/
   
   # 或使用scp复制压缩文件
   tar -czf data.tar.gz /path/to/source/data/
   scp data.tar.gz user@target_host:/path/to/target/
   ssh user@target_host "tar -xzf /path/to/target/data.tar.gz -C /path/to/target/"

3. 复制配置文件：

   rsync -avz /path/to/source/conf/ user@target_host:/path/to/target/conf/

4. 调整配置文件：

   • 根据目标环境调整postgresql.conf中的参数（如listen_addresses、port等）
   • 调整pg_hba.conf中的访问控制规则

5. 设置文件权限：

   chown -R postgres:postgres /path/to/target/data
   chmod 0700 /path/to/target/data

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

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

7. 验证迁移结果：

   SELECT version();
   SELECT datname FROM pg_database;

适用场景

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

注意事项

• 源数据库和目标数据库必须是相同的PostgreSQL版本
• 源数据库和目标数据库的架构必须兼容（如x86_64、arm等）
• 迁移过程中源数据库需要停机
• 适用于相同操作系统或兼容的操作系统（如CentOS 7到CentOS 8）

2. 逻辑迁移（备份恢复）

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

迁移步骤

1. 备份源数据库：

   # 备份所有数据库
   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 c -f /path/to/backup/dbname.dump
   
   # 并行备份（PostgreSQL 9.3+）
   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 -h target_host -p 5432 -U postgres -d postgres /path/to/backup/all_databases.dump
   
   # 恢复单个数据库
   pg_restore -h target_host -p 5432 -U postgres -d dbname /path/to/backup/dbname.dump
   
   # 并行恢复
   pg_restore -h target_host -p 5432 -U postgres -d dbname -j 4 /path/to/backup/dbname_dir

4. 恢复后配置：

   • 重建索引（如果需要）
   • 更新统计信息

   ANALYZE VERBOSE;

5. 验证迁移结果：

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

适用场景

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

注意事项

• 逻辑迁移的速度较慢，适用于数据量较小的数据库
• 迁移过程中源数据库可以继续运行（使用在线备份）
• 恢复后需要重建索引和更新统计信息
• 适用于所有PostgreSQL版本

3. 基于复制的迁移

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

迁移步骤

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_ctl -D /path/to/target/data start

6. 验证复制状态：

   -- 在源数据库上检查
   SELECT * FROM pg_stat_replication;
   
   -- 在目标数据库上检查
   SELECT pg_is_in_recovery();
   SELECT * FROM pg_stat_wal_receiver;

7. 切换主备角色：

   • 方法1：提升备库为主库

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

   • 方法2：使用触发文件

     touch /path/to/target/data/promote

8. 更新应用连接配置：

   • 将应用连接指向新的主库
   • 更新连接池配置

适用场景

• 需要最小化停机时间的场景
• 从单机迁移到主从架构
• 硬件升级或更换

注意事项

• 源数据库和目标数据库必须是相同的PostgreSQL版本
• 需要配置复制权限和网络连接
• 切换过程中会有短暂的停机时间
• 适用于PostgreSQL 9.0+版本

4. 使用pg_upgrade进行同版本迁移

pg_upgrade工具主要用于跨版本升级，但也可以用于同版本迁移，特别是当需要更换数据目录或调整配置时。

迁移步骤

1. 初始化目标数据库：

   initdb -D /path/to/target/data -E UTF8 --locale=en_US.utf8

2. 配置目标数据库：

   • 复制源数据库的配置文件到目标数据库
   • 调整postgresql.conf中的参数

3. 停止源数据库服务：

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

4. 运行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

5. 清理旧数据库：

   ./delete_old_cluster.sh

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

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

7. 更新统计信息：

   ./analyze_new_cluster.sh

8. 验证迁移结果：

   SELECT version();
   SELECT datname FROM pg_database;

适用场景

• 需要更换数据目录的场景
• 需要调整数据库配置的场景
• 从旧版本的同版本数据库迁移到新版本的同版本数据库（如从9.6.10到9.6.24）

注意事项

• 源数据库和目标数据库必须是相同的PostgreSQL主版本
• 迁移过程中源数据库需要停机
• 适用于PostgreSQL 9.0+版本

迁移后验证

基本功能验证

• 检查数据库服务状态：

  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. 制定详细的迁移计划

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

2. 进行充分的测试

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

3. 最小化停机时间

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

4. 确保数据安全

• 进行完整备份：在迁移前对源数据库进行完整备份
• 验证备份的完整性：确保备份文件可用
• 加密敏感数据：在迁移过程中加密敏感数据

5. 监控迁移过程

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

案例分析

案例1：物理迁移（服务器升级）

背景：需要将PostgreSQL 13数据库从旧服务器迁移到新服务器，新服务器使用SSD存储，需要最小化停机时间。

迁移方案：

1. 使用pg_basebackup进行物理备份
2. 复制备份文件到新服务器
3. 恢复备份并启动新服务器
4. 切换应用连接到新服务器

迁移步骤：

1. 在源服务器上进行物理备份：

   pg_basebackup -D /path/to/backup -F t -z -P

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

   scp /path/to/backup/base.tar.gz user@new_server:/path/to/backup/

3. 在新服务器上恢复备份：

   tar -xzf /path/to/backup/base.tar.gz -C /path/to/data/
   chown -R postgres:postgres /path/to/data/

4. 启动新服务器：

   pg_ctl -D /path/to/data start

5. 切换应用连接：

   • 更新应用配置中的数据库连接信息
   • 重启应用服务

迁移结果：

• 迁移时间：30分钟（数据量500GB）
• 停机时间：5分钟（切换应用连接时间）
• 迁移后性能提升：查询响应时间减少60%

案例2：基于复制的迁移（最小化停机时间）

背景：需要将PostgreSQL 14数据库从旧服务器迁移到新服务器，要求停机时间不超过1分钟。

迁移方案：

1. 配置源数据库的复制功能
2. 使用pg_basebackup初始化新服务器
3. 启动新服务器的复制
4. 提升新服务器为主库
5. 切换应用连接到新服务器

迁移步骤：

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

   ALTER SYSTEM SET wal_level = replica;
   ALTER SYSTEM SET max_wal_senders = 10;
   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 new_server/32 md5

4. 初始化新服务器：

   pg_basebackup -h old_server -p 5432 -U repl -D /path/to/data -F p -X stream -P -R

5. 启动新服务器：

   pg_ctl -D /path/to/data start

6. 验证复制状态：

   -- 在源服务器上检查
   SELECT * FROM pg_stat_replication;
   
   -- 在新服务器上检查
   SELECT pg_is_in_recovery();

7. 提升新服务器为主库：

   pg_ctl -D /path/to/data promote

8. 切换应用连接：

   • 更新应用配置中的数据库连接信息
   • 重启应用服务

迁移结果：

• 迁移时间：2小时（数据量2TB）
• 停机时间：30秒（提升新服务器为主库和切换应用连接时间）
• 迁移后性能提升：写入性能提升40%

总结

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

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

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