PostgreSQL数据库版本升级 Postgresql是一个非常活跃的社区开源项目，更新速度很快，每一次版本的更新都会积极的修复旧版本的BUG,性能上也会有不同幅度的提升。10之前的版本由三部分组成，10开始只有两部分数字组成。 PostgreSQL版本发布规则，一年一个大版本，一个季度一个小版本；PG遇到的BUG问题，社区会很快进行修复，并在下一个版本中发布，因此有必要进行对数据库版本升级，避免触发已知的BUG带来业务系统的不稳定。 pg_upgrade 工具可以支持 PostgreSQL 跨版本的就地升级，不需要执行导出和导入操作。pg_upgrade 可以支持 PostgreSQL 8.4.X 到最新版本的升级，包括快照版本和测试版本。 pg_upgrade 提供了升级前的兼容性检查（-c 或者 --check 选项）功能， 可以发现插件、数据类型不兼容等问题。如果指定了 --link 选项，新版本服务可以直接使用原有的数据库文件而不需要执行复制，通常可以在几分钟内完成升级操作。
1、小版本升级
Postgresql每次的小版本升级不会改变内部的存储格式，也不会改变数据目录，并且总是向上兼容同一主版本，9.6.2与9.6.1总是兼容的。升级小版本只需要安装新的可执行文件，并且重启数据库实例。 升级步骤：- 安装最新版本数据库
- 停止数据库实例
- 对数据目录进行备份
- 使用新版本启动数据库
- 调整环境变量，PGHOME/LD_LIRARAY_PATH等
[postgres@node01 ~]$ psql
psql (14.7)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.7 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=# create table test(id int,name text);
CREATE TABLE
postgres=# insert into test values (1,'a');
INSERT 0 1
postgres=# insert into test values (2,'b');
INSERT 0 1
postgres=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
2、安装新版数据库14.8
[root@node01 ~]# tar -xf postgresql-14.8.tar.gz [root@node01 ~]# cd postgresql-14.8/ [root@node01 postgresql-14.8]# ./configure --prefix=/usr/local/pgsql14.8 [root@node01 postgresql-14.8]# gmake world [root@node01 postgresql-14.8]# gmake install-world
3、备份源库
pg_dumpall -Upostgres -f /home/postgres/dumpall_data.sql4、关闭源库
pg_ctl stop -D data5、使用新版数据库执行文件启动数据库
[postgres@node01 ~]$ /usr/local/pgsql14.8/bin/pg_ctl start -D data waiting for server to start....2023-08-07 11:00:14.528 CST [41285] LOG: starting PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2023-08-07 11:00:14.531 CST [41285] LOG: listening on IPv6 address "::1", port 5432 2023-08-07 11:00:14.531 CST [41285] LOG: listening on IPv4 address "127.0.0.1", port 5432 2023-08-07 11:00:14.533 CST [41285] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2023-08-07 11:00:14.537 CST [41286] LOG: database system was shut down at 2023-08-07 10:50:24 CST 2023-08-07 11:00:14.539 CST [41285] LOG: database system is ready to accept connections done server started6、登录数据库查看版本，验证数据
[postgres@node01 ~]$ /usr/local/pgsql14.8/bin/psql
psql (14.8)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.8 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=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
7、修改环境变量
export PATH=/usr/local/pgsql14.8/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql14.8/lib
2、大版本升级
大版本可以用pg_dumpall 和pg_upgrade进行升级。 pg_upgrade（以前称为pg_migrator）允许将存储在PostgreSQL数据文件中的数据升级到更高版本的PostgreSQL主版本，而不需要主要版本升级所需的数据转储/重载，例如从8.4.7升级到PostgreSQL的当前主要版本。次要版本升级不需要，例如从9.0.1到9.0.4。 主要的PostgreSQL版本会定期添加新功能，这些功能通常会改变系统表的布局，但内部数据存储格式很少会发生变化。pg_upgrade通过创建新的系统表并简单地重用旧的用户数据文件来使用此事实来执行快速升级。如果未来的主要版本以一种使旧数据格式不可读的方式更改数据存储格式，则pg_upgrade将无法用于此类升级。（社区将试图避免这种情况。） pg_upgrade尽力确保旧的和新的集群是二进制兼容的，例如通过检查兼容的编译时设置，包括32/64位二进制文件。重要的是，任何外部模块也是二进制兼容的，尽管pg_upgrade无法检查。 pg_upgrade支持从8.4.X及更高版本升级到PostgreSQL的当前主要版本。 有一些外部扩展要求在升级之前先升级旧版本的外部扩展，例如GIS。2.1、使用pg_upgrade升级
pg_upgrade是官方提供的版本升级工具，有普通模式和Link模式两种升级模式。在普通模式下，会把旧版本的数据拷贝到新版本中，需要确保有足够的磁盘空间存储新旧两份数据；Link模式下，只是在新版本的数据目录中建立了旧版本数据文件的硬连接，可以有效减少磁盘占用的空间。2.1.1 pg_upgrade常规升级
- 安装新版本数据库
- 新版数据库初始化目录
- 查看老版本数据库及数据
- 对老版本数据库进行备份
- 停止老版本数据库
- 检查新旧数据库版本兼容性
- 解决版本兼容性问题
- 升级数据库
- 调整环境变量
- 安装新版本数据库
如果使用源码编译安装，configure配置和原库一致。可以使用pg_config查看旧版本数据库安装时的配置参数。 [root@node01 ~]# tar -xf postgresql-15.3.tar.gz [root@node01 ~]# cd postgresql-15.3/ [root@node01 postgresql-15.3]# ./configure --prefix=/usr/local/pgsql15.3 [root@node01 postgresql-15.3]# gmake world [root@node01 postgresql-15.3]# gmake install-world
- 新版数据库初始化目录
只初始化数据库，不启动 [postgres@node01 ~]$ /usr/local/pgsql15.3/bin/initdb -D data-15
- 查看老版本数据库及数据
[postgres@node01 ~]$ psql
psql (14.7)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.7 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=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
- 对老版本数据库进行备份
[postgres@node01 ~]$ pg_dumpall -Upostgres -f /home/postgres/dumpall_data.sql
- 停止老版本数据库
pg_ctl stop -D data
- 检查新旧数据库版本兼容性
/usr/local/pgsql15.3/bin/pg_upgrade \ --old-datadir /home/postgres/data/ \ --new-datadir /home/postgres/data-15/ \ --old-bindir /usr/local/pgsql14.7/bin/ \ --new-bindir /usr/local/pgsql15.3/bin/ \ --check [postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_upgrade \ > --old-datadir /home/postgres/data/ \ > --new-datadir /home/postgres/data-15/ \ > --old-bindir /usr/local/pgsql14.7/bin/ \ > --new-bindir /usr/local/pgsql15.3/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 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@node01 ~]$
- 解决版本兼容性问题
- 升级数据库
/usr/local/pgsql15.3/bin/pg_upgrade \
--old-datadir /home/postgres/data/ \
--new-datadir /home/postgres/data-15/ \
--old-bindir /usr/local/pgsql14.7/bin/ \
--new-bindir /usr/local/pgsql15.3/bin/
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_upgrade \
> --old-datadir /home/postgres/data/ \
> --new-datadir /home/postgres/data-15/ \
> --old-bindir /usr/local/pgsql14.7/bin/ \
> --new-bindir /usr/local/pgsql15.3/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
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:
/usr/local/pgsql15.3/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
- 启动数据库查看数据库版本及验证数据
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_ctl start -D data-15
waiting for server to start....2023-08-07 14:04:08.458 CST [105923] LOG: starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2023-08-07 14:04:08.463 CST [105923] LOG: listening on IPv6 address "::1", port 5432
2023-08-07 14:04:08.463 CST [105923] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-07 14:04:08.464 CST [105923] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-07 14:04:08.467 CST [105926] LOG: database system was shut down at 2023-08-07 14:02:44 CST
2023-08-07 14:04:08.470 CST [105923] LOG: database system is ready to accept connections
done
server started
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/psql
psql (15.3)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 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=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
- 调整环境变量
export PATH=/usr/local/pgsql15.3/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql15.3/lib
- 收集统计信息
[postgres@node01 ~]$ /usr/local/pgsql15.3/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
2.1.2 pg_upgrade使用link升级
–link 表示将新版本的数据目录硬链接到旧版本的数据目录，而不会复制一份新的数据文件，可以快速进行升级，但回退较为麻烦。 实际步骤：- 安装新版本数据库
如果使用源码编译安装，configure配置和原库一致。可以使用pg_config查看旧版本数据库安装时的配置参数。 [root@node01 ~]# tar -xf postgresql-15.3.tar.gz [root@node01 ~]# cd postgresql-15.3/ [root@node01 postgresql-15.3]# ./configure --prefix=/usr/local/pgsql15.3 [root@node01 postgresql-15.3]# gmake world [root@node01 postgresql-15.3]# gmake install-world
- 新版数据库初始化目录
只初始化数据库，不启动 [postgres@node01 ~]$ /usr/local/pgsql15.3/bin/initdb -D data-15
- 查看老版本数据库及数据
[postgres@node01 ~]$ psql
psql (14.7)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.7 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=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
- 对老版本数据库进行备份
[postgres@node01 ~]$ pg_dumpall -Upostgres -f /home/postgres/dumpall_data.sql
- 停止老版本数据库
pg_ctl stop -D data
- 检查新旧数据库版本兼容性
/usr/local/pgsql15.3/bin/pg_upgrade \ --old-datadir /home/postgres/data/ \ --new-datadir /home/postgres/data-15/ \ --old-bindir /usr/local/pgsql14.7/bin/ \ --new-bindir /usr/local/pgsql15.3/bin/ \ --check [postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_upgrade \ > --old-datadir /home/postgres/data/ \ > --new-datadir /home/postgres/data-15/ \ > --old-bindir /usr/local/pgsql14.7/bin/ \ > --new-bindir /usr/local/pgsql15.3/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 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@node01 ~]$
- 解决版本兼容性问题
- link升级数据库
/usr/local/pgsql15.3/bin/pg_upgrade \
--old-datadir /home/postgres/data/ \
--new-datadir /home/postgres/data-15/ \
--old-bindir /usr/local/pgsql14.7/bin/ \
--new-bindir /usr/local/pgsql15.3/bin/ \
--link
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_upgrade \
> --old-datadir /home/postgres/data/ \
> --new-datadir /home/postgres/data-15/ \
> --old-bindir /usr/local/pgsql14.7/bin/ \
> --new-bindir /usr/local/pgsql15.3/bin/ \
> --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 /home/postgres/data/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/pgsql15.3/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
- 回退数据库
- 启动数据库查看数据库版本及验证数据
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/pg_ctl start -D data-15
waiting for server to start....2023-08-07 14:12:47.088 CST [106308] LOG: starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2023-08-07 14:12:47.090 CST [106308] LOG: listening on IPv6 address "::1", port 5432
2023-08-07 14:12:47.090 CST [106308] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-07 14:12:47.092 CST [106308] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-07 14:12:47.096 CST [106311] LOG: database system was shut down at 2023-08-07 14:10:19 CST
2023-08-07 14:12:47.111 CST [106308] LOG: database system is ready to accept connections
done
server started
[postgres@node01 ~]$ /usr/local/pgsql15.3/bin/psql
psql (15.3)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 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=# select * from test ;
id | name
----+------
1 | a
2 | b
(2 rows)
- 调整环境变量
export PATH=/usr/local/pgsql15.3/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql15.3/lib
- 收集统计信息
[postgres@node01 ~]$ /usr/local/pgsql15.3/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