把PostgreSQL领回家

快速了解PostgreSQL

digoal.zhou 4/22/2015

目录


 发展历程，圈子 
 特性 
 如何了解源码 
 如何跟踪内核 
 进程结构，文件结构 
 如何做压力测试 
 版本升级 
 备份恢复 
 高可用 
 读写分离




 分布式 
 挖掘 
 扩展功能 
 TODO和软肋 
 性能优化方法 
 数据库安全 
 benchmark 
 学习资料

PostgreSQL发展历程


 1973 University INGRES (起源于IBM System R的一系列文档, Michael Stonebraker and Eugene Wong) 
 1982 INGRES 
 1985 post-Ingres 
 1988 POSTGRES version 1 - 1993 version 4 (END) 
 1995 Postgres95 (伯克利大学学生Andrew Yu, Jolly Chen重写SQL解释器, 替换原项目中的基于Ingres的SQL解释器.为开源

奠定了基础) 
 1996 更为PostgreSQL, 发布第一个开源版本, 后交由PostgreSQL社区维护.

主要RDBMS发展历程

PostgreSQL 版本发布历程

PostgreSQL 代码活跃度

数据取自 https://github.com/postgres/postgres/graphs

PostgreSQL 代码活跃度

数据取自PostgreSQL主代码管理库 http://git.postgresql.org/gitweb/?p=postgresql.git;a=heads

PostgreSQL 全球贡献者


 Core Team成员 
 Josh Berkus (USA, CEO@PostgreSQL Experts Inc.)


 主要负责PG推广, 性能测试, 优化, 文档编辑等工作. 
 Peter Eisentraut (USA, MeetMe.com)


 主要负责了系统建设, 移植, 文档编辑, 国际化, 以及其他增强性的代码工作. 
 Magnus Hagander (Sweden, redpill-linpro.se)


 帮助维护PostgreSQL WEB主站及基础设施, win32的移植, 以及系统认证等工作. 
 Tom Lane (USA, Salesforce)


 遍及PostgreSQL代码的各个角落, 包括BUG评估和修复, 性能改进, 优化等. 
 Bruce Momjian (USA, EnterpriseDB)


 负责维护TODO和FAQ列表, 代码, 发布版本补丁以及培训. 
 Dave Page (United Kingdom, EnterpriseDB)


 负责pgadmin的开发和维护工作, 同时负责管理postgresql.org主站工程, PostgreSQL的安装程序等.


 主要贡献者 
 http://www.postgresql.org/community/contributors/


 Committers (git@gitmaster.postgresql.org/postgresql.git) 
 目前有21位committer. (http://wiki.postgresql.org/wiki/Committers)

PostgreSQL 全球赞助商


 PostgreSQL全球赞助商 (最新 http://www.postgresql.org/about/sponsors/) 
 赞助商分级


 赞助商列表

PostgreSQL中国



用户会



用户(华为,去 哪儿,邮储,腾 讯,移动,斯凯,

同花顺.,阿 里...)



PostgreSQL 社区

>10000人



BBS， 微信圈，

QQ群



服务提供

商(青云,阿 里,神州)



内核研发

>300人(华 为,移动,国 网,人大,武

大..)

PostgreSQL数据库全球使用情况


 生物制药 {Affymetrix(基因芯片), 美国化学协会, gene(结构生物学应用案例), …} 
 电子商务 { CD BABY, etsy(与淘宝类似), whitepages, flightstats, Endpoint Corporation …} 
 学校 {加州大学伯克利分校, 哈佛大学互联网与社会中心, .LRN, 莫斯科国立大学, 悉尼大学, …} 
 金融 {Journyx, LLC, trusecommerce(类似支付宝), 日本证券交易交所, 邮储银行, 同花顺…} 
 游戏 {MobyGames, …} 
 政府 {美国国家气象局, 印度国家物理实验室, 联合国儿童基金, 美国疾病控制和预防中心, 美国国务院, 俄罗斯杜马…} 
 医疗 {calorieking, 开源电子病历项目, shannon医学中心, …} 
 制造业 {Exoteric Networks, 丰田, 捷豹路虎} 
 媒体 {IMDB.com, 美国华盛顿邮报国会投票数据库, MacWorld, 绿色和平组织, …} 
 开源项目 {Bricolage, Debian, FreshPorts, FLPR, PostGIS, SourceForge, OpenACS, Gforge, …} 
 零售 {ADP, CTC, Safeway, Tsutaya, Rockport, …} 
 科技 {Sony, MySpace, Yahoo, Afilias, APPLE, 富士通, Omniti, Red Hat, Sirius IT, SUN, 国际空间站, Instagram, Disqus, 去哪

儿, 腾讯, 华为, 中兴, 斯凯, 云游, 阿里 …} 
 通信 {Cisco, Juniper, NTT(日本电信), 德国电信, Optus, Skype, Tlestra(澳洲电讯), 中国移动…} 
 物流 {SF} 
 More : http://www.postgresql.org/about/users/

许可


 PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses. 
 PostgreSQL Database Management System 
 (formerly known as Postgres, then as Postgres95) 
 Portions Copyright (c) 1996-2015, The PostgreSQL Global Development Group 
 Portions Copyright (c) 1994, The Regents of the University of California 
 Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and

without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. 
 IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
 THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

特性


 SQL特性 
 聚合 
 窗口 
 递归 
 继承 
 外部表 
 事件触发器


 安全特性 
 存储加密 
 链路加密 
 认证方法 
 行安全策略


 数据类型特性




 几何类型 
 网络类型 
 全文检索类型 
 JSON, JSONB 
 数组 
 范围 
 复合，枚举，域


 索引特性 
 btree 
 hash 
 gist 
 spgist 
 gin 
 brin




 条件索引/部分索引 
 函数索引


 函数特性 
 plpgsql, C, plR, pljava, plpython, plperl, ...


 功能特性 
 流复制 
 模块化 
 钩子 
 元表 
 物化视图, FDW,..

 聚合



特性例子

特性例子


 统计聚合函数的回归测试以及预测应用例子 
 自变量: 昨日收盘价 
 因变量: 今日收盘价 
 公式 y=slope*x+intercept 
 需要用到PostgreSQL统计学相关聚合函数， regr_r2, regr_intercept, regr_slope, 计算数据的相关性，截距，

斜率。使用相关性最高的截距和斜率计算下一天的收盘价。 
 http://blog.163.com/digoal@126/blog/static/16387704020152512741921/

特性例子


 窗口 
 输出每位学生与各学科第一名成绩的分差。

特性例子


 窗口 
 select id,n,course,score, 
 first_value(score) over(partition by course order by score desc) - score as diff 
 from tbl;

特性例子


 递归查询 
 异构查询，例如公交线路信息，可能包含当前站点，上一个站点的信息 
 某些多媒体分类信息，包括大类，小类，每条记录可能记录了父类

WITH(Common Table Expressions)




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



UNION [ALL]



非递归子句 递归子句

TEMP Working TABLE



"递归"SQL



WITH语句的

OUTPUT, 通过LIMIT可以

跳出循环

WITH(Common Table Expressions)




 UNION 去重复(去重复时NULL 视为等同) 
 图中所有输出都涉及UNION [ALL]的操作, 包含以往返回的记录和当前返回的记录



1输出

非递归子句



OUTPUT



2读取 递归子句



开 始

循环



4无,结束递归



4有,递归



TWT有 无数据



6输出 递归子句



5读取



TEMP Working TABLE



6同时输出



7 TWT清空并被替换



TEMP Intermediate TABLE (替换掉TWT的内容后清空自己)



3输出

特性例子


 递归查询

特性例子


 ltree http://www.postgresql.org/docs/devel/static/ltree.html 
 异构数据类型

特性例子


 with原子操作 
 例子, 跨分区更新分区表的分区字段值


 measurement 按月分区字段logdate, 将logdate= '2015-03-01'的值更新到另一个分区,同时还需要更新其他某 字段值为999


 with t1 as 
 (delete from measurement where logdate='2015-03-01' 
 returning city_id,'2015-04-01'::timestamp(0) without time zone,peaktemp,999) 
 insert into measurement select * from t1;

特性例子




 外部表




 https://wiki.postgresql.oFrgor/ewiginki/Fdw Foreign



Table(s)



Table(s)




 可以像操作 
 本地表一样 
 join,read/write



NOT NEED



User Mapping(s)



Server(s)



Server(s)



Foreign Table(s)

User Mapping(s)

Server(s)



Foreign Table(s)

User Mapping(s)

Server(s)



Foreign Table(s)

User Mapping(s)

Server(s)



Foreign Table(s)



TABLE DEFINE



User AUTH Mapping(s) INFO



Conn Server(s) INFO



FDW



FDW



FDW



FDW



FDW



FDW(s) API



External



File



Oracle



MySQL



PostgreSQL



Hive



JDBC,...... Data



Source

特性例子


 事件触发器 
 例子,控制普通用户没有执行DDL的权限

特性例子


 事件触发器 
 例子,控制普通用户没有执行DDL的权限


 目前支持的事件


 ddl_command_start 
 ddl_command_end 
 table_rewrite 
 sql_drop


 支持的SQL, (未完全截取)

特性例子




 LDAP认证或AD域认证 
 支持simple或search bind模式



Client



PG



LDAP Server




 simple bind : 
 host all new 0.0.0.0/0 ldap ldapserver=172.16.3.150 ldapport=389 ldapprefix="uid="

ldapsuffix=",ou=People,dc=my-domain,dc=com"


 search bind : （可选配置ldapbinddn和ldapbindpasswd ） 
 host all new 0.0.0.0/0 ldap ldapserver=172.16.3.150 ldapport=389 ldapsearchattribute="uid"

ldapbasedn="ou=People,dc=my-domain,dc=com"

特性例子


 行安全策略 
 例子，数据共享场景，对同一个表操作时，不同的用户能查看到不同的数据子集




 why not view?



子集 子集




 CREATE POLICY name ON table_name



子集



子集




 [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] 
 [ TO { role_name | PUBLIC } [, ...] ]



子集 子集




 [ USING ( using_expression ) ]




 [ WITH CHECK ( check_expression ) ]




 using 指针对已经存在的记录的校验。因此可实施在select，update，delete，ALL上。




 whth check 指针对将要新增的记录的校验。因此可实施在insert，update，ALL上。

特性例子


 行安全策略 
 例子，数据共享场景，对同一个表操作时，不同的用户能查看到不同的数据子集


 创建一个新增数据的策略（使用with check，检测新数据） 
 这个策略检测test表的r字段，必须等于当前用户名才行。 
 也就是说任何用户在插入test表时，r字段的值必须和当前用户名相同，这样就可以很好的控制多个用户在

使用一张表时不会伪造数据。 
 postgres=# create policy p on test for insert to r1 with check( r = current_user); 
 postgres=# alter table test enable row level security; 
 postgres=# \c postgres r1 
 postgres=> insert into test values(4,'r2'); 
 ERROR: new row violates WITH CHECK OPTION for "test" 
 postgres=> insert into test values(4,'r1'); 
 INSERT 0 1

特性例子


 柱状图妙用 (用作评估，和真实情况有偏差) 
 例子，快速评估值的TOPx 
 假设某表存储了用户下载的APP数组，如何快速统计装机排名前10的APP？


 select * from 
 (select row_number() over(partition by r) as rn,ele from (select unnest(most_common_elems::text::int[]) ele,2 as r

from pg_stats where tablename='test_2' and attname='appid') t) t1 
 join 
 (select row_number() over(partition by r) as rn,freq from (select unnest(most_common_elem_freqs) freq,2 as r

from pg_stats where tablename='test_2' and attname='appid') t) t2 
 on (t1.rn=t2.rn) 
 order by t2.freq desc limit 10;

特性例子


 hll(HyperLogLog)插件 
 快速唯一值，增量评估 
 例如统计用户数，新增用户数。


 select count(distinct userid) from access_log where date(crt_time)='2013-02-01'; -- 非常耗时.


 hll解决了耗时的问题, 使用方法是将用户ID聚合存储到hll类型中. 如下(假设user_id的类型为int) : 
 create table access_date (acc_date date unique, userids hll); 
 insert into access_date select date(crt_time), hll_add_agg(hll_hash_integer(user_id)) from access_log group by 1; 
 select #userids from access_date where acc_date='2013-02-01'; -- 这条语句返回只要1毫秒左右. (10亿个唯一值

返回也在1毫秒左右) 
 而hll仅仅需要1.2KB就可以存储1.6e+12的唯一值.

特性例子


 hll(HyperLogLog)插件 
 快速唯一值，增量评估 
 例如统计用户数，新增用户数。

特性例子


 json , jsonb类型 
{ 
 "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", 
 "name": "Angela Barton", 
 "is_active": true, 
 "company": "Magnafone", 
 "address": "178 Howard Place, Gulf, Washington, 702", 
 "registered": "2009-11-07T08:53:22 +08:00", 
 "latitude": 19.793713, 
 "longitude": 86.513373, 
 "tags": [ 
 "enim", 
 "aliquip", 
 "qui" 
] 
}

特性例子


 gin索引 for jsonb 
 CREATE INDEX idxgin ON api USING gin (jdoc); 
 该索引支持的操作符


 -- Find documents in which the key "company" has value "Magnafone" 
 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; 
 -- Find documents in which the key "tags" contains array element "qui" 
 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

特性例子


 gin索引 for jsonb


 CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags')); 
 -- Find documents in which the key "tags" contains key or array element "qui" 
 SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

特性例子


 hstore 类型 
 Includes zero or more key => value pairs separated by commas.


 例子 
 http://blog.163.com/digoal@126/blog/static/163877040201252575529358/ 
 跟踪 insert, update, delete, truncate 
 client_ip, client_port, username, database, schema, object, time, NEW, OLD, timestamp, TAG


 5 | 23731 | public | test | AFTER | ROW | UPDATE | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43" 
 | "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:55:41.006069 | postgres

特性例子


 table类型 
 例子 
 使用table类型和触发器实现insert,update,delete,truncate 的flashback 
 http://blog.163.com/digoal@126/blog/static/1638770402014728105442434/

以事务为最小单位记录 NEW,OLD for insert,update,delete,truncate 回滚时以事务为最小单位, 封装SQL


 begin 
 for v_op, v_encoding_tmp, v_old, v_new in 
 select op,encoding,old_rec::text,new_rec::text from undo_t where xid>=v_xid order by xid desc,id desc 
 LOOP

特性例子


 execute 'set client_encoding='''||v_encoding_tmp||''''; 
 case v_op 
 when 'INSERT' then 
 delete from public."TBL" t where t=v_new::public."TBL"; 
 when 'DELETE' then 
 insert into public."TBL" values ((v_old::public."TBL").*); 
 when 'TRUNCATE' then 
 insert into public."TBL" values ((v_old::public."TBL").*); 
 when 'UPDATE' then 
 delete from public."TBL" t where t=v_new::public."TBL"; 
 insert into public."TBL" values ((v_old::public."TBL").*); 
 else 
 end case; 
 end loop; 
 execute 'set client_encoding='''||v_encoding_curr||''''; 
 end;

特性例子


 范围类型 
 例子，快速范围查询，例如某个IP是否在某个IP地址段内 
 postgres=# create table tbl(id int,ip_start int8,ip_end int8); 
 CREATE TABLE 
 postgres=# create index idx_tbl on tbl using btree(ip_start,ip_end); 
 CREATE INDEX


 postgres=# create table tbl_r(id int,ip_range int8range); 
 CREATE TABLE 
 postgres=# create index idx_tbl_r on tbl_r using spgist(ip_range); 
 CREATE INDEX 
或 
 postgres=# create index idx_tbl_r1 on tbl_r using gist(ip_range); 
 CREATE INDEX

特性例子


 范围类型 
 例子，快速范围查询，例如某个IP是否在某个IP地址段内 
 查询 
 postgres=# select * from tbl where ? between ip_start and ip_end;


 postgres=# select * from tbl_r where ip_range @> ?; 
 效率可提升几十倍.


 OR 
 create index idx on tbl using gist (int8range(ip_start,ip_end+1)); 
 select * from tbl where int8range(ip_start,ip_end+1) @> ?;

特性例子


 全文检索 
 例子, 中文分词与检索


 分词类型：tsvector，支持分词，位置，段落 
 查询条件类型：tsquery，支持与，或，位置，段落，前缀等组合 
 分词索引：GIN


 to_tsvector('testzhcfg','“今年保障房新开工数量虽然有所下调，但实际的年度在建规模以及竣工规模会超以 往年份，相对应的对资金的需求也会创历史纪录。”陈国强说。在他看来，与2011年相比，2012年的保障 房建设在资金配套上的压力将更为严峻。');


 '2011':27 '2012':29 '上':35 '下调':7 '严峻':37 '会':14 '会创':20 '保障':1,30 '历史':21 '压力':36 '国强':24 '在建':10 '实 际':8 '对应':17 '年份':16 '年度':9 '开工':4 '房':2 '房建':31 '数量':5 '新':3 '有所':6 '相比':28 '看来':26 '竣工':12 '纪录 ':22 '规模':11,13 '设在':32 '说':25 '资金':18,33 '超':15 '配套':34 '陈':23 '需求':19

特性例子


 to_tsquery('testzhcfg', '保障房资金压力'); 
 to_tsquery 
 --------------------------------
 '保障' & '房' & '资金' & '压力'


 SELECT 'super:*'::tsquery; -- super开头的单词 
 tsquery 
 ----------
 'super':*


 查询举例： 
 tsvector @@ to_tsquery('testzhcfg', '保障房资金压力'); -- 包含查询条件

特性例子


 自定义字典 
 http://blog.163.com/digoal@126/blog/static/16387704020153309577689/


 全文检索包括几个重要的步骤： 
 1. parsers将文本按照一定的规则分拆成多个token，并且给token按类型归类例如（url, word, number, file, tag, version .....)。 
 CREATE TEXT SEARCH PARSER name ( 
 START = start_function , 
 GETTOKEN = gettoken_function , 
 END = end_function , 
 LEXTYPES = lextypes_function 
 [, HEADLINE = headline_function ] 
)

特性例子


 2. dictionaries将token转换为规则化的分词（即lexeme）（例如去除复数，大小写转换），同时去除一些没有意义的词如 stop word。


 CREATE TEXT SEARCH DICTIONARY name ( 
 TEMPLATE = template 
 [, option = value [, ... ]] 
) 
 CREATE TEXT SEARCH DICTIONARY my_russian ( 
 template = snowball, 
 language = russian, 
 stopwords = myrussian 
 );

特性例子


 ALTER TEXT SEARCH DICTIONARY name ( 
 option [ = value ] [, ... ] 
) 
 The following example command changes the stopword list for a Snowball-based dictionary. Other parameters remain

unchanged.


 ALTER TEXT SEARCH DICTIONARY my_dict ( StopWords = newrussian ); 
 The following example command changes the language option to dutch, and removes the stopword option entirely.


 ALTER TEXT SEARCH DICTIONARY my_dict ( language = dutch, StopWords );

特性例子


 3. templates提供dictionaries下层的功能，创建字典时需指定一个模板，以及一些参数。 
 CREATE TEXT SEARCH TEMPLATE name ( 
 [ INIT = init_function , ] 
 LEXIZE = lexize_function 
)


 4. configurations选择一个parser，并配置token类型和字典的对应关系（如每种token type可以使用不同的字典来处理)。 
 CREATE TEXT SEARCH CONFIGURATION name ( 
 PARSER = parser_name | 
 COPY = source_config 
) 
 ALTER TEXT SEARCH CONFIGURATION name 
 ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]

特性例子


 ALTER TEXT SEARCH CONFIGURATION name 
 ALTER MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ] 
 ALTER TEXT SEARCH CONFIGURATION name 
 ALTER MAPPING REPLACE old_dictionary WITH new_dictionary 
 ALTER TEXT SEARCH CONFIGURATION name 
 ALTER MAPPING FOR token_type [, ... ] REPLACE old_dictionary WITH new_dictionary 
 ALTER TEXT SEARCH CONFIGURATION name 
 DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ] 
 ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name 
 ALTER TEXT SEARCH CONFIGURATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } 
 ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema

特性例子


 自定义字典的例子 
 http://blog.163.com/digoal@126/blog/static/16387704020153309577689/ 
 例子 
 对于word, 先使用别名字典处理，再使用其他字典处理。


 将刘德华，黎明，郭富城，张学友替换为四大天王。 
 将德哥，周正中替换为digoal。


 postgres@db-192-168-173-33-> cd /opt/pgsql/share/tsearch_data/ 
 postgres@db-192-168-173-33-> vi digoal.syn 
 刘德华 四大天王 
 郭富城 四大天王 
 张学友 四大天王 
 黎明 四大天王 
 德哥 digoal 
 周正中 digoal

特性例子


 digoal=# CREATE TEXT SEARCH DICTIONARY my_synonym ( 
 TEMPLATE = synonym, 
 SYNONYMS = digoal 
 );


 在修改english config前: 
 digoal=# select * from to_tsvector('english','刘德华 张学友 黎明 郭富城 德哥 周正中'); 
 to_tsvector 
 --------------------------------------------------------------
 '刘德华':1 '周正中':6 '张学友':2 '德哥':5 '郭富城':4 '黎明':3 
 (1 row)

特性例子


 修改english config的 word类型的字典关系，将匿名字典放最前面。 
 digoal=# ALTER TEXT SEARCH CONFIGURATION english 
 ALTER MAPPING FOR word 
 WITH my_synonym, english_stem; 
 ALTER TEXT SEARCH CONFIGURATION


 现在分词结果变了，都替换成了想要的结果： 
 digoal=# select * from to_tsvector('english','刘德华 张学友 黎明 郭富城 德哥 周正中'); 
 to_tsvector 
 --------------------------------
 'digoal':5,6 '四大天王':1,2,3,4 
 (1 row)

特性例子




 全文检索类型的限制：




 1. The length of each lexeme must be less than 2K bytes




 规则化后的lexeme必须要与2K，




 2. The length of a tsvector (lexemes + positions) must be less than 1 megabyte




 tsvector类型长度不超过1MB。




 这两个限制见src/include/tsearch/ts_type.h




 typedef struct




{




 uint32




 haspos:1,








len:11,



/* MAX 2Kb */








pos:20;



/* MAX 1Mb */




 } WordEntry;




 3. The number of lexemes must be less than 2^64




 一个tsvector中不能存储超过2的64次方个lexeme.

特性例子


 4. Position values in tsvector must be greater than 0 and no more than 16,383 
 位置值不超过 
 #define MAXENTRYPOS (1<<14)


 。。。 
 if (res - a >= MAXNUMPOS - 1 || 
 WEP_GETPOS(*res) == MAXENTRYPOS - 1) 
 break; 
 。。。 
 5. No more than 256 positions per lexeme 
 每个lexeme不能超过256个位置。 
 #define MAXNUMPOS (256) 
 6. The number of nodes (lexemes + operators) in a tsquery must be less than 32,768 
 tsquery，lexeme和operators累计不能超过32,768个。

特性例子




 分词性能指标 
 英语分词性能：~ 900万 words每秒 ( Intel(R) Xeon(R) CPU 
 中文分词性能：~ 400万 字每秒 ( Intel(R) Xeon(R) CPU



X7460 @ 2.66GHz ) X7460 @ 2.66GHz )




 英文分词+插入性能：~ 666万 字每秒 ( Intel(R) Xeon(R) CPU 
 中文分词+插入性能：~ 290万 字每秒 ( Intel(R) Xeon(R) CPU



X7460 @ 2.66GHz ) X7460 @ 2.66GHz )




 查询性能和查询条件，数据量都有关系，没有很好的评估标准，大多数查询可以在毫秒级返回。

特性例子


 pg_trgm 
 近似度匹配，支持GIN索引检索


 字符串前后各加2个空格，每连续的3个字符一组进行拆分并去重复，不区分大小写 
 digoal=> select show_trgm('digoal'); 
 show_trgm 
 ------------------------------------
 {" d"," di","al ",dig,goa,igo,oal}


 digoal=> select show_trgm('DIGOAL123456'); 
 show_trgm 
 ------------------------------------------------------------
 {" d"," di",123,234,345,456,"56 ",al1,dig,goa,igo,l12,oal} 
 (1 row) 
 近似度算法 
 两个字符串相同trigram个数 除以 总共被拆成多少个trigram

特性例子


 大于等于近似度限制时，返回TRUE，同样可根据近似度高低排名，反映检索条件和数据之间的相关度。 
 digoal=> select show_limit(); 
 show_limit 
 -----------
 0.3 
 (1 row) 
 postgres=# select similarity('postregsql','postgresql'); 
 similarity 
 -----------
 0.375 
 (1 row)


 postgres=# select 'postregsql' % 'postgresql'; -- 在记忆出现问题时，例如输错几个依旧可以匹配


 ?column? 
 ---------
t 
 (1 row)

特性例子


 域或约束 
 例子，限制输入格式，确保输入为一个正确的EMAIL地址。


 域（不支持数组） 
 postgres=# create domain email as text constraint ck check (value ~ '^.+@.+\..+$'); 
 CREATE DOMAIN 
 postgres=# 
 postgres=# create table test1(id int, mail email); 
 CREATE TABLE 
 postgres=# insert into test1 values (1, 'abc'); 
 ERROR: value for domain email violates check constraint "ck" 
 postgres=# insert into test1 values (1, 'digoal@126.com'); 
 INSERT 0 1

特性例子


 域或约束 
 例子，限制输入格式，确保输入为一个正确的EMAIL地址。


 约束（支持数组，需自定义操作符配合数组约束使用） 
 postgres=# create or replace function u_textregexeq(text,text) returns boolean as $$ 
 select textregexeq($2,$1); 
 $$ language sql strict;


 postgres=# CREATE OPERATOR ~~~~ (procedure = u_textregexeq, leftarg=text,rightarg=text); 
 CREATE OPERATOR 
 postgres=# select 'digoal@126.com' ~~~~ '^.+@.+\..+$'; 
 -[ RECORD 1 ] 
 ?column? | f 
 postgres=# select '^.+@.+\..+$' ~~~~ 'digoal@126.com'; 
 -[ RECORD 1 ] 
 ?column? | t

特性例子


 域或约束 
 例子，限制输入格式，确保输入为一个正确的EMAIL地址。


 约束（支持数组，需自定义操作符）


 postgres=# create table t_email(id int, email text[] check ('^.+@.+\..+$' ~~~~ all (email)));


 CREATE TABLE 
 postgres=# insert into t_email values (1, array['digoal@126.com','a@e.com']::text[]); 
 INSERT 0 1 
 postgres=# insert into t_email values (1, array['digoal@126.com','a@e']::text[]); 
 ERROR: new row for relation "t_email" violates check constraint "t_email_email_check" 
 DETAIL: Failing row contains (1, {digoal@126.com,a@e}).

特性例子




 GIN索引 
 例子，快速检索某个值包含在哪些数组中


 支持数组，全文检索等类型



pending list pages



element key pages Btree



pending page信息 无序

key->5, ItemPoint->(0,3) ... key->6, ItemPoint->(0,3)



Merge



leaf page信息 key->1, ItemPoint->(0,1) ... key->5, ItemPoint->list1



heap data

ctid(0,1) [1,2,3,4,5] ctid(0,2) [5,6,7,8,9] ctid(0,3) [5,6,10,11,12]



posting list pages



list1 (0,1),(0,2),(0,3) ...

特性例子


 GIN索引应用场景举例


 label 
 match 
 con1 && con2 && !con3 || con4 ....

特性例子




 BRIN(block range index)索引（lossy索引） 
 例子，流式大数据的快速范围检索




 假设crt_time存储时间值



datafile

block1






 BRIN索引（非常小） 
 1-127 mintime=jQuery110204035456897690892_1436145266768 maxtime=? 
 128-255 mintime=? maxtime=? 
 ... mintime=? maxtime=?



3 ... n ... ...




 查询select * from tbl where crt_time between ? and ?; or where crt_time = ?; 
 扫描符合条件的范围区块，recheck条件。 
 适合流式数据字段，不适合随机数据字段



x

特性例子


 钩子, 例如 auth_delay


 _PG_init, 模块启动时调用 
 _PG_fini, backend process 退出前调用 
 配置, 随数据库启动的模块 
 shared_preload_libraries = '....'

特性例子


 钩子, 例如 auth_delay


 src/include/libpq/auth.h 
 /* Hook for plugins to get control in ClientAuthentication() */ 
 typedef void (*ClientAuthentication_hook_type) (Port *, int); 
 extern PGDLLIMPORT ClientAuthentication_hook_type ClientAuthentication_hook;

特性例子


 钩子, 例如 auth_delay 
 src/backend/libpq/auth.c 
 /* 
 * This hook allows plugins to get control following client authentication, 
 * but before the user has been informed about the results. It could be used 
 * to record login events, insert a delay after failed authentication, etc. 
 */ 
 ClientAuthentication_hook_type ClientAuthentication_hook = NULL; 
 void 
 ClientAuthentication(Port *port) 
{ 
 ...... 
 if (ClientAuthentication_hook) 
 (*ClientAuthentication_hook) (port, status);

特性例子


 /* 
 * Check authentication 
 */ 
 static void 
 auth_delay_checks(Port *port, int status) 
{ 
 /* 
 * Any other plugins which use ClientAuthentication_hook. 
 */ 
 if (original_client_auth_hook) 
 original_client_auth_hook(port, status);


 /* 
 * Inject a short delay if authentication failed. 
 */ 
 if (status != STATUS_OK) 
{ 
 pg_usleep(1000L * auth_delay_milliseconds); 
} 
}

特性例子


 /* 
 * Module Load Callback 
 */ 
 void 
 _PG_init(void) 
{ 
 /* Define custom GUC variables */ 
 DefineCustomIntVariable("auth_delay.milliseconds", 
 "Milliseconds to delay before reporting authentication failure", 
 ............. 
 /* Install Hooks */ 
 original_client_auth_hook = ClientAuthentication_hook; 
 ClientAuthentication_hook = auth_delay_checks; 
}

特性例子


 其他钩子 
 auto_explain, pg_stat_statement, passwordcheck, sepgsql

特性例子


 直接修改元表，绕过rewrite table，例如修改numeric精度, varchar长度. 
 (修改元表有风险，操作需谨慎) 
 postgres=# create table tbl(id int, c1 numeric(6,3), c2 varchar(5)); 
 postgres=# insert into tbl select 1,100.5555,'test' from generate_series(1,5000000); 
 INSERT 0 5000000 
 postgres=# select * from tbl limit 1; 
 id | c1 | c2 
 1 | 100.556 | test 
 postgres=# alter table tbl alter column c1 type numeric(6,2); 
 Time: 4362.482 ms -- rewrite table,同时精度压缩 
 postgres=# select * from tbl limit 1; 
 id | c1 | c2 
 1 | 100.56 | test 
 postgres=# alter table tbl alter column c1 type numeric(6,3); 
 Time: 4565.196 ms -- rewrite table,同时精度无法恢复 
 postgres=# select * from tbl limit 1; 
 id | c1 | c2 
 1 | 100.560 | test

特性例子


 postgres=# alter table tbl alter column c2 type varchar(1); 
 WARNING: value:test too long for type character varying(1) 
 ... -- rewrite table,同时字符串截断 
 ALTER TABLE 
 postgres=# select * from tbl limit 1; 
 id | c1 | c2 
 ----+---------+---
 1 | 100.560 | t 
 (1 row)


 postgres=# alter table tbl alter column c2 type varchar(6); 
 ALTER TABLE 
 Time: 0.793 ms -- 不需要rewrite table. 
 postgres=# select * from tbl limit 1; 
 id | c1 | c2 
 ----+---------+---
 1 | 100.560 | t

特性例子


 变长字段长度相关的元表信息 
 postgres=# select atttypmod from pg_attribute where attrelid='tbl'::regclass and attname='c1'; 
 atttypmod | 393223 -- 需计算 
 postgres=# select atttypmod from pg_attribute where attrelid='tbl'::regclass and attname='c2'; 
 atttypmod | 10 -- varchar变长字段, 附加4字节头, 6+4=10.


 numeric精度转换 
 postgres=# select oid from pg_type where typname='numeric'; 
 1700 
 postgres=# select information_schema._pg_numeric_scale(1700,393223); 
3 
 postgres=# select information_schema._pg_numeric_precision(1700,393223); 
6 
 postgres=# select information_schema._pg_numeric_precision_radix(1700,393223); 
 10 
 postgres=# select numerictypmodin('{6,3}'); -- 从精度计算typmode 
 393223

特性例子


 postgres=# select numerictypmodin('{6,2}'); 
 393222 
 postgres=# select numerictypmodin('{6,4}'); 
 393224 
 修改元表 
 postgres=# update pg_attribute set atttypmod=393222 where attrelid ='tbl'::regclass and attname='c1'; -- 更新为numeric(6,2) 
 postgres=# select * from tbl limit 1; -- 不需要rewrite table, 不影响已有数据 
 id | c1 | c2 
 1 | 100.556 | test 
 postgres=# insert into tbl values (0,100.55555,'test'); -- 精度修改已生效 
 postgres=# select * from tbl where id=0; 
 id | c1 | c2 
 0 | 100.56 | test -- 精度修改已生效 
 postgres=# update pg_attribute set atttypmod=393224 where attrelid ='tbl'::regclass and attname='c1'; -- 更新为numeric(6,4) 
 postgres=# insert into tbl values (0,1.55555,'test'); 
 postgres=# select * from tbl where id=0; 
 id | c1 | c2 
 0 | 1.5556 | test -- 精度修改已生效 
 ...

特性例子


 postgres=# update pg_attribute set atttypmod=5 where attrelid ='tbl'::regclass and attname='c2'; -- 修改为varchar(1) 
 postgres=# select * from tbl where id=0; -- 不需要rewrite table, 现有数据不变 
 id | c1 | c2 
 0 | 100.56 | test 
 0 | 1.5556 | test 
 postgres=# insert into tbl values (0,1.55555,'test'); 
 -- 忽略, 此处因我修改过源码，所以允许插入，但是会TRUNC, 正常情况应该是ERROR不允许插入 
 WARNING: value:test too long for type character varying(1) 
 INSERT 0 1 
 postgres=# insert into tbl values (0,1.55555,'t'); 
 INSERT 0 1 
 postgres=# select * from tbl where id=0; 
 id | c1 | c2 
 ----+--------+-----
 ... 
 0 | 1.5556 | t 
 0 | 1.5556 | t

特性例子


 postgres=# update pg_attribute set atttypmod=10 where attrelid ='tbl'::regclass and attname='c2'; -- 修改为varchar(6) 
 UPDATE 1 
 Time: 0.815 ms 
 postgres=# insert into tbl values (0,1.55555,'testtt'); 
 INSERT 0 1 
 Time: 0.536 ms 
 postgres=# select * from tbl where id=0; 
 id | c1 | c2 
 ----+--------+-------
 0 | 100.56 | test 
 0 | 1.5556 | test 
 0 | 1.5556 | t 
 0 | 1.5556 | t 
 0 | 1.5556 | testtt 
 (5 rows)

特性例子


 可靠性例子

begin; SQLs; commit; -- 区分同步提交 / 异步提交，同步模式支持本地，全局，远程write，远程flush

-- 异步模式等待write完成，不需要等待fsync完成 -- 因为wal是顺序写入，即使使用异步，数据库crash后可能丢事务，但是绝对不会出现数据不一致。



wal wirter process wal buffer

write,fsync (持久化存储)

XLOGs



recovery



XLOGs archive



startup process

PG cluster

特性例子


 pg_rewind 
 使用重做日志处理脑裂


 当备机激活后 
 主机发生了数据变更



老的主 库

新的主 库

特性例子


 pg_rewind工作机制 
 src/bin/pg_rewind 
 1. 首先获得备机激活的时间线(记录了promote时的xlog offset) 
 2. 根据备机激活时的xlog offset, 在老主库上找到这个offset之前的最后一个checkpoint的xlog offset 
 3. 在老主库根据这个checkpoint offset, 找到自此以后老主库产生的所有的XLOG. （如果有归档，需手工拷贝到pg_xlog目

录） 
 4. 从这些XLOG中解析出对应的数据块位置. 
 5. 从新主库将这些数据块抓取过来, 并覆盖掉老主库上的这些数据块. (同时删除老库的新增块,即新主库没有的块.) 
 6. 从新主库拷贝除数据文件以外的所有文件 (如clog, etc等) 到老的主机. 
 7. 在老主库创建backup label, 告知需要从脑裂位置前的最后一个checkpoint开始恢复。 
 8. 现在pg_rewind工作结束.


 pg_rewind退出后只能到达以上状态, 以下步骤需要手工执行. 
 9. 修改老主库的配置文件, 例如 postgresql.conf, recovery.conf, pg_hba.conf 以成为新主机的standby. 
 10. 特别需要注意配置 restore_command, 因为新主库在发生promote后产生的XLOG可能已经归档了. 
 11. 启动老主库, 开始恢复.

特性例子


 流复制 
 块级XLOG传输，支持远程异步复制，远程内存同步复制，远程FLUSH同步复制


 例子，hot_standby，读写分离，HA，容灾，实时归档，基于standby的增量备份，延迟hot_standby 
 流复制对性能的影响点：读xlog，网络占用。（ ~ 7% , 视现实场景而定） 
 流复制的延迟：即时传输xlog，延迟取决于网卡和网络设备的处理能力(～ xxx 微秒)，以及primary产生XLOG的速度。 
 单次send xlog请求的最大数据量： #define MAX_SEND_SIZE (XLOG_BLCKSZ * 16)



primary



wal wirter process



wal buffer



write,fsync



XLOGs



wake up too

wal sender process



standby wal receiver process



write, fsync



XLOGs



startup process

PG cluster

特性例子


 同步流复制 ordered waiting queue


 queue 
 PROCa, XactCommitLSN 
 PROCb, XactCommitLSN 
 ....



Release waiters



primary



wal wirter process



wal buffer



write,fsync



XLOGs



wake up too

wal sender process



sync standby wal receiver process



write, fsync



feedback LSN



XLOGs



startup process

PG cluster

特性例子


 PostGIS 
 地理位置


 模块化 
 http://pgxn.org, contrib, http://pgfoundry.org


 逻辑复制 
 londiste3, slony-I, bucardo, ......

特性例子


 PostGIS 
 地理位置

LINESTRINGs



GEOMETRYCOLLECTIONs



MULTIIPOLYGONs



MULTILINESTRINGs



POLYGONs

特性例子


 PostGIS 
 内部，边界，外部

特性例子


 gist, spgist索引; 平面/立体几何类型 
 例子, exclusion约束 
 CREATE TABLE test(id int,geo circle,EXCLUDE USING GIST (geo WITH pg_catalog.&&)); 
 INSERT INTO test values(1,'<(0,0),2>'::circle); 
 INSERT INTO test values(1,'<(4.1,0),2>'::circle); 
 INSERT INTO test values(1,'<(-1.9,0),2>'::circle); 
 ERROR: conflicting key value violates exclusion constraint "test_geo_excl" 
 DETAIL: Key (geo)=(<(-1.9,0),2>) conflicts with existing key (geo)=(<(0,0),2>).

c3 c1



c2

特性例子


 gist, spgist索引; 平面/立体几何类型 
 例子, KNN


 http://blog.163.com/digoal@126/blog/static/16387704020137610534650

特性例子


 gist, spgist索引; 平面/立体几何类型 
 例子, KNN


 create index idx_cust_jw_1 on cust_jw using gist(jwd);


 digoal=# select *,ST_Distance(jwd, ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163)) from cust_jw


 order by jwd <-> ST_Transform(ST_GeomFromText('POINT(120.19 30.26)', 4326), 2163);


 EXPLAIN 
 Index Scan using idx_cust_jw_1 on cust_jw (cost=0.14..54.44 rows=140 width=548) 
 Order By: (jwd <-> '0101000020730800004C94087D5D4F54C173AA7759E8FB5D41'::geometry) 
 (2 rows)

特性例子


 gist, spgist索引; 平面/立体几何类型 
 优化手段更多

特性例子


 gist, spgist索引; 平面/立体几何类型 
 优化手段更多

特性例子


 数据预热 
 保存当前shared buffer中包含的数据块的位置信息，重启数据库后自动预热这些数据。 
 源码


 contrib/pg_buffercache/pg_buffercache_pages.c 
 contrib/pg_prewarm/pg_prewarm.c


 扩展


 postgres=# create extension pg_buffercache; 
 postgres=# create extension pg_prewarm;


 保存buffer快照(forknumber=0代表main，即数据)


 postgres=# create table buf (id regclass,blk int8,crt_time timestamp); 
 postgres=# truncate buf; 
 postgres=# insert into buf select a.oid::regclass,b.relblocknumber,now() from pg_class a,pg_buffercache b where

pg_relation_filenode(a.oid)=b.relfilenode and b.relforknumber=0 order by 1,2; 
 INSERT 0 32685

特性例子




 重启数据库后的预热方法




 pg95@db-172-16-3-150-> pg_ctl restart -m fast




 pg95@db-172-16-3-150-> psql




 postgres=# select pg_prewarm(id,'buffer','main',blk,blk) from buf;




 验证




 postgres=# select a.oid::regclass,b.relblocknumber,relforknumber from pg_class a,pg_buffercache b where pg_relation_filenode(a.oid)=b.relfilenode and b.relforknumber=0 order by 1,2;




 oid | relblocknumber | relforknumber




 -----------------------------------------+----------------+---------------




 pg_default_acl_role_nsp_obj_index |



0| 0




 pg_tablespace



| 0| 0




 pg_shdepend_reference_index



|



0| 0




 ............

源码

源码



认证延迟,防暴力破解 记录慢查询的执行计划

不区分大小写的数据类型 数据链路

文件外部表接口 类key-value结构

解析heap/idx页头信息,元数据 强制密码复杂度策略



buffercache信息dump FSM信息dump 预热数据到shared buffer

SQL统计,调用次数,io,cpu,hit... 近似度计算和检索 服务端数据加密 从tuple head infomask解读行锁信息 表或索引的垃圾统计,空间统计 postgresql外部表接口

如何了解内核工作机制


 http://www.postgresql.org/developer/backend/ 
 https://wiki.postgresql.org/wiki/Backend_flowchart 
 http://doxygen.postgresql.org/

如何跟踪内核




 gdb




 /* #define COPY_PARSE_PLAN_TREES */




 stap,dtrace




 /* #define RANDOMIZE_ALLOCATED_MEMORY */




 57个自带探针(包含事务，锁，查询，BUFFER，排序，检查 点，WAL，等几个方面)，




 /* #define USE_VALGRIND */




 同时支持自定义探针 
 process function, need "gcc -g arg"




 VERBOSITY 
 日志输出包含代码位置




 log_error_verbosity = verbose




 MACRO 
 如 src/include/pg_config_manual.h




 会话信息输出包含代码位置 
 postgres=# \set VERBOSITY verbose




 /* #define HEAPDEBUGALL */ 
 /* #define ACLDEBUG */ 
 /* #define RTDEBUG */ 
 /* #define TRACE_SYNCSCAN */ 
 /* #define WAL_DEBUG */




 postgres=# s; 
 ERROR: 42601: syntax error at or near "s" 
 LINE 1: s; 
^ 
 LOCATION: scanner_yyerror, scan.l:1053




 /* #define LOCK_DEBUG */

用探针进行内核跟踪例子


 '--enable-dtrace' '--enable-debug' '--enable-cassert' 
 systemtap 例子 
 http://blog.163.com/digoal@126/blog/#m=0&t=1&c=fks_084068084086080075085082085095085080082075083081086071084

用探针进行内核跟踪例子


 simple query 
 每条SQL每次调用, 都需要query start, parse, rewrite, plan, execute.


 绑定变量 
 一次query parse, plan, 以后调用只需要执行execute.

函数跟踪例子




 global f_start[999999],f_stop[999999]




 if (f_start[a,c,e,b]) {




 f_stop[a,d] <<< t - f_start[a,c,e,b]




 probe




}



process("/opt/pgpool3.4.1/bin/pgpool").function("*@/opt/soft_bak/pgp 




ool-II-3.4.1/src/*").call { 
 f_start[execname(), pid(), tid(), cpu()] = gettimeofday_ns()




}




}




 probe timer.s(5) {




 foreach ([a,d] in f_stop @sum - limit 50 ) {




 probe




 printf("avg_ns:%d, sum_ns:%d, cnt:%d, execname:%s, pp:%s\n",



process("/opt/pgpool3.4.1/bin/pgpool").function("*@/opt/soft_bak/pgp @avg(f_stop[a,d]), @sum(f_stop[a,d]), @count(f_stop[a,d]), a, d)



ool-II-3.4.1/src/*").return { 
 t=gettimeofday_ns() 
 a=execname() 
 b=cpu()




} 
 exit() 
}




 c=pid()




 d=pp()




 e=tid()

oprofile




 分析耗时调用




 [root@digoal data06]# opreport -l -f -w -x -t 1




 Using /data06/oprofile_data/samples/ for samples directory.




 CPU: Intel Core/i7, speed 1995.14 MHz (estimated)




 Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000




 vma samples % app name



symbol name




 007827a0 2091381 26.6819 /opt/pgsql9.4.1/bin/postgres HeapTupleSatisfiesVacuum




 00490300 988600 12.6126 /opt/pgsql9.4.1/bin/postgres heap_page_prune




 0078a8c0 698665 8.9136 /opt/pgsql9.4.1/bin/postgres pg_qsort




 [root@digoal data06]# opreport -l -f -g -w -x -t 1 /opt/pgsql/bin/postgres




 Using /data06/oprofile_data/samples/ for samples directory.




 CPU: Intel Core/i7, speed 1995.14 MHz (estimated)




 Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000




 vma samples % linenr info



symbol name




 007827a0 2091381 26.7572 /opt/soft_bak/postgresql-9.4.1/src/backend/utils/time/tqual.c:1116 HeapTupleSatisfiesVacuum




 00490300 988600 12.6482 /opt/soft_bak/postgresql-9.4.1/src/backend/access/heap/pruneheap.c:174 heap_page_prune




 0078a8c0 698665 8.9387 /opt/soft_bak/postgresql-9.4.1/src/port/qsort.c:104 pg_qsort

oprofile




 [root@digoal data06]# opannotate -x -s -t 1 /opt/pgsql/bin/postgres -i HeapTupleSatisfiesVacuum|less




 。。。




 1879024 89.8461 : if (!HeapTupleHeaderXminCommitted(tuple))




 :{




 63 0.0030 :



if (HeapTupleHeaderXminInvalid(tuple))




:



return HEAPTUPLE_DEAD;




 : /* Used by pre-9.0 binary upgrades */




 18 8.6e-04 :



else if (tuple->t_infomask & HEAP_MOVED_OFF)




 。。。

进程结构



autovacuum launcher

autovacuum worker



logger



checkpointer



bgwriter



wal writer



archiver



stats



worker process

进程结构


 autovacuum launcher - 跟踪垃圾版本的阈值, 产生worker 进程. 
 autovacuum worker process - 回收垃圾(MVCC产生的tuple旧版本) 
 bgwriter - 将shared buffer脏数据写入文件 
 checkpointer - 创建检查点 
 pgarch - 归档历史xlog文件 
 pgstat - 收集并更新统计信息, 例如pg_stat_* , 更新, 写入, 删除次数, 数据块命中和未命中读次数等. 
 postmaster - 主进程, 监听, fork 所有其他子进程. 如backend process,... 
 fork_process - postgresql的fork()改写进程. 
 startup - 启动进程, 负责启动初始化以及数据库恢复. 
 syslogger - 数据库写日志进程 
 walwriter - 数据库写重做日志进程, 重做日志用于数据恢复. 
 backend process - 客户端交互进程, 当客户端连接PG时, 由master fork. 
 worker process - 9.4开始新增允许动态fork进程, 动态分配共享内存. 未来可用作多核并行处理.

文件结构


 drwx------ 8 pg93 pg93 4.0K Jun 28 16:09 base 默认表空间目录 
 drwx------ 2 pg93 pg93 4.0K Jul 23 14:38 global 集群的全局数据存储, 例如pg_database,

pg_tablespace, pg_roles, 控制文件, .... 
 drwx------ 2 pg93 pg93 4.0K Jul 16 08:35 pg_clog 事务提交状态信息 
 -rw------- 1 pg93 pg93 4.6K Jul 11 15:58 pg_hba.conf 认证配置 
 -rw------- 1 pg93 pg93 1.6K Jun 28 16:08 pg_ident.conf 系统用户名认证方法用户名和库用户名映

射关系. 
 drwx------ 2 pg93 pg93 48K Jul 23 14:38 pg_log 日志 
 drwx------ 4 pg93 pg93 4.0K Jun 28 16:09 pg_multixact multi transaction状态数据 
 drwx------ 2 pg93 pg93 4.0K Jul 23 14:38 pg_notify 异步消息LISTEN/NOTIFY状态数据 
 drwx------ 2 pg93 pg93 4.0K Jun 28 16:08 pg_serial 串行事务状态数据 
 drwx------ 2 pg93 pg93 4.0K Jun 28 16:09 pg_snapshots 事务镜像状态数据

文件结构


 drwx------ 2 pg93 pg93 4.0K Jul 23 14:38 pg_stat 统计信息持久化保存目录 
 drwx------ 2 pg93 pg93 4.0K Jul 23 15:12 pg_stat_tmp 统计信息临时目录 
 drwx------ 2 pg93 pg93 4.0K Jul 16 08:38 pg_subtrans 子事务状态信息 
 drwx------ 2 pg93 pg93 4.0K Jul 14 09:39 pg_tblspc 表空间软链接 
 drwx------ 2 pg93 pg93 4.0K Jun 28 16:09 pg_twophase 2PC事务状态信息 
 -rw------- 1 pg93 pg93 4 Jun 28 16:08 PG_VERSION 版本文件 
 drwx------ 3 pg93 pg93 20K Jul 23 14:37 pg_xlog 重做日志文件 
 -rw------- 1 pg93 pg93 21K Jul 16 11:12 postgresql.conf 配置文件 
 -rw------- 1 pg93 pg93 35 Jul 23 14:38 postmaster.opts 记录数据库启动参数 
 -rw------- 1 pg93 pg93 70 Jul 23 14:38 postmaster.pid 记录数据库启动进程信息, 包括进程号, $PGDATA, 监

听, shmid.等 
 -rw-r--r-- 1 pg93 pg93 4.7K Jun 28 16:08 recovery.done 恢复文件.conf表示下次启动恢复, .done表示恢复完成. 
 srwx------ 1 pg93 pg93 0 Jul 23 14:38 .s.PGSQL.5432 unix sock文件 
 -rw------- 1 pg93 pg93 42 Jul 23 14:38 .s.PGSQL.5432.lock

文件结构


 ownership(privilege based), logical, physcial



role

table(s)

tablespace (s)

datafile(s) per object

main fork



index(es)



database(s)

schema(s)

large object(s)



toast(s)



OS directory



base表空间 16393数据库 38477文件main fork

pg93@db-172-16-3-150-> cd $PGDATA pg93@db-172-16-3-150-> ll base/16393/38447* -rw------- 1 pg93 pg93 50M Jul 23 14:38 base/16393/38447 -rw------- 1 pg93 pg93 96K Jul 23 14:38 base/16393/38447_fsm -rw------- 1 pg93 pg93 32K Jul 23 15:39 base/16393/38447_vm



vm fsm fork fork



init fork

如何做压力测试




 pgbench




 protocol for submitting queries (default: simple)




 -n, --no-vacuum do not run VACUUM before tests




 pgbench is a benchmarking tool for PostgreSQL.




 -P, --progress=NUM show thread progress report every NUM




 主要参数



seconds




 Usage:









 pgbench [OPTION]... [DBNAME]









 Benchmarking options:









 -c, --client=NUM number of concurrent database clients (default: 




1) 




-r, --report-latencies report average latency per command



-R, --rate=NUM



target rate in transactions per second



-s, --scale=NUM report this scale factor in output



-T, --time=NUM



duration of benchmark test in seconds



--aggregate-interval=NUM aggregate data over NUM seconds




 -C, --connect



establish new connection for each transaction 
 --sampling-rate=NUM fraction of transactions to log (e.g. 0.01 for




 -D, --define=VARNAME=VALUE



1%)








define variable for use by custom script




 Common options:




 -f, --file=FILENAME read transaction script from FILENAME 
 -h, --host=HOSTNAME database server host or socket directory




 -j, --jobs=NUM



number of threads (default: 1)




 -p, --port=PORT database server port number




 -l, --log



write transaction times to log file




 -U, --username=USERNAME connect as specified database user




 -M, --protocol=simple|extended|prepared

如何做压力测试




 模拟用户登录测试 
 创建测试表


略 
 生成测试数据


 编译测试SQL



insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time) select generate_series(1,20000000), 'digoal.zhou', '德哥', 'DBA', '1970-01-01' ,E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!', 'digoal@126.com', 276732431, clock_timestamp(), NULL;




 vi login.sql




 \setrandom userid 1 20000000




 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid;




 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr());




 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;




 使用pgbench进行测试




 pgbench -M prepared -n -r -f ./login.sql -c 16 -j 8 -h 172.16.3.33 -p 1921 -U digoal -T 180 digoal

如何做压力测试


 测试报告 
 postgres@db-172-16-3-150-> pgbench -M prepared -n -r -f ./login.sql -c 16 -j 8 -T 180 
 transaction type: Custom query 
 scaling factor: 1 
 query mode: prepared 
 number of clients: 16 
 number of threads: 8 
 duration: 180 s 
 number of transactions actually processed: 3034773 
 latency average: 0.949 ms 
 tps = 16858.777407 (including connections establishing) 
 tps = 16859.794913 (excluding connections establishing) 
 statement latencies in milliseconds: 
 0.002972 \setrandom userid 1 20000000 
 0.281929 select userid,engname,cnname,occupation,birthday,signname,email,qq from user_info where userid=:userid; 
 0.302971 insert into user_login_rec (userid,login_time,ip) values (:userid,now(),inet_client_addr()); 
 0.355463 update user_session set logintime=now(),login_count=login_count+1 where userid=:userid;

版本升级


 小版本升级 
 阅读release note 
 获取当前编译参数信息 
 pg_config 
 获取当前附加动态链接库信息 
 获取新版本，按照旧的编译参数重新编译并安装到新的目录 
 old /opt/pgsql9.4.0 new /opt/pgsql9.4.1 
 重新编译附加动态链接库 
 以新的数据库版本软件重启数据库 
 /opt/pgsql9.4.1/bin/pg_ctl restart -m fast

版本升级


 大版本升级


 方法1. 停机，pg_dump，pg_restore 
 数据量越大，升级越慢，索引越多，升级越慢。


 方法2. 配置逻辑复制，（使用如londiste3, slony-I工具)，停业务，全量同步非增量数据（如序列，函 数，。。。）。增量同步结束后，业务连到新的库。 
 停机时间短，但是对复制对象有一定要去，必须包含唯一键值。


 方法3. pg_upgrade 
 速度快，因为只需迁移catalog信息，并重新生成统计信息。 
 一般和流复制或文件系统快照结合使用，回滚也很方便。 
 适合数据量很大的数据库版本升级。 
 与小版本升级一样，编译参数建议一致，数据块大小必须一致。

备份恢复


 逻辑备份 
 输出文本或bin格式


 TOC 
 支持调整恢复顺序，恢复目标，如注释无需恢复的对象，调整恢复顺序。 
 10; 145433 TABLE map_resolutions postgres 
 ;2; 145344 TABLE species postgres 
 ;4; 145359 TABLE nt_header postgres 
 6; 145402 TABLE species_records postgres 
 ;8; 145416 TABLE ss_old postgres


 物理备份 
 在线备份数据文件，归档。


 基于时间点的恢复 
 恢复到指定时间点，TARGET name，或XID。

物理备份案例


 PITR 
 http://blog.163.com/digoal@126/blog/static/163877040201451894734122/


 目前PostgreSQL不支持块级别增量备份， 
 只能结合文件系统或存储层快照+流复制 
 例如ZFS，btrfs，存储快照。


 否则数据文件和表空间需要全备。

高可用


 基于共享存储 
 基于块设备复制 
 基于流复制

读写分离


 pgpool-II + 流复制


 支持HINT，强制发往MASTER 
 支持黑名单SQL，白名单SQL，指定发往主/备。


 CPU E5504为例，PGPOOL-II一次SQL请求额外开销0.5毫秒. 
 http://blog.163.com/digoal@126/blog/static/163877040201538071295

6/



APP pgpool-II

读写



Slave



Slave



Slave



HA VIPm



VIPs



Slave



stream rep



Master



Slave



stream rep

分布式


 plproxy 
 函数接口 
 不支持跨库事务 
 性能损耗小


 PG-XC, PG-XL 
 GTM容易成为瓶颈 
 目前还不成熟


 pgpool-II 
 不成熟


 pg_shard 
 citusdb 
 greenplum

数据挖掘


 PostgreSQL | Greenplum side 
 plr 
 MADlib


 R side 
 PivotalR 
 A Fast, Easy-to-use Tool for Manipulating Tables in Databases and A Wrapper of MADlib

MVCC


 xid 区分版本 
 txid snapshot 当前事务状态 
 clog 历史事务状态 
 t_infomask 标记行状态

MVCC


 xid 区分版本 
 txid snapshot 当前事务状态 
 clog 历史事务状态 
 t_infomask 标记行状态

MVCC


 会话A，多次更新，产生多个版本，注意cmin,cmax,xmin,xmax,ctid


 会话B，read committed

MVCC


 postgres=# update a set id=3 where id=2 returning *,ctid,cmin,cmax,xmin,xmax; 
 id | ctid | cmin | cmax | xmin | xmax 
 ----+------+------+------+------+-----
 (0 rows) 
 UPDATE 0 
 postgres=# update a set id=3 where id=1 returning *,ctid,cmin,cmax,xmin,xmax;


 -- waiting lock


 IF A commit, then B update 0 rows 
 IF A rollback, then B update 1 row

MVCC


 pageinspect插件观察：t_infomask2, t_infomask : 
 postgres=# SELECT lp,t_infomask2,t_infomask FROM heap_page_items(get_raw_page('a', 0)); 
 lp | t_infomask2 | t_infomask 
 ----+-------------+-----------
 1 | 16385 | 256 
 2 | 49153 | 8224 
 3 | 32769 | 10240 
 (3 rows)

vacuum freeze




 为什么需要freeze


 http://blog.163.com/digoal@126/blog/static/163877040201412282455978/ 
 xid, unsigned int32




 src/include/access/transam.h




 #define InvalidTransactionId



((TransactionId) 0)




 #define BootstrapTransactionId



((TransactionId) 1)




 #define FrozenTransactionId



((TransactionId) 2)




 #define FirstNormalTransactionId ((TransactionId) 3)




 #define MaxTransactionId



((TransactionId) 0xFFFFFFFF)



过去 未来



现在




 #autovacuum_freeze_max_age = 200000000 # 年龄到达后, 强制auto vauum freeze. 
 #vacuum_freeze_min_age = 50000000 # 手工vacuum时, 年龄大于这个的行的xid置为frozenxid. 
 #vacuum_freeze_table_age = 150000000 # 手工执行vacuum时, 如果表的年龄大于这个, 则扫描全表, 以降低表级年龄.

vacuum freeze


 http://blog.163.com/digoal@126/blog/static/163877040201412282455978/ 
 xid, unsigned int32


 src/backend/access/transam/varsup.c 
 if (IsUnderPostmaster && 
 TransactionIdFollowsOrEquals(xid, xidStopLimit)) 
{ 
 char *oldest_datname = get_database_name(oldest_datoid);


 /* complain even if that DB has disappeared */ 
 if (oldest_datname) 
 ereport(ERROR, 
 (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), 
 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"", 
 oldest_datname), 
 errhint("Stop the postmaster and use a standalone backend to vacuum that database.\n" 
 "You might also need to commit or roll back old prepared transactions.")));

vacuum freeze


 else 
 ereport(ERROR, 
 (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), 
 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u", 
 oldest_datoid), 
 errhint("Stop the postmaster and use a standalone backend to vacuum that database.\n" 
 "You might also need to commit or roll back old prepared transactions."))); 
} 
 else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit)) 
{ 
 char *oldest_datname = get_database_name(oldest_datoid);


 /* complain even if that DB has disappeared */ 
 if (oldest_datname) 
 ereport(WARNING, 
 (errmsg("database \"%s\" must be vacuumed within %u transactions", 
 oldest_datname, 
 xidWrapLimit - xid), 
 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"

vacuum freeze


 "You might also need to commit or roll back old prepared transactions."))); 
 else 
 ereport(WARNING, 
 (errmsg("database with OID %u must be vacuumed within %u transactions", 
 oldest_datoid, 
 xidWrapLimit - xid), 
 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n" 
 "You might also need to commit or roll back old prepared transactions."))); 
}

vacuum freeze




 src/backend/access/transam/transam.c




 /*




 * TransactionIdFollowsOrEquals --- is id1 logically >= id2?




 */




 bool




 TransactionIdFollowsOrEquals(TransactionId id1, TransactionId id2)




{








int32



diff;




 if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) 
 return (id1 >= id2);




 



}



diff = (int32) (id1 - id2); return (diff >= 0);

扩展功能


 自定义数据类型 
 如PostGIS，DNA库，化学模拟库，列存储


 自定义扫描方法 
 自定义索引访问方法 
 自定义采样方法 
 自定义并行计算 
 自定义外部表访问接口 
 自定义函数接口 
 。。。

TODO


 1. http://wiki.postgresql.org/wiki/Todo


 2. 基于WAL的多主复制 (9.4后可以实现) 
 3. 多CPU资源利用, 如并行查询, 并行创建索引等 (9.4后可以实现) 
 4. shared nothing 架构 (目前需要插件来实现如plproxy, pgpool-ii, pg_shard,...) 
 5. 基于块的增量基础备份 (目前只有基于WAL的增量备份, 目前需通过文件系统或存储级来实现, 如zfs,btrfs) 
 6. query cache, 如count(*)性能的提升, (目前可以通过插件实现,如pgpool) 
 7. toast 阈值可配置 (目前只能在编译时指定) 
 8. 使用ssd作为二级缓存, (目前需使用flashcache, bcache, lvm cache来代替) 
 9. 高性能的分区表，目前需要通过规则或触发器来实现分区，效率较低。 
 10. 数据库和系统双重缓存问题，目前仅wal支持DIRECT_IO，数据文件不支持，将来可以对数据文件加入

DIRECT_IO的配置，例如数据库启动时判断表空间所在的文件系统是否支持DIRECT_IO，如果支持则开 启，否则不开启。

TODO


 11. 目前移动表空间(如alter table tbl set tablespace newtbs;) 会产生大量的XLOG, 这个应该也是可以优化的, 例如通过文件迁移和swap filenode的方式.


 12. 目前没有表空间配额限制, (目前需通过文件系统使用配额来简单的限制. ) 
 13. 目前没有rotate table, 类似mongoDB的capped collection. 限制记录条数, 空间, 或记录时长, 超过限制就覆

盖最早的记录 . 
 14. PostgreSQL 目前一个集群只支持1个block_size, 这种不利于复杂场景的使用, 例如我们在同一个数据库

中有大量的OLTP请求, 同时还有大数据的频繁导入需求的情况下, 选择小的block_size或者大的block_size都 不合适, 如果能针对每个表指定不同的block_size的话可以很好的解决这一的问题. 当然如果数据库中存在 不同大小的block_size, 那么随之而来的改动是非常大的, 例如shared buffer也必须兼容不同大小的block size. Oracle 从9开始支持一个数据库中存在不同的数据块大小. 
 15. 当空间不足需要扩展数据文件时，一次只能扩展1个数据块，并且加排他锁。因此对于有高并发写入请 求的场景，如果使用了较小的数据块，会成为瓶颈。

软肋


 1. 读写并发管理通过新增行版本实现, 会带来垃圾数据, 对于非HOT更新的话, 还会引起索引更新, 导致索引 更容易膨胀。


 2. 这种MVCC机制对于频繁更新的应用场景, 假设同一条记录被更新10次的话, 会产生10个版本写xlog和 heap page的IO, 同时在VACUUM的时候仍带来写xlog以及heap page的IO. 垃圾回收不及时则发生膨胀。 
 对于高并发的批量更新场景尤其容易膨胀。 
 在大数据库中使用逻辑备份时, 备份过程中产生的垃圾数据无法被回收, 如果备份时间很长, 将导致数 据库膨胀比较厉害, 同时也会影响对象的freeze。 所以对大数据库建议使用pitr备份方式。 
 同时建议监控和控制长事务，因为垃圾回收进程不回收未关闭的最早事务后面产生的垃圾tuple。


 3. xid为版本号, 大小为32位, 因XID是需要复用的, 所以经过一定的事务分配后需要freeze。 
 当然此MVCC机制的好处也是有的, 例如 
 锁粒度很小, 
 容易实现repeatable read和ssi隔离级别, 
 会话层可实现跨越会话的一致性镜像, 
 行锁可以存储在行头, 不需要耗费内存, 不需要升级锁等, 
 不会出现热点块，因为更新后的新版本可能在其他块了。

性能优化方法


 前期 
 认识数据库软肋，系统设计时尽量规避 
 设计时应考虑，良好的数据库可扩展性，如分库，读写分离 
 合理的硬件架构；配置合理的数据库，操作系统，存储参数 
 遵循管理规范，开发规范


 后期 
 如何快速找到造成性能问题的SQL 
 pg_stat_statements钩子

性能优化方法


 例子 
 如何配置合理的成本因子


 了解优化器成本计算方法 
 跟踪调试，并通过公式反向计算合理的成本因子 
 对于混合场景，（如有机械盘，SSD混合的场景）为不同的表空间设置不同的成本因子


 了解各种索引的原理，使用合理的索引 
 了解索引页的回收原理，理解为什么索引会膨胀 
 使用IOPS能力好的存储，缩短垃圾回收时间间隔 
 监控和控制长事务时长 
 日常维护


 垃圾回收 
 索引重建 
 设置合理的FILLFACTOR，配合HOT

数据库安全


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


 2. 密码复杂度策略 
 创建用户或修改用户密码时，强制限制密码的复杂度，例如密码长度，包含数字，字母，大小写，特殊字

符等，同时排除暴力破解字典中的字符串。 
 请参考, http://blog.163.com/digoal@126/blog/static/16387704020149852941586

数据库安全


 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) 
 请参考, http://blog.163.com/digoal@126/blog/static/16387704020149852941586


 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/blog/static/163877040201342233131835


 2. 认证过程加密 
 认证过程加密，指认证过程中，网络上传输的密码安全，如果使用数据库认证，请使用MD5方法(配置

pg_hba.conf)。确保网络中传输的是随机码和MD5加密后的MD5。

数据库安全


 三、数据安全 
 你的数据安全吗？如果你存储的敏感数据在数据库中是明文的，一旦数据库暴露，用户数据可能泄露，如

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

者加密在数据库端实现，解密在应用程序端实现）


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

数据库安全


 3. 函数代码加密 
 如果我们将业务逻辑放在数据库函数中处理的话，肯定不想让用户看到函数的内容。对于先编译后执行的

函数，例如C函数，是不需要加密的，但是，对于解释性语言函数如plpgsql，建议加密函数的内容。 
 目前enterprisedb有这个特性，社区版本的PostgreSQL没有这个特性。 
 请参考，http://blog.163.com/digoal@126/blog/static/163877040201256056352 
 http://www.cybertec.at/en/products/plpgsql_sec-encrypt-your-stored-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/blog/static/16387704020132131361949/


 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/blog/static/163877040201362402650341/


 6. 创建安全策略 
 与行安全策略类似，但是对表的记录权限控制更加精准细致，例如数据进入表前根据行的值判断数据是否允许插入，查

询时根据已经存在的记录，判断是否允许用户查询该记录。 
 请参考，http://blog.163.com/digoal@126/blog/static/16387704020153984016177/


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

数据库安全


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


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


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

数据库安全


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


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

数据库安全


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

数据库安全


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

数据库安全


 八、补丁 
 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/blog/static/16387704020153305256157/


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

数据库监控


 SQL性能指标监控 
 调用次数 
 CPU时间 
 最短耗时 
 最长耗时 
 平均耗时 
 耗时标准方差 
 接收记录总数 
 共享/本地内存 - 命中率，未命中率 
 共享/本地内存 - 产生脏块统计，驱逐脏块统计 
 临时块 - 读写统计 
 读，写数据块IO时间统计

数据库监控


 归档，备份，容灾，HA，磁盘使用率 
 进程数，CPU，网络，块设备利用率 
 对象膨胀 
 回归率，提交率，tps 
 数据库，表空间，对象SIZE变化趋势 
 对象级IO，缓存命中率，live,dead tuple, insert,update,delete,hot， 扫描次数指标监控 
 standby延迟 
 垃圾回收，检查点，分析点，时间间隔，单次时长监控 
 锁(高级锁，低级锁，轻量锁)，分布式事务监控 
 长事务，空闲事务，慢查询，SQL注入，执行计划监控 
 序列，触发器，事件触发器监控 
 任务调度监控 
 年龄监控 
 线性回归拟合预测

benchmark


 测试用例，测试环境请参考，http://blog.163.com/digoal@126/blog/static/163877040201541104656600/ 
 http://blog.163.com/digoal@126/blog/static/16387704020154431045764/ 
 http://blog.163.com/digoal@126/blog/static/163877040201542103933969/ 
 http://blog.163.com/digoal@126/blog/static/1638770402015463252387/ 
 http://blog.163.com/digoal@126/blog/static/16387704020154651655783/ 
 http://blog.163.com/digoal@126/blog/static/16387704020154653422892/ 
 http://blog.163.com/digoal@126/blog/static/16387704020154811421484/ 
 http://blog.163.com/digoal@126/blog/static/16387704020154129958753/

benchmark


 服务器 2009年购买的 IBM X3950 
 CPU 4 * 6核 Intel(R) Xeon(R) CPU X7460 @ 2.66GHz 
 内存 32 * 4GB DDR2 533MHz 
 硬盘 上海宝存 1.2TB Direct-IO PCI-E SSD 
 数据库 PostgreSQL 9.4.1 
 操作系统 CentOS 6.6 x64 
 文件系统 EXT4, noatime,nodiratime,nobarrier,discard


 更新,查询数据量 5000万, 基于主键的更新，查询。 
 插入数据量 100亿，插入带主键约束的表。

benchmark


 更新 tps 分布情况：

benchmark


 查询 tps 分布情况：

benchmark


 插入 tps 分布情况： 
 20亿后TPS依旧较平稳。

学习资料


 代码树： 
 http://doxygen.postgresql.org/


 代码提交集： 
 https://commitfest.postgresql.org/


 项目GIT： 
 http://git.postgresql.org


 PostgreSQL JDBC 驱动： 
 http://jdbc.postgresql.org


 PostgreSQL ODBC 驱动： 
 http://www.postgresql.org/ftp/odbc/versions/src/


 内核学习： 
 http://www.postgresql.org/developer/backend/




 PostgreSQL 扩展插件： 
 http://pgfoundry.org 
 http://pgxn.org/


 GUI工具(pgAdmin)： 
 http://www.pgadmin.org/


 安全漏洞： 
 http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=postgresql


 文档： 
 http://www.postgresql.org/docs/devel/static/index.html


 其他 
 http://blog.163.com/digoal@126/blog/static/163877040201412291

59715/

Q&A


 digoal.zhou 
 qq: 276732431 
 blog: http://blog.163.com/digoal@126