方案概述
使用pg_upgrade进行PostgreSQL大版本的升级
实施步骤
一、创建测试数据
1.1.创建测试用户
postgres=# create database test;
CREATE DATABASE
postgres=# CREATE USER cg WITH PASSWORD '123456';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE test TO cg;
GRANT
postgres=# grant all privileges on tablespace pg_default to cg;
GRANT1.2.创建表空间
[root@pg12 ~]# mkdir /pg_tablespace
[root@pg12 ~]# chown postgres:postgres /pg_tablespace/
[root@pg12 ~]# su - postgres
Last login: Sun Aug 13 12:35:36 CST 2023 on pts/1
[postgres@pg12 ~]$ psql
psql (12.11)
Type "help" for help.
postgres=# create tablespace tbs_test owner cg location '/pg_tablespace';
CREATE TABLESPACE
postgres=# alter database test set tablespace tbs_test;
ALTER DATABASE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 23 MB |
pg_global | postgres | | | | 623 kB |
tbs_test | cg | /pg_tablespace | | | 7961 kB |
(3 rows)1.3.创建测试数据
postgres=# \c test cg
You are now connected to database "test" as user "cg".
test=> CREATE TABLE a tablespace pg_default AS SELECT id AS a, id AS b, id AS c FROM generate_series(1, 10000000) AS id;
SELECT 10000000
test=> CREATE TABLE b AS SELECT * FROM a;
SELECT 10000000
test=> SELECT pg_size_pretty(pg_database_size('test'));
pg_size_pretty
----------------
852 MB
(1 row)
test=> select pg_relation_filepath('a');
pg_relation_filepath
----------------------
base/16384/16387
(1 row)
test=> select pg_relation_filepath('b');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16386/PG_12_201909212/16384/16390
(1 row)二、安装并初始化pg13数据库
2.1 创建目录
[root@pg12 ~]# mkdir -p /data1/pgdata13
[root@pg12 ~]# chown postgres:postgres -R /data1/2.2 编译安装
[postgres@pg12 ~]$ pg_config | egrep 'CONFIGURE|CC'
CONFIGURE = '--prefix=/usr/local/pgsql12.11' '--with-perl' '--with-python'
CC = gcc -std=gnu99
[root@pg12 local]# tar -xzvf postgresql-13.12.tar.gz
[root@pg12 local]# ln -s postgresql-13.12 pgsql13
[root@pg12 pgsql13]# ./configure --prefix=/usr/local/pgsql13 --with-perl --with-python
[root@pg12 pgsql13]# make -j 8
[root@pg12 pgsql13]# make instal-world -j 82.3 初始化及环境变量配置
[postgres@pg12 ~]$ cat env_13pg
export PATH=/usr/local/pgsql13/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql13/lib:$LD_LIBRARY_PATH
export PGDATA=/data1/pgdata13
export PGHOST=/tmp
[postgres@pg12 ~]$ source env_13pg
[postgres@pg12 ~]$ initdb -D /data1/pgdata132.4 升级前的兼容性检查
[postgres@pg12 ~]$ pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql13/bin -d /data/pgdata/ -D /data1/pgdata13/ -c
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions
This utility can only upgrade to PostgreSQL version 12.
Failure, exiting
[postgres@pg12 ~]$ source env_13pg
[postgres@pg12 ~]$ pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql13/bin -d /data/pgdata/ -D /data1/pgdata13/ -c
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 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*注：可以看到需要使用新版本的pg_upgrade工具，即：需要升级到什么版本就使用那个版本的pg_upgrade工具
2.5 升级到pg13
2.5.1 使用pg_upgrade直接升级
[postgres@pg12 ~]$ time pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql13/bin -d /data/pgdata/ -D /data1/pgdata13/
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
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 analyze new cluster ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
real 0m4.967s
user 0m0.090s
sys 0m2.893s2.5.2使用pg_upgrade加–link参数升级
[postgres@pg12 pg_tablespace]$ time pg_upgrade -b /usr/local/pgsql/bin -B /usr/local/pgsql13/bin -d /data/pgdata/ -D /data1/pgdata13/ --link
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
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
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 /data/pgdata/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 analyze new cluster ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
real 0m2.038s
user 0m0.080s
sys 0m0.287s可以看到是否使用link参数时间有所差别，这里我的数据不多，所以差距不大，随着数据量的递增，这个差距将会变得非常巨大（link可以在秒级完成，不是用link是直接拷贝）
注1：upgrade过程可用使用–verbose选项来观察升级的详细操作
注2：analyze_new_cluster.sh脚本将在pg14中移除，取而代之的是命令行：/usr/local/pgsql14/bin/vacuumdb --all --analyze-in-stages
注3：我们观察这个升级的过程，可以看到：
If you want to start the old cluster, you will need to remove
the “.old” suffix from /data/pgdata/global/pg_control.old.
Because “link” mode was used, the old cluster cannot be safely
started once the new cluster has been started.
通过查阅官方文档，使用pg_upgrade的回退方法如下：
翻译如下：
如果只运行了–check选项命令，表示没有真正执行升级，重新启动服务即可；
如果升级时没有使用–link选项，旧版本的数据库集群没有任何修改，重新启动服务即可；
如果升级时使用了–link选项，数据库文件可能已经被新版本的集群使用：
如果pg_upgrade在链接操作之前终止，旧版本的数据库集群没有任何修改，重新启动服务即可；
如果没有启动过新版本的后台服务，旧版本的数据库集群没有修改，但是链接过程已经将$PGDATA/global/pg_control文件重命名为$PGDATA/global/pg_control.old；此时需要将该文件名中的.old后缀去掉，然后重新启动服务即可；
如果已经启动了新版本的数据库集群，已经修改了数据库文件，再启动旧版本的服务可能导致数据损坏；此时需要通过备份文件还原旧版本的数据库。
2.6 启动数据库
[postgres@pg12 ~]$ pg_ctl start -D $PGDATA
waiting for server to start....2023-08-13 20:43:42.927 CST [53369] LOG: starting PostgreSQL 13.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2023-08-13 20:43:42.927 CST [53369] LOG: listening on IPv6 address "::1", port 5432
2023-08-13 20:43:42.927 CST [53369] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-13 20:43:42.928 CST [53369] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-13 20:43:42.931 CST [53370] LOG: database system was shut down at 2023-08-13 20:35:21 CST
2023-08-13 20:43:42.933 CST [53369] LOG: database system is ready to accept connections
done
server started
[postgres@pg12 ~]$ ps -ef |grep postg
root 1210 1169 0 Aug11 pts/0 00:00:00 su - postgres
postgres 1211 1210 0 Aug11 pts/0 00:00:00 -bash
root 53315 37140 0 20:43 pts/1 00:00:00 su - postgres
postgres 53316 53315 0 20:43 pts/1 00:00:00 -bash
postgres 53369 1 0 20:43 ? 00:00:00 /usr/local/postgresql-13.12/bin/postgres -D /data1/pgdata13
postgres 53371 53369 0 20:43 ? 00:00:00 postgres: checkpointer
postgres 53372 53369 0 20:43 ? 00:00:00 postgres: background writer
postgres 53373 53369 0 20:43 ? 00:00:00 postgres: walwriter
postgres 53374 53369 0 20:43 ? 00:00:00 postgres: autovacuum launcher
postgres 53375 53369 0 20:43 ? 00:00:00 postgres: stats collector
postgres 53376 53369 0 20:43 ? 00:00:00 postgres: logical replication launcher
postgres 53385 53316 0 20:43 pts/1 00:00:00 ps -ef
postgres 53386 53316 0 20:43 pts/1 00:00:00 grep --color=auto postg可以看到数据库已正常启动
2.7 检查
不使用link选项：
[postgres@pg12 ~]$ ll /data1/pgdata13/base/16403/16387
-rw------- 1 postgres postgres 442818560 Aug 13 20:35 /data1/pgdata13/base/16403/16387
[postgres@pg12 ~]$ ll /data1/pgdata13/pg_tblspc/16400/PG_1
PG_12_201909212/ PG_13_202007201/
[postgres@pg12 ~]$ ll /data1/pgdata13/pg_tblspc/16400/PG_13_202007201/16403/16390
-rw------- 1 postgres postgres 442818560 Aug 13 20:35 /data1/pgdata13/pg_tblspc/16400/PG_13_202007201/16403/16390
使用link选项：
[postgres@pg12 pgdata13]$ ll /data1/pgdata13/base/16403/16387
-rw------- 2 postgres postgres 442818560 Aug 13 20:34 /data1/pgdata13/base/16403/16387
[postgres@pg12 pgdata13]$ ll /data1/pgdata13/pg_tblspc/16400/PG_13_202007201/16403/16390
-rw------- 2 postgres postgres 442818560 Aug 13 20:34 /data1/pgdata13/pg_tblspc/16400/PG_13_202007201/16403/16390可以看到我们在一开始创建的测试表空间，表a和表b都自动做了迁移。
如果是使用link选项，可以看到每个“数据”文件条目的第二列是2（不用使用link选项是1），2代表着这个文件是一个硬链接
2.8 执行统计信息收集脚本
[postgres@pg12 pgdata13]$ ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
"/usr/local/pgsql13/bin/vacuumdb" --all --analyze-only
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "test": 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 "test": 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 "test": Generating default (full) optimizer statistics
Done至此数据库升级完成。
三、过程问题处理
3.1 问题1
Checking cluster versions
This utility can only upgrade to PostgreSQL version 13.
Failure, exiting问题原因：
1.环境变量配置不对
2.初始化时使用了错误的环境变量
解决方法：
1.检查确认环境变量是否正确（指向新版本）
2.重新初始化（有可能初始化时候在旧的环境变量下进行）
3.2 问题2
new cluster tablespace directory already exists: "/pg_tablespace/PG_13_202007201"
Failure, exiting问题原因：
升级过程因各种原因中断或未完整清理升级过程遗留文件
解决方法：
数据库带自定义表空间如果升级过程失败，低版本数据目录下会存在高版本的表空间目录，如果升级出错需要手工清理每个表空间下的如下目录
删除命令参考命令如下
rm -rf */PG_13_202007201
参考文档
https://www.cybertec-postgresql.com/en/upgrading-and-updating-postgresql/https://www.postgresql.org/docs/current/pgupgrade.html