外观
PostgreSQL 在线与离线迁移
离线迁移
基本概念
离线迁移是指在迁移过程中，源数据库或目标数据库处于不可用状态，迁移完成后再恢复服务。
特点
- 迁移过程简单，易于操作
- 迁移速度快，不受网络带宽限制
- 迁移过程中服务不可用，影响业务连续性
- 适用于数据量较小或允许停机的场景
适用场景
- 测试环境迁移
- 非核心业务系统迁移
- 数据量较小的系统迁移
- 允许较长停机时间的场景
离线迁移方案
1. 基于逻辑备份的迁移
迁移工具
- pg_dump/pg_restore：PostgreSQL 内置的逻辑备份和恢复工具
- psql：PostgreSQL 命令行客户端，用于执行 SQL 脚本
迁移流程
- 源数据库备份：使用 pg_dump 导出源数据库数据
- 传输备份文件：将备份文件传输到目标服务器
- 目标数据库恢复：使用 pg_restore 或 psql 恢复数据到目标数据库
- 验证数据完整性：比较源数据库和目标数据库的数据一致性
- 切换业务：将业务切换到目标数据库
示例命令
bash
# 源数据库备份
export PGHOST=source_host
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=source_db
# 使用自定义格式备份数据库
pg_dump -F c -b -v -f /tmp/source_db.dump
# 传输备份文件到目标服务器
scp /tmp/source_db.dump target_host:/tmp/
# 目标数据库恢复
export PGHOST=target_host
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=target_db
# 创建目标数据库
createdb target_db
# 恢复数据库
pg_restore -d target_db -v /tmp/source_db.dump2. 基于物理备份的迁移
迁移工具
- pg_basebackup：PostgreSQL 内置的物理备份工具
- rsync：文件同步工具，用于传输物理备份文件
迁移流程
- 源数据库准备：确保源数据库已配置为可进行物理备份
- 执行物理备份：使用 pg_basebackup 或 rsync 备份源数据库数据目录
- 传输备份文件：将备份文件传输到目标服务器
- 目标数据库恢复：将备份文件恢复到目标数据库数据目录
- 配置目标数据库：修改目标数据库的配置文件
- 启动目标数据库：启动目标数据库服务
- 验证数据完整性：比较源数据库和目标数据库的数据一致性
- 切换业务：将业务切换到目标数据库
示例命令
bash
# 源数据库准备
# 确保 postgresql.conf 中已配置 wal_level = replica
# 执行物理备份
pg_basebackup -h source_host -U replicator -D /tmp/base_backup -X stream -F t -Z 5
# 传输备份文件到目标服务器
scp /tmp/base_backup.tar.gz target_host:/tmp/
# 目标数据库恢复
# 停止目标数据库服务
pg_ctl -D /var/lib/postgresql/14/main stop
# 清理目标数据库数据目录
rm -rf /var/lib/postgresql/14/main/*
# 解压备份文件
tar -xzf /tmp/base_backup.tar.gz -C /var/lib/postgresql/14/main/
# 修改配置文件
vi /var/lib/postgresql/14/main/postgresql.conf
# 修改 listen_addresses、port 等参数
# 启动目标数据库服务
pg_ctl -D /var/lib/postgresql/14/main start在线迁移
基本概念
在线迁移是指在迁移过程中，源数据库保持可用，业务可以正常访问，迁移完成后平滑切换到目标数据库。
特点
- 迁移过程中服务保持可用，不影响业务连续性
- 迁移复杂度高，需要特殊的工具和技术
- 迁移速度受网络带宽和系统负载影响
- 适用于核心业务系统或不允许停机的场景
适用场景
- 核心业务系统迁移
- 大型数据库迁移
- 不允许停机的生产环境
- 跨地域迁移
在线迁移方案
1. 基于逻辑复制的迁移
迁移工具
- 逻辑复制：PostgreSQL 内置的逻辑复制功能（PostgreSQL 10+）
- pg_recvlogical：PostgreSQL 内置的逻辑复制客户端
迁移流程
- 源数据库准备：配置源数据库支持逻辑复制
- 目标数据库准备：创建目标数据库和相应的 schema
- 配置逻辑复制：在源数据库创建发布，在目标数据库创建订阅
- 初始数据同步：使用 pg_dump 或其他工具同步初始数据
- 增量数据同步：通过逻辑复制同步增量数据
- 验证数据一致性：比较源数据库和目标数据库的数据一致性
- 切换业务：将业务切换到目标数据库
- 清理资源：删除逻辑复制相关资源
示例配置
- 源数据库配置：
txt
# postgresql.conf
wal_level = logical # 启用逻辑复制
max_replication_slots = 10 # 最大复制槽数量
max_wal_senders = 10 # 最大 WAL 发送者数量- 源数据库创建发布：
sql
-- 创建发布
CREATE PUBLICATION mypublication FOR ALL TABLES;- 目标数据库创建订阅：
sql
-- 创建订阅
CREATE SUBSCRIPTION mysubscription
CONNECTION 'host=source_host port=5432 user=postgres password=your_password dbname=source_db'
PUBLICATION mypublication;2. 基于流复制的迁移
迁移工具
- 流复制：PostgreSQL 内置的物理复制功能
- pg_basebackup：用于初始化从库
迁移流程
- 源数据库准备：配置源数据库支持流复制
- 目标数据库初始化：使用 pg_basebackup 初始化目标数据库
- 配置流复制：在目标数据库配置流复制
- 启动流复制：启动目标数据库，建立流复制连接
- 等待数据同步：等待源数据库和目标数据库数据一致
- 提升目标数据库为主库：将目标数据库提升为主库
- 切换业务：将业务切换到目标数据库
示例配置
- 源数据库配置：
txt
# postgresql.conf
wal_level = replica # 启用流复制
max_wal_senders = 10 # 最大 WAL 发送者数量
max_replication_slots = 10 # 最大复制槽数量- 源数据库创建复制用户：
sql
-- 创建复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_password';- 源数据库配置 pg_hba.conf：
txt
# 允许复制用户从目标服务器连接
host replication replicator target_host/32 md5- 目标数据库初始化：
bash
# 使用 pg_basebackup 初始化目标数据库
pg_basebackup -h source_host -U replicator -D /var/lib/postgresql/14/main -X stream -P -v- 目标数据库创建 standby.signal：
bash
# 创建 standby.signal 文件，指示目标数据库作为从库启动
touch /var/lib/postgresql/14/main/standby.signal- 目标数据库配置 recovery.conf：
txt
# recovery.conf (PostgreSQL 12+ 使用 postgresql.conf 和 standby.signal)
# 主库连接信息
primary_conninfo = 'host=source_host port=5432 user=replicator password=your_password'
# 复制槽名称（可选）
primary_slot_name = 'replication_slot_name'- 启动目标数据库：
bash
pg_ctl -D /var/lib/postgresql/14/main start- 提升目标数据库为主库：
bash
# 停止目标数据库
pg_ctl -D /var/lib/postgresql/14/main stop
# 删除 standby.signal 文件
rm /var/lib/postgresql/14/main/standby.signal
# 启动目标数据库为主库
pg_ctl -D /var/lib/postgresql/14/main start3. 使用第三方工具的在线迁移
常用工具
- pg_migrator：PostgreSQL 迁移工具，支持在线迁移
- pgloader：用于从其他数据库迁移到 PostgreSQL 的工具，支持在线迁移
- Londiste：Skytools 套件中的逻辑复制工具，支持在线迁移
- Slony-I：基于触发器的逻辑复制工具，支持在线迁移
pgloader 示例
bash
# 安装 pgloader
sudo apt-get install pgloader
# 从 PostgreSQL 在线迁移到 PostgreSQL
pgloader pgsql://source_user:source_password@source_host/source_db pgsql://target_user:target_password@target_host/target_db迁移性能优化
1. 离线迁移优化
- 使用并行备份和恢复：使用
-j参数启用并行备份和恢复 - 压缩备份文件：使用
-Z参数压缩备份文件，减少传输时间 - 选择合适的备份格式：使用自定义格式（-F c）或目录格式（-F d），提高恢复速度
- 关闭不必要的数据库功能：在恢复过程中关闭触发器、约束等，恢复完成后再启用
- 优化目标数据库配置：在恢复前调整目标数据库的配置参数，如 shared_buffers、work_mem 等
2. 在线迁移优化
- 优化源数据库配置：调整源数据库的 wal_level、max_wal_senders 等参数
- 使用复制槽：确保 WAL 日志不丢失，避免数据不一致
- 限制复制带宽：使用
pg_hba.conf中的rate_limit参数限制复制带宽，避免影响源数据库性能 - 优化目标数据库：调整目标数据库的配置，提高复制和写入性能
- 监控复制延迟：实时监控复制延迟，及时发现和解决问题
迁移前准备工作
1. 环境准备
- 目标数据库环境：确保目标服务器硬件配置满足要求，操作系统和 PostgreSQL 版本兼容
- 网络连接：确保源数据库和目标数据库之间网络连接稳定，带宽足够
- 权限准备：确保迁移用户具有足够的权限
- 存储空间：确保目标服务器有足够的存储空间容纳迁移数据
2. 数据库准备
- 源数据库清理：清理源数据库中的无用数据，减少迁移数据量
- 源数据库优化：优化源数据库性能，确保迁移过程顺利进行
- 目标数据库初始化：创建目标数据库和相应的用户、权限
- Schema 同步：确保目标数据库的 Schema 与源数据库一致
3. 迁移计划
- 制定详细的迁移计划：包括迁移时间、步骤、责任人、回滚方案等
- 进行迁移测试：在测试环境中进行迁移测试，验证迁移方案的可行性
- 准备回滚方案：制定详细的回滚方案，确保迁移失败时可以快速回滚
- 通知相关人员：提前通知业务人员、运维人员等相关人员
迁移后验证工作
1. 数据完整性验证
- 比较表行数：比较源数据库和目标数据库各表的行数
- 检查关键数据：检查关键业务数据是否完整
- 运行一致性检查：使用 pg_verify_checksums 等工具检查数据完整性
- 验证约束和索引：确保所有约束和索引都已正确创建
2. 功能验证
- 测试业务功能：测试关键业务功能是否正常
- 验证存储过程和函数：确保所有存储过程和函数都能正常执行
- 测试触发器和事件：确保所有触发器和事件都能正常工作
- 验证权限：确保用户权限配置正确
3. 性能验证
- 测试查询性能：测试关键查询的性能是否符合要求
- 测试写入性能：测试写入操作的性能是否符合要求
- 监控系统资源：监控目标数据库的 CPU、内存、磁盘和网络使用率
- 比较性能指标：比较迁移前后的性能指标，确保性能满足要求
迁移最佳实践
1. 离线迁移最佳实践
- 选择合适的备份工具：根据数据量和迁移需求选择合适的备份工具
- 使用并行备份和恢复：提高迁移速度
- 压缩备份文件：减少传输时间和存储空间
- 验证备份文件完整性：使用 pg_restore --list 验证备份文件完整性
- 在低峰期进行迁移：减少对业务的影响
2. 在线迁移最佳实践
- 选择合适的迁移工具：根据数据量、延迟要求和复杂度选择合适的迁移工具
- 进行充分的测试：在测试环境中进行多次迁移测试
- 监控迁移过程：实时监控迁移过程，及时发现和解决问题
- 准备回滚方案：制定详细的回滚方案
- 分阶段迁移：对于大型数据库，可以考虑分阶段迁移
3. 通用最佳实践
- 制定详细的迁移计划：包括迁移前准备、迁移过程、迁移后验证等
- 进行充分的测试：验证迁移方案的可行性和性能
- 备份源数据库：在迁移前备份源数据库，确保数据安全
- 监控系统资源：监控源数据库和目标数据库的资源使用情况
- 记录迁移过程：详细记录迁移过程中的步骤、问题和解决方案
常见问题（FAQ）
Q1: 如何选择在线迁移还是离线迁移？
A1: 选择迁移方式需要考虑以下因素：
- 业务连续性要求：不允许停机则选择在线迁移
- 数据量大小：数据量小可以选择离线迁移，数据量大建议选择在线迁移
- 迁移时间窗口：有足够停机时间可以选择离线迁移
- 系统复杂度：复杂系统建议选择在线迁移
- 技能和经验：在线迁移需要更多的技能和经验
Q2: 如何减少迁移过程中的停机时间？
A2: 减少停机时间的方法：
- 使用在线迁移方式
- 在低峰期进行迁移
- 优化迁移流程，减少各步骤的时间
- 使用并行迁移，提高迁移速度
- 提前进行充分的测试，避免迁移过程中的问题
Q3: 如何确保迁移后数据的一致性？
A3: 确保数据一致性的方法：
- 使用可靠的迁移工具
- 在迁移过程中使用复制槽，确保数据不丢失
- 迁移后进行数据完整性验证
- 比较源数据库和目标数据库的关键数据
- 运行一致性检查工具
Q4: 迁移过程中遇到复制延迟怎么办？
A4: 处理复制延迟的方法：
- 优化源数据库和目标数据库的配置
- 增加网络带宽
- 限制复制带宽，避免影响源数据库性能
- 在低峰期进行迁移
- 考虑使用更高效的迁移工具
Q5: 迁移后如何优化目标数据库性能？
A5: 优化目标数据库性能的方法：
- 调整目标数据库的配置参数，如 shared_buffers、work_mem 等
- 优化目标数据库的索引和查询
- 收集目标数据库的统计信息
- 监控目标数据库的性能指标
- 根据实际负载进行调整
Q6: 如何制定迁移回滚方案？
A6: 制定回滚方案的方法：
- 备份源数据库，确保可以恢复到迁移前状态
- 记录迁移前的系统状态和配置
- 制定详细的回滚步骤和时间计划
- 确定回滚的触发条件
- 准备回滚所需的工具和资源
- 在测试环境中验证回滚方案
Q7: 跨版本迁移需要注意什么？
A7: 跨版本迁移注意事项：
- 检查版本兼容性，确保目标版本支持源版本的功能
- 阅读版本升级文档，了解版本差异和注意事项
- 在测试环境中进行跨版本迁移测试
- 注意新版本的特性和变化
- 考虑使用 pg_upgrade 工具进行版本升级
Q8: 迁移过程中如何监控迁移进度？
A8: 监控迁移进度的方法：
- 使用迁移工具提供的进度监控功能
- 监控源数据库和目标数据库的资源使用情况
- 监控复制延迟（对于在线迁移）
- 定期检查迁移日志
- 使用第三方监控工具，如 Prometheus + Grafana
迁移工具比较
| 迁移工具 | 类型 | 支持版本 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|---|
| pg_dump/pg_restore | 逻辑备份 | 所有版本 | 简单易用，支持跨版本迁移 | 迁移速度慢，影响业务 | 离线迁移，小数据量 |
| pg_basebackup | 物理备份 | 所有版本 | 迁移速度快，适合大数据量 | 不支持跨版本迁移 | 离线迁移，大数据量 |
| 流复制 | 物理复制 | 9.0+ | 支持在线迁移，数据一致性好 | 配置复杂，不支持跨版本 | 在线迁移，同版本迁移 |
| 逻辑复制 | 逻辑复制 | 10+ | 支持在线迁移，支持跨版本 | 配置复杂，性能开销大 | 在线迁移，跨版本迁移 |
| pgloader | 逻辑迁移 | 所有版本 | 支持多种数据源，配置简单 | 迁移速度慢 | 在线迁移，小数据量 |
| Londiste | 逻辑复制 | 8.3+ | 支持在线迁移，成熟稳定 | 配置复杂，维护成本高 | 在线迁移，大型数据库 |
| Slony-I | 逻辑复制 | 8.2+ | 支持在线迁移，功能强大 | 配置复杂，性能开销大 | 在线迁移，复杂场景 |
通过选择合适的迁移方式和工具，制定详细的迁移计划，并进行充分的测试和验证，可以确保 PostgreSQL 数据库迁移的顺利进行，减少对业务的影响，提高迁移成功率。