pg_upgrade 提供了升级前的兼容性检查（-c 或者 —check 选项）功能，可以发现插件、数据类型不兼容等问题。如果指定了—link 选项，新版本服务可以直接使用原有的数据库文件而不需要执行复制，通常可以在几分钟内完成升级操作。
本次文档主要记录的是版本13.5到14.1的升级演示
01/ PG 13环境检查
1、环境变量检查
[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 ~]$
2、数据库版本检查
[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=#
3、模拟数据
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=#
02/ PG 14环境安装
[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 ~]#
2、编译安装
[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]#
3、初始化数据库
[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 ~]$
4、修改目录权限
[root@lyp ~]# chown -R postgres:postgres /opt/pgsql14.1/
[root@lyp ~]#
03/ 备份数据库
利用 pg_dump/pg_dumpall 对数据库进行备份，保证升级中遇到各种问题后以便回退。
[postgres@lyp ~]$ /opt/pgsql13.5/bin/pg_dumpall > pgdumpallfull
04/ 升级数据库
1、升级前检查
[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 ~]$
2、删除不兼容的插件
[postgres@lyp ~]$ /opt/pgsql13.5/bin/psql
psql (13.5)
Type "help" for help.
postgres=# drop extension walminer ;
DROP EXTENSION
[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 ~]$
[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 ~]$
这里我们不使用—link直接升级。
[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 ~]$
05/ 更新hba及参数
将 PG 13环境配置文件 pg_hba.conf 和 postgresql.conf 等中的改动在对应的PG 14配置文件中再次进行修改。
1、修改环境变量
[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 ~]$
06/ 启动数据库
[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 ~]$
07/ 更新统计信息
pg_upgrade 不会生成新版本数据库的统计信息，按提示执行命令：
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 ~]$
08/ 删除原集群数据文件（按需）
确认升级成功后，可以选择删除或者保留旧的数据文件。pg_upgrade 提供了一个删除旧数据文件的脚本
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 ~]$
09/ 验证升级
1、数据库版本检查
[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=#
2、模拟数据检查
postgres=# select count(*) from emp;
count
---------
1100000
(1 row)
10/ 回退方式
若需回退到旧版本的数据库，可以分为以下三种情况：
1、如果只运行了 —check 选项命令，表示没有真正执行升级，重新启动服务即可；
点击此处阅读原文
↓↓↓
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
