PostgreSQL 集群升级完全指南

PostgreSQL 升级是一个关键的运维操作，需要谨慎规划和执行。本指南将帮助您安全、高效地完成 PostgreSQL 版本升级。

📋 目录

版本概念与兼容性
升级前的准备工作
升级方法对比
方法一：pg_dumpall 升级
方法二：pg_upgrade 升级
方法三：复制升级
升级后验证
故障处理

🔢 版本概念与兼容性

PostgreSQL 版本命名规则

版本兼容性说明

兼容性规则

次版本升级：同一主版本内的次版本之间完全兼容
主版本升级：需要特殊的升级程序，可能存在不兼容性 :::

次版本升级示例：

bash

# 这些版本之间可以直接替换执行文件
PostgreSQL 14.1 → 14.2 → 14.9  ✅ 兼容
PostgreSQL 13.5 → 13.8          ✅ 兼容

# 这些需要特殊升级程序
PostgreSQL 13.8 → 14.1          ❌ 需要升级程序
PostgreSQL 12.x → 15.x          ❌ 需要升级程序

业务场景中的版本选择

场景	推荐策略	原因
生产环境	主版本稳定后 1-2 年升级	避免早期 bug，确保稳定性
开发环境	跟随最新次版本	获得最新功能和安全补丁
测试环境	提前测试新主版本	验证应用兼容性

🎯 升级前的准备工作

1. 环境评估

sql

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

-- 检查数据库大小（用于评估升级时间）
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- 检查扩展兼容性
SELECT
    extname AS extension_name,
    extversion AS current_version
FROM pg_extension;

2. 兼容性检查清单

升级兼容性检查清单

查阅目标版本的发行说明
检查自定义函数和存储过程
验证第三方扩展兼容性
测试应用程序连接
检查配置文件变更
验证备份策略 :::

3. 测试环境准备

bash

# 创建测试环境的数据副本
pg_dump -h production_host -U postgres -d mydb > test_backup.sql

# 在测试环境恢复
createdb test_mydb
psql -d test_mydb -f test_backup.sql

⚖️ 升级方法对比

特性	pg_dumpall	pg_upgrade	复制升级
停机时间	长 (数小时到数天)	短 (几分钟到几小时)	极短 (几秒)
磁盘空间	需要 2-3 倍空间	需要 2 倍空间	需要 2 倍空间
复杂度	简单	中等	复杂
回滚难度	容易	中等	困难
适用场景	小型数据库	中大型数据库	大型生产环境

🔄 方法一：pg_dumpall 升级

适用场景

数据库总大小 < 50GB
可以承受较长停机时间
追求操作简单和安全性

详细步骤

步骤 1：备份数据

bash

# 1. 限制数据库访问（编辑 pg_hba.conf）
sudo nano /usr/local/pgsql/data/pg_hba.conf

# 添加或修改，只允许本地访问
local   all   all   trust
host    all   all   127.0.0.1/32   reject
host    all   all   ::1/128        reject

# 2. 重新加载配置
sudo systemctl reload postgresql

# 3. 执行完整备份
pg_dumpall -h localhost -p 5432 -U postgres > /backup/full_backup_$(date +%Y%m%d_%H%M%S).sql

# 4. 验证备份文件
ls -lh /backup/full_backup_*.sql
head -20 /backup/full_backup_*.sql  # 检查文件开头

最佳实践使用新版本的 pg_dumpall 进行备份，即使目标版本还未安装。这可以避免老版本工具的 bug。 :::

步骤 2：停止旧服务器

bash

# 检查 PostgreSQL 状态
sudo systemctl status postgresql

# 停止服务
sudo systemctl stop postgresql

# 确认进程已停止
ps aux | grep postgres

步骤 3：备份和安装新版本

bash

# 1. 备份旧安装目录
sudo mv /usr/local/pgsql /usr/local/pgsql.old

# 2. 安装新版本 PostgreSQL
# (具体安装步骤取决于您的系统和安装方式)

# 3. 初始化新数据库集群
sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

# 4. 恢复配置文件
sudo cp /usr/local/pgsql.old/data/postgresql.conf /usr/local/pgsql/data/
sudo cp /usr/local/pgsql.old/data/pg_hba.conf /usr/local/pgsql/data/

步骤 4：启动新服务器并恢复数据

bash

# 1. 启动新的 PostgreSQL 服务器
sudo systemctl start postgresql

# 2. 恢复数据
sudo -u postgres psql -d postgres -f /backup/full_backup_20231201_143025.sql

# 3. 检查恢复状态
sudo -u postgres psql -d postgres -c "SELECT count(*) FROM pg_database;"

实际案例：电商网站升级

场景：一个中小型电商网站，数据库大小约 30GB，从 PostgreSQL 13.8 升级到 15.2

bash

# 业务背景
# - 数据库：3 个业务数据库 (orders, products, users)
# - 大小：orders(20GB), products(8GB), users(2GB)
# - 预计停机时间：4-6 小时

# 1. 凌晨 2:00 开始维护窗口
echo "$(date): 开始数据库升级维护" >> /var/log/upgrade.log

# 2. 限制用户访问
sudo nano /etc/postgresql/13/main/pg_hba.conf
# 配置只允许本地管理员访问

# 3. 备份所有数据
pg_dumpall -p 5432 > /backup/ecommerce_upgrade_$(date +%Y%m%d_%H%M%S).sql

# 4. 验证备份完整性
echo "备份文件大小: $(du -h /backup/ecommerce_upgrade_*.sql)"
grep -c "CREATE DATABASE" /backup/ecommerce_upgrade_*.sql

# 5. 执行升级流程...
# 6. 凌晨 6:00 完成，业务恢复正常

输出示例：

备份文件大小: 12G    /backup/ecommerce_upgrade_20231201_020000.sql
CREATE DATABASE 数量: 3
升级总耗时: 3小时45分钟
业务验证通过: ✅

⚡ 方法二：pg_upgrade 升级

适用场景

中大型数据库 (50GB - 1TB)
需要控制停机时间
有足够的磁盘空间

pg_upgrade 工作原理

详细步骤

步骤 1：准备和检查

bash

# 1. 安装新版本 PostgreSQL (与旧版本并存)
# 假设旧版本在 /usr/local/pgsql-13，新版本在 /usr/local/pgsql-15

# 2. 执行兼容性检查
sudo -u postgres /usr/local/pgsql-15/bin/pg_upgrade \
  --old-datadir /usr/local/pgsql-13/data \
  --new-datadir /usr/local/pgsql-15/data \
  --old-bindir /usr/local/pgsql-13/bin \
  --new-bindir /usr/local/pgsql-15/bin \
  --check

# 3. 查看检查结果
cat pg_upgrade_utility.log

步骤 2：执行升级

bash

# 1. 停止旧服务器
sudo systemctl stop postgresql-13

# 2. 执行升级（使用链接模式以节省时间和空间）
sudo -u postgres /usr/local/pgsql-15/bin/pg_upgrade \
  --old-datadir /usr/local/pgsql-13/data \
  --new-datadir /usr/local/pgsql-15/data \
  --old-bindir /usr/local/pgsql-13/bin \
  --new-bindir /usr/local/pgsql-15/bin \
  --link \
  --verbose

# 3. 启动新服务器
sudo systemctl start postgresql-15

步骤 3：升级后优化

bash

# 1. 更新统计信息
sudo -u postgres /usr/local/pgsql-15/bin/vacuumdb --all --analyze-in-stages

# 2. 运行清理脚本（可选，建议在确认升级成功后执行）
./delete_old_cluster.sh

性能对比示例

测试环境：500GB 数据库，PostgreSQL 12 → 14

方法	执行时间	磁盘使用	停机时间
pg_dumpall	8 小时	1.5TB	8 小时
pg_upgrade (copy)	2 小时	1TB	2 小时
pg_upgrade (link)	15 分钟	500GB	15 分钟

bash

# pg_upgrade 性能优化技巧

# 1. 使用 --link 模式（硬链接，速度最快）
--link

# 2. 使用 --jobs 参数并行处理
--jobs 4

# 3. 完整的高性能升级命令
sudo -u postgres /usr/local/pgsql-15/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/13/main \
  --new-datadir /var/lib/postgresql/15/main \
  --old-bindir /usr/lib/postgresql/13/bin \
  --new-bindir /usr/lib/postgresql/15/bin \
  --old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf' \
  --link \
  --jobs 4 \
  --verbose

🔄 方法三：复制升级

适用场景

大型生产环境 (> 1TB)
要求极短停机时间
有额外的服务器资源

逻辑复制升级流程

详细实施步骤

步骤 1：配置主服务器（旧版本）

sql

-- 1. 修改主服务器配置
-- 编辑 postgresql.conf
wal_level = logical              -- 启用逻辑复制
max_replication_slots = 10       -- 设置复制槽数量
max_wal_senders = 10            -- 设置 WAL 发送进程数

-- 编辑 pg_hba.conf
host replication replica_user 10.0.1.0/24 md5

-- 2. 重启服务使配置生效
sudo systemctl restart postgresql

sql

-- 3. 创建复制用户
CREATE USER replica_user WITH REPLICATION PASSWORD 'secure_password';

-- 4. 创建发布
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- 5. 查看发布状态
SELECT * FROM pg_publication;

步骤 2：设置备用服务器（新版本）

bash

# 1. 在新服务器上安装 PostgreSQL 15
# 2. 初始化数据库集群
sudo -u postgres /usr/local/pgsql-15/bin/initdb -D /usr/local/pgsql-15/data

# 3. 启动新服务器
sudo systemctl start postgresql-15

sql

-- 4. 创建相同的数据库结构
-- 方法一：使用 pg_dump 导出 schema
pg_dump -h old_server -p 5432 -U postgres -d mydb --schema-only > schema.sql
psql -h new_server -p 5433 -U postgres -d mydb -f schema.sql

-- 方法二：创建订阅时自动复制数据
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=old_server port=5432 user=replica_user password=secure_password dbname=mydb'
PUBLICATION my_publication
WITH (copy_data = true);

步骤 3：监控同步进度

sql

-- 在主服务器检查复制状态
SELECT
    slot_name,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS lag_size
FROM pg_replication_slots;

-- 在备用服务器检查订阅状态
SELECT
    subname,
    received_lsn,
    latest_end_lsn,
    latest_end_time
FROM pg_subscription_rel sr
JOIN pg_subscription s ON sr.subid = s.oid;

步骤 4：切换流程

切换注意事项切换过程中，确保没有长事务在运行，否则可能导致数据不一致。 :::

bash

# 1. 等待同步完成
# 监控脚本示例
#!/bin/bash
while true; do
    lag=$(psql -h old_server -t -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) FROM pg_replication_slots WHERE slot_name='my_subscription';" | xargs)
    echo "当前延迟: $lag"
    if [ "$lag" = "0 bytes" ]; then
        echo "同步完成，可以开始切换"
        break
    fi
    sleep 5
done

# 2. 停止应用写入
# 3. 等待最后的同步
# 4. 切换应用连接到新服务器
# 5. 停止旧服务器

金融机构实际案例

场景：某银行核心业务系统，数据库大小 5TB，从 PostgreSQL 12 升级到 14

bash

# 业务背景
# - 7x24 小时运行，几乎无维护窗口
# - 数据库：交易记录、账户信息、风控数据
# - 要求：停机时间 < 30 秒

# 实施计划 (历时 2 周)
第1周：
  - 搭建备用环境
  - 配置逻辑复制
  - 数据初始同步 (3天)

第2周：
  - 增量同步监控
  - 应用程序兼容性测试
  - 切换演练

切换日 (周六凌晨 3:00):
  1. 03:00:00 - 暂停新交易
  2. 03:00:05 - 等待复制同步
  3. 03:00:15 - 切换数据库连接
  4. 03:00:25 - 恢复交易处理

实际停机时间: 23 秒 ✅

✅ 升级后验证

1. 基础功能验证

sql

-- 检查版本
SELECT version();

-- 检查数据库列表
\l

-- 检查数据完整性
SELECT
    schemaname,
    tablename,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC
LIMIT 10;

-- 检查索引状态
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 找出未使用的索引

2. 性能验证

sql

-- 运行 ANALYZE 更新统计信息
ANALYZE;

-- 检查查询计划是否正常
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2023-01-01'
  AND status = 'completed';

-- 对比升级前后的性能指标
SELECT
    query,
    calls,
    total_time,
    mean_time,
    stddev_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

3. 应用程序兼容性测试

Python 应用测试Java 应用测试

python

import psycopg2
import time

def test_database_connection():
    """测试数据库连接和基本操作"""
    try:
        # 连接数据库
        conn = psycopg2.connect(
            host="localhost",
            database="myapp",
            user="appuser",
            password="password"
        )

        cursor = conn.cursor()

        # 测试查询
        cursor.execute("SELECT version()")
        version = cursor.fetchone()[0]
        print(f"数据库版本: {version}")

        # 测试写入
        cursor.execute("""
            INSERT INTO test_table (name, created_at)
            VALUES (%s, %s)
        """, ("升级测试", time.time()))

        conn.commit()
        print("✅ 数据库连接和操作正常")

    except Exception as e:
        print(f"❌ 数据库测试失败: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    test_database_connection()

java

import java.sql.*;

public class DatabaseUpgradeTest {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/myapp";
        String user = "appuser";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            // 测试连接
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("数据库版本: " + meta.getDatabaseProductVersion());

            // 测试查询
            PreparedStatement stmt = conn.prepareStatement(
                "SELECT COUNT(*) FROM orders WHERE status = ?"
            );
            stmt.setString(1, "pending");
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                System.out.println("待处理订单数: " + rs.getInt(1));
            }

            System.out.println("✅ 应用程序兼容性测试通过");

        } catch (SQLException e) {
            System.err.println("❌ 数据库测试失败: " + e.getMessage());
        }
    }
}

🚨 故障处理

常见问题和解决方案

1. pg_upgrade 失败

bash

# 问题：扩展版本不兼容
# 错误信息：Your installation contains the "plpgsql" extension

# 解决方案：
# 1. 检查扩展兼容性
SELECT extname, extversion FROM pg_extension;

# 2. 更新扩展到兼容版本
ALTER EXTENSION plpgsql UPDATE;

# 3. 如果扩展无法更新，先删除再重建
DROP EXTENSION IF EXISTS old_extension;
# 升级后重新安装兼容版本

2. 数据同步延迟过大

sql

-- 诊断复制延迟
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
    pg_wal_lsn_diff(sent_lsn, flush_lsn) AS receive_lag
FROM pg_stat_replication;

-- 解决方案：
-- 1. 检查网络带宽
-- 2. 调整 wal_sender 参数
-- 3. 检查磁盘 I/O 性能

3. 应用程序连接失败

sql

-- 检查连接配置
SELECT
    datname,
    usename,
    client_addr,
    state
FROM pg_stat_activity
WHERE state = 'active';

-- 常见解决方案：
-- 1. 更新连接字符串中的端口号
-- 2. 检查防火墙设置
-- 3. 验证用户权限

回滚策略

重要提醒制定回滚计划是升级过程中的关键步骤，确保在出现问题时能够快速恢复服务。 :::

bash

# pg_dumpall 方法回滚
# 1. 停止新服务器
sudo systemctl stop postgresql

# 2. 恢复旧版本
sudo rm -rf /usr/local/pgsql
sudo mv /usr/local/pgsql.old /usr/local/pgsql

# 3. 启动旧服务器
sudo systemctl start postgresql

# pg_upgrade 方法回滚
# 1. 停止新服务器
sudo systemctl stop postgresql-15

# 2. 启动旧服务器
sudo systemctl start postgresql-13

# 3. 如果使用了 --link 模式，需要注意数据文件可能已被修改

📊 升级成功案例总结

小型电商网站 (30GB)

方法：pg_dumpall
停机时间：4 小时
成本：低
成功率：99%

中型 SaaS 平台 (200GB)

方法：pg_upgrade --link
停机时间：20 分钟
成本：中等
成功率：95%

大型金融系统 (5TB)

方法：逻辑复制
停机时间：30 秒
成本：高
成功率：90%

🎯 最佳实践总结

升级最佳实践

充分测试：在生产环境升级前，在测试环境完整演练
备份策略：确保有可靠的备份和回滚方案
监控告警：升级过程中持续监控系统状态
分步执行：将复杂升级分解为多个可控步骤
文档记录：详细记录升级过程和遇到的问题 :::

通过本指南，您应该能够根据自己的业务需求选择合适的 PostgreSQL 升级方法，并安全、高效地完成升级过程。记住，升级前的准备工作往往比升级本身更重要！

PostgreSQL 集群升级完全指南 ​

📋 目录 ​

🔢 版本概念与兼容性 ​

PostgreSQL 版本命名规则 ​

版本兼容性说明 ​

业务场景中的版本选择 ​

🎯 升级前的准备工作 ​

1. 环境评估 ​

2. 兼容性检查清单 ​

3. 测试环境准备 ​

⚖️ 升级方法对比 ​

🔄 方法一：pg_dumpall 升级 ​

适用场景 ​

详细步骤 ​

步骤 1：备份数据 ​

步骤 2：停止旧服务器 ​

步骤 3：备份和安装新版本 ​

步骤 4：启动新服务器并恢复数据 ​

实际案例：电商网站升级 ​

⚡ 方法二：pg_upgrade 升级 ​

适用场景 ​

pg_upgrade 工作原理 ​

详细步骤 ​

步骤 1：准备和检查 ​

步骤 2：执行升级 ​

步骤 3：升级后优化 ​

性能对比示例 ​

🔄 方法三：复制升级 ​

适用场景 ​

逻辑复制升级流程 ​

详细实施步骤 ​

步骤 1：配置主服务器（旧版本） ​

步骤 2：设置备用服务器（新版本） ​

步骤 3：监控同步进度 ​

步骤 4：切换流程 ​

金融机构实际案例 ​

✅ 升级后验证 ​

1. 基础功能验证 ​

2. 性能验证 ​

3. 应用程序兼容性测试 ​

🚨 故障处理 ​

常见问题和解决方案 ​

1. pg_upgrade 失败 ​

2. 数据同步延迟过大 ​

3. 应用程序连接失败 ​

回滚策略 ​

📊 升级成功案例总结 ​

小型电商网站 (30GB) ​

中型 SaaS 平台 (200GB) ​

大型金融系统 (5TB) ​

🎯 最佳实践总结 ​

PostgreSQL 集群升级完全指南

📋 目录

🔢 版本概念与兼容性

PostgreSQL 版本命名规则

版本兼容性说明

业务场景中的版本选择

🎯 升级前的准备工作

1. 环境评估

2. 兼容性检查清单

3. 测试环境准备

⚖️ 升级方法对比

🔄 方法一：pg_dumpall 升级

适用场景

详细步骤

步骤 1：备份数据

步骤 2：停止旧服务器

步骤 3：备份和安装新版本

步骤 4：启动新服务器并恢复数据

实际案例：电商网站升级

⚡ 方法二：pg_upgrade 升级

适用场景

pg_upgrade 工作原理

详细步骤

步骤 1：准备和检查

步骤 2：执行升级

步骤 3：升级后优化

性能对比示例

🔄 方法三：复制升级

适用场景

逻辑复制升级流程

详细实施步骤

步骤 1：配置主服务器（旧版本）

步骤 2：设置备用服务器（新版本）

步骤 3：监控同步进度

步骤 4：切换流程

金融机构实际案例

✅ 升级后验证

1. 基础功能验证

2. 性能验证

3. 应用程序兼容性测试

🚨 故障处理

常见问题和解决方案

1. pg_upgrade 失败

2. 数据同步延迟过大

3. 应用程序连接失败

回滚策略

📊 升级成功案例总结

小型电商网站 (30GB)

中型 SaaS 平台 (200GB)

大型金融系统 (5TB)

🎯 最佳实践总结