1.实用程序

PostgreSQL 11 新特性学习-参数、实用程序、扩展调整
介绍实用程序命令的主要增强功能。

1.1.configure

“--disable-strong-random”选项已从从源代码安装 PostgreSQL 时运行的“configure”命令中删除。

1.2.initdb

initdb 命令现在在确定数据库集群的时区时引用 /etc/localtime 文件。如果未指定环境变量 TZ，则引用此文件。

1.3.oid2name

已查看 oid2name 命令的选项，现在可以使用长名称选项。

1.4.pg_basebackup

指定 --write-recovery-conf 参数 （-R） 时，pg_basebackup 命令的行为已更改。standby.signal 文件将在备份目标文件夹中自动创建，primary_conninfo 参数将添加到 postgresql.auto.conf 文件中。仅当连接到 PostgreSQL 12 或更高版本的实例时，才会执行此行为。

$ pg_basebackup -D back -R
$ ls -l back
...
...
-rw------- 1 postgres postgres   451 Feb 25 03:18 postgresql.auto.conf
-rw------- 1 postgres postgres 26802 Feb 25 03:18 postgresql.conf
-rw------- 1 postgres postgres     0 Feb 25 03:18 standby.signal

$ cat back/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
logging_collector = 'on'
allow_system_table_mods = 'on'
primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

1.5.pg_checksums

用于检查校验和完整性的命令 pg_verify_checksums 已重命名为 pg_checksums。除了检查校验和的完整性外，现在还可以更改校验和的激活/停用。

Options:
  -c, --check              check data checksums (default)
  -d, --disable            disable data checksums
  -e, --enable             enable data checksums
...
...

指定 --check 选项以检查块的完整性。如果确认一致性错误，则命令以返回值 1 结束。

$ pg_checksums -D data --check 
Checksum operation completed 
Files scanned:  973 
Blocks scanned: 11187 
Bad checksums:  0 
Data checksum version: 1 
$ echo $? 
0 
$

指定 --enable 选项（–disable 选项可禁用）以启用数据库集群校验和。即使启用了校验和，也会执行一致性检查。

$ pg_checksums -D data --enable 
Checksum operation completed 
Files scanned:  973 
Blocks scanned: 11187 
pg_checksums: syncing data directory 
pg_checksums: updating control file 
Checksums enabled in cluster 
$ 

必须在实例成功关闭的情况下运行此命令。如果实例异常终止或无法执行，则数据库集群在实例启动期间。

$ pg_ctl -D data -m immediate stop 
waiting for server to shut down.... done server stopped  
$ pg_checksums -D data --check 
pg_checksums: cluster must be shut down 
$

指定 --progress 选项（-P 选项）以显示处理状态。

$ pg_checksums -D data --check --progress 
87/87 MB (100%) computed 
Checksum operation completed 
Files scanned:  973 
Blocks scanned: 11187 
Bad checksums:  0 
Data checksum version: 1 

1.6.pg_ctl

pg_ctl 命令中添加了以下功能。

• logrotate

pg_ctl 命令中新增用于日志轮换的参数 logrotate。过去，需要向 logger 进程发送 SIGHUP 信号。要禁止显示该消息，请同时指定 -s 参数。

$ pg_ctl -D data logrotate
server signaled to rotate log file

1.7.pg_dump

pg_dump 命令中添加了以下选项。

• --on-conflict-do-nothing

此选项自动将 ON CONFLICT DO NOTHING 子句分配给输出 INSERT 语句。必须使用 --inserts 选项或 --column-inserts 选项指定。

$ pg_dump -t data1 --inserts --on-conflict-do-nothing 
-- 
-- PostgreSQL database dump 
… 
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: demo 
-- 

INSERT INTO public.data1 VALUES (1, 'data1') ON CONFLICT DO NOTHING; 
INSERT INTO public.data1 VALUES (2, 'data1') ON CONFLICT DO NOTHING; 
… 

• --extra-float-digits

如果为此参数指定了整数值，则在使用 pg_dump 命令进行数据采集之前执行 “SET extra_float_digits = specified value” 语句。转储文件不包含 SET 语句。可以指定的值范围是 -15 到 3。如果指定了非数字值，则将其视为 0。

• --rows-per-insert

此选项与 --inserts 选项一起使用。可以在单个 INSERT 语句中插入多个元组。值的范围是 1 到 2,147,483,647。

$ pg_dump -t data1 --inserts --rows-per-insert=2 
-- 
-- PostgreSQL database dump 
… 
-- Data for Name: data1; Type: TABLE DATA; Schema: public; Owner: postgres 
-- 
INSERT INTO public.data1 VALUES 
(1, 'data1'), 
(2, 'data1'); 
INSERT INTO public.data1 VALUES 
(3, 'data1'), 
… 

1.8.pg_dumpall

pg_dumpall 命令中添加了以下选项。

• --extra-float-digits

如果为此参数指定了整数值，则在使用 pg_dumpall 命令进行数据采集之前执行 “SET extra_float_digits = specified value” 语句。转储文件不包含 SET 语句。可以指定的值范围是 -15 到 3。如果指定了非数字值，则将其视为 0。

pg_dumpall -f all2.sql --extra-float-digits=-1

• --exclude-database

在 PostgreSQL 12 中，添加了 --exclude-database 选项。此选项指定要从备份中排除的数据库。指定多个数据库时，请使用与 psql 命令相同的模式。也可以多次指定相同的选项。在以下示例中，db1 和 db2 被指定为排除数据库。

$ pg_dumpall --exclude-database='db1' --exclude-database='db2' -f alldump.sql

• --oids

此选项已被删除。

• 其他注释

在输出文件中添加了用户设置（ALTER USER SET 语句）和数据库设置的注释

-- User Configurations 
-- User Config {User_name} 
-- Databases 
-- Database {Database_name} dump

1.9.pg_rewind

pg_rewind 命令中添加了不执行存储同步系统调用的 --no-sync 选项。

Options:
...
...
  -N, --no-sync                  do not wait for changes to be written
...
...

1.10.pg_restore

将标准输出指定为数据输出目标时，请指定 “-f -”。

$ pg_dump -Fc db1|pg_restore -c -f -

1.11.pg_upgrade

pg_upgrade 命令中添加了以下选项。

• --socketdir

添加了 --socketdir 选项（或 -s 选项），用于指定用于创建本地套接字的目录。

Options:
...
  -s, --socketdir=DIR           socket directory to use (default current dir.)
...

• --clone

–clone 选项使用 “reflink” 功能执行快速克隆。此功能仅在某些操作系统和文件系统上可用。

Options:
...
  --clone                       clone instead of copying files to new cluster
...

1.12.psql

psql 命令中添加了以下功能。

• CSV format

psql 命令的输出格式可以更改为 CSV 格式。可以通过以下任一方式更改它。

指定 psql 命令参数 --csv
从 psql 命令中执行 “pset format csv” 命令

列分隔符的默认值是逗号 （，），但可以通过 “\pset csv_fieldsep” 命令进行更改。如果输出值包含分隔符，则该值用双引号 （“） 括起来。可以使用 “\pset tuples_only on” 命令抑制列标题。

postgres=# \pset format csv
Output format is csv.
postgres=#  \pset csv_fieldsep
Field separator for CSV is ",". 

postgres=# SELECT * FROM t;
id,n
1,a

• 显示分区表

在执行 \d 命令时，分区表会显式输出。

postgres=# \d+ part1
                                    Partitioned table "public.part1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Partition key: RANGE (c1)
Partitions: part1v1 FOR VALUES FROM ('0') TO ('100'),
            part1v2 FOR VALUES FROM ('100') TO ('200')

现在显示分区索引的表空间。

postgres=#  \d idx1_part1
       Partitioned index "public.idx1_part1"
 Column |         Type          | Key? | Definition 
--------+-----------------------+------+------------
 c2     | character varying(10) | yes  | c2
btree, for table "public.part1"
Tablespace: "ts1"

postgres=# \db
               List of tablespaces
    Name    |  Owner   |        Location         
------------+----------+-------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 ts1        | postgres | /var/lib/postgresql/ts1
(3 rows)

• 显示对象权限

分区表现在在对象权限列表中指定。

postgres=# \z part4  
                                   Access privileges
 Schema | Name  |       Type        | Access privileges | Column privileges | Policies 
--------+-------+-------------------+-------------------+-------------------+----------
 public | part4 | partitioned table |                   |                   | 
(1 row)

• 显示连接信息

现在，在执行 \conninfo 命令时，会输出 TCP/IP 地址。

$ psql -h 127.1   
psql (12.20 (Debian 12.20-1.pgdg120+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.1" (address "127.0.0.1") at port "5432".

• 具有 VERBOSITY 的 SQLSTATE

现在可以在 “\set VERBOSITY” 命令中指定 SQLSTATE。

postgres=> \set VERBOSITY sqlstate 
postgres=> SELECT * FROM not_exists ; 
psql: ERROR:  42P01

• 分区表的显示

新增仅显示分区表的 “\dP” 命令。

postgres=# \dP
               List of partitioned relations
 Schema |    Name    |  Owner   |       Type        | Table 
--------+------------+----------+-------------------+-------
 public | fkey1      | postgres | partitioned table | 
 public | part1      | postgres | partitioned table | 
 public | part2      | postgres | partitioned table | 
 public | part3      | postgres | partitioned table | 
 public | part4      | postgres | partitioned table | 
 public | parttbs    | postgres | partitioned table | 
 public | fkey1_pkey | postgres | partitioned index | fkey1
 public | idx1_part1 | postgres | partitioned index | part1
(8 rows)

• 显示手动 URL
  使用 \h 命令指定 DDL 会显示相应手册的 URL。

postgres=#  \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

URL: https://www.postgresql.org/docs/12/sql-createindex.html

1.13.vacuumdb

以下选项已添加到 vacuumdb 命令中。

• --disable-page-skipping
  此选项用于从命令行执行 VACUUM （DISABLE_PAGE_SKIPPING） 语句。可以为 PostgreSQL 9.6 或更高版本的实例指定此选项。

$ vacuumdb --disable-page-skipping
vacuumdb: vacuuming database "postgres"

• --skip-locked

此选项用于从命令行执行 VACUUM （SKIPP_LOCKED） 语句。可以为 PostgreSQL 12 或更高版本的实例指定此选项。

$  vacuumdb --skip-locked
vacuumdb: vacuuming database "postgres"

• --min-mxid-age

仅在multixact ID 年龄至少为mxid_age的表上执行 vacuum 或 analyze 命令。

• --min-xid-age

仅在事务ID 年龄至少为xid_age的表上执行 vacuum 或 analyze 命令。

1.14.vacuumlo

vacuumlo 命令现在有一个可用的长名称选项。

2.扩展调整

描述与 contrib 模块相关的新功能。

2.1.auto_explain

• auto_explain.log_level

postgres=#  LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_level = NOTICE ;
SET
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# select * from api limit 1;
NOTICE:  duration: 0.079 ms  plan:
Query Text: select * from api limit 1;
Limit  (cost=0.00..0.21 rows=1 width=36)
  ->  Seq Scan on api  (cost=0.00..1.05 rows=5 width=36)
 id |                          jdoc                           
----+---------------------------------------------------------
  1 | {"guid": "1", "name": "Item 1", "tags": ["qui", "foo"]}
(1 row)

• auto_explain.log_settings

确定是否将更改的优化器相关参数输出到日志中。默认值为 ‘off’，因此不会输出日志。

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# SET auto_explain.log_min_duration = 0 ;
SET
postgres=# set random_page_cost=8;
SET
postgres=# select * from api limit 1;
 id |                          jdoc                           
----+---------------------------------------------------------
  1 | {"guid": "1", "name": "Item 1", "tags": ["qui", "foo"]}
(1 row)

日志
2025-02-26 07:51:32.933 UTC [3004] LOG:  duration: 0.020 ms  plan:
        Query Text: select * from api limit 1;
        Limit  (cost=0.00..0.21 rows=1 width=36)
          ->  Seq Scan on api  (cost=0.00..1.05 rows=5 width=36)
        Settings: random_page_cost = '8'

• auto_explain.sample_rate

指定日志输出的采样率。默认值为 1，输出所有的 SQL 语句。

• 添加 JIT 编译信息

JIT 编译信息现在输出到日志中（向后移植到 PostgreSQL 11.2）。

2025-02-26 08:30:39.585 UTC [3004] LOG:  duration: 1.828 ms  plan:
        Query Text: select count(1) from t;
        Aggregate  (cost=1.01..1.02 rows=1 width=8)
          ->  Seq Scan on t  (cost=0.00..1.01 rows=1 width=0)
        Settings: jit_above_cost = '1', random_page_cost = '8'
        JIT:
          Functions: 2
          Options: Inlining false, Optimization false, Expressions true, Deforming true

2.2.citext

添加了计算 64 位哈希值的 citext_hash_extended。第二个参数指定 SEED。

postgres=# create extension citext;
CREATE EXTENSION
postgres=# CREATE TABLE users (
postgres(#     username CITEXT,
postgres(#     email CITEXT
postgres(# ) PARTITION BY HASH (citext_hash_extended(username, 0));
CREATE TABLE
postgres=# CREATE TABLE users_part1 PARTITION OF users
postgres-#     FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_part2 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE users_part2 PARTITION OF users
postgres-#     FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE users_part3 PARTITION OF users
postgres-#     FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE users_part4 PARTITION OF users
postgres-#     FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE
postgres=# INSERT INTO users (username, email) VALUES ('Admin', 'admin@example.com');
INSERT 0 1
postgres=# INSERT INTO users (username, email) VALUES ('User1', 'user1@example.com');
INSERT 0 1
postgres=# INSERT INTO users (username, email) VALUES ('User2', 'user2@example.com');
INSERT 0 1
postgres=# SELECT * FROM users;
 username |       email       
----------+-------------------
 Admin    | admin@example.com
 User1    | user1@example.com
 User2    | user2@example.com
(3 rows)

postgres=# select citext_hash_extended(username, 0) from users;
 citext_hash_extended 
----------------------
 -5410357177159054500
  -548550277032800196
 -1221930631176555206
(3 rows)

2.3.hstore

添加了 hstore_hash_extended 以计算 64 位哈希值。第二个参数指定 SEED。

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# CREATE TABLE my_table (
postgres(#     id SERIAL,
postgres(#     data HSTORE
postgres(# ) PARTITION BY HASH (hstore_hash_extended(data, 12345));
CREATE TABLE
postgres=# INSERT INTO my_table (data) VALUES 
postgres-#     ('"key1"=>"value1", "key2"=>"value2"'),
postgres-#     ('"key3"=>"value3", "key4"=>"value4"'),
postgres-#     ('"key5"=>"value5", "key6"=>"value6"');
ERROR:  no partition of relation "my_table" found for row
DETAIL:  Partition key of the failing row contains (hstore_hash_extended(data, 12345::bigint)) = (9012811922705927596).
postgres=# 
postgres=# 
postgres=# CREATE TABLE my_table_part2 PARTITION OF my_table FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE
postgres=# CREATE TABLE my_table_part1 PARTITION OF my_table FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
postgres=# CREATE TABLE my_table_part3 PARTITION OF my_table FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE
postgres=# CREATE TABLE my_table_part4 PARTITION OF my_table FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE
postgres=# INSERT INTO my_table (data) VALUES 
    ('"key1"=>"value1", "key2"=>"value2"'),
    ('"key3"=>"value3", "key4"=>"value4"'),
    ('"key5"=>"value5", "key6"=>"value6"');
INSERT 0 3
postgres=# SELECT 
postgres-#     id,
postgres-#     data,
postgres-#     hstore_hash_extended(data, 12345) AS hash_value
postgres-# FROM 
postgres-#     my_table;
 id |                data                |     hash_value      
----+------------------------------------+---------------------
  4 | "key5"=>"value5", "key6"=>"value6" | 6442377617269448344
  3 | "key3"=>"value3", "key4"=>"value4" | 1241036135797765354
  2 | "key1"=>"value1", "key2"=>"value2" | 9012811922705927596
(3 rows)

2.4.pg_stat_statements

pg_stat_statements 模块的 pg_stat_statements_reset 功能新增了限制统计信息删除范围的参数。可以通过指定数据库 ID、用户 ID 和查询 ID 来删除统计信息。如果省略这些参数（默认值为 0），则将像以前一样丢弃所有统计信息。

postgres=#  \dfS pg_stat_statements_reset
                                                        List of functions
 Schema |           Name           | Result data type |                        Argument data types                         | Type 
--------+--------------------------+------------------+--------------------------------------------------------------------+------
 public | pg_stat_statements_reset | void             | userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0 | func
(1 row)

拥有 pg_read_all_stats 角色的用户无法执行此函数。

2.5.postgres_fdw

postgres_fdw 模块添加了一个 “options” 选项，用于指定可以在远程会话中运行的 GUC 选项。例如，可以更改 work_mem 参数和 geqo 参数的设置。设置值以 “-c parameter name = value” 格式描述。指定多个参数时，请指定整个 “-c parameter_name = value” ，并用空格分隔。

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw  OPTIONS (host '172.17.0.5', options '-c work_mem=4MB') ;
CREATE SERVER
postgres=# ALTER SERVER remsvr1 OPTIONS (SET options '-c work_mem=16MB -c geqo=off') ;
ALTER SERVER
postgres=#  SELECT * FROM pg_foreign_server ;
  oid  | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl |                        srvoptions                        
-------+---------+----------+--------+---------+------------+--------+----------------------------------------------------------
 74084 | remsvr1 |       10 |  74083 |         |            |        | {host=172.17.0.5,"options=-c work_mem=16MB -c geqo=off"}
(1 row)

参考

《PostgreSQL_12_GA_New_Features_en_20191011-1》