查询标识符(query identifier)
指定log_line_prefix之后，可以在csvlog中看到query_id。从用户角度来看，有两个好处：
1.所有的工具和扩展都使用相同的query_id,便于交叉使用query_id。在此之前，所有的工具和扩展都要使用自己的算法计算query_id。
2.所有的工具和扩展都使用内核计算出的query_id，不再自己单独计算，有益于提升性能。
通过参数 compute_query_id开启或关闭query_id的计算，默认值是auto。可以在参数文件中配置，也可以通过set命令配置。
pg_stat_activity
set compute_query_id=off;
SELECT datname, query, query_id FROM pg_stat_activity; datname | query | query_id ----------+-----------------------------------------------------------------------+---------- postgres | select datname, query, query_id from pg_stat_activity; | postgres | UPDATE pgbench_branches SET bbalance = bbalance + 2361 WHERE bid = 1; |
set compute_query_id=on;
SELECT datname, query, query_id FROM pg_stat_activity; datname | query | query_id ----------+-----------------------------------------------------------------------+--------------------- postgres | select datname, query, query_id from pg_stat_activity; | 846165942585941982 postgres | UPDATE pgbench_tellers SET tbalance = tbalance + 3001 WHERE tid = 44; | 3354982309855590749
log
在以前的版本中，内核没有这种提供query_id的机制。query_id在日志是非常有用的。为此，要配置参数log_line_prefix。“%Q”增加了query_id的功能。例如：
log_line_prefix = 'query_id = [%Q] -> '
query_id = [0] -> LOG: statement: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; query_id = [-6788509697256188685] -> ERROR: return type mismatch in function declared to return record query_id = [-6788509697256188685] -> DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. query_id = [-6788509697256188685] -> CONTEXT: SQL function "ptestx" query_id = [-6788509697256188685] -> STATEMENT: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
explain
如果开启了compute_query_id,explain就会显示query_id。
set compute_query_id=off;
EXPLAIN VERBOSE SELECT * FROM foo;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on public.foo (cost=0.00..15.01 rows=1001 width=4)
Output: a
(2 rows)
set compute_query_id=on;
EXPLAIN VERBOSE SELECT * FROM foo;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on public.foo (cost=0.00..15.01 rows=1001 width=4)
Output: a
Query Identifier: 3480779799680626233
(3 rows)
autovacuum和auto-analyze日志增强
版本14增强了auto-vacuum和auto-analyze日志功能。现在可以在日志中看到I/O计时功能。显示了读和写花费了多少时间。
automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 1 pages: 0 removed, 67 remain, 0 skipped due to pins, 0 skipped frozen tuples: 89 removed, 8873 remain, 0 are dead but not yet removable, oldest xmin: 210871 index scan needed: 2 pages from table (2.99% of total) had 341 dead item identifiers removed index "pg_depend_depender_index": pages: 39 in total, 0 newly deleted, 0 currently deleted, 0 reusable index "pg_depend_reference_index": pages: 41 in total, 0 newly deleted, 0 currently deleted, 0 reusable I/O timings: read: 44.254 ms, write: 0.531 ms avg read rate: 13.191 MB/s, avg write rate: 8.794 MB/s buffer usage: 167 hits, 126 misses, 84 dirtied WAL usage: 85 records, 15 full page images, 78064 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s
连接日志
如果开启了log_connections/log_disconnections选项，就会记录建立连接和断开连接到日志。因此，版本14现在也记录实际的用户名。如果使用了外部验证机制，并在pg_ident.conf定义了映射关系，就很难识别真正的用户名。在版本14之前，只能看到映射名。
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME pg vagrant postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer map=pg
在PostgreSQL-14之前
LOG: database system was shut down at 2021-11-19 11:24:30 UTC LOG: database system is ready to accept connections LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=postgres application_name=psql
在PostgreSQL-14中
LOG: database system is ready to accept connections LOG: connection received: host=[local] LOG: connection authenticated: identity="vagrant" method=peer (/usr/local/pgsql.14/bin/data/pg_hba.conf:89) LOG: connection authorized: user=postgres database=postgres application_name=psql