1.字典表修改

• 新增字典

• information_schema增加字典

• 字典增加列

*1 表示多个字典表都取消了隐藏属性

• 字典删除列

• 字典修改列

1.1.pg_stat_progress_cluster

pg_stat_progress_cluster 字典可以跟踪 CLUSTER 语句或 VACUUM FULL 语句的执行状态。

1.2.pg_stat_progress_create_index

pg_stat_progress_create_index 字典可以跟踪 CREATE INDEX、REINDEX 语句的执行进度。

postgres=> SELECT * FROM pg_stat_progress_create_index ; 
-[ RECORD 1 ]------+--------------------------------------- 
pid         | 13233 
datid       | 16385        
datname     | postgres        
relid       | 16386        
index_relid | 0       
phase       | building index: loading tuples in tree        
lockers_total  | 0     
lockers_done   | 0     
current_locker_pid | 0 
blocks_total       | 0 
blocks_done        | 0 
tuples_total       | 10000000 
tuples_done        | 6207353 
partitions_total   | 0 
partitions_done    | 0 

1.3.pg_stat_replication

reply_time 列已添加到 pg_stat_replication 字典中。此列存储从备用实例收到的最后一条回复消息的发送时间。此列只能由 SUPERUSER 属性或 pg_monitor 角色的持有者显示。

ostgres=# SELECT * FROM pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ 
pid              | 12497 
usesysid         | 10 
usename          | postgres 
application_name | walreceiver 
client_addr      | 
client_hostname  | 
client_port      | -1 
backend_start    | 2025-01-08 20:13:15.551032-04 
backend_xmin     | 
state            | streaming 
sent_lsn         | 0/3000060 
write_lsn        | 0/3000060 
flush_lsn        | 0/3000060 
replay_lsn       | 0/3000060 
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0 
sync_state       | async 
reply_time       | 2025-01-08 20:15:45.68363-04 

1.4.pg_indexes

pg_indexes 字典现在包含分区索引。具体来说包含 pg_class 字典中 relkind 列值为 ‘I’ 的索引 。

1.5.pg_stat_database

当检测到数据块校验和错误时，pg_stat_database 目录中的 checksum_failures 列和checksum_last_failure 列会更新。checksum_failures 列的值在每次检测到校验和错误时都会更新，因此它不表示实际损坏的块数。

postgres=# SELECT * FROM data1 ; 
psql: WARNING:  page verification failed, calculated checksum 27311 
but expected 12320 
ERROR:  invalid page in block 0 of relation base/13567/16384 
postgres=# 
postgres=# SELECT datname, checksum_failures , checksum_last_failure FROM pg_stat_database WHERE datname='postgres' ; 
-[ RECORD 1 ]-----+------------------------------ 
datname           | postgres 
checksum_failures | 1 
checksum_last_failure | 2025-01-08 20:06:23.747741-04

在 PostgreSQL 12 中，添加了 datid 列为 0 的元组。这是独立于数据库的全局对象信息。

1.6.pg_stat_gssapi

pg_stat_gssapi视图显示有关此连接上的 GSSAPI 使用情况的信息。它可以联接到 pg_stat_activity 或 pid 列上的 pg_stat_replication 以获取有关连接的更多详细信息。

1.7.pg_statistic_ext_data

此目录包含在 pg_statistic_ext 中定义的扩展计划器统计的数据。

2.废除 recovery.conf 文件

用于在流式复制环境中构建备用实例以及用于从备份中恢复的 recovery.conf 文件已过时。recovery.conf 文件中的各种设置已集成到 postgresql.conf 文件中。更改了一些参数。

如果进行恢复，请在数据库集群中创建 recovery.signal 文件并启动实例。恢复过程完成后，此文件将被删除。在流复制环境中，在从数据库中创建 standby.signal 文件。此文件在提升为主文件时被删除。如果数据目录中存在 recovery.conf 文件，则实例启动将失败。

$ ls data/recovery.conf 
data/recovery.conf 
$ pg_ctl -D data start 
waiting for server to start....2024-10-03 21:55:53.749 EDT [18397] 
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 
2024-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv6 address 
"::1", port 5432 
2024-10-03 21:55:53.750 EDT [18397] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432 
2024-10-03 21:55:53.755 EDT [18397] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432" 
2024-10-03 21:55:53.771 EDT [18398] LOG:  database system was shut 
down at 2019-10-03 21:55:09 EDT 
2024-10-03 21:55:53.771 EDT [18398] FATAL:  using recovery command 
file "recovery.conf" is not supported 
2024-10-03 21:55:53.772 EDT [18397] LOG:  startup process (PID 
18398) exited with exit code 1 
2024-10-03 21:55:53.772 EDT [18397] LOG:  aborting startup due to 
startup process failure 
2024-10-03 21:55:53.773 EDT [18397] LOG:  database system is shut 
down 
 stopped waiting 
pg_ctl: could not start server 
Examine the log output.

3.实例启动日志

版本号现在在实例启动时输出到日志中。如果参数 logging_collector 指定为 “on”，则将其输出到日志文件中。

$ pg_ctl -D data start 
waiting for server to start....
2024-10-03 21:36:22.445 EDT [17730] 
LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 
2024-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv6 address 
"::1", port 5432 
2024-10-03 21:36:22.447 EDT [17730] LOG:  listening on IPv4 address 
"127.0.0.1", … 

4.最大连接数

可以使用 max_wal_senders 参数获取复制的连接数，而不依赖于 max_connections 参数。max_wal_senders 的信息已添加到 pg_controldata 命令的输出中。

$ pg_controldata -D data | grep max_wal_senders 
max_wal_senders setting:              
10 

5.并行查询增强功能

即使会话的事务隔离级别为 SERIALIZABLE，并行查询现在也可以工作。以下示例使用 auto_explain 模块输出 SERIALIZABLE 隔离级别的事务中执行的 SQL 语句的执行计划。

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# show auto_explain.log_min_duration;
 auto_explain.log_min_duration 
-------------------------------
 0
(1 row)

postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# SELECT COUNT(*) FROM test;
  count  
---------
 1000000
(1 row)

postgres=# commit;
COMMIT

PostgreSQL 11

2025-01-16 08:33:20.866 UTC [659] LOG:  duration: 56.324 ms  plan:
        Query Text: SELECT COUNT(*) FROM test;
        Aggregate  (cost=17906..17906 rows=1 width=8)
          ->  Seq Scan on ftest  (cost=0.00..8237.64 rows=1000000 width=0)

PostgreSQL 12

2025-01-16 08:27:32.955 UTC [82] LOG:  duration: 44.266 ms  plan:
        Query Text: SELECT COUNT(*) FROM test;
        Finalize Aggregate  (cost=12578.55..12578.56 rows=1 width=8)
          ->  Gather  (cost=12578.33..12578.54 rows=2 width=8)
                Workers Planned: 2
                ->  Partial Aggregate  (cost=11578.33..11578.34 rows=1 width=8)
                      ->  Parallel Seq Scan on test  (cost=0.00..10536.67 rows=416667 width=0)

6.Jsonb 类型和 GIN 索引

“jsonb @@ jsonpath” 和 “jsonb @? jsonpath“运算符已添加到为 jsonb 类型创建的 GIN 索引中。GIN index 的运算符类可以使用 jsonb_ops 或 json_path_ops。以下是手册中描述的 SELECT 语句的执行计划。

select opc.opcname,amop.amopopr::regoperator, amop.amopstrategy
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where 
opc.opcname ~'jsonb'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'gin'
and amop.amoplefttype = opc.opcintype;
    opcname     |      amopopr       | amopstrategy 
----------------+--------------------+--------------
 jsonb_ops      | ?(jsonb,text)      |            9
 jsonb_ops      | ?|(jsonb,text[])   |           10
 jsonb_ops      | ?&(jsonb,text[])   |           11
 jsonb_ops      | @>(jsonb,jsonb)    |            7
 jsonb_ops      | @?(jsonb,jsonpath) |           15   --12版本新增
 jsonb_ops      | @@(jsonb,jsonpath) |           16   --12版本新增
 jsonb_path_ops | @>(jsonb,jsonb)    |            7
 jsonb_path_ops | @?(jsonb,jsonpath) |           15   --12版本新增
 jsonb_path_ops | @@(jsonb,jsonpath) |           16   --12版本新增

$.tags[*] 是一个 JSON 路径表达式，表示选择 JSON 对象中 tags 数组的所有元素。
@@ 是 JSON 路径查询操作符，用于判断 JSON 数据是否符合指定的 JSON 路径条件。
查询条件 $.tags[*] == “qui” 会检查 tags 数组中的每个元素是否为 “qui”，如果数组中有 “qui”，则该行数据会被返回。

postgres=# CREATE TABLE api (
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     jdoc JSONB
postgres(# );

-- 创建 GIN 索引
CREATE INDEX idxgin ON api USING GIN (jdoc);
CREATE TABLE
postgres=# 
postgres=# -- 创建 GIN 索引
postgres=# CREATE INDEX idxgin ON api USING GIN (jdoc);
CREATE INDEX
postgres=# 
postgres=# 
postgres=# INSERT INTO api (jdoc) VALUES
postgres-# ('{"guid": "1", "name": "Item 1", "tags": ["qui", "foo"]}'),
postgres-# ('{"guid": "2", "name": "Item 2", "tags": ["bar", "qui"]}'),
postgres-# ('{"guid": "3", "name": "Item 3", "tags": ["baz"]}'),
postgres-# ('{"guid": "4", "name": "Item 4", "tags": ["qui", "quux"]}'),
postgres-# ('{"guid": "5", "name": "Item 5", "tags": ["quux", "corge"]}');
INSERT 0 5
postgres=# 
postgres=# select jsonb_path_exists(jdoc,'$.tags[0]') from api limit 1;
 jsonb_path_exists 
-------------------
 t
(1 row)

postgres=# select jsonb_path_query(jdoc,'$.tags[0]') from api limit 1;
 jsonb_path_query 
------------------
 "qui"
(1 row)

postgres=# EXPLAIN (COSTS OFF) 
postgres-# SELECT jdoc->'guid', jdoc->'name' 
postgres-# FROM api 
postgres-# WHERE jdoc @@ '$.tags[*] == "qui"';
                            QUERY PLAN                            
------------------------------------------------------------------
 Bitmap Heap Scan on api
   Recheck Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
   ->  Bitmap Index Scan on idxgin
         Index Cond: (jdoc @@ '($."tags"[*] == "qui")'::jsonpath)
(4 rows)

postgres=# SELECT jdoc->'guid', jdoc->'name' 
FROM api                          
WHERE jdoc @@ '$.tags[*] == "qui"';
 ?column? | ?column? 
----------+----------
 "1"      | "Item 1"
 "2"      | "Item 2"
 "4"      | "Item 4"
(3 rows)

7.等待事件

对 pg_stat_activity 目录的 wait_event 列的 wait 事件输出进行了以下更改。

8.ECPG

ECPG 中添加了以下功能。

• Bytea 数据类型

bytea 类型的变量在 DECLARE SECTION 中定义，可用于数据输入/输出。

{ 
    EXEC SQL BEGIN DECLARE SECTION ; 
        bytea data[1024] ; 
        short data_ind = 0 ; 
    EXEC SQL END DECLARE SECTION ; 
 
    memset(data.arr, 0, sizeof(data.arr)) ; 
    data.len = 0 ; 
 
    EXEC SQL SELECT col1 INTO :data FROM data1 ; 
 
    for (i = 0; i < data.len; i++)  
    { 
        printf("data[%d] = %c\n", i, data.arr[i]) ; 
    } 
} 

• PREPARE AS语句

现在可以通过 PREPARE AS 语句直接编写 SQL 语句。

{ 
EXEC SQL BEGIN DECLARE SECTION ; 
int ivar1 = 100; 
int ivar2 = 200; 
EXEC SQL END DECLARE SECTION ; 
EXEC SQL PREPARE pre_name(INT, INT) AS INSERT INTO prepare1 
VALUES ($1, $2); 
EXEC SQL EXECUTE pre_name(:ivar1, :ivar2); 
}

9.可插拔存储引擎

使用多个存储引擎的基本规格已经确定。该表的访问方法在 CREATE ACCESS METHOD 语句中指定 TYPE TABLE 子句。
语法:

CREATE ACCESS METHOD name
    TYPE access_method_type
    HANDLER handler_function

默认存储引擎由 default_table_access_method 参数指定。此参数的默认值为 “heap”。作为此修改的结果，它已被添加到 pg_am 目录中。

postgres=# SELECT amname, amhandler, amtype FROM pg_am ;
 amname |      amhandler       | amtype 
--------+----------------------+--------
 heap   | heap_tableam_handler | t	--12版本新增
 btree  | bthandler            | i	
 hash   | hashhandler          | i
 gist   | gisthandler          | i
 gin    | ginhandler           | i
 spgist | spghandler           | i
 brin   | brinhandler          | i
(7 rows)

• 创建表时指定 engine

要为表指定存储引擎，请在 CREATE TABLE 语句中指定 USING 子句。也可以使用 CREATE TABLE AS SELECT 语句或 CREATE MATERIALIZED VIEW 语句指定它。

postgres=# CREATE TABLE t1(c1 NUMERIC, c2 VARCHAR(10)) USING heap ;
CREATE TABLE
postgres=#  CREATE TABLE t2 USING heap AS SELECT * FROM t1 ;
SELECT 0
postgres=# CREATE MATERIALIZED VIEW mview1 USING heap as SELECT COUNT(*) cnt FROM t1;
SELECT 1

• 通过 psql 命令显示表定义

执行 \d+ table_name 命令时，输出表的存储引擎名称。

postgres=# \d+ t1
                                            Table "public.t1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Access method: heap

为 psql 变量 HIDE_TABLEAM 指定 on（默认值：‘off’）可以禁止显示 Access Method 项的输出。

postgres=#  \set HIDE_TABLEAM on 
postgres=# \d+ t1
                                            Table "public.t1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 

通过 psql 命令显示访问方法
表访问方法也使用 \dA 命令显示。以前，仅输出索引访问方法。

postgres=# \dA
List of access methods
  Name  | Type  
--------+-------
 brin   | Index
 btree  | Index
 gin    | Index
 gist   | Index
 hash   | Index
 heap   | Table
 spgist | Index
(7 rows)

10.pg_hba.conf

以下新功能已添加到 pg_hba.conf 文件中。

• clientcert 项的新参数

可以为 clientcert 参数指定新设置 “verfy-full”。此选项还确保证书的 “cn” （通用名称） 与用户名或正确的映射匹配。

• GSSAPI 身份验证加密选项

带加密选项的通用安全标准应用程序编程接口 （GSSAPI） 可用于客户端身份验证。可以在 pg_hba.conf 文件中指定 hostgssenc / hostnogssenc 的条目。为了启用 GSSAPI 身份验证，必须在安装时指定 --with-gssapi 作为“configure”命令的选项。

11.文本搜索

文本搜索支持的语言增加。在 PostgreSQL 11 中为 16 种语言，但在 PostgreSQL 12 中已增加到 22 种语言。

postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description              
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language
 pg_catalog | irish      | configuration for irish language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language
 pg_catalog | turkish    | configuration for turkish language
(22 rows)

12.libpq

以下函数已添加到 PostgreSQL 的 C 语言接口中。

• PQresultMemorySize

添加了“size_t PQresultMemorySize （const PGresult * res）” API。此函数返回 PGresult 分配的内存量。它可用于管理应用程序内存。

• GetForeignDataWrapperExtended

添加了“ForeignDataWrapper * GetForeignDataWrapperExtended （Oid fwdid， bits16 flags）” API。

• GetForeignServerExtended

添加了“ForeignServer * GetForeignServerExtended（Oid fwdid，bits16 flags）”API。

13.事物id

64 位事务 ID 可用 。提供 API “GetTopFullTransactionIdIfAny”、“GetTopFullTransactionId”、“GetCurrentFullTransactionId”、“GetCurrentFullTransactionIdIfAny”。但是，当前堆不使用它们。

14.客户端环境变量

添加了环境变量 PG_COLOR 和 PG_COLORS。指定是否使用 PG_COLOR 中诊断消息的颜色。可能的值为 always、auto 或 never。PG_COLORS 是通过将转义序列和代码的类别与等号 （=） 组合来指定的。指定多个类别时，请用冒号 (: ).

export PG_COLOR=always
export PG_COLORS='error=01;31:warning=01;35:locus=01'

15.客户端连接字符串

tcp_user_timeout 指示已将 TCP 超时添加到客户端连接字符串中。

参考

《PostgreSQL_12_GA_New_Features_en_20191011-1》
《快速掌握PostgreSQL版本新特性》