PostgreSQL-Pgdump && Pgdumpall

這次介紹Pgdump 與Pgdumpall 這兩個指令都是PostgreSQL的備份指令
相關的備份指令我就不多講了~~底下兩個教學文件有很詳細的介紹~~
Pgdump :http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pgdump.html
Pgdumpall :http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pg-dumpall.html

這次主要是講Pgdump 與 Pgdumpall差異!!!
顧名思義Pgdumpall是一次備份全資料庫~Pgdump 是一次備份單一資料庫~~
Pgdumpall可以把目前資料庫用戶和組，以及適用於整個資料庫的訪問權限備份出來(Pgdump 不行)!!
Pgdump 可以對"大對像"做備份(Pgdumpall不行)!!!
關於"大對像":
因為PostgreSQL 允許表的大小大於您的系統允許的最大文件大小， 可能把表轉儲到一個文件會有問題，因為生成的文件很可能比您的系統允許的最大文件大。 因為pg_dump 輸出到標準輸出，您可以用標準的Unix 工具繞開這個問題：
使用壓縮的轉儲. 使用您熟悉的壓縮程序，比如說gzip， split。

根據上面的官方說法:
我的PostgreSQL是安裝於FreeBSD上面~~查了一下他用的UFS filesystem ~~單一檔案最大SIZE已經超過TB了~~所以我不用使用Pgdump 的"大對像"功能!!!改成使用Pgdumpall ~~他又能備資料~~又能備份使用者加權限~~非常符合我需求!!!

Microsoft 分散式交易協調員

MS DTC 是 Windows 2000 元件服務的一部份。（MS DTC 這個觀念在 SQL Server 7 就已經存在。）元件服務也包含 COM+。COM+ 是用於有非交易式通訊需求時，而 MS DTC 則用於有交易式訊息通訊需求時。 MS DTC 主要用來處理分散式交易。所謂 分散式交易 ，就是跨越兩個或多個資料庫的單一 SQL Server 內部的交易，這些資料庫可以放在相同或分別放在不同的電腦系統內。而在同一個資料庫內不同資料表間的交易，則不能稱作分散式交易。在同一個系統上，資料庫與資料庫間的前端交易可利用一般（非分散式）處理；但是 SQL 在執行時仍會以分散式交易處理。
MS DTC 概觀
　
當一筆交易包含許多不同來源的資料時，就必須協調這些提供資料的應用程式。協調的重要性在於確保交易必須是不可部份完成。換句話說，資料的修改不是全部執行，就是全部不執行。如果一筆交易部份完成，部份失敗，就會造成交易邏輯沒有一致性，還可能使資料流失。MS DTC 執行 兩階段交易認可（Two-Phase Commit） 確認交易邏輯。
兩階段交易認可近年來在技術上有很大的改革，這並不是新的技術，也非 SQL Server 獨有。事實上，因 SQL Server 和 Oracle 兩種資料庫皆支援兩階段交易認可的模式及 ODBC，因此兩種資料庫間還可利用兩階段交易認可進行分散式交易。
兩階段交易認可的兩個階段分別為：準備階段與認可階段。在應用程式中以 COMMIT 指令初始化 MS DTC 執行兩階段交易認可。一台 SQL Server 上的 MS DTC，與另一個系統上的 MS DTC 協調處理一筆分散式交易。MS DTC 中處理兩段交易認可的元件稱作 資源管理員 。
當您執行了認可（COMMIT）指令，MS DTC 通知資源管理員開始 準備階段 。第一階段（也就是準備階段），會執行所有認可所需要的動作，包括排清緩衝區和寫入交易記錄檔等動作。這個階段所執行的步驟和一般的標準認可步驟類似，唯一不同的是在這裡，SQL Server 不會真的認可交易，也不會對交易所用到的資源解除鎖定。當所有認可交易的前置動作都完成時，資源管理員便傳送準備成功的訊號給交易管理員。在所有的資源管理員都傳出成功的訊號時，就進入認可階段。
 認可階段 在分散式系統中進行。在這個階段，如果所有的資源管理員都傳出成功訊號，應用程式端就會接受到認可成功的訊息；如果其中有一個資源管理員傳出失敗訊號，就會傳送 復原（rollback） 指令給每位資源管理員，這時應用程式端就會收到認可失敗的訊息。當系統交易時間過長，也會執行復原指令。當一個系統內的認可失敗，分散式交易中所有其他相關系統也會執行復原指令。

備註:
兩階段交易認可:

對於分散式資料庫系統來說，因為資料庫的資料是分散儲存在各地的資料庫伺服器，當交易需要執行交付時，如果馬上執行，若任何一個資料庫伺服器沒有完成交付，就有可能造成整個分散式資料庫的資料不一致。

分散式資料庫系統是將交付分為兩個階段來執行，稱為「二階段交付」（Two-phase Commit, 2PC），如下所示：

1. 協調者（Coordinator）送出SECURE訊息給各資料庫伺服器，此時各資料庫伺服器交付分散儲存各處的資料庫伺服器可以執行交付，否則將放棄此交易。

2. 當各資料庫伺服器都回覆可執行交付後，協調者才送出交付訊息，讓各資料庫伺服器執行交付交易，並且回覆是否成功，如果有任何一個資料庫伺服器回覆錯誤，協調者就會送出復原訊息取消交易。

參考資料:

http://blog.csdn.net/gzwl/archive/2004/10/03/123821.aspx

http://structedtext.appspot.com/db/two_phase_commit.html

PostgreSQL 跨資料庫查詢!!!!

什麼是跨資料庫查詢呢~?!
例如:有A,B這兩個Databaes,
         隨便下一個Join SQL: select * from A.table inner join B.table on a.id=b.id;
這就是跨資料庫查詢了!我記得Oracle,SQL SERVER,MySQL都能支援這種跨資料庫查詢的功能!
可是當PostgreSQL 要做這總功能時會產生下列
Error Code:ERROR:  cross-database references are not implemented
到GOOGLE查了一下原來PostgreSQL 不支援跨資料庫查詢~~如果要達到這種功能要用DBLINK~~~看起來挺麻煩的!!接下來來介紹怎麼去用PostgreSQL的DBLINK!!!

1.安裝postgresql-contrib
   #cd /root/postgresql-8.4.4/contrib/
   #make && make install
2.安裝dblink
   #cd /root/postgresql-8.4.4/contrib/dblink
   #psql -Upgsql -p6789 dbname -f dblink.sql
   (這裡的dbname 是指你要在哪個DB上安裝dblink.sql,如果全部的DB都要裝那應該要多裝幾次
   如果是剛新裝的DB那就裝在template1裡面~~之後開新DB時就可以用template1當模板~這樣     dblink就不用一直重覆裝了)
   裝完後會看到很多DDlink的Function!!

3.使用dblink
    SELECT *
    FROM dblink('host=IP port=6789 dbname=dbname user=user password=pw', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

   這樣就達成PostgreSQL的跨資料庫查詢了~~還真麻煩!!!
   感覺SQL很長看了很刺目~~如果想變短一點可以把他寫成View~~

參考資料:
http://www.postgresql.org/docs/current/static/contrib-dblink.html
http://zh-tw.w3support.net/index.php?db=so&id=46324
http://space.itpub.net/7351491/viewspace-615645

在SQL SERVER 2005 建立Linked Server To PostgreSQL

今天在SQL SERVER 2005 建立Linked Server To PostgreSQL有發生奇妙的錯誤!!
我建立的方式是OLE DB Provider for ODBC+psqlodbc_09_00_0200~~
當SQL SERVER 2005都還沒做SP升級前OLE DB Provider for ODBC+psqlodbc_09_00_0200這樣openquery是可以正常執行的!!
可是當我SQL SERVER 2005升級到SP3時執行openquery就會發生錯誤!!!
Error code:
訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "XXX" 的 OLE DB 提供者 "MSDASQL" 報告了錯誤。提供者報告了未預期的重大錯誤。
訊息 7350，層級 16，狀態 2，行 1
無法從連結伺服器 "XXX" 的 OLE DB 提供者 "MSDASQL" 取得資料行資訊。
去GOOGLE找了一下也沒發現這個問題的解決方式~~之後有發現解決方法在來補上!!
============================================================

所以我換了連結方式為PostgreSQL Native Provider (PGNP)~~
當把這個東東裝完後會在SSMS>伺服器物件>連結的伺服器>提供者裡面有多一個PGNP的~~這樣就代表裝完可以用了~~!!

PGNP如何建立Linked Server 底下有手冊可以參考!!!!!!
補充:後來發現PGNP是要錢的~~試用版每次傳回筆數只能是100行!!
所以後來改用psqlodbc~~不然PGNP真是好物一個~~速度比PGNP快好多!!!
First commercial PostgreSQL OLEDB provider:
http://www.postgresql.org/about/news.895
PGNP-1.3.0.2190.exe:
http://www.pgoledb.com/index.php?option=com_filecabinet&task=download&cid[0]=10&Itemid=68
使用手冊(第14頁有介紹Linked Server 及相關使用方法):
http://www.pgoledb.com/downloads/Developer%20Manual.pdf

============================================================
補上解決方法:
方法1:
原來我找的psqlodbc_09_00_0200這個版本與我的PostgreSQL版本(8.4.4)不同~~
所以我重新安裝 psqlodbc_08_04_0200~~這樣就不會再跑ERROR出來了!
psqlodbc下載網址:
http://www.postgresql.org/ftp/odbc/versions/msi/

方法2:
跟allow in process這個選項有關~~這個控制項如果啟用, 就會把Access的process包在SQL process裡一起跑. 不啟用就是讓他分開跑.這樣access端有錯誤就不會丟回SQL Server. 我想您access端雖然有問題但是不影響執行, 因此只要讓它不丟回SQL Server就好了.
關於兩者效能的差別, 官方的部落格有提到如果不選allow in process可能效能會差一些.這個選項主要是給一些third party的provider, 例如 Oracle, 在做linked server的時候不能跟SQL Server分開跑, 因此必須設定這個選項.  在正常情況下, 勾或不勾都是可以執行的.
http://blog.csdn.net/haipingma/archive/2010/04/27/5535147.aspx

============================================================