瀚高数据库
目录
环境
文档用途
详细信息

环境
系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：12,14
文档用途
本文主要介绍pg_upgrade这一工具，以pg12升级至pg14为例，给读者演示这一工具如何使用及使用中的注意事项。

详细信息
一、背景说明
出于消除BUG、提升数据库性能、优化代码等不同原因，PG社区会定期更新数据库版本，最新版本为pg15，我们DBA在运维过程中也面临着数据库大版本升级这一切实的需求，目前PG大版本的升级方法主要有以下四种方式：

1.转储数据的方式（pg_dump或pg_dumpall导出数据，pg_restore或psql导入）

2.通过pg_upgrade进行升级

3.pg_logical扩展进行升级

4.通过内置逻辑复制的方式进行版本升级

本次给大家介绍如何通过pg_upgrade工具进行大版本升级。同转储数据的方式相比较，在存量业务数据较大的场景下，pg_upgrade升级相对省时，但是升级总是有风险的，例如升级过程中的硬件故障等，所以第一重要的事情依然是做好备份。升级之前需要检查旧版本已经安装的外部扩展，有一些外部扩展要求在升级之前先升级旧版本的外部扩展，例如PostGIS。

二、pg_upgrade介绍
pg_upgrade（以前称为pg_migrator）允许在不需要数据转储/恢复的情况下，将存储在PostgreSQL数据文件中的数据升级到更高版本的PostgreSQL主版本，例如从9.5.8升级到9.6.4或从10.7升级到11.2。

PostgreSQL主版本会定期添加新功能，这些功能通常会更改系统表的布局，但内部数据存储格式很少更改。pg_upgrade利用这一事实通过创建新的系统表并简单地重用旧的用户数据文件来执行快速升级。如果将来的主要版本更改数据存储格式，使旧数据格式不可读，则pg_upgrade将无法用于此类升级。（社区将尝试避免这种情况.)

pg_upgrade尽最大努力确保新旧集群与二进制兼容，例如，通过检查兼容的编译时设置，包括 32/64 位二进制文件。重要的是，任何外部模块也是二进制兼容的，尽管这不能由pg_upgrade检查。

pg_upgrade支持从 8.4.X 及更高版本升级到当前的主要版本的 PostgreSQL，包括快照和测试版本。

1、参数选项
-b --old-bindir=BINDIR 旧版本PostgreSQL的可执行文件目录；环境变量名称为PGBINOLD

-B --new-bindir=BINDIR 新版本PostgreSQL的可执行文件目录；默认路径为pg_upgrade所在目录；环境变量名称为PGBINNEW

-c --check 只检查集群升级兼容性，不会真正的升级，不改变数据

-d --old-datadir=configdir 旧版本数据库配置/数据目录；环境变量名称为PGDATAOLD

-D --new-datadir=configdir 新版本数据库配置/数据目录；环境变量名称为PGDATANEW

-j --jobs 允许多个CPU核复制或链接文件以及并行地转储和重载数据库模式，一般可以设置为CPU核数。这个选项可以显著地减少升级时间。

-k --link 使用硬链接方式而不是将文件copy到新版本数据库的方式升级

-o --old-options=OPTIONS 直接传送给旧postgres 命令的选项,多个选项可以追加在后面

-O --new-options=OPTIONS 直接传送给新postgres 命令的选项,多个选项可以追加在后面

-p --old-port=PORT 旧版本数据库使用的端口号；环境变量名称为PGPORTOLD

-P --new-port=PORT 新版本数据库使用的端口号;环境变量名称为PGPORTNEW;新旧版本实例使用的端口号必须不同

-r --retain 即使在成功完成后也保留SQL和日志文件

-s --socketdir=DIR 在升级过程中postmaster sockets使用的目录,默认是当前工作目录，环境变量名称为PGSOCKETDIR

-U --username=username 数据库的安装用户；环境变量名称为PGUSER

-v --verbose 启用详细的内部日志记录

-V --version 显示版本信息，然后退出

–clone

使用高效的文件克隆（在某些系统上也称为“reflinks”），而不是将文件复制到新群集。这可能导致近乎即时地复制数据文件，从而提供类似于 -k/–link 的速度优势，同时保持旧集群不变。

文件克隆仅在某些操作系统和文件系统上受支持。如果在不支持的系统上使用了该选项，则pg_upgrade运行将会出错。目前，它在具有Btrfs和XFS（在支持reflink的文件系统上）的Linux（内核4.5或更高版本）以及带有APFS的macOS上受支持。

在升级之前应该运行pg_upgrade并用-c参数检查新旧版本的兼容性，把每一项不兼容的问题都解决了才可以顺利升级。使用pg_upgrade时加上-c参数只会检查新旧版本的兼容性，不会运行真正的升级程序，不会修改数据文件，并且在命令结束时，会输出一份检查结果的报告，还会对需要手动调整的项做出简要的描述。

2、升级模式区分
pg_upgrade有普通模式和Link模式两种升级模式。在普通模式下，会把旧版本的数据拷贝到新版本中，所以如果使用普通模式升级，要确保有足够的磁盘空间存储新旧两份数据；link模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬链接，可以有效减少磁盘占用的空间。

3、升级步骤归纳
1）安装新版本PostgreSQL。注意新版本数据库的安装目录一定要同旧版本区分开，如果使用源码编译安装则手动指定安装目录即可，如果使用rpm包安装且安装目录是/usr/local/pgsql的方式，则推荐将旧版本数据库目录重命名，如果旧版本数据库中安装的扩展存在.so

2）初始化新版本PostgreSQL数据目录。

3）停止旧版本数据库。若只做升级前兼容性测试且期间数据库不会做数据结构修改，则不用停止旧版本数据库，若使用普通模式升级，则将postgresql.conf、postgresql.auto.conf及pg_hba.conf文件进行备份。

4）旧集簇使用的所有自定义共享对象文件（或者 DLL）安装到新集簇中， 例如pgcrypto.so，不管它们是来自于 contrib还是某些其他源码。

5）检查新旧版本兼容性。最后一行输出“Clusters are compatible”说明已经通过兼容性测试，如果最后一行输出“Failure，exiting”，说明新旧版本不兼容，这时应该查看输出中给出的提示，手动消除这些冲突，直到通过兼容性测试。

6）使用pg_upgrade普通模式升级。旧版本数据库必须是停止状态，如果运行pg_upgrade失败，必须重新初始化新版本的数据目录。看到“Upgrade Complete”说明升级已经顺利完成。

使用pg_upgrade的link模式升级：首先需要了解旧版本有哪些Extension及表空间，当使用链接模式运行pg_upgrade之后，pg_upgrade程序会把旧版本数据目录中的pg_control文件重命名为pg_control.old，如果仍然想运行旧版本的数据库实例，需要把pg_control.old重命名回pg_control。但是一旦使用新版本启动了数据库实例，旧的实例将无法再被访问，这一点一定要注意。

7）将旧版本数据库的相关参数配置文件及pg配置文件及pg_hba.conf文件移动到新版本数据库数据目录内，并根据实际情况决定是否只启用本地连接，若使用Link升级方式，则忽略本步。

8）启动新版本数据库实例。

9）更新统计信息。pg_upgrade会创建新的系统表，并重用旧的数据进行升级，统计信息并不会随升级过程迁移，所以在启用新版本之前，应该首先重新收集统计信息，避免没有统计信息导致错误的查询计划。

在升级结束后，根据提示使用vacuumdb --all --analyze-in-stages命令。

10）登录并验证业务数据。

11）清除旧版本数据库，根据升级提示，运行清理旧数据脚本。

三、场景演示
环境信息：

升级前版本：pg12.3

旧版本安装目录：/data/pg12.3

升级后版本：pg14.4

升级后安装目录：/data/pg14.4

pg14.4已通过源码编译安装，未初始化data目录

1、普通模式升级
1）pg12.3环境变量信息检查

[postgres@mode2 ~]$ cat ~/.bash_profile # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PATH=/data/pg12.3/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATHexport PGHOME=/data/pg12.3export PGDATA=$PGHOME/dataexport PGPORT=5432export PGUSER=postgresexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH

2）pg12.3数据库升级前信息统计

[postgres@mode2 ~]$ psqlpsql (12.3)Type "help" for help.postgres=# \l+  List of databasesName    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------a         | a        | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 189 MB  | pg_default | postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 54 MB   | pg_default | default administrative connection databasetemplate0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database|          |          |             |             | postgres=CTc/postgres |         |            | template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 7953 kB | pg_default | default template for new databases|          |          |             |             | postgres=CTc/postgres |         |            | (4 rows)##postgres库内已安装扩展明细postgres=# \dx  List of installed extensionsName        | Version |   Schema   |                         Description                          --------------------+---------+------------+--------------------------------------------------------------dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a databasepg_profile         | 0.3.6   | public     | PostgreSQL load profile repository and report builderpg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executedplpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language(4 rows)##a库内已安装扩展明细postgres=# \c a aYou are now connected to database "a" as user "a".a=> \dxList of installed extensionsName   | Version |   Schema   |                            Description                            ---------+---------+------------+-------------------------------------------------------------------pg_trgm | 1.4     | public     | text similarity measurement and index searching based on trigramsplpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language(2 rows)##a库内示例表明细a=> \dt+List of relationsSchema |       Name       | Type  | Owner |  Size  | Description --------+------------------+-------+-------+--------+-------------public | ch               | table | a     | 79 MB  | public | pgbench_accounts | table | a     | 15 MB  | public | pgbench_branches | table | a     | 288 kB | public | pgbench_history  | table | a     | 12 MB  | public | pgbench_tellers  | table | a     | 416 kB | public | t                | table | a     | 472 kB | public | t1               | table | a     | 448 kB | public | t2               | table | a     | 448 kB | (8 rows)##示例表ch、pg_bench_accounts表结构明细a=> \d+ ch Table "public.ch"Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+-------------id     | integer |           |          |         | plain    |              | hanzi  | text    |           |          |         | extended |              | num    | integer |           |          |         | plain    |              | Indexes:"ind_ch_hanzi" btree (hanzi)Access method: heapa=> \d+ pgbench_accountsTable "public.pgbench_accounts"Column  |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description ----------+---------------+-----------+----------+---------+----------+--------------+-------------aid      | integer       |           | not null |         | plain    |              | bid      | integer       |           |          |         | plain    |              | abalance | integer       |           |          |         | plain    |              | filler   | character(84) |           |          |         | extended |              | Indexes:"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)Access method: heapOptions: fillfactor=100a=> \q

3）查看pg12.3部分postgresql.conf参数及pg_hba.conf文件

[postgres@mode2 ~]$ cd $PGDATA[postgres@mode2 data]$ pwd/data/pg12.3/data[postgres@mode2 data]$ cat postgresql.conf |grep shared_buffersshared_buffers = 256MB          # min 128kB#wal_buffers = -1           # min 32kB, -1 sets based on shared_buffers[postgres@mode2 data]$ cat postgresql.conf |grep wal_levelwal_level = logical         # minimal, replica, or logical[postgres@mode2 data]$ cat pg_hba.conf |grep -v '#'local   all             all                                     trusthost    all             all             127.0.0.1/32            trusthost    all             all             192.168.164.50/32       trusthost    all     all     192.168.164.51/32       trusthost    all     all     192.168.164.52/32   trusthost    all             all             ::1/128                 trustlocal   replication     all                                     trusthost    replication     all             127.0.0.1/32            trusthost    replication all     192.168.164.0/24    trusthost    replication     all             ::1/128                 trust

4）初始化pg14.4的data目录

[postgres@mode2 pg14.4]$ ./bin/initdb -D /data/pg14.4/dataThe 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 "zh_CN.UTF-8".The default database encoding has accordingly been set to "UTF8".initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"The default text search configuration will be set to "simple".Data page checksums are disabled.creating directory /data/pg14.4/data ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... Asia/Shanghaicreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou 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:./bin/pg_ctl -D /data/pg14.4/data -l logfile start

5）停止pg12.3的数据库服务

[postgres@mode2 data]$ pg_ctl statuspg_ctl: server is running (PID: 3459)/data/pg12.3/bin/postgres[postgres@mode2 data]$ pg_ctl stopwaiting for server to shut down....2022-12-06 11:06:26.627 CST [3459] LOG:  received fast shutdown request2022-12-06 11:06:26.628 CST [3459] LOG:  aborting any active transactions2022-12-06 11:06:26.701 CST [3459] LOG:  background worker "logical replication launcher" (PID 3466) exited with exit code 12022-12-06 11:06:26.701 CST [3461] LOG:  shutting down2022-12-06 11:06:26.846 CST [3459] LOG:  database system is shut downdoneserver stopped

6）进行升级兼容性测试，如升级窗口期内无数据结构变更，也可不停库进行兼容性测试，但升级过程必须停库

[postgres@mode2 data]$ /data/pg14.4/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.4/bin -d /data/pg12.3/data/ -D /data/pg14.4/data -cPerforming Consistency Checks-----------------------------Checking cluster versions                                   okChecking database user is the install user                  okChecking database connection settings                       okChecking for prepared transactions                          okChecking for system-defined composite types in user tables  okChecking for reg* data types in user tables                 okChecking for contrib/isn with bigint-passing mismatch       okChecking for user-defined encoding conversions              okChecking for user-defined postfix operators                 okChecking for presence of required libraries                 okChecking database user is the install user                  okChecking for prepared transactions                          okChecking for new cluster tablespace directories             ok*Clusters are compatible*

7）升级pg12.3至pg14.4

[postgres@mode2 data]$ /data/pg14.4/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.4/bin -d /data/pg12.3/data/ -D /data/pg14.4/data Performing Consistency Checks-----------------------------Checking cluster versions                                   okChecking database user is the install user                  okChecking database connection settings                       okChecking for prepared transactions                          okChecking for system-defined composite types in user tables  okChecking for reg* data types in user tables                 okChecking for contrib/isn with bigint-passing mismatch       okChecking for user-defined encoding conversions              okChecking for user-defined postfix operators                 okCreating dump of global objects                             okCreating dump of database schemasokChecking for presence of required libraries                 okChecking database user is the install user                  okChecking for prepared transactions                          okChecking for new cluster tablespace directories             okIf pg_upgrade fails after this point, you must re-initdb thenew cluster before continuing.Performing Upgrade------------------Analyzing all rows in the new cluster                       okFreezing all rows in the new cluster                        okDeleting files from new pg_xact                             okCopying old pg_xact to new server                           okSetting oldest XID for new cluster                          okSetting next transaction ID and epoch for new cluster       okDeleting files from new pg_multixact/offsets                okCopying old pg_multixact/offsets to new server              okDeleting files from new pg_multixact/members                okCopying old pg_multixact/members to new server              okSetting next multixact ID and offset for new cluster        okResetting WAL archives                                      okSetting frozenxid and minmxid counters in new cluster       okRestoring global objects in the new cluster                 okRestoring database schemas in the new clusterokCopying user relation filesokSetting next OID for new cluster                            okSync data directory to disk                                 okCreating script to delete old cluster                       okChecking for extension updates                              noticeYour installation contains extensions that should be updatedwith the ALTER EXTENSION command.  The fileupdate_extensions.sqlwhen executed by psql by the database superuser will updatethese extensions.Upgrade Complete----------------Optimizer statistics are not transferred by pg_upgrade.Once you start the new server, consider running:/data/pg14.4/bin/vacuumdb --all --analyze-in-stagesRunning this script will delete the old cluster's data files:./delete_old_cluster.sh##可看到pg12.3数据目录内自动生成的扩展升级SQL及删除旧数据目录的脚本[postgres@mode2 data]$ ll总用量 272-rw-rw-r-- 1 postgres postgres 173140 12月  6 10:34 awr_report_postgres_1_2.htmldrwx------ 7 postgres postgres     71 11月 29 15:00 base-rwx------ 1 postgres postgres     38 12月  6 11:08 delete_old_cluster.shdrwx------ 2 postgres postgres   4096 12月  6 11:07 global-rw-rw-r-- 1 postgres postgres   1105 12月  6 10:26 pgbench_log.3486-rw-rw-r-- 1 postgres postgres   1104 12月  6 10:26 pgbench_log.3486.1-rw-rw-r-- 1 postgres postgres   1104 12月  6 10:26 pgbench_log.3486.2-rw-rw-r-- 1 postgres postgres   1104 12月  6 10:26 pgbench_log.3486.3-rw-rw-r-- 1 postgres postgres   1112 12月  6 10:30 pgbench_log.3609-rw-rw-r-- 1 postgres postgres   1112 12月  6 10:30 pgbench_log.3609.1-rw-rw-r-- 1 postgres postgres   1112 12月  6 10:30 pgbench_log.3609.2-rw-rw-r-- 1 postgres postgres   1113 12月  6 10:30 pgbench_log.3609.3drwx------ 2 postgres postgres      6 2月  24 2022 pg_commit_tsdrwx------ 2 postgres postgres      6 2月  24 2022 pg_dynshmem-rw------- 1 postgres postgres   4712 12月  6 09:52 pg_hba.conf-rw------- 1 postgres postgres   1636 2月  24 2022 pg_ident.confdrwx------ 4 postgres postgres     68 12月  6 11:07 pg_logicaldrwx------ 4 postgres postgres     36 2月  24 2022 pg_multixactdrwx------ 2 postgres postgres     18 12月  6 11:07 pg_notifydrwx------ 2 postgres postgres      6 2月  24 2022 pg_replslotdrwx------ 2 postgres postgres      6 2月  24 2022 pg_serialdrwx------ 2 postgres postgres      6 2月  24 2022 pg_snapshotsdrwx------ 2 postgres postgres    132 12月  6 11:07 pg_statdrwx------ 2 postgres postgres      6 12月  6 11:07 pg_stat_tmpdrwx------ 2 postgres postgres     18 12月  6 10:33 pg_subtransdrwx------ 2 postgres postgres      6 2月  24 2022 pg_tblspcdrwx------ 2 postgres postgres      6 2月  24 2022 pg_twophase-rw------- 1 postgres postgres      3 2月  24 2022 PG_VERSIONdrwx------ 3 postgres postgres   4096 12月  6 10:44 pg_waldrwx------ 2 postgres postgres     18 2月  24 2022 pg_xact-rw------- 1 postgres postgres     88 2月  24 2022 postgresql.auto.conf-rw------- 1 postgres postgres  26887 12月  6 10:53 postgresql.conf-rw------- 1 postgres postgres    179 12月  6 11:07 postmaster.opts-rw------- 1 postgres postgres    108 12月  6 11:08 update_extensions.sql

7）将pg12.3的相关配置文件移动到pg14.4的data目录中，启动数据库

[postgres@mode2 data]$ cp pg_hba.conf /data/pg14.4/data[postgres@mode2 data]$ cp postgresql.conf /data/pg14.4/data/postgresql.base.conf[postgres@mode2 data]$ vi /data/pg14.4/data/postgresql.conf ##在最后一行增加如下内容include postgresql.base.conf[postgres@mode2 pg12.3]$ cd /data/pg14.4[postgres@mode2 data]$ /data/pg14.4/bin/pg_ctl start -D /data/pg14.4/datawaiting for server to start....2022-12-06 14:05:14.940 CST [16455] LOG:  starting PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit2022-12-06 14:05:14.950 CST [16455] LOG:  listening on IPv6 address "::1", port 54322022-12-06 14:05:14.950 CST [16455] LOG:  listening on IPv4 address "127.0.0.1", port 54322022-12-06 14:05:14.951 CST [16455] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2022-12-06 14:05:14.970 CST [16456] LOG:  database system was shut down at 2022-12-06 11:08:05 CST2022-12-06 14:05:15.055 CST [16455] LOG:  database system is ready to accept connectionsdoneserver started

8）根据提示执行扩展升级的SQL语句，发现报symbol错误，此问题为环境变量仍为pg12.3原因导致，手动设置下环境变量

[postgres@mode2 data]$ /data/pg14.4/bin/psql -f update_extensions.sql /data/pg14.4/bin/psql: symbol lookup error: /data/pg14.4/bin/psql: undefined symbol: PQmblenBounded##手动设置环境后再次执行SQL[postgres@mode2 data]$ export LD_LIBRARY_PATH=/data/pg14.4/lib[postgres@mode2 data]$ [postgres@mode2 data]$ /data/pg14.4/bin/psql -U postgres -f update_extensions.sql You are now connected to database "a" as user "postgres".ALTER EXTENSIONYou are now connected to database "postgres" as user "postgres".ALTER EXTENSION

9）根据升级过程中的提示更新统计信息

[postgres@mode2 data]$ /data/pg14.4/bin/vacuumdb --all --analyze-in-stagesvacuumdb: processing database "a": 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 "a": 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 "a": Generating default (full) optimizer statisticsvacuumdb: processing database "postgres": Generating default (full) optimizer statisticsvacuumdb: processing database "template1": Generating default (full) optimizer statistics

10）登录升级后的数据库核对信息,升级后数据库参数明细

[postgres@mode2 data]$ /data/pg14.4/bin/psqlpsql (14.4)Type "help" for help.postgres=# show shared_buffers ;shared_buffers ----------------256MB(1 row)postgres=# show wal_level ;wal_level -----------logical(1 row)postgres=# show shared_preload_libraries ;shared_preload_libraries --------------------------pg_stat_statements(1 row)

11）录升级后数据库核对数据

##升级后数据库明细postgres=# \l+List of databasesName    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------a         | a        | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 189 MB  | pg_default | postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 55 MB   | pg_default | default administrative connection databasetemplate0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 8585 kB | pg_default | unmodifiable empty database|          |          |             |             | postgres=CTc/postgres |         |            | template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres=CTc/postgres+| 8553 kB | pg_default | default template for new databases|          |          |             |             | =c/postgres           |         |            | (4 rows)##升级后示例扩展明细postgres=# \dxList of installed extensionsName        | Version |   Schema   |                         Description                          --------------------+---------+------------+--------------------------------------------------------------dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a databasepg_profile         | 0.3.6   | public     | PostgreSQL load profile repository and report builderpg_stat_statements | 1.9     | public     | track execution statistics of all SQL statements executedplpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language(4 rows)##升级后示例表明细postgres=# \c a aYou are now connected to database "a" as user "a".a=> \dt+List of relationsSchema |       Name       | Type  | Owner | Persistence | Access method |  Size  | Description --------+------------------+-------+-------+-------------+---------------+--------+-------------public | ch               | table | a     | permanent   | heap          | 79 MB  | public | pgbench_accounts | table | a     | permanent   | heap          | 15 MB  | public | pgbench_branches | table | a     | permanent   | heap          | 288 kB | public | pgbench_history  | table | a     | permanent   | heap          | 12 MB  | public | pgbench_tellers  | table | a     | permanent   | heap          | 416 kB | public | t                | table | a     | permanent   | heap          | 472 kB | public | t1               | table | a     | permanent   | heap          | 448 kB | public | t2               | table | a     | permanent   | heap          | 448 kB | (8 rows)a=> select count(*) from ch ;count  ---------1210000(1 row)a=> select count(*) from pgbench_accounts ;count  --------100000(1 row)#升级后示例表ch、pg_bench_accounts表结构明细a=> \d+ chTable "public.ch"Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------id     | integer |           |          |         | plain    |             |              | hanzi  | text    |           |          |         | extended |             |              | num    | integer |           |          |         | plain    |             |              | Indexes:"ind_ch_hanzi" btree (hanzi)Access method: heapa=> \d+ pgbench_accountsTable "public.pgbench_accounts"Column  |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description ----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------aid      | integer       |           | not null |         | plain    |             |              | bid      | integer       |           |          |         | plain    |             |              | abalance | integer       |           |          |         | plain    |             |              | filler   | character(84) |           |          |         | extended |             |              | Indexes:"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)Access method: heapOptions: fillfactor=100a=> \q[postgres@mode2 data]$ 

12）根据升级完成后的提示，验证无误后执行删除旧数据目录脚本，清理空间

[postgres@mode2 data]$ lsawr_report_postgres_1_2.html  pgbench_log.3486    pgbench_log.3609    pg_commit_ts   pg_logical    pg_serial     pg_subtrans  pg_wal                postmaster.optsbase                          pgbench_log.3486.1  pgbench_log.3609.1  pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    pg_xact               update_extensions.sqldelete_old_cluster.sh         pgbench_log.3486.2  pgbench_log.3609.2  pg_hba.conf    pg_notify     pg_stat       pg_twophase  postgresql.auto.confglobal                        pgbench_log.3486.3  pgbench_log.3609.3  pg_ident.conf  pg_replslot   pg_stat_tmp   PG_VERSION   postgresql.conf[postgres@mode2 data]$ cat delete_old_cluster.sh #!/bin/shrm -rf '/data/pg12.3/data'[postgres@mode2 data]$ ./delete_old_cluster.sh [postgres@mode2 data]$ ls

13）修改环境变量为pg14.4，并使之生效

[postgres@mode2 data]$ vi ~/.bash_profile[postgres@mode2 data]$ cat ~/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PATH=/data/pg14.4/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATHexport PGHOME=/data/pg14.4export PGDATA=$PGHOME/dataexport PGPORT=5432export PGUSER=postgresexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH[postgres@mode2 data]$ source ~/.bash_profile[postgres@mode2 data]$ psqlpsql (14.4)Type "help" for help.postgres=# 

2、LINK模式升级
环境信息：

升级前版本：pg12.3

旧版本安装目录：/data/pg12.3

升级后版本：pg14.2

升级后安装目录：/data/pg14.2

1）pg14.2已通过源码编译安装，未初始化data目录

##还原环境，环境变量切换为pg12.3的环境变量，数据库各类明细同上一步相同，不再单独列出[postgres@mode2 data]$ cat ~/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PATH=/data/pg12.3/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATHexport PGHOME=/data/pg12.3export PGDATA=$PGHOME/dataexport PGPORT=5432export PGUSER=postgresexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH[postgres@mode2 data]$ pwd/data/pg14.2/data[postgres@mode2 data]$ ll总用量 0

2）初始化pg14.2数据目录

[postgres@mode2 data]$ /data/pg14.2/bin/initdb -D /data/pg14.2/dataThe 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 "zh_CN.UTF-8".The default database encoding has accordingly been set to "UTF8".initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"The default text search configuration will be set to "simple".Data page checksums are disabled.fixing permissions on existing directory /data/pg14.2/data ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... Asia/Shanghaicreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okinitdb: warning: enabling "trust" authentication for local connectionsYou 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:/data/pg14.2/bin/pg_ctl -D /data/pg14.2/data -l logfile start

3）进行升级兼容性测试

[postgres@mode2 data]$ /data/pg14.2/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.2/bin -d /data/pg12.3/data/ -D /data/pg14.2/data -cPerforming Consistency Checks-----------------------------Checking cluster versions                                   okChecking database user is the install user                  okChecking database connection settings                       okChecking for prepared transactions                          okChecking for system-defined composite types in user tables  okChecking for reg* data types in user tables                 okChecking for contrib/isn with bigint-passing mismatch       okChecking for user-defined encoding conversions              okChecking for user-defined postfix operators                 okChecking for presence of required libraries                 okChecking database user is the install user                  okChecking for prepared transactions                          okChecking for new cluster tablespace directories             ok*Clusters are compatible*

4）硬链接升级 使用-k参数

[postgres@mode2 data]$ /data/pg14.2/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.2/bin -d /data/pg12.3/data/ -D /data/pg14.2/data -kPerforming Consistency Checks-----------------------------Checking cluster versions                                   okChecking database user is the install user                  okChecking database connection settings                       okChecking for prepared transactions                          okChecking for system-defined composite types in user tables  okChecking for reg* data types in user tables                 okChecking for contrib/isn with bigint-passing mismatch       okChecking for user-defined encoding conversions              okChecking for user-defined postfix operators                 okCreating dump of global objects                             okCreating dump of database schemasokChecking for presence of required libraries                 okChecking database user is the install user                  okChecking for prepared transactions                          okChecking for new cluster tablespace directories             okIf pg_upgrade fails after this point, you must re-initdb thenew cluster before continuing.Performing Upgrade------------------Analyzing all rows in the new cluster                       okFreezing all rows in the new cluster                        okDeleting files from new pg_xact                             okCopying old pg_xact to new server                           okSetting oldest XID for new cluster                          okSetting next transaction ID and epoch for new cluster       okDeleting files from new pg_multixact/offsets                okCopying old pg_multixact/offsets to new server              okDeleting files from new pg_multixact/members                okCopying old pg_multixact/members to new server              okSetting next multixact ID and offset for new cluster        okResetting WAL archives                                      okSetting frozenxid and minmxid counters in new cluster       okRestoring global objects in the new cluster                 okRestoring database schemas in the new clusterokAdding ".old" suffix to old global/pg_control               okIf you want to start the old cluster, you will need to removethe ".old" suffix from /data/pg12.3/data/global/pg_control.old.Because "link" mode was used, the old cluster cannot be safelystarted once the new cluster has been started.Linking user relation filesokSetting next OID for new cluster                            okSync data directory to disk                                 okCreating script to delete old cluster                       okChecking for extension updates                              noticeYour installation contains extensions that should be updatedwith the ALTER EXTENSION command.  The fileupdate_extensions.sqlwhen executed by psql by the database superuser will updatethese extensions.Upgrade Complete----------------Optimizer statistics are not transferred by pg_upgrade.Once you start the new server, consider running:/data/pg14.2/bin/vacuumdb --all --analyze-in-stagesRunning this script will delete the old cluster's data files:./delete_old_cluster.sh[postgres@mode2 data]$ 

5）将pg12.3的相关配置文件移动到pg14.2的data目录中，启动数据库

[postgres@mode2 data]$ cp pg_hba.conf /data/pg14.2/data[postgres@mode2 data]$ cp postgresql.conf /data/pg14.2/data/postgresql.base.conf[postgres@mode2 data]$ vi /data/pg14.2/data/postgresql.conf ##在最后一行增加如下内容include postgresql.base.conf

6）启动新版本pg14.2数据库

[postgres@mode2 pg12.3]$ /data/pg14.2/bin/pg_ctl start -D /data/pg14.2/datawaiting for server to start.....2022-12-07 10:25:41.359 CST [43925] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit.2022-12-07 10:25:41.394 CST [43925] LOG:  listening on IPv6 address "::1", port 54322022-12-07 10:25:41.394 CST [43925] LOG:  listening on IPv4 address "127.0.0.1", port 54322022-12-07 10:25:41.395 CST [43925] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2022-12-07 10:25:41.427 CST [43927] LOG:  database system was shut down at 2022-12-07 09:56:24 CST2022-12-07 10:25:41.501 CST [43925] LOG:  database system is ready to accept connectionsdoneserver started

7）更新新版本数据库统计信息

[postgres@mode2 data]$ /data/pg14.2/bin/vacuumdb --all --analyze-in-stagesvacuumdb: processing database "a": 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 "a": 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 "a": Generating default (full) optimizer statisticsvacuumdb: processing database "postgres": Generating default (full) optimizer statisticsvacuumdb: processing database "template1": Generating default (full) optimizer statistics

8）修改环境信息为pg14.2，并使之生效，环境变量信息如下

[postgres@mode2 data]$ cat ~/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PATH=/data/pg14.2/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATHexport PGHOME=/data/pg14.2export PGDATA=$PGHOME/dataexport PGPORT=5432export PGUSER=postgresexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH

9）登录升级后数据库核对数据

[postgres@mode2 data]$ psqlpsql (14.2)Type "help" for help.postgres=# \l+List of databasesName    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------a         | a        | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 189 MB  | pg_default | postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                       | 55 MB   | pg_default | default administrative connection databasetemplate0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +| 8585 kB | pg_default | unmodifiable empty database|          |          |             |             | postgres=CTc/postgres |         |            | template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres=CTc/postgres+| 8561 kB | pg_default | default template for new databases|          |          |             |             | =c/postgres           |         |            | (4 rows)postgres=# \dxList of installed extensionsName        | Version |   Schema   |                         Description                          --------------------+---------+------------+--------------------------------------------------------------dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a databasepg_profile         | 0.3.6   | public     | PostgreSQL load profile repository and report builderpg_stat_statements | 1.9     | public     | track execution statistics of all SQL statements executedplpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language(4 rows)postgres=# \c a aYou are now connected to database "a" as user "a".a=> \dxList of installed extensionsName   | Version |   Schema   |                            Description                            ---------+---------+------------+-------------------------------------------------------------------pg_trgm | 1.6     | public     | text similarity measurement and index searching based on trigramsplpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language(2 rows)a=> \dt+List of relationsSchema |       Name       | Type  | Owner | Persistence | Access method |  Size  | Description --------+------------------+-------+-------+-------------+---------------+--------+-------------public | ch               | table | a     | permanent   | heap          | 79 MB  | public | pgbench_accounts | table | a     | permanent   | heap          | 15 MB  | public | pgbench_branches | table | a     | permanent   | heap          | 288 kB | public | pgbench_history  | table | a     | permanent   | heap          | 12 MB  | public | pgbench_tellers  | table | a     | permanent   | heap          | 416 kB | public | t                | table | a     | permanent   | heap          | 472 kB | public | t1               | table | a     | permanent   | heap          | 448 kB | public | t2               | table | a     | permanent   | heap          | 448 kB | (8 rows)a=> \d+ chTable "public.ch"Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------id     | integer |           |          |         | plain    |             |              | hanzi  | text    |           |          |         | extended |             |              | num    | integer |           |          |         | plain    |             |              | Indexes:"ind_ch_hanzi" btree (hanzi)Access method: heapa=> \d+ pgbench_accountsTable "public.pgbench_accounts"Column  |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description ----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------aid      | integer       |           | not null |         | plain    |             |              | bid      | integer       |           |          |         | plain    |             |              | abalance | integer       |           |          |         | plain    |             |              | filler   | character(84) |           |          |         | extended |             |              | Indexes:"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)Access method: heapOptions: fillfactor=100a=> select count(*) from ch;count  ---------1210000(1 row)a=> select count(*) from pgbench_accounts;count  --------100000(1 row)

10）根据升级完成后的提示，验证无误后执行删除旧数据目录脚本，清理空间

[postgres@mode2 data]$ sh delete_old_cluster.sh [postgres@mode2 data]$ cd /data/pg12.3[postgres@mode2 pg12.3]$ ll总用量 16drwxr-xr-x  2 postgres postgres 4096 1月  27 2022 bindrwxr-xr-x  4 postgres postgres 4096 1月  27 2022 includedrwxr-xr-x  4 postgres postgres 4096 1月  27 2022 libdrwxr-xr-x  5 postgres postgres   46 1月  27 2022 share

注：本次的两次示例在编译安装新版本数据库时，使用了make world及make install-world，做到了最大化安装，各位读者在实际升级过程中的兼容性测试过程中可能会遇到缺少插件相关包的报错，进行针对性的解决即可。