AnolisOS 8.8 PostgreSQL 数据库大版本升级

随着系统的运行和 PostgreSQL 新版本的发布，有一些特性只在新版本中支持，所以为了能更好的利用这些特性，我们就需要对 PostgreSQL 的版本进行升级。版本升级有好几种方法，根据具体的情况选择一种合适的方法进行操作。

下面我提供了三种方法，由于一些原因没办法选择第一种和第三种方案。我简单说一下第一种和第三种的优缺点。第一种方案比较适合分库的业务，如果你的业务交互有相同实例的跨库就不适合，需要修改很多底层业务代码。还有就是如果你的业务数据库非常大，这种逻辑迁移方案也会非常耗时。第三种迁移对系统数据库的设计依赖很大，如果存在表没有主键等原因，那么迁移比较吃力。

进行迁移之前记得修改数据库的配置文件 pg_hba.conf 为 trust
vim pg_hba.conf
host    all             all             192.168.30.151/32        trust

方案一：使用 pg_dumpall 逻辑备份所有的数据库
方案二：使用 pg_upgrade 进行数据库版本升级
方案三：使用逻辑复制槽的方式进行数据库迁移升级

方案二的升级步骤
1、修改源的 pg_hba.conf 然后用 pg_reload_conf() 使其生效，并做一下检查点 checkpoint 等操作
2、安装高版本的 PostgreSQL 并修改配置参数，然后关闭服务
3、关闭源的 PostgreSQL 数据库服务
4、运行 pg_upgrade 进行数据库大版本升级

下面这个是我以前做过的一个案例
pg_upgrade -b /home/postgres/pgsql-12/bin -B /home/postgres/pgsql-14/bin -d /home/postgres/pgsql-12/data -D /home/postgres/pgsql-14/data

--备注：这一步需要特别的关注一下扩展插件的问题，如果旧版已经安装但是高版本没有安装插件的话会导致升级失败。
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/local/pgsql-14.5/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

5、启动数据库服务并进行最后的回收和删除
/usr/local/pgsql-14.5/bin/pg_ctl start -D /usr/local/pgsql-14.5/data
/usr/local/pgsql-14.5/bin/vacuumdb --all --analyze-in-stages
./delete_old_cluster.sh

pg_upgrade 升级一个数据库实例
语法
pg_upgrade 的语法
pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]

选项
pg_upgrade 支持的选项
-b bindir	--old-bindir=bindir the old PostgreSQL executable directory; environment variable PGBINOLD
-B bindir	--new-bindir=bindir the new PostgreSQL executable directory; environment variable PGBINNEW
-c	--check check clusters only, don't change any data
-d configdir	--old-datadir=configdir the old database cluster configuration directory; environment variable PGDATAOLD
-D configdir	--new-datadir=configdir the new database cluster configuration directory; environment variable PGDATANEW
-j njobs	--jobs=njobs number of simultaneous processes or threads to use
-k	--link use hard links instead of copying files to the new cluster
-o options	--old-options options options to be passed directly to the old postgres command; multiple option invocations are appended
-O options	--new-options options options to be passed directly to the new postgres command; multiple option invocations are appended
-p port	--old-port=port the old cluster port number; environment variable PGPORTOLD
-P port	--new-port=port the new cluster port number; environment variable PGPORTNEW
-r	--retain retain SQL and log files even after successful completion
-s dir	--socketdir=dir directory to use for postmaster sockets during upgrade; default is current working directory; environment variable PGSOCKETDIR
-U username --username=username cluster's install user name; environment variable PGUSER
-v --verbose enable verbose internal logging
-V	--version display version information, then exit
--clone	Use efficient file cloning (also known as “reflinks” on some systems) instead of copying files to the new cluster. This can result in near-instantaneous copying of the data files, giving the speed advantages of -k/--link while leaving the old cluster untouched.
    File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.
-?	--help show help, then exit