PostgreSQL by Tony Jian

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 中文杂志

议事日程

消息发布

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

10 大强悍新特性

期待 9.2

社区观点

人物专访

8 Stephan Kaltenbrunner

NoSQL ?

技巧分享

Upcoming Events

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

读者问答

网读器页需主要管答复：我想你可能是在讨论有关 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

消息

PostGIS 2.0 出了！

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

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

PG Mag #01

发

布

Postgres-XC 1.0 Beta1 发布了

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

EnterpriseDB启动云数据库

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

产品新闻 7

PGXN Client 1.0

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

Skytools 3.0

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

VMware结合 PostgreSQL扩展 vFabric

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

PostgreSQL加入谷歌夏日代码活动

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发布了

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

Postgres Enterprise Manager v2.1

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

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

PG Mag #01

人物专访 PG Mag #01

人物专访

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

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

最想看到的特性是什么？

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

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 foreigndata 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

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 pgxnclient0.2.1py2.6.egg ... Installed pgxnclient0.2.1py2.6.egg

$ pgxn install pair INFO: best version: pair 0.1.3 INFO: saving /tmp/tmpezwyEO/pair0.1.3.zip INFO: unpacking: /tmp/tmpezwyEO/pair0.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

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

了解更多

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

社区观点

把 PostgreSQL 开源计划中的票子

砸在新特性的开发上

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

PG Mag #01

社区观点

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

有关作者 Joshua Drake (@Linuxpoet)

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

PG Mag #01

期待 9.2

级

联

流

复

制

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

Cascading replication feature for streaming logbased 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_64unknownlinuxgnu, compiled by gcc4.5.real (Ubuntu/Linaro 4.5.2 8ubuntu4) 4.5.2, 64bit (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 ?

66488 26136 66492 26144 66560

1532 900 7496 720 1400

Ss Ss S Ss Ss

79460

2148 ?

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 ?

13:03

0:00

\_ postgres: wal sender process depesz [local]

67452

2476 ?

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 ? ?

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

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

PG Mag #01

技巧分享

大象与狮子

苹果最近发布的 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

技巧分享

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

技巧分享

… 或者 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