pg 大版本升级 15.4 --16.0
目录
一、 介绍
出于消除BUG、提升数据库性能、优化代码等不同原因，PG社区会定期更新数据库版本，最新版本为pg15，我们DBA在运维过程中也面临着数据库大版本升级这一切实的需求，目前PG大版本的升级方法主要有以下四种方式：
1.转储数据的方式（pg_dump或pg_dumpall导出数据，pg_restore或psql导入）
2.通过pg_upgrade进行升级
3.pg_logical扩展进行升级
4.通过内置逻辑复制的方式进行版本升级
二、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，包括快照和测试版本。
三、 参数介绍
-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参数只会检查新旧版本的兼容性，不会运行真正的升级程序，不会修改
数据文件，并且在命令结束时，会输出一份检查结果的报告，还会对需要手动调整的项做出简要的描述。
四、环境准备
| 节点类别 | 主机名 | IP |
|---|---|---|
| 主库 | node1 | 192.168.3.3 |
| 备库1 | node2 | 192.168.3.4 |
| 备库2 | node3 | 192.168.3.5 |
五、下载和pg16 安装包
5.1 下载地址
https://www.postgresql.org/ftp/source/v16.0/
5.2 解压和创建目录
[postgres@node1 soft]$ cd /postgresql/
[postgres@node1 postgresql]$ tar -xzvf postgresql-16.0.tar.gz
[postgres@node1 postgresql]$
[postgres@node1 postgresql]$ ll
total 56
drwxrwxr-x 2 postgres postgres 32768 Sep 19 16:09 archive
drwxrwxr-x 2 postgres postgres 6 Nov 5 2022 backup
drwxrwxr-x 6 postgres postgres 56 Nov 5 2022 pg15
drwx------ 20 postgres postgres 4096 Sep 20 12:51 pgdata
drwxrwxr-x 2 postgres postgres 6 Nov 5 2022 scripts
drwxrwxr-x 5 postgres postgres 191 Sep 20 15:06 soft
[postgres@node1 postgresql]$ mkdir pg16
[postgres@node1 postgresql]$ mkdir pgdata16
[postgres@node1 postgresql]$ cd soft/postgresql-16.0/
六、升级准备
注： pg主从复制 ，要先升级主库
6.1 编译安装
注：可能会出现问题1到问题13 尽量和老集群保持一致是segsize和blocksize
./configure --prefix=/postgresql/pg16 --with-pgport=5432 --with-openssl --with-perl --with-python --with-readline --with-libxml --with-libxslt
gmake -j 4 && gmake install -j 4
gmake world -j 4 && gmake install-world -j 4
6.2 初始化数据库
initdb -D /postgresql/pgdata16 -E UTF8 --locale=en_US.utf8 -U postgres
6.3 备份数据库
注：利用 pg_dump/pg_dumpall 对数据库进行备份，保证升级中遇到各种问题后以便回退。
/postgresql/pg15/bin/pg_dumpall > pgdumpallfull
6.4 升级检查
[postgres@node1 postgresql-16.0]$ pg_upgrade -b /postgresql/pg15/bin/ -B /postgresql/pg16/bin/ -d /postgresql/pgdata/ -D /postgresql/pgdata16/ -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 incompatible "aclitem" data type in user tables 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*
注：检查时会出现问题14 和问题15
6.5 正式升级数据库
[postgres@node1 postgresql-16.0]$ pg_upgrade -b /postgresql/pg15/bin/ -B /postgresql/pg16/bin/ -d /postgresql/pgdata/ -D /postgresql/pgdata16/
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 incompatible "aclitem" data type in user tables 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
------------------
Setting locale and encoding for new cluster ok
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 notice
Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/postgresql/pg16/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
6.6 更新hba及参数
将旧版本配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的新配置文件中再次进行修改。
[postgres@node1 pgdata16]$ cp ../pgdata/postgresql.* ./
[postgres@node1 pgdata16]$ cp ../pgdata/pg_hba.conf ./
6.7 先不要启动，用rsync升级standby
[postgres@node1 pg16]$ rsync --archive --delete --hard-links --size-only --no-inc-recursive /postgresql/pgdata16/ /postgresql/pgdata16/ node3:/postgresql/pgdata16/
[postgres@node1 pg16]$ rsync --archive --delete --hard-links --size-only --no-inc-recursive /postgresql/pgdata16/ /postgresql/pgdata16/ node2:/postgresql/pgdata16/
6.8 启动1主2从
6.9 更新统计信息
[postgres@node1 ~]$ /postgresql/pg16/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "appdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "cmdb": 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 "appdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "cmdb": 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 "appdb": Generating default (full) optimizer statistics
vacuumdb: processing database "cmdb": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
[postgres@node1 ~]$ psql
psql (16.0)
七、 验证升级
7.1 数据库版本检查
[postgres@node1 pgdata]$ psql
psql (16.0)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
postgres=#
7.2 检查主从同步
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 | w
rite_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+--
---------+-----------+------------+---------------+------------+-------------------------------
31018 | 16388 | repuser | walreceiver | 192.168.3.5 | | 14291 | 2023-09-21 06:45:21.653192+08 | | streaming | 3/3F0001C0 | 3/3F0001C0 | 3/3F0001C0 | 3/3F0001C0 |
| | | 0 | async | 2023-09-21 09:31:35.825259+08
29971 | 16388 | repuser | walreceiver | 192.168.3.4 | | 38845 | 2023-09-21 06:28:38.205624+08 | | streaming | 3/3F0001C0 | 3/3F0001C0 | 3/3F0001C0 | 3/3F0001C0 |
| | | 0 | async | 2023-09-21 09:31:42.234696+08
(2 rows)
postgres=#
7.3 创建测试表 验证主从同步
postgres=# create table userstest(id int,name text,age int);
CREATE TABLE
postgres=#
postgres=# postgres=# insert into userstest select n,md5(n::text),random()*30 from generate_series(1,100) n;
INSERT 0 100
postgres=#
在从库查询
[postgres@node2 pgdata16]$ psql
psql (16.0)
Type "help" for help.
postgres=#
postgres=# select count(*) from userstest;
count
-------
100
(1 row)
7.4 检查插件
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
btree_gist | 1.7 | public | support for indexing common datatypes in GiST
file_fdw | 1.0 | public | foreign-data wrapper for flat file access
pageinspect | 1.10 | public | inspect the contents of database pages at a low level
pg_permissions | 1.1 | public | view object permissions and compare them with the desired state
pg_stat_statements | 1.10 | 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
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
(8 rows)
postgres=#
八、过程问题处理
configure编译报错集锦
问题1：
checking for dtrace... no
configure: error: dtrace not found
解决方法：
yum search dtrace
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* extras: mirrors.163.com
* updates: mirrors.163.com
=============================================================================================== Matched: dtrace ===============================================================================================
systemtap-sdt-devel.i686 : Static probe support tools
systemtap-sdt-devel.x86_64 : Static probe support tools
找到了，就安装，我是64位的，安装第二个
[root@localhost ]# yum install -y systemtap-sdt-devel.x86_64
问题2：
checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
no
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法：
yum install perl-ExtUtils-Embed -y
问题3：
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法：
yum install perl-ExtUtils-Embed
问题4：
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
解决方法：
yum install readline readline-devel
问题5：
checking for inflate in -lz... no
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.
解决方法：
yum install zlib zlib-devel
问题6：
checking for CRYPTO_new_ex_data in -lcrypto... no
configure: error: library 'crypto' is required for OpenSSL
解决方法：
yum install openssl openssl-devel
问题7：
checking for pam_start in -lpam... no
configure: error: library 'pam' is required for PAM
解决方法：
yum install pam pam-devel
问题8：
checking for xmlSaveToBuffer in -lxml2... no
configure: error: library 'xml2' (version >= 2.6.23) is required for XML support
解决方法：
yum install libxml2 libxml2-devel
问题9：
checking for xsltCleanupGlobals in -lxslt... no
configure: error: library 'xslt' is required for XSLT support
解决方法：
yum install libxslt libxslt-devel
问题10：
configure: error: Tcl shell not found
解决方法：
yum install tcl tcl-devel
问题11：
checking for ldap.h... no
configure: error: header file is required for LDAP
解决方法：
yum install openldap openldap-devel
问题12：
checking for Python.h... no
configure: error: header file <Python.h> is required for Python
解决方法：
yum install python python-devel
问题13：
Error when bootstrapping CMake:
Cannot find appropriate C++ compiler on this system.
Please specify one using environment variable CXX.
See cmake_bootstrap.log for compilers attempted.
解决方法：
yum install gcc-c++
问题14 、pg_checksums
[postgres@node1 postgresql-16.0]$ pg_upgrade -b /postgresql/pg15/bin/ -B /postgresql/pg16/bin/ -d /postgresql/pgdata/ -D /postgresql/pgdata16/ -c
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
old cluster uses data checksums but the new one does not
Failure, exiting
[postgres@node1 ~]$ pg_checksums -e
Checksum operation completed
Files scanned: 948
Blocks scanned: 2883
Files written: 780
Blocks written: 2883
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
[postgres@node1 ~]$
[postgres@node1 ~]$
[postgres@node1 ~]$
[postgres@node1 ~]$ pg_checksums -P
22/22 MB (100%) computed
Checksum operation completed
Files scanned: 948
Blocks scanned: 2883
Bad checksums: 0
Data checksum version: 1
[postgres@node1 ~]$
问题15、 插件问题
[postgres@node1 postgresql-16.0]$
[postgres@node1 postgresql-16.0]$ pg_upgrade -b /postgresql/pg15/bin/ -B /postgresql/pg16/bin/ -d /postgresql/pgdata/ -D /postgresql/pgdata16/ -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 incompatible "aclitem" data type in user tables ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
/postgresql/pgdata16/pg_upgrade_output.d/20230920T154327.074/loadable_libraries.txt
Failure, exiting
[postgres@node1 postgresql-16.0]$ cat /postgresql/pgdata16/pg_upgrade_output.d/20230920T154327.074/loadable_libraries.txt
could not load library "$libdir/hypopg": ERROR: incompatible library "/postgresql/pg16/lib/hypopg.so": version mismatch
DETAIL: Server is version 16, library is version 15.
In database: postgres
could not load library "$libdir/orafce": ERROR: could not load library "/postgresql/pg16/lib/orafce.so": /postgresql/pg16/lib/orafce.so: undefined symbol: pg_namespace_aclcheck
In database: postgres
could not load library "$libdir/pgaudit": ERROR: could not load library "/postgresql/pg16/lib/pgaudit.so": /postgresql/pg16/lib/pgaudit.so: undefined symbol: bms_is_empty
In database: postgres
could not load library "$libdir/zhparser": ERROR: incompatible library "/postgresql/pg16/lib/zhparser.so": version mismatch
DETAIL: Server is version 16, library is version 15.
In database: postgres
处理方法：
启动老的库 drop插件 ，后续在安装
[postgres@node1 ~]$ source .bash_profile
[postgres@node1 ~]$ pg_ctl start
waiting for server to start....2023-09-20 16:50:30.473 CST [32602] LOG: pgaudit extension initialized
2023-09-20 16:50:30.694 CST [32602] LOG: redirecting log output to logging collector process
2023-09-20 16:50:30.694 CST [32602] HINT: Future log output will appear in directory "pg_log".
done
server started
[postgres@node1 ~]$
[postgres@node1 ~]$
[postgres@node1 ~]$ psql
psql (15.4)
Type "help" for help.
postgres=# drop extension walminer ;
DROP EXTENSION
postgres=#
[postgres@node1 postgresql-16.0]$ cat /postgresql/pgdata16/pg_upgrade_output.d/20230920T165711.411/loadable_libraries.txt
could not load library "$libdir/hypopg": ERROR: incompatible library "/postgresql/pg16/lib/hypopg.so": version mismatch
DETAIL: Server is version 16, library is version 15.
In database: postgres
could not load library "$libdir/orafce": ERROR: could not load library "/postgresql/pg16/lib/orafce.so": /postgresql/pg16/lib/orafce.so: undefined symbol: pg_namespace_aclcheck
In database: postgres
could not load library "$libdir/pgaudit": ERROR: could not load library "/postgresql/pg16/lib/pgaudit.so": /postgresql/pg16/lib/pgaudit.so: undefined symbol: bms_is_empty
In database: postgres
could not load library "$libdir/zhparser": ERROR: incompatible library "/postgresql/pg16/lib/zhparser.so": version mismatch
DETAIL: Server is version 16, library is version 15.
In database: postgres
[postgres@node1 ~]$
[postgres@node1 ~]$ psql
psql (15.4)
Type "help" for help.
postgres=#
postgres=# drop extension orafce ;
drop extension hypopg ;
drop extension pgaudit ;
DROP EXTENSION
postgres=# DROP EXTENSION
postgres=# DROP EXTENSION
postgres=# drop extension zhparser ;
ERROR: cannot drop extension zhparser because other objects depend on it
DETAIL: text search configuration testzhcfg depends on text search parser zhparser
column info_tsv of table t45 depends on text search configuration testzhcfg
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# drop extension zhparser CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to text search configuration testzhcfg
drop cascades to column info_tsv of table t45
DROP EXTENSION
postgres=#
