Postgresql的小技巧(by
使用Oracle和Postgresql都有一段时间，一直试图将Oracle中许多先进的功能在Postgresql
中使用，所以也在这方面一直比较注意，下面先整理出3点，以后会慢慢完善。
（1）和Oracle类似的dblink功能
使用过oracle的人都知道，oracle有个很先进的功能叫：dblink,能够在一个数据库中操作另外
一个远程的数据库，比如：一个数据库在中国北京，另外一台数据库在中国上海，我可以在北京
这台数据库上面建立一个到上海数据库的dblink，然后可以在北京这台数据库上面对上海的数据库
进行query或者update或者delete。这个先进功能其实在Postgresql的源代码：contrib/dblink
中已经有了，大家可以像这样将他编译并安装到我们的数据库中。
#cdcontrib/dblink
#make
#makeinstall
假设我们的postgresql安装在：/home/pgsql中。
makeinstall后，在/home/pgsql/lib/中会有一个：dblink.so文件。这就是使用dblink必须的
函数文件。另外，在/home/pgsql/share/contrib中会有一个dblink.sql文件，这就是安装dblink.so
的函数所需要的sql语句。
大家可以像这样安装dblink的所有函数：
#catdblink.sql|psql
[pgsql@webtrendscontrib]$catdblink.sql|psql
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATETYPE
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
CREATEFUNCTION
[pgsql@webtrendscontrib]$
说明我们的函数安装成功。
下面可以使用dblink的所有先进功能了。
大家可以先看看dblink.sql中的一些函数申明，让我们更了解他的作用。
下面进入psql:
pgsql=#selectdblink_connect(’host=localhostuser=pgsqlpassword=’);
dblink_connect
—————-
OK
(1row)
这个函数用来建立到远程数据库的连接。
我们可以像这样想远程的数据库中insert一条记录：
pgsql=#selectdblink_exec(’insertintostudentvalues(/’linux_prog/’,/’12345/’)');
dblink_exec
——————-
INSERT225162761
(1row)
现在我们检索我们刚才insert的记录：
pgsql=#select*fromdblink(’select*fromstudent’)asstudent(namevarchar(100),passvarchar(100));
name|pass
————+——-
linux_prog|12345
(1row)
怎么样？刚才insert的记录已经在里面了。
dblink的功能非常强大，我上面列举的只是他的最简单的应用。大家可以参考Postgresql的sourcecode下面：
contrib/dblink/sql/dblink.sql仔细看一下。
(2)找出系统中性能很差的sql，并加以优化
我们在做Oracle系统管理的时候，经常做的事情是：
首先看看系统中哪几条sql的性能最差，通过linux命令：top-c找出该最前面的几个oracle进程的PID，然后在oracle的相关view
中将这些sql找出来，然后去看看这些sql的executeplan,然后进行相关的优化。
Postgresql也提供了这样先进的功能。
首先，在postgresql.conf中把stats_command_string=true打开，使Postgresql的statscollectorprocess监控每个session
的sql语句。
编写相关的脚本：
viewsql.sh:
#!/bin/sh
######################################################
#viewsql.sh#
#Author:linux_prog#
#usetoshowallactivesession’ssqlinPostgresql.#
######################################################
iftest-z$1;then
echo”Usage:$0pid”
exit10
fi
echo”select*from(SELECTpg_stat_get_backend_pid(s.backendid)ASprocpid,pg_stat_get_backend_activity(s.backendid)AScurrent_queryFROM(SELECTpg_stat_get_backend_idset()ASbackendid)ASs)asquerystringwhereprocpid=$1;”|psql
这个脚本是显示指定的pid的session目前正在执行的sql语句。
比如：
我用top-c,结果是：
3665pgsql150124M124M122MR30.02.10:04postgres:pgsqlpgsql[local]INSERT
可以看到：3665这个pid显示在第一条，说明它的sql可能效率比较低。
[pgsql@webtrendsbin]$./viewsql.sh3665
procpid|current_query
———+—————————————————
3665|insertintoaccess_logselect*fromaccess_log;
(1row)
我们可以看到他正在进行的sql语句，然后我们就可以对这些sql进行性能的优化。
如果，如果是一条select语句，执行速度狂慢的话，我们可以用explain来看看他的executeplan,
看是否有合适的index或者是否是某个table很久没有analyze过了，等等。
另外，可以提供一个KILL一个session的脚本，比如：有个session占用的资源太多，如果不kill掉他的话，可能会导致系统DOWN机.
killsession.sh:
#!/bin/sh
################################################
#Author:linux_prog#
#usetokillonesession.#
################################################
iftest-z$1;then
echo”Usage:$0pid”
exit10
fi
SID=$1
echo”selectpg_cancel_backend($SID);”|psql
比如：我执行：
[pgsql@webtrendsbin]$./killsession.sh3665
pg_cancel_backend
——————-
1
(1row)
刚才那个很占资源的session的目前的sql操作就被cancel掉了。
在3665的psql中会显示：
pgsql=#insertintoaccess_logselect*fromaccess_log;
ERROR:cancelingqueryduetouserrequest
(3)清楚的知道每个table或者index的大小
每一个DBA都应该知道，IO的瓶颈是所有数据库性能的瓶颈。所以我们在设计表结构的时候，一定要尽量的减少每个字段的大小，
只有这样，table的size才会尽量的小。
还有，我们在进行sql调整的时候，首先做的，肯定是对大的TABLE的performanceturnning。
因此，我们很清楚的知道每个table或者index所占用的磁盘大小是很有必要的，在oracle中可以直接访问dba_segments这个view
来知道每个TABLE或者INDEX的大小。
Postgresql的contrib/dbsize中也有这样的一个模块。
大家可以像上面安装dblink那样安装dbsize.so。
像这样查看table:access_log的大小：
pgsql=#selectrelation_size(’access_log’)/1024/1024||’M'asdbsize;
dbsize
——–
332M
(1row)
像这样查看index:test_idx的大小：
pgsql=#selectrelation_size(’test_idx’)/1024/1024||’M'asdbsize;
dbsize
——–
0M
(1row)
我们先可以写个脚本来进行这样的操作（留给大家自己完成）。
