PostgreSQL 升级步骤

升级步骤概述

PostgreSQL升级步骤根据选择的升级方式不同而有所差异。本文档将详细介绍三种主要升级方式的操作步骤：pg_upgrade、逻辑备份恢复和复制升级。每种方式都有其适用场景和注意事项，DBA需要根据实际情况选择合适的升级方式。

pg_upgrade升级步骤

pg_upgrade是PostgreSQL官方提供的快速升级工具，适用于支持直接升级路径的版本。以下是使用pg_upgrade进行升级的详细步骤：

1. 准备工作

1.1 安装目标版本

bash

# 在Ubuntu/Debian上安装PostgreSQL 14
sudo apt-get update
sudo apt-get install postgresql-14

# 在CentOS/RHEL上安装PostgreSQL 14
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14 postgresql14-server postgresql14-contrib

1.2 初始化目标版本数据库

bash

# 初始化PostgreSQL 14数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

1.3 停止目标版本服务

bash

# 停止PostgreSQL 14服务
sudo systemctl stop postgresql-14

1.4 配置目标版本

bash

# 复制当前版本的配置文件到目标版本
cp /var/lib/postgresql/13/main/postgresql.conf /var/lib/postgresql/14/main/
cp /var/lib/postgresql/13/main/pg_hba.conf /var/lib/postgresql/14/main/
cp /var/lib/postgresql/13/main/pg_ident.conf /var/lib/postgresql/14/main/

# 调整目标版本的配置参数
vi /var/lib/postgresql/14/main/postgresql.conf
# 修改数据目录、端口等参数（如果需要）

1.5 运行预检查

bash

# 使用pg_upgrade --check进行预检查
/usr/pgsql-14/bin/pg_upgrade --check \
  --old-datadir=/var/lib/postgresql/13/main \
  --new-datadir=/var/lib/postgresql/14/main \
  --old-bindir=/usr/lib/postgresql/13/bin \
  --new-bindir=/usr/pgsql-14/bin

2. 执行升级

2.1 停止源版本服务

bash

# 停止PostgreSQL 13服务
sudo systemctl stop postgresql-13

2.2 执行pg_upgrade

bash

# 创建日志目录
mkdir -p /var/log/postgresql/upgrade

# 执行pg_upgrade升级（使用4个并行进程）
/usr/pgsql-14/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/13/main \
  --new-datadir=/var/lib/postgresql/14/main \
  --old-bindir=/usr/lib/postgresql/13/bin \
  --new-bindir=/usr/pgsql-14/bin \
  --jobs=4 \
  --logfile=/var/log/postgresql/upgrade/pg_upgrade.log

2.3 清理旧版本

bash

# 运行pg_upgrade提供的清理脚本
sudo -u postgres ./delete_old_cluster.sh

3. 升级后配置

3.1 重建扩展

bash

# 启动目标版本服务
sudo systemctl start postgresql-14

# 连接到目标版本
sudo -u postgres psql

# 重建扩展（如果需要）
ALTER EXTENSION extension_name UPDATE;

3.2 更新统计信息

bash

# 更新所有数据库的统计信息
sudo -u postgres vacuumdb --all --analyze-in-stages

3.3 重建全文搜索词典（如果需要）

bash

# 重建全文搜索词典
sudo -u postgres psql -c "SELECT ts_restore_dict();"

4. 验证升级

4.1 检查版本

bash

# 检查PostgreSQL版本
sudo -u postgres psql -c "SELECT version();"

4.2 检查数据库对象

bash

# 检查数据库列表
sudo -u postgres psql -l

# 检查表列表
sudo -u postgres psql -c "\dt" database_name

# 检查扩展列表
sudo -u postgres psql -c "SELECT extname, extversion FROM pg_extension;"

4.3 测试应用连接

测试应用程序与新版本的连接
测试应用程序的核心功能
测试第三方工具与新版本的兼容性

逻辑备份恢复升级步骤

逻辑备份恢复是使用pg_dump/pg_dumpall和pg_restore工具进行升级，适用于跨多个主版本、跨平台或跨硬件架构升级。以下是使用逻辑备份恢复进行升级的详细步骤：

1. 准备工作

1.1 安装目标版本

bash

# 安装目标版本（同pg_upgrade步骤）
sudo apt-get install postgresql-14

1.2 初始化目标版本数据库

bash

# 初始化目标版本数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

1.3 配置目标版本

bash

# 配置目标版本（同pg_upgrade步骤）
vi /var/lib/postgresql/14/main/postgresql.conf
vi /var/lib/postgresql/14/main/pg_hba.conf

1.4 启动目标版本服务

bash

# 启动目标版本服务
sudo systemctl start postgresql-14

2. 备份源版本

2.1 选择备份方式

根据数据库规模和需求选择合适的备份方式：

对于小型数据库：使用pg_dumpall进行全库备份
对于大型数据库：使用pg_dump进行并行备份
对于特定数据库：使用pg_dump备份单个数据库

2.2 执行备份

使用pg_dumpall备份所有数据库

bash

# 备份所有数据库
sudo -u postgres pg_dumpall -f /path/to/dumpall.sql

# 压缩备份
sudo -u postgres pg_dumpall | gzip > /path/to/dumpall.sql.gz

使用pg_dump并行备份单个数据库

bash

# 并行备份单个数据库（4个并行进程，自定义格式）
sudo -u postgres pg_dump -d database_name -Fd -j 4 -f /path/to/database_dump

# 压缩备份
sudo -u postgres pg_dump -d database_name | gzip > /path/to/database.sql.gz

2.3 验证备份

bash

# 检查备份文件的完整性
pg_restore -l /path/to/database_dump > /dev/null

3. 恢复到目标版本

3.1 恢复所有数据库

bash

# 恢复所有数据库
sudo -u postgres psql -f /path/to/dumpall.sql postgres

# 从压缩文件恢复
gunzip -c /path/to/dumpall.sql.gz | sudo -u postgres psql postgres

3.2 并行恢复单个数据库

bash

# 并行恢复单个数据库（4个并行进程）
sudo -u postgres pg_restore -d database_name -j 4 /path/to/database_dump

# 从压缩文件恢复
gunzip -c /path/to/database.sql.gz | sudo -u postgres psql database_name

3.3 创建数据库用户（如果需要）

bash

# 创建数据库用户
sudo -u postgres psql -c "CREATE USER username WITH PASSWORD 'password';"

# 授予权限
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE database_name TO username;"

4. 恢复后配置

4.1 更新统计信息

bash

# 更新所有数据库的统计信息
sudo -u postgres vacuumdb --all --analyze-in-stages

4.2 重建扩展

bash

# 重建扩展（如果需要）
sudo -u postgres psql -c "ALTER EXTENSION extension_name UPDATE;"

4.3 调整配置参数

根据目标版本的最佳实践调整配置参数，如shared_buffers、work_mem、maintenance_work_mem等。

5. 验证恢复

5.1 检查版本和数据库

bash

# 检查PostgreSQL版本
sudo -u postgres psql -c "SELECT version();"

# 检查数据库列表
sudo -u postgres psql -l

5.2 检查数据完整性

bash

# 检查表行数
sudo -u postgres psql -c "SELECT COUNT(*) FROM table_name;" database_name

# 执行简单查询测试
sudo -u postgres psql -c "SELECT * FROM table_name LIMIT 10;" database_name

5.3 测试应用连接

测试应用程序与新版本的连接
测试应用程序的核心功能
验证业务数据的完整性

复制升级步骤

复制升级是利用PostgreSQL的复制功能进行升级，适用于对业务中断时间非常敏感的场景。以下是使用复制升级进行升级的详细步骤：

1. 准备工作

1.1 安装目标版本

bash

# 在从库服务器上安装PostgreSQL 14
sudo apt-get install postgresql-14

1.2 配置源版本的复制参数

bash

# 在源版本（旧版本）上配置复制参数
vi /var/lib/postgresql/13/main/postgresql.conf
# 设置以下参数
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# 配置pg_hba.conf，允许从库连接
vi /var/lib/postgresql/13/main/pg_hba.conf
# 添加以下行
host replication replication_user  slave_ip/32 md5

# 重启源版本服务
sudo systemctl restart postgresql-13

1.3 创建复制用户

bash

# 在源版本上创建复制用户
sudo -u postgres psql -c "CREATE USER replication_user WITH REPLICATION PASSWORD 'password';"

2. 搭建从库

2.1 初始化从库数据库

bash

# 初始化从库数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

2.2 配置从库连接到源版本

bash

# 配置从库连接参数
vi /var/lib/postgresql/14/main/postgresql.conf
# 设置以下参数
primary_conninfo = 'host=master_ip port=5432 user=replication_user password=password'

# 创建standby.signal文件，表示这是一个从库
touch /var/lib/postgresql/14/main/standby.signal

# 配置从库的pg_hba.conf（同主库）
cp /var/lib/postgresql/13/main/pg_hba.conf /var/lib/postgresql/14/main/

2.3 启动从库服务

bash

# 启动从库服务
sudo systemctl start postgresql-14

3. 验证复制

3.1 检查主库复制状态

bash

# 在主库上检查复制状态
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

3.2 检查从库复制状态

bash

# 在从库上检查复制状态
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -c "SELECT pg_last_wal_receive_lsn();"
sudo -u postgres psql -c "SELECT pg_last_wal_replay_lsn();"
sudo -u postgres psql -c "SELECT pg_last_xact_replay_timestamp();"

3.3 等待复制完成

bash

# 等待从库复制到主库的最新位置
# 在主库上获取当前LSN
sudo -u postgres psql -c "SELECT pg_current_wal_lsn();"

# 在从库上检查是否已经复制到该LSN
sudo -u postgres psql -c "SELECT pg_last_wal_replay_lsn();"

4. 切换主备

4.1 准备切换

通知相关团队和业务方
暂停写操作（如果可能）
确保从库已经复制到主库的最新位置

4.2 提升从库为主库

bash

# 在从库上执行提升操作
sudo -u postgres /usr/pgsql-14/bin/pg_ctl promote -D /var/lib/postgresql/14/main

# 验证从库是否已经提升为主库
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

4.3 更新应用连接配置

更新应用程序的数据库连接配置，指向新的主库
测试应用程序与新主库的连接

4.4 调整新主库配置

bash

# 调整新主库的配置参数，如wal_level、max_wal_senders等
vi /var/lib/postgresql/14/main/postgresql.conf

# 重启新主库服务（如果需要）
sudo systemctl restart postgresql-14

5. 处理原主库

5.1 原主库作为从库

如果需要将原主库作为新主库的从库：

bash

# 清理原主库的数据目录
sudo systemctl stop postgresql-13
rm -rf /var/lib/postgresql/13/main/*

# 从新主库创建基础备份
pg_basebackup -D /var/lib/postgresql/13/main -c fast -Fp -Xs -v -P -U replication_user -h new_master_ip

# 配置原主库作为从库
vi /var/lib/postgresql/13/main/postgresql.conf
# 设置primary_conninfo指向新主库

# 创建standby.signal文件
touch /var/lib/postgresql/13/main/standby.signal

# 启动原主库服务
sudo systemctl start postgresql-13

5.2 原主库退役

如果原主库不再需要：

bash

# 停止原主库服务
sudo systemctl stop postgresql-13
sudo systemctl disable postgresql-13

# 卸载原主库软件
sudo apt-get remove postgresql-13

6. 验证切换

6.1 检查新主库状态

bash

# 检查新主库状态
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

6.2 测试应用功能

测试应用程序的核心功能
测试写入和读取操作
验证业务数据的完整性

6.3 监控系统性能

监控新主库的CPU、内存、磁盘使用情况
监控查询响应时间
监控连接数和锁等待

不同PostgreSQL版本的升级步骤差异

PostgreSQL 9.x

pg_upgrade步骤差异

使用pg_resetxlog而非pg_resetwal（如果需要重置WAL）
不支持并行升级功能
扩展兼容性较差，可能需要重新编译

逻辑备份恢复步骤差异

逻辑备份工具功能相对简单，不支持并行备份和恢复
某些数据类型可能需要特殊处理

复制升级步骤差异

复制功能相对简单，不支持逻辑复制
需要使用recovery.conf文件而非standby.signal

PostgreSQL 10+

pg_upgrade步骤差异

支持并行升级功能
支持pg_resetwal（pg_resetxlog作为别名）
增强了扩展兼容性

逻辑备份恢复步骤差异

支持并行备份和恢复
支持更多数据类型和功能

复制升级步骤差异

支持逻辑复制
使用standby.signal文件而非recovery.conf

PostgreSQL 12+

pg_upgrade步骤差异

增强了并行升级功能
支持更多扩展
改进了升级日志和错误信息

逻辑备份恢复步骤差异

支持并行备份和恢复的更多选项
支持分区表的更高效备份和恢复

复制升级步骤差异

增强了逻辑复制功能
支持更多复制选项

PostgreSQL 14+

pg_upgrade步骤差异

增强了兼容性检查
改进了升级性能
支持更多扩展和功能

逻辑备份恢复步骤差异

支持更多数据类型和功能
增强了备份和恢复的安全性

复制升级步骤差异

增强了复制监控和管理功能
支持更多复制选项

升级步骤最佳实践

1. 详细规划

制定详细的升级计划，包括每个步骤的时间、责任人、验证方法
考虑所有可能的风险和应对措施
准备回滚方案

2. 充分测试

在测试环境中完整测试升级步骤
测试升级、回滚和验证的整个流程
测试应用程序与新版本的兼容性

3. 备份优先

升级前进行完整的备份
确保备份的完整性和可用性
遵循3-2-1备份原则

4. 逐步执行

严格按照计划执行升级步骤
每个步骤完成后进行验证
遇到问题及时停止，分析原因后再继续

5. 密切监控

升级过程中密切监控系统状态
记录升级过程中的所有操作和输出
升级后进行全面的监控和验证

6. 沟通协调

与业务方和相关团队充分沟通
提前通知升级时间和影响范围
升级过程中及时通报进度和问题

7. 回滚准备

无论选择哪种升级方式，都要准备回滚方案
在测试环境中测试回滚方案
确保回滚过程快速、可靠

总结

PostgreSQL升级步骤根据选择的升级方式不同而有所差异。pg_upgrade适合大型数据库和需要快速升级的场景，逻辑备份恢复适合跨多个主版本、跨平台或跨硬件架构升级，复制升级适合对业务中断时间非常敏感的场景。

不同PostgreSQL版本的升级步骤也存在差异，DBA需要根据实际版本调整升级步骤。遵循升级步骤最佳实践，可以提高升级的成功率，减少业务影响，确保数据库的可靠性和可用性。

在执行升级操作时，DBA需要保持谨慎，严格按照计划执行，密切监控系统状态，确保升级过程顺利完成。

PostgreSQL 升级步骤 ​

升级步骤概述 ​

pg_upgrade升级步骤 ​

1. 准备工作 ​

1.1 安装目标版本 ​

1.2 初始化目标版本数据库 ​

1.3 停止目标版本服务 ​

1.4 配置目标版本 ​

1.5 运行预检查 ​

2. 执行升级 ​

2.1 停止源版本服务 ​

2.2 执行pg_upgrade ​

2.3 清理旧版本 ​

3. 升级后配置 ​

3.1 重建扩展 ​

3.2 更新统计信息 ​

3.3 重建全文搜索词典（如果需要） ​

4. 验证升级 ​

4.1 检查版本 ​

4.2 检查数据库对象 ​

4.3 测试应用连接 ​

逻辑备份恢复升级步骤 ​

1. 准备工作 ​

1.1 安装目标版本 ​

1.2 初始化目标版本数据库 ​

1.3 配置目标版本 ​

1.4 启动目标版本服务 ​

2. 备份源版本 ​

2.1 选择备份方式 ​

2.2 执行备份 ​

使用pg_dumpall备份所有数据库 ​

使用pg_dump并行备份单个数据库 ​

2.3 验证备份 ​

3. 恢复到目标版本 ​

3.1 恢复所有数据库 ​

3.2 并行恢复单个数据库 ​

3.3 创建数据库用户（如果需要） ​

4. 恢复后配置 ​

4.1 更新统计信息 ​

4.2 重建扩展 ​

4.3 调整配置参数 ​

5. 验证恢复 ​

5.1 检查版本和数据库 ​

5.2 检查数据完整性 ​

5.3 测试应用连接 ​

复制升级步骤 ​

1. 准备工作 ​

1.1 安装目标版本 ​

1.2 配置源版本的复制参数 ​

1.3 创建复制用户 ​

2. 搭建从库 ​

2.1 初始化从库数据库 ​

2.2 配置从库连接到源版本 ​

2.3 启动从库服务 ​

3. 验证复制 ​

3.1 检查主库复制状态 ​

3.2 检查从库复制状态 ​

3.3 等待复制完成 ​

4. 切换主备 ​

4.1 准备切换 ​

4.2 提升从库为主库 ​

4.3 更新应用连接配置 ​

4.4 调整新主库配置 ​

5. 处理原主库 ​

5.1 原主库作为从库 ​

5.2 原主库退役 ​

6. 验证切换 ​

6.1 检查新主库状态 ​

6.2 测试应用功能 ​

6.3 监控系统性能 ​

不同PostgreSQL版本的升级步骤差异 ​

PostgreSQL 9.x ​

pg_upgrade步骤差异 ​

逻辑备份恢复步骤差异 ​

复制升级步骤差异 ​

PostgreSQL 10+ ​

pg_upgrade步骤差异 ​

逻辑备份恢复步骤差异 ​

复制升级步骤差异 ​

PostgreSQL 12+ ​

PostgreSQL 升级步骤

升级步骤概述

pg_upgrade升级步骤

1. 准备工作

1.1 安装目标版本

1.2 初始化目标版本数据库

1.3 停止目标版本服务

1.4 配置目标版本

1.5 运行预检查

2. 执行升级

2.1 停止源版本服务

2.2 执行pg_upgrade

2.3 清理旧版本

3. 升级后配置

3.1 重建扩展

3.2 更新统计信息

3.3 重建全文搜索词典（如果需要）

4. 验证升级

4.1 检查版本

4.2 检查数据库对象

4.3 测试应用连接

逻辑备份恢复升级步骤

1. 准备工作

1.1 安装目标版本

1.2 初始化目标版本数据库

1.3 配置目标版本

1.4 启动目标版本服务

2. 备份源版本

2.1 选择备份方式

2.2 执行备份

使用pg_dumpall备份所有数据库

使用pg_dump并行备份单个数据库

2.3 验证备份

3. 恢复到目标版本

3.1 恢复所有数据库

3.2 并行恢复单个数据库

3.3 创建数据库用户（如果需要）

4. 恢复后配置

4.1 更新统计信息

4.2 重建扩展

4.3 调整配置参数

5. 验证恢复

5.1 检查版本和数据库

5.2 检查数据完整性

5.3 测试应用连接

复制升级步骤

1. 准备工作

1.1 安装目标版本

1.2 配置源版本的复制参数

1.3 创建复制用户

2. 搭建从库

2.1 初始化从库数据库

2.2 配置从库连接到源版本

2.3 启动从库服务

3. 验证复制

3.1 检查主库复制状态

3.2 检查从库复制状态

3.3 等待复制完成

4. 切换主备

4.1 准备切换

4.2 提升从库为主库

4.3 更新应用连接配置

4.4 调整新主库配置

5. 处理原主库

5.1 原主库作为从库

5.2 原主库退役

6. 验证切换

6.1 检查新主库状态

6.2 测试应用功能

6.3 监控系统性能

不同PostgreSQL版本的升级步骤差异

PostgreSQL 9.x

pg_upgrade步骤差异

逻辑备份恢复步骤差异

复制升级步骤差异

PostgreSQL 10+

pg_upgrade步骤差异

逻辑备份恢复步骤差异

复制升级步骤差异

PostgreSQL 12+