目录
一、 介绍
PG的小版本升级是十分简单的，小版本的升级是为了修复一些bug缺陷,或者安全性上的问题，对主版本PG上的功能是没有任何影响的。
这次的升级案例是 PG 从15.0升级达到 15.4的版本。 数据库架构是一主2从复制模式。
二、环境准备
| 节点类别 | 主机名 | IP |
|---|---|---|
| 主库 | node1 | 192.168.3.3 |
| 备库1 | node2 | 192.168.3.4 |
| 备库2 | node3 | 192.168.3.5 |
三、安装包准备
3.1 下载安装包
下载PG15.4 的版本 https://www.postgresql.org/ftp/source/v15.4/
3.2 上传与解压软件包
[postgres@node3 soft]$ tar -xvf postgresql-15.4.tar.gz
[postgres@node3 soft]$ ll
total 57984
drwxrwxr-x 6 postgres postgres 4096 Nov 5 2022 postgresql-15.0
-rw-rw-r-- 1 postgres postgres 29385837 Nov 5 2022 postgresql-15.0.tar.gz
drwxrwxr-x 6 postgres postgres 4096 Aug 8 04:24 postgresql-15.4
-rw-rw-r-- 1 postgres postgres 29978353 Sep 19 14:48 postgresql-15.4.tar.gz
四、升级数据库
4.1 从库2上进行安装
4.1.1 当前版本是15.0, 关闭数据库
[postgres@node3 ~]$ psql
psql (15.0)
Type "help" for help.
postgres=# 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-36), 64-bit
(1 row)
postgres=# \q
[postgres@node3 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
4.1.2 执行 config, make, make install 等源码编译的命令
注：这里需要注意的是 config 需要和 上一次 PG 版本15.0 config的时候 保持参数一致
[postgres@node3 ~]$ pg_config |grep CONFIGURE
CONFIGURE = '--prefix=/postgresql/pg15' '--without-readline'
[postgres@node3 ~]$
cd postgresql-15.4
./configure --prefix=/postgresql/pg15 --without-readline
make
make install
注：如果没有任何报错， 我们下来执行 make world ,make install-world
make world
make install-world
4.1.3 至此软件升级完毕，查询一下升级后软件的版本
[postgres@node3 postgresql-15.4]$ pg_config | grep VERSION
VERSION = PostgreSQL 15.4
[postgres@node3 postgresql-15.4]$
4.1.4启动一下 standby2 的数据库
[postgres@node3 postgresql-15.4]$ pg_ctl start
waiting for server to start....2023-09-19 15:58:23.788 CST [31030] LOG: pgaudit extension initialized
2023-09-19 15:58:23.914 CST [31030] LOG: redirecting log output to logging collector process
2023-09-19 15:58:23.914 CST [31030] HINT: Future log output will appear in directory "pg_log".
done
server started
[postgres@node3 postgresql-15.4]$ psql
psql (15.4)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.4 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=#
4.2 从库1上进行安装
4.2.1 当前版本是15.0, 关闭数据库
[postgres@node2 ~]$ psql
psql (15.0)
Type "help" for help.
postgres=# 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-36), 64-bit
(1 row)
postgres=# \q
[postgres@node2 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
4.2.2 执行 config, make, make install 等源码编译的命令
注：这里需要注意的是 config 需要和 上一次 PG 版本15.0 config的时候 保持参数一致
[postgres@node2 ~]$ pg_config |grep CONFIGURE
CONFIGURE = '--prefix=/postgresql/pg15' '--without-readline'
[postgres@node2 ~]$
cd postgresql-15.4
./configure --prefix=/postgresql/pg15 --without-readline
make
make install
注：如果没有任何报错， 我们下来执行 make world ,make install-world
make world
make install-world
4.2.3 至此软件升级完毕，查询一下升级后软件的版本
[postgres@node2 postgresql-15.4]$ pg_config | grep VERSION
VERSION = PostgreSQL 15.4
[postgres@node2 postgresql-15.4]$
4.2.4启动一下 standby1 的数据库
[postgres@node2 postgresql-15.4]$ pg_ctl start
waiting for server to start....2023-09-19 15:58:23.788 CST [31030] LOG: pgaudit extension initialized
2023-09-19 15:58:23.914 CST [31030] LOG: redirecting log output to logging collector process
2023-09-19 15:58:23.914 CST [31030] HINT: Future log output will appear in directory "pg_log".
done
server started
[postgres@node2 postgresql-15.4]$ psql
psql (15.4)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.4 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=#
4.3 主库上进行安装
4.3.1 当前版本是15.0, 关闭数据库
[postgres@node1 ~]$ psql
psql (15.0)
Type "help" for help.
postgres=# 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-36), 64-bit
(1 row)
postgres=# \q
[postgres@node1 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
4.3.2 执行 config, make, make install 等源码编译的命令
注：这里需要注意的是 config 需要和 上一次 PG 版本15.0 config的时候 保持参数一致
[postgres@node1 ~]$ pg_config |grep CONFIGURE
CONFIGURE = '--prefix=/postgresql/pg15' '--without-readline'
[postgres@node1 ~]$
cd postgresql-15.4
./configure --prefix=/postgresql/pg15 --without-readline
make
make install
注：如果没有任何报错， 我们下来执行 make world ,make install-world
make world
make install-world
4.3.3 至此软件升级完毕，查询一下升级后软件的版本
[postgres@node1 postgresql-15.4]$ pg_config | grep VERSION
VERSION = PostgreSQL 15.4
[postgres@node1 postgresql-15.4]$
4.3.4启动一下 主库 的数据库
[postgres@node1 postgresql-15.4]$ pg_ctl start
waiting for server to start....2023-09-19 15:58:23.788 CST [31030] LOG: pgaudit extension initialized
2023-09-19 15:58:23.914 CST [31030] LOG: redirecting log output to logging collector process
2023-09-19 15:58:23.914 CST [31030] HINT: Future log output will appear in directory "pg_log".
done
server started
[postgres@node1 postgresql-15.4]$ psql
psql (15.4)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.4 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=#
五、检查一下插件
[postgres@node3 postgresql-15.4]$ psql
psql (15.4)
Type "help" for help.
postgres=# \sx
invalid command \sx
Try \? for help.
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
hypopg | 1.4.0 | public | Hypothetical indexes for PostgreSQL
orafce | 4.6 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
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
pgaudit | 1.7 | public | provides auditing functionality
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
walminer | 3.0 | public | analyse wal to SQL
zhparser | 2.2 | public | a parser for full-text search of Chinese
(13 rows)
六、主库查看 同步延迟
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
-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+--
---------------+-----------------+-----------------+---------------+------------+-------------------------------
24487 | 16388 | repuser | walreceiver | 192.168.3.4 | | 33615 | 2023-09-19 16:13:20.867203+08 | | streaming | 2/F901E6B0 | 2/F901E6B0 | 2/F901E6B0 | 2/F901E6B0 | 0
0:00:00.000335 | 00:00:00.001863 | 00:00:00.002067 | 0 | async | 2023-09-19 16:28:22.429572+08
24724 | 16388 | repuser | walreceiver | 192.168.3.5 | | 16433 | 2023-09-19 16:22:49.931795+08 | | streaming | 2/F901E6B0 | 2/F901E6B0 | 2/F901E6B0 | 2/F901E6B0 | 0
0:00:00.000286 | 00:00:00.001131 | 00:00:00.001227 | 0 | async | 2023-09-19 16:28:22.718429+08
(2 rows)
七、创建测试表
postgres=# create table t09 (id int,name text);
CREATE TABLE
postgres=# insert into t09 select repeat(md5(n::text),100) from generate_series(1,10) n;
ERROR: column "id" is of type integer but expression is of type text
LINE 1: insert into t09 select repeat(md5(n::text),100) from generat...
^
HINT: You will need to rewrite or cast the expression.
postgres=# insert into t09 select n,repeat(md5(n::text),100) from generate_series(1,10) n;
INSERT 0 10
备库：检查
postgres=# select count(*) from t09;
count
-------
10
(1 row)