Postgresql升級(jí)-小版本升級(jí)

說明

? Postgresql是一個(gè)非常活躍的社區(qū)開源數(shù)據(jù)庫，更新速度很快，每一次版本的更新都會(huì)積極的修復(fù)舊版本的BUG，性能上也會(huì)有不同幅度的提升。
? PostgreSQL版本號(hào)由主要版本和次要版本組成。例如，PostgreSQL13.2中的13是主要版本，2是次要版本；PostgreSQL10.0之前的版本由3個(gè)數(shù)字組成，例如9.5.25，其中9.5是主要版本，25是次要版本。
? PostgreSQL發(fā)布次要版本是不會(huì)改變內(nèi)存的存儲(chǔ)格式，因此總是和相同的主要版本兼容。例如13.2與13.5，以及與13.X，總是兼容的。
? 對(duì)于這些兼容版本的升級(jí)非常簡單，只需要關(guān)閉數(shù)據(jù)庫服務(wù)，安裝替換二進(jìn)制的可執(zhí)行文件，重新啟動(dòng)服務(wù)即可。
本次文檔主要記錄的是版本13.2到13.5的升級(jí)演示

備份數(shù)據(jù)庫

可以進(jìn)行數(shù)據(jù)庫備份pg_dump備份

old環(huán)境

環(huán)境變量檢查

[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 ~]$ 

數(shù)據(jù)庫版本檢查

[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=# 

模擬數(shù)據(jù)

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]# 

升級(jí)數(shù)據(jù)庫

關(guān)閉數(shù)據(jù)庫

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

修改環(huán)境變量

[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 ~]$

修改數(shù)據(jù)庫目錄

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

啟動(dòng)數(shù)據(jù)庫

[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 ~]$ 

驗(yàn)證升級(jí)

數(shù)據(jù)庫版本檢查

[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=# 

模擬數(shù)據(jù)檢查

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=#