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