极湖

较低版本（1.0 ?） CakePHP 的文件
cake/libs/model/dbo/dbo_postgres.php
中，有一句 SQL：

SELECT sequence_name FROM information_schema.sequences

在 PostgreSQL 8 中，以上 SQL 运行出错。

根据原意修改如下：

SELECT c.relname as sequence_name FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind='S' and n.nspname = 'public'

其中的 'public' 是 schema 名，需要根据实际情况修改。

顺便把 dbo_postgres.php 中包含以上 SQL 的函数修改如下：

    function sequenceExists($seq) {
        $cache = parent::__cacheDescription('sequences');
        if($cache != null) {
            return in_array($seq, $cache);
        }
        $sequences = array();
        // ★修改这句
        //$res = $this->rawQuery("SELECT sequence_name FROM information_schema.sequences");
        $schema = $this->config['schema'];
        $res = $this->rawQuery("SELECT c.relname as sequence_name FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind='S' AND n.nspname = '{$schema}'");
        while($row = $this->fetchRow($res)) {
            $sequences[] = $row[0]['sequence_name'];
        }
        
        parent::__cacheDescription('sequences', $sequences);
        return in_array($seq, $sequences);
    }

需要说明的是，在新版本的 CakePHP 中，已见不到以上函数。

-- 随机顺序
SELECT relname, reltuples, relpages FROM pg_class ;

-- 按 relpages（磁盘使用量）排序（降序）
SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC ;

-- 按 reltuples（记录数）排序（降序）
SELECT relname, reltuples, relpages FROM pg_class ORDER BY reltuples DESC ;

参考：PostgreSQL Tips and Tricks

在PostgreSQL的提示符下，用"\d"或是"\d<头字母>"命令可以很方便的查询各种对象的定义。今天发现有一个叫rule的东西不能用这个命令，在网上搜索了一下才在PostgreSQL的官方网站上找到方法。原来，各种rule的定义保存在pg_rules之中，通过查询这个表格即可得到rule的定义，如：

select * from pg_rules;

select * from pg_rules where rulename='RULE_NAME';

select * from pg_rules where tablename='TABLE_NAME';

因为apache用到了mod_auth_pgsql模块，启动时候出错误：

错误信息：

# /etc/init.d/httpd start
httpdを起動中: Syntax error on line 211 of /usr/local/apache2/conf/httpd.conf:
Cannot load /usr/local/apache2/modules/mod_auth_pgsql.so into server: libpq.so.4: cannot open shared object file: No such file or directory
                                                           [失敗]

解决办法：

# vi /etc/ld.so.conf  
（追加）
/usr/local/pgsql/lib/

# ldconfig

之后重新启动apache，OK。

--linux_prog at 2005-04-24

本人使用Oracle和PostgreSQL数年，一直试图将Oracle中许多先进的功能在PostgreSQL中使用，所以也在这方面一直比较注意，下面先整理出3点，以后会慢慢完善。

（1）和Oracle类似的dblink功能

使用过oracle的人都知道，oracle有个很先进的功能叫：dblink,能够在一个数据库中操作另外一个远程的数据库，比如：一个数据库在中国北京，另外一台数据库在中国上海，我可以在北京这台数据库上面建立一个到上海数据库的dblink，然后可以在北京这台数据库上面对上海的数据库进行query或者update或者delete。这个先进的功能在PostgreSQL的原代码的：contrib\dblink 中已经有了，大家可以像这样将他编译并安装到我们的数据库中。

#cd contrib/dblink
#make
#make install

假设我们的postgresql安装在：/home/pgsql中。

make install后，在/home/pgsql/lib/中会有一个：dblink.so文件。这就是使用dblink必须的函数文件。另外，在/home/pgsql/share/contrib中会有一个dblink.sql文件，这就是安装dblink.so的函数所需要的sql语句。

大家可以像这样安装dblink的所有函数：
#cat dblink.sql|psql

函数安装成功之后，就可以使用dblink的所有先进功能了。
大家可以先看看dblink.sql中的一些函数申明，让我们更了解他的作用。

下面进入psql:
pgsql=# select dblink_connect('host=localhost user=pgsql password=');
dblink_connect
----------------
OK
(1 row)
这个函数用来建立到远程数据库的连接。

我们可以像这样想远程的数据库中insert一条记录：
pgsql=# select dblink_exec('insert into student values(\'linux_prog\',\'12345\')');
dblink_exec
-------------------
INSERT 22516276 1
(1 row)

现在我们检索我们刚才insert的记录：
pgsql=# select * from dblink('select * from student') as student(name varchar(100),pass varchar(100));
name | pass
------------+-------
linux_prog | 12345
(1 row)

怎么样？刚才insert的记录已经在里面了。

dblink的功能非常强大，我上面列举的只是他的最简单的应用。大家可以参考PostgreSQL的source code：
contrib/dblink/sql/dblink.sql
仔细看一下。

（2）找出系统中性能很差的SQL，并加以优化

我们在做Oracle系统管理的时候，经常做的事情是：
首先看看系统中哪几条SQL的性能最差，通过linux命令：top -c找出该最前面的几个oracle进程的PID，然后在oracle的相关view中将这些SQL找出来，然后去看看这些SQL的execute plan,然后进行相关的优化。

PostgreSQL也提供了这样先进的功能。

首先，在postgresql.conf中把stats_command_string = true打开，使PostgreSQL的stats collector process监控每个session的sql语句。

编写相关的脚本：
viewsql.sh:

#!/bin/sh

######################################################
# viewsql.sh                                         #
# Author:linux_prog                                  #
# use to show all active session's sql in PostgreSQL.#
######################################################

if test -z $1 ;then
echo "Usage: $0 pid"
exit 10
fi

echo "select * from (SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s) as querystring where procpid=$1;" | psql

这个脚本是显示指定的pid的session目前正在执行的sql语句。
比如：
我用top -c,结果是：
3665 pgsql 15 0 124M 124M 122M R 30.0 2.1 0:04 postgres: pgsql pgsql [local] INSERT

可以看到：3665这个pid显示在第一条，说明它的sql可能效率比较低。
[pgsql@webtrends bin]$ ./viewsql.sh 3665
procpid | current_query
---------+---------------------------------------------------
3665 | insert into access_log select * from access_log ;
(1 row)

我们可以看到他正在进行的SQL语句，然后我们就可以对这些SQL进行性能的优化。
如果，如果是一条select语句，执行速度狂慢的话，我们可以用explain来看看他的execute plan,看是否有合适的index或者是否是某个table很久没有analyze过了，等等。

另外，可以提供一个KILL一个session的脚本，比如：有个session占用的资源太多，如果不kill掉他的话，可能会导致系统DOWN机。

killsession.sh:
#!/bin/sh
################################################
# Author:linux_prog                            #
# use to kill one session.                     #
################################################
if test -z $1; then
echo "Usage: $0 pid"
exit 10
fi

SID=$1
echo "select pg_cancel_backend($SID);"|psql

比如：我执行：
[pgsql@webtrends bin]$ ./killsession.sh 3665
pg_cancel_backend
-------------------
1
(1 row)

刚才那个很占资源的session的目前的SQL操作就被cancel掉了。
在3665的psql中会显示：
pgsql=# insert into access_log select * from access_log ;
ERROR: canceling query due to user request

（3）清楚的知道每个table或者index的大小

每一个DBA都应该知道，IO的瓶颈是所有数据库性能的瓶颈。所以我们在设计表结构的时候，一定要尽量的减少每个字段的大小，只有这样，table的size才会尽量的小。还有，我们在进行SQL调整的时候，首先做的，肯定是对大的TABLE的performance tuning。因此，我们很清楚的知道每个table或者index所占用的磁盘大小是很有必要的，在oracle中可以直接访问dba_segments这个view来知道每个TABLE或者INDEX的大小。

PostgreSQL的contrib/dbsize中也有这样的一个模块。

大家可以像上面安装dblink那样安装dbsize.so。

像这样查看table:access_log的大小：
pgsql=# select relation_size('access_log')/1024/1024 ||'M' as dbsize;
dbsize
--------
332M
(1 row)

像这样查看index:test_idx的大小：
pgsql=# select relation_size('test_idx')/1024/1024 ||'M' as dbsize;
dbsize
--------
0M
(1 row)

说明:
无意中检索到这篇文章，觉得很有用，因为以前就想过在PostgreSQL中实现dblink等功能。收藏之，也许以后会派上用场。