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等
注意：在安装新版本数据库时，要保证数据库块大小一致。
1、查看当前数据库版本和测试数据
[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.sql
4、关闭源库
pg_ctl stop -D data
5、使用新版数据库执行文件启动数据库
[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 started
6、登录数据库查看版本，验证数据
[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查看旧版本数据库安装时的配置参数。
如果使用源码编译安装，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
- 回退数据库
把老版本数据库目录中global下，pg_control.old改为pg_control。启动源数据库即可
- 启动数据库查看数据库版本及验证数据
[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
2.2 回退方式
若需回退到旧版本的数据库，可以分为以下三种情况：
1、如果只运行了 —check 选项命令，表示没有真正执行升级，重新启动服务即可；
2、如果升级时没有使用 —link 选项，旧版本的数据库集群没有任何修改，重新启动服务即可；
3、如果升级时使用了 —link 选项，数据库文件可能已经被新版本的集群使用：
a、如果 pg_upgrade 在链接操作之前终止，旧版本的数据库集群没有任何修改，重新启动服务即可；
b、如果没有启动过新版本的后台服务，旧版本的数据库集群没有修改，但是链接过程已经将 $PGDATA/global/pg_control 文件重命名为 $PGDATA/global/pg_control.old；此时需要将该文件名中的 .old 后缀去掉，然后重新启动服务即可；
c、如果已经启动了新版本的数据库集群，已经修改了数据库文件，再启动旧版本的服务可能导致数据损坏；此时需要通过备份文件还原旧版本的数据库。(所以做任何操作之前，需要备份数据库，以便回退)。所以在升级中，尽量避免使用link参数进行升级。