1.架构调整

1.1.新增元数据字典

伴随着新增的功能，添加了以下系统目录。

• pg_hba_file_rules

pg_hba_file_rules 可以引用 pg_hba.conf 文件的内容。更改文件时，视图的内容会立即反映出来。视图仅包含文件中非注释的行。

postgres=# select * from pg_hba_file_rules ;
 line_number | type  |   database    | user_name |  address  |                 netmask                 | auth_method | options | error 
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
          84 | local | {all}         | {all}     |           |                                         | trust       |         | 
          86 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          88 | host  | {all}         | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          91 | local | {replication} | {all}     |           |                                         | trust       |         | 
          93 | host  | {replication} | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
          95 | host  | {all}         | {all}     | all       |                                         | md5         |         | 
(6 rows)

• pg_partitioned_table

pg_partitioned_table 查看父表及分区子表之间的关系

• pg_publication

查看发布的名称、捕获的操作及发布的owner用户信息

• pg_publication_rel

查看发布表与发布之间的对应关系

• pg_publication_tables

存储发布表与发布的关系

• pg_sequence

创建的序列信息，普通用户可执行

postgres=> table pg_sequence;
 seqrelid | seqtypid | seqstart | seqincrement |   seqmax   | seqmin | seqcache | seqcycle 
----------+----------+----------+--------------+------------+--------+----------+----------
    17331 |       23 |        1 |            1 | 2147483647 |      1 |        1 | f
    17348 |       23 |        1 |            1 | 2147483647 |      1 |        1 | f
(2 rows)

• pg_sequences

pg_sequences 是根据 pg_sequence创建的视图，同样存储序列的信息，但是它可以查询 last_value 的值（用户需要有函数pg_sequence_last_value的执行权限并且对查询出来的序列有USAGE、SELECT权限，否则查询出来的值是空）

postgres=# table pg_sequences;
 schemaname |   sequencename    | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+-------------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 public     | base_table_id_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |           
 public     | pg_9_tab_id_seq   | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |      21842
(2 rows)

• pg_stat_subscription

查看 subscription 接受到的Wal位置，可以查看到其他库的 subscription 应用情况

• pg_statistic_ext

pg_statistic_ext 存储通过CREATE STATISTICS创建的扩展的统计信息

• pg_subscription

查看发布的名称、捕获的操作及发布的owner用户信息;需要用户拥有superuser属性

• pg_subscription_rel

记录了订阅端逻辑复制的表

1.2.修改的元数据字典

下面的数据字典已经有所调整

除 postmaster 进程之外的所有后端进程的状态现在都显示在pg_stat_activity目录。后端进程的类型可以通过backend_type列来确认。

1.3.libpq 库的增强

以下增强功能已添加到 PostgreSQL 客户端库 libpq

1.3.1.多实例配置

在 JDBC 驱动程序中已经支持的连接多个实例的设置，现在也在 libpq 库中实现。如下面所述，可以用逗号分隔的形式描述多个主机名和端口号。

host=host1,host2 
host=host1,host2 port=port1,port2 
postgresql://host1,host2/ 
postgresql://host1:port2,host2:port2/ 

可以在环境变量 PGHOST 和 PGPORT 中使用逗号 (,) 分隔符指定多个值。同时，现在也可以为 psql 和 pg_basebackup 命令的 --host 和 --port 参数指定多个值。

1.3.2.添加了 target_session_attrs 属性

target_session_attrs 已作为一个新的连接属性添加。这个参数可以设置为 “any”（如果要连接的实例是热备份模式），或者设置为 “read-write”（如果实例是可写的）。对于环境变量 PGTARGETSESSIONATTRS 也可以指定类似的值。在内部，它似乎使用 SHOW transaction_read_only 语句来确定连接的目标实例。
参考:

host=localhost dbname=mydatabase user=myuser password=mypassword port=5432 target_session_attrs=read-write

1.3.3.添加了 passfile 属性

“passfile” 被添加为一个新的连接属性。过去它是通过环境变量 PGPASSFILE 等来指定的。passfile 属性提供了一个方式来避免在连接字符串中直接暴露密码，这样可以提升安全性
参考:

host=localhost dbname=mydatabase user=myuser passfile=/home/user/.pgpass

1.4.事物日志名称从 XLOG 改为 WAL

在函数、目录名称和工具中，原先使用的 XLOG 名称已统一为 WAL。同时，pg_clog 目录已更名为 pg_xact 目录。由于 log_directory 参数默认值的变化，日志文件的默认输出目录名称也发生了变化。此外，表示 WAL 位置的 “location” 名称已更改为 “lsn”。

同时，错误消息中包含的字符串 XLOG 也已更改为 WAL。它已更改为以下消息。

Failed while allocating a WAL reading processor. 
• could not read two-phase state from WAL at … 
• expected two-phase state data is not present in WAL at … 
• Failed while allocating a WAL reading processor. 
• WAL redo at %X/%X for %s 
• Forces a switch to the next WAL file if a new file has not been started within N seconds. 

参数 archive_timeout 的描述已更改如下。

• Forces a switch to the next WAL file if a new file has not been started within N seconds.

1.5.临时复制槽

复制槽可用于构建流式复制环境或用于 pg_basebackup 命令。在 PostgreSQL 10 中，现在可以创建临时复制槽。一个临时复制槽与普通复制槽相同，只是它会在会话终止时自动删除。要创建临时复制槽，需要设置pg_create_physical_replication_slot 函数或 pg_create_logical_replication_slot 函数第三个参数为 true ，temporary 列已添加到 pg_replication_slots 视图中。

postgres=# SELECT pg_create_physical_replication_slot('temp1', true, true) ;
 pg_create_physical_replication_slot 
-------------------------------------
 (temp1,0/7F21CCB8)
(1 row)

postgres=# SELECT pg_create_logical_replication_slot('temp2', 'pgoutput', true) ;
 pg_create_logical_replication_slot 
------------------------------------
 (temp2,0/7F21CDD0)
(1 row)

postgres=# select slot_name,plugin,slot_type from pg_replication_slots where temporary='t';
 slot_name |  plugin  | slot_type 
-----------+----------+-----------
 temp1     |          | physical
 temp2     | pgoutput | logical
(2 rows)

1.6.hash index 记录到WAL日志

以前版本的哈希索引在更新时未生成 WAL。在 PostgreSQL 10 中，它现在会生成 WAL，因此它现在可以在流复制环境中使用。在CREATE INDEX USING HASH 语句不再输出。
PostgreSQL 9.6

postgres=> CREATE INDEX Idx1_hash1 ON hash1 USING hash (c1) ; 
WARNING:  hash indexes are not WAL-logged and their use is discouraged 
CREATE INDEX 

PostgreSQL 10

postgres=> CREATE INDEX idx1_hash1 ON hash1 USING hash (c1) ; 
CREATE INDEX 

1.7.增加role

增加了下面的角色，这些角色没有login权限

注册以下 Contrib 模块
• pg_freespacemap
• pg_stat_statements
• pg_visibility
• pgstattuple
• pg_buffercache

角色 pg_stat_scan_tables 可以访问
select * from pg_stat_statements;
select * from pg_stat_user_tables;
select * from pg_stat_activity;
SELECT * FROM pgstattuple('base_table');
SELECT * FROM pg_freespace('base_table',0);
select  all_frozen,all_visible, count(1) from pg_visibility('base_table') group by  all_visible,all_frozen ;
访问pg_buffercache 报错
postgres=> select * from pg_buffercache;
ERROR:  permission denied for relation pg_buffercache

角色 pg_read_all_stats\pg_read_all_settings 可以访问
select * from pg_stat_statements ;
select * from pg_stat_activity;
select * from pg_stat_user_tables;

访问其他的视图或函数报错
postgres=> SELECT * FROM pgstattuple('base_table');
ERROR:  permission denied for function pgstattuple
postgres=> SELECT * FROM pg_freespace('base_table',0);
ERROR:  permission denied for function pg_freespace
postgres=> select  all_frozen,all_visible, count(1) from pg_visibility('base_table') group by  all_visible,all_frozen ;
ERROR:  permission denied for function pg_visibility
postgres=> select * from pg_buffercache;
ERROR:  permission denied for relation pg_buffercache

角色 pg_monitor可以访问
select * from pg_stat_statements;
select * from pg_stat_user_tables;
select * from pg_stat_activity;
SELECT * FROM pgstattuple('base_table');
SELECT * FROM pg_freespace('base_table',0);
select  all_frozen,all_visible, count(1) from pg_visibility('base_table') group by  all_visible,all_frozen ;
select * from pg_buffercache;

经过验证 pg_monitor 的权限高于pg_read_all_stats、pg_read_all_settings、pg_stat_scan_tables 三个角色的加和，可以查询 pg_buffercache，其他三个角色同时赋予一个用户也无法查询 pg_buffercache 视图

1.8.Wal大小

由 “configure” 命令的 --with-wal-segsize 选项决定的 WAL 文件大小选择已增加。除了传统的 1 到 64 之外，还可以使用 128、256、512、1024。

1.9.ICU

ICU 可用于 locale 功能。在执行 configure 命令时指定 --with-icu。什么时候在 Linux 环境中构建，需要安装 libicu 包和 libicu-devel 包。

postgres=# CREATE COLLATION zh_pinyin (provider = icu, locale ='zh@collation=pinyin');
CREATE COLLATION
postgres=# 
postgres=# 
postgres=# select * from (
    values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
    ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
    ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
    ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
) _(id,name,remark) order by name collate zh_pinyin;
 id |   name    |          remark          
----+-----------+--------------------------
 10 |           | 这是一个<空串>
 11 |           | 这是一个<空格>
 6  |  A孙剑  | 
 21 | _         | 
 16 | !         | 
 25 | {         | 
 17 | @         | 
 18 | /         | 
 15 | #         | 
 22 | ~         | 
 19 | 9         | 
 9  | 陈强    | 
 8  | 董大鹏 | 
 1  | 秦浪    | 
 2  | 魏东来 | 
 4  | 徐大嫂 | 
 3  | 张大大 | 
 5  | 张老师 | 
 7  | 周芷诺 | 
 13 | a         | 
 14 | A         | 
 12 | b         | 
 23 | z         | 
 20 | Z         | 
 24 |           | 这是一个空值<NULL>
(25 rows)

postgres=# select * from (
    values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
    ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
    ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
    ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
) _(id,name,remark) order by name;
 id |   name    |          remark          
----+-----------+--------------------------
 10 |           | 这是一个<空串>
 11 |           | 这是一个<空格>
 16 | !         | 
 15 | #         | 
 18 | /         | 
 17 | @         | 
 21 | _         | 
 25 | {         | 
 22 | ~         | 
 19 | 9         | 
 13 | a         | 
 14 | A         | 
 6  |  A孙剑  | 
 12 | b         | 
 23 | z         | 
 20 | Z         | 
 7  | 周芷诺 | 
 3  | 张大大 | 
 5  | 张老师 | 
 4  | 徐大嫂 | 
 1  | 秦浪    | 
 8  | 董大鹏 | 
 9  | 陈强    | 
 2  | 魏东来 | 
 24 |           | 这是一个空值<NULL>
(25 rows)

1.10.EUI-64数据类型

在 PostgreSQL 数据库中，我们可以选择不同的数据类型来存储 EUI-64 地址。常见的数据类型包括bit(64)、bytea 和自定义类型等

bit(64)类型：

postgres=# CREATE TABLE devices (
    name VARCHAR(255),
    address BIT(64)
);
postgres=# insert into devices values('a',B'0010000000100001001000010010001100100100001001010010011000100111');
INSERT 0 1

--bit(64)使用to_hex()转换为16进制，然后使用decode()转换为bytea类型，convert_from()转换为ascii 字符
postgres=# SELECT *,convert_from(decode(to_hex(address::bigint), 'hex'),'utf8')  from devices;
 name |                             address                              | convert_from 
------+------------------------------------------------------------------+--------------
 a    | 0010000000100001001000010010001100100100001001010010011000100111 |  !!#$%&'
(1 row)

bytea 类型

postgres=# CREATE TABLE devices1 (
    name VARCHAR(255),
    address BYTEA
);
postgres=# insert into devices1 values('a',E'\\x2021222324252627');
INSERT 0 1
--convert_from 转换bytea类型数据为对应的ascii 字符
postgres=# select *,convert_from(address,'utf8') from devices1;
  name   |         address          | convert_from 
---------+--------------------------+--------------
 a       | \x2021222324252627       |  !"#$%&'
(1 row)

1.11.唯一键join

在 JOIN 表时，可以生成使用唯一索引执行 JOIN 的执行计划。在 EXPLAIN VERBOSE 语句显示的执行计划中，会出现 “Inner Unique： true”。

postgres=# CREATE TABLE tab1(c1 INTEGER PRIMARY KEY, c2 VARCHAR(10)) ; 
CREATE TABLE
postgres=# CREATE TABLE tab2(c1 INTEGER PRIMARY KEY, c2 VARCHAR(10)) ; 
CREATE TABLE
postgres=# EXPLAIN VERBOSE SELECT * FROM tab1 u1 INNER JOIN tab2 u2 ON u1.c1 = u2.c1 ; 
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Hash Join  (cost=36.10..60.76 rows=1160 width=84)
   Output: u1.c1, u1.c2, u2.c1, u2.c2
   Inner Unique: true
   Hash Cond: (u1.c1 = u2.c1)
   ->  Seq Scan on public.tab1 u1  (cost=0.00..21.60 rows=1160 width=42)
         Output: u1.c1, u1.c2
   ->  Hash  (cost=21.60..21.60 rows=1160 width=42)
         Output: u2.c1, u2.c2
         ->  Seq Scan on public.tab2 u2  (cost=0.00..21.60 rows=1160 width=42)
               Output: u2.c1, u2.c2
(10 rows)

2.监控

2.1.监视等待事件

pg_stat_activity中 wait_event_type 和 wait_event 列中显示的 Wait 事件已添加。PostgreSQL 9.6 列wait_event_type 等待事件名称 LWLockNamed 和 LWLockTranche 列已重命名为 LWLock。

2.2.EXPLAIN SUMMARY 语句

EXPLAIN 语句中新增 SUMMARY 子句，只输出执行计划生成时间。

postgres=# EXPLAIN (SUMMARY) SELECT * FROM tab1 ;
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on tab1  (cost=0.00..21.60 rows=1160 width=42)
 Planning time: 0.059 ms
(2 rows)

2.3.VACUUM VERBOSE语句

最旧的 xmin 和冻结的页面现在作为 VACUUM VERBOSE 语句的输出。

postgres=#  VACUUM VERBOSE tab1;
INFO:  vacuuming "public.tab1"
INFO:  index "tab1_pkey" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "tab1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6537
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

3.基于Quorum的同步复制

在 PostgreSQL 9.5 及更早版本中，只有一个实例可用于同步复制。在PostgreSQL 9.6 版本，可以在多个实例上执行同步复制。PostgreSQL 10 实现了基于 Quorum 的同步复制，任意选择实例进行同步复制。同步复制环境由synchronous_standby_names 配置参数。
PostgreSQL 9.5及之前
synchronous_standby_names = application_name, application_name, …
表示在同一时刻只有一个同步备库，按照应用名称先后顺序进行选择

PostgreSQL 9.6
synchronous_standby_names = num_sync (application_name, application_name, …)
表示在同一时刻可以有一个或者多个同步备库，按照应用名称先后顺序进行选择

PostgreSQL 10
synchronous_standby_names = FIRST | ANY num_sync (application_name, application_name, …)
指定 FIRST 或省略它将具有与 PostgreSQL 9.6 相同的行为。优先事项是按照参数 application_name 中描述的顺序确定，同步复制为为 num_sync 指定的实例数执行。
如果指定了 ANY，则它不依赖于 parameter 中指定的实例的顺序application_name，并将确定当 WAL 传输到 num_sync 指定的从属实例时同步复制的完成。如果为配置参数 synchronous_standby_names 指定了 ANY，在视图pg_stat_replication 列sync_state显示为quorum 。

postgres=> SHOW synchronous_standby_names ; 
synchronous_standby_names -------------------------------------- 
any 2 (standby1, standby2, standby3) 
(1 row) 

postgres=> SELECT application_name,sync_state,sync_priority FROM pg_stat_replication ; 
application_name | sync_state | sync_priority 
------------------+------------+--------------- 
standby1         | quorum     |             1
standby2         | quorum     |             1
standby3         | quorum     |             1
(3 rows) 

4.行级别安全性的增强

4.1.概述

当为一个表设置多个策略时，策略由 PostgreSQL 9.6 及更早版本中的 OR 条件确定。在 PostgreSQL 10 中，可以指定具有 AND 条件的策略。现在可以在创建策略的 CREATE POLICY 语句中指定 AS PERMISSIVE 子句和 AS RESTRICTIVE 子句。指定 AS PERMISSIVE 子句时，限制变为松散 （OR），如果指定了 AS RESTRICTIVE，则限制变为严格 （AND）。指定省略时，它将与以前的版本相同。与此同时，pg_policy Catalog 和 pg_policies Catalog 中添加了表示条件指定的列。
pg_policy 新增列

pg_policies 新增列

创建命令

Command:     CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

4.2.验证多个 POLICY 设置

设置了多个 POLICY 并验证了效果。PERMISSIVE 模式下的 POLICY p1 和 RESTRICTIVE 模式下的 POLICY p2、p3 是为表 tab1 准备的，并通过将它们组合进行验证。

--创建表和 POLICY （PERMISSIVE + RESTRICTIVE）
postgres=> CREATE TABLE tab1 (c1 NUMERIC, c2 VARCHAR(10), uname VARCHAR(10)) ; 
CREATE TABLE 
postgres=> ALTER TABLE tab1 ENABLE ROW LEVEL SECURITY ; 
ALTER TABLE 
postgres=> CREATE POLICY p1 ON tab1 FOR ALL USING (uname = current_user) ; 
CREATE POLICY 
postgres=> CREATE POLICY p2 ON tab1 AS RESTRICTIVE FOR ALL USING (c2 = 
'data') ; 
CREATE POLICY 
postgres=> SELECT polname, polpermissive FROM pg_policy ; 
 polname | polpermissive ---------+--------------- 
 p1    | t 
 p2    | f 
(2 rows) 
postgres=> SELECT tablename, policyname, permissive FROM pg_policies ; 
 tablename | policyname | permissive -----------+------------+------------- 
 tab1   | p1       | PERMISSIVE 
 tab1   | p2       | RESTRICTIVE 
(2 rows) 
postgres=> \d tab1 
                     Table "public.tab1" 
 Column |         Type          | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- 
 c1     | numeric               |           |          | 
 c2     | character varying(10) |           |          | 
 uname  | character varying(10) |           |          | 
Policies: 
    POLICY "p1" 
      USING (((uname)::name = CURRENT_USER)) 
    POLICY "p2" AS RESTRICTIVE 
      USING (((c2)::text = 'data'::text))

在上面的示例中，表 tab1 的设置显示 POLICY p2 是 RESTRICTIVE。

--查看此策略 （PERMISSIVE + RESTRICTIVE）下的执行计划
postgres=> EXPLAIN SELECT * FROM tab1 ; 
                                 QUERY PLAN -------------------------------------------------------------------------- 
 Seq Scan on tab1  (cost=0.00..20.50 rows=1 width=108) 
   Filter: (((c2)::text = 'data'::text) AND ((uname)::name = CURRENT_USER)) 
(2 rows)

可以看出，这两个条件是由 “AND” 组合而成的。删除 POLICY p1 并创建在 RESTRICTIVE 模式下的policy p3。

postgres=> drop policy p1 on tab1 ;
DROP POLICY
postgres=> CREATE POLICY p3 ON poltbl1 AS RESTRICTIVE FOR ALL USING 
(c1 > 1000) ; 
CREATE POLICY 
postgres=> \d tab1 
                     Table "public.tab1" 
 Column |         Type          | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- 
 c1     | numeric               |           |          | 
 c2     | character varying(10) |           |          | 
 uname  | character varying(10) |           |          | 
Policies: 
    POLICY "p2" AS RESTRICTIVE 
      USING (((c2)::text = 'data'::text)) 
    POLICY "p3" AS RESTRICTIVE 
      USING ((c1 > (1000)::numeric))

--查看此策略 （RESTRICTIVE + RESTRICTIVE）下的执行计划
postgres=> EXPLAIN SELECT * FROM poltbl1 ; 
QUERY PLAN -------------------------------------------- 
Result  (cost=0.00..0.00 rows=0 width=108) 
One-Time Filter: false 
(2 rows)

如果所有策略都处于 RESTRICTIVE 模式，则执行计划似乎不会显示。

参考

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