PostgreSQL Magazine 01 Chinese Edition

PostgreSQL

杂志 - 中 文版

中 文版由 以下团 队共同 完成 Postgres中 文社区 wilson.guam@postgres.org.cn 北京神州立诚科技有限公司 translator@focus-soft.com

P o s t g re S Q L ! t u o is

NoSQL :

9.1

Stephan Kaltenbrunner

: :

PostgreSQL in Mac OS X Lion

01 期

2012 年 05 月

About PG Mag PostgreSQL Magazine is edited by and for the PostgreSQL Community.

Editor: Damien Clochard Layout Editor: Cédric Gemy

编者按

Writers:

Zohaib Sibte Hassan, Marc Cousin, Simon Merrick, Joshua Drake, Hubert Lubaczewski

Reviewers:

Thom Brown, Ray O'Donnell, Osvaldo Tulini, Sivakumar, Matt Keranen, Jaime Casanova, Luca Ferrari, Nicolas Thauvin, Bob Hetrick, Josh Kupershmidt

翻译： 管威（ 神州立诚）

Tools: Scribus 1.4 / Gimp 2.6 License: The articles contained in this magazine are released under the Creative Commons Attribution-ShareAlike 3.0 Unported license. This means you may adapt, copy, distribute and transmit the articles but only under the following conditions: You must attribute the work to the original author in some way (at least a name, email or URL) and to this magazine by name ('PostgreSQL Magazine') and the URL (pgmag.org). You cannot attribute the article(s) in any way that suggests that they endorse you or your use of the work. If you alter, transform, or build upon this work, you must distribute the resulting work under the same, similar or a compatible license.

Disclaimer: PostgreSQL Magazine is an independent media.

The views and opinions in the magazine should in no way be assumed to be endorsed by the PostgreSQL Global Development Group. This magazine is provided with absolutely no warranty whatsoever; neither the contributors nor PostgreSQL Magazine accept any responsibility or liability for loss or damage resulting from readers choosing to apply this content to theirs or others computers and equipment.

Damien Clochard

PostgreSQL 中 文 杂 志

议事日程

消 息发布

6

P o s t g re S Q L 9 . 1

14

4

10 大 强 悍 新 特 性

期待 9.2

28

社 区 观点

26

01

人物 专访

8 Stephan Kaltenbrunner

NoSQL ?

12

技巧分享

32

4

Upcoming Events

4

Agenda Agenda

活

动

预

告

PGCon 2012

Postgres Open 2012

http://www.pgcon.org/2012/

http://postgresopen.org/

PG Day France

http://www.pgday.fr/

PG Conference Europe 2012

http://2012.pgconf.eu/

想知道更多本土和国际的 PostgreSQL用户 会议， wiki一下！ http://wiki.postgresql.org/wiki/Events

PG Mag #01

读 者 来自我们 的

纠正， 点子和 要求： 这部分是 每期 PostgreSQL Magazine 任由 你们倾诉想法的 地方 !

你们 的计划 是？ 来自 PostgreSQL杂志的亲们， 我们（ 维也纳城的数据库爱好者） 读了上一期试行版， 对这本杂志非常感兴趣。 关于计划多久发行一次你们 有没有任何的想法？ 有没有 可能订阅 你们 的杂志？ 你们 有没有预估它 的 售价？

如 果对于以上任何一个问题你们仍没有明确答案的话， 我们 觉得由我们 来向你反馈建议是个不错的点子。 你的 , Laurenz Albe

小编答 复： 至今我们 还不确定本杂志的发行周 期 和售价。 现在还不可能订阅 本杂志， 但我们 正致力于此。 这些问题正 在商榷之中 。 邮 件。

敬请关注我们 的 twitter， 或是订阅 我们 的新闻

http://twitter.com/#! /pg_mag http://pgmag.org/join_the_newsletter

读者 问 答

5

网读器页需主要 管答 复： 我想你可能是在讨论有关 PDF在线阅 flash 插件的 问题。 我知 道这是一种私有的格式， 但你应该可以用 Adobe插件代替以便自由 地阅读 …你试过 gnash 或是 lightspark吗？

Flash 格式目 前还没有开放， 这我也知 道。 至今我还没有 任何办法用 HTML5在线呈现一本杂志。 我知 道幻灯片可以 用 Scribd做到在线呈现， 但是对杂志来说不太合适。 如 果你有任何能帮 助 我们在这方面改进的建议， 我无任欢 迎！

平板电脑上的 PostgreSQL杂志

嗨！ 能够看到一本关于 PostgreSQL的杂志， 为 此我感到心 潮澎湃。 我喜欢它的格式和围 绕杂志的一些开源的实验。 我 在骇客新闻 中 看到了 有关它 的 公告。 所以我确 信这本杂志很 快就会流行起来。 如 果你们 的杂志在 iPad上也可用的话那就更棒了 。 我相 信还有其他一些喜欢使用 kindle或者 nook之类的电子书 阅 读的人。 我会优先在 iPad上使用 iBooks， Flipboard以及 Zinio 等程序看论文或杂志。 我尤其喜欢 flipboard在 iPad上 看论文的界面。 我知道 PDF版可以在 iPad 或者其他平板电脑上阅读。 但是， 它 还是有那么一点不方便， 因为 我需要手动 拖动它 。 如 果杂志能适用 于我的设备， 那将省了不少麻烦。 让它 适应 我的设备也会让我更加轻松地接触到它， 尤其当我不是特别 忙想看看新闻 和杂志的时候。 感谢你们 迄今做出 的巨大努力 。 John

在线版非普遍可读 …

… 由 于缺少某些私有的第三方插件。 把传输技术限定在 某些免费的格式怎么样？ 当然， 我会下载并阅读 PDF版的， 但我有一种被排斥的 感觉。 其他的在线刊 物中唯一能让我付费阅读的是 LWN， 而我付费是因为它的内容。 事实上， 对于从事技术的读者来 说， 赋予他们 一种简约的设计是非常有吸引 力 的。

也许我不属于读者中 的主流人群？ 还是你们 更多地把目 标放在那些需要浮华颜色的企业人群？ 不晓得 … 此致 敬礼！

小编答 复： 目 前我们 的注意力 正放在纸质版本上。 我们 非常乐于提供在线版但这不是我们 的主要目标。 在 “平板电

脑 ” 在个词背后， 意味着多种 多样的设备和格式类型！ 为 每 款平板电脑发行其各自版本的杂志将花费我们大量的时间。 这就是我们 将暂时坚持使用 PDF格式的原因 。

欢迎向 我们反馈意见和 建议！ 请将您对 PostgreSQL杂志的想法发送 至 feedback@pgmag.org 。 信件可以 写 得更加 翔 实一些。 但愿我们 能把所有 收到 的信息都发布出 来。

Mark Lawrence

PG Mag #01

6

消 息

PostGIS 2.0 出了！

P

ostGIS， 是一个 OGC（ 开放地理空间 信息联盟） 的 项目 ， 于 2012 年 4月推 出 2.0 版本。 这个 PostgreSQL的扩展存储 了 空间 数据， 并允许用户根据地理学规则和 相关准则 搜索这些数据。

2

.0的开发经历了 很长一段时间 ， 但是带 来了大量令人兴奋的新特性： 栅格数据 和光栅 /矢量分析， 拓扑模型， 三维及四 维 索引 ， 与 PostgreSQL扩展系统的集成， 支 持多 文件导入 Shapefile图 形用户界面， 等 等 …… http://www.postgis.org/

PG Mag #01

发

布

Postgres-XC 1.0 Beta1 发布了

P

ostgres-XC 是一种 基 PostgreSQL的 可缩放式写入的 多主对称式集群。 这 个 Beta 版基于 PostgreSQL 9.1 Beta2， PostgreSQL 9.1 稳定分支的所有修复补丁将 应用 于 Postgres-XC的稳定版上。 它在 PostgreSQL的许可下分发。 http://postgres-xc.sourceforge.net

EnterpriseDB启动云数据库

E

nterpriseDB宣布 PostgreSQL 9.1和 Postgres Plus®Advanced Server 9.0 可在 Amazon Web Services平台上运行。 http://cloud.enterprisedb.com

产品 新闻 7

PGXN Client 1.0

P

GXN Client是一种 命令行工具， 配合 PostgreSQL Extension Network使 用 ， 允许在 PostgreSQL安装程序或数据库 中 搜索， 编译， 装载或删 除扩展。 http://pgxnclient.projects.postgresql.org/

Skytools 3.0

S

kytool 是由 Skype公司 开发的用 于复 制和失效备援的工具。 他包含一个通用 队列体系 ——PgQ和 一个易用 复制 工具 ——Londiste。 与 2.1 版相比， 主要的新功 能有： 级联排队， 平行拷贝 ， 多 数据库 ticker以及可自 定义处理的 Londiste处理程 序模块。 http://wiki.postgresql.org/wiki/SkyTools

VMware结合 PostgreSQL扩展 vFabric

V

Mware建立 vFabric Postgres， 将其 作为 一种 PostgreSQL的 闭 源分支， 给 vSphere环境带来了 显著提升。 vFabric Postgres的弹性数据库内存可动 态地适应变 化的工作负载， 以实现更大的 内存效率和 更 高的固化比。 vFabric Postgres还可用 于 http://cloudfoundry.com/。

PostgreSQL加入谷歌夏日 代码活动

G

oogle再次选择将 PostgreSQL项目 加 入 2012 年度的 夏日 代码 。 这个夏天 谷歌将组织一些学生跟我们 项目 中 的导师一 起工作， 目的是去修改 PostgreSQL的代

码。

Scalr 2.5 支持 PostgreSQL

开

源计划 Scalr荣幸地宣布他们可以支 持 PostgreSQL。 从现在起， Scalr 与 PostgreSQL的用户 可以享受云的 高自 动 化调 节和低维护成本 ——包括自 动备份、 恢复以及简便管理。 http://scalr.net/features/databases/postgres

Heroku 推出 独立的 PostgreSQL服务

自

2007 年以来， PostgreSQL已成为 Heroku 平台的 可用模块。 但现在， Postgres还成为了 一项独立服务。 Heroku 可能是目 前世界上最大的 PostgreSQL托管 服务， 有超过 150000个 PostgreSQL数据 库开启 并正常运行着。 https://postgres.heroku.com/

Npgsql 2.0.12 beta3发布了

N

pgsql 是一种 100%用 C#编 写 的 .Net数据提供程序， 允许 .Net程序 与 PostgreSQL后台交互。 Npgsql 在 BSD 下 许可。 http://www.npgsql.org

Postgres Enterprise Manager v2.1

E

nterpriseDB发布了 一个新版的的管理 工具， 用 于 SNMP和 邮件告警。 Postgres Enterprise Manager(PEM) 2.1 增 添了 一系列 的 可高度配置的 仪表板， 预定 和 组织管理任务的新功 能， 以及更强的报警 能力 。

http://www.postgresql.org/developer/

PG Mag #01

8

人物 专访 PG Mag #01

人物 专访

9

PostgreSQL 杂志 : 你在哪任职？ Stefan Kaltenbrunner:

PG 杂志 :你是怎样参与 PostgreSQL项目 的？ SK:

PG 杂志 : 做一个 postgresql.org 的管理 员难吗？ 你每周 花多 长时间 在这上面？ SK:

PG 杂志 : 这个构架还有多 少其他的系统管 理员？ SK:

http://wiki.postgresql.org/wiki/Infrastructure_team

PG 杂志 : 有多 少服务正处在运行中？ SK:

“ 此时此刻， 在 61 台主 机上我们共有 571个用 Nagios监控的服务 ” PG Mag #01

10 人物 专访

PG 杂志 : 多 少台服务器被用 于运行这些 服务？ SK:

http://www.postgresql.org/about/servers/

PG 杂志 : 你似乎非常了 解 Nagios。 你 如何比较它和其他监控软件？ 如 Zabbix 或 Hyperic。 SK:

PG 杂志 : 这些服务器是谁捐 赠的 ？ SK:

PG 杂志 : 系统管理员于 2011 年 11 月更新 了 www.postgresql.org 的 主平台。 你 能再给我们 透露点有关新版本的消息吗 ?

SK:

“pgFoundry 已经不能满足我 们成长的需要了， 我认为我们应 该永不止步 ” PG 杂志 :你们 是怎么监控的 ？ SK:

➥ ➥ ➥ ➥ ➥

PG 杂志 : 话说 pgFoundry， PostgreSQL的 Forge软件 … 对此我们听到了 一 PG Mag #01

11

些抱怨。 我们 看到大量项目被转移到到其 他的代码 共享平台 (如 github) ， 一些社 区成员甚至认为 pgFoundry 应该被关 闭。 您的意见是？

最想看到 的特性是什么？

SK:

SK:

PG Conference Europe, 阿姆斯特丹 /2011

➥

➥

➥

PG 杂志 :你怎么看待新推出 的 PostgreSQL Extension Network(PGXN)？

PG 杂志 :在开源和 PostgreSQL之外你还 对什么事情感兴趣 ?

SK:

SK:

PGM: 在 PostgreSQL的下一个版本中你 PG Mag #01

12 NoSQL ?

被世人忽略的 key-value存储 是的， 我当然知道你对自己一贯拥护的 key value存储非常满意。 但是有人留意到 PostgreSQL的 hstore吗？

./configure && make install cd ./contrib && make install

CREATE EXTENSION hstore; SELECT 'foo=>bar'::hstore;

CREATE TABLE my_store ( id character varying(1024) NOT NULL, doc hstore, CONSTRAINT my_store_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX my_store_doc_idx_gist ON my_store USING gist (doc);

=>

PG Mag #01

NoSQL ? 13

“ 在一个存储下结合关系 型和 key value类型二者的 功效 ”

注释

SELECT doc ­> 'text' as tweet, doc ­> 'created_at' as created_at FROM my_store WHERE doc @> 'created_at=>00001323446095';

=>

SELECT doc ­> 'text' as tweet, doc ­> 'created_at' as created_at FROM my_store WHERE doc @> 'has_hashtags=>:t';

关于文章

原作可

见

http://pgmag.org/0113 SELECT doc ­> 'text' as tweet, doc ­> 'created_at' as created_at FROM my_store WHERE doc @> 'has_hashtags=>:t' AND doc ? 'has_urls' ORDER BY doc ­> 'created_at' DESC;

关于作者 Zohaib Sibte Hassan（ 昵称

SELECT doc ­> 'text' as tweet, doc ­> 'created_at' as created_at FROM my_store WHERE doc @> 'has_hashtags=>:t' AND doc ?| ARRAY['has_urls', 'has_mentions']

maxpert） 是一位 DooPHP的 贡 献者和 微框架 MiMViC的创始 人。 他在 Bumpin 社交媒体担任高级软件工 程师。 他解读了 NoSQL， NodeJS， 及其 他的 一些专业术语。 关注他的 博客 http://blog.creapptives.com/

PG Mag #01

10

14 PostgreSQL 9.1

新

9.1

AWESOME

特性 in

PostgreSQL

PG Mag #01

PostgreSQL 9.1 15

PostgreSQL 9.1

PostgreSQL 最新的重要版本有灰常多 的创 新和 提升！ 我们 真的难以抉择， 从 中 挑选了十个最逆天的特性， 用 逐步演示的试验秀给你看。 PostgreSQL 9.1为 你的数据服务器做出了哪些改变， 让我们 拭目 以待！

回应用户

推动最先进水平

SQL/MED Per-column collation Unlogged tables Synchronous replication Extensions KNN indexing PGXN SE PostgreSQL SSI Writeable CTE

维基一下

K nearest neighbor (KNN) indexing, serializable snapshot isolation (SSI), writeable common table expressions (wCTE) and security-enhanced (SE) PostgreSQL.

扩展数据库引擎

16 17 18 19 20 21 22 23 24 25

http://www.pgmag.org/0115

关于作者 “PostgreSQL 9.1 提供一些开源数据库中 最先进的企 业功 能 , 由 一个充满生机和 革新性、 有可靠用户的社区 支持。 PostgreSQL在云应用的建立和 运行中 找到了 良好的定位 ” , VMware的高级研发副总监 Charles Fan 这样说道。

Marc Cousin 自 1999 年起成为

PostgreSQL和 Oracle 的 DBA。 他以不同 的方式投身于 Postgres社区， 包括在 法语论坛上提供帮助和参与 PG Day France。 他在 Dalibo 担任高级 DBA一职。 PG Mag #01

16 PostgreSQL 9.1

SQL/MED SQL/MED（ 即外部数据管理） 是 SQL： 2003 标准的 一项扩展， 它为 SQL提供了扩展， 以定义允许 SQL 访问 存储在关系型数据库管理系统外部的数据的 foreign-data wrappers（ FDW)和 数据链路类型。

SQL/MED让 PostgreSQL成为 一个强 大的 企业数据 集成工具。

http://multicorn.org/

http://wiki.postgresql.org/wiki/Foreign_data_wrappers file_fdw =# CREATE EXTENSION file_fdw WITH SCHEMA extensions; \dx+ file_fdw Objects in extension "file_fdw" Object Description ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ foreign­data wrapper file_fdw function extensions.file_fdw_handler() function extensions.file_fdw_validator(text[],oid)

=# CREATE FOREIGN DATA WRAPPER file_data_wrapper ­# HANDLER extensions.file_fdw_handler; CREATE FOREIGN DATA WRAPPER

file_fdw

为什么 SQL/MED很酷？

更多细节， 请看 Robert Haas关于 SQL/MED的 博客： http: //pgmag.org/01 1 6 原文 请看 : http: //pgmag.org/01 1 0

PG Mag #01

=# CREATE SERVER file ­# FOREIGN DATA WRAPPER file_fdw ; CREATE SERVER

=# CREATE FOREIGN TABLE stats ( ­# field1 numeric, ­# field2 numeric ­# ) ­# server file options ( ­# filename '/tmp/data.csv', ­# format 'csv', ­# delimiter ';' ­# ); CREATE FOREIGN TABLE =# SELECT * from stats ; field1 | field2 ­­­­­­­­+­­­­­­­­ 0.1 | 0.2 0.2 | 0.4 0.3 | 0.9 0.4 | 1.61.6

PostgreSQL 9.1 17

Per-column collation 在多 语言的数据库中， 用户现在可以在单独在 每一列 里为字符串 设置排序规则 。 这允许真正 的多语言数据库， 没一列 文本是一中不同 的语 言， 以及对该语言的正确索引 和 排序。

=# SELECT * from (values ('élève'),('élevé'),('élever'),('Élève')) ­# as tmp order by column1; column1 ­­­­­­­­­ élevé élève Élève élever

Postgres 9.1 =# SELECT * FROM (VALUES ('élève'),('élevé'),('élever'),('Élève')) ­# AS tmp ORDER BY column1 COLLATE "fr_FR.utf8"; column1 ­­­­­­­­­ élève Élève élevé élever

=# =# ­# =# ­#

一个数据库中的 排列顺序不再唯一 =# CREATE TABLE french_messages (message TEXT COLLATE "fr_FR.utf8"); =# INSERT INTO french_messages VALUES ('élève'),('élevé'),('élever'),('Élève'); =# SELECT * FROM french_messages ORDER BY message; message ­­­­­­­­­ élève Élève élevé élever

CREATE TABLE french_messages2 (message TEXT); INSERT INTO french_messages2 SELECT * FROM french_messages, generate_series(1,100000); CREATE INDEX idx_fr_ctype ON french_messages2 (message COLLATE "fr_FR.utf8");

=# EXPLAIN SELECT * FROM french_messages2 ­# ORDER BY message; QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ Sort (cost=62134.28..63134.28 rows=400000 width=32) Sort Key: message ­> Seq Scan on french_messages2 (cost=0.00..5770.00 rows=400000 width=32) =# EXPLAIN SELECT * FROM french_messages2 ­# ORDER BY message COLLATE "fr_FR.utf8"; QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ Index Scan using idx_fr_ctype on french_messages2(cost=0.00..17139.15 rows=400000 width=8)

PG Mag #01

18 PostgreSQL 9.1

Unlogged tables 当 性能比持 更重要时 提 一种 久 ， unlogged tables 供了 提升性能的同 时将数据保管在 PostgreSQL中 的方法。 移除日志记录功能减少了 I/O开销， 可将相比于 logged tables的 性能提高倍数拉升至 10。 预计利 用 unlogged tables的方案包括网 页会话数据， 实时日志记录， ETL 和临时 /中 间 表等功 能。

对于临时数据 性能显著提升

PostgreSQL 9.1 GiST

# CREATE UNLOGGED TABLE testu (a int); CREATE TABLE

# CREATE TABLE test (a int); CREATE TABLE

# CREATE INDEX idx_test on test (a); CREATE INDEX # CREATE INDEX idx_testu on testu (a); CREATE INDEX

=# \timing Timing is on. =# INSERT INTO test SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 17601,201 ms =# INSERT INTO testu SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 3439,982 ms

$ cat test.sql INSERT INTO testu VALUES (1); CHECKPOINT INSERT INTO testu VALUES (2); $ psql ­f test.sql ; killall ­9 postgres postmaster INSERT 0 1 CHECKPOINT INSERT 0 1 postmaster: no process found

# select * from testu; a ­­­­­­­­­­­ (0 rows)

换句话说

unlogged table对于缓存数据来说非常高效， 或是对于其他任何能在崩溃时重建的东西。 PG Mag #01

PostgreSQL 9.1 19

Synchronous replication Synchronous Replication（ 同 步复制） 使多 节点 一致的高可用 性变为可能。 Synchronous Replication 支持 “2-safe（ 双保险） replication” ， 保证事务 被主服务器和从服务器同 时确认， 大幅度降低了数据 丢失的 可能性。 只有 PostgreSQL拥有事务等级同 步 复制 的 功 能， 允许用户 基于每个事务在响 应时间 和 数据安全之间做出 选择。

synchronous_standby_names = 'newcluster'

➥ ➥

pg_ctl reload

➥ ➥ ➥ ➥

一句警告 SET synchronous_commit TO local;

PG Mag #01

20 PostgreSQL 9.1

Extensions

PostgreSQL一直保持着可扩展性， 现在用户 可以利 用 EXTENSION 数 据库对象轻松创 建、 载入、 升级和 管理数十种数据库扩展中 的任何一个。

\i /usr/local/pgsql/share/contrib/pg_trgm.sql

CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema ] [ VERSION version ] [ FROM old_version ]

“ 扩展 ” 一下 作为 一个开发者， 为了 将你的 代码 包装成 extension， 你几乎不需要做新的东西。 你必须提供一个 “ 控制 文件 ” ， 且必须以 extension 的 名字命名， 以 .control为后 缀， 放在安装目录 'SHAREDIR/extension'下。 必须至 少还有一个 SQL脚本文件， 遵循 extension 的命名 格式 ——version.sql.你可以再提供一个依赖于 PGXS的 Makefile。 更多 细节请查阅 文档 ! http://pgmag.org/0120

PG Mag #01

轻松创建， 加载并管理新的 数据库特征 =# CREATE schema extensions; CREATE SCHEMA =# CREATE EXTENSION pg_trgm ­# WITH SCHEMA extensions; CREATE EXTENSION

\dx List of installed extensions \dx List of installed extensions Name | Version | Schema | Description ­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­ pg_trgm | 1.0 | extensions | ..... plpgsql | 1.0 | pg_catalog | .....

(2 rows))

PostgreSQL 9.1 21

K nearest neighbor (KNN) Indexing

KNN 索引提供了 一种避免高代价表扫描的 创 新方式。 通过 使用数学上的 “distance” 来索引 和 搜索， 他们加 强了 PostgreSQL的查询 能力 。 这些索引 可以用来改善通常的 文本搜索， 文本相似度搜索， 地理空间定位比较和其他查询。 文本搜索索引 可以配成给类似 '%string%' 的查询提供支持， 无需改变任何 SQL。 PostgreSQL第一个具备 KNN 的数据库系统。

你现在可以在 “distance” 上创 建索引 以 获得更快的定位和 文本

搜索查询

SELECT show_trgm('hello'); show_trgm ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ {" h"," he",ell,hel,llo,"lo "}

CREATE TABLE test_trgm (text_data text); CREATE INDEX test_trgm_idx ON test_trgm USING gist (text_data extensions.gist_trgm_ops);

SELECT text_data, text_data <­> 'hello' FROM test_trgm ORDER BY text_data <­> 'hello' LIMIT 2;

SELECT text_data FROM test_trgm WHERE text_data LIKE '%hello%';

SELECT text_data, similarity(text_data, 'hello') FROM test_trgm WHERE text_data % 'hello' ORDER BY similarity(text_data, 'hello') LIMIT 2;

PG Mag #01

22 PostgreSQL 9.1

PGXN PostgreSQL Extension Network (PGXN) 是一种 开源 PostgreSQL扩展库的中央分布系统。 它 的灵感 源自 Comprehensive Perl Archive Network (CPAN) .

新站点 PGXN.org为贡献和 下载扩展提供了一个资源 库。 PostgreSQL 9.1 PGXN

$ easy_install pgxnclient Searching for pgxnclient ...

Best match: pgxnclient 0.2.1 Processing pgxnclient­0.2.1­py2.6.egg ...

Installed pgxnclient­0.2.1­py2.6.egg

$ pgxn install pair INFO: best version: pair 0.1.3 INFO: saving /tmp/tmpezwyEO/pair­0.1.3.zip INFO: unpacking: /tmp/tmpezwyEO/pair­0.1.3.zip INFO: building extension ...

INFO: installing extension [sudo] password for piro: /bin/mkdir ­p '/usr/local/pg91b1/share/postgresql/extension' ...

$ pgxn load ­d mydb pair INFO: best version: pair 0.1.3 CREATE EXTENSION

$ pgxn search pair pair 0.1.3 ... Usage There are two ways to construct key/value *pairs*: Via the *pair*() function: % SELECT *pair*('foo', 'bar'); *pair* ­­­­­­­­­­­­ (foo,bar) Or by using the ~> operator: % SELECT 'foo' ~> 'bar'; *pair*... semver 0.2.2 *pair* | 0.1.0 | Key/value *pair* data type Note that "0.35.0b1" is less than "0.35.0", as required by the specification. Use ORDER BY to get more of a feel for semantic version ordering rules: SELECT...

PG Mag #01

PostgreSQL 9.1 23

SE Postgres

Security enhanced (SE) Postgres是为军事化级别 的数据存储设计的。 通过集成了对 Security enhanced Linux (SE Linux) 的全方位支持， 它允许 强制执行访问控制。 SE Linux是一个 NSA项目 ， 他对 Linux内 核进行了 一些安全相 关功 能的修改。 它 已经被 一些主流的 Linux版本认可并纳入， 包括 Red Hat, CentOS, Debian和 Ubuntu 。 但绝不仅限于此。 PostgreSQL 是唯一紧密结合 SE Linux的数据库系

统。

SE PostgreSQL

1

用 SE Postgres你 可以部署军事化 级别的安全以及 强制执行访问控制。

2

3

了解更多

PG Mag #01

24 PostgreSQL 9.1

Serializable snapshot isolation 这个新功能允许用户 在数据库中 无阻地执行 自定义任意复杂度的业务规则， 通过自 动 检测 SQL事务运行中存在的竞态条件。 这种 功 能目 前仅存在于 PostgreSQL中 。

SSI 无阻保持并发事务连 贯， 采用了真正的序列化功

能。

session 1 session 2 begin; update dots set color = 'black' where color = 'white'; begin; update dots set color = 'white' where color = 'black'; At this point one transaction or the other is doomed to fail. commit; First commit wins. select * from dots order by id; id | color ­­­­+­­­­­­­ 1 | white 2 | white 3 | white (3 rows) This one ran as if by itself. commit; ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Cancelled on identification as a pivot, during commit attempt.

HINT: The transaction might succeed if retried.

A serialization failure. We roll back and try again. rollback; begin; update dots set color = 'black' where color = 'white'; commit; No concurrent transaction to interfere. select * from dots order by id; id | color ­­­­+­­­­­­­ 1 | black 2 | black 3 | black (3 rows)

更多例子

This transaction ran by itself, after the other.

PG Mag #01

http: //wiki.postgresql.org/wiki/SSI

PostgreSQL 9.1 25

Writeable common table expressions

这个功能（ 也成 wCTE） 支持数据的关系型整 合， 通过允许你在一个语句中 更新多 条级联的 有关的记录。 通过利用 一个查询的结果去执行 另 一个查询 ， 你可以递归 地、 逐层地， 更有创 造力地更新。 PostgreSQL提供了当 前该项 SQL功 能最完整和 最灵活的实现。

CREATE TABLE old_text_data (text_data text); WITH deleted AS ( DELETE FROM test_trgm WHERE text_data like '%hello%' RETURNING text_data ) INSERT INTO old_text_data SELECT * FROM deleted;

wCTE 在一次查询中执行复 杂的多阶段数据更新

WITH deleted_xtns AS ( DELETE FROM pgbench_history WHERE bid = 4 and tid = 9 RETURNING * ), deleted_per_account AS ( SELECT aid, sum(delta) as baldiff FROM deleted_xtns GROUP BY 1 ), accounts_rebalanced as ( UPDATE pgbench_accounts SET abalance = abalance ­ baldiff FROM deleted_per_account WHERE deleted_per_account.aid = pgbench_accounts.aid RETURNING deleted_per_account.aid, pgbench_accounts.bid, baldiff ), branch_adjustment as ( SELECT bid, SUM(baldiff) as branchdiff FROM accounts_rebalanced GROUP BY bid ) UPDATE pgbench_branches SET bbalance = bbalance ­ branchdiff FROM branch_adjustment WHERE branch_adjustment.bid = pgbench_branches.bid RETURNING branch_adjustment.bid,branchdiff,bbalance;

PG Mag #01

26

社 区 观点

把 PostgreSQL 开源计划中 的票子

砸在新特性的开发上

开源技术的发展日新月异。 多数情况下 , 自由 软件项目 在启动时没有或是只 有很少的资金。 在开源环境中， 钱不是实现一个好想法的必要条件。 然而， 当 项目 逐渐成长并取得商业成功时， 将能筹集到 更多的资金直接投入到 新特 性的开发上。

PG Mag #01

社 区 观点

“ 关键在于为众开发者和公 司持续开发 PostgreSQL找 到筹资的多条途径。 ”

27

有关作者 Joshua Drake (@Linuxpoet)

是 Command Prompt的创始 人， 北美最早投身于 PostgreSQL的支持供应 商。 1997 年至今， 他一直开发、 支持、 部署 并倡 导 PostgreSQL。

PG Mag #01

期待 9.2

28

级

联

流

复

制

PostgreSQL内 置的 复制 系统非常简约 和可靠。 不过目 前为 止， 但它 缺少一个 重要功能： 级联复制 。 在 PostgreSQL9.2中， 你将可以设计精细的复制 集群。

Cascading replication feature for streaming log­based replication. Standby servers can now have WALSender processes, which can work with either WALReceiver or archive_commands to pass data. Fully updated docs, including new conceptual terms of sending server, upstream and downstream servers. WALSenders is terminated when promoted to master.

Fujii Masao, review, rework and doc rewrite by Simon Riggs

=$ pg_ctl ­D master start server starting =$ psql ­p 4001 ­d postgres ­c "select version()" version ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ PostgreSQL 9.2devel on x86_64­unknown­linux­gnu, compiled by gcc­4.5.real (Ubuntu/Linaro 4.5.2­ 8ubuntu4) 4.5.2, 64­bit (1 row)

注意事项

=$ psql ­p 4001 ­d postgres ­c "select pg_start_backup('whatever')" pg_start_backup ­­­­­­­­­­­­­­­­­ 0/2000020 (1 row) =$ rsync ­a master/ slave/ =$ psql ­p 4001 ­d postgres ­c "select pg_stop_backup()" NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ­­­­­­­­­­­­­­­­ 0/20000D8 (1 row)

=$ mkdir master =$ initdb ­D master ... =$ vim master/postgresql.conf

port = 4001 wal_level = hot_standby checkpoint_segments = 20 archive_mode = on archive_command = '/bin/true' max_wal_senders = 3 wal_keep_segments = 100 logging_collector = on log_checkpoints = on log_connections = on log_line_prefix = '%m %r %u %d %p: '

=$ rm ­f slave/pg_xlog/???????????????????????? slave/pg_xlog/archive_status/* slave/pg_log/* slave/postmaster.pid =$ vim slave/postgresql.conf

# TYPE local local host

DATABASE replication all all

PG Mag #01

USER all all all

ADDRESS 127.0.0.1/32

METHOD trust trust trust

port = 4002 hot_standby = on

期待 9.2 29

关于本文 restore_command = '/bin/false' standby_mode = 'on' primary_conninfo = 'port=4001 user=depesz' trigger_file = '/tmp/slave.finish.recovery'

=$ pg_ctl ­D slave start server starting =$ head ­n 1 slave/postmaster.pid | xargs ­IPG USER PID %CPU %MEM VSZ RSS TTY depesz 13082 1.5 0.0 66484 7492 pts/3 depesz 13083 0.0 0.0 26136 716 ? depesz 13084 0.0 0.0 66556 1428 ? depesz 13087 2.7 0.0 81504 3064 ? depesz 13091 0.0 0.0 66484 1012 ? depesz 13092 0.0 0.0 26132 896 ?

http://pgmag.org/0134

ps uwf ­p PG ­­ppid PG STAT START TIME COMMAND S 12:51 0:00 /home/pgdba/work/bin/postgres ­D slave Ss 12:51 0:00 \_ postgres: logger process Ss 12:51 0:00 \_ postgres: startup process recovering 000000010000000000000006 Ss 12:51 0:00 \_ postgres: wal receiver process streaming 0/6000078 Ss 12:51 0:00 \_ postgres: writer process Ss 12:51 0:00 \_ postgres: stats collector process

=$ head ­n 1 master/postmaster.pid | xargs ­IPG ps uwf ­p PG ­­ppid PG USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND depesz 12981 0.2 0.0 66456 7520 pts/3 S 12:50 0:00 /home/pgdba/work/bin/postgres ­D master depesz 12982 0.0 0.0 26140 724 ?

Ss 12:50 0:00 \_ postgres: logger process depesz 12984 0.0 0.0 66456 1016 ?

Ss 12:50 0:00 \_ postgres: writer process depesz 12985 0.0 0.0 66456 1012 ?

Ss 12:50 0:00 \_ postgres: wal writer process depesz 12986 0.0 0.0 67296 2096 ?

Ss 12:50 0:00 \_ postgres: autovacuum launcher process depesz 12987 0.0 0.0 26136 732 ?

Ss 12:50 0:00 \_ postgres: archiver process depesz 12988 0.0 0.0 26136 1040 ?

Ss 12:50 0:00 \_ postgres: stats collector process depesz 13088 0.3 0.0 67428 2480 ?

Ss 12:51 0:00 \_ postgres: wal sender process depesz [local] streaming 0/6000078

注意事项 关于作者 =$ psql ­p 4001 ­d postgres ­c “create table i (x int4)”; psql ­p 4002 ­d postgres ­c '\d i'

Hubert “Depesz” Lubaczewski

CREATE TABLE Table "public.i" Column | Type | Modifiers ­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­ x | integer |

如何在家里做这些测试 ? =$ pg_ctl ­D slave waiting for server server stopped =$ rsync ­a slave/ =$ rsync ­a slave/ =$ pg_ctl ­D slave server starting

stop to shut down.... done slave2/ slave3/ start

http://wiki.postgresql.org/wiki/HowToBetaTest

PG Mag #01

30 =$ =$ =$ =$ =$

期待 9.2 perl perl perl perl perl

­pi ­pi ­pi ­pi ­pi

­e ­e ­e ­e ­e

's/port = 4002/port = 4003/' slave2/postgresql.co 's/port = 4002/port = 4004/' slave3/postgresql.conf 's/port=4001/port=4002/' slave{2,3}/recovery.conf 's/slave.finish.recovery/slave2.finish.recovery/' slave2/recovery.conf 's/slave.finish.recovery/slave3.finish.recovery/' slave3/recovery.conf

=$ for a in slave2 slave3; do pg_ctl ­D $a/ start; done server starting server starting =$ head ­n 1 ­q */*.pid | USER PID %CPU %MEM depesz 14031 0.0 0.0 depesz 14032 0.0 0.0 depesz 14033 0.0 0.0 000000010000000000000006 depesz 14063 0.0 0.0 0/6012ED0 depesz 14069 0.0 0.0 depesz 14070 0.0 0.0 depesz 14026 0.0 0.0 depesz 14042 0.0 0.0 depesz 14043 0.0 0.0 000000010000000000000006 depesz 14067 0.0 0.0 0/6012ED0 depesz 14071 0.0 0.0 depesz 14072 0.0 0.0 depesz 14021 0.0 0.0 depesz 14037 0.0 0.0 depesz 14038 0.0 0.0 000000010000000000000006 depesz 14048 0.0 0.0 depesz 14050 0.0 0.0 depesz 14052 0.0 0.0 0/6012ED0 depesz 14064 0.0 0.0 streaming 0/6012ED0 depesz 14068 0.0 0.0 streaming 0/6012ED0 depesz 12981 0.0 0.0 depesz 12982 0.0 0.0 depesz 12984 0.0 0.0 depesz 12985 0.0 0.0 depesz 12986 0.0 0.0 depesz 12987 0.0 0.0 depesz 12988 0.0 0.0 depesz 14053 0.0 0.0 streaming 0/6012ED0

xargs ­IPG echo "­p PG ­­ppid PG" VSZ RSS TTY STAT START 66488 7496 pts/3 S 13:03 26140 720 ?

Ss 13:03 66556 1400 ?

Ss 13:03 79456

2148 ?

Ss

66488 26136 66492 26144 66560

1532 900 7496 720 1400

Ss Ss S Ss Ss

79460

2148 ?

Ss

66492 26140 66488 26140 66560

1532 900 7528 724 1572

Ss Ss S Ss Ss

13:03 13:03 13:03 13:03 13:03

66488 1536 ?

26136 904 ?

79460 2136 ?

Ss Ss Ss

13:03 13:03 13:03

0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 \_ postgres: wal receiver process streaming

67332

2476 ?

Ss

13:03

0:00

\_ postgres: wal sender process depesz [local]

67452

2476 ?

Ss

13:03

0:00

\_ postgres: wal sender process depesz [local]

S Ss Ss Ss Ss Ss Ss Ss

12:50 12:50 12:50 12:50 12:50 12:50 12:50 13:03

0:00 /home/pgdba/work/bin/postgres ­D master 0:00 \_ postgres: logger process 0:00 \_ postgres: writer process 0:00 \_ postgres: wal writer process 0:00 \_ postgres: autovacuum launcher process 0:00 \_ postgres: archiver process 0:00 \_ postgres: stats collector process 0:00 \_ postgres: wal sender process depesz [local]

? ? pts/3 ? ?

? ? pts/3 ? ?

66456 7524 pts/3 26140 724 ?

66456 1780 ?

66456 1012 ?

67296 2156 ?

26136 732 ?

26136 1040 ?

67444 2520 ?

13:03

| xargs ps uwf TIME COMMAND 0:00 /home/pgdba/work/bin/postgres ­D slave3 0:00 \_ postgres: logger process 0:00 \_ postgres: startup process recovering

13:03 13:03 13:03 13:03 13:03 13:03

0:00

streaming

0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 /home/pgdba/work/bin/postgres ­D slave2 0:00 \_ postgres: logger process 0:00 \_ postgres: startup process recovering 0:00

\_ postgres: wal receiver process

streaming

0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 /home/pgdba/work/bin/postgres ­D slave 0:00 \_ postgres: logger process 0:00 \_ postgres: startup process recovering

Picture PG Mag #01

\_ postgres: wal receiver process

期待 9.2 31

=$ for port in 4002 4003 4004 do echo "port=$port" psql ­p $port ­d postgres ­c "select * from i" done port=4002 x ­­­­­ 123 (1 row)

=$ psql ­d postgres ­p 4001 ­c \ 'insert into i(x) values (123)' for port in 4002 4003 4004 do echo "port=$port" psql ­p $port ­d postgres ­c \ "select * from i" done INSERT 0 1 port=4002 x ­­­ (0 rows)

port=4003 x ­­­­­ 123 (1 row) port=4004 x ­­­­­ 123 (1 row)

port=4003 x ­­­ (0 rows) port=4004 x ­­­ (0 rows)

=$ touch /tmp/slave.finish.recovery; sleep 5; head USER PID %CPU %MEM VSZ RSS TTY depesz 14896 0.1 0.0 66488 7524 pts/3 depesz 14897 0.0 0.0 26140 720 ? depesz 14898 0.0 0.0 66556 1696 ?

000000010000000000000006 depesz 14901 0.0 0.0 66488 1276 ? depesz 14902 0.0 0.0 26136 900 ? depesz 14883 0.1 0.0 66492 7528 pts/3 depesz 14885 0.0 0.0 26144 724 ? depesz 14886 0.0 0.0 66560 1700 ?

000000010000000000000006 depesz 14890 0.0 0.0 66492 1280 ? depesz 14891 0.0 0.0 26140 904 ? depesz 14021 0.0 0.0 66488 7528 pts/3 depesz 14037 0.0 0.0 26140 724 ? depesz 14048 0.0 0.0 66488 1780 ? depesz 14050 0.0 0.0 26136 1032 ? depesz 15018 0.0 0.0 66488 1016 ? depesz 15019 0.0 0.0 67320 2100 ? depesz 15020 0.0 0.0 26136 912 ?

00000002.history depesz 12981 0.0 0.0 66456 7524 pts/3 depesz 12982 0.0 0.0 26140 724 ? depesz 12984 0.0 0.0 66456 1780 ? depesz 12985 0.0 0.0 66456 1012 ? depesz 12986 0.0 0.0 67296 2164 ? depesz 12987 0.0 0.0 26136 732 ? depesz 12988 0.0 0.0 26136 1040 ?

­n 1 ­q */*.pid | xargs ­IPG echo "­p PG ­­ppid PG" | xargs ps uwf STAT START TIME COMMAND S 13:18 0:00 /home/pgdba/work/bin/postgres ­D slave3 Ss 13:18 0:00 \_ postgres: logger process Ss 13:18 0:00 \_ postgres: startup process waiting for Ss Ss S Ss Ss

13:18 13:18 13:18 13:18 13:18

0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 /home/pgdba/work/bin/postgres ­D slave2 0:00 \_ postgres: logger process 0:00 \_ postgres: startup process waiting for

Ss Ss S Ss Ss Ss Ss Ss Ss

13:18 13:18 13:03 13:03 13:03 13:03 13:20 13:20 13:20

0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 /home/pgdba/work/bin/postgres ­D slave 0:00 \_ postgres: logger process 0:00 \_ postgres: writer process 0:00 \_ postgres: stats collector process 0:00 \_ postgres: wal writer process 0:00 \_ postgres: autovacuum launcher process 0:00 \_ postgres: archiver process last was

S Ss Ss Ss Ss Ss Ss

12:50 12:50 12:50 12:50 12:50 12:50 12:50

0:00 /home/pgdba/work/bin/postgres ­D master 0:00 \_ postgres: logger process 0:00 \_ postgres: writer process 0:00 \_ postgres: wal writer process 0:00 \_ postgres: autovacuum launcher process 0:00 \_ postgres: archiver process 0:00 \_ postgres: stats collector process

2011­07­26 13:26:41.483 CEST 16318: FATAL: timeline 2 of the primary does not match recovery target timeline 1

PG Mag #01

32

技巧分享

大象与狮子

苹果最近发布的 Lion和 Lion Server, 一个显而易见的缺席者就是 MySQL, 随后我们发现取而 代之的正是 PostgreSQL。 在本篇文章里， 我们 将看一看如何连接到 Lion Server’ s内 置的 PostgreSQL服务。 我们 将聚焦于两大流行管理工具： 一个叫作 pgAdmin 的应用程序和 一个被 称为 PgPhpAdmin 的网页服务。

如何使用 pgAdmin…

sudo serveradmin fullstatus postgres

sudo serveradmin start postgres

sudo serveradmin list

/System/Library/LaunchDaemons/org.postgresql.postgre s.plist

<string>listen_addresses=</string>

<string>listen_addresses=127.0.0.1</string>

PG Mag #01

技巧分享

33

sudo serveradmin stop postgres sudo serveradmin start postgres

已知问题

如 果你遇到 权限问题或是连接 pg_hba.conf文件的 问 题， 你可能需要修改 /private/var/下 pgsql 文件夹的权 限 ——获取 pgsql 文件夹的信息并为 当 前你的管理用户 添加访问 权限。

关于本文 http://pgmag.org/0132

下载 http://www.pgadmin.org/ http://phppgadmin.sourceforge.net/

PG Mag #01

34

技巧分享

… 或者 phpPgAdmin phpPgAdmin

/Library/Server/Web/Data/Sites/Default/

http://localhost/phppgadmin/

有关作者 Simon Merrick(@Mactasia) 在过去 7 年里从 事于 IT教育工作， 同 时提供苹果咨询服务。 他经 常为他的 Mactasia 网站 (mactasia.co.uk)在博 客上更新 “how to” 论文。

PG Mag #01

Next 35

Contribute How to

This magazine is a community-driven initiative. You can help us in various ways!!

Translators

You like the magazine, but you'd like to read it in your own language? Don't worry, this is our next challenge! So far we have 4 translating teams coming off the ground: Spanish, Portuguese, French and Chinese. For now, there's no translation available but hopefully we'll have something to present by the end of the year. If you want to help one of these teams, check out our website and contact the team leader. If you want to create a new team for another language, please send us a message at contact@pgmag.org. http://pgmag.org/Contribute#translating_an_issue

Writers

Do you have something to say about PostgreSQL? As article?

An tutorial? Some insights? Just send us your text at articles@pgmag.org. Our next issue will talk about many things, including PostGIS 2.0 and PostgreSQL 9.2… The deadline is July 24th, 2012.

Copy Editors

Photo Credits

Front: © claudiaveja (fotolia) / p4: © ITPUG / p6 : © wiccked (Flickr)¹ / p8: © MagnusHagander / p11: © ThomasVondra (Flickr) / p1 2: © r_leontiev (flickr) / p16: © andrechinn (Flickr)² / p1 7: © ilovememphis (Flickr) ³ / p18: © DanBrady (Flickr)² / p19: © boklm (Flickr) ² / p20: © / p21: © CarbonNYC (Flickr) ² / p22: / p23: © SmSm (Flickr) ² / p24: / p 25: © QUOI Media Group (Flickr) ⁴ / p26: © DMM Photography Art (fotolia) / p27: © remik44992 (fotolia) / p30: © droolcup (Flickr)⁵ / p32-34 : © SimonMerrick / P34 : © Silvercupcake1

We're looking for editors to help us improve the formatting, style, and accuracy of the articles we receive. Typically, this involves correcting spelling, avoiding jargon, cutting article when they're long and ensuring that to the text adheres style guidelines. This job is probably the most interesting part of the magazine as you need to be both an experienced PostgreSQL DBA and a skilled wordsmith!

Distribute PG Mag

If you organize an event or meeting dedicated to PostgreSQL, please contact us. We will try to send you free copies of the magazine. You can also get the PDF version here: http://pgmag.org/01/download

¹ : Creative Commons BY-NC-SA ² : Creative Commons BY ³ : Creative Commons BY-ND ⁴ : Creative Commons BY-SA ⁵ : Creative Commons BY-NC

PG Mag #01

www. pg m a g . org