1.pg_upgrade工具介绍
pg_upgrade 会创建新的系统表，并以重用旧的数据文件的方式进行升级。
pg_upgrade 的参数选项如下：
-b bindir，--old-bindir=bindir：旧的 PostgreSQL 可执行文件目录；
-B bindir，--new-bindir=bindir：新的 PostgreSQL 可执行文件目录；
-c，--check：只检查升级兼容性，不更改任何数据
-d configdir，--old-datadir=configdir：旧版本的数据目录
-D configdir，--new-datadir=configdir：新版本的数据目录
-j，--jobs=njobs：要同时使用的进程或线程数
-k，--link：硬链接方式升级
-o options，--old-options options：直接传送给旧 postgres 命令的选项，多个选
项可以追加在后面
-O options，--new-options options：直接传送给新 postgres 命令的选项，多个
选项可以追加在后面
-p port，--old-port=port：旧版本的端口号
-P port，--new-port=port：新版本的端口号
-r，--retain：即使在成功完成后也保留 SQL 和日志文件
在升级之前应该运行 pg_upgrade -c 检查新旧版本的兼容性，把每一项不兼容的问题都解决了才可以顺利升级。使用 pg_upgrade -c 只会检查新旧版本的兼容性，不会运行真正的升级程序，不会修改数据文件，并且在命令结束时，会输出一份检查结果的报告，还会对需要手动调整的项做出简要的描述。
pg_upgrade 有普通模式和 link 模式两种升级模式。在普通模式下，会把旧版本的数据拷贝到新版本中，所以如果使用普通升级模式，要确保有足够的磁盘空间存储新旧两份数据；
link 模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬链接，可以有效减少磁盘占用的空间。
（本文将采用普通升级模式进行升级）
2.版本信息
| 升级前 | 升级后 |
| Postgresql 16.3 | Postgresql 17.0 |
3.升级准备
安装依赖：（源环境已安装）
yum install libicu-devel.x86_64 python3 python3-devel
备份数据：
pg_dumpall -p 5432 > /tmp/backup.sql
4.安装新版pg数据库
上传安装包至/soft目录
tar -xvf postgresql-17.0.tar.gz
（1）编译安装
mkdir -p /usr/local/pg17
chown postgres:postgres /usr/local/pg17
cd postgresql-17.0
./configure --prefix=/usr/local/pg17
make
make install
（2）切换postgres用户并建新版pg数据目录
su postgres
cd /data
mkdir pg17
（3）初始化新版本pg
/usr/local/pg17/bin/initdb -D /data/pg17
（4）修改配置文件，端口改为5433
[postgres@postgres pg17]$ vi postgresql.conf
port = 5433
（5）启动数据库服务
[postgres@postgres pg17]$ /usr/local/pg17/bin/pg_ctl -D /data/pg17 -l logfile start
waiting for server to start.... done
server started
[postgres@postgres pg17]$ ps -ef|grep postgres
root 20202 10588 0 10:07 pts/0 00:00:00 su - postgres
postgres 20203 20202 0 10:07 pts/0 00:00:00 -bash
postgres 20322 1 0 10:10 ? 00:00:00 /usr/local/pg17/bin/postgres -D /data/pg17
postgres 20323 20322 0 10:10 ? 00:00:00 postgres: checkpointer
postgres 20324 20322 0 10:10 ? 00:00:00 postgres: background writer
postgres 20326 20322 0 10:10 ? 00:00:00 postgres: walwriter
postgres 20327 20322 0 10:10 ? 00:00:00 postgres: autovacuum launcher
postgres 20328 20322 0 10:10 ? 00:00:00 postgres: logical replication launcher
postgres 20329 20203 0 10:10 pts/0 00:00:00 ps -ef
postgres 20330 20203 0 10:10 pts/0 00:00:00 grep --color=auto postgres
5.停止旧版本pg数据库服务
Oct 03 09:53:53 postgres postgresql[10694]: Starting PostgreSQL: ok
[root@postgres ~]# systemctl stop postgresql
[root@postgres ~]# systemctl status postgresql
● postgresql.service - SYSV: PostgreSQL RDBMS
Loaded: loaded (/etc/rc.d/init.d/postgresql; bad; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2024-10-03 10:05:25 CST; 2s ago
Docs: man:systemd-sysv-generator(8)
Process: 19461 ExecStop=/etc/rc.d/init.d/postgresql stop (code=exited, status=1/FAILURE)
Process: 10694 ExecStart=/etc/rc.d/init.d/postgresql start (code=exited, status=0/SUCCESS)
Oct 03 09:53:53 postgres systemd[1]: Started SYSV: PostgreSQL RDBMS.
Oct 03 09:53:53 postgres postgresql[10694]: Starting PostgreSQL: ok
Oct 03 10:05:25 postgres systemd[1]: Stopping SYSV: PostgreSQL RDBMS...
Oct 03 10:05:25 postgres su[19463]: (to postgres) root on none
Oct 03 10:05:25 postgres postgresql[19461]: Stopping PostgreSQL: pg_ctl: PID file "/data/pg16/postmaster.p...xist
Oct 03 10:05:25 postgres postgresql[19461]: Is server running?
Oct 03 10:05:25 postgres systemd[1]: postgresql.service: control process exited, code=exited status=1
Oct 03 10:05:25 postgres systemd[1]: Stopped SYSV: PostgreSQL RDBMS.
Oct 03 10:05:25 postgres systemd[1]: Unit postgresql.service entered failed state.
Oct 03 10:05:25 postgres systemd[1]: postgresql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
[root@postgres ~]# systemctl disable postgresql
postgresql.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig postgresql off
6.检查新旧版本兼容性
pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/ -k -c
[postgres@postgres ~]$ pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/ -k -c
报错1：
check for "/usr/local/pg17/bin/postgres" failed: incorrect version: found "postgres (PostgreSQL) 17.0", expected "postgres (PostgreSQL) 16.3"
Failure, exiting
[postgres@postgres ~]$ which pg_upgrade
/usr/local/pg16/bin/pg_upgrade
如果看到此错误，则可能是由于运行旧版本 (16) 而不是新版本 (17) 附带的 pg_upgrade 二进制文件造成的。使用版本 17 的 pg_upgrade 二进制文件的绝对路径来运行正确的二进制文件。
报错2：
[postgres@postgres ~]$ /usr/local/pg17/bin/pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/ -k -c
There seems to be a postmaster servicing the new cluster.
Please shutdown that postmaster and try again.
Failure, exiting
[postgres@postgres ~]$
源集群没有完全关闭，关闭后再次进行检查。
报错3：
[postgres@postgres ~]$ /usr/local/pg17/bin/pg_ctl -D /data/pg17 -l logfile stop
waiting for server to shut down.... done
server stopped
[postgres@postgres ~]$
[postgres@postgres ~]$
[postgres@postgres ~]$
[postgres@postgres ~]$ /usr/local/pg17/bin/pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/ -k -c
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
/data/pg17/pg_upgrade_output.d/20241003T102958.773/loadable_libraries.txt
Failure, exiting
[postgres@postgres ~]$
[postgres@postgres pg16]$ more /data/pg17/pg_upgrade_output.d/20241003T102958.773/loadable_libraries.txt
could not load library "$libdir/pg_stat_statements": ERROR: could not access file "$libdir/pg_stat_statements":
No such file or directory
In database: postgres
[postgres@postgres pg16]$
是因为新库pg17未安装pg_stat_statements、auto_explain等源库已经安装的插件。在pg17环境进行相关插件编译安装，可参考博主其他博文。
启动数据库：
[postgres@postgres pg17]$ /usr/local/pg17/bin/pg_ctl -D /data/pg17 -l logfile start
waiting for server to start.... done
server started
[postgres@postgres pg17]$
再次检查兼容性：
[postgres@postgres ~]$ /usr/local/pg17/bin/pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/ -k -c
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
[postgres@postgres ~]$
看到以上提示表明检查通过。
7.进行正式升级
/usr/local/pg17/bin/pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/
[postgres@postgres ~]$ /usr/local/pg17/bin/pg_upgrade -b /usr/local/pg16/bin -B /usr/local/pg17/bin -d /data/pg16/ -D /data/pg17/
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates notice
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.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/local/pg17/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
[postgres@postgres ~]$
看到 Upgrade Complete 说明升级已经顺利完成。
8.更新统计信息
pg_upgrade 会创建新的系统表，并重用旧的数据进行升级，统计信息并不会随升级过程迁移，所以在启用新版本之前，应该首先重新收集统计信息，避免没有统计信息导致错误的查询计划。我们可以手动运行 vacuum 命令，如下：
vacuumdb --all --analyze-in-stages -h 127.0.0.1 -p xxx
启动新版本pg服务：
/usr/local/pg17/bin/pg_ctl -D /data/pg17 -l logfile start
[postgres@postgres pg17]$ /usr/local/pg17/bin/pg_ctl -D /data/pg17 -l logfile start
waiting for server to start.... done
server started
[postgres@postgres pg17]$
更新统计信息：
vacuumdb --all --analyze-in-stages -h 127.0.0.1 -p 5433
[postgres@postgres pg17]$ vacuumdb --all --analyze-in-stages -h 127.0.0.1 -p 5433
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
[postgres@postgres pg17]$
9.测试数据库
修改环境变量：
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/pg17/bin
export PATH
PGDATA=/data/pg17
export PGDATA
export LANG=en_US.UTF-8
修改端口5433为原来端口：
修改完重启数据库生效。
登录测试：
[postgres@postgres pg17]$ psql
psql (16.3, server 17.0)
WARNING: psql major version 16, server major version 17.
Some psql features might not work.
Type "help" for help.
postgres=#
检查扩展：
postgres=# SELECT pg_stat_statements_reset();
ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"
postgres=# CREATE EXTENSION PG_STAT_STATEMENTS;
ERROR: extension "pg_stat_statements" already exists
postgres=#
发现是参数文件问题：
修改新库配置文件和源库一致：
shared_preload_libraries ='auto_explain,pg_stat_statements'
#以下pg_stat_statements相关配置
track_io_timing = on
track_activity_query_size = 2048
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on
##auto_explain####
auto_explain.log_min_duration = '100ms' # 或其他时间阈值，例如'sec', 'min'
auto_explain.log_analyze = on # 输出实际的执行统计信息
auto_explain.log_verbose = on # 输出详细的计划信息
auto_explain.log_timing = on # 输出查询的执行时间
auto_explain.log_nested_statements = off # 默认情况下不记录嵌套的解释计划，可根据需要开启
重启数据库后查看：
postgres=# show shared_preload_libraries;
shared_preload_libraries
---------------------------------
auto_explain,pg_stat_statements
(1 row)
postgres=# select name,setting from pg_settings where name like 'auto_explain%';
name | setting
---------------------------------------+---------
auto_explain.log_analyze | on
auto_explain.log_buffers | off
auto_explain.log_format | text
auto_explain.log_level | log
auto_explain.log_min_duration | 100
auto_explain.log_nested_statements | off
auto_explain.log_parameter_max_length | -1
auto_explain.log_settings | off
auto_explain.log_timing | on
auto_explain.log_triggers | off
auto_explain.log_verbose | on
auto_explain.log_wal | off
auto_explain.sample_rate | 1
(13 rows)
postgres=#
至此，整个16.3升级17.0完成。
10.新库确认没问题后删除旧库
[postgres@postgres ~]$ ./delete_old_cluster.sh
[postgres@postgres ~]$ ll
total 16
-rwx------. 1 postgres postgres 31 Oct 3 10:55 delete_old_cluster.sh
-rw-------. 1 postgres postgres 1741 Jun 27 07:08 logfile
-rw-rw-r--. 1 postgres postgres 19 Jul 7 08:43 test_t1.txt
-rw-------. 1 postgres postgres 63 Oct 3 10:55 update_extensions.sql
[postgres@postgres ~]$ more delete_old_cluster.sh
#!/bin/sh
rm -rf '/data/pg16'
[postgres@postgres ~]$ cd /data
[postgres@postgres data]$ ll
total 4
drwx------. 20 postgres postgres 4096 Oct 3 11:00 pg17
[postgres@postgres data]$