签到成功

知道了

CNDBA社区CNDBA社区

PostgreSQL 小版本升级（13.3 到 13.9 ） 操作手册

2022-12-17 12:08 2340 0 原创 PostgreSQL
作者： dave
本文链接：http://www.cndba.cn/dave/article/116399

在之前的博客我们介绍了PG的生命周期，如下：

PostgreSQL 学习笔记（2） — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372

从PG 10 开始，PG 每年会发布一个大版本（major version），每个季度会发布一个小版本（minor release）。PG 大版本不保持向后兼容性，所以升级需要使用pg_upgrade命令来导出导入数据。 小版本升级相对比较简单，直接替换bin文件即可。

本篇我们看一下13.3 升级到13.9 的操作过程。

1 安装 13.3 PG环境

在之前的博客我们了解的PG的安装，如下：

PostgreSQL 学习笔记（3） — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374

1.1 安装软件

直接yum 会安装最新版：

http://www.cndba.cn/dave/article/116399 

[dave@www.cndba.cn  yum.repos.d]# yum search postgresql13
postgresql13.x86_64 : PostgreSQL client programs and libraries
postgresql13-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql13-devel.x86_64 : PostgreSQL development header files and libraries
postgresql13-docs.x86_64 : Extra documentation for PostgreSQL
postgresql13-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql13-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql13-odbc.x86_64 : PostgreSQL ODBC driver
postgresql13-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql13-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql13-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql13-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql13-test.x86_64 : The test suite distributed with PostgreSQL

所以我们这里直接下载13.3 的RPM 包进行安装：http://www.cndba.cn/dave/article/116399

[dave@www.cndba.cn  software]# pwd
/data/postgresql/software
[dave@www.cndba.cn  software]# ll
total 7948
-rw-r--r-- 1 root root 1484272 May 13  2021 postgresql13-13.3-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root  622484 May 13  2021 postgresql13-contrib-13.3-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root  389080 May 13  2021 postgresql13-libs-13.3-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root 5639660 May 13  2021 postgresql13-server-13.3-1PGDG.rhel7.x86_64.rpm
[dave@www.cndba.cn  software]#

[dave@www.cndba.cn  software]# rpm -ivh postgresql13-libs-13.3-1PGDG.rhel7.x86_64.rpm
warning: postgresql13-libs-13.3-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql13-libs-13.3-1PGDG.rhel################################# [100%]
[dave@www.cndba.cn  software]# rpm -ivh postgresql13-13.3-1PGDG.rhel7.x86_64.rpm
warning: postgresql13-13.3-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql13-13.3-1PGDG.rhel7    ################################# [100%]
[dave@www.cndba.cn  software]# rpm -ivh postgresql13-server-13.3-1PGDG.rhel7.x86_64.rpm
warning: postgresql13-server-13.3-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql13-server-13.3-1PGDG.rh################################# [100%]
[dave@www.cndba.cn  software]# rpm -ivh postgresql13-contrib-13.3-1PGDG.rhel7.x86_64.rpm
warning: postgresql13-contrib-13.3-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql13-contrib-13.3-1PGDG.r################################# [100%]
[dave@www.cndba.cn  software]#

1.2 配置下环境变量

在/etc/profile 文件添加如下内容：http://www.cndba.cn/dave/article/116399http://www.cndba.cn/dave/article/116399

export PATH=/usr/pgsql-13/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-13/lib:$LD_LIBRARY_PATH
export PGDATA=/data/postgresql/data

postgres用户的~/.bash_profile 也需要检查一下。http://www.cndba.cn/dave/article/116399

1.3 初始化实例

-bash-4.2$ pg_ctl init -D /data/postgresql/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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

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

    /usr/pgsql-13/bin/pg_ctl -D /data/postgresql/data -l logfile start

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /data/postgresql/data -l logfile start
waiting for server to start.... done
server started
-bash-4.2$

-bash-4.2$ psql
psql (13.3)
Type "help" for help.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=#

1.4 创建一个测试库和表

postgres=# create database cndba;
CREATE DATABASE

cndba=# /l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cndba     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

cndba=#

postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# create table dave(id int,url varchar(40));
CREATE TABLE
cndba=# insert into dave values(1,'https://www.cndba.cn');
INSERT 0 1
cndba=# select * from dave;
 id |         url
----+----------------------
  1 | https://www.cndba.cn
(1 row)

2 升级到13.9 版本

生产环境操作之前建议做个备份：

PostgreSQL 学习笔记（13） — PG 备份 与 恢复
https://www.cndba.cn/dave/article/116386

2.1 下载13.9的介质

PG 有多种安装方法，我们之前是采用rpm 安装的，我们这里还继续使用13.9 rpm 来安装。

http://www.cndba.cn/dave/article/116399 

[dave@www.cndba.cn  13.9]# wget https://yum.postgresql.org/13/redhat/rhel-7-x86_64/postgresql13-server-13.9-1PGDG.rhel7.x86_64.rpm --no-check-certificate
[dave@www.cndba.cn  13.9]# wget https://yum.postgresql.org/13/redhat/rhel-7-x86_64/postgresql13-libs-13.9-1PGDG.rhel7.x86_64.rpm --no-check-certificate
[dave@www.cndba.cn  13.9]# wget https://yum.postgresql.org/13/redhat/rhel-7-x86_64/postgresql13-contrib-13.9-1PGDG.rhel7.x86_64.rpm --no-check-certificate
[dave@www.cndba.cn  13.9]# wget https://yum.postgresql.org/13/redhat/rhel-7-x86_64/postgresql13-13.9-1PGDG.rhel7.x86_64.rpm --no-check-certificate

[dave@www.cndba.cn  13.9]# pwd
/data/postgresql/software/13.9
[dave@www.cndba.cn  13.9]# ll
total 7940
-rw-r--r-- 1 root root 1483036 Nov 10 16:43 postgresql13-13.9-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root  628680 Nov 10 16:43 postgresql13-contrib-13.9-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root  393308 Nov 10 16:43 postgresql13-libs-13.9-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root 5614440 Nov 10 16:43 postgresql13-server-13.9-1PGDG.rhel7.x86_64.rpm
[dave@www.cndba.cn  13.9]#

2.2 从其他主机复制bin文件

其实这里最关键的就是得到13.9 的bin 目录，因为rpm 安装的目录是固定的。 这里我们可以将13.9 安装到其他环境上，然后把文件复制过来。 

[dave@www.cndba.cn  pgsql-13]# pwd
/usr/pgsql-13
[dave@www.cndba.cn  pgsql-13]# ll
total 16
drwxr-xr-x 2 root root 4096 Dec 17 11:12 bin
drwxr-xr-x 3 root root   23 Dec 17 11:12 doc
drwxr-xr-x 3 root root 4096 Dec 17 11:12 lib
drwxr-xr-x 8 root root 4096 Dec 17 11:12 share
[dave@www.cndba.cn  pgsql-13]#

停库：

-bash-4.2$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped
-bash-4.2$

备份原安装目录：

[dave@www.cndba.cn  usr]# pwd
/usr
[dave@www.cndba.cn  usr]# mv pgsql-13 pgsql-13-bak

复制新目录过来：

[root@cndba 13.9]# scp -r /usr/pgsql-13 192.168.74.202:/usr/pgsql-13

起库验证：http://www.cndba.cn/dave/article/116399

-bash-4.2$ pg_ctl start -D $PGDATA
waiting for server to start....2022-12-17 11:49:11.892 CST [121839] LOG:  redirecting log output to logging collector process
2022-12-17 11:49:11.892 CST [121839] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$ psql
psql (13.9)
Type "help" for help.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# select * from dave;
 id |         url
----+----------------------
  1 | https://www.cndba.cn
(1 row)

cndba=#

2.3 直接本机安装新版本

因为PG的数据库软件和实例目录是分开的，我们可以直接卸载掉之前的版本，然后安装13.9 的新版本。 http://www.cndba.cn/dave/article/116399

这里必须先卸载，不然直接安装新版本rpm 会报错。http://www.cndba.cn/dave/article/116399

停库：

-bash-4.2$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped
-bash-4.2$

卸载RPM:http://www.cndba.cn/dave/article/116399

[dave@www.cndba.cn  software]# rpm -qa|grep postgre
postgresql13-libs-13.3-1PGDG.rhel7.x86_64
postgresql13-server-13.3-1PGDG.rhel7.x86_64
postgresql13-contrib-13.3-1PGDG.rhel7.x86_64
postgresql13-13.3-1PGDG.rhel7.x86_64
[dave@www.cndba.cn  software]#
[dave@www.cndba.cn  software]# rpm -e postgresql13-contrib-13.3-1PGDG.rhel7.x86_64
[dave@www.cndba.cn  software]# rpm -e postgresql13-13.3-1PGDG.rhel7.x86_64
[dave@www.cndba.cn  software]# rpm -e postgresql13-server-13.3-1PGDG.rhel7.x86_64
error: package postgresql13-server-13.3-1PGDG.rhel7.x86_64 is not installed
[dave@www.cndba.cn  software]# rpm -e postgresql13-libs-13.3-1PGDG.rhel7.x86_64
[dave@www.cndba.cn  software]#

检查下之前的文件，如果还存在，MV 备份一下：

[dave@www.cndba.cn  usr]# mv pgsql-13 pgsql-13-bak

安装13.9 的RPM：

[dave@www.cndba.cn  13.9]# rpm -ivh postgresql13-libs-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-server-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-contrib-13.9-1PGDG.rhel7.x86_64.rpm
warning: postgresql13-libs-13.9-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql13-libs-13.9-1PGDG.rhel################################# [ 25%]
   2:postgresql13-13.9-1PGDG.rhel7    ################################# [ 50%]
   3:postgresql13-server-13.9-1PGDG.rh################################# [ 75%]
   4:postgresql13-contrib-13.9-1PGDG.r################################# [100%]
[dave@www.cndba.cn  13.9]#

起库验证：

-bash-4.2$ pg_ctl start -D /data/postgresql/data
waiting for server to start....2022-12-17 12:00:42.831 CST [127136] LOG:  redirecting log output to logging collector process
2022-12-17 12:00:42.831 CST [127136] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$ psql
psql (13.9)
Type "help" for help.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# select * from dave;
 id |         url
----+----------------------
  1 | https://www.cndba.cn
(1 row)

cndba=#

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2261
    原创
  • 3
    翻译
  • 578
    转载
  • 191
    评论
  • 访问：7971682次
  • 积分：4346
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ