【赢IPAD+1000元】PostgreSQL的互联网+特性如何跟业务更好的结合｜征文活动

Yem凌 · 发表于 2015-6-3 15:50

获奖名单已公布，请移驾至：http://www.itpub.net/thread-1926938-1-1.html

PostgreSQL在NoSQL的兼容、GIS插件支持以及处理复杂查询等特性在关系型数据库中表现的相当出色。

6月1日起阿里云RDS也支持了PostgreSQL引擎，又在运维成本、硬件投入上降低了大家使用PostgreSQL的门槛。

今天邀请大家从NoSQL兼容、GIS应用以及大规模计算上说说PostgreSQL是如何跟自己业务结合的。最高可获得IPAD一台以及阿里云RDS 1000元的免费体验券

参与方式：

回帖：我的分享（200字以上）+阿里云账号

ipad mini和千元大奖等着你哦！

奖项设置：

一等奖（1名）：IPAD mini 16G一台+1000元RDS新购代金券

二等奖（10名）：500元RDS新购代金券

三等奖（100名）：100元RDS新购代金券

活动时间：

1）征文：6月3日-6月18日

2）评审：6月19日-6月20日

3）颁奖：6月21日以后5个工作日内

活动要求：

1）参加用户需要留下阿里云注册账号，不留阿里云账号的无法发送代金券，视为自动放弃

2）分享要求真实，是自己在使用PostgreSQL过程中行业或者业务场景结合的真实描述，内容要求200字以上。

其中，一等奖和二等奖由阿里云数据库专家团进行内容评审得出（活动结束后会给出获奖理由），三等奖会从符合要求的用户中随机抽取。

3）如有作假，取消获奖资格

4）活动解释权归阿里云所有

阿里云RDS支持PostgreSQL引擎

邀您体验 RDS for PostgreSQL 服务：http://click.aliyun.com/m/1204/

liu_qingwei2008 · 发表于 2015-6-3 18:55

zhichi.

daizyflower · 发表于 2015-6-4 10:19

stay_sun · 发表于 2015-6-4 11:38

本帖最后由 stay_sun 于 2015-6-4 11:40 编辑

2014年8月份来到新的公司面试时候说的数据库是postgresql，对于postgresql的了解只在跟老师聊天时候说过。
他在选免费数据库的时候回选择pg，因为pg的设计跟oracle 最像。（我原来学习的是oracle）作为一个队新技术很痴迷的人。基本上pg的基金会ibm投了很多的钱。为了大家选择小型数据库时候放弃oracle
我来到了这家公司。想对新的数据库拼下。
来到公司后的第一个任务是了解postgresql，
简单介绍下
优点

PostgreSQL 是世界上可以获得的最先进的开放源码的数据库系统，它提供了多版本并行控制，支持几乎所有 SQL 构件(包括子查询，事务和用户定义类型和函数)，并且可以获得非常广阔范围的(开发)语言绑定 (包括 C，C++，Java，perl，tcl，和 python)。具体的优点特性如下：
　　1. PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99，是目前世界上支持最丰富的数据类型的数据库。
2. PostgreSQL 是全功能的自由软件数据库，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。
　　3. PostgreSQL 采用的是比较经典的 C/S (client/server)结构，也就是一个客户端对应一个服务器端守护进程的模式，这个守护进程分析客户端来的查询请求，生成规划树，进行数据检索并最终把结果格式化输出后返回给客户端。
　　4. PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。
　 5. 很多 DBMS 产品都是 PostgreSQL 的衍生品，比如 Greenplum, Aster Data nCluster, Netezza 等等。
　　6. PostgreSQL 的创新仍在继续。
　　7. 无法保证 Oracle 会在 MySQL 项目上持续加强投入力度，特别是 Oracle 的反垄断承诺将在2014年到期。 Postgresql 是基于完全开源协议 BSD

缺点

   1.关于mvcc 的多版本控制。会生成很多个版本。定期要清理
   2.pg的分布式集群。bug很多实用需要谨慎
   3.pg的中文文档和环境太少了。人才也太少了。
   任何的数据库都不是完美的，他都有实用环境。到底我们应该在什么时候使用postgresql呢。

了解这个东西很简单的，也很快，作为一个原来做oracle 没感觉出什么东西

集群

选型数据库集群，为了满足业务的需要数据库的高可用性，公司要做个数据库集群，主要任务是高可用，读写分离。

基本上就这个五个

一 pgpool 相当于增加了中间件管理负载均衡

具有一下特性:

* Connection Pooling(连接池管理),

* Replication(备份),

* Load Balance(负载均衡),

* Limiting Exceeding Connection(连接数限制)

* Parallel Query(并发查询)

BSD licensed, 不支持windows.

一个异步的主从复制系统，基于PostgreSQL,不能用于热备互主分发器进行负载均衡

二 Slony

Slony 是一种异步的主从(master-slave)数据库复制方案，支持一主多从结构,支持cascading。

该解决方案适用于备份(非热备)。 PostgreSQL 9.0提供异步的复制方案, streaming replication

三 GridSQL

GridSQL是一个基于java的 shared nothing集群系统，包含加速查询的负载均衡机制, Designed for Parallel Querying,基于key hash.

无心跳检测和灾备功能。

四 PGCluster

PGCluster是一个多主数据库同步复制工具，基于shared-nothing架构。

可以用于热备。

BSD licensed.

PGCluster的主要特性是防止数据丢失，同时也提供读操作负载均衡，但是实际应用中实施不多。

PGCluster intended features:

五lproxy

一个数据库分页系统，实现基于一种pl语言设计。

对于数据操作需要使用其pl语言，pl类似于SQL.

支持水平分片，支持基于查询的负载均衡

总结

最后根据官方的选择。选择了pgpool 因为pgpool postgresql 官网推荐的集群件支持高可用读写分离。

然后就是我痛苦的经历下载官方的支持支持的编译包，按照德哥的文档，baidu的文档，谷歌的文档安装最后折腾一个星期没有安装上。

各种报错。复杂的不行。最后去官方下载了 rpm的安装包读了下bash才安装上。

然后我做的就是在公司生产环境中安装新的pgpool 也就是阿里云。发现阿里云没有共享ip 只能做自己切换。

欲哭无泪的历史已经讲完了。

总结： postgresql 数据性能不错但是中文支持力度实在不够。跟阿里大神聊过drds 也就是大约05年的时候pg不如mysql 大家都用mysql 现在中国用的太少了。

pg原生系统还是很好用的，但是集群环境坑太多。使用太复杂。建议乣轻易使用，对于pgpool的安装在我的博客里可以看大家。还是买阿里的服务吧比较简单

小尾巴鱼 · 发表于 2015-6-4 11:58

感谢大家的支持，有网友反映，阿里云账号是自己的手机号，不方便直接回贴，大家可以站短我哈，如果中奖的话，用于发奖使用。

wanzai01 · 发表于 2015-6-4 13:53

PostgreSQL使用安全指导性

可以分为如下几个方面来加固你的数据库。
一、认证安全
认证是使用数据库的第一关，如果认证不安全，你的数据库将很容易被入侵。
1. pg_hba.conf安全
配置合理的pg_hba.conf，将权限控制到最小。
例如：
任何情况下都不允许trust认证方法；
超级用户只允许从本地连接，不允许从网络连接；
将dbname+username+ip限制到最小，"授权用户"只能从"授权IP"过来连接"授权数据库"；
如果使用数据库密码认证，请务必使用md5认证方法，网络传输的密码是md5+随机字符加密后的密文。

2. 密码复杂度策略
创建用户或修改用户密码时，强制限制密码的复杂度，例如密码长度，包含数字，字母，大小写，特殊字符等，同时排除暴力破解字典中的字符串。
请参考, http://blog.163.com/digoal@126/b ... 7704020149852941586

3. 密码更换周期
使用合理的密码更换周期，创建角色时使用VALID UNTIL ‘timestamp'，同时限制密码不能重复使用，
请注意配合监控使用，及时提醒管理员和用户密码快到期了。

4. 密码存储策略
如果使用数据库密码认证，创建角色时请使用encrypted password，这样pg_shadow.passwd存储的是密码+角色名的MD5码，否则是明文。
postgres=# create role r_test unencrypted password 'hello123' login;
postgres=# select usename,passwd from pg_shadow where usename='r_test';
usename |  passwd
---------+----------
r_test  | hello123
(1 row)

postgres=# alter role r_test encrypted password 'hello123';
ALTER ROLE
postgres=# select usename,passwd from pg_shadow where usename='r_test';
usename |             passwd
---------+-------------------------------------
r_test  | md5bb0d7bef45a0530ac529e7b43943a2d1
(1 row)

postgres=# select md5('hello123r_test');
            md5
----------------------------------
bb0d7bef45a0530ac529e7b43943a2d1
(1 row)

5. 设置密码时防止密码被记录到数据库日志,history,或审计日志中.
(例如使用了readline, 堡垒机, 或者开启了log_statement)
  ~/.psql_history
  pg_log/xxx.csv
  堡垒机日志
请参考, http://blog.163.com/digoal@126/b ... 7704020149852941586

6. 外部表密码安全
回收pg_user_mappings视图的public权限，否则mapping用户可以看到user mapping下的密码。
revoke all on view pg_user_mapings from public;
7. dblink密码安全
普通用户使用dblink时，需要提供连接用户和密码，不建议使用。如果一定要用，请限制dblink目标用户在目标数据库集群的权限到最小化。

8. 如果使用外部认证，如AD域，请加固对应的认证服务。

9. 应用程序配置文件中如果需要配置用户和密码，请确保应用程序服务器的安全。防止配置文件泄露。

二、数据传输安全
确保数据传输过程的安全，即使数据被截获，也不需要担心。
1. 数据传输加密
如果你的网络是不可靠的，请使用加密传输，例如OPENSSL。
参考，http://blog.163.com/digoal@126/b ... 7040201342233131835

2. 认证过程加密
认证过程加密，指认证过程中，网络上传输的密码安全，如果使用数据库认证，请使用MD5方法(配置pg_hba.conf)。确保网络中传输的是随机码和MD5加密后的MD5。

三、数据安全
你的数据安全吗？如果你存储的敏感数据在数据库中是明文的，一旦数据库暴露，用户数据可能泄露，如何尽可能的保证泄露的数据的安全呢？
1. 字段存储加密
将敏感数据加密后存储在数据库中，即使加密数据泄露，只要加解密方法没有泄露，也是相对安全的。
加解密方法建议放在应用端实现，如果加解密在数据库端实现，用户一旦入侵数据库，更容易破解。（或者加密在数据库端实现，解密在应用程序端实现）

2. 敏感数据，跟踪并记录DML,truncate操作的undo
对于非常敏感的数据，我们应该记录对这些数据操作的UNDO，在必要时刻可以快速的回滚到误操作前。
这种方法主要是对付SQL注入，人为误操作（包括delete,update,insert,truncate的回滚）。
请参考，http://blog.163.com/digoal@126/b ... 402014728105442434/

3. 函数代码加密
如果我们将业务逻辑放在数据库函数中处理的话，肯定不想让用户看到函数的内容。对于先编译后执行的函数，例如C函数，是不需要加密的，但是，对于解释性语言函数如plpgsql，建议加密函数的内容。
目前enterprisedb有这个特性，社区版本的PostgreSQL没有这个特性。
请参考，http://blog.163.com/digoal@126/blog/static/163877040201256056352
http://www.cybertec.at/en/produc ... ed-procedure-codes/

4. 使用recycle bin插件，用户在删对象时，对象会存储在recycle bin schema下，而不会被真实删除。那么表被误删除或恶意删除后，很容易找回。(使用钩子实现)
请参考，http://blog.163.com/digoal@126/blog/static/1638770402014339374747

四、权限控制
1. 权限管理
最危险的就是最容易暴露的数据库用户，当然是应用连接数据库的账号（以下简称应用账号）。
应用账号权限越大，应用程序被攻击后破坏性就越大。
例如用户有删数据库，删表，删索引，删表空间，删SCHEMA，删函数等等这样的权限的话，危害极大。
安全建议：
1.1. 使用超级用户创建数据库，SCHEMA，应用所需的对象（如表，索引，函数）。
1.2. 创建应用账号角色。
1.3. 回收数据库，schema，language，应用对象的public权限。
revoke all on database dbname from public;
revoke all on schema sch_name from public;
revoke all on language plpgsql from public;
revoke all on table ... from public;
revoke all on function ... from public;
......

1.4. 将数据库，schema的使用权限赋予给应用账号。
grant connect on database dbname to approle;
grant usage on schema sch_name to approle;
1.5. 将应用需要访问的对象的相关权限赋予给应用账号。
例如表的select,insert,update,delete权限, 函数的execute权限等.
这样，应用账号只有对象的使用权限，没有对象的DROP，TRUNCATE，REPLACE权限，相对来说是更安全的。

2. 通过事件触发器禁止应用账号执行DDL，通过这种方法可以限制用户执行DDL，防止被攻击后，用户执行DROP或TRUNCATE删除对象或清空数据 (当然delete不带条件还是能删除数据的，需要用其他手段)。
请参考，http://blog.163.com/digoal@126/b ... 704020132131361949/

3. 防止执行不带条件的delete,update。
例如，在需要保护的表里，新增一条dummy记录，创建行触发器，当这条记录被更新或删除时，抛出异常。
对于业务上不允许执行删除操作的表，当然不需要赋予delete权限给应用账号，也就不会有这个风险。

4. 函数语言安全
建议回收函数语言的public权限，以及普通用户的权限，用户不能创建函数。执行online code。
例如：
revoke all on language plpgsql from public;
revoke all on language plpgsql from app_role;

5. 行级安全
限制普通用户只能操作表中的指定条件的记录，用于rewriter改写重写规则，普通用户只能访问满足指定条件的行。
请参考，http://blog.163.com/digoal@126/b ... 040201362402650341/

6. 创建安全策略
与行安全策略类似，但是对表的记录权限控制更加精准细致，例如数据进入表前根据行的值判断数据是否允许插入，查询时根据已经存在的记录，判断是否允许用户查询该记录。
请参考，http://blog.163.com/digoal@126/b ... 704020153984016177/

7. 对于只需要访问某些行，或某些列的需求，可以通过列权限或视图来限制应用账号的权限。

五、防恶意攻击
1. 视图攻击
用户利用PostgreSQL的优化器原理，创建成本极低的函数，在函数中获取视图限制外的隐藏内容。
如果用户没有创建函数的权限，用户就无法利用这个原理。
或者使用安全栅栏来弥补。
请参考，http://blog.163.com/digoal@126/b ... 040201361031431669/
http://blog.163.com/digoal@126/b ... 7040201431410032638

2. 防止SQL注入
应用层应该有SQL注入预防手段，例如使用简单的过滤器，使用绑定变量等手段。

3. 密码暴力破解
目前可以通过密码错误延迟认证(auth_delay)来增加暴力破解需要的时间。
请参考，http://blog.163.com/digoal@126/b ... 704020149852941586/

六、备份，容灾，恢复测试
再好的安全策略，也需要备份。
基于时间点的，块级别增量备份，是比较靠谱的。(你可以选择合适的文件系统，例如btrfs)
请参考，http://blog.163.com/digoal@126/b ... 040201451894734122/
http://blog.163.com/digoal@126/b ... 020141110105858171/

七、审计
审计功能，一般是用于排查问题的，当然也是一种举证的手段，例如你的数据库遭到暴力破坏了，证据非常重要。
这里有一些例子：
如何跟踪postgresql.conf的配置变更?
  --  worker process钩子程序的妙用.
http://blog.163.com/digoal@126/b ... 704020137624414708/
如何跟踪表中的记录被哪个用户修改或插入?
http://blog.163.com/digoal@126/b ... 040201201333830383/

使用pg_log_userqueries插件, 审计指定用户,数据库或超级用户的所有执行的SQL.
http://blog.163.com/digoal@126/b ... 402012019112218804/
使用hstore插件和触发器跟踪表的行记录变更.
http://blog.163.com/digoal@126/b ... 040201252575529358/
PostgreSQL中如何跟踪表的创建时间, 表定义的修改时间
http://blog.163.com/digoal@126/b ... 402012526105017774/
PostgreSQL 精细化审计的实施.
1. 审计指定表的INSERT, UPDATE, DELETE, TRUNCATE
2. 审计指定用户对指定表的INSERT, UPDATE, DELETE, TRUNCATE
3. 审计指定表的指定数据的INSERT, UPDATE, DELETE
4. 如何让数据库只审计成功提交的数据, 而不记录回滚事务.
http://blog.163.com/digoal@126/b ... 704020132209854525/
PostgreSQL 审计功能配置
http://blog.163.com/digoal@126/b ... 704020132208241607/

PostgreSQL 9.3 规则系统改进, 允许在规则的values中使用多次NEW, OLD.
--  使用规则跟踪数据变更, 记录新老数据.
http://blog.163.com/digoal@126/b ... 704020134915429197/
如何跟踪基于字段值为条件的行的变更,插入和删除呢?
创建触发器时when的用法, 或在触发器函数中处理. 选择效率高的.
http://blog.163.com/digoal@126/b ... 704020148178320844/
PostgreSQL数据库在上市公司重要应用中的SOX审计
http://blog.163.com/digoal@126/b ... 704020148304551659/
审计表的DDL行为, 以及哪个会话在什么时间点,通过什么IP干的.
http://blog.163.com/digoal@126/b ... 402014111194225536/
审计变更的行, 以及被变更的字段内容; 新增的行, 删除的行; 以及哪个会话在什么时间点,通过什么IP干的.
http://blog.163.com/digoal@126/b ... 402014111473644127/
pg_audit模块
http://blog.163.com/digoal@126/b ... 040201541595510867/

八、补丁
PostgreSQL社区的更新速度很快，几乎每天都会有大大小小的更新，有些可能是FIX patch，有些可能是feature，有些可能是性能提升patch，正常情况下，我们只要跟随小版本的升级就可以了，一般社区遇到比较大的安全漏洞，提交补丁后马上就会发布小版本，如果没有发布小版本，说明没有大的安全漏洞，当然你可以通过http://git.postgresql.org实时跟踪社区的动态，自行打patch。
大版本的更新，通常情况下大版本有大量的feature，如果需要使用的话，也可以更新到大的版本，但是请注意与应用有关的修改，模块的更新等。

九、外界环境安全
1. 应用程序是否安全？
2. 中间件是否安全？
3. 数据库所在操作系统是否安全？
4. 数据库所在服务器是否安全？
5. 存储安全，存储是否在安全的地方，有没有硬盘被拔掉的风险？
6. 网络安全，如机架交换机，未插网线的端口是否禁用了，是否做了MAC地址过滤或绑定？
7. 机房安全？

十、资源控制
虽然我们前面已经控制的挺好了，但是数据库还有一种风险和网络的DDOS攻击类似，大量的用户请求可以把数据库搞慢。或者大量的运算量或者IO极大的请求，也很容易把数据库搞慢。
资源控制手段举例：
控制连接数，控制活动连接数，控制SQL执行时间，控制锁等待时间，控制事务空闲时间。
另一方面，因为PostgreSQL的并发控制用到了多版本，所以当更新或删除数据时，老的版本依旧存在于数据库中，需要vacuum进程回收这些数据，目前有一个缺陷，当有长事务存在时，事务开启后产生的垃圾被视为新的垃圾，不会被回收，所以长事务容易导致数据库膨胀，太长的事务甚至可以导致数据库的xid耗尽，必须关机做vacuum freeze。请参考，http://blog.163.com/digoal@126/b ... 704020153305256157/

十一、监控
监控是DBA的眼睛，好的监控可以提前发现问题，将问题排除在发生之前。
常用监控项请参考，http://blog.163.com/digoal@126/b ... 040201412763135184/

wanzai01 · 发表于 2015-6-4 13:55

PostgreSQL 中英文分词的性能，以及带索引的情况下的插入，查询性能。
测试环境：
CPU：Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz
MEM：60G
OS：CentOS 6.x x64
PostgreSQL 9.4.1
中文分词使用 http://blog.163.com/digoal@126/b ... 040201422410175698/

性能数据汇总：
英语分词性能：~ 900万 words每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
中文分词性能：~ 400万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
英文分词+插入性能：~ 666万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
中文分词+插入性能：~ 290万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
查询性能和查询条件，数据量都有关系，没有很好的评估标准，大多数查询可以在毫秒级返回。

分词性能测试过程如下：
为了测试效果，首先要修改to_tsvector的函数稳定性，避免一次计算多次调用。
对稳定性不了解的话，介绍请参考我以前写的文章或视频。
digoal=> \df+ to_tsvector
                                                                        List of functions
Schema | Name    | Result data type | Argument data types |  Type  | Security | Volatility |  Owner | Language | Source
code |    Description
------------+-------------+------------------+---------------------+--------+----------+------------+----------+----------+---------
---------+-----------------------
pg_catalog | to_tsvector | tsvector       | regconfig, text    | normal | invoker  | immutable  | postgres | internal | to_tsvec
tor_byid | transform to tsvector
pg_catalog | to_tsvector | tsvector       | text             | normal | invoker  | stable    | postgres | internal | to_tsvec
tor    | transform to tsvector
(2 rows)
digoal=# alter function to_tsvector(regconfig,text) stable;
ALTER FUNCTION
Time: 0.632 ms

创建英语和中文的测试函数：
英文272个单词，中文包含291个字或单词.
create or replace function f_testts_en() returns void as $$
declare
begin
perform to_tsvector('english', 'Before you can use PostgreSQL you need to install it, of course. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating system distribution or because the system administrator already installed it. If that is the case, you should obtain information from the operating system documentation or your system administrator about how to access PostgreSQL.

If you are not sure whether PostgreSQL is already available or whether you can use it for your experimentation then you can install it yourself. Doing so is not hard and it can be a good exercise. PostgreSQL can be installed by any unprivileged user; no superuser (root) access is required.

If you are installing PostgreSQL yourself, then refer to Chapter 15 for instructions on installation, and return to this guide when the installation is complete. Be sure to follow closely the section about setting up the appropriate environment variables.

If your site administrator has not set things up in the default way, you might have some more work to do. For example, if the database server machine is a remote machine, you will need to set the PGHOST environment variable to the name of the database server machine. The environment variable PGPORT might also have to be set. The bottom line is this: if you try to start an application program and it complains that it cannot connect to the database, you should consult your site administrator or, if that is you, the documentation to make sure that your environment is properly set up. If you did not understand the preceding paragraph then read the next section.');
return;
end;
$$ language plpgsql strict;

分词结果：
'15':126 'access':63,113 'administr':38,59,158,242 'alreadi':19,39,73 'also':209 'applic':225 'appropri':152 'avail':74 'bottom':21
5 'cannot':232 'case':46 'chapter':125 'close':145 'complain':229 'complet':140 'connect':233 'consult':239 'cours':12 'databas':180
,201,236 'default':166 'distribut':33 'document':55,249 'either':24 'environ':153,194,205,255 'exampl':177 'exercis':101 'experiment
':83 'follow':144 'good':100 'guid':135 'hard':94 'includ':28 'inform':50 'instal':9,20,40,87,105,119,130,138 'instruct':128 'line':
216 'machin':182,186,203 'make':251 'might':169,208 'name':198 'need':7,189 'next':271 'obtain':49 'oper':31,53 'paragraph':267 'pgh
ost':193 'pgport':207 'possibl':15 'postgresql':5,17,64,71,102,120 'preced':266 'program':226 'proper':257 'read':269 'refer':123 'r
emot':185 'requir':115 'return':132 'root':112 'section':147,272 'server':181,202 'set':149,161,191,213,258 'site':23,157,241 'start
':223 'superus':111 'sure':69,142,252 'system':32,37,54,58 'thing':162 'tri':221 'understand':264 'unprivileg':108 'use':4,79 'user'
:109 'variabl':154,195,206 'way':167 'whether':70,76 'work':173
(1 row)

create or replace function f_testts_zh() returns void as $$
declare
begin
perform to_tsvector('testzhcfg','
<PostgreSQL发展历程,社区介绍,资源介绍 2小时>

<PostgreSQL SQL基础 2天>
第一天
  PostgreSQL的安装, 架构基础介绍, 如何创建和访问数据库;
  SQL语言基础(创建表, 操作表数据, 表关联查询, 聚合查询);
  SQL高级应用(视图, 外键, 事务, 窗口函数, 继承);
  SQL语义结构介绍(关键字, 常量, 操作符, 特殊字符, 注释, 操作符的优先级);
  值表达式介绍(列, 位置参数, 数组下标, field引用, 操作符调用, 函数调用, 聚合表达式, 窗口函数调用, 类型转换, collate表达式, 标量子查询, 数组构造器, 行构造器, 表达式逻辑运算规则);
  函数调用用法介绍(位置参数调用, 命名参数调用, 混合调用);
  数据定义(默认值, 约束, 系统列, 表结构和定义修改, 权限, schema, 继承, 分区, 外部数据);
第二天
  数据查询(from子句, where子句, group by having子句, 窗口子句, select子句, 结果集合操作, 排序, 限制, 位移, values子句, CET用法);
  数据类型(数字, 货币, 字符, 字节, 时间, 布尔, 枚举, 几何, 网络, 比特, 文本搜索, UUID, XML, JSON, 数组, 复合类型, 范围类型, 对象类型, 伪类型);
  函数与操作符(逻辑, 比较, 数学运算, 字符串, 字节流, 比特位, 样式匹配, 格式化, 时间, 枚举, 几何, 网络, 全文检索, XML, JSON, 序列, 条件, 数组, 范围, 聚合, 窗口, 子查询, 行与数组的比较, SRF函数, 系统信息函数, 系统管理函数, 触发器函数, 事件触发器函数);
  类型转换;
  索引用法;
  全文检索用法;
  并行控制;
  SQL性能优化;

<PostgreSQL 数据库管理 3天>
第一天
  PostgreSQL源代码安装(源码结构介绍, 配置介绍, 自定义FLAG介绍, 内核参数优化, 安装);
  数据库服务器配置(OS USER, 初始化集群, 启动数据库集群, 关闭数据库集群, 升级数据库软件, 防止欺骗, 数据封装方法, SSL, SSH隧道)
  数据库集群进程结构介绍(postmaster, logger, checkpointer, writer, wal writer, autovacuum launcher, autovacuum worker, stats collector, backend, worker);
  认证和连接(客户端认证方法介绍, 认证配置, 常用认证方法使用举例, 常见认证错误排错);
  pgAdmin III 使用介绍(安装, 使用, 调试函数, 维护数据);
  数据库配置(guc.c, 参数优先级, 参数含义, 开发参数, guc隐含参数, 如何修改参数并使之生效, 如何查看当前参数值, 如何查看参数值的范围);
  用户管理(创建角色, 角色权限管理, 角色membership管理, 角色成员权限管理);
  数据库逻辑结构(cluster, database, schema, object, field, access privilege);
  数据库物理结构(tablespace, datafile, segment, block, controlfile, xlog, archivelog);
第二天
  数据库管理(创建表空间, 创建数据库, 创建数据库模板, 数据库配置, 删除数据库, 跨数据库的数据访问);
  基于角色的对象权限管理(表, 列, 序列, 数据库, 域, 外部数据, 函数, 语言, 大对象, schema, 表空间, 类型);
  默认权限和继承权限(default privilege, inherit privilege);
  数据库安全(认证, 网络, 数据存储, 密码, 注入, 欺骗);
  数据库监控(状态监控, 趋势监控, 预警, 常用监控工具和插件nagios, zabbix, pg_statsinfo);
  多版本并发控制和垃圾回收;
  日常维护(垃圾回收, 数据重组, 索引维护, VM维护, 预防XID溢出, 日志维护);
第三天
  数据迁移(逻辑备份和还原, 增量数据迁移的方法, 异构数据迁移的方法(如oracle to postgresql));
  如何打数据库补丁;
  数据库版本升级(小版本升级方法, 大版本升级方法);
  建模与Benchmark(如何根据业务形态建立数据库测试模型, 测试工具的使用, 根据测试模型和硬件标准输出benchmark);
  数据库日志分析(错误代码介绍, 日志的筛选过滤, 日志的维护);
  数据库审计(参数层面的审计开关, 定制化审计(如触发器结合HSTORE), 审计数据的维护手段);
  本地化(字符集, collate, 编码转换, 如何防止乱码);
  数据库性能分析(OS性能分析报表, SQL性能分析报表);
  数据库巡检(如何定制巡检项, 指标, 如何分析巡检报告);');
return;
end;
$$ language plpgsql strict;

分词结果：
'access':352 'archivelog':363 'autovacuum':271,273 'backend':277 'benchmark':486,500 'block':360 'by':123 'c':309 'cet':137 'checkp
ointer':267 'cluster':347 'collate':75,530 'collector':276 'controlfile':361 'database':348 'datafile':358 'default':405 'field':63,
351 'flag':230 'from':118 'group':122 'guc':308,316 'having':124 'hstore':523 'iii':297 'inherit':407 'json':154,181 'launcher':272
'logger':266 'membership':338 'nagios':429 'object':350 'oracle':468 'os':239,537 'pg_statsinfo':431 'pgadmin':296 'postgresql':1,9,
15,215,221,470 'postmaster':265 'privilege':353,406,408 'schema':112,349,397 'segment':359 'select':127 'sql':10,23,35,43,212,540 's
rf':191 'ssh':258 'ssl':257 'stats':275 'tablespace':357 'to':469 'user':240 'uuid':152 'values':135 'vm':444 'wal':269 'where':120
'worker':274,278 'writer':268,270 'xid':447 'xlog':362 'xml':153,180 'zabbix':430 '下标':62 '业务':487 '举例':291 '乱码':534 '事件':
201 '事务':40 '二天':116,364 '介绍':5,7,19,46,57,91,226,228,231,264,284,299,506 '代码':505 '优先级':54,311 '优化':214,234 '位移':134
'位置':59,92 '使':321 '使用':290,298,301,494 '信息':194 '修改':110,319 '值':55,103,325,328 '全文检索':179,208 '关联':31 '关键字':47
'关闭':246 '内核':232 '几何':148,177 '函数':41,67,71,88,163,192,195,198,200,203,303,393 '分区':114 '分析':503,548 '列':58,106,388 '
创建':20,25,332,367,370,372 '初始化':241 '删除':377 '匹配':173 '升级':249,476,479,483 '历程':3 '参数':60,93,96,233,310,312,315,318,3
20,324,327,514 '发展':2 '含义':313 '启动':243 '命名':95 '回收':436,439 '垃圾':435,438 '域':391 '基础':11,18 '增量':458 '备份':456 '
合':156 '大':395,481 '天':12,14,218,220,452 '如':467,520 '子句':119,121,125,126,128,136 '字':170 '字符':51,143 '字符串':169 '字符集'
:529 '字节':144 '存储':414 '安全':410 '安装':16,223,235,300 '定义':101,109,229 '定制':518,545 '审计':513,516,519,524 '客户端':281 '
码':415 '对象':160,384,396 '封装':255 '小':477 '小时':8 '层面':515 '巡检':544,546,549 '工具':427 '布尔':146 '常用':287,425 '常见':29
2 '常量':48 '并发':433 '并行':210 '序列':182,389 '应用':37 '建模':485 '建立':489 '开关':517 '开发':314 '异':462 '引用':64 '形态':488
'性能':213 '性能分析':536,538,541 '成员':341 '手段':527 '打':471 '报告':550 '报表':539,542 '指标':547 '排序':132 '排错':295 '控制':
211,434 '插件':428 '搜索':151 '操作':27,131 '操作符':49,53,65,164 '数字':141 '数学':167 '数据':29,100,115,139,254,305,381,392,413,44
0,453,459,464,525 '数据库':22,216,236,244,247,250,260,306,344,354,365,371,373,375,378,380,390,409,418,472,474,490,501,512,535,543 '
据查询':117 '数组':61,80,155,184,189 '文本':150 '方法':256,283,289,461,466,480,484 '日志':449,502,507,510 '时间':145,175 '服务器':23
7 '本地化':528 '权限':111,335,342,385,402,404 '条件':183 '构':463 '构造器':81,83 '枚举':147,176 '架构':17 '查看':323,326 '查询':32,3
4,79,187 '标':77 '标准':498 '样式':172 '格式化':174 '模型':492,496 '模板':374 '欺骗':253,417 '比较':166,190 '注入':416 '注释':52 '测
试':491,495 '测试工具':493 '混合':98 '源代码':222 '源码':224 '溢出':448 '版本':432,475,478,482 '物理':355 '特殊':50 '状态':420 '生效
':322 '用户':330 '用法':90,138,207,209 '监控':419,421,423,426 '硬件':497 '社区':4 '空间':369,399 '第一':13,219 '第三':451 '筛选':508
'管理':197,217,331,336,339,343,366,386 '类型':73,140,157,159,161,162,204,400 '系统':105,193,196 '索引':206,442 '约束':104 '结合':52
2 '结构':45,108,225,263,346,356 '结果':129 '继承':42,113,403 '维护':304,437,443,445,450,511,526 '编码':531 '网络':149,178,412 '聚合'
:33,69,186 '节流':171 '范围':158,185,329 '行':82,188 '补丁':473 '表':26,28,30,107,368,387,398 '表达式':56,70,76,84 '规则':87 '视图':
38 '角色':333,334,337,340,383 '触发器':199,202,521 '认证':279,282,285,288,293,411 '访问':21,382 '语义':44 '语言':394 '语言基础':24 '
调用':66,68,72,89,94,97,99 '调试':302 '货币':142 '资源':6 '趋势':422 '跨':379 '转换':74,205,532 '软件':251 '输出':499 '迁移':454,460
,465 '过滤':509 '运算':86,168 '还原':457 '进程':262 '连接':280 '逻辑':85,165,345,455 '配置':227,238,286,307,376 '重组':441 '量子':78
'错误':294,504 '键':39 '防止':252,533 '限制':133 '隐含':317 '隧道':259 '集合':130 '集群':242,245,248,261 '预警':424 '预防':446 '高
':36 '默认':102,401
(1 row)

英文分词性能测试结果：
英语分词性能：~ 900万 words每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
postgres@db-192-168-173-33-> vi test.sql
select f_testts_en();

postgres@db-192-168-173-33-> pgbench -M prepared -n -f test.sql -P 5 -c 36 -j 36 -T 30000000
progress: 5.0 s, 33072.6 tps, lat 1.083 ms stddev 0.371
progress: 10.0 s, 33218.9 tps, lat 1.082 ms stddev 0.366
progress: 15.0 s, 33213.4 tps, lat 1.083 ms stddev 0.365
progress: 20.0 s, 33202.3 tps, lat 1.083 ms stddev 0.364
progress: 25.0 s, 33204.5 tps, lat 1.083 ms stddev 0.364
progress: 30.0 s, 33215.0 tps, lat 1.083 ms stddev 0.366
progress: 35.0 s, 33220.8 tps, lat 1.082 ms stddev 0.367
progress: 40.0 s, 33210.7 tps, lat 1.083 ms stddev 0.364
progress: 45.0 s, 33220.4 tps, lat 1.082 ms stddev 0.365
progress: 50.0 s, 33217.2 tps, lat 1.083 ms stddev 0.365
progress: 55.0 s, 33231.6 tps, lat 1.082 ms stddev 0.367
progress: 60.0 s, 33234.4 tps, lat 1.082 ms stddev 0.367
progress: 65.0 s, 33232.7 tps, lat 1.082 ms stddev 0.367
progress: 70.0 s, 33212.2 tps, lat 1.083 ms stddev 0.369
progress: 75.0 s, 33232.4 tps, lat 1.082 ms stddev 0.367
progress: 80.0 s, 33222.7 tps, lat 1.082 ms stddev 0.367
progress: 85.0 s, 33221.1 tps, lat 1.082 ms stddev 0.365
progress: 90.0 s, 33220.4 tps, lat 1.082 ms stddev 0.365
progress: 95.0 s, 33213.6 tps, lat 1.083 ms stddev 0.364
progress: 100.0 s, 33227.5 tps, lat 1.082 ms stddev 0.368
progress: 105.0 s, 33230.8 tps, lat 1.082 ms stddev 0.366
progress: 110.0 s, 33228.1 tps, lat 1.082 ms stddev 0.366

中文分词性能测试结果：
中文分词性能：~ 400万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
postgres@db-192-168-173-33-> vi test.sql
select f_testts_zh();

postgres@db-192-168-173-33-> pgbench -M prepared -n -f test.sql -P 5 -c 36 -j 36 -T 30000000
progress: 5.0 s, 13874.6 tps, lat 2.582 ms stddev 0.892
progress: 10.0 s, 13947.0 tps, lat 2.580 ms stddev 0.875
progress: 15.0 s, 13949.0 tps, lat 2.580 ms stddev 0.874
progress: 20.0 s, 13950.1 tps, lat 2.579 ms stddev 0.871
progress: 25.0 s, 13951.1 tps, lat 2.579 ms stddev 0.876
progress: 30.0 s, 13950.6 tps, lat 2.579 ms stddev 0.873
progress: 35.0 s, 13950.5 tps, lat 2.579 ms stddev 0.877
progress: 40.0 s, 13951.8 tps, lat 2.579 ms stddev 0.876
progress: 45.0 s, 13953.2 tps, lat 2.579 ms stddev 0.878
progress: 50.0 s, 13949.9 tps, lat 2.579 ms stddev 0.876
progress: 55.0 s, 13947.4 tps, lat 2.580 ms stddev 0.875
progress: 60.0 s, 13947.8 tps, lat 2.580 ms stddev 0.877
progress: 65.0 s, 13948.4 tps, lat 2.580 ms stddev 0.877
progress: 70.0 s, 13952.1 tps, lat 2.579 ms stddev 0.878
progress: 75.0 s, 13948.1 tps, lat 2.580 ms stddev 0.877
progress: 80.0 s, 13950.3 tps, lat 2.579 ms stddev 0.875
progress: 85.0 s, 13951.3 tps, lat 2.579 ms stddev 0.875
progress: 90.0 s, 13950.9 tps, lat 2.579 ms stddev 0.879
progress: 95.0 s, 13949.6 tps, lat 2.579 ms stddev 0.876
progress: 100.0 s, 13950.1 tps, lat 2.579 ms stddev 0.876
progress: 105.0 s, 13951.9 tps, lat 2.579 ms stddev 0.874
progress: 110.0 s, 13950.7 tps, lat 2.579 ms stddev 0.878

分词+插入性能测试：
使用gist或gin索引，索引的适用场景和优化手段可以参考我以前写的文章。
(静态数据考虑GIN，动态数据考虑GIST，GIN可以通过增加WORK_MEM优化动态性能)。
digoal=> create table test_ts(ts tsvector);
CREATE TABLE
digoal=> create index idx_test_ts on test_ts using gist(ts);
CREATE INDEX

create or replace function f_testts_en() returns void as $$
declare
begin
insert into test_ts values (to_tsvector('Before you can use PostgreSQL you need to install it, of course. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating system distribution or because the system administrator already installed it. If that is the case, you should obtain information from the operating system documentation or your system administrator about how to access PostgreSQL.

If you are not sure whether PostgreSQL is already available or whether you can use it for your experimentation then you can install it yourself. Doing so is not hard and it can be a good exercise. PostgreSQL can be installed by any unprivileged user; no superuser (root) access is required.

If you are installing PostgreSQL yourself, then refer to Chapter 15 for instructions on installation, and return to this guide when the installation is complete. Be sure to follow closely the section about setting up the appropriate environment variables.

If your site administrator has not set things up in the default way, you might have some more work to do. For example, if the database server machine is a remote machine, you will need to set the PGHOST environment variable to the name of the database server machine. The environment variable PGPORT might also have to be set. The bottom line is this: if you try to start an application program and it complains that it cannot connect to the database, you should consult your site administrator or, if that is you, the documentation to make sure that your environment is properly set up. If you did not understand the preceding paragraph then read the next section.'));
return;
end;
$$ language plpgsql strict;

create or replace function f_testts_zh() returns void as $$
declare
begin
insert into test_ts values ( to_tsvector('testzhcfg','
<PostgreSQL发展历程,社区介绍,资源介绍 2小时>

<PostgreSQL SQL基础 2天>
第一天
  PostgreSQL的安装, 架构基础介绍, 如何创建和访问数据库;
  SQL语言基础(创建表, 操作表数据, 表关联查询, 聚合查询);
  SQL高级应用(视图, 外键, 事务, 窗口函数, 继承);
  SQL语义结构介绍(关键字, 常量, 操作符, 特殊字符, 注释, 操作符的优先级);
  值表达式介绍(列, 位置参数, 数组下标, field引用, 操作符调用, 函数调用, 聚合表达式, 窗口函数调用, 类型转换, collate表达式, 标量子查询, 数组构造器, 行构造器, 表达式逻辑运算规则);
  函数调用用法介绍(位置参数调用, 命名参数调用, 混合调用);
  数据定义(默认值, 约束, 系统列, 表结构和定义修改, 权限, schema, 继承, 分区, 外部数据);
第二天
  数据查询(from子句, where子句, group by having子句, 窗口子句, select子句, 结果集合操作, 排序, 限制, 位移, values子句, CET用法);
  数据类型(数字, 货币, 字符, 字节, 时间, 布尔, 枚举, 几何, 网络, 比特, 文本搜索, UUID, XML, JSON, 数组, 复合类型, 范围类型, 对象类型, 伪类型);
  函数与操作符(逻辑, 比较, 数学运算, 字符串, 字节流, 比特位, 样式匹配, 格式化, 时间, 枚举, 几何, 网络, 全文检索, XML, JSON, 序列, 条件, 数组, 范围, 聚合, 窗口, 子查询, 行与数组的比较, SRF函数, 系统信息函数, 系统管理函数, 触发器函数, 事件触发器函数);
  类型转换;
  索引用法;
  全文检索用法;
  并行控制;
  SQL性能优化;

<PostgreSQL 数据库管理 3天>
第一天
  PostgreSQL源代码安装(源码结构介绍, 配置介绍, 自定义FLAG介绍, 内核参数优化, 安装);
  数据库服务器配置(OS USER, 初始化集群, 启动数据库集群, 关闭数据库集群, 升级数据库软件, 防止欺骗, 数据封装方法, SSL, SSH隧道)
  数据库集群进程结构介绍(postmaster, logger, checkpointer, writer, wal writer, autovacuum launcher, autovacuum worker, stats collector, backend, worker);
  认证和连接(客户端认证方法介绍, 认证配置, 常用认证方法使用举例, 常见认证错误排错);
  pgAdmin III 使用介绍(安装, 使用, 调试函数, 维护数据);
  数据库配置(guc.c, 参数优先级, 参数含义, 开发参数, guc隐含参数, 如何修改参数并使之生效, 如何查看当前参数值, 如何查看参数值的范围);
  用户管理(创建角色, 角色权限管理, 角色membership管理, 角色成员权限管理);
  数据库逻辑结构(cluster, database, schema, object, field, access privilege);
  数据库物理结构(tablespace, datafile, segment, block, controlfile, xlog, archivelog);
第二天
  数据库管理(创建表空间, 创建数据库, 创建数据库模板, 数据库配置, 删除数据库, 跨数据库的数据访问);
  基于角色的对象权限管理(表, 列, 序列, 数据库, 域, 外部数据, 函数, 语言, 大对象, schema, 表空间, 类型);
  默认权限和继承权限(default privilege, inherit privilege);
  数据库安全(认证, 网络, 数据存储, 密码, 注入, 欺骗);
  数据库监控(状态监控, 趋势监控, 预警, 常用监控工具和插件nagios, zabbix, pg_statsinfo);
  多版本并发控制和垃圾回收;
  日常维护(垃圾回收, 数据重组, 索引维护, VM维护, 预防XID溢出, 日志维护);
第三天
  数据迁移(逻辑备份和还原, 增量数据迁移的方法, 异构数据迁移的方法(如oracle to postgresql));
  如何打数据库补丁;
  数据库版本升级(小版本升级方法, 大版本升级方法);
  建模与Benchmark(如何根据业务形态建立数据库测试模型, 测试工具的使用, 根据测试模型和硬件标准输出benchmark);
  数据库日志分析(错误代码介绍, 日志的筛选过滤, 日志的维护);
  数据库审计(参数层面的审计开关, 定制化审计(如触发器结合HSTORE), 审计数据的维护手段);
  本地化(字符集, collate, 编码转换, 如何防止乱码);
  数据库性能分析(OS性能分析报表, SQL性能分析报表);
  数据库巡检(如何定制巡检项, 指标, 如何分析巡检报告);'));
return;
end;
$$ language plpgsql strict;

英语分词+插入性能：
英文分词+插入性能：~ 666万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
postgres@db-192-168-173-33-> pgbench -M prepared -n -f test.sql -P 5 -c 36 -j 36 -T 30000000
progress: 5.0 s, 24825.6 tps, lat 1.443 ms stddev 1.082
progress: 10.0 s, 24609.2 tps, lat 1.462 ms stddev 1.112
progress: 15.0 s, 24766.2 tps, lat 1.452 ms stddev 1.106
progress: 20.0 s, 24578.8 tps, lat 1.463 ms stddev 1.095
progress: 25.0 s, 24761.1 tps, lat 1.453 ms stddev 1.106
progress: 30.0 s, 24558.6 tps, lat 1.465 ms stddev 1.129
progress: 35.0 s, 22420.1 tps, lat 1.604 ms stddev 1.376
progress: 40.0 s, 25480.7 tps, lat 1.412 ms stddev 1.108
progress: 45.0 s, 25528.4 tps, lat 1.409 ms stddev 1.097
progress: 50.0 s, 25239.3 tps, lat 1.425 ms stddev 1.113
progress: 55.0 s, 25236.4 tps, lat 1.425 ms stddev 1.117
progress: 60.0 s, 25392.1 tps, lat 1.417 ms stddev 1.079
progress: 65.0 s, 25521.0 tps, lat 1.409 ms stddev 1.100
progress: 70.0 s, 25154.2 tps, lat 1.430 ms stddev 1.117
progress: 75.0 s, 25260.6 tps, lat 1.424 ms stddev 1.105
progress: 80.0 s, 25364.2 tps, lat 1.418 ms stddev 1.086
progress: 85.0 s, 25383.5 tps, lat 1.417 ms stddev 1.094
progress: 90.0 s, 25392.8 tps, lat 1.417 ms stddev 1.116
progress: 95.0 s, 25651.1 tps, lat 1.402 ms stddev 1.102
progress: 100.0 s, 25663.7 tps, lat 1.401 ms stddev 1.082
progress: 105.0 s, 24486.3 tps, lat 1.469 ms stddev 1.204
progress: 110.0 s, 22847.7 tps, lat 1.574 ms stddev 1.362
progress: 115.0 s, 24687.0 tps, lat 1.457 ms stddev 1.248
progress: 120.0 s, 25433.6 tps, lat 1.414 ms stddev 1.095
progress: 125.0 s, 25667.7 tps, lat 1.401 ms stddev 1.094
progress: 130.0 s, 25581.7 tps, lat 1.406 ms stddev 1.116
progress: 135.0 s, 25411.5 tps, lat 1.415 ms stddev 1.128
progress: 140.0 s, 25507.5 tps, lat 1.410 ms stddev 1.105
progress: 145.0 s, 25503.0 tps, lat 1.410 ms stddev 1.087
progress: 150.0 s, 25533.5 tps, lat 1.409 ms stddev 1.110
progress: 155.0 s, 25708.8 tps, lat 1.399 ms stddev 1.087
progress: 160.0 s, 25574.1 tps, lat 1.406 ms stddev 1.118
progress: 165.0 s, 25580.5 tps, lat 1.406 ms stddev 1.098
progress: 170.0 s, 25428.9 tps, lat 1.414 ms stddev 1.111
progress: 175.0 s, 25522.0 tps, lat 1.409 ms stddev 1.102
progress: 180.0 s, 23641.7 tps, lat 1.521 ms stddev 1.276
progress: 185.0 s, 23065.6 tps, lat 1.559 ms stddev 1.425
progress: 190.0 s, 24623.5 tps, lat 1.461 ms stddev 1.218
progress: 195.0 s, 25486.2 tps, lat 1.411 ms stddev 1.142
progress: 200.0 s, 25507.4 tps, lat 1.410 ms stddev 1.108

中文分词+插入性能：
中文分词+插入性能：~ 290万字每秒 ( Intel(R) Xeon(R) CPU          X7460  @ 2.66GHz )
postgres@db-192-168-173-33-> pgbench -M prepared -n -f test.sql -P 5 -c 36 -j 36 -T 30000000
progress: 5.0 s, 10934.3 tps, lat 3.277 ms stddev 2.349
progress: 10.0 s, 10855.9 tps, lat 3.315 ms stddev 2.359
progress: 15.0 s, 10828.8 tps, lat 3.323 ms stddev 2.426
progress: 20.0 s, 10849.6 tps, lat 3.316 ms stddev 2.361
progress: 25.0 s, 10909.1 tps, lat 3.299 ms stddev 2.320
progress: 30.0 s, 10855.7 tps, lat 3.315 ms stddev 2.388
progress: 35.0 s, 10857.4 tps, lat 3.315 ms stddev 2.367
progress: 40.0 s, 10799.4 tps, lat 3.332 ms stddev 2.503
progress: 45.0 s, 10962.9 tps, lat 3.283 ms stddev 2.390
progress: 50.0 s, 10843.2 tps, lat 3.319 ms stddev 2.466
progress: 55.0 s, 9665.7 tps, lat 3.723 ms stddev 3.115
progress: 60.0 s, 9950.5 tps, lat 3.618 ms stddev 2.918
progress: 65.0 s, 10822.1 tps, lat 3.324 ms stddev 2.470
progress: 70.0 s, 10764.6 tps, lat 3.343 ms stddev 2.517
progress: 75.0 s, 10887.8 tps, lat 3.306 ms stddev 2.481
progress: 80.0 s, 10897.6 tps, lat 3.303 ms stddev 2.474
progress: 85.0 s, 10814.6 tps, lat 3.328 ms stddev 2.458
progress: 90.0 s, 10805.0 tps, lat 3.330 ms stddev 2.552
progress: 95.0 s, 10921.5 tps, lat 3.296 ms stddev 2.422
progress: 100.0 s, 10820.0 tps, lat 3.326 ms stddev 2.518
progress: 105.0 s, 9833.8 tps, lat 3.659 ms stddev 3.212
progress: 110.0 s, 9609.7 tps, lat 3.744 ms stddev 3.084
progress: 115.0 s, 10159.1 tps, lat 3.543 ms stddev 2.917
progress: 120.0 s, 10849.9 tps, lat 3.316 ms stddev 2.520
progress: 125.0 s, 10820.0 tps, lat 3.327 ms stddev 2.505
progress: 130.0 s, 10828.0 tps, lat 3.323 ms stddev 2.549
progress: 135.0 s, 10819.1 tps, lat 3.326 ms stddev 2.536

查询性能：
查询性能视查询条件，数据内容而定。一般可以在毫秒级返回。当然还要关注实际查询条件和索引存储结构，有些查询组合可能使查询时间较长，因为需要扫描的数据链路更长。
digoal=> explain analyze select * from test_ts where ts @@ tsquery '源代码' limit 1;
                                                            QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
------
Limit  (cost=0.41..0.85 rows=1 width=4426) (actual time=0.156..0.156 rows=1 loops=1)
->  Index Scan using idx_test_ts on test_ts  (cost=0.41..1039026.29 rows=2371193 width=4426) (actual time=0.154..0.154 rows=1 loo
ps=1)
      Index Cond: (ts @@ '''源代码'''::tsquery)
Planning time: 0.222 ms
Execution time: 0.236 ms
(5 rows)
Time: 0.881 ms

digoal=> explain analyze select * from test_ts where ts @@ tsquery '源代码 & !集群' limit 1;
                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit  (cost=178.00..180.01 rows=1 width=4426) (actual time=363.066..363.066 rows=0 loops=1)
->  Bitmap Heap Scan on test_ts  (cost=178.00..180.01 rows=1 width=4426) (actual time=363.064..363.064 rows=0 loops=1)
      Recheck Cond: (ts @@ '''源代码'' & !''集群'''::tsquery)
      ->  Bitmap Index Scan on idx_1  (cost=0.00..178.00 rows=1 width=0) (actual time=363.061..363.061 rows=0 loops=1)
            Index Cond: (ts @@ '''源代码'' & !''集群'''::tsquery)
Planning time: 0.167 ms
Execution time: 363.105 ms
(7 rows)
Time: 363.683 ms

digoal=> explain analyze select * from test_ts where ts @@ tsquery '德哥' limit 1;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit  (cost=181.88..183.68 rows=1 width=4426) (actual time=0.019..0.019 rows=0 loops=1)
->  Bitmap Heap Scan on test_ts  (cost=181.88..21478.97 rows=11856 width=4426) (actual time=0.018..0.018 rows=0 loops=1)
      Recheck Cond: (ts @@ '''德哥'''::tsquery)
      ->  Bitmap Index Scan on idx_1  (cost=0.00..178.92 rows=11856 width=0) (actual time=0.015..0.015 rows=0 loops=1)
            Index Cond: (ts @@ '''德哥'''::tsquery)
Planning time: 0.180 ms
Execution time: 0.052 ms
(7 rows)
Time: 0.592 ms

存储空间：
digoal=> select count(*) from test_ts;
  count
---------
2371193
(1 row)
约6.9亿中文字，分词后约6.4亿中英文词组。
分词占用10GB，索引占用5GB
digoal=> \dt+ test_ts
                  List of relations
Schema |  Name | Type  | Owner  | Size  | Description
--------+---------+-------+--------+-------+-------------
digoal | test_ts | table | digoal | 10 GB |
(1 row)

digoal=> \di+
                        List of relations
Schema | Name    | Type  | Owner  |  Table  |  Size | Description
--------+-------------+-------+--------+---------+---------+-------------
digoal | idx_test_ts | index | digoal | test_ts | 4959 MB |
(1 row)

[参考]
1. http://blog.163.com/digoal@126/b ... 040201422410175698/
2. http://blog.163.com/digoal@126/b ... 040201552102814822/

wanzai01 · 发表于 2015-6-4 14:01

本帖最后由 wanzai01 于 2015-6-4 16:54 编辑

PostgreSQL 9.5 允许pg_receivexlog实时反馈wal的flush位置，并且可以将pg_receivexlog作为sync standby角色来使用。
这样的话，我们可以利用pg_receivexlog来担任sync standby的角色（极度轻量），从而实现更轻量级的同时保障数据0丢失以及高可用（以前我们需要改一下pg_receivexlog来实现这个功能，现在9.5已经添加了，看样子市场需求蛮大）。
以往，我们使用同步流复制的话，如果只配置了一台standby，当standby或网络异常时，主节点会hang住，因为需要等待sync standby返回wal的flush位置来返回事务提交状态。

为了避免primary hang住，我们需要将sync状态改为async状态，但是这样需要一个后台进程来完成监护，当primary出现异常时，有数据丢失的风险，同时在修改synchronous_standby_names = ''前，primary 的写事务也是会hang住的。

所以为了避免sync standby带来的单点故障，我们往往需要配置多台standby，请注意，每台standby都需要和primary同样的存储空间，并且需要有足够强的io能力，如果你不需要读负载均衡的话，这样的搭配有点浪费。

现在好了，PostgreSQL 9.5允许pg_receivexlog这个工具作为sync standby角色来使用，那么我们只需要一台standby即可，另外再开一个pg_receivexlog来避免standby的单点故障，当standby发生故障时，pg_receivexlog会向主节点回报wal flush位置，所以不会导致primary hang住。
使用pg_receivexlog的好处，不需要和primary一样大的存储空间，只需要放xlog的空间即可。

我们可以把pg_receivexlog配置在数据库主机本地，这样来避免流复制网络问题导致的primary hang住。

这样的同步流复制HA，确保0数据丢失的同时还能提供非常好的可用性。
(当然，这么做其实没有意义，因为本地不需要放多份wal，只是为了避免standby return wal flush lsn异常)

如果可以的话，可以用性能较好的云硬盘来放wal。但是要和sync standby流复制走不同的数据链路，否则也无法避免链路问题导致的primary hang。

相关参数：
pg_receivexlog can perform one of the two following actions in order to control physical replication slots:
--create-slot
Create a new physical replication slot with the name specified in --slot, then start to stream WAL.

--drop-slot
Drop the replication slot with the name specified in --slot, then exit.

-S slotname
--slot=slotname
Require pg_receivexlog to use an existing replication slot (see Section 25.2.6). When this option is used, pg_receivexlog will report a flush position to the server, indicating when each segment has been synchronized to disk so that the server can remove that segment if it is not otherwise needed. --synchronous option must be specified when making pg_receivexlog run as synchronous standby by using replication slot. Otherwise WAL data cannot be flushed frequently enough for this to work correctly.

--synchronous
Flush the WAL data to disk immediately after it has been received. Also send a status packet back to the server immediately after flushing, regardless of --status-interval.

小测：
pg_hba.conf
# replication privilege.
local replication    postgres                               trust

postgresql.conf
synchronous_standby_names = '*'

pg95@db-172-16-3-150-> pg_receivexlog --create-slot -S xlogsync_node1 --synchronous -D /data03/pgdata95/sync

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+-----------------------------
pid             | 3851
usesysid       | 10
usename       | postgres
application_name | pg_receivexlog
client_addr    |
client_hostname  |
client_port    | -1
backend_start | 2015-05-25 15:07:42.50071+08
backend_xmin    |
state          | streaming
sent_location | 1/8A77B690
write_location | 1/8A77B690
flush_location | 1/8A77B690
replay_location  |
sync_priority | 1
sync_state    | sync
postgres=# select * from pg_replication_slots ;
-[ RECORD 1 ]+---------------
slot_name | xlogsync_node1
plugin    |
slot_type | physical
datoid    |
database    |
active    | t
active_pid | 3851
xmin       |
catalog_xmin |
restart_lsn  | 1/8A77BBD0

[参考]
1. http://www.postgresql.org/docs/d ... -pgreceivexlog.html

wanzai01 · 发表于 2015-6-4 14:03

PostgreSQL的函数是原子操作，所以不能像Oracle那样在函数中实现分段提交。
但是如果你要从Oracle迁移到PostgreSQL的话，必然会面临这样的问题，那么怎么办呢？
有几种方法可以来实现，下面是例子：
1. 通过exception来实现分段提交。
create table tbl (id int primary key, info text);

create or replace function func1() returns void as $$
declare
  v_stat int := 0;
begin
  insert into tbl values(1);
  v_stat := 1;
  insert into tbl values(2);
  v_stat := 2;
  insert into tbl values(3);
  v_stat := 3;
  insert into tbl values(3);
  v_stat := 4;
  return;
  exception when others then
case
   when v_stat = 1 then
      insert into tbl values(1);
   when v_stat = 2 then
      insert into tbl values(1);
      insert into tbl values(2);
   when v_stat = 3 then
      insert into tbl values(1);
      insert into tbl values(2);
      insert into tbl values(3);
   when v_stat = 4 then
      insert into tbl values(1);
      insert into tbl values(2);
insert into tbl values(3);
insert into tbl values(3);
   else
      return;
   end case;
end;
$$ language plpgsql;

postgres=# select func1();
func1
-------

(1 row)

postgres=# select ctid,* from tbl;
ctid  | id | info
-------+----+------
(0,5) |  1 |
(0,6) |  2 |
(0,7) |  3 |
(3 rows)
这样做的弊端很明显，需要重新插入，相当于前面的操作全部回滚，产生了双份数据，其中前面插入的一份是垃圾。
另外，代码会变的很繁琐。
所以我们可以略微改进一下。

2. 把每个分段作为一个子函数，正常返回true，异常返回false，在函数中调用这些子函数来实现分段来规避上面的问题。
create or replace function subf1() returns boolean as $$
declare
begin
  insert into tbl values(1);
  -- 以及其他需要放一起提交的SQL和逻辑
  return true;
exception when others then
  return false;
end;
$$ language plpgsql strict;

create or replace function subf2() returns boolean as $$
declare
begin
  insert into tbl values(2);
  -- 以及其他需要放一起提交的SQL和逻辑
  return true;
exception when others then
  return false;
end;
$$ language plpgsql strict;

create or replace function subf3() returns boolean as $$
declare
begin
  insert into tbl values(3);
  -- 以及其他需要放一起提交的SQL和逻辑
  return true;
exception when others then
  return false;
end;
$$ language plpgsql strict;

create or replace function subf4() returns boolean as $$
declare
begin
  insert into tbl values(3);
  -- 以及其他需要放一起提交的SQL和逻辑
  return true;
exception when others then
  return false;
end;
$$ language plpgsql strict;

create or replace function func1() returns void as $$
declare
  v_stat boolean;
begin
--  模拟分段1
  select subf1() into v_stat;
  if not v_stat then
return;
  end if;
--  模拟分段2
  select subf2() into v_stat;
  if not v_stat then
return;
  end if;
--  模拟分段3
  select subf3() into v_stat;
  if not v_stat then
return;
  end if;
--  模拟分段4
  select subf4() into v_stat;
  if not v_stat then
return;
  end if;
return;
end;
$$ language plpgsql;

postgres=# truncate tbl;
TRUNCATE TABLE
postgres=# select func1();
func1
-------

(1 row)

postgres=# select ctid,* from tbl;
ctid  | id | info
-------+----+------
(0,1) |  1 |
(0,2) |  2 |
(0,3) |  3 |
(3 rows)
现在正常了，不会产生双份垃圾。

wanzai01 · 发表于 2015-6-4 14:05

如何了解PostgreSQL中的等待详情，例如你正在等待的是哪个PID，这个PID正在干什么?你在等待它的什么资源释放。
PostgreSQL和大多数传统RDBMS一样，都设计了大量的锁来保证并发操作的数据一致性。
同时PG在设计锁等待时，以队列方式存储等待锁。
参考
ProcSleep()@src/backend/storage/lmgr/proc.c
http://blog.163.com/digoal@126/b ... 040201352010122653/
因此，会出现一种问题。
例如一个长事务A持有一个表的某条记录的更新锁。
接下来的一个事务B要TRUNCATE这个表，会把这个锁放到等待队列去。
在接下来的事务C......如果请求的锁和TRUNCATE表的锁发生冲突，也会放到等待队列去。
这其实是有利有弊的，利是什么呢？
B在A释放后，可以立即获得锁进行TRUNCATE。
弊是什么？
排在B后面的事务会被堵塞，虽然它们可能和A没有锁冲突，也需要等待。

弊端往往在某些凑巧的情况下起到放大效果。
例如一个表的操作非常频繁，如果刚好有一个长事务在里面，然后又刚好有事务需要获得一个排他锁，那么接下来的频繁DML请求都会被堵塞。

一般可以用锁超时来降低弊端带来的这种影响，配置参数lock_timeout，如果锁等待超过这个时间，会强行中断，从等待队列去除。

另外，如果我们没有设置lock_timeout或者不方便设置lock_timeout的话，一旦发现数据库出现了大量的等待，应该如何找到罪魁祸首呢？即处于等待状态的查询，它们到底在等待谁？
找到之后可以认为的杀掉这些罪魁祸首。
使用以下SQL
with t_wait as
(select a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,r.xact_start r_xact_start,r.query_start r_query_start,r.query r_query,
w.usename w_user,w.datname w_db,w.pid w_pid,w.xact_start w_xact_start,w.query_start w_query_start,w.query w_query
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid
  order by r.xact_start;
例如：
-[ RECORD 1 ]-+---------------------------------------------------------------------
locktype    | relation
mode       | ShareUpdateExclusiveLock
r_user       | postgres
r_db       | postgres
relation    | tbl
r_pid       | 24579
r_xact_start  | 2015-05-10 09:43:53.956252+08
r_query_start | 2015-05-10 09:43:53.956252+08
r_query    | autovacuum: VACUUM ANALYZE public.tbl (to prevent wraparound)
w_user       | postgres
w_db       | postgres
w_pid       | 24737
w_xact_start  | 2015-05-10 09:47:15.294562+08
w_query_start | 2015-05-10 09:47:15.294562+08
w_query    | insert into tbl(crt_time) select now() from generate_series(1,1000);
.....
(1001 rows)
干掉它：
postgres=# select pg_terminate_backend(24579);
-[ RECORD 1 ]--------+--
pg_terminate_backend | t
再次查询，等待消失。
postgres=# with t_wait as
(select a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,r.xact_start r_xact_start,r.query_start r_query_start,r.query r_query,
w.usename w_user,w.datname w_db,w.pid w_pid,w.xact_start w_xact_start,w.query_start w_query_start,w.query w_query
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid
  order by r.xact_start;
(No rows)
实际上，这个查询还不是完美，因为锁等待实际上是一个TRUNCATE TABLE的操作造成的，而因为AUTO VACUUM FREEZE和TRUNCATE操作冲突了，所以这两个会话的锁都会对后面的DML造成影响，实际上，我们要找到的应该是级别最高的锁(TRUNCATE)，干掉这个才是最重要的，普通的vacuum并不会和DML冲突。
所以我们需要改进一下这个查询语句。
从pg_locks视图的函数的源码，可以知道mode是怎么来的。
src/backend/utils/adt/lockfuncs.c
/*
* pg_lock_status - produce a view with one row per held or awaited lock mode
*/
Datum
pg_lock_status(PG_FUNCTION_ARGS)
{
......
            Datum          values[NUM_LOCK_STATUS_COLUMNS];
......
            values[12] = CStringGetTextDatum(GetLockmodeName(instance->locktag.locktag_lockmethodid, mode));
......
}
src/backend/storage/lmgr/lock.c
/*
* Fetch the lock method table associated with a given lock
*/
LockMethod
GetLocksMethodTable(const LOCK *lock)
{
      LOCKMETHODID lockmethodid = LOCK_LOCKMETHOD(*lock);

      Assert(0 < lockmethodid && lockmethodid < lengthof(LockMethods));
      return LockMethods[lockmethodid];
}

/* Names of lock modes, for debug printouts */
static const char *const lock_mode_names[] =
{
      "INVALID",
      "AccessShareLock",
      "RowShareLock",
      "RowExclusiveLock",
      "ShareUpdateExclusiveLock",
      "ShareLock",
      "ShareRowExclusiveLock",
      "ExclusiveLock",
      "AccessExclusiveLock"
};

/*
* Data structures defining the semantics of the standard lock methods.
*
* The conflict table defines the semantics of the various lock modes.
*/
static const LOCKMASK LockConflicts[] = {
      0,
      /* AccessShareLock */
      (1 << AccessExclusiveLock),

      /* RowShareLock */
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* RowExclusiveLock */
      (1 << ShareLock) | (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* ShareUpdateExclusiveLock */
      (1 << ShareUpdateExclusiveLock) |
      (1 << ShareLock) | (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* ShareLock */
      (1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
      (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* ShareRowExclusiveLock */
      (1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
      (1 << ShareLock) | (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* ExclusiveLock */
      (1 << RowShareLock) |
      (1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
      (1 << ShareLock) | (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock),

      /* AccessExclusiveLock */
      (1 << AccessShareLock) | (1 << RowShareLock) |
      (1 << RowExclusiveLock) | (1 << ShareUpdateExclusiveLock) |
      (1 << ShareLock) | (1 << ShareRowExclusiveLock) |
      (1 << ExclusiveLock) | (1 << AccessExclusiveLock)

};

static const LockMethodData default_lockmethod = {
      AccessExclusiveLock,          /* highest valid lock mode number */
      LockConflicts,
      lock_mode_names,
#ifdef LOCK_DEBUG
      &Trace_locks
#else
      &Dummy_trace
#endif
};

static const LockMethodData user_lockmethod = {
      AccessExclusiveLock,          /* highest valid lock mode number */
      LockConflicts,
      lock_mode_names,
#ifdef LOCK_DEBUG
      &Trace_userlocks
#else
      &Dummy_trace
#endif
};
src/include/storage/lock.h
/*
* These are the valid values of type LOCKMODE for all the standard lock
* methods (both DEFAULT and USER).
*/

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */
#define NoLock                               0

#define AccessShareLock                1             /* SELECT */
#define RowShareLock                   2             /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock             3             /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4             /* VACUUM (non-FULL),ANALYZE, CREATE
                                    * INDEX CONCURRENTLY */
#define ShareLock                            5             /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6             /* like EXCLUSIVE MODE, but allows ROW
                                    * SHARE */
#define ExclusiveLock                7             /* blocks ROW SHARE/SELECT...FOR
                                             * UPDATE */
#define AccessExclusiveLock          8             /* ALTER TABLE, DROP TABLE, VACUUM
                                             * FULL, and unqualified LOCK TABLE */
改进后的查询如下：
用一个函数来将锁转换为数字，
postgres=# create or replace function f_lock_level(i_mode text) returns int as $$
declare
begin
  case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
  end case;
end;
$$ language plpgsql strict;
修改查询语句，按锁级别排序：
with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid and
  r.transactionid is not distinct from w.transactionid and
  r.pid <> w.pid
  order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
现在可以排在前面的就是锁级别高的等待，优先干掉这个。
-[ RECORD 1 ]-+---------------------------------------------------------------------
locktype    | relation  -- 冲突类型
r_mode       | ShareUpdateExclusiveLock  -- 持锁模式
r_user       | postgres  -- 持锁用户
r_db       | postgres  -- 持锁数据库
relation    | tbl  -- 持锁对象
r_pid       | 25656  -- 持锁进程
r_xact_start  | 2015-05-10 14:11:16.08318+08  -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08  -- 持锁SQL开始时间
r_locktime | 00:01:49.460779  -- 持锁时长
r_query    | vacuum freeze tbl;  --  持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode       | AccessExclusiveLock  -- 等待锁模式
w_pid       | 26731  -- 等待锁进程
w_xact_start  | 2015-05-10 14:11:17.987362+08  --  等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08  --  等待锁SQL开始时间
w_locktime | 00:01:47.556597  --  等待锁时长
w_query    | truncate tbl;  -- 等待锁SQL
-[ RECORD 2 ]-+---------------------------------------------------------------------
locktype    | relation
r_mode       | ShareUpdateExclusiveLock
r_user       | postgres
r_db       | postgres
relation    | tbl
r_pid       | 25656
r_xact_start  | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query    | vacuum freeze tbl;
w_mode       | RowExclusiveLock
w_pid       | 25582
w_xact_start  | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query    | insert into tbl(crt_time) select now() from generate_series(1,1000);  -- 这个SQL其实等待的是truncate tbl的锁;
......
锁冲突判断函数：
LockCheckConflicts()@src/backend/storage/lmgr/lock.c

我们可以创建一个函数用来杀掉对同一个对象某些锁等待时间和等待进程超出阈值的进程。
例如扩展数据块的锁超出一定数量，我们想办法杀掉，避免大量等待。
CREATE OR REPLACE FUNCTION public.f_kill_extend(i_interval interval, i_waiting bigint)
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
declare
  v_database oid;
  v_relation oid;
  v_pid int;
  v_record record;
begin
if (pg_is_in_recovery()) then
  return;
end if;

for v_record in with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid and
  r.transactionid is not distinct from w.transactionid and
  r.pid <> w.pid
  order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start
LOOP
  raise notice '%', v_record;
END LOOP;

for v_database,v_relation in select database,relation from pg_locks where
  locktype='extend' and mode='ExclusiveLock' and not granted and
  pid in (select pid from pg_stat_activity where now()-xact_start > i_interval)
  group by 1,2 having count(*) > i_waiting
loop
  perform pg_terminate_backend(pid) from pg_locks
where database=v_database and relation=v_relation;
end loop;

return;
end;
$function$;
例如：
psql -c "select f_kill_extend(interval '1 sec', 10);"

[参考]
1. http://blog.163.com/digoal@126/b ... 040201352010122653/
2. src/backend/storage/lmgr/proc.c
/*
* ProcSleep -- put a process to sleep on the specified lock
*
* Caller must have set MyProc->heldLocks to reflect locks already held
* on the lockable object by this process (under all XIDs).
*
* The lock table's partition lock must be held at entry, and will be held
* at exit.
*
* Result: STATUS_OK if we acquired the lock, STATUS_ERROR if not (deadlock).
*
* ASSUME: that no one will fiddle with the queue until after
*             we release the partition lock.
*
* NOTES: The process queue is now a priority queue for locking.
*
* P() on the semaphore should put us to sleep.  The process
* semaphore is normally zero, so when we try to acquire it, we sleep.
*/
int
ProcSleep(LOCALLOCK *locallock, LockMethod lockMethodTable)
{
      LOCKMODE       lockmode = locallock->tag.mode;
      LOCK    *lock = locallock->lock;
      PROCLOCK *proclock = locallock->proclock;
      uint32       hashcode = locallock->hashcode;
      LWLockId       partitionLock = LockHashPartitionLock(hashcode);
      PROC_QUEUE *waitQueue = &(lock->waitProcs);
      LOCKMASK       myHeldLocks = MyProc->heldLocks;
      bool          early_deadlock = false;
      bool          allow_autovacuum_cancel = true;
      int                   myWaitStatus;
      PGPROC    *proc;
      int                   i;

      /*
      * Determine where to add myself in the wait queue.
      *
      * Normally I should go at the end of the queue.  However, if I already
      * hold locks that conflict with the request of any previous waiter, put
      * myself in the queue just in front of the first such waiter. This is not
      * a necessary step, since deadlock detection would move me to before that
      * waiter anyway; but it's relatively cheap to detect such a conflict
      * immediately, and avoid delaying till deadlock timeout.
      *
      * Special case: if I find I should go in front of some waiter, check to
      * see if I conflict with already-held locks or the requests before that
      * waiter.  If not, then just grant myself the requested lock immediately.
      * This is the same as the test for immediate grant in LockAcquire, except
      * we are only considering the part of the wait queue before my insertion
      * point.
      */
      if (myHeldLocks != 0)
      {
            LOCKMASK       aheadRequests = 0;

            proc = (PGPROC *) waitQueue->links.next;
            for (i = 0; i < waitQueue->size; i++)
            {
                     /* Must he wait for me? */
                     if (lockMethodTable->conflictTab[proc->waitLockMode] & myHeldLocks)
                     {
                              /* Must I wait for him ? */
                              if (lockMethodTable->conflictTab[lockmode] & proc->heldLocks)
                              {
                                    /*
                                       * Yes, so we have a deadlock.  Easiest way to clean up
                                       * correctly is to call RemoveFromWaitQueue(), but we
                                       * can't do that until we are *on* the wait queue. So, set
                                       * a flag to check below, and break out of loop.  Also,
                                       * record deadlock info for later message.
                                       */
                                    RememberSimpleDeadLock(MyProc, lockmode, lock, proc);
                                    early_deadlock = true;
                                    break;
                              }
                              /* I must go before this waiter.  Check special case. */
                              if ((lockMethodTable->conflictTab[lockmode] & aheadRequests) == 0 &&
                                    LockCheckConflicts(lockMethodTable,
                                                                        lockmode,
                                                                        lock,
                                                                        proclock,
                                                                        MyProc) == STATUS_OK)
                              {
                                    /* Skip the wait and just grant myself the lock. */
                                    GrantLock(lock, proclock, lockmode);
                                    GrantAwaitedLock();
                                    return STATUS_OK;
                              }
                              /* Break out of loop to put myself before him */
                              break;
                     }
                     /* Nope, so advance to next waiter */
                     aheadRequests |= LOCKBIT_ON(proc->waitLockMode);
                     proc = (PGPROC *) proc->links.next;
            }

            /*
               * If we fall out of loop normally, proc points to waitQueue head, so
               * we will insert at tail of queue as desired.
               */
      }
      else
      {
            /* I hold no locks, so I can't push in front of anyone. */
            proc = (PGPROC *) &(waitQueue->links);
      }

      /*
      * Insert self into queue, ahead of the given proc (or at tail of queue).
      */
      SHMQueueInsertBefore(&(proc->links), &(MyProc->links));
      waitQueue->size++;
      lock->waitMask |= LOCKBIT_ON(lockmode);

      /* Set up wait information in PGPROC object, too */
      MyProc->waitLock = lock;
      MyProc->waitProcLock = proclock;
      MyProc->waitLockMode = lockmode;

      MyProc->waitStatus = STATUS_WAITING;

      /*
      * If we detected deadlock, give up without waiting.  This must agree with
      * CheckDeadLock's recovery code, except that we shouldn't release the
      * semaphore since we haven't tried to lock it yet.
      */
      if (early_deadlock)
      {
            RemoveFromWaitQueue(MyProc, hashcode);
            return STATUS_ERROR;
      }

      /* mark that we are waiting for a lock */
      lockAwaited = locallock;

      /*
      * Release the lock table's partition lock.
      *
      * NOTE: this may also cause us to exit critical-section state, possibly
      * allowing a cancel/die interrupt to be accepted. This is OK because we
      * have recorded the fact that we are waiting for a lock, and so
      * LockErrorCleanup will clean up if cancel/die happens.
      */
      LWLockRelease(partitionLock);

      /*
      * Also, now that we will successfully clean up after an ereport, it's
      * safe to check to see if there's a buffer pin deadlock against the
      * Startup process.  Of course, that's only necessary if we're doing Hot
      * Standby and are not the Startup process ourselves.
      */
      if (RecoveryInProgress() && !InRecovery)
            CheckRecoveryConflictDeadlock();

      /* Reset deadlock_state before enabling the timeout handler */
      deadlock_state = DS_NOT_YET_CHECKED;

      /*
      * Set timer so we can wake up after awhile and check for a deadlock. If a
      * deadlock is detected, the handler releases the process's semaphore and
      * sets MyProc->waitStatus = STATUS_ERROR, allowing us to know that we
      * must report failure rather than success.
      *
      * By delaying the check until we've waited for a bit, we can avoid
      * running the rather expensive deadlock-check code in most cases.
      *
      * If LockTimeout is set, also enable the timeout for that.  We can save a
      * few cycles by enabling both timeout sources in one call.
      */
      if (LockTimeout > 0)
      {
            EnableTimeoutParams timeouts[2];

            timeouts[0].id = DEADLOCK_TIMEOUT;
            timeouts[0].type = TMPARAM_AFTER;
            timeouts[0].delay_ms = DeadlockTimeout;
            timeouts[1].id = LOCK_TIMEOUT;
            timeouts[1].type = TMPARAM_AFTER;
            timeouts[1].delay_ms = LockTimeout;
            enable_timeouts(timeouts, 2);
      }
      else
            enable_timeout_after(DEADLOCK_TIMEOUT, DeadlockTimeout);

      /*
      * If someone wakes us between LWLockRelease and PGSemaphoreLock,
      * PGSemaphoreLock will not block.  The wakeup is "saved" by the semaphore
      * implementation.  While this is normally good, there are cases where a
      * saved wakeup might be leftover from a previous operation (for example,
      * we aborted ProcWaitForSignal just before someone did ProcSendSignal).
      * So, loop to wait again if the waitStatus shows we haven't been granted
      * nor denied the lock yet.
      *
      * We pass interruptOK = true, which eliminates a window in which
      * cancel/die interrupts would be held off undesirably.  This is a promise
      * that we don't mind losing control to a cancel/die interrupt here.  We
      * don't, because we have no shared-state-change work to do after being
      * granted the lock (the grantor did it all).  We do have to worry about
      * canceling the deadlock timeout and updating the locallock table, but if
      * we lose control to an error, LockErrorCleanup will fix that up.
      */
      do
      {
            PGSemaphoreLock(&MyProc->sem, true);

            /*
               * waitStatus could change from STATUS_WAITING to something else
               * asynchronously.  Read it just once per loop to prevent surprising
               * behavior (such as missing log messages).
               */
            myWaitStatus = MyProc->waitStatus;

            /*
               * If we are not deadlocked, but are waiting on an autovacuum-induced
               * task, send a signal to interrupt it.
               */
            if (deadlock_state == DS_BLOCKED_BY_AUTOVACUUM && allow_autovacuum_cancel)
            {
                     PGPROC    *autovac = GetBlockingAutoVacuumPgproc();
                     PGXACT    *autovac_pgxact = &ProcGlobal->allPgXact[autovac->pgprocno];

                     LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);

                     /*
                     * Only do it if the worker is not working to protect against Xid
                     * wraparound.
                     */
                     if ((autovac != NULL) &&
                              (autovac_pgxact->vacuumFlags & PROC_IS_AUTOVACUUM) &&
                              !(autovac_pgxact->vacuumFlags & PROC_VACUUM_FOR_WRAPAROUND))
                     {
                              int                   pid = autovac->pid;
                              StringInfoData locktagbuf;
                              StringInfoData logbuf;  /* errdetail for server log */

                              initStringInfo(&locktagbuf);
                              initStringInfo(&logbuf);
                              DescribeLockTag(&locktagbuf, &lock->tag);
                              appendStringInfo(&logbuf,
                                                               _("Process %d waits for %s on %s."),
                                                               MyProcPid,
                                                      GetLockmodeName(lock->tag.locktag_lockmethodid,
                                                                                       lockmode),
                                                               locktagbuf.data);

                              /* release lock as quickly as possible */
                              LWLockRelease(ProcArrayLock);

                              ereport(LOG,
                                       (errmsg("sending cancel to blocking autovacuum PID %d",
                                                      pid),
                                       errdetail_log("%s", logbuf.data)));

                              pfree(logbuf.data);
                              pfree(locktagbuf.data);

                              /* send the autovacuum worker Back to Old Kent Road */
                              if (kill(pid, SIGINT) < 0)
                              {
                                    /* Just a warning to allow multiple callers */
                                    ereport(WARNING,
                                                      (errmsg("could not send signal to process %d: %m",
                                                                     pid)));
                              }
                     }
                     else
                              LWLockRelease(ProcArrayLock);

                     /* prevent signal from being resent more than once */
                     allow_autovacuum_cancel = false;
            }

            /*
               * If awoken after the deadlock check interrupt has run, and
               * log_lock_waits is on, then report about the wait.
               */
            if (log_lock_waits && deadlock_state != DS_NOT_YET_CHECKED)
            {
                     StringInfoData buf;
                     const char *modename;
                     long          secs;
                     int                   usecs;
                     long          msecs;

                     initStringInfo(&buf);
                     DescribeLockTag(&buf, &locallock->tag.lock);
                     modename = GetLockmodeName(locallock->tag.lock.locktag_lockmethodid,
                                                                        lockmode);
                     TimestampDifference(get_timeout_start_time(DEADLOCK_TIMEOUT),
                                                            GetCurrentTimestamp(),
                                                            &secs, &usecs);
                     msecs = secs * 1000 + usecs / 1000;
                     usecs = usecs % 1000;

                     if (deadlock_state == DS_SOFT_DEADLOCK)
                              ereport(LOG,
                                             (errmsg("process %d avoided deadlock for %s on %s by rearranging queue order after %ld.%03d ms",
                                                      MyProcPid, modename, buf.data, msecs, usecs)));
                     else if (deadlock_state == DS_HARD_DEADLOCK)
                     {
                              /*
                              * This message is a bit redundant with the error that will be
                              * reported subsequently, but in some cases the error report
                              * might not make it to the log (eg, if it's caught by an
                              * exception handler), and we want to ensure all long-wait
                              * events get logged.
                              */
                              ereport(LOG,
                                             (errmsg("process %d detected deadlock while waiting for %s on %s after %ld.%03d ms",
                                                      MyProcPid, modename, buf.data, msecs, usecs)));
                     }

                     if (myWaitStatus == STATUS_WAITING)
                              ereport(LOG,
                                             (errmsg("process %d still waiting for %s on %s after %ld.%03d ms",
                                                      MyProcPid, modename, buf.data, msecs, usecs)));
                     else if (myWaitStatus == STATUS_OK)
                              ereport(LOG,
                                    (errmsg("process %d acquired %s on %s after %ld.%03d ms",
                                                      MyProcPid, modename, buf.data, msecs, usecs)));
                     else
                     {
                              Assert(myWaitStatus == STATUS_ERROR);

                              /*
                              * Currently, the deadlock checker always kicks its own
                              * process, which means that we'll only see STATUS_ERROR when
                              * deadlock_state == DS_HARD_DEADLOCK, and there's no need to
                              * print redundant messages.  But for completeness and
                              * future-proofing, print a message if it looks like someone
                              * else kicked us off the lock.
                              */
                              if (deadlock_state != DS_HARD_DEADLOCK)
                                    ereport(LOG,
                                                      (errmsg("process %d failed to acquire %s on %s after %ld.%03d ms",
                                                      MyProcPid, modename, buf.data, msecs, usecs)));
                     }

                     /*
                     * At this point we might still need to wait for the lock. Reset
                     * state so we don't print the above messages again.
                     */
                     deadlock_state = DS_NO_DEADLOCK;

                     pfree(buf.data);
            }
      } while (myWaitStatus == STATUS_WAITING);

      /*
      * Disable the timers, if they are still running.  As in LockErrorCleanup,
      * we must preserve the LOCK_TIMEOUT indicator flag: if a lock timeout has
      * already caused QueryCancelPending to become set, we want the cancel to
      * be reported as a lock timeout, not a user cancel.
      */
      if (LockTimeout > 0)
      {
            DisableTimeoutParams timeouts[2];

            timeouts[0].id = DEADLOCK_TIMEOUT;
            timeouts[0].keep_indicator = false;
            timeouts[1].id = LOCK_TIMEOUT;
            timeouts[1].keep_indicator = true;
            disable_timeouts(timeouts, 2);
      }
      else
            disable_timeout(DEADLOCK_TIMEOUT, false);

      /*
      * Re-acquire the lock table's partition lock.  We have to do this to hold
      * off cancel/die interrupts before we can mess with lockAwaited (else we
      * might have a missed or duplicated locallock update).
      */
      LWLockAcquire(partitionLock, LW_EXCLUSIVE);

      /*
      * We no longer want LockErrorCleanup to do anything.
      */
      lockAwaited = NULL;

      /*
      * If we got the lock, be sure to remember it in the locallock table.
      */
      if (MyProc->waitStatus == STATUS_OK)
            GrantAwaitedLock();

      /*
      * We don't have to do anything else, because the awaker did all the
      * necessary update of the lock table and MyProc.
      */
      return MyProc->waitStatus;
}