PostgreSQL 9.3 中的新功能

来自 PostgreSQL wiki
跳转到导航跳转到搜索

此页面包含 PostgreSQL 版本 9.3 特性的概览，包括说明、测试及使用信息，以及指向包含进一步信息的博客文章的链接。另请参阅 发行说明 和 PostgreSQL 9.3 打开项。

配置指令“include_dir”

除通过“include”指令包含单独的配置文件之外，postgresql.conf 现在还提供了“include_dir”指令，它会在指定的目录或目录中读取所有以“.conf”结尾的文件。

目录既可以以绝对路径指定，也可以相对于主配置文件的位置指定。将按照出现的顺序读取目录，而文件将按 C 区域规则排序后读取。包含的文件可以包含其自己的“include_dir”指令。

链接

COPY FREEZE 用于更高效的批量加载

为了改进表的初始批量加载，已向 COPY 命令添加了一个 FREEZE 参数，使其能够以已冻结行的方式复制数据。请参阅文档了解用法和注意事项。

链接

  • 文档 - 请参阅 FREEZE 参数

自定义后台工作进程

此功能使模块能够将自身注册为“后台工作进程”，有效地作为自定义服务器进程运行。这是一个功能强大的新特性，具有多种可能的用例，例如监视服务器活动、按预定义间隔执行任务以及自定义日志记录等。

后台工作进程可以附加到 PostgreSQL 的共享内存区，并从内部连接到数据库；通过链接到 libpq，它们还可以像常规客户端应用程序一样连接到服务器。后台工作进程是用 C 编写的，作为服务器进程，它们可以无限制地访问所有数据，并可能影响其他服务器进程，这意味着它们可能会导致潜在的安全/稳定性风险。因此，应谨慎开发和部署后台工作进程。

提供一个示例超出了本文的范围；请参阅下面链接的博客，其中提供了带注释的样例代码。PostgreSQL 源代码还在 contrib/worker_spi 中包含一个示例后台工作进程。

链接

数据校验和

现在 PostgreSQL 可以校验和数据页并报告损坏情况。这是一个集群范围内的设置，不能应用于单个数据库或对象。还应该注意，此功能可能会产生显着的影响性能。必须在 initdb 期间启用此选项，并且不能更改（不过有一个新的 GUC 参数“ignore_checksum_failure”，即使检测到损坏情况，也会强制 PostgreSQL 继续处理事务）。

链接

JSON：附加功能

PostgreSQL 9.2 中引入了用于转换行和数组的 JSON 数据类型 和 两个支持函数。随着 PostgreSQL 9.3 的发布，它引入了专用的 JSON 运算符，并将函数数量扩展到 12 个，其中包括 JSON 解析支持。JSON 解析器已公开，供扩展程序等其他模块作为 API 使用。

此外，hstore 扩展程序获得了两个与 JSON 相关的函数，hstore_to_json(hstore) 和 hstore_to_json_loose(hstore)。在将 hstore 值转换为 json 时使用前者。

链接

LATERAL JOIN

简而言之，LATERAL JOIN允许子查询中的FROM部分引用 FROM 列表中前面项中的列。

以下是一个自包含的（尽管相当无意义）示例，说明有时能够编写这种子句的类型，

SELECT base.nr,
       multiples.multiple
  FROM (SELECT generate_series(1,10) AS nr) base
  JOIN (SELECT generate_series(1,10) AS b_nr, base.nr * 2 AS multiple) multiples
       ON multiples.b_nr = base.nr

但它会产生以下之类的错误消息

 LINE 4:    JOIN (SELECT generate_series(1,10) AS b_nr, base.nr * 2 A...
                                                       ^
 HINT:  There is an entry for table "base", but it cannot be referenced from this part of the query.

使用LATERAL JOIN，现在可以使第二个子查询引用第一个子查询中的值

SELECT base.nr,
       multiples.multiple
  FROM (SELECT generate_series(1,10) AS nr) base,
LATERAL (
  SELECT multiples.multiple FROM
   ( SELECT generate_series(1,10) AS b_nr, base.nr * 2 AS multiple ) multiples
    WHERE multiples.b_nr = base.nr
  ) multiples;

请注意，现在函数调用可以直接引用前面FROM的项目中的列，甚至没有LATERAL关键字。示例

CREATE FUNCTION multiply(INT, INT)
  RETURNS INT
  LANGUAGE SQL
AS
$$
  SELECT $1 * $2;
$$

列表中的函数调用查询FROM

SELECT base.nr,
       multiple
  FROM (SELECT generate_series(1,10) AS nr) base,
       multiply(base.nr, 2) AS multiple

在以前的版本中，这个查询会产生类似错误

ERROR:  function expression in FROM cannot refer to other relations of same query level
LINE 4:        multiply(base.nr, 2) AS multiple

请参阅下面链接的文章，了解一些更贴近实际的示例。

链接

用于进行更快速备份的并行 pg_dump

新的 -j njobs (--jobs='njobs') 选项使 pg_dump 能够同时转储 njobs 个表，从而减少了转储数据库所花费的时间。示例

 pg_dump -U postgres -j4  -Fd -f /tmp/mydb-dump mydb

此示例使用 4 个并发连接将数据库“mydb”的内容转储到目录“/tmp/mydb-dump”。

警告

  • 并行转储只能采用目录格式
  • 并行转储会给数据库带来更多负载，尽管总的转储时间应该会更短
  • pg_dump 将向数据库打开 njobs + 1 个连接，因此应适当地设置 max_connections
  • 在运行并行转存时，请求数据库对象的独占锁可能会导致转存失败
  • 9.2 之前的服务器执行并行转存时，需要特别注意

针对一个 4.5GB 的数据库（压缩后大约为 370MB 的转存文件）对该特性的即席测试，设置不同的 -j 值时，产生了以下耗时

  • (无 -j)：1 分 3 秒
  • -j2：0 分 28 秒
  • -j3：0 分 24 秒
  • -j4：0 分 24 秒
  • -j5：0 分 25 秒

链接

'pg_isready' 服务器监控工具

pg_isready 是一个封装了 PQping，并作为一个标准客户端应用程序创建的包装器。它接受一个 libpq 风格的连接字符串，并返回四个退出状态之一

  • 0：服务器正常接收连接
  • 1：服务器拒绝连接（例如，在启动期间）
  • 2：服务器未响应连接尝试
  • 3：未进行连接尝试（例如，由于无效的连接参数）

使用示例

barwick@localhost:~$ pg_isready
/tmp:5432 - accepting connections
barwick@localhost:~$ pg_isready --quiet && echo "OK"
OK
barwick@localhost:~$ pg_isready -p5431 -h localhost
localhost:5431 - accepting connections
barwick@localhost:~$ pg_isready -h example.com
example.com:5432 - no response

链接

切换到 Posix 共享内存和 mmap()

在 9.3 版本中，PostgreSQL 已从使用 SysV 共享内存切换到使用 Posix 共享内存和 mmap 进行内存管理。这可以更容易地安装和配置 PostgreSQL，并且这意味着除了在非正常情况下之外，不再需要调整系统参数（例如 SHMMAX 和 SHMALL）。我们需要用户严格地测试并确保该更改未引入任何内存管理问题。

链接

触发器特性

事件触发器

现在可以在 DDL 事件（CREATE、ALTER、DROP）上定义触发器。

链接

视图特性

物化视图

物化视图是一种特殊的视图，它将视图的输出作为物理表来缓存，而不是每次访问时执行底层查询。从概念上讲，它们类似于 “CREATE TABLE AS”，但会存储视图定义，以便可以轻松地刷新视图。

请注意，物化视图无法自动刷新并且刷新不是增量的；同时，也无法操作基础表。但是，它们将由 pg_restore 自动填充（更准确地说，pg_dump 包含一个 “REFRESH MATERIALIZED VIEW” 语句）。

人为示例

使用一些任意数据创建一个表并填充该表

CREATE TABLE matview_test_table (
 id SERIAL PRIMARY KEY,
 ts TIMESTAMPTZ NOT NULL
)

INSERT INTO matview_test_table VALUES (
 DEFAULT,
 ((NOW() - '2 days'::INTERVAL) + (generate_series(1,1000) || ' seconds')::INTERVAL)::TIMESTAMPTZ
)

创建一个物化视图，该视图将列出最近的 5 项条目

CREATE MATERIALIZED VIEW matview_test_view AS
  SELECT id, ts
    FROM matview_test_table
ORDER BY id DESC 
   LIMIT 5

postgres=# SELECT * from matview_test_view ;
  id  |              ts               
------+-------------------------------
 1000 | 2013-05-06 12:02:10.974711+09
  999 | 2013-05-06 12:02:09.974711+09
  998 | 2013-05-06 12:02:08.974711+09
  997 | 2013-05-06 12:02:07.974711+09
  996 | 2013-05-06 12:02:06.974711+09
(5 rows)

向表中添加更多数据

INSERT INTO matview_test_table VALUES (
 DEFAULT,
 ((NOW() - '1 days'::INTERVAL) + (generate_series(1,1000) || ' seconds')::INTERVAL)::TIMESTAMPTZ
)

视图输出没有更改

postgres=# SELECT * from matview_test_view ;
  id  |              ts               
------+-------------------------------
 1000 | 2013-05-06 12:02:10.974711+09
  999 | 2013-05-06 12:02:09.974711+09
  998 | 2013-05-06 12:02:08.974711+09
  997 | 2013-05-06 12:02:07.974711+09
  996 | 2013-05-06 12:02:06.974711+09
(5 rows)

刷新视图以显示表中的最新条目

postgres=# REFRESH MATERIALIZED VIEW matview_test_view ;
REFRESH MATERIALIZED VIEW
postgres=# SELECT * from matview_test_view ;
  id  |              ts               
------+-------------------------------
 2001 | 2013-05-07 12:03:10.696626+09
 2000 | 2013-05-07 12:03:09.696626+09
 1999 | 2013-05-07 12:03:08.696626+09
 1998 | 2013-05-07 12:03:07.696626+09
 1997 | 2013-05-07 12:03:06.696626+09
(5 rows)

以下链接包含更多详细信息和示例。

链接

递归视图语法

CREATE RECURSIVE VIEW 语法提供了一种简便方法，可以将递归公共表表达式 (CTE) 表述为视图。

采用来自 CTE 文档 中的示例

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;

可以按照如下方式将其创建为递归视图

CREATE RECURSIVE VIEW t(n) AS
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100;

链接

可更新视图

现在，可以使用与常规表相同的方式更新简单视图。视图只能引用一个表（或另一个可更新视图），且不得包含更复杂的运算符、联接类型等。

如果视图有 WHERE 条件，则对基础表的 UPDATE 和 DELETE 将受其定义的那些行限制。但是，UPDATE 可能会更改某行，使其在视图中不再可见，而 INSERT 命令可能会插入不满足 WHERE 条件的行。

以前可以使用 INSTEAD OF 触发器或 INSTEAD 规则将更复杂的视图制作成可更新视图。

以下表和视图使用的简单示例： 

CREATE TABLE postgres_versions ( version VARCHAR(3) PRIMARY KEY, nickname TEXT NOT NULL ); 
INSERT INTO postgres_versions VALUES ('8.0', 'Excitable Element'), ('8.1', 'Fishy Foreign Key'), ('8.2', 'Grumpy Grant'), ('8.3', 'Hysterical Hstore'), ('8.4', 'Insane Index'), ('9.0', 'Jumpy Join'), ('9.1', 'Killer Key'), ('9.2', 'Laconical Lexer'), ('9.3', 'Morose Module'); 
CREATE VIEW postgres_versions_9 AS SELECT version, nickname FROM postgres_versions WHERE version LIKE '9.%'; 

postgres=# SELECT * from postgres_versions_9; version | nickname ---------+----------------- 9.0 | Jumpy Join 9.1 | Killer Key 9.2 | Laconical Lexer 9.3 | Morose Module (4 rows) postgres=# UPDATE postgres_versions_9 SET nickname='Maniac Master' WHERE version='9.3'; UPDATE 1 postgres=# SELECT * from postgres_versions_9; version | nickname ---------+----------------- 9.0 | Jumpy Join 9.1 | Killer Key 9.2 | Laconical Lexer 9.3 | Maniac Master (4 rows)

链接

可写外部表

PostgreSQL 9.1 引入了“外部数据封装器”(FDW)，提供了一种从 PostgreSQL 内使用 SQL 访问外部数据源的方式。原始实现是只读的，但 9.3 也会支持写访问，前提是各个 FDW 驱动程序已经更新为支持此功能。在撰写本文时，只有 PostgreSQL 驱动程序支持写操作。

有关 PostgreSQL 驱动程序和简单示例的更多信息，请参阅下文。

链接

postgres_fdw

一个新的贡献模块 postgres_fdw 提供同名远程数据包装器，用于读写访问远程 PostgreSQL 服务器（或本地服务器上的另一个数据库）。

一个简单的用法示例（连接到同一服务器上的另一个数据库以方便测试）。

1. 构建 postgres_fdw 贡献模块

cd contrib/postgres_fdw
make install

2. 将模块安装为扩展

postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

3. 创建一个测试“远程”数据库

postgres=# CREATE DATABASE fdw_test;
CREATE DATABASE
postgres=# \c fdw_test
You are now connected to database "fdw_test" as user "barwick".
fdw_test=# CREATE TABLE world (greeting TEXT);
CREATE TABLE

4. 创建服务器、用户和表映射，让本地 PostgreSQL 服务器了解远程数据库

postgres=# CREATE SERVER postgres_fdw_test FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'fdw_test');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER postgres_fdw_test OPTIONS (password '');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE other_world (greeting TEXT) SERVER postgres_fdw_test OPTIONS (table_name 'world');
CREATE FOREIGN TABLE
postgres=# \det
          List of foreign tables
 Schema |    Table    |      Server       
--------+-------------+-------------------
 public | other_world | postgres_fdw_test
(1 row)

5. 操作远程表如同操作本地表一样

postgres=# INSERT INTO other_world VALUES('Take me to your leader');
INSERT 0 1 
postgres=# \c fdw_test
You are now connected to database "fdw_test" as user "barwick".
fdw_test=# SELECT * FROM world;
         hello          
------------------------
 Take me to your leader
(1 row)

下面是另一个示例，我们在此链接到远程 pgbench 数据库上的“帐户”和“分支”表

 create extension postgres_fdw;
 create user mapping for current_user server remotesrv options ( user 'postgres', password 'password' );
 create server remotesrv foreign data wrapper postgres_fdw options ( host '192.168.1.5', port '5433', dbname 'bench');
 create foreign table remoteacct (aid int, bid int, abalance int, filler char(84)) 
 server remotesrv options ( table_name 'pgbench_accounts' );
 create foreign table remotebranch ( bid int, bbalance int, filler char(88) ) 
 server remotesrv  options ( table_name 'pgbench_branches');

在设置完成后，我们可以查询远程服务器

   explain select * from remotebranch join remoteacct using ( bid ) where bid = 5;
                                   QUERY PLAN
   ----------------------------------------------------------------------------
   Nested Loop  (cost=200.00..225.40 rows=1 width=712)
   ->  Foreign Scan on remotebranch  (cost=100.00..112.66 rows=1 width=364)
   ->  Foreign Scan on remoteacct  (cost=100.00..112.73 rows=1 width=352)

注意几件事：首先，向远程服务器下推 JOIN 尚未实现（请等待 9.4！）。其次，我们获取不到远程表的真实估算值。这是可以修复的，但要告诉 Postgres 查询 EXPLAIN 信息的远程数据库

   alter foreign table remotebranch options (add use_remote_estimate 'true');
   alter foreign table remoteacct options (add use_remote_estimate 'true');
   bench=# explain select * from remotebranch join remoteacct using ( bid ) where bid = 5;
                                   QUERY PLAN
   ------------------------------------------------------------------------------
   Nested Loop  (cost=200.42..7648.07 rows=99400 width=712)
   ->  Foreign Scan on remotebranch  (cost=100.00..101.14 rows=1 width=364)
   ->  Foreign Scan on remoteacct  (cost=100.42..6552.93 rows=99400 width=97)

链接

复制改进

PostgreSQL 内置的二进制复制以四种方式进行改进：仅流式重新制作、快速故障转移、与体系结构无关的流式传输以及 pg_basebackup conf 设置。

仅流式重新制作

“重新制作”是复本集中的一个复本成为所有其他复本的新主服务器的过程。例如

  1. 主服务器 M1 正在复制到复本 R1、R2 和 R3。
  2. 需要关闭主服务器 M1 进行硬件升级。
  3. 数据库管理员提升 R1 为主服务器。
  4. R2 和 R3 重新配置和重新启动，现在从 R1 复制

简而言之，这就是重新制作。如果结合级联复制（在 9.2 中引入），它更加有用。

在较早的 PostgreSQL 版本中，重新制作需要使用 WAL 文件归档。级联复本不能仅使用流式切换主服务器；它们必须被重新克隆。该限制现已解除，允许从流中重新制作。这使得设置大型复制集群变得更加容易；如果管理员不需要在线 WAL 归档用于灾难恢复，他们就不必再设置在线 WAL 归档。

顺便提一下，这还可以设置复制呈环状的“循环”。这到底是功能还是缺陷，取决于你的看法。

链接

快速故障转移

允许在不到一秒的时间内提升复本，从而允许实现 99.999% 的正常运行时间。更多详细信息待补充。

与体系结构无关的流式传输

允许使用 pg_basebackup（用于基本备份）和 pg_receivexlog（用于日志归档）在不同的操作系统和硬件体系结构之间进行流式传输。（请注意，还原备份仍需要相同的体系结构，但这对于集中式备份服务器来说很有用）

pg_basebackup conf 设置

如果您使用 -R 开关，pg_basebackup 将在新的克隆数据目录中创建一个简单的（仅流媒体）recovery.conf 文件。这意味着您无需进行其他编辑便可以立即启动新的数据库服务器。

向后兼容性

这些更改可能导致您的应用程序出现回归问题。

CREATE TABLE 输出

除非日志级别设置为 DEBUG1，否则 CREATE TABLE 将不再输出有关隐式索引和序列创建的消息。

服务器设置

  • 参数 'commit_delay' 仅限于超级用户
  • 参数 'replication_timeout' 已重命名为 'wal_sender_timeout'
  • 参数 'unix_socket_directory' 已替换为 'unix_socket_directories'
  • 内存中分类使用它们的全部内存分配；如果 work_mem 是基于 9.3 前的行为设置的，则可能需要检查其值。

WAL 文件名可能以 FF 结束

现在的 WAL 文件将以连续流的形式写入，而不是每 4GB 跳过最后一个 16MB 段，这意味着 WAL 文件名可能以 FF 结束。可能需要修改 WAL 备份或还原脚本。

取自 "https://wiki.postgresql.ac.cn/index.php?title=What%27s_new_in_PostgreSQL_9.3&oldid=28123"