postgres小版本升级

本文为小版本升级案例演示

PostgreSQL每次的小版本升级不会改变内部的存储格式，不会改变数据目录， 并且总是向上兼容同一主版本， 例如9.6.2与9.6.1总是兼容的， 以此类推，9.6.3与9.6.2也是兼容的，无论他们之间跨越了几个小版本。 升级小版本也很简单， 只需安装新的可执行文件， 并重新启动数据库实例。

本次文档演示的版本为11.1升级到11.7

1、查看当前安装的服务器版本

postgres=# select version();                                                  version                                                 ---------------------------------------------------------------------------------------------------------  PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit (1 row)   postgres=#

2、查看旧版本安装路径

首先检查当前的安装位置，在原来的安装位置覆盖安装新服务器版本即可

[postgres@postgres bin]$ which psql /opt/pgsql11/bin/psql [postgres@postgres bin]$ cd /opt/pgsql11/ [postgres@postgres pgsql11]$ ls bin  include  lib  share [postgres@postgres pgsql11]$ pwd /opt/pgsql11 [postgres@postgres pgsql11]$

 

3、备份旧安装文件

tar -cvf /opt/pgsql11.tar /opt/pgsql11

解压时包含绝对路径

4、下载新的pg版本

https://ftp.postgresql.org/pub/source/ https://www.postgresql.org/ftp/source/

 

5、上传源文件到/opt/soft目录

root用户

mkdir -p /opt/soft cd /opt/soft/ tar -xvf postgresql-11.7.tar.gz

 

6、配置

root用户

cd /opt/soft/postgresql-11.7 ./configure --prefix=/opt/pgsql11 --with-pgport=5442 --with-perl --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=8 --with-blocksize=8 --enable-dtrace --enable-debug --enable-cassert

配置的参数文件可以参考11.1版本安装时的参数

[root@postgres postgresql-11.1]# [root@postgres postgresql-11.1]# pwd /opt/soft_bak/postgresql-11.1 [root@postgres postgresql-11.1]# ls -ltr config.log -rw-rw-r-- 1 postgres postgres 470854 Dec 19  2018 config.log [root@postgres postgresql-11.1]# more config.log This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake.   It was created by PostgreSQL configure 11.1, which was generated by GNU Autoconf 2.69.  Invocation command line was     $ ./configure --prefix=/opt/pgsql11 --with-pgport=5442 --with-perl --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=8 --with-blocksize=8 --enable-dtrace --enable-debug --enable-cassert   ## --------- ## ## Platform. ## ## --------- ##

或者参考

[root@postgres pgsql11]# pg_config BINDIR = /opt/pgsql9.3.4/bin DOCDIR = /opt/pgsql9.3.4/share/doc HTMLDIR = /opt/pgsql9.3.4/share/doc INCLUDEDIR = /opt/pgsql9.3.4/include PKGINCLUDEDIR = /opt/pgsql9.3.4/include INCLUDEDIR-SERVER = /opt/pgsql9.3.4/include/server LIBDIR = /opt/pgsql9.3.4/lib PKGLIBDIR = /opt/pgsql9.3.4/lib LOCALEDIR = /opt/pgsql9.3.4/share/locale MANDIR = /opt/pgsql9.3.4/share/man SHAREDIR = /opt/pgsql9.3.4/share SYSCONFDIR = /opt/pgsql9.3.4/etc PGXS = /opt/pgsql9.3.4/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/opt/pgsql9.3.4' '--with-pgport=5432' '--with-perl' '--with-tcl' '--with-python' '--with-openssl' '--with-pam' '--without-ldap' '--with-libxml' '--with-libxslt' '--enable-thread-safety' '--with-wal-blocksize=8' '--with-blocksize=8' '--enable-dtrace' '--enable-debug' '--enable-cassert' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g CFLAGS_SL = -fpic LDFLAGS = -L../../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pgsql9.3.4/lib',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 9.3.4 [root@postgres pgsql11]#

 

7、编译&安装

make make install

 

8、升级验证

重启数据库验证升级

安装完可执行文件后， 可以不必立即重启数据库服务器。在重启数据库服务之前， 升级并不会立即生效。 可以有计划地在数据库的维护窗口期间对数据库服务器进行重启。

[postgres@postgres ~]$ psql -h 127.0.0.1 -p 5442 psql (11.7, server 11.1) Type "help" for help.   postgres=#

 

在重启之前， 可以看到命令提示符处显示， psql客户端的版本已经升级到了11.7， 但server的版本还是11.1， 这里应当注意理解和区分命令行中psql客户端的版本和server端的版本。

 

[postgres@postgres ~]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2020-09-02 23:22:04.021 EDT [4444] LOG:  listening on IPv4 address "0.0.0.0", port 5442 2020-09-02 23:22:04.021 EDT [4444] LOG:  listening on IPv6 address "::", port 5442 2020-09-02 23:22:04.022 EDT [4444] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5442" 2020-09-02 23:22:04.036 EDT [4444] LOG:  redirecting log output to logging collector process 2020-09-02 23:22:04.036 EDT [4444] HINT:  Future log output will appear in directory "log".  done server started [postgres@postgres ~]$ psql -h 127.0.0.1 -p 5442 psql (11.7) Type "help" for help.   postgres=#

重启之后再检查服务器的版本， server端才会升级到11.7。