PostgreSQL的小技巧(dblink,SQL administrator,dbsize)-

（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
[pgsql@webtrends contrib]$ cat dblink.sql |psql
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[pgsql@webtrends contrib]$ 
說明我們的函數安裝成功。
下面可以使用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 turnning。
 因此，我們很清楚的知道每個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

select * from dblink('select id,name from table2 ') as  

A(id integer,name character varying(32)) inner join cc_table B on B.id= A.id