postgres小版本升级

tech2026-03-30 11

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

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。

最新回复(0)