PostgreSQL升級(jí)-pg_upgrade升級(jí)

說明

? pg_upgrade 工具可以支持 PostgreSQL 跨版本的就地升級(jí)，不需要執(zhí)行導(dǎo)出和導(dǎo)入操作。pg_upgrade 可以支持 PostgreSQL 8.4.X 到最新版本的升級(jí)，包括快照版本和測(cè)試版本。

? pg_upgrade 提供了升級(jí)前的兼容性檢查（-c 或者 --check 選項(xiàng)）功能， 可以發(fā)現(xiàn)插件、數(shù)據(jù)類型不兼容等問題。如果指定了 --link 選項(xiàng)，新版本服務(wù)可以直接使用原有的數(shù)據(jù)庫文件而不需要執(zhí)行復(fù)制，通常可以在幾分鐘內(nèi)完成升級(jí)操作。

? 本次文檔主要記錄的是版本13.5到14.1的升級(jí)演示

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

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

new環(huán)境安裝

解壓安裝包

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

初始化數(shù)據(jù)庫

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

修改目錄權(quán)限

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

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

利用 pg_dump/pg_dumpall 對(duì)數(shù)據(jù)庫進(jìn)行備份，保證升級(jí)中遇到各種問題后以便回退。

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_dumpall > pgdumpallfull
[postgres@lyp ~]$

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

升級(jí)檢查

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ \
>   --check
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting
[postgres@lyp ~]$ more loadable_libraries.txt
could not load library "$libdir/walminer": ERROR:  could not access file "$libdir/walminer": No such file or directory
In database: postgres
[postgres@lyp ~]$ 

如果舊版本中安裝了擴(kuò)展模塊，在新版本中也需要安裝相應(yīng)的共享對(duì)象文件或者 DLL 文件。但是不要執(zhí)行CREATE EXTENSION命令，因?yàn)闀?huì)從舊數(shù)據(jù)庫中進(jìn)行升級(jí)。另外，如果使用了任何自定義的全文搜索文件（字典、同義詞、詞庫、停用詞），也需要復(fù)制到新的數(shù)據(jù)庫集群目錄中。

但是這里的walminer插件MAJORVERSION當(dāng)前只支持‘10’,‘11’,‘12’,‘13’，所以需要?jiǎng)h除插件。

刪除插件

[postgres@lyp ~]$ /opt/pgsql13.5/bin/psql 
psql (13.5)
Type "help" for help.

postgres=# drop extension walminer ;
DROP EXTENSION
postgres=# 

重新升級(jí)檢查

–check 表示執(zhí)行升級(jí)檢查，而不會(huì)真的執(zhí)行升級(jí)操作。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ \
>   --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

*Clusters are compatible*
[postgres@lyp ~]$ 

停止數(shù)據(jù)庫

確定沒有客戶端訪問之后備份數(shù)據(jù)庫，然后停止舊版本的后臺(tái)服務(wù)。

[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_ctl stop -D /home/postgres/pgdata
waiting for server to shut down....2022-02-08 02:28:38.499 CST [47025] LOG:  received fast shutdown request
2022-02-08 02:28:38.499 CST [47025] LOG:  aborting any active transactions
2022-02-08 02:28:38.500 CST [47034] FATAL:  terminating connection due to administrator command
2022-02-08 02:28:38.500 CST [47033] FATAL:  terminating connection due to administrator command
2022-02-08 02:28:38.501 CST [47025] LOG:  background worker "logical replication launcher" (PID 47032) exited with exit code 1
2022-02-08 02:28:38.502 CST [47027] LOG:  shutting down
2022-02-08 02:28:38.509 CST [47025] LOG:  database system is shut down
 done
server stopped
[postgres@lyp ~]$ 

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

可以使用–link 表示將新版本的數(shù)據(jù)目錄硬鏈接到舊版本的數(shù)據(jù)目錄，而不會(huì)復(fù)制一份新的數(shù)據(jù)文件，可以快速進(jìn)行升級(jí)，但回退較為麻煩。

這里我們不使用–link直接升級(jí)。

[postgres@lyp ~]$ /opt/pgsql14.1/bin/pg_upgrade \
>   --old-datadir /home/postgres/pgdata/ \
>   --new-datadir /home/postgres/pgdata-14/ \
>   --old-bindir /opt/pgsql13.5/bin/ \
>   --new-bindir /opt/pgsql14.1/bin/ 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
[postgres@lyp ~]$ 

更新hba及參數(shù)

將舊版本配置文件 pg_hba.conf 和 postgresql.conf 等中的改動(dòng)在對(duì)應(yīng)的新配置文件中再次進(jìn)行修改。

修改環(huán)境變量

[postgres@lyp ~]$ vi .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 ~]$ 

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

[postgres@lyp ~]$ pg_ctl start
waiting for server to start....2022-02-08 04:07:35.375 CST [95334] LOG:  starting 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
2022-02-08 04:07:35.376 CST [95334] LOG:  listening on IPv6 address "::1", port 5433
2022-02-08 04:07:35.376 CST [95334] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2022-02-08 04:07:35.379 CST [95334] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2022-02-08 04:07:35.382 CST [95335] LOG:  database system was shut down at 2022-02-08 04:04:46 CST
2022-02-08 04:07:35.384 CST [95334] LOG:  database system is ready to accept connections
 done
server started
[postgres@lyp ~]$

更新統(tǒng)計(jì)信息

pg_upgrade 不會(huì)生成新版本數(shù)據(jù)庫的統(tǒng)計(jì)信息，按提示執(zhí)行命令：

Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages

[postgres@lyp ~]$ /opt/pgsql14.1/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
[postgres@lyp ~]$ 

刪除原集群數(shù)據(jù)文件（按需）

確認(rèn)升級(jí)成功后，可以選擇刪除或者保留舊的數(shù)據(jù)文件。pg_upgrade 提供了一個(gè)刪除舊數(shù)據(jù)文件的腳本

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
[postgres@lyp ~]$

[postgres@lyp ~]$ more delete_old_cluster.sh
#!/bin/sh

rm -rf '/home/postgres/pgdata'
[postgres@lyp ~]$ 

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

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

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

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

postgres=# select count(*) from emp;
  count  
---------
 1100000
(1 row)

postgres=# 

回退方式

若需回退到舊版本的數(shù)據(jù)庫，可以分為以下三種情況：

1. 如果只運(yùn)行了 --check 選項(xiàng)命令，表示沒有真正執(zhí)行升級(jí)，重新啟動(dòng)服務(wù)即可；

2. 如果升級(jí)時(shí)沒有使用 --link 選項(xiàng)，舊版本的數(shù)據(jù)庫集群沒有任何修改，重新啟動(dòng)服務(wù)即可；

3. 如果升級(jí)時(shí)使用了 --link 選項(xiàng)，數(shù)據(jù)庫文件可能已經(jīng)被新版本的集群使用：

• 如果 pg_upgrade 在鏈接操作之前終止，舊版本的數(shù)據(jù)庫集群沒有任何修改，重新啟動(dòng)服務(wù)即可；

• 如果沒有啟動(dòng)過新版本的后臺(tái)服務(wù)，舊版本的數(shù)據(jù)庫集群沒有修改，但是鏈接過程已經(jīng)將 $PGDATA/global/pg_control 文件重命名為 $PGDATA/global/pg_control.old；此時(shí)需要將該文件名中的 .old 后綴去掉，然后重新啟動(dòng)服務(wù)即可；

• 如果已經(jīng)啟動(dòng)了新版本的數(shù)據(jù)庫集群，已經(jīng)修改了數(shù)據(jù)庫文件，再啟動(dòng)舊版本的服務(wù)可能導(dǎo)致數(shù)據(jù)損壞；此時(shí)需要通過備份文件還原舊版本的數(shù)據(jù)庫。(所以做任何操作之前，需要備份數(shù)據(jù)庫，以便回退)

所以在升級(jí)中，盡量避免使用link參數(shù)進(jìn)行升級(jí)。