对于跨版本的PostgreSQL升级，官方给出了以下三种方法：
• pg_dumpall--把数据从旧版本中导出，然后再导入到新版本，此过程就是一个导入导出的过程。
• Logical Replication--创建一个高版本的从库，待数据同步完成后，主备角色互换，以达到升级目的。
• pg_upgrade--官方推荐的快速升级方法，通过创建新的系统表并使用旧的用户表的方式进行升级。
1、使用 pg_dumpall
连接源库，创建测试表
pg_ctl start -D /data/pg_data142
psql -p 5433
postgres=# create table test_upgrade (id int);
CREATE TABLE
postgres=#
postgres=# insert into test_upgrade values(1);
INSERT 0 1
安装新版本软件， 并初始化数据库
[postgres@hosta ~]$ cd /data/
[postgres@hosta data]$ mkdir pg_data152
[postgres@hosta data]$
[postgres@hosta data]$ chmod 700 pg_data152
[postgres@hosta data]$ cd ~
[postgres@hosta ~]$ . pg15_2.env
[postgres@hosta ~]$ cat pg15_2.env
export PG_HOME=/usr/pgsql-15
export PATH=$PG_HOME/bin:$PATH
export PGDATA=/data/pg_data152/
[postgres@hosta ~]$ initdb -D /data/pg_data152/
拷贝旧版本的 配置文件和hba 认证文件到新版本集群， 并修改新版本集群数据库 port 为 5434
[postgres@hosta ~]$ cp /data/pg_data142/postgresql.conf /data/pg_data152/
[postgres@hosta pg_data152]$ grep port postgresql.conf
port = 5434 # (change requires restart)
[postgres@hosta ~]$ ps -ajxf |grep postgres
12995 16048 16048 12995 pts/0 17830 S 0 0:00 \_ su - postgres
16049 17831 17830 12995 pts/0 17830 S+ 1001 0:00 \_ grep --color=auto postgres
1 17770 17770 17770 ? -1 Ss 1001 0:00 /usr/pgsql-15/bin/postgres -D /data/pg_data152
17770 17771 17771 17771 ? -1 Ss 1001 0:00 \_ postgres: logger
17770 17772 17772 17772 ? -1 Ss 1001 0:00 \_ postgres: checkpointer
17770 17773 17773 17773 ? -1 Ss 1001 0:00 \_ postgres: background writer
17770 17775 17775 17775 ? -1 Ss 1001 0:00 \_ postgres: walwriter
17770 17776 17776 17776 ? -1 Ss 1001 0:00 \_ postgres: autovacuum launcher
17770 17777 17777 17777 ? -1 Ss 1001 0:00 \_ postgres: logical replication launcher
1 17819 17819 17819 ? -1 Ss 1001 0:00 /usr/pgsql-14/bin/postgres -D /data/pg_data142
17819 17820 17820 17820 ? -1 Ss 1001 0:00 \_ postgres: logger
17819 17822 17822 17822 ? -1 Ss 1001 0:00 \_ postgres: checkpointer
17819 17823 17823 17823 ? -1 Ss 1001 0:00 \_ postgres: background writer
17819 17824 17824 17824 ? -1 Ss 1001 0:00 \_ postgres: walwriter
17819 17825 17825 17825 ? -1 Ss 1001 0:00 \_ postgres: autovacuum launcher
17819 17826 17826 17826 ? -1 Ss 1001 0:00 \_ postgres: stats collector
17819 17827 17827 17827 ? -1 Ss 1001 0:00 \_ postgres: logical replication launcher
保持新旧版本数据库同时运行，新版本使用 5434 ，旧版本使用5433 端口 ，通过管道符 导出导入数据库实现版本升级
pg_dumpall -p 5433 |psql -d postgres -p 5434
登录新版本数据库查看数据
[postgres@hosta ~]$ psql -p 5434
psql (15.0)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | test_upgrade | table | postgres
(1 row)
postgres=# select * from test_upgrade ;
id
----
1
(1 row)
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
2、使用pg_upgrade （原地升级和非原地升级）
https://www.postgresql.org/docs/current/pgupgrade.html#:~:text=pg_upgrade%20%28formerly%20called%20pg_migrator%29%20allows%20data%20stored%20in,9.5.8%20to%209.6.4%20or%20from%2010.7%20to%2011.2.
root 执行安装PG15
rpm -ivh postgresql15* -->安装15 可做升级
su - postgresql
cat >> pg15.env
export PG_HOME=/usr/pgsql-15
export PATH=$PG_HOME/bin:$PATH
export PGDATA=/data/pg_data15/
mkdir /data/pg_data15/
chmod 700 /data/pg_data15/
[postgres@hosta ~]$ . pg15.env
[postgres@hosta ~]$ initdb -D $PGDATA
stop both servers
ps -ef|grep postgres
先使用--check 参数检查新旧版本的兼容性，避免因升级失败造成长时间的宕机
[postgres@hosta ~]$ . pg15.env
[postgres@hosta ~]$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/pg_data14/ -D /data/pg_data15/ -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 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 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*
--检查通过以后使用去掉 -c 参数，升级数据库 ， -k 表示使用link的方式
pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
/usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/pg_data14/ -D /data/pg_data15/ -k
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
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
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /data/pg_data14/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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-15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
--升级完成以后，拷贝旧版本的 配置文件和 hba 文件 ， 然后启动数据库
copy old config file and hba to new cluster
[postgres@hosta pg_data14]$ cp postgresql.conf pg_hba.conf ../pg_data15/
[postgres@hosta ~]$ pg_ctl start -D /data/pg_data15/
收集统计信息
[postgres@hosta ~]$ /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "mia_demo": 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 "mia_demo": 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 "mia_demo": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
查看版本为15， 升级成功
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
