postgresql升级-m6米乐安卓版下载

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 米乐app官网下载 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=#