PostgreSQL 数据库版本升级实战

PostgreSQL 版本发布规则，一年一个大版本，一个季度一个小版本；PG 遇到的 BUG 问题，社区会很快进行修复，并在下一个版本中发布，因此有必要进行对数据库版本升级，避免触发已知的 BUG 带来业务系统的不稳定。本文主要分享一下，PG 小版本升级、跨版本升级和借助逻辑复制进行数据库升级。

1. PG 小版本升级

PG 小版本的迭代不会改变内部的存储格式，因此升级小版本只需要安装最新版本后，使用新版本重新启动数据库即可。

停止数据库实例

对数据目录进行备份

安装最新版本数据库

使用新版本启动数据库

调整环境变量，PGHOME/LD_LIRARAY_PATH 等

注意：在安装新版本数据库时，要保证数据库块大小一致。

2. PG 跨版本升级

大版本的升级方法主要有如下三种：

1. 使用 pg_dumpall / pg_restore 进行升级
2. 使用 pg_upgrade 进行升级
3. 使用逻辑复制进行升级

2.1. 使用 pg_dumpall 和 pg_dump 进行逻辑转储恢复升级

此方案的原理是使用 pg_dumpall 命令将旧数据库的全部 dump 成 sql 文件，然后使用 psql 命令还原至高版本的实例中。借助于管道，可以实现在线升级。配合脚本的话，切换至高版本几乎零宕机。由于 dump+restore 总体相对耗时较长，因此不适用于大数据量的数据库，或是写入比较频繁的场景使用。

2.1.1. 备份数据库

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d dbname -c -C -f dbname_bak_`date "+%Y%m%d%H%M%S"`.sql

压缩备份文件

gzip dbname_bak_xxxxxx.sql

2.1.2. 删除数据库

## 停止服务
sudo systemctl stop postgresql-14
## 删除服务
sudo systemctl disable postgresql-14


## 卸载软件
sudo dnf remove postgresql14-server postgresql14-libs; sudo dnf autoremove ; sudo dnf clean packages

## 清除数据

sudo rm -rf /usr/pgsql-14/

sudo rm -rf /var/lib/pgsql/14

2.1.3. 安装高版本数据库

sudo dnf install -y postgresql15-server

2.1.4. 配置数据库

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

2.1.5. 启动 PostgreSQL 服务

# 设置PostgreSQL服务为开机启动, 并立即启动postgresql服务
sudo systemctl enable --now postgresql-15

# 查看postgresql的状态
systemctl status postgresql-15

2.1.6. 修改 postgres 账号密码

进入 PostgreSQL 命令行

通过 su 命令切换 linux 用户为 postgres 会自动进入命令行

sudo bash
su - postgres

启动 SQL Shell

psql

修改密码

ALTER USER postgres WITH PASSWORD 'NewPassword';

2.1.7. 修改 IP 绑定

#修改配置文件
sudo vi /var/lib/pgsql/15/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

2.1.8. 允许所有 IP 访问

#修改配置文件
sudo vi /var/lib/pgsql/15/data/pg_hba.conf

#在问号尾部加入
host    all             all             0.0.0.0/0               md5
# 或者只允许局域网内的用户访问
host    all             all             192.168.1.0/24          md5

2.1.9. 重启 PostgreSQL 服务

#重启PostgreSQL服务
sudo systemctl restart postgresql-15

2.1.10. 恢复数据

恢复方式很简单，执行恢复命令即可：

# 普通用户权限即可
psql -h 127.0.0.1 -p 5432 -U postgres -f db_bak.sql

2.2. 使用 pg_upgrade 进行升级

pg_upgrade 是官方提供的版本升级工具，有普通模式和 Link 模式两种升级模式。在普通模式下，会把旧版本的数据拷贝到新版本中，需要确保有足够的磁盘空间存储新旧两份数据；Link 模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬连接，可以有效减少磁盘占用的空间。

在升级之前建议使用pg_upgrade --check参数检查新旧版本的兼容性，把每一项不兼容的问题解决后才可以顺利升级，-c 参数只会检查新旧版本兼容性，不会运行真正的升级程序，不会修改数据文件，在运行结束后，会输出兼容性结果。

2.2.1. 首先安装新版本数据库

sudo dnf install -y postgresql15-server

2.2.2. 初始化新数据库

/usr/pgsql-15/bin/initdb --version

/usr/pgsql-15/bin/initdb --data-checksums -D /var/lib/pgsql/15

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

2.2.3. 然后检查新旧版本兼容性

su - postgres

export PGBINOLD=/usr/pgsql-14/bin
export PGBINNEW=/usr/pgsql-15/bin
export PGDATAOLD=/var/lib/pgsql/14
export PGDATANEW=/var/lib/pgsql/15

/usr/pgsql-15/bin/pg_upgrade --check

运行以上命令会生成在两个 Postgres 集群之间发现的任何不兼容性的详细报告。我们希望在底部看到以下消息：

*Clusters are compatible*

postgres@fedora:~$ /usr/pgsql-15/bin/pg_upgrade -c
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     成功
Checking database user is the install user                    成功
Checking database connection settings                         成功
Checking for prepared transactions                            成功
Checking for system-defined composite types in user tables    成功
Checking for reg* data types in user tables                   成功
Checking for contrib/isn with bigint-passing mismatch         成功
Checking for incompatible "aclitem" data type in user tables  成功
Checking for presence of required libraries                   成功
Checking database user is the install user                    成功
Checking for prepared transactions                            成功
Checking for new cluster tablespace directories               成功

*Clusters are compatible*

2.2.4. 备份数据

如果您使用 streaming replication，这些 replication 也需要升级。坏消息是我们无法在副本服务器上运行 pg_upgrade。好消息是升级它们的过程很棘手，但速度很快。它们的升级发生在主服务器之后，但这是准备工作的绝佳时机。对于每个副本，您需要安装新的 Postgres 软件，就像我们上面所做的那样，但不要运行 initdb。将每个副本上数据目录中存在的所有配置文件复制到该目录之外的安全位置。

pg_dumpall -h 127.0.0.1 -p 5432 -U postgres  -c  -C -f  dbname_`date "+%Y%m%d%H%M%S"`.sql
 

2.2.5. 正式开始升级

pg_upgrade –check 完成后，我们就可以进行实际升级了。确保使用–linkpg_upgrade 的参数非常重要。这样做可以避免将所有实际数据文件从旧集群复制到新集群。相反，它会在旧数据文件和新数据文件之间创建硬链接（而不是更常见的符号链接）。这样做会告诉操作系统每个数据文件都有两个完全有效的路径来访问该文件。而且，与复制文件内容相比，它的速度非常快。命令如下所示：

## This assumes the ENVs from above were exported
/usr/pgsql-15/bin/pg_upgrade --link

pg_upgrade 命令的输出将类似于以下内容：

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /home/greg/pg/15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

2.2.6. 复制并调整配置文件

现在新的集群已经到位，在我们将应用程序连接到它之前还需要几个步骤。

新集群将具有默认的 postgresql.conf、pg_hba.conf 和 pg_ident.conf 文件，这不是您想要的。从旧集群复制 pg_hba.conf 和 pg_ident.conf 文件。它们很可能无需任何修改即可工作，因为这些文件的格式很少改变。接下来，您需要将旧的 postgresql.conf 文件与新文件合并。如果您习惯在 postgresql.conf 文件底部进行更改，则可以轻松地复制并粘贴它们。无论哪种方式，您都应该仔细检查并确保所有设置仍然适用于新集群。不要忘记 postgresql.auto.conf 或“include*”选项引用的任何 conf 文件。

2.2.7. 启用校验和

如果您的新集群未启用页面级校验和，那么现在是启用它们的好时机。Postgres 附带一个程序，用于将您的集群更改为使用页面级校验和，名为 pg_checksums。它将与我们上面使用的 initdb 位于同一目录中，用法如下：

/usr/pgsql-15/bin/pg_checksums -D $PGDATANEW --enable --verbose

2.2.8. 同步您的副本

对于这一部分，我将向您推荐官方文档的 step 11 []”Upgrade streaming replication and log-shipping standby servers”](https://www.postgresql.org/docs/current/pgupgrade.html) 。严格按照 rsync 配方操作。将来可能会有更好的方法，所以我宁愿在这里留下一个链接，也不愿复制那个大 rsync 命令并在这里打印出来。:)

rsync 进程非常快，因为它只复制已更改的文件。它会比较主服务器上的新旧目录，然后与副本进行比较。与以前一样，此进程的速度取决于文件数量。您可以同时升级副本。

2.2.9. 启动并生成统计数据

现在副本已准备就绪，您可以启动主服务器。如果您没有使用 pg_ctl，而是使用 systemd 之类的东西，则可能需要调整一些配置文件或脚本以指向新的数据目录。如果集群启动时没有错误，那么恭喜您！您几乎拥有一个正常工作的集群。但是，如果没有关于表的良好统计数据，Postgres 就无法生成良好的查询计划。截至撰写本文时，Postgres 不会将数据库统计数据从旧集群复制到新集群。这种情况会在某个时候改变，但在此之前，您需要自己生成统计数据。正如我们上面看到的 pg_upgrade 输出所述，您可以分阶段执行此操作以尽可能快地生成统计数据：

/usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages

如果您不需要分阶段进行，您可以简单地运行：

/usr/pgsql-15/bin/vacuumdb --all --analyze-only

2.2.10. 升级扩展

Postgres 扩展有版本，可能需要自行升级。升级方式是登录新数据库并发出：

ALTER EXTENSION foobar UPDATE;

如果 pg_upgrade 能够检测到扩展，您将看到如下消息：

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

该文件应包含命令，但您也可以通过运行以下命令生成可能需要升级的扩展列表：

SELECT * FROM pg_available_extensions WHERE default_version <> installed_version;

成功升级将不会返回任何消息，而不需要的升级将会有提示消息：

alter extension hstore update;
ALTER EXTENSION

alter extension hstore update;
NOTICE:  version "1.8" of extension "hstore" is already installed
ALTER EXTENSION

2.2.11. 升级监控和工具

不要忘记更新与数据库交互的所有内容，并告诉它您使用的是新的主要版本。您可能还会有一个新的数据目录，某些工具需要知道它。请记住，主要升级意味着新的集群、新的系统标识符以及新的统计数据。

2.2.12. 再次备份

现在是运行另一次备份的好时机，这次是针对新版本。

pgbackrest backup --stanza=mystanza --type=full

2.2.13. 删除旧集群

一切运行良好一段时间后，您可以删除旧集群。在运行结束时，pg_upgrade 会创建一个小型 shell 脚本来帮助完成此操作。此脚本非常简单，并且执行“rm -fr $OLDDATADIR”。最好运行该脚本，无论它看起来多么简单，而不是冒着手滑并删除错误的数据目录的风险！

2.2.14. 为下一次升级做好准备

最后一项任务 - 为下一次升级做好准备！Postgres 每年都会发布一个新的主要版本。因此，每年或每隔一年升级一次主要版本是一个好的解决方案。大约每三个月就会发布一个新的次要版本。您希望立即安装这些版本，因为它们只包含关键的错误修复，并且比上述过程更容易安装！

3. 参考文档

Linux 上安装 PostgreSQL

PostgreSQL 备份与恢复

PostgreSQL 数据库版本升级

pgupgrade

Examining Postgres Upgrades with pg_upgrade