【流复制环境PostgreSQL-14.1到PostgreSQL-16.1大版本升级】

PostgreSQL大版本会定期添加新特性，这些新特性通常会改变系统表的布局，但内部数据存储格式很少改变。pg_upgrade通过创建新的系统表和重用旧的用户数据文件来执行快速升级。

pg_upgrade升级主要有三种用法：
1、使用pg_upgrade拷贝升级。
2、使用pg_upgrade链接升级（带有- -link选项），- -link较快，但是启动新版本后修改了数据文件，再启动旧版本可能数据损坏，回滚较麻烦，所以尽量避免使用link参数进行升级。
3.带有- -clone选项，但是有操作系统内核版本和文件系统类型限制。

本文主要介绍第一种使用pg_upgrade默认拷贝升级。

一、本地环境

确认原环境和升级环境

旧版本相关信息	value
原端口	5432
原软件目录	/home/postgres/soft
原数据目录	/home/postgres/data

新版本相关信息	value
新端口	5432
原软件目录	/home/postgres/soft-16
原数据目录	/home/postgres/data-16

[目前升级方式使用了新的软件目录和数据目录，如果想使用原来的路径，建议做完升级后，暂时不启动数据库，先对目录进行重命名，更改环境变量，然后再启动数据库]

检查当前版本，和主备同步状况

Expanded display is on.
postgres=# select version();
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------
version | PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 86356
usesysid         | 16388
usename          | repl
application_name | walreceiver
client_addr      | 172.20.10.7
client_hostname  |
client_port      | 60282
backend_start    | 2023-12-25 20:00:59.799815+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/5000148
write_lsn        | 0/5000148
flush_lsn        | 0/5000148
replay_lsn       | 0/5000148
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-12-25 20:04:49.603619+08

二、查看当前实例每个库下安装的插件（主库）

for db in `psql --pset=pager=off -t -A -q -c 'select datname from pg_database where datname not in ($$template0$$, $$template1$$)'`
do
psql -d $db --pset=pager=off -q -c 'select current_database(),extname as name,extversion as version from pg_extension'
done

结果如下，可以看到每个数据库里安装的插件

postgres@ubuntu-linux-22-04-desktop:~/data$ for db in `psql --pset=pager=off -t -A -q -c 'select datname from pg_database where datname not in ($$template0$$, $$template1$$)'`
do
psql -d $db --pset=pager=off -q -c 'select current_database(),extname as name,extversion as version from pg_extension'
done
 current_database |        name        | version
------------------+--------------------+---------
 postgres         | plpgsql            | 1.0
 postgres         | pg_hint_plan       | 1.4.2
 postgres         | pg_stat_statements | 1.9
 postgres         | pg_bigm            | 1.2
 postgres         | pg_trgm            | 1.6
 postgres         | pgcrypto           | 1.3
(6 rows)

 current_database |  name   | version
------------------+---------+---------
 test_upgrade     | plpgsql | 1.0
 test_upgrade     | pg_bigm | 1.2
 test_upgrade     | pg_trgm | 1.6
(3 rows)

三、上传软件包并解压（主备）

软件包放到规定的目录下

root@ubuntu-linux-22-04-desktop:~# su - postgres
postgres@ubuntu-linux-22-04-desktop:~$
postgres@ubuntu-linux-22-04-desktop:~$ pwd
/home/postgres
postgres@ubuntu-linux-22-04-desktop:~$ ll postgresql-16.1.tar.gz
-rw-r--r-- 1 postgres postgres 32433767 Dec 24 17:11 postgresql-16.1.tar.gz
postgres@ubuntu-linux-22-04-desktop:~$ tar -xf postgresql-16.1.tar.gz
postgres@ubuntu-linux-22-04-desktop:~$ ls postgresql-16.1
COPYRIGHT    HISTORY Makefile aclocal.m4 configure   contrib meson.build    src
GNUmakefile.in INSTALL README  config   configure.ac doc   meson_options.txt

四、编译安装新版本的数据库软件（主备节点）

创建数据目录和安装目录

mkdir /home/postgres/data-16
mkdir /home/postgres/soft-16

进到新版本的软件包下

postgres@ubuntu-linux-22-04-desktop:~$ cd postgresql-16.1/
postgres@ubuntu-linux-22-04-desktop:~/postgresql-16.1$ ls
COPYRIGHT    HISTORY Makefile aclocal.m4 configure   contrib meson.build    src
GNUmakefile.in INSTALL README  config   configure.ac doc   meson_options.txt

根据pg_config里的编译选项，编译新版本的软件，并指定新的软件目录，（不可以和原来的旧版本的软件目录重复）

通过查看，原来的编译命令为

./configure --prefix=/home/postgres/soft --with-openssl  --with-pgport=5432 --enable-debug --enable-depend -enable-cassert --with-uuid=ossp

则新的编译命令为

./configure --prefix=/home/postgres/soft-16 --with-openssl  --with-pgport=5432 --enable-debug --enable-depend -enable-cassert --with-uuid=ossp

然后安装

make -j 24
make install -j 24

五、初始化新的版本数据库（主库）

执行如下的语句初始化新的数据库实例，根据需求看是否要带上-E UTF8

/home/postgres/soft-16/bin/initdb -D /home/postgres/data-16

初始化之后不要启动新版本为数据库，因为端口信息是一致的会冲突。

postgres@ubuntu-linux-22-04-desktop:~$ /home/postgres/soft-16/bin/initdb -D /home/postgres/data-16 -E UTF8
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 "C.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/data-16 ... 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:

    /home/postgres/soft-16/bin/pg_ctl -D /home/postgres/data-16 -l logfile start

注意检查新版本软件目录和数据目录的权限，是否postgres用户可以访问。

六、新版本安装旧版本里安装的插件（主备节点）

新版本安装旧的版本里安装的插件

//如下是数据库安装包里自带的

cd /home/postgres/postgresql-16.1/contrib/pg_stat_statements/
make PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
make install PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24

cd /home/postgres/postgresql-16.1/contrib/pgcrypto/
make PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
make install PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24

cd /home/postgres/postgresql-16.1/contrib/pg_trgm/
make PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
make install PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24

#cd /home/postgres/postgresql-16.1/contrib/uuid-ossp
#make PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
#make install PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24


//如下不是数据库安装包里自带的，需要额外下载对应的包进行安装

cd /home/postgres/postgresql-16.1/contrib
unzip pg_hint_plan-PG16.zip
cd pg_hint_plan-PG16/
make PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
make install PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24

cd /home/postgres/postgresql-16.1/contrib
unzip pg_bigm-REL1_2_STABLE.zip
cd pg_bigm-REL1_2_STABLE
make USE_PGXS=1 PG_CONFIG=/home/postgres/soft-16/bin/pg_config -j 24
make USE_PGXS=1 PG_CONFIG=/home/postgres/soft-16/bin/pg_config  install -j 24

七、修改新版本的配置文件（主库）

调整新的版本的postgresql.conf配置文件，修改shared_preload_libraries，添加所用的插件

shared_preload_libraries = 'pg_stat_statements,pg_trgm,pgcrypto,pg_hint_plan,pg_bigm'

如果不处理插件，更新检查会如下报错：

报错文件里记录的如下

将旧版本配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的新配置文件中再次进行修改，不建议postgresql.conf参数文件直接拷贝，因为大版本更新可能配置文件的参数发生了调整，可能某个参数被删除，而且例如归档目录或者一些涉及到目录的参数可能需要调整。

可以提前使用下面语句筛出在配置文件里更改的参数。

postgres@ubuntu-linux-22-04-desktop:~$ grep '^\ *[a-z]' $PGDATA/postgresql.conf|awk -F "#" '{print $1}'
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'C.UTF-8'
lc_monetary = 'C.UTF-8'
lc_numeric = 'C.UTF-8'
lc_time = 'C.UTF-8'
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements,pg_trgm,pgcrypto,pg_hint_plan,pg_bigm'
port=5432

postgres@ubuntu-linux-22-04-desktop:~$ grep '^\ *[a-z]' $PGDATA/pg_hba.conf
local  all       all                   trust
host  all       all       127.0.0.1/32      trust
host  all       all       ::1/128         trust
local  replication   all                   trust
host  replication   all       127.0.0.1/32      trust
host  replication   all       ::1/128         trust

八、升级检查（主库）

升级检查操作可以在线做

/home/postgres/soft-16/bin/pg_upgrade --old-datadir /home/postgres/data/ --new-datadir /home/postgres/data-16/ --old-bindir /home/postgres/soft/bin --new-bindir /home/postgres/soft-16/bin --check

备库需要在升级后使用pg_basebackup同步数据并重新构建同步关系。

九、停业务，并且备份

确定没有客户端访问之后。根据以往的备份策略进行一次全量备份，建议使用物理备份。升级必须做备份，如果升级失败有可能数据损坏。

十、停数据库，并做升级（主库）

停数据库

postgres@ubuntu-linux-22-04-desktop:~$ ps xf
  PID TTY   STAT  TIME COMMAND
1301722 pts/3  S   0:00 -bash
1330558 pts/3  R+   0:00 \_ ps xf
1330543 ?    Ss   0:00 /home/postgres/soft/bin/postgres
1330544 ?    Ss   0:00 \_ postgres: logger
1330546 ?    Ss   0:00 \_ postgres: checkpointer
1330547 ?    Ss   0:00 \_ postgres: background writer
1330548 ?    Ss   0:00 \_ postgres: walwriter
1330549 ?    Ss   0:00 \_ postgres: autovacuum launcher
1330550 ?    Ss   0:00 \_ postgres: stats collector
1330551 ?    Ss   0:00 \_ postgres: logical replication launcher
postgres@ubuntu-linux-22-04-desktop:~$ pg_ctl stop -D /home/postgres/data
waiting for server to shut down.... done
server stopped

可以使–link表示将新版本的数据目录硬链接到旧版本的数据目录，而不会复制一份新的数据文件，可以快速进行升级，但回退较为麻烦。

1.如果升级时没有使用 --link选项，旧版本的数据库集群没有任何修改，重新启动服务即可；

2.如果升级时使用了 --link 选项，数据库文件可能已经被新版本的集群使用。

所以在升级中，尽量避免使用link参数进行升级。

/home/postgres/soft-16/bin/pg_upgrade --old-datadir /home/postgres/data/ --new-datadir /home/postgres/data-16/ --old-bindir /home/postgres/soft/bin --new-bindir /home/postgres/soft-16/bin 

十一、再次检查新版本的hba及参数（主库）

检查参数，避免参数配置问题引起问题

十二、修改环境变量（主备节点）

修改环境变量

su – postgres
vi .bashrc

使环境变量生效

source .bashrc

十三、启动数据库并验证版本，验证数据

postgres@ubuntu-linux-22-04-desktop:~$ pg_ctl start -D /home/postgres/data-16/
waiting for server to start....2023-12-26 00:56:52.756 CST [189162] LOG:  starting PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
2023-12-26 00:56:52.757 CST [189162] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-12-26 00:56:52.757 CST [189162] LOG:  listening on IPv6 address "::", port 5432
2023-12-26 00:56:52.759 CST [189162] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-12-26 00:56:52.764 CST [189165] LOG:  database system was shut down at 2023-12-26 00:53:57 CST
2023-12-26 00:56:52.769 CST [189162] LOG:  database system is ready to accept connections
2023-12-26 00:56:52.773 CST [189168] WARNING:  archive_mode enabled, yet archiving is not configured
 done
server started
postgres@ubuntu-linux-22-04-desktop:~$ psql
psql (16.1)
Type "help" for help.

postgres=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

postgres=# \l+
                                                                                       List of databases
     Name     |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |
        Description
--------------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------+---------+------------+--------
------------------------------------
 postgres     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |                       | 7468 kB | pg_default | default
 administrative connection database
 template0    | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +| 7121 kB | pg_default | unmodif
iable empty database
              |          |          |                 |         |         |            |           | postgres=CTc/postgres |         |            |
 template1    | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | postgres=CTc/postgres+| 7121 kB | pg_default | default
 template for new databases
              |          |          |                 |         |         |            |           | =c/postgres           |         |            |
 test_upgrade | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |                       | 7348 kB | pg_default |
(4 rows)

postgres=# \c test_upgrade
You are now connected to database "test_upgrade" as user "postgres".
test_upgrade=# select * from test01 ;
 id | name
----+------
  1 | sss
(1 row)

十四、验证插件的情况

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_bigm            | 1.2     | public     | text similarity measurement and index searching based on bigrams
 pg_hint_plan       | 1.4.2   | hint_plan  |
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 pg_trgm            | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto           | 1.3     | public     | cryptographic functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(6 rows)

postgres=# select userid::regrole, dbid, query from pg_stat_statements order by total_exec_time desc limit 5;
  userid  | dbid |                                                              query
----------+------+----------------------------------------------------------------------------------------------------------------------------------
 postgres |    5 | CREATE DATABASE "template1" WITH TEMPLATE = template0 OID = 1 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8'
 postgres |    1 | DROP DATABASE "postgres"
 postgres |    1 | CREATE DATABASE "postgres" WITH TEMPLATE = template0 OID = 13008 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8'
 postgres |    1 | CREATE DATABASE "test_upgrade" WITH TEMPLATE = template0 OID = 16385 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8'
 postgres |    1 | VACUUM (SKIP_DATABASE_STATS, ANALYZE) pg_catalog.pg_proc
(5 rows)

插件状态正常

十五、构建备机，恢复主备环境

使用pg_basebackup获取主库全量数据

postgres@ubuntu-linux-22-04-desktop:~$ pg_basebackup -h 172.20.10.6 -p 5432 -U repl -l pg_basebackup_`date +%Y%m%d%H%M%S` -Fp -X fetch -P -v  -D /home/postgres/data-16 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/10000028 on timeline 1
46904/46904 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/10000138
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

确认下备库的参数文件和pg_hba.conf文件，看是否有需要更改或者调整的参数，例如增加hot_standby = 'on’等。

启动数据库

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_ctl start -D /home/postgres/data-16
waiting for server to start....2023-12-26 01:12:36.160 CST [216580] LOG:  starting PostgreSQL 16.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2023-12-26 01:12:36.160 CST [216580] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-12-26 01:12:36.160 CST [216580] LOG:  listening on IPv6 address "::", port 5432
2023-12-26 01:12:36.161 CST [216580] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-12-26 01:12:36.165 CST [216583] LOG:  database system was interrupted; last known up at 2023-12-26 01:06:42 CST
2023-12-26 01:12:36.216 CST [216583] LOG:  entering standby mode
2023-12-26 01:12:36.219 CST [216583] LOG:  redo starts at 0/10000028
2023-12-26 01:12:36.220 CST [216583] LOG:  consistent recovery state reached at 0/10000138
2023-12-26 01:12:36.220 CST [216580] LOG:  database system is ready to accept read-only connections
2023-12-26 01:12:36.232 CST [216584] LOG:  started streaming WAL from primary at 0/11000000 on timeline 1
 done
server started

在主库上查询流复制信息

postgres@ubuntu-linux-22-04-desktop:~/data-16$ psql
psql (16.1)
Type "help" for help.

postgres=# select * from pg_stat_replication;
  pid   | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   |
 sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
--------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-
-----------+------------+------------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 192284 |    16384 | repl    | walreceiver      | 172.20.10.7 |                 |       33060 | 2023-12-26 01:12:36.194886+08 |              | streaming |
0/11000148 | 0/11000148 | 0/11000148 | 0/11000148 |           |           |            |             0 | async      | 2023-12-26 01:13:26.254202+08
(1 row)

主备同步正常

十六、更新统计信息

pg_upgrade 不会生成新版本数据库的统计信息，按提示执行命令：

postgres@ubuntu-linux-22-04-desktop:~$ /home/postgres/soft-16/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "test_upgrade": 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 "test_upgrade": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "test_upgrade": Generating default (full) optimizer statistics

十七、删除原集群数据文件

确认升级成功后，可以选择删除或者保留旧的数据文件。pg_upgrade 提供了一个删除旧数据文件的脚本delete_old_cluster.sh，这个是自动生成的,也可以不使用，本身里边包含的内容也比较简单，只是删除原数据目录的命令。

postgres@ubuntu-linux-22-04-desktop:~/data-16$ cat delete_old_cluster.sh
#!/bin/sh

rm -rf '/home/postgres/data'

十八、回退方案

1.升级未使用—link选项

如果升级时没有使用 --link 选项，旧版本的数据库集群没有任何修改，修改环境变量到原来的版本，重新启动服务即可。

2.升级使用了—link选项

如果升级时使用了 --link 选项，数据库文件可能已经被新版本的集群使用：

（1） 如果 pg_upgrade 在链接操作之前终止，旧版本的数据库集群没有任何修改，重新启动服务即可；

（2）如果没有启动过新版本的后台服务，旧版本的数据库集群没有修改，但是链接过程已经将 $PGDATA/global/pg_control 文件重命名为 $PGDATA/global/pg_control.old；此时需要将该文件名中的 .old 后缀去掉，然后重新启动服务即可；

（3）如果已经启动了新版本的数据库集群，已经修改了数据库文件，再启动旧版本的服务可能导致数据损坏；此时需要通过备份文件还原旧版本的数据库。(所以做任何操作之前，需要备份数据库，以便回退)

所以在升级中，尽量避免使用link参数进行升级。