PostgreSQL的小技巧

概述本人使用Oracle和PostgreSQL数年，一直试图将Oracle中许多先进的功能在PostgreSQL中使用，所以也在这方面一直比较注意，下面先整理出3点，以后会慢慢完善。 　　(1)和Oracle类似的dblink功能 　　使用过oracle的人都知道，oracle有个很先进的功能叫：dblink,能够在一个数据库中 *** 作另外一个远程的数据库，比如：一个数据库在中国北京，另外一台数据库在中国 本人使用Oracle和Postgresql数年，一直试图将Oracle中许多先进的功能在Postgresql中使用，所以也在这方面一直比较注意，下面先整理出3点，以后会慢慢完善。 @H_404_0@　　(1)和Oracle类似的dblink功能

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

                　　#cd contrib      /      dblink 　　#make 　　#make install    

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

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

@H_404_0@　　大家可以像这样安装dblink的所有函数：

                　　#cat dblink.sql      |      psql 　　[pgsql@webtrends contrib]$ cat dblink.sql       |      psql 　　CREATE FUNCTION 　　[pgsql@webtrends contrib]$    

@H_404_0@　　说明我们的函数安装成功。

@H_404_0@　　下面可以使用dblink的所有先进功能了。

@H_404_0@　　大家可以先看看dblink.sql中的一些函数申明，让我们更了解他的作用。

@H_404_0@　　下面进入psql:

                　　pgsql      =      # select dblink_connect(      '      host=localhost user=pgsql password=      '      ); 　　dblink_connect 　　      ----------------       　　OK 　　(      1       row)    

@H_404_0@　　这个函数用来建立到远程数据库的连接。

@H_404_0@　　我们可以像这样想远程的数据库中insert一条记录：

                　　pgsql      =      # select dblink_exec(      '      insert into student values(/'linux_prog/',/'12345/')      '      ); 　　dblink_exec 　　      -------------------       　　INSERT       22516276             1       　　(      1       row)    

@H_404_0@　　现在我们检索我们刚才insert的记录：怎么样?刚才insert的记录已经在里面了。

@H_404_0@　　dblink的功能非常强大，我上面列举的只是他的最简单的应用。大家可以参考Postgresql的source code下面：

@H_404_0@　　contrib/dblink/sql/dblink.sql仔细看一下。

@H_404_0@　　(2)找出系统中性能很差的sql，并加以优化

@H_404_0@　　我们在做Oracle系统管理的时候，经常做的事情是：

@H_404_0@　　首先看看系统中哪几条sql的性能最差，通过linux命令：top -c找出该最前面的几个oracle进程的PID，然后在oracle的相关vIEw

@H_404_0@　　中将这些sql找出来，然后去看看这些sql的execute plan,然后进行相关的优化。

@H_404_0@　　Postgresql也提供了这样先进的功能。

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

@H_404_0@　　编写相关的脚本：

                　　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:" 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=;              "             |       psql      　　[pgsql@webtrends bin]$ .    

@H_404_0@　　这个脚本是显示指定的pID的session目前正在执行的SQL语句。

@H_404_0@　　比如：

@H_404_0@　　我用top -c,结果是：

@H_404_0@　　3665 pgsql 15 0 124M 124M 122M R 30.0 2.1 0:04 postgres: pgsql pgsql [local] INSERT

@H_404_0@　　可以看到：3665这个pID显示在第一条，说明它的sql可能效率比较低。

                /      vIEwsql.sh       3665       　　procpID       |       current_query 　　      ---------+---------------------------------------------------       　　      3665             |       insert into access_log select       *       from access_log ; 　　(      1       row)      　　killsession.sh: 　　#    

@H_404_0@　　我们可以看到他正在进行的SQL语句，然后我们就可以对这些sql进行性能的优化。

@H_404_0@　　如果，如果是一条select语句，执行速度狂慢的话，我们可以用explain来看看他的execute plan,看是否有合适的index或者是否是某个table很久没有analyze过了，等等。

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

                !/      bin      /      sh 　　################################################ 　　# Author:linux_prog # 　　# use to kill one session. # 　　################################################ 　　      if       test       -      z $      1      ; then 　　echo       "      Usage:" 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)                

@H_404_0@　　刚才那个很占资源的session的目前的sql *** 作就被cancel掉了。

@H_404_0@　　在3665的psql中会显示：

@H_404_0@　　pgsql=# insert into access_log select * from access_log ;

@H_404_0@　　ERROR: canceling query due to user request

@H_404_0@　　(3)清楚的知道每个table或者index的大小

@H_404_0@　　每一个DBA都应该知道，IO的瓶颈是所有数据库性能的瓶颈。所以我们在设计表结构的时候，一定要尽量的减少每个字段的大小，只有这样，table的size才会尽量的小。

@H_404_0@　　还有，我们在进行sql调整的时候，首先做的，肯定是对大的table的performance turnning。

@H_404_0@　　因此，我们很清楚的知道每个table或者index所占用的磁盘大小是很有必要的，在oracle中可以直接访问dba_segments这个vIEw来知道每个table或者INDEX的大小。

@H_404_0@　　Postgresql的contrib/dbsize中也有这样的一个模块。

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

@H_404_0@　　像这样查看table:access_log的大小：

@H_404_0@　　pgsql=# select relation_size('access_log')/1024/1024 ||'M' as dbsize;

@H_404_0@　　dbsize

@H_404_0@　　--------

@H_404_0@　　332M

@H_404_0@　　(1 row)

@H_404_0@　　像这样查看index:test_IDx的大小：

@H_404_0@　　pgsql=# select relation_size('test_IDx')/1024/1024 ||'M' as dbsize;

@H_404_0@　　dbsize

@H_404_0@　　--------

@H_404_0@　　0M

@H_404_0@　　(1 row)

@H_404_0@　　我们先可以写个脚本来进行这样的 *** 作(留给大家自己完成)。

@H_404_0@　　以上内容都是比较实用的东东，大家如果能够深刻理解的话，一定能够在日常的数据库管理中发挥不可小看的工作。

1 总结

以上是内存溢出为你收集整理的PostgreSQL的小技巧全部内容，希望文章能够帮你解决PostgreSQL的小技巧所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。