本文主要是介绍【流复制环境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-bitpostgres=# 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'
donecurrent_database | name | version
------------------+--------------------+---------postgres | plpgsql | 1.0postgres | pg_hint_plan | 1.4.2postgres | pg_stat_statements | 1.9postgres | pg_bigm | 1.2postgres | pg_trgm | 1.6postgres | pgcrypto | 1.3
(6 rows)current_database | name | version
------------------+---------+---------test_upgrade | plpgsql | 1.0test_upgrade | pg_bigm | 1.2test_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 ... okinitdb: 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 24cd /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 24cd /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 24cd /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 xfPID 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 configureddone
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 databasesName | 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 | defaultadministrative connection databasetemplate0 | 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 | defaulttemplate 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=# \dxList of installed extensionsName | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------pg_bigm | 1.2 | public | text similarity measurement and index searching based on bigramspg_hint_plan | 1.4.2 | hint_plan |pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executedpg_trgm | 1.6 | public | text similarity measurement and index searching based on trigramspgcrypto | 1.3 | public | cryptographic functionsplpgsql | 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 1done
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/shrm -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参数进行升级。
这篇关于【流复制环境PostgreSQL-14.1到PostgreSQL-16.1大版本升级】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
