PostgreSQL-大版本升级（pg_upgrade方式）

1.pg_upgrade工具介绍

pg_upgrade 会创建新的系统表，并以重用旧的数据文件的方式进行升级。

pg_upgrade 的参数选项如下： 

 -b bindir，--old-bindir=bindir：旧的 PostgreSQL 可执行文件目录；

 -B bindir，--new-bindir=bindir：新的 PostgreSQL 可执行文件目录；

 -c，--check：只检查升级兼容性，不更改任何数据

 -d configdir，--old-datadir=configdir：旧版本的数据目录

 -D configdir，--new-datadir=configdir：新版本的数据目录

 -j，--jobs=njobs：要同时使用的进程或线程数

 -k，--link：硬链接方式升级

 -o options，--old-options options：直接传送给旧 postgres 命令的选项，多个选

项可以追加在后面

 -O options，--new-options options：直接传送给新 postgres 命令的选项，多个

选项可以追加在后面

 -p port，--old-port=port：旧版本的端口号

 -P port，--new-port=port：新版本的端口号

 -r，--retain：即使在成功完成后也保留 SQL 和日志文件

在升级之前应该运行 pg_upgrade -c 检查新旧版本的兼容性，把每一项不兼容的问题都

解决了才可以顺利升级。使用 pg_upgrade -c 只会检查新旧版本的兼容性，不会运行真

正的升级程序，不会修改数据文件，并且在命令结束时，会输出一份检查结果的报告，还

会对需要手动调整的项做出简要的描述。

pg_upgrade 有普通模式和 link 模式两种升级模式。在普通模式下，会把旧版本的数据拷

贝到新版本中，所以如果使用普通升级模式，要确保有足够的磁盘空间存储新旧两份数据；

link 模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬链接，可以有效减少

磁盘占用的空间。

2.版本信息

升级前	升级后
postgresql14.11	postgresql16.2

3.升级准备

安装依赖：

yum install libicu-devel.x86_64 python3 python3-devel

备份数据：

pg_dumpall -p 5432 > /tmp/backup.sql

4.安装新版pg数据库

wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.bz2 
tar -xvf postgresql-16.2.tar.bz2

#编译
cd postgresql-16.2
[root@localhost postgresql-16.2]# mkdir -p /usr/local/pgsql16.2

[root@localhost postgresql-16.2]# make && make install

#生成软连接
[root@localhost local]# unlink pgsql
[root@localhost local]# ln -s /usr/local/pgsql16.2 /usr/local/pgsql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root  6 Apr 11  2018 bin
drwxr-xr-x. 2 root root  6 Apr 11  2018 etc
drwxr-xr-x. 2 root root  6 Apr 11  2018 games
drwxr-xr-x. 2 root root  6 Apr 11  2018 include
drwxr-xr-x. 2 root root  6 Apr 11  2018 lib
drwxr-xr-x. 2 root root  6 Apr 11  2018 lib64
drwxr-xr-x. 2 root root  6 Apr 11  2018 libexec
drwxr-xr-x  6 root root 56 May  2 09:51 pgsq14.11
lrwxrwxrwx  1 root root 20 May  2 10:24 pgsql -> /usr/local/pgsql16.2
drwxr-xr-x  2 root root  6 May  2 09:46 pgsql14.11
drwxr-xr-x  6 root root 56 May  2 10:22 pgsql16.2
drwxr-xr-x. 2 root root  6 Apr 11  2018 sbin
drwxr-xr-x. 5 root root 49 Oct  5  2018 share
drwxr-xr-x. 2 root root  6 Apr 11  2018 src
[root@localhost local]#
#切换postgres用户并建新版pg数据目录
[root@localhost local]# su postgres
[postgres@localhost local]$ mkdir  /home/postgres/pgdata16
[postgres@localhost local]$
#初始化新版本pg
[postgres@localhost ~]$ initdb --locale C -D /home/postgres/pgdata16/ -W
Success. You can now start the database server using:

    pg_ctl -D /home/postgres/pgdata16/ -l logfile start
#修改配置文件，端口改为5433
[postgres@localhost pgdata16]$ vi postgresql.conf
port = 5433     

5.停止旧版本pg数据库服务

[postgres@localhost pgdata16]$ pg_ctl stop -D /home/postgres/pgdata14/ -p 5432
waiting for server to shut down.... done
server stopped

6.检查新旧版本兼容性

[postgres@localhost pgdata16]$ pg_upgrade -b /usr/local/pgsq14.11/bin -B /usr/local/pgsql16.2/bin -d /home/postgres/pgdata14/ -D /home/postgres/pgdata16/ -k -c
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

old cluster uses data checksums but the new one does not
Failure, exiting

#解决报错，新版本启用checksums
[postgres@localhost pgdata16]$ pg_checksums -D /home/postgres/pgdata16/ -e -P
22/22 MB (100%) computed
Checksum operation completed
Files scanned:   948
Blocks scanned:  2881
Files written:  780
Blocks written: 2881
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
[postgres@localhost pgdata16]$ pg_checksums -D /home/postgres/pgdata16/
Checksum operation completed
Files scanned:   948
Blocks scanned:  2881
Bad checksums:  0
Data checksum version: 1
[postgres@localhost pgdata16]$
#再次检查兼容性
[postgres@localhost pgdata16]$ pg_upgrade -b /usr/local/pgsq14.11/bin -B /usr/local/pgsql16.2/bin -d /home/postgres/pgdata14/ -D /home/postgres/pgdata16/ -k -c
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 incompatible "aclitem" data type in user tables  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@localhost pgdata16]$

7.升级

注：两种方式，普通模式和硬链接模式，可视磁盘空间剩余大小情况而定

#普通模式升级
pg_upgrade -b /usr/local/pgsq14.11/bin -B /usr/local/pgsql16.2/bin -d /home/postgres/pgdata14/ -D /home/postgres/pgdata16/

#link模式/硬链接模式
[postgres@localhost pgdata16]$ pg_upgrade -b /usr/local/pgsq14.11/bin -B /usr/local/pgsql16.2/bin -d /home/postgres/pgdata14/ -D /home/postgres/pgdata16/ -k
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 incompatible "aclitem" data type in user tables  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
------------------
Setting locale and encoding for new cluster                   ok
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
Adding ".old" suffix to old global/pg_control                 ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /home/postgres/pgdata14/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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:
    /usr/local/pgsql16.2/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
[postgres@localhost pgdata16]$

注：如果运行 pg_upgrade 失败，必须重新初始化新版本的数据目录，看到 upgrade

complete 说明升级已经顺利完成。

8.更新统计信息

pg_upgrade 会创建新的系统表，并重用旧的数据进行升级，统计信息并不会随升级过程

迁移，所以在启用新版本之前，应该首先重新收集统计信息，避免没有统计信息导致错误

的查询计划。我们可以手动运行 vacuum 命令，如下：

vacuumdb --all --analyze-in-stages -h 127.0.0.1 -p xxx

#启动新版本pg服务
[postgres@localhost pgdata16]$  pg_ctl -D /home/postgres/pgdata16/ -l logfile start
waiting for server to start.... done
server started

#更新统计信息
[postgres@localhost pgdata16]$ vacuumdb --all --analyze-in-stages -h 127.0.0.1 -p 5433
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "testdb": 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 "testdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "testdb": Generating default (full) optimizer statistics
[postgres@localhost pgdata16]$

9.验证数据

#升级前
[postgres@localhost ~]$ psql
psql (14.11)
Type "help" for help.

postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# create table t_trgm (id int,trgm text,no_trgm text);
CREATE TABLE
testdb=# insert into t_trgm select t,md5(t::text),md5(t::text) from generate_series(1,1000000) as t;
INSERT 0 1000000
testdb=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | t_trgm | table | postgres
(1 row)

testdb=# select count(*) from t_trgm;
  count
---------
 1000000
(1 row)

testdb=# \d t_trgm
               Table "public.t_trgm"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 id      | integer |           |          |
 trgm    | text    |           |          |
 no_trgm | text    |           |          |



#升级后

[postgres@localhost pgdata16]$ psql -p 5433
psql (16.2)
Type "help" for help.

postgres=# \l
                                                  List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C     |            |           |
 template0 | postgres | UTF8     | libc            | C       | C     |            |           | =c/postgres          +
           |          |          |                 |         |       |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |            |           | postgres=CTc/postgres+
           |          |          |                 |         |       |            |           | =c/postgres
 testdb    | postgres | UTF8     | libc            | C       | C     |            |           |
(4 rows)

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | t_trgm | table | postgres
(1 row)

testdb=# \d t_trgm;
               Table "public.t_trgm"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 id      | integer |           |          |
 trgm    | text    |           |          |
 no_trgm | text    |           |          |

testdb=# select count(*) from t_trgm;
  count
---------
 1000000
(1 row)

testdb=#

10.处理旧数据更改环境信息

#确认新版本运行正常，酌情移除旧版本的数据目录即可。 
--删除旧版本数据 
./delete_old_cluster.sh 

--修改环境变量 
vi .bashrc 
export PGDATA=/home/postgres/pgdata16/ 
source .bashrc

 11.失败回滚方案

略