CNDBA社区
在之前的博客我们介绍了PG的生命周期，如下：
PostgreSQL 学习笔记（2） — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372
从PG 10 开始，PG 每年会发布一个大版本（major version），每个季度会发布一个小版本（minor release）。PG 大版本不保持向后兼容性，所以升级需要使用pg_upgrade命令来导出导入数据。 小版本升级相对比较简单，直接替换bin文件即可。
也了解了PG小版本升级的操作步骤：
PostgreSQL 小版本升级（13.3 到 13.9 ） 操作手册
https://www.cndba.cn/dave/article/116399
本篇我们看一下13.9 升级到14.6 的操作过程。
1 升级方法说明
大版本的升级方法主要有如下三种：
- 使用 pg_dumpall / pg_restore 进行升级
- 使用 pg_upgrade 进行升级
- 使用逻辑复制进行升级
每种方法都有自己的优缺点。 比如逻辑复制切换时间短，但只支持表级同步，函数和DDL 不支持，备份恢复操作简单，但对于大库，停机时间就会比较长。
本篇我们只看pg_upgrade的方法，pg_upgrade通过创建新的系统表和重用旧的用户数据文件来执行快速升级。
2 安装新14.6 软件
在之前的博客我们了解的PG的安装，如下：
PostgreSQL 学习笔记（3） — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374
因为我们这里的14.6 是最新版，所以我们直接yum 安装了。
[dave@www.cndba.cn 13.9]# yum install -y postgresql14-server
[dave@www.cndba.cn pgsql-14]# pwd
/usr/pgsql-14
[dave@www.cndba.cn pgsql-14]# ll
total 12
drwxr-xr-x 2 root root 4096 Dec 17 12:36 bin
drwxr-xr-x 3 root root 4096 Dec 17 12:36 lib
drwxr-xr-x 7 root root 4096 Dec 17 12:36 share
[dave@www.cndba.cn pgsql-14]#
初始化实例，但不启动：
[dave@www.cndba.cn bin]# su - postgres
Last login: Sat Dec 17 12:04:07 CST 2022 on pts/4
-bash-4.2$ cd /usr/pgsql-14/bin
-bash-4.2$ ./pg_ctl init -D /data/postgresql/data14
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /data/postgresql/data14 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-14/bin/pg_ctl -D /data/postgresql/data14 -l logfile start
-bash-4.2$
3 在新版本下执行pg_upgrade检查
3.1 pg_upgrade 帮助
-bash-4.2$ ./pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.
Usage:
pg_upgrade [OPTION]...
Options:
-b, --old-bindir=BINDIR old cluster executable directory
-B, --new-bindir=BINDIR new cluster executable directory (default
same directory as pg_upgrade)
-c, --check check clusters only, don't change any data
-d, --old-datadir=DATADIR old cluster data directory
-D, --new-datadir=DATADIR new cluster data directory
-j, --jobs=NUM number of simultaneous processes or threads to use
-k, --link link instead of copying files to new cluster
-o, --old-options=OPTIONS old cluster options to pass to the server
-O, --new-options=OPTIONS new cluster options to pass to the server
-p, --old-port=PORT old cluster port number (default 50432)
-P, --new-port=PORT new cluster port number (default 50432)
-r, --retain retain SQL and log files after success
-s, --socketdir=DIR socket directory to use (default current dir.)
-U, --username=NAME cluster superuser (default "postgres")
-v, --verbose enable verbose internal logging
-V, --version display version information, then exit
--clone clone instead of copying files to new cluster
-?, --help show this help, then exit
Before running pg_upgrade you must:
create a new database cluster (using the new version of initdb)
shutdown the postmaster servicing the old cluster
shutdown the postmaster servicing the new cluster
When you run pg_upgrade, you must provide the following information:
the data directory for the old cluster (-d DATADIR)
the data directory for the new cluster (-D DATADIR)
the "bin" directory for the old version (-b BINDIR)
the "bin" directory for the new version (-B BINDIR)
For example:
pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
or
$ export PGDATAOLD=oldCluster/data
$ export PGDATANEW=newCluster/data
$ export PGBINOLD=oldCluster/bin
$ export PGBINNEW=newCluster/bin
$ pg_upgrade
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
-bash-4.2$
这里要注意，在执行检查之前，需要先关闭旧实例。
主要几个参数如下：
-b (–old-bindir=bindir)： 旧的集群可执行目录。
-B (–new-bindir=bindir)： 新的集群可执行目录。
-d (–old-datadir=configdir)： 旧的集群数据目录。
-D (–new-datadir=configdir)： 旧的集群数据目录
-k (–link)：link模式，相对于克隆模式，克隆模式需要将文件复制到新的集群。
-c (–check)：只做“检查”，不改变任何数据。
pg_upgrade 有其有几种不同的方式：
- 默认使用操作系统命令复制数据文件到新目录，这种方式需要文件系统上有足够的空间存储双份数据，如果数据量很大，复制数据文件的过程会占用较长的升级时间。
- 使用 —link 参数，在新数据目录下做数据文件的硬链接，优点是不需要双倍空间存储数据文件，并且升级过程中不需要复制数据文件，节约升级时间，缺点是使用新版本软件启动数据库后不能再使用旧版本软件启动数据库，不利于升级后的回滚，建议做好数据库备份。
- 使用 —clone 参数，有操作系统内核版本和文件系统类型限制，比如需要XFS格式。这种方式也需要文件系统上有足够的空间存储双份数据，但是相比于第一种方式，速度要快得多，堪比第二种方式。
3.2 执行check 检查
关闭旧库：
-bash-4.2$ pg_ctl stop -D /data/postgresql/data
waiting for server to shut down.... done
server stopped
-bash-4.2$
执行检查：
-bash-4.2$ pwd
/usr/pgsql-14/bin
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /data/postgresql/data -D /data/postgresql/data14 -b /usr/pgsql-13/bin -B /usr/pgsql-14/bin --check --link
could not open log file "pg_upgrade_internal.log": Permission denied
Failure, exiting
-bash-4.2$
我们这里用postgres 用户执行，权限不足的错误。 因为postgres 用户没有/usr/pgsql-14/bin下的权限，切换到其他目录即可执行。
-bash-4.2$ cd /data/postgresql/
--默认方式：
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /data/postgresql/data -D /data/postgresql/data14 -b /usr/pgsql-13/bin -B /usr/pgsql-14/bin --check
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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*
-bash-4.2$
--link方式：
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /data/postgresql/data -D /data/postgresql/data14 -b /usr/pgsql-13/bin -B /usr/pgsql-14/bin --check --link
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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*
--clone 方式：
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /data/postgresql/data -D /data/postgresql/data14 -b /usr/pgsql-13/bin -B /usr/pgsql-14/bin --check --clone
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for presence of required libraries ok
could not clone file between old and new data directories: Operation not supported
Failure, exiting
-bash-4.2$
这里提示克隆不支持，因为该方式有内核和文件系统格式的限制。
4 执行升级
我们这里测试库小，直接采用第一种方式，使用操作系统复制。
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /data/postgresql/data -D /data/postgresql/data14 -b /usr/pgsql-13/bin -B /usr/pgsql-14/bin --jobs 4
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 system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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
------------------
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 ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
-bash-4.2$
这里提示在启动数据库后执行2个命令，我们这里先不执行，放到后面执行。
-bash-4.2$ ll
total 12
drwx------ 20 postgres postgres 4096 Dec 17 16:19 data
drwx------ 20 postgres postgres 4096 Dec 17 16:19 data14
-rwx------ 1 postgres postgres 42 Dec 17 16:19 delete_old_cluster.sh
drwxr-xr-x 4 postgres postgres 30 Dec 17 11:21 software
-bash-4.2$ cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/data/postgresql/data'
-bash-4.2$
5 善后工作
5.1 复制配置文件
没有使用 —link 的情况下，需要将原环境的pg_hba.conf 和 postgresql.conf 参数文件复制到新目录下。
我这里是测试环境，所以这里不用复制。
5.2 修改环境变量和服务
修改/etc/profile文件，指向新路径：
export PATH=/usr/pgsql-14/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-14/lib:$LD_LIBRARY_PATH
export PGDATA=/data/postgresql/data14
如果启用了服务，那么也要检查服务里的路径：
[dave@www.cndba.cn pgsql-13]# systemctl enable postgresql-14
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.
[dave@www.cndba.cn multi-user.target.wants]# cat /usr/lib/systemd/system/postgresql-14.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-14.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-14"
# Look at systemd.unit(5) manual page for more info.
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-14-setup.
[Unit]
Description=PostgreSQL 14 database server
Documentation=https://www.postgresql.org/docs/14/static/
After=syslog.target
After=network.target
[Service]
Type=notify
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/data/postgresql/data14
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
TimeoutStopSec=1h
[Install]
WantedBy=multi-user.target
[dave@www.cndba.cn multi-user.target.wants]#
5.3 启动新实例并验证版本
-bash-4.2$ pg_ctl start -D $PGDATA
waiting for server to start....2022-12-17 16:32:38.514 CST [112702] LOG: redirecting log output to logging collector process
2022-12-17 16:32:38.514 CST [112702] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$ ps -ef|grep pg
postgres 112702 1 0 16:32 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /data/postgresql/data14
postgres 112755 112301 0 16:32 pts/3 00:00:00 grep --color=auto pg
-bash-4.2$ psql
psql (14.6)
Type "help" for help.
postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
(1 row)
cndba=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
cndba=#
5.4 执行善后脚本
升级的时候，提示我们执行2个脚本，我们现在来执行：
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
-bash-4.2$ /usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "cndba": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "cndba": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "cndba": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
-bash-4.2$
-bash-4.2$ ./delete_old_cluster.sh
-bash-4.2$
-bash-4.2$ pwd
/data/postgresql
-bash-4.2$ ll
total 8
drwx------ 20 postgres postgres 4096 Dec 17 16:32 data14
-rwx------ 1 postgres postgres 42 Dec 17 16:19 delete_old_cluster.sh
drwxr-xr-x 4 postgres postgres 30 Dec 17 11:21 software
-bash-4.2$
版权声明：本文为博主原创文章，未经博主允许不得转载。
