印刻数据库

外观

PostgreSQL 跨版本迁移 ​

迁移方法 ​

pg_upgrade 迁移 ​

  1. pg_upgrade 原理

    • 二进制升级：直接使用旧版本数据文件，仅升级系统目录
    • 链接模式：使用硬链接减少磁盘空间使用
    • 复制模式：复制数据文件到新位置

  2. pg_upgrade 准备

    bash
    # 安装新版本PostgreSQL
# 确保新旧版本PostgreSQL都已安装

# 初始化新版本数据库
/usr/pgsql-15/bin/initdb -D /path/to/new/data

# 配置新版本postgresql.conf
echo "listen_addresses = '*'" >> /path/to/new/data/postgresql.conf
echo "port = 5433" >> /path/to/new/data/postgresql.conf

  3. pg_upgrade 执行

    bash
    # 停止旧版本PostgreSQL
pg_ctl -D /path/to/old/data stop

# 执行pg_upgrade（链接模式）
/usr/pgsql-15/bin/pg_upgrade \
  --old-datadir=/path/to/old/data \
  --new-datadir=/path/to/new/data \
  --old-bindir=/usr/pgsql-14/bin \
  --new-bindir=/usr/pgsql-15/bin \
  --old-port=5432 \
  --new-port=5433 \
  --link \
  --check

# 检查通过后，执行实际迁移
/usr/pgsql-15/bin/pg_upgrade \
  --old-datadir=/path/to/old/data \
  --new-datadir=/path/to/new/data \
  --old-bindir=/usr/pgsql-14/bin \
  --new-bindir=/usr/pgsql-15/bin \
  --old-port=5432 \
  --new-port=5433 \
  --link

  4. pg_upgrade 后续处理

    bash
    # 运行分析脚本
./analyze_new_cluster.sh

# 清理旧集群
./delete_old_cluster.sh

# 启动新版本PostgreSQL
pg_ctl -D /path/to/new/data start

基于备份恢复的迁移 ​

  1. dump/restore 迁移

    bash
    # 使用旧版本pg_dump备份
/usr/pgsql-14/bin/pg_dump -h localhost -p 5432 -U postgres -d dbname -F c -b -v -f dbname.dump

# 使用新版本pg_restore恢复
/usr/pgsql-15/bin/pg_restore -h localhost -p 5433 -U postgres -d dbname -v dbname.dump

  2. pg_dumpall 迁移所有数据库

    bash
    # 备份所有数据库
/usr/pgsql-14/bin/pg_dumpall -h localhost -p 5432 -U postgres -v > all_databases.sql

# 恢复到新版本
/usr/pgsql-15/bin/psql -h localhost -p 5433 -U postgres -v -f all_databases.sql

逻辑复制迁移 ​

  1. 逻辑复制配置

    bash
    # 旧版本配置（源）
echo "wal_level = logical" >> /path/to/old/data/postgresql.conf
echo "max_replication_slots = 10" >> /path/to/old/data/postgresql.conf
echo "max_wal_senders = 10" >> /path/to/old/data/postgresql.conf

# 重启旧版本
pg_ctl -D /path/to/old/data reload

  2. 创建发布和订阅

    sql
    -- 在源数据库（旧版本）创建发布
CREATE PUBLICATION pub1 FOR ALL TABLES;

-- 在目标数据库（新版本）创建订阅
CREATE SUBSCRIPTION sub1 
CONNECTION 'host=source_host port=5432 dbname=dbname user=replication password=password' 
PUBLICATION pub1;

版本差异处理 ​

PostgreSQL 13 → 14 差异 ​

  1. 分区表增强

    • 验证分区表约束
    • 检查分区裁剪功能
    sql
    -- 检查分区表
SELECT * FROM pg_partitioned_table;

  2. 逻辑复制增强

    • 验证逻辑复制配置
    • 检查复制槽状态
    sql
    -- 检查复制槽
SELECT * FROM pg_replication_slots;

PostgreSQL 14 → 15 差异 ​

  1. JSONB 性能优化

    • 测试JSONB查询性能
    • 验证JSONB索引使用

  2. 并行查询增强

    • 检查并行查询执行计划
    • 验证并行度设置
    sql
    -- 检查并行设置
SHOW max_parallel_workers;
SHOW max_parallel_workers_per_gather;

PostgreSQL 15 → 16 差异 ​

  1. MERGE 语句支持

    • 验证现有UPSERT语句
    • 测试新增MERGE语句
    sql
    -- 测试MERGE语句
MERGE INTO target_table t
USING source_table s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

  2. 权限管理增强

    • 检查权限设置
    • 验证新权限模型
    sql
    -- 检查权限
SELECT * FROM information_schema.role_table_grants;

迁移准备 ​

预迁移检查 ​

  1. 版本兼容性检查

    bash
    # 检查pg_upgrade兼容性
/usr/pgsql-15/bin/pg_upgrade --check \
  --old-datadir=/path/to/old/data \
  --new-datadir=/path/to/new/data \
  --old-bindir=/usr/pgsql-14/bin \
  --new-bindir=/usr/pgsql-15/bin

  2. 依赖检查

    sql
    -- 检查扩展
SELECT * FROM pg_extension WHERE extname NOT IN ('plpgsql', 'pg_stat_statements');

-- 检查用户函数
SELECT proname, prosrc FROM pg_proc 
WHERE pronamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'));

  3. 性能基准测试

    bash
    # 运行pgbench基准测试
pgbench -h localhost -p 5432 -U postgres -d dbname -t 10000 -c 10 -j 4

迁移执行 ​

迁移步骤 ​

  1. 备份旧版本数据

    bash
    # 全量备份
pg_dumpall -h localhost -p 5432 -U postgres -v > pre_migration_backup.sql

  2. 执行迁移

    • 根据选择的迁移方法执行
    • 监控迁移进度
    • 记录迁移日志

  3. 迁移验证

    sql
    -- 验证数据库对象
SELECT schemaname, relname, relkind FROM pg_class 
WHERE relkind IN ('r', 'i', 'S', 'v', 'm', 'c', 'f', 'p')
AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, relname;

-- 验证数据完整性
SELECT COUNT(*) FROM important_table;

-- 验证扩展
SELECT * FROM pg_extension;

迁移后处理 ​

优化和调整 ​

  1. 重建统计信息

    sql
    -- 重建所有数据库统计信息
ANALYZE VERBOSE;

  2. 重建索引

    sql
    -- 重建特定表索引
REINDEX TABLE important_table;

-- 重建所有索引
REINDEX DATABASE dbname;

  3. 调整配置参数

    sql
    -- 根据新版本推荐调整参数
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

应用切换 ​

  1. 切换策略

    • 停机切换：直接将应用连接指向新版本
    • 双写切换：同时向新旧版本写入，验证后切换
    • 滚动切换：逐步将应用实例切换到新版本

  2. 切换验证

    bash
    # 检查应用连接
psql -h localhost -p 5433 -U app_user -d dbname -c "SELECT 1;"

# 监控性能
SELECT * FROM pg_stat_activity WHERE state <> 'idle';

最佳实践 ​

迁移策略选择 ​

迁移方法适用场景优点缺点
pg_upgrade大型数据库迁移速度快需要停机时间
dump/restore中小型数据库兼容性好迁移时间长
逻辑复制高可用要求几乎零停机配置复杂

性能优化 ​

  1. 并行迁移

    bash
    # 并行备份
pg_dump -j 4 -h localhost -p 5432 -U postgres -d dbname -F d -b -v -f /path/to/backup/

# 并行恢复
pg_restore -j 4 -h localhost -p 5433 -U postgres -d dbname -v /path/to/backup/

  2. 压缩传输

    bash
    # 压缩备份
pg_dump -h localhost -p 5432 -U postgres -d dbname -F c -Z 5 -b -v -f backup.dump

  3. 增量迁移

    • 先迁移历史数据
    • 再迁移增量数据
    • 最后切换应用

常见问题（FAQ） ​

Q1：pg_upgrade 失败怎么办？ ​

A1：

  1. 检查错误日志，定位问题
  2. 回滚到旧版本：
    bash
    pg_ctl -D /path/to/old/data start
  3. 修复问题后重新尝试

Q2：迁移后性能下降怎么办？ ​

A2：

  1. 分析查询执行计划：
    sql
    EXPLAIN ANALYZE SELECT * FROM important_table WHERE condition;
  2. 重建统计信息：
    sql
    ANALYZE VERBOSE important_table;
  3. 调整配置参数
  4. 重建索引

Q3：迁移后扩展无法使用怎么办？ ​

A3：

  1. 检查扩展是否兼容新版本
  2. 重新创建扩展：
    sql
    DROP EXTENSION IF EXISTS extension_name;
CREATE EXTENSION extension_name;

Q4：如何处理迁移过程中的数据不一致？ ​

A4：

  1. 使用pg_verify_checksums验证数据完整性
  2. 运行pg_checksums检查数据页
  3. 对比迁移前后的表行数和数据抽样

Q5：跨多个大版本迁移（如12→16）需要注意什么？ ​

A5：

  1. 建议逐步迁移（12→13→14→15→16）
  2. 每个版本都进行验证
  3. 特别注意版本间的重大变更
  4. 提前测试应用兼容性