Postgresql跨版本升级-逻辑备份

一、相关信息

系统版本	数据库升级前版本	升级后版本
CentOS7	11.3	13.1

二、通过逻辑备份与还原进行升级

使用pg_dumpall导出数据库至指定文件，再在新版本库中执行该文件还原数据库

1.备份旧版本数据库

执行备份之前停止应用连接，确保没有数据更新，也可以修改 pg_hba.conf 文件禁止其他人访问数据库。

[postgres@Postgresql tmp]$ pg_dumpall > outputfile11.3
[postgres@Postgresql tmp]$ ll
total 2.2M
-rw-rw-r-- 1 postgres postgres 2.2M Jan  6 15:23 outputfile11.3

2.停止旧版本数据库后台服务（关闭数据库）

[postgres@Postgresql tmp]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

生产环境可通过其他方式停止后台服务

3.安装新版本 PostgreSQL 软件

上传13.1版本的安装包到安装目录/usr/local，指定安装目录为 /opt/pgsql13.1，端口号为5433

[root@Postgresql postgresql-13.1]# ./configure --prefix=/opt/pgsql13.1 --with-pgport=5433
[root@Postgresql postgresql-13.1]#gmake
[root@Postgresql postgresql-13.1]#gmake install

编译安装完成后，查看数据库版本

[root@Postgresql postgresql-13.1]# /opt/pgsql13.1/bin/postgres --version
postgres (PostgreSQL) 13.1

4.初始化数据库

数据目录为/db/pgdata13

[postgres@Postgresql postgresql-13.1]#/opt/pgsql13.1/bin/initdb -D /db/pgdata13

5.修改用户环境变量

# 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
# add PG env
export PGHOME=/opt/pgsql13.1
export PGDATA=/db/pgdata13
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
alias rm='rm  -i'
alias ll='ls -lh'
#alias pg_start='pg_ctl start -D $PGDATA'
#alias pg_stop='pg_ctl stop -D $PGDATA -m fast'

#psql -h 主机名 -p 端口号 -U 用户名 -W(强制口令提示) [-d]数据库名
#psql -h $GHOST -p $PGPORT -U $PGUSER -W -d $PGDATABASE
#PGHOST 设置数据库服务器名。 如果它以一个斜杠开头，那么它声明一个 Unix 域套接字而不是 TCP/IP 通讯； 其值就是该套接字文件存储的目录（在缺省安装中，这个目录会是 /tmp）
#export PGHOST=$PGDATA
export PGHOST=localhost
#PGPORT 设置 TCP 端口号或者设置与 PostgreSQL 通讯的 Unix 域套接字的文件扩展。
export PGPORT=5433
export PGUSER=postgres #用于与数据库连接的用户名，initdb -U posgtres指定
#export PGDATABASE=demo#数据库名

6.修改相关配置文件

将旧版本配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的新配置文件中再次进行修改。

7.启动数据库

[postgres@Postgresql db]$ /opt/pgsql13.1/bin/pg_ctl start -D /db/pgdata13 -l /tmp/logfile

8.还原数据

利用新版本的 psql 命令还原数据
还原前：

[postgres@Postgresql tmp]$ psql
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

还原：

[postgres@Postgresql tmp]$ psql -d postgres -f outputfile11.3

还原后：

[postgres@Postgresql tmp]$ psql
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 mydb      | pguser   | UTF8     | C          | C          | 
 osdbadb   | osdba    | UTF8     | C          | C          | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C          | C          | 
(6 rows)

升级完成