Postgresql升级-小版本升级

Postgresql升级-小版本升级

说明

​ Postgresql是一个非常活跃的社区开源数据库，更新速度很快，每一次版本的更新都会积极的修复旧版本的BUG，性能上也会有不同幅度的提升。
​ PostgreSQL版本号由主要版本和次要版本组成。例如，PostgreSQL13.2中的13是主要版本，2是次要版本；PostgreSQL10.0之前的版本由3个数字组成，例如9.5.25，其中9.5是主要版本，25是次要版本。
​ PostgreSQL发布次要版本是不会改变内存的存储格式，因此总是和相同的主要版本兼容。例如13.2与13.5，以及与13.X，总是兼容的。
​ 对于这些兼容版本的升级非常简单，只需要关闭数据库服务，安装替换二进制的可执行文件，重新启动服务即可。
本次文档主要记录的是版本13.2到13.5的升级演示

备份数据库

可以进行数据库备份pg_dump备份

old环境

环境变量检查

[postgres@lyp ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

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

export PATH

export PGHOME=/opt/pgsql13.2
export PGDATA=/home/postgres/pgdata
export PGUSER=postgres
export PGPORT=5433
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
[postgres@lyp ~]$ 

数据库版本检查

[postgres@lyp ~]$ psql
psql (13.2)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 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=# \d
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | emp               | table | postgres
(1 rows)

postgres=# select count(*) from emp;
  count  
---------
 1100000
(1 row)

postgres=#

new安装

解压安装包

[root@lyp ~]$ tar -zxvf postgresql-13.5.tar.gz
[root@lyp ~]# ll postgresql-13.5
total 744
-rw-r--r--.  1 1107 1107    490 Nov  9 06:00 aclocal.m4
drwxrwxrwx.  2 1107 1107   4096 Nov  9 06:13 config
-rwxr-xr-x.  1 1107 1107 569031 Nov  9 06:00 configure
-rw-r--r--.  1 1107 1107  82710 Nov  9 06:00 configure.in
drwxrwxrwx. 57 1107 1107   4096 Nov  9 06:13 contrib
-rw-r--r--.  1 1107 1107   1192 Nov  9 06:00 COPYRIGHT
drwxrwxrwx.  3 1107 1107     87 Nov  9 06:13 doc
-rw-r--r--.  1 1107 1107   4259 Nov  9 06:00 GNUmakefile.in
-rw-r--r--.  1 1107 1107    277 Nov  9 06:00 HISTORY
-rw-r--r--.  1 1107 1107  63750 Nov  9 06:14 INSTALL
-rw-r--r--.  1 1107 1107   1665 Nov  9 06:00 Makefile
-rw-r--r--.  1 1107 1107   1213 Nov  9 06:00 README
drwxrwxrwx. 16 1107 1107   4096 Nov  9 06:14 src
[root@lyp ~]# 
[root@lyp ~]$ mv postgresql-13.5 /opt/pgsql13.5

编译安装

[root@lyp ~]# cd /opt/pgsql13.5/
[root@lyp pgsql13.5]# ./configure --prefix=/opt/pgsql13.5
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
......
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[root@lyp pgsql13.5]#
[root@lyp pgsql13.5]# gmake world
gmake -C ./src/backend generated-headers
gmake[1]: Entering directory `/opt/pgsql13.5/src/backend'
......
gmake[2]: Leaving directory `/opt/pgsql13.5/contrib/vacuumlo'
gmake[1]: Leaving directory `/opt/pgsql13.5/contrib'
[root@lyp pgsql13.5]# 
[root@lyp pgsql13.5]# gmake install-world
gmake -C ./src/backend generated-headers
gmake[1]: Entering directory `/opt/pgsql13.5/src/backend'
......
gmake[2]: Leaving directory `/opt/pgsql13.5/contrib/vacuumlo'
gmake[1]: Leaving directory `/opt/pgsql13.5/contrib'
[root@lyp pgsql13.5]# 

升级数据库

关闭数据库

[postgres@lyp ~]$ pg_ctl stop -D pgdata/
waiting for server to shut down.... done
server stopped
[postgres@lyp ~]$ 

修改环境变量

[root@lyp ~]$ vi /home/postgres/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

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

export PATH

export PGHOME=/opt/pgsql13.5
export PGDATA=/home/postgres/pgdata
export PGUSER=postgres
export PGPORT=5433
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

[root@lyp ~]$

修改数据库目录

[root@lyp ~]# chown -R postgres:postgres /opt/pgsql13.5/
[root@lyp ~]# 

启动数据库

[postgres@lyp ~]$ pg_ctl start -D pgdata/ 
waiting for server to start....2022-02-07 23:37:46.570 CST [25886] LOG:  starting PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-02-07 23:37:46.571 CST [25886] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2022-02-07 23:37:46.571 CST [25886] LOG:  listening on IPv6 address "::", port 5433
2022-02-07 23:37:46.572 CST [25886] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2022-02-07 23:37:46.575 CST [25887] LOG:  database system was shut down at 2022-02-07 23:36:57 CST
2022-02-07 23:37:46.577 CST [25886] LOG:  database system is ready to accept connections
 done
server started
[postgres@lyp ~]$ 

验证升级

数据库版本检查

[postgres@lyp ~]$ psql
psql (13.5)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.5 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=# \d
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | emp               | table | postgres
(1 rows)

postgres=# select count(*) from emp;
  count  
---------
 1100000
(1 row)

postgres=#