PostgreSQL升级-逻辑备份与还原

PostgreSQL升级-逻辑备份与还原

说明

​ 传统的跨版本升级方法就是利用 pg_dump/pg_dumpall 逻辑备份导出数据库，然后在新版本中通过 pg_restore 进行还原。导出旧版本数据库时推荐使用新版本的 pg_dump/pg_dumpall 工具，可以利用最新的并行导出和还原功能，同时可以减少数据库膨胀问题。

​ 逻辑备份与还原非常简单但速度比较慢，停机时间取决于数据库的大小，因此适合中小型数据库的升级。

​ 本次文档主要记录的是版本13.5到14.1的升级演示

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

new安装

解压安装包

[root@lyp ~]# tar -zxvf postgresql-14.1.tar.gz 
[root@lyp ~]# ll postgresql-14.1
total 756
-rw-r--r--.  1 1107 1107    490 Nov  9 05:58 aclocal.m4
drwxrwxrwx.  2 1107 1107   4096 Nov  9 06:10 config
-rwxr-xr-x.  1 1107 1107 580807 Nov  9 05:58 configure
-rw-r--r--.  1 1107 1107  83288 Nov  9 05:58 configure.ac
drwxrwxrwx. 58 1107 1107   4096 Nov  9 06:10 contrib
-rw-r--r--.  1 1107 1107   1192 Nov  9 05:58 COPYRIGHT
drwxrwxrwx.  3 1107 1107     87 Nov  9 06:10 doc
-rw-r--r--.  1 1107 1107   4259 Nov  9 05:58 GNUmakefile.in
-rw-r--r--.  1 1107 1107    277 Nov  9 05:58 HISTORY
-rw-r--r--.  1 1107 1107  63953 Nov  9 06:11 INSTALL
-rw-r--r--.  1 1107 1107   1665 Nov  9 05:58 Makefile
-rw-r--r--.  1 1107 1107   1213 Nov  9 05:58 README
drwxrwxrwx. 16 1107 1107   4096 Nov  9 06:11 src
[root@lyp ~]# 
[root@lyp ~]# mv postgresql-14.1 /opt/pgsql14.1
[root@lyp ~]# 

编译安装

[root@lyp ~]# cd /opt/pgsql14.1/
[root@lyp pgsql14.1]# ./configure --prefix=/opt/pgsql14.1
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 pgsql14.1]# 
[root@lyp pgsql14.1]# gmake world
gmake -C ./src/backend generated-headers
gmake[1]: Entering directory `/opt/pgsql14.1/src/backend'
......
gmake[2]: Leaving directory `/opt/pgsql14.1/contrib/vacuumlo'
gmake[1]: Leaving directory `/opt/pgsql14.1/contrib'
[root@lyp pgsql14.1]# 
[root@lyp pgsql14.1]# gmake install-world
gmake -C ./src/backend generated-headers
gmake[1]: Entering directory `/opt/pgsql14.1/src/backend'
......
gmake[2]: Leaving directory `/opt/pgsql14.1/contrib/vacuumlo'
gmake[1]: Leaving directory `/opt/pgsql14.1/contrib'
[root@lyp pgsql14.1]# 

初始化数据库

[postgres@lyp ~]$ mkdir pgdata-14
[postgres@lyp ~]$ /opt/pgsql14.1/bin/initdb -D /home/postgres/pgdata-14/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/pgdata-14 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /opt/pgsql14.1/bin/pg_ctl -D /home/postgres/pgdata-14/ -l logfile start

[postgres@lyp ~]$ 

修改目录权限

[root@lyp ~]# chown -R postgres:postgres /opt/pgsql14.1/
[root@lyp ~]# 

升级数据库

备份数据库

执行逻辑备份之前停止应用程序，确保没有数据更新，因为备份开始后的更新不会被导出。如有必要，可以修改 /usr/local/pgsql/data/pg_hba.conf 文件禁止其他人访问数据库。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_dumpall > pgdumpallfull
[postgres@lyp ~]$ 

由于已经安装了新版本的 PostgreSQL，可以使用新版本的 pg_dumpall 命令备份旧版本数据库。

停止数据库

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_ctl stop -D /home/postgres/pgdata
waiting for server to shut down....2022-02-08 01:01:14.395 CST [25886] LOG:  received fast shutdown request
2022-02-08 01:01:14.396 CST [25886] LOG:  aborting any active transactions
2022-02-08 01:01:14.396 CST [25886] LOG:  background worker "logical replication launcher" (PID 25893) exited with exit code 1
2022-02-08 01:01:14.396 CST [25908] FATAL:  terminating connection due to administrator command
2022-02-08 01:01:14.397 CST [25888] LOG:  shutting down
2022-02-08 01:01:14.442 CST [25886] LOG:  database system is shut down
 done
server stopped
[postgres@lyp ~]$ 

如果安装目录没有包含特定版本标识（如本文中的13.5/14.1），可以使用mv将目录改名，必要时可以再修改回来。

修改环境变量

[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/pgsql14.1
export PGDATA=/home/postgres/pgdata-14
export PGUSER=postgres
export PGPORT=5433
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
[postgres@lyp ~]$ source .bash_profile 
[postgres@lyp ~]$

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

启动数据库

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_ctl -D /home/postgres/pgdata-14/ -l logfile start
waiting for server to start.... done
server started
[postgres@lyp ~]$ 

还原数据

[postgres@lyp ~]$ psql -d postgres -f pgdumpallfull 
SET
SET
SET
CREATE ROLE
ALTER ROLE
psql:pgdumpallfull:16: ERROR:  role "postgres" already exists
ALTER ROLE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 1100000
COPY 0
[postgres@lyp ~]$ 

​ 为了减少停机时间，可以将新版本的 PostgreSQL 安装到另一个目录（例如 /usr/local/pgsql-13），同时使用不同的端口启动服务。然后同时执行数据库的导出和导入：

pg_dumpall -p 5433 | psql -d postgres -p 5434

执行以上操作时，新旧版本的后台服务同时运行，新版本使用 5434 端口，旧版本使用 5433 端口。

验证升级

数据库版本查询

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

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 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=#