postgresql数据库升级，小版本及大版本 pg_upgrade

jnrjian

已于 2023-03-29 10:18:35 修改

阅读量4.4k

点赞数

文章标签：数据库 postgresql

于 2023-03-29 10:08:15 首次发布

原文链接：https://postgresql.blog.csdn.net/article/details/129704514?spm=1001.2014.3001.5502

版权

PG 小版本升级直接替换执行文件

PG 大版本升级类似oracle 的startup upgrade方式

pg如何进行小版本升级

环境
系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：14
文档用途
本文以pg14.2升级至pg14.6为例，向各位读者介绍pg小版本升级的方法及步骤。

详细信息

一、背景介绍
当前PostgreSQL版本号由主要版本号和小版本号组成。例如，在版本号10.1中，10是主要版本号，1是小要版本号，这意味着这将是主要版本10的第一个小版本。对于PostgreSQL版本10.0之前的版本，版本号由三个数字组成，例如9.5.3。在这些情况下，主要版本由版本号的前两位数组组成，例如9.5，而小版本是第三个数字，例如3，这意味着这将是主要版本9.5的第三个小版本。

小版本不会更改内部存储格式，并且始终与相同主要版本号的早期和后续小版本兼容。例如，版本10.1与版本10.0和版本10.6兼容。同样的9.5.3与9.5.0、9.5.1和9.5.6兼容。要在兼容版本之间进行更新，只需在数据库服务关闭时更换可执行文件并重新启动服务器即可。数据目录保持不变-小版本升级就这么简单。

以上信息为pg社区官方说明，但在实际的生产环境中，升级前需要详细的阅读各版本的release说明，比如在pg10.19升级至pg10.20的版本介绍中，为了解决一项inde-only扫描的bug,在升级完成后需要s手动重建btree索引。

二、升级步骤归纳

根据PG社区提供的说明，pg小版本升级只需在停止数据库服务后覆盖安装bin目录下的可执行命令即可，但在实际的操作过程中，推荐读者首先阅读个版本的升级说明，看是否有一些升级后续的操作事宜。pg小版本升级在具体的生产环境中的过程主要分为以下两种场景：

场景一、数据库的安装目录、数据目录未包含版本号，如/usr/local/pgsql/bin /usr/local/pgsql/data

在这种场景下，只需要规划新的安装路径，编译安装好新版本数据库，停止旧版本数据库服务后，将新版本数据库的bin目录内的文件copy到/usr/local/pgsql/bin下覆盖，启动数据库即可。如果设置了pg的开机自启服务，pg的开机自启服务文件也不需要修改。

场景二、数据库的安装目录、数据目录均包含版本号，如/usr/local/pg14.2/bin /usr/local/pg14.2/data

在这种情景下，编译安装新版本数据库，停止旧版本数据库，将旧版本数据库的数据目录移动到新装的数据库的对应路径，启动该数据库即可。此种场景下需要修改环境变量信息，另外如果设置了pg的开机自启服务，还需要修改对应的service文件，防止开机自启服务失效。

三、小版本升级示例

场景一过程简单，下面以第二种场景作为示例给读者演示下pg14.2升级到pg14.6的升级过程

1、平台信息

pg版本 pg14.2 pg14.6

OS版本：Redhat 7.7

CPU型号：X86_64

2、升级前环境变量信息

[postgres@mode2 ~]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PATH=/data/pg14.2/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATH

export PGHOME=/data/pg14.2

export PGDATA=$PGHOME/data

export PGPORT=5432

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
3、pg14.2数据库明细

[postgres@mode2 ~]$ psql

psql (14.2)

Type "help" for help.

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

a | a | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 189 MB | pg_default |

(4 rows)

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+--------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databases from within a database

pg_profile | 0.3.6 | public | PostgreSQL load profile repository and report builder

pg_stat_statements | 1.9 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 rows)

postgres=# \c a a

You are now connected to database "a" as user "a".

a=> \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+-------------------------------------------------------------------

pg_trgm | 1.6 | public | text similarity measurement and index searching based on trigrams

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(2 rows)

a=> \dt+

List of relations

--------+------------------+-------+-------+-------------+---------------+--------+-------------

public | ch | table | a | permanent | heap | 79 MB |

public | t | table | a | permanent | heap | 472 kB |

public | t1 | table | a | permanent | heap | 448 kB |

public | t2 | table | a | permanent | heap | 448 kB |

(8 rows)

a=> select count(*) from ch ;

count

---------

1210000

(1 row)

a=> select * from ch limit 1;

id | hanzi | num

----+----------------------+-----

54 | 鵶獷跌緗鉄燴聀鋧扴鄨 | 1

(1 row)

a=> select * from ch where num=1;

id | hanzi | num

----+----------------------+-----

54 | 鵶獷跌緗鉄燴聀鋧扴鄨 | 1

56 | 扝噲蹘傝膷倱蕠鷦前哆 | 1

10 | 輨墦嬮牛鞑鶓欄鍷捻奉 | 1

91 | 刂唹錒浣酂锵透热壾氃 | 1

(4 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
4、解压缩安装pg14.6，修改安装文件属主

[root@mode2 download]# tar -zxvf postgresql-14.6.tar.gz

[root@mode2 download]# cd postgresql-14.6/

##编译安装过程略

[root@mode2 postgresql-14.6]# ./configure --prefix=/data/pg14.6 --with-perl --with-python --with-openssl --with-readline --with-libxml --with-libxslt

[root@mode2 postgresql-14.6]# make world -j 4 && make install-world -j 4

##修改安装完成后的文件属主为postgres

[root@mode2 postgresql-14.6]# chown -R postgres:postgres /data/pg14.6

[root@mode2 postgresql-14.6]# ll /data/pg14.6

总用量 12

drwxr-xr-x 2 postgres postgres 4096 12月 7 17:18 bin

drwxr-xr-x 4 postgres postgres 4096 12月 7 17:18 include

drwxr-xr-x 4 postgres postgres 4096 12月 7 17:18 lib

drwxr-xr-x 5 postgres postgres 46 12月 7 17:18 share
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
5、停止pg14.2数据库

[postgres@mode2 ~]$ pg_ctl stop

waiting for server to shut down....2022-12-07 17:27:21.298 CST [13677] LOG: received fast shutdown request

2022-12-07 17:27:21.298 CST [13677] LOG: aborting any active transactions

2022-12-07 17:27:21.299 CST [13677] LOG: background worker "logical replication launcher" (PID 13684) exited with exit code 1

2022-12-07 17:27:21.299 CST [13679] LOG: shutting down

2022-12-07 17:27:21.306 CST [13677] LOG: database system is shut down

done

server stopped
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
6、拷贝data目录到新版本数据库数据目录下

[postgres@mode2 pg14.2]$ cp -rf data /data/pg14.6/

[postgres@mode2 pg14.2]$ vi ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PATH=/data/pg14.6/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATH

export PGHOME=/data/pg14.6

export PGDATA=$PGHOME/data

export PGPORT=5432

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH

[postgres@mode2 pg14.2]$ source ~/.bash_profile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
7、核对版本信息、数据库数据

[postgres@mode2 pg14.2]$ pg_ctl start

waiting for server to start....2022-12-07 18:12:55.516 CST [14453] LOG: starting PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

2022-12-07 18:12:55.516 CST [14453] LOG: listening on IPv6 address "::1", port 5432

2022-12-07 18:12:55.516 CST [14453] LOG: listening on IPv4 address "127.0.0.1", port 5432

2022-12-07 18:12:55.518 CST [14453] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2022-12-07 18:12:55.541 CST [14454] LOG: database system was shut down at 2022-12-07 18:10:47 CST

2022-12-07 18:12:55.545 CST [14453] LOG: database system is ready to accept connections

done

server started

[postgres@mode2 ~]$ psql

psql (14.6)

Type "help" for help.

postgres=# select version();

version

---------------------------------------------------------------------------------------------------------

PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

(1 row)

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

a | a | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 190 MB | pg_default |

(4 rows)

postgres=# \c a a

You are now connected to database "a" as user "a".

a=> select * from ch where num=1;

id | hanzi | num

----+----------------------+-----

54 | 鵶獷跌緗鉄燴聀鋧扴鄨 | 1

56 | 扝噲蹘傝膷倱蕠鷦前哆 | 1

91 | 刂唹錒浣酂锵透热壾氃 | 1

10 | 輨墦嬮牛鞑鶓欄鍷捻奉 | 1

(4 rows)

a=> \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+-------------------------------------------------------------------

pg_trgm | 1.6 | public | text similarity measurement and index searching based on trigrams

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(2 rows)

a=> \c postgres postgres

You are now connected to database "postgres" as user "postgres".

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+--------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databases from within a database

pg_profile | 0.3.6 | public | PostgreSQL load profile repository and report builder

pg_stat_statements | 1.9 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 rows)

环境
系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：12,14
文档用途
本文主要介绍pg_upgrade这一工具，以pg12升级至pg14为例，给读者演示这一工具如何使用及使用中的注意事项。

详细信息
一、背景说明
出于消除BUG、提升数据库性能、优化代码等不同原因，PG社区会定期更新数据库版本，最新版本为pg15，我们DBA在运维过程中也面临着数据库大版本升级这一切实的需求，目前PG大版本的升级方法主要有以下四种方式：

1.转储数据的方式（pg_dump或pg_dumpall导出数据，pg_restore或psql导入）

2.通过pg_upgrade进行升级

3.pg_logical扩展进行升级

4.通过内置逻辑复制的方式进行版本升级

本次给大家介绍如何通过pg_upgrade工具进行大版本升级。同转储数据的方式相比较，在存量业务数据较大的场景下，pg_upgrade升级相对省时，但是升级总是有风险的，例如升级过程中的硬件故障等，所以第一重要的事情依然是做好备份。升级之前需要检查旧版本已经安装的外部扩展，有一些外部扩展要求在升级之前先升级旧版本的外部扩展，例如PostGIS。

二、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，包括快照和测试版本。

1、参数选项
-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参数只会检查新旧版本的兼容性，不会运行真正的升级程序，不会修改数据文件，并且在命令结束时，会输出一份检查结果的报告，还会对需要手动调整的项做出简要的描述。

2、升级模式区分
pg_upgrade有普通模式和Link模式两种升级模式。在普通模式下，会把旧版本的数据拷贝到新版本中，所以如果使用普通模式升级，要确保有足够的磁盘空间存储新旧两份数据；link模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬链接，可以有效减少磁盘占用的空间。

3、升级步骤归纳
1）安装新版本PostgreSQL。注意新版本数据库的安装目录一定要同旧版本区分开，如果使用源码编译安装则手动指定安装目录即可，如果使用rpm包安装且安装目录是/usr/local/pgsql的方式，则推荐将旧版本数据库目录重命名，如果旧版本数据库中安装的扩展存在.so

2）初始化新版本PostgreSQL数据目录。

3）停止旧版本数据库。若只做升级前兼容性测试且期间数据库不会做数据结构修改，则不用停止旧版本数据库，若使用普通模式升级，则将postgresql.conf、postgresql.auto.conf及pg_hba.conf文件进行备份。

4）旧集簇使用的所有自定义共享对象文件（或者 DLL）安装到新集簇中，例如pgcrypto.so，不管它们是来自于 contrib还是某些其他源码。

5）检查新旧版本兼容性。最后一行输出“Clusters are compatible”说明已经通过兼容性测试，如果最后一行输出“Failure，exiting”，说明新旧版本不兼容，这时应该查看输出中给出的提示，手动消除这些冲突，直到通过兼容性测试。

6）使用pg_upgrade普通模式升级。旧版本数据库必须是停止状态，如果运行pg_upgrade失败，必须重新初始化新版本的数据目录。看到“Upgrade Complete”说明升级已经顺利完成。

使用pg_upgrade的link模式升级：首先需要了解旧版本有哪些Extension及表空间，当使用链接模式运行pg_upgrade之后，pg_upgrade程序会把旧版本数据目录中的pg_control文件重命名为pg_control.old，如果仍然想运行旧版本的数据库实例，需要把pg_control.old重命名回pg_control。但是一旦使用新版本启动了数据库实例，旧的实例将无法再被访问，这一点一定要注意。

7）将旧版本数据库的相关参数配置文件及pg配置文件及pg_hba.conf文件移动到新版本数据库数据目录内，并根据实际情况决定是否只启用本地连接，若使用Link升级方式，则忽略本步。

8）启动新版本数据库实例。

9）更新统计信息。pg_upgrade会创建新的系统表，并重用旧的数据进行升级，统计信息并不会随升级过程迁移，所以在启用新版本之前，应该首先重新收集统计信息，避免没有统计信息导致错误的查询计划。

在升级结束后，根据提示使用vacuumdb --all --analyze-in-stages命令。

10）登录并验证业务数据。

11）清除旧版本数据库，根据升级提示，运行清理旧数据脚本。

三、场景演示
环境信息：

升级前版本：pg12.3

旧版本安装目录：/data/pg12.3

升级后版本：pg14.4

升级后安装目录：/data/pg14.4

pg14.4已通过源码编译安装，未初始化data目录

1、普通模式升级
1）pg12.3环境变量信息检查

[postgres@mode2 ~]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PATH=/data/pg12.3/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATH

export PGHOME=/data/pg12.3

export PGDATA=$PGHOME/data

export PGPORT=5432

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2）pg12.3数据库升级前信息统计

[postgres@mode2 ~]$ psql

psql (12.3)

Type "help" for help.

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+---------------------------

-----------------

a | a | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 189 MB | pg_default |

nection database

atabases

(4 rows)

##postgres库内已安装扩展明细

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+--------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databases from within a database

pg_profile | 0.3.6 | public | PostgreSQL load profile repository and report builder

pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 rows)

##a库内已安装扩展明细

postgres=# \c a a

You are now connected to database "a" as user "a".

a=> \dx

List of installed extensions

Name | Version | Schema | Description

---------+---------+------------+-------------------------------------------------------------------

pg_trgm | 1.4 | public | text similarity measurement and index searching based on trigrams

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(2 rows)

##a库内示例表明细

a=> \dt+

List of relations

--------+------------------+-------+-------+--------+-------------

public | ch | table | a | 79 MB |

public | t | table | a | 472 kB |

public | t1 | table | a | 448 kB |

public | t2 | table | a | 448 kB |

(8 rows)

##示例表ch、pg_bench_accounts表结构明细

a=> \d+ ch

Table "public.ch"

--------+---------+-----------+----------+---------+----------+--------------+-------------

Indexes:

"ind_ch_hanzi" btree (hanzi)

Access method: heap

a=> \d+ pgbench_accounts

Table "public.pgbench_accounts"

----------+---------------+-----------+----------+---------+----------+--------------+-------------

Indexes:

"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Access method: heap

Options: fillfactor=100

a=> \q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
3）查看pg12.3部分postgresql.conf参数及pg_hba.conf文件

[postgres@mode2 ~]$ cd $PGDATA

[postgres@mode2 data]$ pwd

/data/pg12.3/data

[postgres@mode2 data]$ cat postgresql.conf |grep shared_buffers

shared_buffers = 256MB # min 128kB

#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers

[postgres@mode2 data]$ cat postgresql.conf |grep wal_level

wal_level = logical # minimal, replica, or logical

[postgres@mode2 data]$ cat pg_hba.conf |grep -v '#'

local all all trust

host all all 127.0.0.1/32 trust

host all all 192.168.164.50/32 trust

host all all 192.168.164.51/32 trust

host all all 192.168.164.52/32 trust

host all all ::1/128 trust

local replication all trust

host replication all 127.0.0.1/32 trust

host replication all 192.168.164.0/24 trust

host replication all ::1/128 trust
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
4）初始化pg14.4的data目录

[postgres@mode2 pg14.4]$ ./bin/initdb -D /data/pg14.4/data

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 "zh_CN.UTF-8".

The default database encoding has accordingly been set to "UTF8".

initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"

The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /data/pg14.4/data ... 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:

./bin/pg_ctl -D /data/pg14.4/data -l logfile start
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
5）停止pg12.3的数据库服务

[postgres@mode2 data]$ pg_ctl status

pg_ctl: server is running (PID: 3459)

/data/pg12.3/bin/postgres

[postgres@mode2 data]$ pg_ctl stop

waiting for server to shut down....2022-12-06 11:06:26.627 CST [3459] LOG: received fast shutdown request

2022-12-06 11:06:26.628 CST [3459] LOG: aborting any active transactions

2022-12-06 11:06:26.701 CST [3459] LOG: background worker "logical replication launcher" (PID 3466) exited with exit code 1

2022-12-06 11:06:26.701 CST [3461] LOG: shutting down

2022-12-06 11:06:26.846 CST [3459] LOG: database system is shut down

done

server stopped
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
6）进行升级兼容性测试，如升级窗口期内无数据结构变更，也可不停库进行兼容性测试，但升级过程必须停库

[postgres@mode2 data]$ /data/pg14.4/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.4/bin -d /data/pg12.3/data/ -D /data/pg14.4/data -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 user-defined encoding conversions ok

Checking for user-defined postfix operators 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*
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
7）升级pg12.3至pg14.4

[postgres@mode2 data]$ /data/pg14.4/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.4/bin -d /data/pg12.3/data/ -D /data/pg14.4/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

Creating dump of global objects ok

Creating dump of database schemas

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

Copying user relation files

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:

/data/pg14.4/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:

./delete_old_cluster.sh

##可看到pg12.3数据目录内自动生成的扩展升级SQL及删除旧数据目录的脚本

[postgres@mode2 data]$ ll

总用量 272

-rw-rw-r-- 1 postgres postgres 173140 12月 6 10:34 awr_report_postgres_1_2.html

drwx------ 7 postgres postgres 71 11月 29 15:00 base

-rwx------ 1 postgres postgres 38 12月 6 11:08 delete_old_cluster.sh

drwx------ 2 postgres postgres 4096 12月 6 11:07 global

-rw-rw-r-- 1 postgres postgres 1105 12月 6 10:26 pgbench_log.3486

-rw-rw-r-- 1 postgres postgres 1104 12月 6 10:26 pgbench_log.3486.1

-rw-rw-r-- 1 postgres postgres 1104 12月 6 10:26 pgbench_log.3486.2

-rw-rw-r-- 1 postgres postgres 1104 12月 6 10:26 pgbench_log.3486.3

-rw-rw-r-- 1 postgres postgres 1112 12月 6 10:30 pgbench_log.3609

-rw-rw-r-- 1 postgres postgres 1112 12月 6 10:30 pgbench_log.3609.1

-rw-rw-r-- 1 postgres postgres 1112 12月 6 10:30 pgbench_log.3609.2

-rw-rw-r-- 1 postgres postgres 1113 12月 6 10:30 pgbench_log.3609.3

drwx------ 2 postgres postgres 6 2月 24 2022 pg_commit_ts

drwx------ 2 postgres postgres 6 2月 24 2022 pg_dynshmem

-rw------- 1 postgres postgres 4712 12月 6 09:52 pg_hba.conf

-rw------- 1 postgres postgres 1636 2月 24 2022 pg_ident.conf

drwx------ 4 postgres postgres 68 12月 6 11:07 pg_logical

drwx------ 4 postgres postgres 36 2月 24 2022 pg_multixact

drwx------ 2 postgres postgres 18 12月 6 11:07 pg_notify

drwx------ 2 postgres postgres 6 2月 24 2022 pg_replslot

drwx------ 2 postgres postgres 6 2月 24 2022 pg_serial

drwx------ 2 postgres postgres 6 2月 24 2022 pg_snapshots

drwx------ 2 postgres postgres 132 12月 6 11:07 pg_stat

drwx------ 2 postgres postgres 6 12月 6 11:07 pg_stat_tmp

drwx------ 2 postgres postgres 18 12月 6 10:33 pg_subtrans

drwx------ 2 postgres postgres 6 2月 24 2022 pg_tblspc

drwx------ 2 postgres postgres 6 2月 24 2022 pg_twophase

-rw------- 1 postgres postgres 3 2月 24 2022 PG_VERSION

drwx------ 3 postgres postgres 4096 12月 6 10:44 pg_wal

drwx------ 2 postgres postgres 18 2月 24 2022 pg_xact

-rw------- 1 postgres postgres 88 2月 24 2022 postgresql.auto.conf

-rw------- 1 postgres postgres 26887 12月 6 10:53 postgresql.conf

-rw------- 1 postgres postgres 179 12月 6 11:07 postmaster.opts

-rw------- 1 postgres postgres 108 12月 6 11:08 update_extensions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
7）将pg12.3的相关配置文件移动到pg14.4的data目录中，启动数据库

[postgres@mode2 data]$ cp pg_hba.conf /data/pg14.4/data

[postgres@mode2 data]$ cp postgresql.conf /data/pg14.4/data/postgresql.base.conf

[postgres@mode2 data]$ vi /data/pg14.4/data/postgresql.conf

##在最后一行增加如下内容

include postgresql.base.conf

[postgres@mode2 pg12.3]$ cd /data/pg14.4

[postgres@mode2 data]$ /data/pg14.4/bin/pg_ctl start -D /data/pg14.4/data

waiting for server to start....2022-12-06 14:05:14.940 CST [16455] LOG: starting PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

2022-12-06 14:05:14.950 CST [16455] LOG: listening on IPv6 address "::1", port 5432

2022-12-06 14:05:14.950 CST [16455] LOG: listening on IPv4 address "127.0.0.1", port 5432

2022-12-06 14:05:14.951 CST [16455] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2022-12-06 14:05:14.970 CST [16456] LOG: database system was shut down at 2022-12-06 11:08:05 CST

2022-12-06 14:05:15.055 CST [16455] LOG: database system is ready to accept connections

done

server started
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
8）根据提示执行扩展升级的SQL语句，发现报symbol错误，此问题为环境变量仍为pg12.3原因导致，手动设置下环境变量

[postgres@mode2 data]$ /data/pg14.4/bin/psql -f update_extensions.sql

/data/pg14.4/bin/psql: symbol lookup error: /data/pg14.4/bin/psql: undefined symbol: PQmblenBounded

##手动设置环境后再次执行SQL

[postgres@mode2 data]$ export LD_LIBRARY_PATH=/data/pg14.4/lib

[postgres@mode2 data]$

[postgres@mode2 data]$ /data/pg14.4/bin/psql -U postgres -f update_extensions.sql

You are now connected to database "a" as user "postgres".

ALTER EXTENSION

You are now connected to database "postgres" as user "postgres".

ALTER EXTENSION
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
9）根据升级过程中的提示更新统计信息

[postgres@mode2 data]$ /data/pg14.4/bin/vacuumdb --all --analyze-in-stages

vacuumdb: processing database "a": 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 "a": 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 "a": Generating default (full) optimizer statistics

vacuumdb: processing database "postgres": Generating default (full) optimizer statistics

vacuumdb: processing database "template1": Generating default (full) optimizer statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
10）登录升级后的数据库核对信息,升级后数据库参数明细

[postgres@mode2 data]$ /data/pg14.4/bin/psql

psql (14.4)

Type "help" for help.

postgres=# show shared_buffers ;

shared_buffers

----------------

256MB

(1 row)

postgres=# show wal_level ;

wal_level

-----------

logical

(1 row)

postgres=# show shared_preload_libraries ;

shared_preload_libraries

--------------------------

pg_stat_statements

(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
11）录升级后数据库核对数据

##升级后数据库明细

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

a | a | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 189 MB | pg_default |

(4 rows)

##升级后示例扩展明细

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+--------------------------------------------------------------

dblink | 1.2 | public | connect to other PostgreSQL databases from within a database

pg_profile | 0.3.6 | public | PostgreSQL load profile repository and report builder

pg_stat_statements | 1.9 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 rows)

##升级后示例表明细

postgres=# \c a a

You are now connected to database "a" as user "a".

a=> \dt+

List of relations

--------+------------------+-------+-------+-------------+---------------+--------+-------------

public | ch | table | a | permanent | heap | 79 MB |

public | t | table | a | permanent | heap | 472 kB |

public | t1 | table | a | permanent | heap | 448 kB |

public | t2 | table | a | permanent | heap | 448 kB |

(8 rows)

a=> select count(*) from ch ;

count

---------

1210000

(1 row)

a=> select count(*) from pgbench_accounts ;

count

--------

100000

(1 row)

#升级后示例表ch、pg_bench_accounts表结构明细

a=> \d+ ch

Table "public.ch"

--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------

Indexes:

"ind_ch_hanzi" btree (hanzi)

Access method: heap

a=> \d+ pgbench_accounts

Table "public.pgbench_accounts"

----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------

Indexes:

"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Access method: heap

Options: fillfactor=100

a=> \q

[postgres@mode2 data]$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
12）根据升级完成后的提示，验证无误后执行删除旧数据目录脚本，清理空间

[postgres@mode2 data]$ ls

awr_report_postgres_1_2.html pgbench_log.3486 pgbench_log.3609 pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postmaster.opts

base pgbench_log.3486.1 pgbench_log.3609.1 pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact update_extensions.sql

delete_old_cluster.sh pgbench_log.3486.2 pgbench_log.3609.2 pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf

global pgbench_log.3486.3 pgbench_log.3609.3 pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

[postgres@mode2 data]$ cat delete_old_cluster.sh

#!/bin/sh

rm -rf '/data/pg12.3/data'

[postgres@mode2 data]$ ./delete_old_cluster.sh

[postgres@mode2 data]$ ls
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
13）修改环境变量为pg14.4，并使之生效

[postgres@mode2 data]$ vi ~/.bash_profile

[postgres@mode2 data]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PATH=/data/pg14.4/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATH

export PGHOME=/data/pg14.4

export PGDATA=$PGHOME/data

export PGPORT=5432

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH

[postgres@mode2 data]$ source ~/.bash_profile

[postgres@mode2 data]$ psql

psql (14.4)

Type "help" for help.

postgres=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2、LINK模式升级
环境信息：

升级前版本：pg12.3

旧版本安装目录：/data/pg12.3

升级后版本：pg14.2

升级后安装目录：/data/pg14.2

1）pg14.2已通过源码编译安装，未初始化data目录

##还原环境，环境变量切换为pg12.3的环境变量，数据库各类明细同上一步相同，不再单独列出

[postgres@mode2 data]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PATH=/data/pg12.3/bin:/usr/bin:/usr/sbin:/bin:/sbin:$PATH

export PGHOME=/data/pg12.3

export PGDATA=$PGHOME/data

export PGPORT=5432

export PGUSER=postgres

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/lib/:$LD_LIBRARY_PATH

[postgres@mode2 data]$ pwd

/data/pg14.2/data

[postgres@mode2 data]$ ll

总用量 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2）初始化pg14.2数据目录

[postgres@mode2 data]$ /data/pg14.2/bin/initdb -D /data/pg14.2/data

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 "zh_CN.UTF-8".

The default database encoding has accordingly been set to "UTF8".

initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"

The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /data/pg14.2/data ... 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:

/data/pg14.2/bin/pg_ctl -D /data/pg14.2/data -l logfile start
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
3）进行升级兼容性测试

[postgres@mode2 data]$ /data/pg14.2/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.2/bin -d /data/pg12.3/data/ -D /data/pg14.2/data -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 user-defined encoding conversions ok

Checking for user-defined postfix operators 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*

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
4）硬链接升级使用-k参数

[postgres@mode2 data]$ /data/pg14.2/bin/pg_upgrade -b /data/pg12.3/bin -B /data/pg14.2/bin -d /data/pg12.3/data/ -D /data/pg14.2/data -k

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

Creating dump of global objects ok

Creating dump of database schemas

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

Adding ".old" suffix to old global/pg_control ok

If you want to start the old cluster, you will need to remove

the ".old" suffix from /data/pg12.3/data/global/pg_control.old.

Because "link" mode was used, the old cluster cannot be safely

started once the new cluster has been started.

Linking user relation files

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:

/data/pg14.2/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:

./delete_old_cluster.sh

[postgres@mode2 data]$

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
5）将pg12.3的相关配置文件移动到pg14.2的data目录中，启动数据库

[postgres@mode2 data]$ cp pg_hba.conf /data/pg14.2/data

[postgres@mode2 data]$ cp postgresql.conf /data/pg14.2/data/postgresql.base.conf

[postgres@mode2 data]$ vi /data/pg14.2/data/postgresql.conf

##在最后一行增加如下内容

include postgresql.base.conf
1
2
3
4
5
6
7
8
9
6）启动新版本pg14.2数据库

[postgres@mode2 pg12.3]$ /data/pg14.2/bin/pg_ctl start -D /data/pg14.2/data

waiting for server to start.....2022-12-07 10:25:41.359 CST [43925] LOG: starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

.2022-12-07 10:25:41.394 CST [43925] LOG: listening on IPv6 address "::1", port 5432

2022-12-07 10:25:41.394 CST [43925] LOG: listening on IPv4 address "127.0.0.1", port 5432

2022-12-07 10:25:41.395 CST [43925] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2022-12-07 10:25:41.427 CST [43927] LOG: database system was shut down at 2022-12-07 09:56:24 CST

2022-12-07 10:25:41.501 CST [43925] LOG: database system is ready to accept connections

done

server started
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
7）更新新版本数据库统计信息

[postgres@mode2 data]$ /data/pg14.2/bin/vacuumdb --all --analyze-in-stages