PostgreSQL 影片

Loading...
Google

2007-04-24

PostgreSQL 安全性更新版次發佈

更新:2007-04-25
對映章節:
國際官方站點新聞

內容:
PostgreSQL 全球性發展團隊安全性更新版次發佈.

更新追加一個特權擴張可被利用在 SECURITY DEFINER 函數, 命名為(SDF)的功能。這個追加修正有效在 8.2.4, 8.1.9, 8.0.13, 7.4.17, 及 7.3.19 版本中, 且這個特點的所有用戶均被強烈敦促在儘快獲取這些新產生的作用在您最新的較小次版本上並且遵守安裝教導。

這些較小版次的發布中, 並且包含著其它細部的修正與強化安全性, 因此所有用戶應該回顧發行筆記, 並計劃部署他們遵照的必要性。

一旦您進行更新後, 額外的步驟必需鞏固安全性在您的資料庫, 來達到本次更新的目的。請讀發行筆記(release notes)、CVE 資訊(CVE information), 和 TechDocs instructions 將指示您關於如何 LOCK DOWN 在您的安全定義器函數(SDF)中, 如果您使用著它們。

下載是使用在這位置裡:
Download Page

延伸閱讀:
http://www.postgresql.org/docs/techdocs.77.html

2007-04-23

pgpool-II使用指南-并发查询(二)(簡體)

更新:2007-
對映章節:
本文翻译自源代码包中的“pgpool-II Tutorial”文档

定义数据分布规则
我们将定义把pgbench生成的示例数据分布在三个数据库节点的分布规则,在这个小节中创建名为“bench_parallel”的数据库,并且使用“pgbench -i -s 3”生成示例数据。

在pgpool-II源代码的sample目录能够找到dist_def_pgbench.sql文件,使用这个文件创建数据分布规则,执行如下命令:
$ psql -f sample/dist_def_pgbench.sql -p 5432 pgpool

以下是dist_def_pgbench.sql文件内容的解释。

插入四行数据到数据表“dist_def”中。每个数据表(前边提到过的accounts、branches、tellers以及history)各自有一个不同的分布函数。分别为branches、tellers、accounts 定义bid、tid、aid作为他们的key-columns字段(这几个字段也是他们的主键),history以tid作为key-columns字段。

INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'branches',
'bid',
ARRAY['bid', 'bbalance', 'filler'],
ARRAY['integer', 'integer', 'character(88)'],
'pgpool_catalog.dist_def_branches'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'tellers',
'tid',
ARRAY['tid', 'bid', 'tbalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)'],
'pgpool_catalog.dist_def_tellers'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'accounts',
'aid',
ARRAY['aid', 'bid', 'abalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)'],
'pgpool_catalog.dist_def_accounts'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'history',
'tid',
ARRAY['tid', 'bid', 'aid', 'delta', 'mtime', 'filler'],
ARRAY['integer', 'integer', 'integer', 'integer', 'timestamp without time zone', 'character(22)'],
'pgpool_catalog.dist_def_history'
);


接下来,必须为每个表定义分布函数,不同的表可以使用同一个分布函数,并且使用过程语言(PL/pgSQL、PL/Tcl等等)定义而不是SQL。
下边是由pgbench -i -s 3生成的数据概要:

数据表名 数据行数
branches 3
tellers 30
accounts 300000
history 0

继续定义4个函数将上述数据平分到三个节点,根据给出的参数返回0、1或者2

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)
RETURNS integer AS $$
SELECT CASE WHEN $1 > 0 AND $1 <= 1 THEN 0 WHEN $1 > 1 AND $1 <= 2 THEN 1 ELSE 2 END; $$ LANGUAGE sql; CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_tellers(anyelement) RETURNS integer AS $$ SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0 WHEN $1 > 10 AND $1 <= 20 THEN 1 ELSE 2 END; $$ LANGUAGE sql; CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement) RETURNS integer AS $$ SELECT CASE WHEN $1 > 0 AND $1 <= 100000 THEN 0 WHEN $1 > 100000 AND $1 <= 200000 THEN 1 ELSE 2 END; $$ LANGUAGE sql; CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_history(anyelement) RETURNS integer AS $$ SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0 WHEN $1 > 10 AND $1 <= 20 THEN 1 ELSE 2 END; $$ LANGUAGE sql;


检验并发查询
重新启动pgpool-II载入pgpool.conf的变化,然后我们来检验一下并发查询是否在正常运行。

首先,命名为“bench_parallel”的分布数据库,通过pgpool-II创建到每一个节点上:
$ createdb -p 9999 bench_parallel

接下来,生成测试数据:
$ pgbench -i -s 3 -p 9999 bench_parallel

预想的数据分布情况:
数据表名 Key-Column名
节点1 节点2 节点3
branches bid 1 2 3
tellers tid 1 - 10 11 - 20 21 - 30
accounts aid 1 - 100000 100001 - 200000 200001 - 300000
history tid 1 - 10 11 - 20 21 - 30

简单的验证脚本,比较每个节点的结果和pgpool-II的结果:
$ for port in 5432 5433 5434 9999; do
> echo $port
> psql -c "SELECT min(aid), max(aid) FROM accounts" -p $port bench_parallel
> done

(accounts有30万数据,这个愚蠢的脚本不是我写的,除非你是超人,否则用这个脚本什么也不能做。)

今天不在状态,脑子很乱,可能是放假综合症发作。黄金周到来,暂时不再写什么东西,放松几天。后边可能(仅仅是可能)会写写关于pgbench方面的,毕竟前边的文章已经涉及到,了解一下也好。这几天的帖子都没有经过仔细校正,基本上写完就commit,难免有错别字语意不通之类。
这几天各种事情缠绕(真的是缠绕)着我,脑筋有点不够用,手头的project马上进入编码阶段,后边时间也许会很紧张没时间写东西。


延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

pgpool-II使用指南-并发查询(一)(簡體)

更新:2007-
對映章節:
本文翻译自源代码包中的“pgpool-II Tutorial”文档

正如我们前边提到的,复制系统把每一个查询转发到每个数据库节点,而这些节点都保存有全部数据。而并发查询(Parallel query)则是根据预定义的规则将特定范围的数据分布到各个节点。
为了能够启用pgpool-II的并发查询功能,必须设置另外一个叫做“System Database”的数据库(我们称之为SystemDB)。SystemDB保存决定数据如何在各节点中保存的用户定义规则,另一个用途是合并使用数据库链(dblink)从数据库节点返回的结果。

设置并发查询
将pgpool.conf文件中的parallel_mode参数设置为true开启数据库复制功能:
parallel_mode = true
这样并不能自动开始并发查询,pgpool-II还需要SystemDB以及分布规则来知道如何在各节点中分布数据。

另外,SystemDB使用数据库链连接pgpool-II,因此,需要设置listen_addresses参数好让pgpool-II接受这些连接请求:
listen_addresses = '*'

注意:并发查询和复制是不能共存的,使用并发查询功能时,replication_mode必须设置为false。此外,并发查询和复制采用不同的格式储存数据,这样前边我们创建的“bench_replication”数据库无法被重用(reuse)。
replication_mode = false
load_balance_mode = false


在本节中,parallel_mode设置为true、listen_addresses设置为'*'、replication_mode和load_balance_mode设置为false。

配置SystemDB
SystemDB仅仅是另一个安装有数据库链的数据库,数据表“dist_def”被用来保存数据分布规则。可以在单独的电脑中放置SystemDB,也可以与数据库节点之一共存。

我们将在5432端口的节点上创建SystemDB,下边是SystemDB的参数设置列表:
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''


事实上,这是pgpool.conf中的缺省设置。
然后创建叫做“pgpool”的用户,再创建所有者为“pgpool”的数据库“pgpool”:
$ createuser -p 5432 pgpool
$ createdb -p 5432 -O pgpool pgpool


安装数据链
接下来,必须将数据库链安装到“pgpool”数据库,它是包含在PostgreSQL源代码contrib目录下的工具之一。

执行如下命令安装数据库链:
$ USE_PGXS=1 make -C contrib/dblink
$ USE_PGXS=1 make -C contrib/dblink install


安装完毕以后,在“pgpool”数据库中定义数据库链函数。如果PostgreSQL安装在/usr/local/pgsql,dblink.sql(函数定义文件)会被放置在/usr/local/pgsql/share/contrib中,运行如下命令创建函数:
$ psql -f /usr/local/pgsql/share/contrib/dblink.sql -p 5432 pgpool

定义dist_def数据表
再接下来,定义数据库表“dist_def”存放数据分布规则。安装pgpool-II时,system_db.sql文件放置在/usr/local/share/system_db.sql(这里我们使用缺省安装路径),它包括了一些包括“dist_def”在内的特殊用途数据表,执行下边的命令创建dist_def数据表:
$ psql -f /usr/local/share/system_db.sql -p 5432 -U pgpool pgpool

在system_db.sql文件里,dist_def被创建在叫做pgpool_catalog的schema中,如果参数system_db_schema设置为其他的schema,需要相应的修改system_db.sql。

下边是dist_def的定义语句:
CREATE TABLE pgpool_catalog.dist_def (
dbname text, -- database name
schema_name text, -- schema name
table_name text, -- table name
col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- distribution key-column
col_list text[] NOT NULL, -- list of column names
type_list text[] NOT NULL, -- list of column types
dist_def_func text NOT NULL, -- distribution function name
PRIMARY KEY (dbname, schema_name, table_name)
);


dist_def中的每一行数据分为两个部分:

  • 数据分布规则(col_name、dist_def_func)
  • 数据表的meta-information(dbname、schema_name、table_name, col_list、type_list)
分布规则决定了数据如何在各节点中分布,也就是按照“col_name”字段的值分布。“dist_def_func”以“col_name”值为参数的函数,返回数据应该被储存在哪个节点的ID。

meta-information用来重写查询,并发查询必须重写查询以便能够将个节点返回的结果合并为一个结果。


延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

pgpool-II使用指南-复制(簡體)

更新:2007-04-25
對映章節:
本文翻译自源代码包中的“pgpool-II Tutorial”文档

(服务器采用上一节安装的结构,单机三DB)

配置复制系统
将pgpool.conf文件中的replication_mode参数设置为true开启数据库复制功能:
replication_mode = true

replication_mode设置为true时,pgpool-II将收到的查询请求转发到每一个数据库节点。

load_balance_mode设置为true时,pgpool-II在数据库节点之间分布SELECT查询。
load_balance_mode = true

在这里我们将这两个参数都设置为true。


验证复制系统
为了能够响应pgpool.conf的修改,必须重新启动pgpool-II。
在重新启动完成后,测试一下复制系统运行是否正常。

首先,创建一个用来复制的数据库,命名为“bench_replication”,它应该在每个节点上创建。
通过pgpool-II使用createdb命令,就会在每个节点创建数据库:
$ createdb -p 9999 bench_replication

接下来,使用带有-i选项的pgbench以预定义的数据表和数据初始化数据库:
$ pgbench -i -p 9999 bench_replication

下边的表格是pgbench -i 创建的数据表和数据的概要信息,如果数据表和数据在所有节点上被正确创建,复制系统将会正常工作。

数据表名 数据行数
branches 1
tellers 10
accounts 100000
history 0

用一个简单的shell脚本来检查每个节点,显示上述4个数据表的数据行数:
$ for port in 5432 5433 5434; do
> echo $port
> for table_name in branches tellers accounts history; do
> echo $table_name
> psql -c "SELECT count(*) FROM $table_name" -p $port bench_replication
> done
>done

关于复制的文档比较简单,就这么多。

题外话,我并不是专门作pgsql的,对它的了解也很肤浅,一边上班一边学习它而已。因为很少在实际工作中使用,所以经验很少。翻译这些东西,也是为了自己能够知道更多关于pgsqld的内容。如果有问题,可以mail给我,并请同时cc给阿益,大家共同探讨。同时我们也在寻求更多的交流渠道供大家使用,希望朋友越来越多,社群越来越大,加强交流使我们进步。

延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

pgpool-II使用指南-安装与配置(簡體)

更新:2007-04-24
對映章節:
本文翻译自源代码包中的“pgpool-II Tutorial”文档

安装pgpool-II
安装pgpool-II是件非常容易的事情,在解压缩的源代码tar包目录中运行如下命令:
$ ./configure
$ make
$ make install

configure命令收集系统信息并将它们用在编译过程中,可以传递命令行参数给configure脚本来控制它的行为,比如安装目录,缺省情况下将安装到/usr/local之下。
make命令编译源代码,make install安装编译后的可执行文件,在要安装的目录中必须具有写权限。
本指南将使用缺省安装目录/usr/local。

注意:pgpool-II需要PostgreSQL 7.4或者更高版本的libpq库(第三版本协议),如果configure脚本显示类似下边的错误信息,可能是没有安装libpq库或者它不支持第三版协议:
configure: error: libpq is not installed or libpq is old
(这里与前边讲最低支持6.4的说法冲突,看起来这个更可信。)

如果确信是支持第三版协议的libpq库,但是仍然收到这样的错误信息,可能是因为configure脚本没有正确识别它。
configure脚本在/usr/local/pgsql下搜索libpq库,如果PostgreSQL安装在其他目录,使用--with-pgsql或者是--with-pgsql-includedir和--with-pgsql-libdir命令行参数来指定它。

配置文件
pgpool-II的配置参数保存在pgpool.conf文件中,每一行采用"parameter = value"格式。安装pgpool-II时,自动创建pgpool.conf.sample文件,推荐将它拷贝并且重命名为pgpool.conf再编辑:
$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf

pgpool-II仅使用9999端口接受本地连接,如果需要接受其他主机的连接请求,将listen_addresses设置为“*”:
listen_addresses = 'localhost'
port = 9999

本指南将使用缺省参数。

配置PCP命令
pgpool-II有一个出于管理目的可以通过网络查询数据库节点信息、关闭pgpool-II等功能的接口(interface)。使用PCP命令首先必须通过用户验证,它不同于PostgreSQL的用户验证,需要在pcp.conf中定义用户名和密码。文件中的用户名/密码成对出现在每一行,用冒号隔开,密码是md5格式:
postgres:e8a48653851e28c69d0506508fb27fc5

安装pgpool-II时,自动建立pcp.conf.sample文件,推荐拷贝并且重命名以后再编辑:
$ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf

使用pg_md5命令可以将密码加密为md5格式,它是作为pgpool-II可执行部分安装的,pg_md5使用一个文本作为参数,然后显示出这个文本的md5加密文本。
例如: 将“postgres”作为参数传递给pg_md5,pg_md5将把md5加密过文本打印到标准输出:
$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5


PCP命令通过网络执行,因此必须在pcp.conf文件中使用pcp_port设置监听端口。
本指南将使用pcp_port的缺省值:
pcp_port = 9898

设置数据库节点
现在,我们要设置pgpool-II的后端数据库,它们可以跟pgpool-II在同一台主机上,也可以在独立的机器上。如果放在同一主机之上,每个数据库服务器必须使用不同的端口。如果放在其他独立的机器上,必须设置它允许接受来自pgpool-II的连接请求。

本指南中,我们将在pgpool-II的同一个服务器上放置三个数据库,分别使用5432、5433、5434端口。在pgpool-II的配置文件pgpool.conf中作如下设置:

backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_hostname2 = 'localhost'
backend_port2 = 5434
backend_weight2 = 1

backend_hostname、backend_port、backend_weight分别设置节点的主机名、端口号、负载平衡比率,每一个参数名字符串的最后必须描述一个从0开始(1、2、3···)的节点ID。

backend_weight参数全部设置为1,意味着SELECT查询将平均分配到三个数据库中。

启动/关闭pgpool-II

启动pgpool-II,在终端之上使用如下命令:
$ pgpool
这个命令会把日志输出在终端上,因此推荐使用如下选项:
$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
使用-n选项,pgpool-II作为非deamon进程运行,不会与终端脱离,这个选项需要将日志信息重定向至某个文件中。选项-d指令生成debug信息。

停止pgpool-II,使用如下命令:
$ pgpool stop
如果有客户端正在连接,pgpool-II会等待他们断开,然后才停止。如果需要强制终止,使用下边的命令:
$ pgpool -m fast stop


延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

pgpool-II不能处理的语句(簡體)

更新:2007-04-24

INSERT
不能在分区字段中使用DEFAULT,比如,如果x是数据表t的分区字段,下边的语句无效:
INSERT INTO t(x) VALUES (DEFAULT);
同样,函数也不能作为值使用:
INSERT INTO t(x) VALUES (func());
在这里必须使用常量。
另外,也不能支持SELECT INTO和INSERT INTO ... SELECT 语句。

UPDATE
如果更新分区列可能会丧失数据一致性,pgpool-II不能对更新过的数据重新分区。
如果查询因为违反约束(constraint)而引发错误,那么事务不能回滚(rollback)。
如果从WHERE子句中调用函数,更新可能无法正确执行。比如:
UPDATE branches set bid = 100 where bid = (select max(bid) from beances);

SELECT ... FOR UPDATE
如果从WHERE子句中调用函数,查询可能无法正确执行。比如:
SELECT * FROM branches where bid = (select max(bid) from beances) FOR UPDATE;

COPY
不支持COPY BINARY,也不支持从文件中copy,只能支持COPY FROM STDIN和COPY TO STDOUT。

ALTER/CREATE TABLE
更新分区规则(partition rule)以后,必须重新启动pgpool-II从系统DB中读入新设置。

Transaction
在事务块中执行的SELECT语句将被放在分离的事务块中执行,例如:
BEGIN;
INSERT INTO t(a) VALUES (1);
SELECT * FROM t ORDER BY a; <-- 上边的INSERT结果在这个SELECT语句中不可见 END;


Views / Rules
对于视图和规则,后端数据库将创建相同的定义。
SELECT * FROM a, b where a.i = b.i
像上边这样的联合(join)将在每一个后端数据库运行,然后合并它们的结果,无法建立跨越节点的视图和规则。
(看起来这里有个问题,如果存在分区,那么a和b的必须基于i而且必须一样。)

Functions / Triggers
相同的函数定义在每一个后端数据库创建,跨越节点的联合(join)和其它节点上的数据无法在函数内部操作。

Extended Query Protocol
不支持被JDBC驱动等程序使用的扩展查询协议(extended query protocol),必须使用简单查询协议(simple query protocols)。

Multi-byte Characters
pgpool-II不能进行不同字符集之间的转换,因此客户端、后端数据库和pgpool-II系统DB的字符集编码必须相同。

Deadlocks
跨越后端服务器的死锁无法被检测,例如:
(tellers数据表按照以下规则分区
-partition)
tid <= 10 --> node 0
tid >= 10 --> node 1
A) BEGIN;
B) BEGIN;
A) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
A) SELECT * FROM tellers WHERE tid = 1 FOR UPDATE;
B) SELECT * FROM tellers WHERE tid = 11 FOR UPDATE;


这个例子,单一节点无法检测思锁,pgpool-II会一直等待响应,这个现象发生在其他检索获得行级锁(row level lock)时。可以设置replication_timeout参数避免这个问题
同样,如果某个节点发生死锁,每个节点的事务状态会变得不一致,所以,如果检测到死锁pgpool-II会中止处理。
pool_read_kind: kind does not match between master(84) slot[1] (69)

Schemas
public之外的schema的对象必须用全名引用:
schema.object
否则,当设置路径set search_path = xxx并且在查询中省略schema名时,pgpool-II无法解析正确的schema。

关于pgpool-II的一些特点就写完了,后边准备写一点使用指南。
建议不要使用分区功能,因为它看起来很不健壮,会带来意想不到的问题,相信大家从文档中也能看到这一点。我认为它更适合于大型查询系统而不是有频繁更新的地方,不然维护数据一致性对采用它的project来讲是一个非常繁重的工作,而且有太多限制,有些几乎是致命的,会导致某些功能根本无法实现。


延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

About pgpool-II(簡體)

更新:2007-04-23

题外话:pgpool以及pgpool-II又是日本人对pgsql的贡献,他们真的做了大量工作。

pgpool的相关网站请在延伸阅读里边查找,这篇文章是基于pgpool-II文档所写,来自源代码包,完整版本请自行下载观看,时间所限我只能简单提及部分内容。

什么是pgpool-II
pgpool-II是一个工作于PostgreSQL服务器和PostgreSQL客户端之间的中间件(middleware),具有如下特性:

pgpool-II使用PostgreSQL的前端(frontend)和后端(backend)协议进行会话,在它们之间维持一个连接。这样,数据库应用程序(前端)会认为pgpool-II是一个真实的PostgreSQL服务器,而服务器(后端)会认为pgpool-II是一个客户端。因为pgpool-II对于服务器和客户端是透明的,一个现存的数据库复制系统几乎不用作任何改动就可以同pgpool-II协同工作。

支持平台
pgpool-II可以在Linux、Solaris、FreeBSD以及大多数类UNIX架构上正常运行,目前还不能支持Windows平台。此外需要PostgreSQL 6.4或者更高版本,如果想使用并发查询,则必须使用7.4或者更高版本。

pgpool-II功能与工作模式支持矩阵
这是很重要的部分,因为涉及到pgpool-II的运行设置,我只简单放个对应矩阵,其它请自行查阅文档。此外值得注意:这里的O和X并不是能不能够支持,比如复制模式也能支持“故障恢复”。
功能/模式 raw 模式 连接池模式 复制模式 主从模式 并发查询模式
连接池 X O O O O
复制 X X O X X
负载平衡 X X O O X
Degeneration X X O O X
故障恢复 O O X X X
并发查询 X X X X O
需要服务器数目 1 or higher 1 or higher 2 or higher 2 or higher 2 or higher
是否需要系统DB? no no no no yes

制约
验证与访问控制
复制模式下的函数等对象
不能保证那些每次查询会返回不同值的数据(比如:随机数、事务ID、OID、
SERIAL、序列号、CURRENT_TIMESTAMP)能够正确地在多个后端节点中被复制。
CREATE TEMP TABLE
创建的临时表不会在会话(session)结束后删除,这是因为连接池的原因,从后端服务器看起来会话仍然在活动。为避免这一点,必需显式使用DROP TABLE删除临时表,或者在事务块中使用CREATE TEMP TABLE ... ON COMMIT DROP创建临时表。
语句
部分语句不能被
pgpool-II处理。(比较多,准备另开篇写)

Over.

写在最后,大家如果有什么好的心得或者翻译文章,请发mail给阿益或者给我,分享知识才能得到更多知识
我的文章一般都写得很匆忙,难免有些错误和不适当的表达,如果发现请一定指出好让我校正,在blog留言即可。又或者看完以后有些什么建议也请写下来,无论是批评还是建议,都是对我的鼓励。翻译过程也是学习过程,您如果发现有什么关于pgsql比较有趣的东西,也请来信告诉我。
英文技术文档阅读对程序员来讲我认为是基本技能,而我也没有时间去翻译全部细节,所以只写一点我知道的肤浅内容,更多内容请大家自行阅读相关英文,一般我都会给出连接地址。

延伸閱讀(Link):
pgpool官方英文网站
pgpool-II官方英文网站

2007-04-18

[台灣應用實列]從 Oracle 遷移到 PostgreSQL

更新:2007-04-18
對映章節:
作者簡介: ingram, chen
Java狂,熱衷 server-side open source 技術。現於中研院某角落謀有一職,兼任 DinBenDon
長。(本文已連絡原著作者, 經同意後轉載.)

阿益:這是一篇很棒的遷移經驗談, 更具說服力的是作者來自台灣最高的學術研究單位:國立中央研究院, 從原本使用者 Oracle 到為何選擇 PostgreSQL 做了這篇文, 感謝作者同意轉載.

內容:
我們單位已經開始規畫將原本 oracle 的資料庫轉移到 PostgreSQL,原因無它,Oracle 對學術單位來說太 over kill,我們也鮮少使用 oracle 進階的功能。基於經費、維護上的考量,最後選擇改採 Open source 的 PostgreSQL。至於為啥不用 MySQL ? 表面的理由是 postgreSQL 和 oracle 在很多方面都很類似,轉移比較簡單,而 PostgreSQL 功能也比 MySQL 完整太多了。背後的理由嘛... 算是個人偏好與信仰吧?MySQL vs. PostgreSQL 對我來說就像是 VB vs. Java一樣。選擇 Java 的我自然也就選擇 PostgreSQL 囉。

Lob, Lob, Lob

轉移 db 最痛苦的欄位非 Blob, Clob 莫屬了,因為各家 db 對 lob 的做法都不一樣。PostgreSQL 本身並不支援Blob, Clob 欄位。你得使用 bytea, oid, text 等欄位來模擬。text 可以對應的 jdbc clob。而 bytea 與 oid 則對應到 Blob。bytea(byte array) 可直接在該欄位上儲存所有 binary 的資料,算是最簡單的做法。但缺點是 query 該 row 時也會一口氣把全部的資料都讀到記憶體裡... 除非是儲存一些 .gif, .jpg 之類的小檔,不然還是選擇採間接儲存的 oid 比較好。現行 hibernate 的工具會替 java Blob 產生 oid 的欄位型別。

oid 是 PostgreSQL 一個內藏指標,任何資料庫物件 (sequence/table/row... etc) 都有一個 oid。而 oid 欄位就是可儲存 oid 值的欄位。PosgreSQL 裡還有一個系統 table 叫 pg_largeobject,所有的 lob 物件都會存到這個公用的 table 上。我們來看看 blob 的資料會怎樣儲存:

table: MY_BLOBS
id(int8) filename(varchar) blob_data(oid)
==================================================
12 mydata.xls 20300
13 yourdata.png 20301

table: pg_largeobject
loid pageno data
==================================================
20300 0 134134134AED14134DD.....
20300 1 19AE3ADE91091999000.....
20301 0 1093101910519949592.....

MY_BLOBS.blob_data 裡記錄的 oid 直接對應到 pg_largeobject.loid 欄位。而 pg_largeobject.data 裡才是真正的 binary 資料。在預設的情況下,一筆資料最大是 8 kB,所以如果太長的資料會分段儲存,例如上面的 20300 就被分成兩頁存了 (pageno, 0 和 1)。ok, 這就是 blob 在 postgreSQL 間接儲存的做法。

blob 採用 oid 的缺點

  • 無法控管權限,任何可以存取該 database 的 user 都可以存取 pg_largeobject 的內容
  • 讀取 oid 的欄位要在 transaction 下操作。沒有 transaction 會丟 exception。
  • 修改/刪除 blob 會留下舊的資料,系統不會自動清除。(pg_largeobject 會留下一堆用不著的 binary data)

第一個問題對我們來說還好,只要管理得宜就不是問題。第三個就很麻煩了,隨著時間一長,垃圾資料會越來越多 (這種垃圾叫 orphan large object,請用 google 查一查....)。所幸已經有人寫工具專門來清理 -- 安裝 postgres-contrib 套件後,裡面有個 vaccumlo 的程式,只要用 cron 排程定期清理即可。至於讀取 oid 要在 transaction 下... 這個最慘了!我們過去的程式讀取 blob 都沒有在 transaction 內,而且有些部份是仰賴 OpenSessionInView 這個 pattern 來減輕 DAO 的實做 (note 1),因為這些在 oracle 上都沒有問題。現在 port 到 postgreSQL,程式得被迫重新再檢驗一次,甚至改寫.... orz。再次證明即使使用了 hibernate,migrate db 還是相當頭痛的一件事。

note 1: Spring 的 OpenSessionInViewFilter 在 page render 這個階段並沒有包 transaction,所以遇到 oid 也會出 exception。

奇特的 hibernate blob truncate bug

除了上面三個 oid 的缺點外,搭配 hibernate 使用時還有個 bug (?)。看看下面這段程式:

  1. //Read and update in transaction
  2. Transaction transaction = session.beginTransaction();
  3. MyEntity myEntity = (MyEntity) session.get(MyEntity.class, new Long(12));
  4. byte[] readBytes = IOUtils.toByteArray(myEntity.getData().getBinaryStream());
  5. assertEquals(1000, readBytes.length); // data is 1000 bytes.
  6. myEntity.setFilename("makeSomeChange.txt");

  7. // fileName got updated but blob is truncated to 0 byte at database!
  8. session.saveOrUpdate(myEntity);
  9. transaction.commit();
  10. session.close();

MyEntity 有兩個 property,一是 fileName(varchar),二是 data(blob by oid)。上面的程式很簡單,(1) 開始 transaction ,(2) 讀出一筆 myEntity,(3) 裡面的 blob 讀出來變成 byte array (4) 修改檔名 (5) update myEntity 回資料庫 (6) transaction commit。可以預期的是,這一筆 myEntity 應該只會修改 fileName 欄位,其他欄位都不變... 呃,回頭查一下 db,發現 pg_largeobject 裡的資料長度竟然變成 0,資料無故被砍了!究其原因,應該是 hibernate 將 "讀到空的 binary inputStream" 也寫回 db 裡去了!真是個大 bug 地雷。貼到 hibernate 論譠但是沒人理... orz。所幸還是有暫時的解決方案,昨個參加 Javaworld TWJUG,Cyberjos兄建議替 mapping 加上 dynamic-update="true"

  1. <class name="MyEntity" table="my_entity"
  2. dynamic-update="true">

我試了結果果然可行。hibernate 產生的 update sql 就不再包含 oid 那個欄位了,因此也不會有讀過的資料被砍的問題。dynamic-update="true" 的功能是 update 時,只替 dirty 的欄位產生 set foo = ? ,所以能夠避免不必要的 update。

結語

我們的程式不採用 file system,而使用 lob 來儲存檔案有幾個原因:

  • Lob 支援 transaction,file based 的沒有。
  • 資料都統一放在 db 裡,簡化備份。
  • 檔案都不是很大

但是 jdbc/hibernate/blob/clob 卻讓我們開發過程中吃足了苦頭:oracle9i 時期的 jdbc driver 處理 lob 時會有 leak、而目前最新的 oracle jdbc driver 處理 clob 時,遇到長度為 1000~2000 byte 的檔案會出錯 ( <1000> 2000 時就沒問題.... 別問我為什麼)... blah blah。現在我們打算擺脫 oracle 了,想說 postgreSQL 應該就沒這些怪問題了,結果還是有一堆東西要調整... lob 真是讓人又愛又恨啊。


延伸閱讀(Link):
PostgreSQL 大型物件 BYTEA vs OID

PostgreSQL 8.3的新功能[HOT](七)(簡體)

更新:2007-04-18
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

benchwarm.sh运行结束以后,在当前目录下创建两个文件:tpsdata.txt和latency.txt,它们包含gnuplot可以使用的数据,用gnuplot转化成图表显示:

sh gnplot.sh

这个命令创建名为tpsdata.png的图像文件。

在我自己的笔记本上用正在开发中的8.3生成tpsdata.png图示如下:
纵轴是TPS,横轴是时间,正如我们看到的,在10:17性能变得非常低下,这就是checkpoint的影响。

下面看一下commit时的延迟,使用 sh latency.sh 命令生成latency.png图形文件,通常是500毫秒以下的延迟,在性能变差的10:17这个时刻,延迟变成了3秒左右。
8.2.3之上的数据如下:

与8.3相比,虽然8.2的整体性能比较低,但是没有像8.3那样极端的性能下降。这不能说明8.3有这样的性能偏差,实际上在不同硬件平台使用同样的方法取得数据,8.3出现波峰和波谷变化的现象也比较少。


■作者介绍
石井达夫(ishii・tatsuo)先生
一方面参与PostgreSQL核心开发的国际化部分。另一方面,主要业务是进行PostgreSQL相关产品和服务的规划开发,追求PostgreSQL的商业化。主要著作有:『PostgreSQL完全攻略ガイド』(技術評論社), 『PHPxPostgreSQLで作る最強WWWシステム』(技術評論社),『PostgreSQL構築・運用ガイド』(日経BP,共著)等等。同时还是日本PostgreSQL用户协会会員。

2007-04-17

PostgreSQL 8.3的新功能[HOT](六)(簡體)

更新:2007-04-18
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

引申话题
既然前边已经有了PostgreSQL和pgbench的话题,顺便接触一下pgbench在8.3中的改善,主要有三点:
(1) CREATE TABLE时可以指定filefactor(-F选项)
(2) 数据的初期载入变得更快。这里是利用8.3的新特性,在某些情况(TRUNCATE之后的COPY和INSERT)下不输出WAL日志来实现高速化。实际上,数据载入时间的缩短程度令人吃惊。
(3) 更加详细的latency log

后边还要介绍使用3)对性能提升效果的分析方法,不过这并不是笔者的原创,3)的补丁提案者GregSmith先生在下边的网页中介绍了一些与8.3结合的若干调整事项:
http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm

这次使用的文件benchwarmer.sh,log-to-csv.py,create.sqlplot.sh,latency.sh)放在一个压缩包中下载:
benchwarmer.tar.gz

按时间顺序分析pgbench结果
pgbench运行一定数目的事务,然后报告平均每秒运行的事务数(TPS)。但是由于checkpoint的影响,实际运行中处理速度会有很大变化,如果有一个图表化的方法表示每时每刻的变化,分析也就变得更加容易,另外,也能表示到提交(commit)为止花费了多长时间。

首先运行benchwarmer.sh,这个脚本以-l选项运行pgbench,运行结果向作业数据表中写入用SQL处理之后转换成gnuplot的数据。

$ sh benchwarmer.sh
Cleaning up database test
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "branches_pkey" for table "branches"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts"
vacuum...done.
1250 transactions for each of 16 concurrent users...
transaction type: Update only accounts
scaling factor: 1
number of clients: 16
number of transactions per client: 1250
number of transactions actually processed: 20000/20000
tps = 526.130190 (including connections establishing)
tps = 528.044259 (excluding connections establishing)
556303
556315
556415
558679
566797
567560
574470
575667
577738
584986
Pager usage is off.
psql:create.sql:1: NOTICE: table "timing" does not exist, skipping
DROP TABLE
CREATE TABLE


To be continued ...

PostgreSQL 8.3的新功能[HOT](五)(簡體)

更新:2007-04-17
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

pgpool-II的柔韧性(scalability)
改变话题,讲一下pgpool-II的节点柔韧性。pgpool-II是向PostgreSQL中增加连接池(connection pooling)和复制(replication)功能的pgpool的后继项目,是针对可以运行并发检索的大型DB的中间软件(middleware),以open-source形式公开。

关于pgpool-II的并发检索效果可以在下边的文档中找到benchmark结果:
http://www.sraoss.co.jp/event_seminar/2006/pgpool-2_intro.pdf(日文)

可以看到9节点与单独的PostgreSQL相比性能提高9-18倍,但是并没有节点数与性能的关系(scalability)。本次在日立制作所的协助下,取得了1到9个节点会引起怎样的性能变化数据,报告如下:
硬件:BladeSymphony BS320 (10台)
CPU: Intel Xeon5110 1.6GHz 2way(4core)
内存: 4G
硬盘: SATA 73GB×2
pgpool-II的版本: 1.0.2
PostgreSQL的版本: 各节点都是8.2.3
测试数据: 使用pgbench -i -s 900生成

系统构成图(图中日文 ノード:node,节点):

采用的机器是刀片服务器(Blade Server),其中一台运行pgpool-II和benchmark工具pgbench,其余9台运行PostgreSQL数据库。

使用pgbench生成9千万条数据,在各节点中分散放置,比如:一个节点时每节点9千万数据,3个节点时每节点3千万数据······9个节点时每个节点1千万数据。

测试中使用下边这样的SQL语句:
SELECT abalance FROM accounts WHERE aid = 值
“值”采用1到9千万之间的一个随机数。
结果图示(同時接続数:并发连接数):


纵轴是TPS(Transaction Per Second),数值越大表示性能越好。横轴是并发连接数,概略表示数据库负荷。

节点数从1开始增加,可以看出性能得到急剧提高。比较16个并发连接时的情况,1个节点具有380TPS而4个节点具有19033TPS,实际性能提高50倍以上。不要期待节点数能够成比例的增加性能,数据分散放置各节点中的数据量减少,缓存能够更有效的工作才是性能提高的主要原因。

但是5个节点以上性能就慢慢降了下来,一般认为这是pgpool-II的overhead造成的。

To be continued ...

延伸閱讀(Link):
http://pgpool.projects.postgresql.org/pgpool-II/en/

2007-04-12

PostgreSQL 日本NTT協助取得 ISO/IEC15408 安全認證

更新:2007-04-12
對映章節:
オープンソースDB初のISO15408セキュリティ認証版PostgreSQL,NTTデータが無償公開
開放源代碼(OSS)DB 首次的 ISO15408 安全性認證版 PostgreSQL, NTT 資料無償公開

內容:
ISO/IEC 15408 簡介

  • ISO/IEC15408 旨在支持產品(最終是指已經在系統中安裝了的產品,雖然目前指的是一般產品)中IT安全特徵的技術性評估。ISO/IEC15408 標準還有一個重要作用,即它可以用於描述用戶對安全性的技術需求。
  • 在國際上推行多年的 ISO/IEC 15408 資安產品評估標準已證實,取得驗證證書的產品,發生資安事件的比例相對較低。有效提高資安防護能力的第一步,就從資安產品評估開始。
  • 一般說來,經過 ISO/IEC15408 評估的IT安全產品有助於確保一個機構安全專案的成功,這些 IT 產品的使用能夠極大地減少機構所面臨的安全風險。

日本 NTT 簡介
  • 日本 NTT 是日本電信界的龍頭企業體, 擁有龐大的電信網路技術研究與人才.
  • 目前正大量佈署著 PostgreSQL 應用和其 Cluster 在自己的企業 IT 使用中.
 NTT 數據 4月11日,無償公開取得了 ISO/IEC15408 安全性認證的 PostgreSQL。NTT 數據強化安全性設定認證的申請已取得了。開放源代碼的資料庫管理系統取得 ISO/IEC15408 安全性認證在全球世界上亦為第一次。

 ISO/IEC 15408 是國際的安全性基準。經濟產業省(日本政府的財經部門)對取得了 ISO/IEC 15408 認證的產品從 2007年4月到 2009年3月底實施著稅制優待措施。(這才叫重品質的國家政府)能接受對具體, 基準取得價額的稅額扣除(10%)又特別償還(50%)。再在省廳的籌措中, ISO/IEC 15408認證產品的利用也被大力推薦。

 PostgreSQL 的開發是由開放源代碼·獨立自治團體的 PostgreSQL Global Development Team 進行。但是變得需要為了 ISO/IEC 15408 取得認證的確保除了費用以外安全性的組織體制等條件, 由於獨立自治團體的取得難以進行。為此 NTT 數據改變做法, 由獨立行政法人信息處理推進機構(IPA)申請這認證。Linux OS 已經有實體案例, 在開放源代碼的數據庫管理系統的 ISO/IEC15408 認證取得更為世界上首次。

 公開的 PostgreSQL, 把版本 8.1.5 做為基本 NTT 數據雖然改良了但是實行版(二進制)。ISO/IEC 15408 認證的對象為了不是源碼出自實行文件, 不是源碼公開著二進制(具體性 RPM文件)。實行環境是 Red Hat Enterprise Linux AS4 for x86。

 安全性的強化亦進行了的密碼認證, 監查對數(記錄)表示·閱覽機能。根據這些的改良作為源碼被地方自治團體反饋。評價保障水平是最基本的 EAL1

 NTT數據「ゆうちょくらぶ」的會員管理系統等大規模系統活用著 PostgreSQL。開發再擴張到 clustering·軟件「PostgresForest」和全文檢索工具「Ludia」, 等 PostgreSQL 的開放源代碼·軟件無償公開。PostgreSQL 關聯以外, 開發運用管理工具的 Hinemos, sekyuaOS 的 TOMOYO Linux等的開放源代碼·軟件也無償公開。


延伸閱讀(Link):
日本 NTT - PostgreSQL 認證版新聞與下載頁

PostgreSQL 8.3的新功能[HOT](四)(簡體)

更新:2007-04-16
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

原文來自日经BP网站的“PostgreSQL观察”栏目,作者是石井达夫先生。

8.3中HOT之外的改善项目
除了HOT,还有许多预定在8.3中追加的新特性,下边我们来看一下引人关注的项目,不过这其中还有一些正在评估(preview)中,最终不在8.3中引入也是有可能的。

并发CREATE INDEX(CREATE INDEX CONCURRENTLY: CIC)

一直以来运行CREATE INDEX时,会将相应数据表锁住而禁止行的插入、更新和删除操作。这次追加的「CONCURRENTLY」选项,将不锁表进行CREATE INDEX,这对向正在使用中的大数据表增加新的索引特别有用。

BITMAP索引
BITMAP索引时已经在许多商业数据库中实现的索引类型,对性别这种种类比较少的值特别有效。

负荷分散的CHECKPOINT
CHECKPOINT是定期运行的对数据库缓存和数据库磁盘内容的同期化处理,这个时候会发生大量的磁盘写入操作而导致性能低下,负荷分散的CHECKPOINT能够分散磁盘写入操作,尽量使数据库负荷均等化。

DSM(Dead Space Map)
DSM是在共享内存中记录数据表和索引废弃区域的数据结构,VACUUM命令参照DSM而不是将整个数据表读入,因此能够实现高速化。DSM是由bgwriter进程生成的。

其他改善项目
追加对JIS X 0213(日语文字编码)字符集的支持。
JIS X 0213是2000年制定、2004年修正的最新日语编码标准,包含现在PostgreSQL支持的JIS X 0208和JIS X0212编码的文字,以及第三第四水准的汉字(这一点类似简体中文编码),还包含一些厂商追加的“用户定义文字”和“平台依赖文字”中的部分。因此,使用JIS X 0213能够保持平台间的互换性,以及提供更丰富的文字种类。
此外,在Windows Vista已经将JIS X 0213的字符集映射(map)到Unicode编码,在相应字符集上已经能够实际运用。
8.3为了能够支持JIS X 0213,新增两个字符集:
(1) EUC_JIS_2004
(2) Shift_JIS-2004
(1)是EUC_JP的JIS X 0213版,(2)是SJIS的JIS X 0213版。已经存在的UTF_8也可以对应JIS X 0213,可以进行1)、 2)、UTF_8之间的自由编码转换。

8.3之上还有更多的改善项目,跟随功能确定的顺序,,PostgreSQL观察今后也将进行详细的报道。

题外话,希望更多说中文的人能够参与进来,不然中文能够得到的支持就会少许多,这不仅仅是指pgsql。看看日本民间自由软件水平,我们真的落后太多。

To be continued ...

PostgreSQL 8.3的新功能[HOT](三)(簡體)

更新:2007-04-12
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

原文來自日经BP网站的“PostgreSQL观察”栏目,作者是石井达夫先生。

HOT的效果
使用HOT以后更新性能到底会有多大程度的改善呢?我们看看开发者自己的benchmark测试结果。

benchmark是利用PostgreSQL附属的pgbench工具进行的。这个测试在900万行的数据表(accounts)上进行更新,同时将日志插入到另一个表中,这一连串操作都在同一个事务中。而这样的事务同时由90个每个运行5万次的session并发执行,并且没有在更新的列上定义索引。
运行这个测试的PC机fill factor设定为90%,2G内存,128M共享缓存,测试中autovacuum设定为每分钟启动一次。
benchmark在开发中的8.3之上,HOT有效和无效的情况下运行比较。
测试结果图示如下(HOTあり:HOT有效; HOTなし:HOT无效):
纵轴是TPS,即每秒钟被执行的事务数,正如我们看到的,HOT有效的情况下性能大约提高一倍,也就是说HOT的效果是很大的。

另外,也证明了HOT能有效抑制废弃区域的增长。
看一下开始前和结束后数据表以及索引尺寸的比较图,在HOT有效的情况下,数据表和索引尺寸几乎都没有增长。(オリジナルサイズ:原始尺寸; HOTあり:HOT有效; HOTなし:HOT无效)

To be continued ...

2007-04-11

PostgreSQL 8.3的新功能[HOT](二)(簡體)

更新:2007-04-11
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

原文來自日经BP网站的“PostgreSQL观察”栏目,作者是石井达夫先生。

下边,简单地解释HOT的工作原理。
另外,正如文章开头描述的那样,PostgreSQL 8.3还在开发中,今后还会有些变化,也有不同实现的可能性。说不定,最坏比如8.3根本不会发布的情况也会出现(当然这种可能性非常低)。

抑制索引废弃领域的增加
假设有如下一个管理网页访问次数的简单数据表,当某个URL被访问时cnt增加计数,这是一个频繁更新的典型案例。
CREATE TABLE t1(
url TEXT PRIMARY KEY,-- 主键,自动附加索引
cnt INTEGER
);

cnt更新时,url字段的关联索引也会被更新。考虑一下cnt发生变更而url没有变化的情况,这时url的索引更新是没有必要的。而在HOT下,这样的索引更新不会发生,因而能够抑制索引肥大化。
这个没有关联索引更新的行为被称呼为「HEAP ONLY TUPLE」(HOT就是这样命名的)。

不使用VACUUM抑制数据表的废弃领域
更新cnt时,这行数据被删除(正确地说是在这个时刻变得不可见),然后追加新的数据行。被删除的行中放置一个被删除标志,以及一个指向新行的指针。再次更新这行数据时,从旧行的指针很容易找到新行,然后用上边提到的同样方式在这里放置指向新行的指针。这样反复更新的话就会形成一个指针链,叫做更新链(UPDATE chain)。
更新链越长检索和更新花费的时间就越长,直到运行VACUUM才会消除这种影响,这是现在PostgreSQL的问题点。
HOT也会形成更新链,这一点没有变化,但是如果没有其他的事务参照,对于HEAP ONLY TUPLE而言,会不调用VACUUM而仅仅回收删除行。能这样做的原因就是不需要从索引参照HEAP ONLY TUPLE。回收是用SELECT处理进行的,沿着HEAP ONLY TUPLE的更新链一边前进一边判断这一行是否需要废弃,如果可以废弃的话则作为可再利用对象处理。因此,它能够把更新链的长度控制在最短。
在SELECT时做这样的处理会有overhead的担心,通常PostgreSQL对数据表的访问是以块为单位进行,而上边对更新链的寻址是在已经读入块的缓存之上处理的,不会产生额外的I/O,因此几乎不会影响效率。
当然更新后的数据如果不写入数据块是不能进行这样的处理的,这个时候运行VACUUM就变得必要,实际业务中许多案例更新后的长度并没有增长很多,我认为也不会有什么大的影响。

To be continued ...

PostgreSQL 8.3的新功能[HOT](一)(簡體)

更新:2007-04-11
對映章節:
【PostgreSQLウォッチ】第35回 性能を大幅に改善するPostgreSQL 8.3の新機能「HOT」とは

原文來自日经BP网站的“PostgreSQL观察”栏目,作者是石井达夫先生。

现在正在开发中的PostgreSQL 8.3,很快就要「feature freeze」(功能确定)。5月期间进入β测试阶段,最早预计在7月正式发表。
8.3版本在性能方面有大幅度的改善,尤其被关注的是HOT(Heap Only Tuples)。

关于HOT
HOT用一句话描述的话就是:改善PostgreSQL更新性能的部件。在说明HOT之前,首先解释为何PostgreSQL的更新性能成为一个问题。

PostgreSQL更新处理的弱点
PostgreSQL采用追加型的架构,更新的时候,在数据库内部删除行之后再追加新行。删除掉的行由VACUUM命令作为可以再利用区域回收,然后被再利用。如果频繁进行更新处理也必须让VACUUM频繁运行,不然废弃区域会不断增加导致数据表肥大化而致使性能恶化。
VACUUM需要扫描数据表的全部数据,所以数据表越大处理时间越长。因此,如果数据表很大而且更新又很频繁,那么无论怎么频繁运行VACUUM也来不及回收更新处理产生的废弃区域。
然而问题不仅如此。
更新处理不仅仅是针对数据表本身,索引也必须同时更新。PostgreSQL的某个列被更新的话,关联索引也需要全部更新。因此,拥有大量索引的数据表的更新处理量将会特别大,索引也会越来越肥大化。索引的废弃区域也因此难于被VACUUM回收再利用,比数据表的问题更加严重。

利用HOT消除废弃区域
HOT为了解决这个问题设计了新的更新处理算法,利用HOT,不仅仅是数据表的肥大化,索引的无用肥大化也可以防止。

To be continued ...

2007-04-09

PostgreSQL 登入角色與角色成員間的關係

更新:2007-04-09
對映章節:
http://www.postgresql.org/docs/8.2/static/user-manag.html

詞彙:ROLE(角色), USER(用戶/使用者)
內容:
PostgreSQL 同如 Oracle 對 User 的定義, 在 8.1 版後全採用 ROLE(角色)的關念.
User 在登入到 DBMS 時, 可以是一個 ROLE, 也可在權限許可的情況下, 任意變更自己的角色成另一個 ROLE 且擁有該 ROLE 的物件權限, 就好比一個普通人進了戲團, 他可以扮演任何的戲角, 也相對取得這戲角在戲中的權限與地位一般.

ROLE 的關念取代了原有的 USER/GROUP 的設計, 以下我做了幾個簡單的示範, 來協助您學習更多 PostgreSQL 的理念:


  1. ROLE 是物件(Object/對象), 屬於 DDL(資料定義語言)的範圍, 故一樣擁有 CRETAT, DROP, ALTER 等方法.
  2. CREATE ROLE [name] ; -- 創建角色成員(membership/group role), 相當於以前的 Group.
  3. CREATE ROLE [name] LOGIN ; -- 不同於第2點, 這是在創建一個登入系統的角色, 相當於以前的 User, 亦可用 CREATE USER [name]; 來替代 SQL.
查看系統表
pg_roles : 會列如全部 ROLE (相當於以前的 Group + User 清單).
pg_group : 列出上述第2點的內容清單.
pg_user : 列出上述第3點的內容清單.

延伸閱讀(Link):

PostgreSQL 複寫叢集系統 Slony-I(二) (簡體)

更新:2007-04-09
對映章節:
http://www.pgadmin.org/docs/dev/slony/index.html

內容:
阿益曾经写过Slony-I的简单介绍,我跟在后边再展开一点,仍然是简介。
关键性技术文章请在Slony-I的官方网站查阅,我不是专业翻译,只能表达自己知道的一点内容。

Slony-I是个非同步复制系统,使用上存在很多制约条件,而且无法做到数据库的完全复制。Slony-II正在开发中,是完全独立于一代进行开发的,也就是说是个完全不同的复制系统,功能上有很大的进步和完善,大家可以去关注一下。

Slony-I用于所有节点在任何时间都有效并且保证安全性的状况,如果某些节点会规律性的脱离网络或者不能保证安全性,那么它并不是一个合适的解决方案。列举一些不能正常工作的例子:
*网络连接不正常
*试图复制到连接不可预知的节点
*把中心服务器的价格数据库复制到销售人员用来更新数据而定期连接的服务器
*服务器因为偶然因素更改配置
*客户可以自己修改数据库结构的某些节点

Slony-I不能做的事:
*Slony-I不是网络管理系统
*没有检测节点崩溃的功能,也不能把一个节点提升为主节点或者其它数据源
*不是一个多主节点复制系统(Slony-II支持多主节点

Slony-I的制约条件:
*不能复制数据结构的变化,并且不能复制大对象(large object)
Slony-I目前具有复制结构变化的能力,但是它不是自动完成的,请查阅EXECUTE SCRIPT
*如果必须有这方面的需求,可以使用PostgreSQL 8.X PITR (Point In Time Recovery),当然它也有一些缺点(自行查阅吧)。

通讯代价:
Slony-I的通讯量是节点数量的二次方增长的,n个节点需要有n(n-1)条网络路径。


延伸閱讀(Link):
http://www.pgadmin.org/docs/dev/slony.html
http://www.pgadmin.org/docs/dev/slony/index.html

2007-04-07

EnterpriseDB 獲選為Linux商業應用大獎-最佳企業級資料庫

更新:2007-04-07
對映章節:

內容:
EnterpriseDB 是一家將 PostgreSQL 商業化的資料庫公司,
在每年 Linuxpilot 華人雜誌公司都會舉辦 OSS 商業應用大獎,
EnterpriseDB 在本次 2007 年獲選為最佳企業級資料庫.

.....

在文中訪問了 EnterpriseDB 公司,
更提出了為何能成為最佳企業級資料庫的證明:

  1. 從 Oracle 資料庫遷移到 EnterpriseDB 高達 90% 不需要修改程序.
  2. 從 Oracle 轉換到 EnterpriseDB 後的系統效能, 可提升 50%~100%.
  3. 完全基於 PostgreSQL, EnterpriseDB 僅為商業化更多 Utility 與提供技術服務.
  4. 最佳的例證: SOE(Sony Online Entertainment)公司, 全面替換 Oracle 資料庫系統成 PostgreSQL 在線上遊戲伺服主機上, 理由是 Oracle 龐大的授權成本與無法完成建置自訂化的系統修改權, PostgreSQL 因其為 BSD 授權, 比 GPL 更為自由, 且能自主商業化產品.



延伸閱讀(Link):

2007-04-06

PostgreSQL 架構模式中 Domain 用途與功能

更新:2007-04-05
對映章節:

內容:
詞彙翻譯: DOMAIN(共同值域), Schema(架構模式)

CREATE DOMAIN 允許使用者(用戶)為當前的資料庫庫在 PostgreSQL 中註冊登記一個新的使用者資料共同值域(Domain), 定義共同值域(Domain)的使用者預定也成為其物件擁有人.

如果指出一個架構模式(Schema)名稱 (例如,CREATE DOMAIN myschema.mydomain ...), 那麼該共同值域是在指定的架構模式中創建的, 否則它會在當前架構模式中創建 (在搜索路徑中最前面的那個;參閱 CURRENT_SCHEMA()). 共同值域名稱必需在其所在架構模式中的現有類型和共同值域中唯一.

共同值域可以便於我們把不同表(Tables)之間的共同(公共)值抽取到一個集中位置進行維護, 一個電子郵件地址欄位(字段)可能在多個表中使用,所有的都是同樣的屬性, 我們可以定義並使用一個共同值域,而不是分別設置每個表的約束限制.

這個例子創建了 country_code 數據類型並且在 一個表定義中使用了該類型.

CREATE DOMAIN country_code char(2) NOT NULL;
CREATE TABLE countrylist (id INT4, country country_code);

2007-04-04

日本conference 2007(简体)

更新:2007-04-04

內容:
日本PostgreSQL用户协会(JPUG)将于6月5日在秋叶原UDX举行conference 2007。

日程安排:
|10:00-| 基調講演 |||
|11:00-| JOSH by Sun PostgreSQL update |||
| | buisiness | solution | tech |
|13:00-| A | | エントリー(Entry) |
|14:00-| B | C | Web/DB開発 |
|15:00-| | | 性能 |
|16:00-| D | | クラスタ(Cluster) |
|17:00-| Lighting talk || 当日募集内容パネルトーク |
|18:00-| 会員総会 |||

门票价格:2000日元

地图:秋葉原UDX

延伸閱讀(Link):
http://www.postgresql.jp/

2007-04-03

pgAdmin III Windows开发环境的构建(简体)

更新:2007-04-03
對映章節:
http://www.pgadmin.org/download/source.php

內容:
1、关联软件
Windows 2000, XP and 2003
Microsoft Platform SDK 2003 R2
Visual C++ 2005 Express Edition
wxWidgets
iconv 1.9.x
zlib 1.2.x
libxml2 2.6.18
libxslt 1.1.x
PostgreSQL 8.0

其中:iconv、zlib、libxml、libxslt的windows版本可以在zlatkovic.com下载。

2、版权
完全不用担心,除了OS需要花钱以外,其它都是免费的。
甚至M$的开发平台也可以在 这里 免费下载,当然他们仍然不是我们所说的freeware,只是free edition而已。

3、wiWidgets
优秀的跨平台开发platform,我们当然要用C++部分,最新版本是2.8.3。目前Dave Pave还在对2.8.3进行评估,2.8.2和这个版本中的修正还没有确定到底会带来什么样的影响,所以开发的话最好采用2.8.0,它是没有任何问题的。虽然wiWidgets也支持MinGW和CygWin,但是我们必须使用VC++,因为pgAdmin III源代码只能支持它。
需要编译的部分包括:contrib中的ogl和stc以及全部的lib,编译环境的设置可以完全不用考虑,缺省下用Batch Build工具build "debug|win32"、"release|win32"、"Unicode debug|win32"以及"Unicode release|win32"4个部分即可。
设置系统变量: WXWIN=wiWidgets目录

4、iconv、zlib、libxml、libxslt
解压缩至同一目录下,然后设置系统变量: PGBUILD=此目录

5、PostgreSQL
别忘记选择安装源代码,不然无法编译pgAdmin III,设置系统变量: PGDIR=pgsql-root。
比如:PGDIR=C:\Program Files\PostgreSQL\8.2

6、pgAdmin III源代码
Windows下使用TortoiseSVN来管理src无疑是最好的选择,当然也可以直接从source code下载一个稳定版本的src。(小秘密:SVN库经常会因为某人的粗心大意导致无法编译,他总是喜欢把自己改写的程序上传一半,嘿嘿)。

7、最后
似乎没什么好写的,不过在没有经验的情况下构建环境真得很麻烦,慢慢摸索,也是一个很好的学习过程。
我在编译wxWidgets上浪费过多时间,因为一直在试图将所有模式进行编译,实际上这完全是浪费时间的无用功。要知道我的目标是编译pgAdmin III,而不是wiWidgets,把需要的部分编译出来就足够,不要学我 :)。
介绍一个不错的C++ IDE: Dev-C++,缺省采用Mingw/GCC编译器,虽然已经有两年没有更新,但是依然好用,wxDev-C++是基于前者和wxWidgets制作的GUI开发平台,可以试试看。

最最后,很多freeware的开发者都是凭着极大的热情投入进来的,他们同我们一样也要面对来自生活的压力。使用这些东西时,如果觉得它很好用而且力有所及,请帮助他们,不论是金钱还是人力。

PostgreSQL [應用實例]荷蘭國家人口註冊管理系統的改寫

更新:2007-04-02
原文網址:
http://www.ososs.nl/article.jsp?article=18575

內容:
開放來源開發者參訪荷蘭(Dutch)政府

12-09-2005 • 幾位開放來源開發商上星期五參觀了荷蘭政府組織(ICT), 即 ICTU, 為 OSS 宗師小組的首次會議。OSS 宗師小組的目標將創造一個能承受的關係在開放來源社區和開發軟體為政府的 ICTU 專案之間。軟體在發展中當前需要 GBA, 即人口登記的再設計。
這軟體將根據開放來源組分, 和將部署在大規模由大約 3 個部會、500 個自治市和超過 5,000 個政府機構

荷蘭政府開發了一項政策為機會均等為開放來源軟體。ICTU 和它的校長在部決定, 軟體將根據開放來源組分。核心開發商的長期參與認為是關鍵的為軟體的成功的發展和維護。所以OSOSS <http://www.ososs.nl>, ICTU 的當中一個其它專案, 被創始和被促進OSS 宗師小組的創作。

將被使用的主要開放來源組分是 PostgreSQL, JBoss 和, Hibernate, JBPMAPACHE Webserver/AXIS。 以下四位核心開發商是存在在OSS 宗師小組的首次會議上: Dirk-Willem van Gulik (Apache), 邁克爾・Meskes, 彼得・Eisentraut (PostgreSQL) 並且最大 Rydahl Andersen (JBoss/Hibernate) 。幾個主題被提出了和日間被談論了。除介紹以外在mGBA 和它的建築學, 開發商提出了他們的OSS 專案(參見附件) 。所有參加者對OSS 宗師小組的主動性和同意的未來會議是熱心的每年兩次被組織。

對於更多資訊關於專案"modernisering 的GBA" 看見:

介紹(PDF):

  1. Modernisering GBA
  2. PostgreSQL
  3. Apache
  4. JBoss


延伸閱讀(Link):

2007-04-01

PostgreSQL 日誌分析器 pgFouine

更新:2007-04-01
對映章節:
http://pgfouine.projects.postgresql.org/index.html

內容:
pgFouine 是一個 PostgreSQL 日誌分析器(log analyzer) 使用從 PostgreSQL 日誌檔案來創建詳細的報表. pgFouine 能協助您判斷您的 PostgreSQL 基礎性應用那一個查詞式您應該在速度上優化它.




網誌存檔

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)