PostgreSQL 主版本升级

升级前准备

了解升级内容

bash

# 查看版本间的主要变更
echo "请访问PostgreSQL官方网站查看主版本变更日志："
echo "https://www.postgresql.org/docs/current/release.html"

# 查看具体版本的发布说明
echo "例如，PostgreSQL 15发布说明："
echo "https://www.postgresql.org/docs/15/release-15.html"

检查当前环境

sql

-- 检查当前PostgreSQL版本
SELECT version();

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

-- 检查数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));

备份数据

bash

# 进行完整的数据备份
pg_dumpall -h localhost -U postgres -f pg_dumpall_$(date +%Y%m%d%H%M%S).sql

# 或使用pg_basebackup进行物理备份
pg_basebackup -h localhost -U replication -D /backup/pg_basebackup_$(date +%Y%m%d%H%M%S) -Fp -Xs -P

测试升级

bash

# 在测试环境中进行升级测试
# 1. 复制生产数据到测试环境
# 2. 执行升级操作
# 3. 验证应用兼容性
# 4. 验证性能
# 5. 测试回滚计划

准备升级计划

步骤	内容	负责人	时间估计
1	升级前准备	DBA	60分钟
2	停止应用访问	应用管理员	15分钟
3	执行升级	DBA	120分钟
4	验证升级	DBA	60分钟
5	恢复应用访问	应用管理员	15分钟
6	监控系统状态	运维人员	120分钟

升级方法

使用pg_upgrade升级

pg_upgrade是PostgreSQL官方提供的主版本升级工具，支持二进制升级，升级速度快。

准备工作

bash

# 1. 安装目标版本的PostgreSQL
sudo apt install postgresql-15 postgresql-contrib-15

# 2. 确保两个版本的PostgreSQL都已安装
sudo systemctl stop postgresql@14-main
sudo systemctl stop postgresql@15-main

执行升级

bash

# 1. 创建目标数据目录
sudo mkdir -p /var/lib/postgresql/15/main

# 2. 初始化目标数据目录
sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main

# 3. 执行pg_upgrade
sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/15/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/15/bin \
  --check

# 4. 如果检查通过，执行实际升级
sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/15/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/15/bin

# 5. 更新统计信息
sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb -U postgres -d postgres -a -z

# 6. 启动新版本的PostgreSQL
sudo systemctl start postgresql@15-main

使用逻辑备份恢复升级

逻辑备份恢复是一种更安全但速度较慢的升级方法，适用于跨多个主版本的升级。

bash

# 1. 使用旧版本的pg_dump导出数据
pg_dumpall -h localhost -U postgres -p 5432 -f pg_dumpall_$(date +%Y%m%d%H%M%S).sql

# 2. 安装并初始化目标版本的PostgreSQL
sudo apt install postgresql-15 postgresql-contrib-15
sudo systemctl start postgresql@15-main

# 3. 使用新版本的psql导入数据
psql -h localhost -U postgres -p 5433 -f pg_dumpall_$(date +%Y%m%d%H%M%S).sql

# 4. 更新统计信息
vacuumdb -U postgres -d postgres -a -z

使用复制槽进行升级

对于流式复制架构，可以使用复制槽进行升级，减少停机时间。

bash

# 1. 在主库上创建复制槽
psql -c "SELECT * FROM pg_create_physical_replication_slot('upgrade_slot');"

# 2. 使用pg_basebackup从复制槽备份
pg_basebackup -D /backup/upgrade_base -S upgrade_slot -Xs -P

# 3. 在备份上执行pg_upgrade
# 4. 将升级后的数据库作为新主库
# 5. 调整复制配置

升级后验证

验证版本

sql

-- 验证PostgreSQL版本
SELECT version();

-- 检查集群版本
SELECT current_setting('server_version_num')::integer;

验证数据完整性

sql

-- 运行数据库一致性检查
VACUUM ANALYZE VERBOSE;

-- 检查扩展是否正常
SELECT extname, extversion FROM pg_extension;

-- 验证数据量
SELECT count(*) FROM users;
SELECT count(*) FROM orders;

验证应用兼容性

bash

# 运行应用测试套件
# 或使用简单的查询测试
psql -d mydb -c "SELECT * FROM users LIMIT 10;"
psql -d mydb -c "SELECT * FROM orders WHERE customer_id = 12345;"
psql -d mydb -c "INSERT INTO test (name) VALUES ('test'); DELETE FROM test WHERE name = 'test';"

验证性能

bash

# 使用pgbench进行性能测试
pgbench -i -h localhost -U postgres -d mydb
pgbench -h localhost -U postgres -d mydb -c 20 -j 4 -T 120 -r > post_upgrade_performance.txt

# 与升级前的性能基线对比
diff pre_upgrade_performance.txt post_upgrade_performance.txt

验证扩展功能

sql

-- 测试常用扩展
SELECT * FROM pg_stat_statements LIMIT 5;
SELECT * FROM pg_stat_user_tables;

回滚计划

基于pg_upgrade的回滚

bash

# 如果使用pg_upgrade升级失败，可以使用以下方法回滚

# 1. 停止新版本的PostgreSQL
sudo systemctl stop postgresql@15-main

# 2. 启动旧版本的PostgreSQL
sudo systemctl start postgresql@14-main

# 3. 验证旧版本是否正常运行
psql -h localhost -U postgres -p 5432 -c "SELECT version();"

基于备份的回滚

bash

# 如果使用逻辑备份恢复升级失败，可以使用以下方法回滚

# 1. 停止新版本的PostgreSQL
sudo systemctl stop postgresql@15-main

# 2. 恢复旧版本的数据目录
rm -rf /var/lib/postgresql/14/main/*
cp -r /backup/pg_basebackup_20231201143000/* /var/lib/postgresql/14/main/

# 3. 调整权限
sudo chown -R postgres:postgres /var/lib/postgresql/14/main/

# 4. 启动旧版本的PostgreSQL
sudo systemctl start postgresql@14-main

# 5. 验证回滚结果
psql -c "SELECT version();"

升级最佳实践

升级时机选择

选择业务低峰期进行升级
预留足够的升级和回滚时间
避开重要业务活动期间
提前通知相关团队

升级顺序

开发环境
测试环境
预生产环境
生产环境

监控和告警

bash

# 升级过程中监控系统状态
top
iostat -x 1
vmstat 1

# 监控PostgreSQL日志
tail -f /var/log/postgresql/postgresql-15-main.log

升级后的优化

sql

-- 更新统计信息
VACUUM ANALYZE;

-- 重建索引（如果需要）
REINDEX DATABASE mydb;

-- 更新扩展
ALTER EXTENSION pg_stat_statements UPDATE;
ALTER EXTENSION plpgsql UPDATE;

-- 优化配置参数
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
SELECT pg_reload_conf();

常见问题处理

问题	解决方案
扩展不兼容	更新扩展或寻找替代方案
配置参数弃用	查看文档，使用新的参数替代
性能下降	分析慢查询，调整参数，重建索引
应用兼容性问题	修改应用代码，适配新的API

常见问题（FAQ）

Q1：主版本升级需要多长时间？

A1：升级时间取决于多种因素：

数据库大小
升级方法（pg_upgrade比逻辑备份恢复快得多）
硬件性能
扩展数量
并发连接数

建议在测试环境中进行演练，以获得更准确的时间估计。

Q2：主版本升级可以跨多个版本吗？

A2：是的，但建议使用逻辑备份恢复方法，例如从12.x直接升级到15.x。使用pg_upgrade时，通常建议最多跨一个主版本。

Q3：升级后需要重新编译扩展吗？

A3：是的，大多数扩展需要针对新的PostgreSQL版本重新编译或更新。建议在升级前检查所有扩展的兼容性。

Q4：如何最小化主版本升级的停机时间？

A4：可以通过以下方式最小化停机时间：

使用pg_upgrade进行二进制升级
采用蓝绿部署策略
使用复制槽进行升级
在业务低峰期进行升级
提前做好充分的准备工作

Q5：升级后需要更新应用驱动吗？

A5：是的，建议更新应用使用的PostgreSQL驱动到最新版本，以确保与新的PostgreSQL版本兼容。

Q6：如何验证升级后的系统稳定性？

A6：升级后，建议：

运行完整的应用测试套件
监控系统性能24-48小时
检查PostgreSQL日志中的错误
验证关键业务功能
进行压力测试，验证系统的并发处理能力

PostgreSQL 主版本升级 ​

升级前准备 ​

了解升级内容 ​

检查当前环境 ​

备份数据 ​

测试升级 ​

准备升级计划 ​

升级方法 ​

使用pg_upgrade升级 ​

准备工作 ​

执行升级 ​

使用逻辑备份恢复升级 ​

使用复制槽进行升级 ​

升级后验证 ​

验证版本 ​

验证数据完整性 ​

验证应用兼容性 ​

验证性能 ​

验证扩展功能 ​

回滚计划 ​

基于pg_upgrade的回滚 ​

基于备份的回滚 ​

升级最佳实践 ​

升级时机选择 ​

升级顺序 ​

监控和告警 ​

升级后的优化 ​

常见问题处理 ​

常见问题（FAQ） ​

Q1：主版本升级需要多长时间？ ​

Q2：主版本升级可以跨多个版本吗？ ​

Q3：升级后需要重新编译扩展吗？ ​

Q4：如何最小化主版本升级的停机时间？ ​

Q5：升级后需要更新应用驱动吗？ ​

Q6：如何验证升级后的系统稳定性？ ​

PostgreSQL 主版本升级

升级前准备

了解升级内容

检查当前环境

备份数据

测试升级

准备升级计划

升级方法

使用pg_upgrade升级

准备工作

执行升级

使用逻辑备份恢复升级

使用复制槽进行升级

升级后验证

验证版本

验证数据完整性

验证应用兼容性

验证性能

验证扩展功能

回滚计划

基于pg_upgrade的回滚

基于备份的回滚

升级最佳实践

升级时机选择

升级顺序

监控和告警

升级后的优化

常见问题处理

常见问题（FAQ）

Q1：主版本升级需要多长时间？

Q2：主版本升级可以跨多个版本吗？

Q3：升级后需要重新编译扩展吗？

Q4：如何最小化主版本升级的停机时间？

Q5：升级后需要更新应用驱动吗？

Q6：如何验证升级后的系统稳定性？