PostgreSQL 全球开发组于 2024-05-09 发布 PostgreSQL 16.3 版本，这是当前最新稳定版本，可以用于生产环境。
PG 16.3 版本作为 16 系列的更新，引入了一系列的改进和修复。
本文介绍 PG 16.3 的主要变更点，以及从 PG 12 升级到 16.3 的几点注意事项。
PG 16.3 变更点
PG 16.3 版本包含了若干改进和修复，其中包括：
1. 安全漏-洞修复
修复了系统视图 pg_stats_ext 和 pg_stats_ext_exprs 中的安全漏-洞(CVE-2024-4317)，该漏-洞可能允许经过身份验证的数据库用户查看他们没有足够权限查看的数据。
这些视图无法隐藏涉及访问用户无权读取的列的表达式的统计信息。 诸如 most_common_vals 之类的视图列可能会暴露与安全相关的数据。 这里潜在的交互并不完全清楚，因此为了安全起见，请使这些视图中的行仅对关联表的所有者可见。
此修复仅修复新 initdb 数据库集群中的行为。如果你希望在现有集群中应用此更改，则需要执行以下操作：
在 PostgreSQL 安装的共享目录中找到 SQL 脚本 fix-CVE-2024-4317.sql（通常位于 /usr/share/postgresql/ 等位置）。确保使用适合你的 PostgreSQL 主要版本的脚本。如果没有看到此文件，则说明你的版本不易受到攻-击（仅 v14–v16 受到影响），或者是次要版本太旧，无法进行修复。
在集群的每个数据库中，以超级用户身份运行 fix-CVE-2024-4317.sql 脚本。 在 psql 中，执行：
\i /usr/share/postgresql/fix-CVE-2024-4317.sql
不要忘记包含 template0 和 template1 数据库，否则该漏-洞仍将存在于你以后创建的数据库中。要修复 template0，你需要暂时允许其接受连接。
ALTER DATABASE template0 WITH ALLOW_CONNECTIONS true;
修复 template0 后，撤销允许连接。
ALTER DATABASE template0 WITH ALLOW_CONNECTIONS false;
题外话， 生产环境的PG版本什么时候需要升级？ 当有重要安全漏-洞时，建议及时升级修补。
2. 问题修复
修复并行清理(parallel vacuuming)的缓冲区使用报告。并行工作线程执行的缓冲区访问不会计入 VERBOSE 模式下报告的统计信息中。
修复 EXPLAIN 对位图堆扫描访问的堆页的计数；修复 MERGE 中子计划的 EXPLAIN 输出
修复了在 GEQO 下规划分区连接(partition-wise joins)时使用已释放数据的问题，这可能会导致崩溃或意外错误。
使用非常量参数，禁用 ntile() 和 count() 窗口函数的“运行条件”优化，以避免子查询可能出现的错误行为，通常会导致“在子计划目标列表中找不到 WindowFunc”之类的错误。
客户端：对 pg_dumpall, pg_basebackup, pg_checksums 和 pg_rewind 等客户端工具的改进，以及对 contrib 模块的更新。
更多内容请参阅发版说明：
https://www.postgresql.org/docs/release/16.3/
PG 12 即将 EOL
19年10月，PG 12 发布，时隔5年，PG 12即将于今年11月发布最后一个小版本，结束生命周期。
如果你还在生产环境使用 PG 12，建议升级到更新的版本，比如 PG 16.3， 15.7 或 14.12。
升级 PG 12 到 16
升级步骤
下面简要介绍如何从PG 12.18 升级到 16.3，大致步骤如下：
- 准备新版本安装包： 下载并安装PG 16.3。可以在同一服务器上安装新版本，也可以选择一台新的服务器。
- 准备升级：在升级前，确保备份所有重要数据。可以使用 pg_dumpall 或 pg_dump 工具进行备份。
- 升级数据库：使用 pg_upgrade 工具或逻辑备份与还原的方法进行升级。 pg_upgrade （以前称为pg_migrator）是官方推荐的快速升级方法，它通过创建新的系统表并使用旧的用户表的方式进行升级。
此外，升级版本时，还需注意：
- 阅读发版说明：逐一阅读两个版本之间发版说明，了解版本差异和升级过程中可能需要执行的特定步骤。
- 兼容性检查：在升级前，检查应用程序和数据库对象的兼容性，因为新版本可能会引入不兼容的更改。
- 测试环境验证：在生产环境升级之前，在测试环境中进行测试、验证。
- 监控系统资源：在升级过程中和升级后的初期，密切监控系统资源和性能。
- 考虑停机时间：计划升级时间，以最小化对业务的影响。
升级演示
下面模拟版本升级(备份步骤略)：
- 在数据库中创建测试表。
[postgres@pg16 bin]$ psql yandb
psql (12.18)
Type "help" for help.
yandb=# create table t (id int);
CREATE TABLE
yandb=# insert into t select 1;
INSERT 0 1
yandb=# table t;
id
----
1
(1 row)
yandb=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 12.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 row)
- 停库、安装新版本
[root@pg16 data]# dnf install postgresql16-server-16.3
Last metadata expiration check: 0:45:16 ago on Thu May 23 07:22:50 2024.
Dependencies resolved.
=====================================================================================================
Package Architecture Version Repository Size
=====================================================================================================
Installing:
postgresql16-server x86_64 16.3-1PGDG.rhel9 pgdg16 6.7 M
Installing dependencies:
lz4 x86_64 1.9.3-5.el9 baseos 58 k
postgresql16 x86_64 16.3-1PGDG.rhel9 pgdg16 1.7 M
postgresql16-libs x86_64 16.3-1PGDG.rhel9 pgdg16 333 k
Transaction Summary
=====================================================================================================
Install 4 Packages
Total download size: 8.9 M
Installed size: 40 M
...
Installed:
lz4-1.9.3-5.el9.x86_64 postgresql16-16.3-1PGDG.rhel9.x86_64
postgresql16-libs-16.3-1PGDG.rhel9.x86_64 postgresql16-server-16.3-1PGDG.rhel9.x86_64
Complete!
[root@pg16 data]#
- 使用新版本二进制文件初始化数据目录
[postgres@pg16 ~]$ /usr/pgsql-16/bin/initdb -D /data
...
Success. You can now start the database server using:
/usr/pgsql-16/bin/pg_ctl -D /data -l logfile start
- 检查新旧版本兼容性
[postgres@pg16 ~]$ /usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-12/bin/ -B /usr/pgsql-16/bin/ -d /data12/ -D /data -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 user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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@pg16 ~]$
检查通过，接下来进行升级。
- 使用 pg_upgrade 升级
[postgres@pg16 ~]$ /usr/pgsql-16/bin/pg_upgrade -b /usr/pgsql-12/bin/ -B /usr/pgsql-16/bin/ -d /data12/ -D /data -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
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions 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 /data12/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/pgsql-16/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
[postgres@pg16 ~]$
升级完成，但是像提示语所示，统计数据不会迁移，需要重新收集。
- 启动数据库、收集统计信息
[postgres@pg16 ~]$ /usr/pgsql-16/bin/vacuumdb --all --analyze-in-stages
...
vacuumdb: processing database "yandb": Generating minimal optimizer statistics (1 target)
...
vacuumdb: processing database "yandb": Generating medium optimizer statistics (10 targets)
...
vacuumdb: processing database "yandb": Generating default (full) optimizer statistics
- 连接数据库，查看版本信息和测试数据
[postgres@pg16 ~]$ psql yandb
psql (16.3)
Type "help" for help.
yandb=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
(1 row)
yandb=# table t;
id
----
1
(1 row)
yandb=# \q
到此，PG数据库升级顺利完成。
知识扩展
PG大版本变化说明引入诸多新特性和变更，其中参数变化也值得注意，从PG12.8到16.3，新增、移除若干参数，也有部分参数的默认值发生了变更。
举个栗子，下列参数在16.3和12.8的参数默认值发生了变化：
- PG 16.3
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
(1 row)
postgres=# SELECT name,
reset_val,
category,
short_desc
FROM pg_settings
WHERE name in (
'checkpoint_completion_target',
'log_autovacuum_min_duration',
'log_checkpoints',
'password_encryption',
'ssl_min_protocol_version',
'vacuum_cost_page_miss'
);
name | reset_val | category | short_desc
------------------------------+---------------+-------------------------------------------------+------------------------------------------------------------------------------------------
checkpoint_completion_target | 0.9 | Write-Ahead Log / Checkpoints | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
log_autovacuum_min_duration | 600000 | Reporting and Logging / What to Log | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Reporting and Logging / What to Log | Logs each checkpoint.
password_encryption | scram-sha-256 | Connections and Authentication / Authentication | Chooses the algorithm for encrypting passwords.
ssl_min_protocol_version | TLSv1.2 | Connections and Authentication / SSL | Sets the minimum SSL/TLS protocol version to use.
vacuum_cost_page_miss | 2 | Resource Usage / Cost-Based Vacuum Delay | Vacuum cost for a page not found in the buffer cache.
(6 rows)
- PG 12.18
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 12.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
(1 row)
postgres=# SELECT name,
reset_val
FROM pg_settings
WHERE name in (
'checkpoint_completion_target',
'log_autovacuum_min_duration',
'log_checkpoints',
'password_encryption',
'ssl_min_protocol_version',
'vacuum_cost_page_miss'
);
name | reset_val
------------------------------+-----------
checkpoint_completion_target | 0.5
log_autovacuum_min_duration | -1
log_checkpoints | off
password_encryption | md5
ssl_min_protocol_version | TLSv1
vacuum_cost_page_miss | 10
(6 rows)
好了，本期内容分享到这里，欢迎关注本专栏。
-- / END / --
- [从 PG 技术峰会南京站汲取的那些干货]
- [PG 扩展推荐：pgpasswd & PG 密碼長度]
- [PG Style! 盘点几个常用的 Postgres 环境变量]
如果这篇文章为你带来了灵感或启发，就请帮忙点『赞』or『在看』or『转发』吧，感谢！(๑˃̵ᴗ˂̵)