PostgreSQL 示範用(Sample)資料庫

更新:2007-03-31
對映章節:
http://pgfoundry.org/projects/dbsamples/

內容:
在一般的情況下, 我們可能必須要一個大量資料的 PostgreSQL 資料庫內容, 例如用途在文章教學、講解與測試, 或其它可能的例子裡。
PostgreSQL 子專案就包含了一個專門用來存放給您這目的用的樣品資料庫。
Pagila 被發布於近日的新版本, postgresql 樣品資料庫。這特點的新版本包括分區表(partitioned table)實施使用 PostgreSQL 的 inheritence 特點和規則系統。
您能下載資料庫的拷貝在 Pagila 是 MySQL "sakila" 資料庫的分支, 和被設計顯示特點和PostgreSQL 的實施方面。當前的圖解包含特點包括視觀表(views)、觸發器, 作用, 表分區(Table Partitioning)和更多。它使可利用在 BSD 執照之下。

提示指令
$psql -U postgres -f -d pagila c:\pagila_data.sql


延伸閱讀(Link):

PostgreSQL 效能評估工具 pgbench (上)

更新:2007-03-29
對映章節:

內容:
pgbench 是被 PostgreSQL 作為性能基準檢查測試(Benchmark)用的程序，與 PostgreSQL 源代碼一起被散佈。在這篇我來對 pgbench 和關於基準測試做概要解說。

(日本)石井達夫 先生與 pgbench
為了能進行 PostgreSQL 的基準測試的程序由 石井達夫 先生寫了 pgbench 程序遞交給 PstgreSQL 並與實體源代碼一同被散佈。
主要用來對 Server 方面 Databases 的基本的基準把能用的 TPC-B 做為基礎製作，以每 1秒鐘能實行的事務交易(transaction)數來判斷性能。主要特徵，能模擬多用戶環境，能容易同時試驗連線環境。同時，移植性高，安裝也簡單.

pgbench 在 PostgreSQL 解壓後的源代碼包的目錄內的 contrib/pgbench 裡存在。因此，不需要重新取得。 能做以 contrib/pgbench 移動，以 DBA 權限編譯·安裝。
(在 Win32 - PostgreSQL 8.2+ 其指令已內附在 \bin 之內 pgbench.exe )

pgbench 的使用順序

要啟動 pgbench 命令如下:

$ pgbench -option database_name

同時，pgbench 要進行基準測試，必須照以下的步驟:

1. 做資料庫的初始化→ 基準測試使用的資料庫的作成。
2. 指定基準測試的實行→各種各樣的條件，實行基準測試。

選項的指定
對 pgbench，有指定為啟動的時候的各種各樣的選擇。在這裡，只使用 pgbench 上重要的選項摘錄介紹。

• -h :PostgreSQL(postmaster)啟動的主機名。到省略的時候 Unix domain socket 連接給自主人。
• -U :用戶名.
  
• -p :PostgreSQL(postmaster)啟動的端口號。與省略的時候 PostgreSQL(postmaster) 默認使用的 5432端口被指定了的東西被看作。
• -i :初始化為基準測試使用的資料庫。
• -s [定標係數] :初期化為資料庫的時候使用，[定標係數]*10萬筆的資料被製作。被省略的時候等同 1指定。
• -c :同時實行的客戶端數。被省略的時候與 1相同。
• -t :一個客戶端指定實行的 transaction 數。被省略的時候與 10 是相同。
• -S :這個選擇的話進行檢索處理的試驗。其他的處理不被進行。
• -N :這個選擇的話從通常的試驗省去了一部分的更新處理的試驗被進行。
• -d :這個選擇的話，試驗的流逝等各種各樣的信息被表示。可是，為了在大量裡(上)表示信息的處理被進行，多少試驗結果惡化了。
  

舉例:
$ pgbench -i -U postgres -s 10 test

說明:
初始化創建基準測試用資料表在用戶"postgres"的"test"資料庫裡, 且"accounts"資料筆數創建 10*10萬筆資料.

下圖是執行上述命令後產生的 4個資料表和資料筆數(點圖放大)




延伸閱讀(Link):
http://www.techscore.com/tech/sql/pgbench/index.html

PostgreSQL 8.3 版新特性概觀(一)全文搜尋引擎-FTS

更新:2007-03-28
對映章節:

內容:
PostgreSQL 8.3 版新特性概觀
面對著即將到來的次版本的升級, 總是會帶給我們更棒的效能與更多商業型 DBMS 上的優越功能, 現在我將會開始整理對 8.3 版帶來的新特點, 簡要的介紹給中文(正/簡)使用的您, 當然也更希望您把這樣優秀且無任何版權費用的 DBMS 介紹給您身邊的好友, 更多人使用就更加速它的前進!!!

(一)全文搜尋引擎(Full-Text Search, FTS)
這個功能一直都是商業型 DBMS 主張的賣點, 現在我們也將可以大聲驕傲的說 FTS 是 Free 的, 即將到來的 8.3 已不再像之前的版本附加功能(Tsearch v1, Tsearch v2)的方式導入, 而是整合到 DBMS 成為系統一部分, 也伴隨著一系列的 SQL 命令, 大至如下:

• CREATE FULLTEXT CONFIGURATION -- 創建全文配置
  DROP FULLTEXT CONFIGURATION -- 移除全文配置
  ALTER FULLTEXT CONFIGURATION -- 變更全文配置
  CREATE FULLTEXT DICTIONARY -- 創建全文字典
  DROP FULLTEXT DICTIONARY -- 移除全文字典
  ALTER FULLTEXT DICTIONARY -- 變更全文字典
  CREATE FULLTEXT MAPPING -- 創建聯結式(bind lexeme type)類型與字典
  ALTER FULLTEXT MAPPING -- 變更 binding for lexeme type
  DROP FULLTEXT MAPPING -- 移除聯結式(bind lexeme type)類型與字典
  CREATE FULLTEXT PARSER -- 創建全文搜尋語法分析器
  DROP FULLTEXT PARSER -- 移除全文搜尋語法分析器
  ALTER FULLTEXT PARSER -- 變更全文搜尋語法分析器
  ALTER FULLTEXT ... OWNER -- 變更全文物件擁有者
  COMMENT ON FULLTEXT -- 定義或變更全文物件的註釋

當然也連帶著許多 FTS 物件化的出現, 重要的是您必須先更新您對 FTS 的了解程度, 好迎接它的到來.

延伸閱讀(Link):

PostgreSQL 推薦手動 VACUUM 的時機與目的

更新:2007-03-29
對映章節:

內容:
PostgreSQL 在 8.1 版後雖然加上了自動執行空間清理與回收機制(autovacuum).
減少 DBA 去定期手動執行 VACUUM 的過程, 但有時我們可能更想更快反映這效果, 這時就必須以手動執行的方式來達到目的.

推薦運行 VACUUM

在 pgAdmin III 提供了一個很易觀察當前是否應進行手動 VACUUM 的判斷, 在下圖右邊的黃體標示區預測值:"資料列數(已估算)"與實際值:"資料列數(已計數)", 二個值若產生嚴重偏離實際行數, 就應該在這個資料表上運行 VACUUM ANALYZE。

除了手動運行 VACUUM ANALYZE 命令（也可以利用 pgAdminIII 的「維護」選單來做）之外，還應該考慮定期有規律或者自動地運行 VACUUM ANALYZE (8.1 版後預設值是已啟用)。使用排程程序也可以做到這一點，另外 PostgreSQL 也提供了一個叫做 pg_autovacuum 的後端程序，能夠跟蹤資料庫的變化並在適當時刻自動調用 vacuum 命令。在大多數情況下，pg_autovacuum 是最好的選擇。

(點圖可放大)


pgAdmin III 工作排程代理員:


VACUUM 有什麼好處？

PostgreSQL 的查詢計劃根據預測行數做出決定，如果實際行數與預測行數有太大差異，可能會作出錯誤判斷，造成查詢計劃不是最優化的，導致執行效率過低。

PostgreSQL 資料庫需要 VACUUM 修復表中的事務交易 ID。另外，由於更新和刪除操作而產生的過時資料直到在這個表上運行 VACUUM 命令才會被清理。按下 pgAdmin III VACUUM 介面中的 [幫助/說明] 按鈕，可以從線上文檔中看到更詳細資訊。

延伸閱讀(Link):

PostgreSQL 大型物件 BYTEA vs OID

更新:2007-03-29
Source : PostgresDAC

內容: (翻譯未校稿完)
PostgreSQL 大型物件(Large Objects) BYTEA vs OID

名詞:Binary Large OBjects (BLOBs)

在 PostgreSQL 有二種方式用來存放二進制大物件(BLOBs)。首先, 是使用 bytea 類型。第二是, 將使用大物件(Large Objects)的原有能力。

BYTEA 類型

BYTEA 類型或二進制串與簡單的字符串是非常相似, 像 varchar 和 text。但是, 他們由二個特徵上的區別:

1. 二進制串具體地准許存放八進制值零和其它"非可印的" 八進制(通常, 八進制在範圍 32 到 126 之外) 。字符串禁止零的八進制, 和並且禁止的八進制值所有其它八進制值和序列是無效達成協議對資料庫的選擇的字符集合內碼。

2. 操作在二進制串處理實際位元組(byte), 但是處理字符串取決於地點設置。在短, 二進制串是適當的為存放程式員作為"未加工的位元組" 認為的資料, 但是字符串是適當的為存放 text。

Similitude 意味, BYTEA 價值將包括對結果資料, 可以成本效率。換句話說, PostgresDAC 當地將裝載所有資料從伺服器並且用戶然後能工作與它。

開發商必須並且記住寄發 BYTEA 值到伺服器, 八進制某些值必須逃脫。總之, 逃脫八進制, 它被轉換成它的小數八進制價值三數字的八進制數等值, 和由二條斜線在之前, 即。 ' \\xxx '。因而在最壞的文件資料的大小被送通過網路也許被增加很多。

OID 類型(大物件)

PostgreSQL 有一種大物件設置, 提供串流形式(Stream-style)存取對於使用者資料被存放在一個特別的大物件結構中。串流中的存取是有效的當工作以太大的以至於不能方便地操作整體上的資料值時。

所有大物件被安置在一張唯一系統表叫做 pg_largeobject 。PostgreSQL 並且支持存儲系統叫做 "TOAST" 那自動地存放每個表值大比唯一資料庫頁入一個間接儲藏區域。這使大物件設施部份地過時。大物件設施的一剩餘好處是, 它提供價值 2 GB 在大小, 但是 TOAST 的欄位可能是在多數 1 GB 。並且, 大物件可能任意地被修改使用比進行這樣操作高效率的使用 TOAST 的讀寫 API 。

大物件實施打破大物件"大塊(chunks)" 和存放大塊在列在資料庫。B-tree 索引保證快速的查尋正確大塊數字當做隨機存取讀和寫。

它是可能的, pg_largeobject 表將舉行一些物件資料, 但它也許不被使用任何地方在資料庫。沒有暫時修造在方法去除這 ghosts, 依照被指出在 PostgreSQL 幫助。但有想法, 這也許由 VACUUM 命令做。但是, 這種功能不被實施。

比較表

特徵	BYTEA	OID
最大允許的空間	1 GB	2 GB
資料存取	整體上	Stream-style
儲存	在被定義的表裡	在 pg_largeobject 系統表裡
資料操作	使用 SQL 和脫逸的 sequnces	只在事物交易區塊之內由特別函數
載入	預先	在要求時

延伸閱讀(Link):

PL/pgSQL 入門教學篇(一)

更新:2007-03-28
對映章節:
http://www.faqs.org/docs/ppbook/c19610.htm

內容:
PL/pgSQL 是一個可載入的程式語言
因為歷史性及開發者大多從 Oracle 轉入關係, 導致二者語法相似度非常的高, 對學習者也較易於在 Oracle PL/SQL 與 PostgreSQL PL/pgSQL 間互換。

您能使用 PL/pgSQL 群組序列化 SQL 和架構模式(Schema)性宣告式在資料庫伺服器之內, 減少在網路和通信上, 由客戶端應用導致經常必須請求資料從資料庫和進行邏輯操作資料從一個遠程位置。

您 得以進入對所有 PostgreSQL 資料類型、運算子, 和作用的在 PL/pgSQL 代碼之內。
"SQL" 在 PL/pgSQL 是表示的事實您被允許直接地使用 SQL 語言從 PL/pgSQL 代碼內部。對SQL 的用途在 PL/pgSQL 代碼之內可能增加您的能力、靈活性, 和效能。如果多個SQL 聲明被執行從PL/pgSQL 代碼區塊, 聲明一次被處理, 代替處理一個唯一聲明的正常作用一次。

其它使用 PL/pgSQL 的重要方面是它的輕便; 它的作用是與可能操作 PostgreSQL 資料庫系統的所有平臺相容。

以下部分描述怎麼使 PL/pgSQL 可被利用作為一個程式語言在您的資料庫中。

安裝 PL/pgSQL 在 template1 資料庫會導致被創造與 template1 的所有隨後新創資料庫都當是預設值, 因為的他們的模板資料庫有 PL/pgSQL 被安裝。

最短的創建宣告式:
#CREATE LANGUAGE 'plpgsql' ;

查看系統可用的程序語言:
#SELECT * FROM pg_language ;

基本的編程區塊
CREATE OR REPLACE FUNCTION [f_name](引數)
RETURNS type AS '
DECLARE ...
var ;
BEGIN
...;
RETURN var;
END ;
' LANGUAGE 'plpgsql' ;

-- 結尾的宣告語言種類, 主要是因 PostgreSQL 擁有可自訂化的 10 多種可程序語言.

使用一個函數
#SELECT function_name();

延伸閱讀(Link):

關聯式資料庫系統(RDBMS)的家譜圖

更新:2007-03-28
對映章節:
http://mira.sai.msu.su/~megera/pgsql/

內容:
一張很有趣的圖~(點圖可放大)




延伸閱讀(Link):

pgAdmin III 1.7 開發中版本的新特點探討(一)

更新:2007-03-28
對映章節:

內容:
pgAdmin III 1.7 開發中版本預計會隨著 PostgreSQL 8.3 版本發佈正式的 pgAdmin III 1.8.0 版, 1.7 版本屬開發者 SVN 中的版本, 下面這張說明了幾點基礎上的改變.

• 完整的大中文介面支持(正體/簡體)::翻譯字串達 2,500條以上
• 支持最多的平台標準 GUI 管理工具
• 屬 PostgreSQL 開發團隊專案, 設計構架方式較正統
• 支持 SSL 加密連線
  
• 內建支持 PL/pgSQL 預儲程序除錯器(Debugger)
• SQL 解釋分析器(優化查詢用)
  
• 更完整的 SQL 查詢工具(含自動補齊功能)
• 資料導出入工具
• 完整的 DB 備份與回存工具
  
• 內建 pgAgent (工作任務排程代理員)
  
• 支持 Slony-I 複寫叢集系統
• 內建伺服器狀態監視器, 伺服器組態編輯器
• 內建各式相關 DBMS 資訊報表輸出器(XML, XHTML)
• 完整的官方 PostgreSQL, pgAdmin III, Slony-I 說明文件與參照(chm)
• 完整的使用者/角色授權介面
• 完整的系統統計訊息框
  

延伸閱讀(Link):

PostgreSQL 應用程序設計的效能要訣(二)

原文作者簡介:

Josh Berkus 在 2002年加入 PostgreSQL 的核心開發成員. 現在他在 Sun Microsystems 的開放源碼資料庫團隊中擔任領導者.
擁有 11 年的資料庫經驗, 同時亦曾工作於其它專案包括: OpenOffice.org, Microsoft SQL Server, Oracle PL/SQL, and (shudder) COM+...


更新:2007-03-27
對映章節:PostgreSQL Application Performance Tips

內容:
這是一篇由 Josh Berkus 撰寫的好文章, 阿益翻譯分享給大家參考:

為 PostgreSQL 性能的應用程序設計

Tip 4: DELETE 是昂貴的

一般的應用開發者認為 DELETE 宣告實際上是沒有成本的。您是合理的認同這些嗎?
相反的。DBMSes 不是檔案系統(Filesystems); 當您刪除一筆資料列(row), 索引需要被更新並且釋放空間需要被清掃, 使得 delete 在實際上比 insert 更昂貴。
因此應用程序習慣地 delete 全部詳細資料列並且取代它們及每一次使用新的製造任何改變的儲存結果在這應用的資料庫上。這應該是被複寫及更多區分 row-replacement 行為, 看似更新唯一修改過的列。
同樣的, 當清理一張完整資料表, 時常使用 TRUNCATE TABLE 宣告總是比 DELETE FROM TABLE 來的恰當。比以往要 100 次時間期要來的快速是由於它採用批量處理(batch processes)整張資料表代替比以一列一次為單位。

Tip 5: 查詢迴圈使用 PREPARE/EXECUTE

有時儘管您企圖統合相近似的查詢式到一個大宣告式裡, 它不可能正當的歸因於您的應用程序結構。那就用 PREPARE ... EXECUTE 來做; 它允許資料庫引擎略過解析和計劃對每個查詢疊代。就範例來說:

Prepare:
query_handle = query('SELECT * FROM TABLE WHERE id = ?')(parameter_type = INTEGER)

接著開始您的迴圈:
for 1..100
query_handle.execute(i);
end

Classes for prepared statements for C++ are explained in the libpqxx documentation.

This will reduce query execution time in direct proportion to the size of the looped set.

Tip 6: 使用連結池是有效地(connection pools)

由於網路應用程序, 您將發現您潛在的性能非常大約 50% 可能被受制於您經過的用途, 及適當的組態配置, 連線中的連結池(pooling). 這是因為創建與移除中的資料庫連線消耗重要的系統時間總量, 以及過多閒置空轉的連線仍請求著記憶體和系統資源.

There are a number of tools you can use to do connection pooling on PostgreSQL. 一個第三方的開放源碼工具非常受歡迎是 pgPool. 無論如何, 由於一個 C++ 應用程序 HA 必要條件, it's probably better to use libpqxx's native pseudo-pooling technique called "lazy connections" . I suggest contacting the libpqxx mailing list for more information on how to use this utility.

With PostgreSQL, you want to have as many persistent connections (or connection objects) defined as your normal peak concurrent connection usage. So if max normal usage (first thing in the morning, say) is 200 concurrent connections from agents, users and components, then you want to have that many defined so that your application does not have to wait for new connections during a peak when they will be slow to fork.

(未翻完....)

PostgreSQL 擁有勝過所有DBMS的高度客製化特點

更新:2007-03-25
對映章節:V

內容:
PostgreSQL 是目前支持最多編程語言(Server-side Progremming)的 DBMS, 支持高達 10種以上編程語言的高度客製化 DBMS, 換句話說您可以使用您熟悉的程序語言來構建"預儲程序(Stored Procedures)置於 DBMS 中, 相對其它 DBMS 僅提供自家專屬的語言, 就失色許多.
您可以使用包括:

1. PL/Java
2. PL/Perl
3. PL/PHP
   
4. PL/Python
5. PL/Ruby
6. PL/R
   
7. Tcl
8. C/C++
9. PL/Proxy (Skype 公司貢獻)
   
10. PL/pgSQL (自帶編程語言相仿於 PL/SQL, 讓您易於在 Oracle 間替換.)
    包括有它的標準子例程程式庫範圍從基本的算術和串操作對密碼學和 Oracle 兼容性的上百內部函數。觸發器和被存放的規程可能被寫在 C 和被裝載入資料庫作為 Library , 允許偉大的靈活性在擴大它的能力。

客戶端 API(Application Programming Interfaces)
正因許多語言被 PostgreSQL 支持, 並且有著許多 Library API, 允許各種各樣的語言被編寫和被解釋對接口與PostgreSQL 。

* DBD::Pg - Perl Driver
* JDBC - JDBC Driver
* libpqxx - C++ API
* Npgsql - .Net Data Provider
* ODBC - ODBC Driver
* PgOleDb - OLE-DB Driver
* pgtclng - Next generation interface for TCL
* Pgtcl - Tcl binding library
* Pgin - A Tcl interface to PostgreSQL written in Tcl
* PyGreSQL - Python interface for PostgreSQL
* psycopg - Another Python interface for PostgreSQL

延伸閱讀(Link):

PostgreSQL 是對物件名稱的英文大小寫敏感的資料庫

更新:2007-03-25
對映章節:

內容:
下面這 2 張用 pgAdmin III 製作的圖說明了,
PostgreSQL 是對物件名稱的英文大小寫敏感的資料庫,
同時創建三個同名稱, 但大小寫不同的資料庫,
對 PostgreSQL 來說是三個分別持有不同物件序號(OID)
在使用上請多留心.





延伸閱讀(Link):

PostgreSQL 應用程序設計的效能要訣(一)

原文作者簡介:

Josh Berkus 在 2002年加入 PostgreSQL 的核心開發成員. 現在他在 Sun Microsystems 的開放源碼資料庫團隊中擔任領導者.
擁有 11 年的資料庫經驗, 同時也曾經工作於其它社群專案包括: OpenOffice.org, Microsoft SQL Server, Oracle PL/SQL, and (shudder) COM+...


更新:2007-03-25
對映章節:PostgreSQL Application Performance Tips

內容:
這是一篇由 Josh Berkus 撰寫的好文章, 阿益翻譯分享給大家參考:

為 PostgreSQL 性能的應用程序設計

查詢撰寫規則
所有資料庫管理系統(DBMSes), "往返"(round-trip)時間是很寶貴的。這是因為它取得 Query 回來時必須通過語言分析器(language parser)、驅動器, 穿透網路介面, 資料庫分析器(database parser), 計劃器, 執行器, 反譯分析器, 再返回穿透網路介面, 通過驅動資料處理器, 和對 Client 應用程序的時間。
DBMSes 總是在時間上跟 CPU 爭取進程來處理這個循環週期, 並且由於各式各樣的原因每次在"往返"(round-trip) 上, PostgreSQL 是很嚴苛在意時間和系統資源。

更進一步, PostgreSQL 有著重要特點一切全是事務交易(per-transaction), 每筆事務交易處理都包括日誌記錄的產出和各種需要被設置的可見性規則在內。當您認為您可以不使用事務交易處理下的單一唯讀(singleton read-only) SELECT 的宣告, 實際上每一個宣告對 PostgreSQL 都是在進行事務交易處理。在沒有一種明確事務交易處理時, 宣告也是一種隱藏事務交易處理。

補充這, PostgreSQL 是唯一不輸給 Oracle 在處理大型複雜的查詢式(queries)上, 並且有能力輕易地處理複雜多宣告(multi-statement)式的事務交易在併發同作時的衝突處理。我們並且支持游標(cursors), 包括可捲動式(scrollable)與非捲動式(non-scrollable)。

Tip 1: 永遠不使用多數的小型查詢在當一個大型查詢能執行任務時.
它是共同在 MySQL 應用裡討論後加入的應用代碼; 那是由於手工查詢當 ID 來自父級記錄及之後迴路經由子級記錄 ID。這可能導致來自每個用戶元件螢幕連續性上百或數以萬計的查詢。每筆這種查詢往返時間大約花費 2-6 milleseconds, 看起來似乎不重大但直到您把它增加到 1000 筆次查詢, 到時您丟失 3-5 秒對往返時間。比較之下, 檢索所有那些紀錄在一次唯一查詢時只消耗少於不到 100 milleseconds, 時間節省達 80%.

Tip 2: 群組化多數的小查詢 UPDATES, INSERTS 或 DELETEs 到一個大型的宣告, 或者沒有的話, 大型事務.
首先, 在 MySQL 的早期的版本缺乏子查詢(subselects)導致應用開發者設計他們的資料修改宣告(DML)在相似情況下加入在中介軟體(middleware)中。這對 PostgreSQL 同樣是一種壞方法為。反之, 您若想要利用子查詢並且加入您的 UPDATE、INSERT 及 DELETE 宣告設法修改成批次(batches)在一個單一宣告中。這可減少往返時間與事務交易之上。

在一些案例中, 無論如何, 單一查詢不能寫入所有資料到您要的查詢並且您必須使用一串連續宣告。在這種情況下, 您想要保證包裹您的一系列 DML 宣告在一種清楚的事務交易。 (例如. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). 這是縮減事務交易成本支出且節省執行時間高達 50%。

Tip 3: 連續性的 INSERTS 考慮用塊裝堆疊式(bulk)導入.

PostgreSQL 提供一個塊裝堆疊式的機制稱為 COPY, 採取定位分隔(tab-delimited)製表或 CSV 檔從文件或導管(pipe)來輸入。
COPY 能夠被使用在代替上百或數以萬計的 INSERTS, 它能削減執行時間達 75% 以上。


延伸閱讀:
PostgreSQL 應用程序設計的效能要訣(二)

PostgreSQL 全球開發團隊:我們不進行開發的特點

更新:2007-03-22
對映章節:
http://www.postgresql.org/docs/faqs.TODO.html

內容:
以下幾點是 PostgreSQL 開發團隊聲明不進行研究的項目:

• 全部伺服端(backends)運行線程(threads)在一個單一進程(process).
  
  我們得到從當前的設定這是會消滅進程(process)的保護特徵。在現代系統上, 線程建立通常也同似進程建立, 因此它似乎愚笨的使用一個純淨的線程模型。

• 優化器(optimizer)建議.
  

  優化器建議是被使用在工作週遭的特定問題上的優化器. 我們寧可接受問題的報告與俢正問題點.
  http://archives.postgresql.org/pgsql-hackers/2006-08/msg00506.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg00517.php http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php

• 允許 AS 在 "SELECT col AS label" to be optional.

  Because we support postfix operators, it isn't possible to make AS optional and continue to use bison. http://archives.postgresql.org/pgsql-sql/2006-08/msg00164.php

• 嵌入式伺服器(Embedded server).

  當 PostgreSQL 客戶端運行精巧地在受限制資源環境上時, 伺服器請求多工進程及穩定的連結池資源來運行可靠性及高效能. 但剝削 PostgreSQL 伺服器來運行在單調的進程位址時客戶端應用程式將增加極為龐大複雜和失敗的案例.

Execute Query 功能齊全的開放源碼 DBMS GUI前端軟體

更新:2007-03-21
官方網站:http://executequery.org/



內容:
Execute Query 是一個 GNU 授權的開放源碼的 DBMS GUI 前端軟體
功能非常的齊全, 重點是免費但功能不輸給商業型軟體
全用 JAVA 撰寫, 所以通行所有OS平台, 搭配 JDBC 即可操作您的 DBMS

使用 ERD 功能...


下面這張圖是使用畫面(JDK 6.0 + PostgreSQL 8.2.3 + pg-JDBC Type 4 8.2)
很專業又詳細的操作介面很類似 Oracle ...
圖形化資料表參考的情況真棒, 又可進行圖形化的查詢與ERD設計...



主要包含特點:

1. 查詢編輯器(含圖形化資料表)
2. 資料庫瀏覽器
3. 創建 ERD
4. 比較資料類型
5. 建立資料表
6. 建立索引
7. 創建SQL稿本
8. 導入/導出資料
9. 導入/導出 XML 資料
10. 資料庫屬性
11. SQL-92 關鍵字
12. 連線管理員
13. etc...
    

PostgreSQL 翻譯工作的心得日誌與詞彙定語

更新日期:2007-03-21

今天又大修了 pgAdmin 的語系檔
理由是想近可能的與學術上的用詞相符
學習 PostgreSQL 忠於表現國際 ISO-SQL 一樣
近期 pgAdmin III 1.6.3 發佈時內附最棒的正體和簡體中文版.

原文:schema
未來考慮:架構模式 (己更新!)
schema 就像一個原始的石頭當進入到db過程中, 要受到的一切"審判"和決定.

tuple::值列

Domain::共同值域
這是個很棒的設計, 用來建構公用的值, 簡化與減少同型別的細部誤差.

原文:view
當前翻譯:視圖 <--- CN 未來考慮:視觀表 (TW版己更新! CN考慮中..)

一開始我都翻成"視表", 初期一直不太能認同大多數的翻譯"視圖".."視界", 雖然用 Google 來對這一個字做搜尋可以看到很大的數量差異, 但大家都知道"View"不過是從"Table"做變化而來, 個人覺得跟"圖"字沒任何關係, 用"表"字會切合其意.
ORACLE 把 View 翻成"視觀表", 其實我還蠻認同的, 因為我們不也是在各種軟體上把功能列的"View"一直翻成"檢視"嗎? 這是我個人認為最符合其意的中化詞, 未來再考慮要不要用這詞來替代.

Oracle::PostgreSQL 表格空間(Tablespace)

更新:2007-03-21

對映章節:C19.06

內容:
在預設的情況下, Oracle 10g 會使用以下的表格空間

1. SYSTEM : 存放資料字典
2. SYSAUX : 存放統計和非必要在 SYSTEM 裡的資訊.
3. Template : 暫存SQL運算.
   
4. undo : 用來復舊用.
5. User
   

PostgreSQL 在預設的情況下, 僅使用二個

1. pg_global : 對映 Oracle 的三個預設集.
2. pg_default : 樣本資料庫與其它新建 DB 的預設空間(template0 & tempplate1)

查看系統表 pg_tablespaces 可以了解目前的表格空間狀態.

Oracel & PostgreSQL 均使用 CREATE/DROP TABLESPACE

相關文章:
PostgreSQL 表格空間(Tablespace)功能與未來特點

PostgreSQL 表格空間(Tablespace)功能與未來特點

更新:2007-03-21
對映章節:

內容:
表格空間, 是整個系統的資料主體
PostgreSQL 使用 pg_global 來存放和系統有關的物件
使用 pg_default 表格空間來存放使用者建立的資料庫物件

表格空間決定著DBMS整體效能的重點調校之一
在以下的情況 DBA 就有必要考量 Tablespace 的分割與配置

• 資料間具有競爭系統效能.
• 大型物件資料表與小型物件資料表應該分別在不同的 Tablespace.
• 分離資料和索引的存放空間是不錯的效能強化.
  

PostgreSQL 開發團隊對下個版本的工作目標:

Tablespaces

• Allow a database in tablespace t1 with tables created in tablespace t2 to be used as a template for a new database created with default tablespace t2
  All objects in the default database tablespace must have default tablespace specifications. This is because new databases are created by copying directories. If you mix default tablespace tables and tablespace-specified tables in the same directory, creating a new database from such a mixed directory would create a new database with tables that had incorrect explicit tablespaces. To fix this would require modifying pg_class in the newly copied database, which we don't currently do.
• Allow reporting of which objects are in which tablespaces
  This item is difficult because a tablespace can contain objects from multiple databases. There is a server-side function that returns the databases which use a specific tablespace, so this requires a tool that will call that function and connect to each database to find the objects in each database for that tablespace.
• -Add a GUC variable to control the tablespace for temporary objects and sort files
  It could start with a random tablespace from a supplied list and cycle through the list.
• Allow WAL replay of CREATE TABLESPACE to work when the directory structure on the recovery computer is different from the original
  (類似 Oracle 的 重作日誌表格空間的功能)
  
• 允許對每個表格空間配額(quotas)

• 允許 ALTER TABLESPACE 來移動表格空間到不同的目錄(directories)
• 允許資料庫被移動到不同的表格空間
• 允許移動系統表(global system tables)到其它的表格空間, where possible
  Currently non-global system tables must be in the default database tablespace. Global system tables can never be moved.

Oracle::PostgreSQL 從 PL/SQL 移植到 PL/pgSQL

更新:2007-03-21
對映章節:C37.11

內容:

本節解釋了 Oracle 的 PL/SQL 和 PostgreSQL 的 PL/pgSQL語言之間的差別， 希望能對那些從Oracle® 向 PostgreSQL 移植應用的人有所幫助。

PL/pgSQL與 PL/SQL 在許多方面都非常類似。 它是一種塊結構的，命令式的語言並且必須宣告所有變量。 賦值，循環，條件等都很類似。 在從 Oracle 向 PostgreSQL 移植的時候必須記住一些事情：

• 在PostgreSQL中參數沒有預設值。

• 你可以在PostgreSQL裡重載函數。 這個特性常用於繞開缺乏預設參數的問題。

• You cannot use parameter names that are the same as columns that are referenced in the function. Oracle allows you to do this if you qualify the parameter name using function_name.parameter_name. In PL/pgSQL, you can instead avoid a conflict by qualifying the column or table name.

• 在PL/pgSQL裡不需要游標(CURSOR)， 只要把命令放在 FOR 語句裡就可以了

• 在 PostgreSQL 裡，函數體必須寫成字串文本， 因此你需要使用美元符包圍或者逃逸函數體裡面的單引號。

• 我們應該用模式把函數組織成不同的群組，而不是用 package。

• 因為沒有 package，所以也沒有 package 級別的變量。這一點有時候挺討厭。 你可以在臨時表裡保存會話級別的狀態

更多參考資料:
EnterpriseDB 對 Oracle 遷移至 PostgreSQL 的支持(近乎80%的不需修改移植)

PostgreSQL 整體擁有成本(TCO)

更新:2007-03-17
對映章節:

內容:
在 Microsoft SQL Server 2005 的網站
看到了這頁 => "比較資料庫" 或許可以提供您另類對 PostgreSQL 的評估
下面這張圖來自 Microsoft - 整體擁有成本(TCO)


問題來了...

• 這張圖表示 MS SQL 2005 只有基本功能? 還是其它比不上 O & I ??

SQL 2005 也要 25K, 那您也許可以選擇
功能與 HA 架構雷同 Oracle 最頂級的 304K的產品,
但 TCO 卻可以近乎 0 K 的 PostgreSQL.

Oracle::PostgreSQL 伺服器基本架構

更新:2007-03-16

對映章節:

內容:
伺服器的 2 個實體(entity)

1. Instance => Postmaster
2. Database => Database

SGA (System Global Area)基本上差異不大, 同樣具有

1. Shared pool
2. Database buffer cache
3. Log buffer

Process

1. SMON => postgres a
   
2. PMON => postgres b
   
3. DBwn => postgres (not limit)
   
4. LGWR => postgres c
5. CKPT => postgres e

Oracle::PostgreSQL 叢集(Cluster)系統

更新:2007-03-17
對映章節:

內容:
PostgreSQL 提供的 Cluster 有多種不同目的功能可選用, 軟體取得成本等於零, 且不限制使用 CPU 數.

1. Oracle : RAC(Real Application Cluster)
2. PostgreSQL
• pgCluster
• Slony-I

Skype 捐獻 PostgreSQL code 三個子專案

更新:2007-03-16
對映章節:

內容:
2007年3月 Skype 公司回饋給 PostgreSQL 開發團隊
該公司正在進行中的產品相關成果.
預估在下個 8.3 版或更早前 PostgreSQL 開發團隊會進行整併入
來提供其更佳的功能.

1. 輕量級的連結池: PGBouncer
2. 管理與開發工具組: Skytools
   
3. 預儲程序語言: PL/proxy
   

   PL/Proxy is a proxy language used for remote database procedure calls and data partitioning between databases based on hashing field values.

   Main idea is that proxy function will be created with same signature as remote function to be called, so only destination info needs to be specified inside proxy function body.

   
   

PostgreSQL 二進制碼資料類型(Bytea)

更新:2007-03-15
對映章節:C8.4

內容:
在使用 Oracle 時, 要使用支援 multi-media 和其它大型物件的資料類型為
"BLOB"(Binary Large Object).

在 PostgreSQL 則使用名稱為"BYTEA".

SQL 標準定義了一種不同的二進制碼類型， 叫做 BLOB 或者 BINARY LARGE OBJECT。
其輸入格式和 bytea 不同，但是提供的函數和操作符大多一樣。

PostgreSQL 高可靠性與預寫式日誌(WAL)

更新:2007-03-15
對映章節:III.C27
版本限制: > 7.1

內容:
商用 DBMS 為了確保在系統非預期下潰散後, 仍可複原至近 100% 潰散前的 DB 狀態, 大多採用 類似 PostgreSQL 預寫式日誌(Write-Ahead Logging :WAL)的關念, 預先將一連串的操作先行寫入到日誌檔, 來預防系統潰散時的損失, 對 DBMS 而言這是提高 HR 的重要功能.
(PostgreSQL 在 7.1+版後加入本功能, 至今仍不斷改良使用中)

高可靠性(High-Reliability)
PostgreSQL 的實現方式:
WAL 提供了資料庫即時備份和恢復（backup and restore (BAR)）的實作

• 熱備份(Hot backup):利用 pg_dump 取得一定時間點的復原資料.
• 利用 WAL 來復原損失最少的狀態.
  

2.預寫式日誌(WAL:Write-Ahead Logging)
說明:
預寫式日誌 （WAL） 是一種實現交易日誌的標準方法。WAL 的中心思想是對資料文件的修改（它們是資料表和索引的載體）必須是只能發生在這些修改已經記錄了日誌之後， 也就是說，在描述這些變化的日誌記錄沖刷到永久儲存器之後。
如果我們遵循這個過程，那麼我們就不需要在每次交易提交的時候都把資料頁立即儲到磁碟，因為我們知道在出現崩潰的情況下， 我們可以用日誌來恢復資料庫：任何尚未附加到資料頁的記錄都將先從日誌記錄中重做（這叫向前滾動恢復，也叫做 REDO）。
(This is roll-forward recovery, also known as REDO.)
好處:

使用 WAL 的第一個主要的好處就是顯著地減少了磁盤寫的次數。 因為在日誌提交的時候只有日誌文件需要沖刷到磁盤；而不是交易修改的所有資料文件。 在多用戶環境裡，許多交易的提交可以用日誌文件的一次 fsync() 來完成。而且，日誌文件是順序寫的， 因此同步日誌的開銷要遠比同步資料頁的開銷要小。 這一點對於許多小交易修改資料儲存的許多不同的位置更是如此。

另外一個好處就是資料頁的完整性。

PL/pgSQL - 游標(CURSOR)

更新:2007-03-14
對映章節:C37.8

內容:
一般的 SELECT 使用 LIMIT 來限制取回 Data 的數量, 其餘的 Queries 均會回傳整個搜尋結果.
但當資料量龐大時回傳會造成本上升, 且可能導致 Client 的 RAM 耗盡. CURSOR 就在這情況被用來解決上述的問題.

條件:

1. 必須於 Transaction Block 中宣告使用. (SQL-92不限制)
   
2. SQL-92定義:CURSOR 必須搭配 OPEN/CLOSE 來挷住資源
   (PostgreSQL 可不宣告).
3. SQL-92:CURSOR 是可更新的, PostgreSQL read only.
   

Syntax
DECLARE [name] CURSOR FOR [Queries]
**FOR 可替換成 IS 為了和 ORACLE 相容****

OPEN [name];
CLOSE [name];

FETCH ...

PostgreSQL 架構模式(Schemas)簡介

更新:2007-03-15
對映章節:

內容:
pgAdmin III 1.6.2 的 Schemas 擷圖


phpPgAdmin 4.2 的 Schemas 擷圖

PostgreSQL 的構架模式預設情況下有5個(當然!您也可以自由擴充囉!!!)

1. information_schema : 一定會存在各DB的
2. pg_catalog : 系統目錄, 主要提供系統管理與查看狀態用
3. pg_toast : TOAST 保留專用
4. pg_temp_1 : 暫存用
   
   • 會雖著 SELECT * INTO TEMP [TableName] FROM .... 而增加.
   • Session 結束後, Schema 會殘存, Owner is Postgres.
   • DROP SCHEMA pg_temp_nnn;
     
5. public : 標準公用:這是主要工作用, 同其它簡易型的DBMS功能, 全在這區完成.

1~4 基本上屬隱秘性的, 又可稱為 Data dictionary (資料辭典), 裡頭包含著"超元資料"(metadata)的資訊, 意思是有關於資料的資訊集合體, 搭配著許多 Views 來協助管理者與使用者了解資料的資訊內容.

PostgreSQL 更嚴謹的Transaction控制技術:二段式提交

更新:2007-03-11
對映章節:

內容:
PostgreSQL 在 8.1 版本時加入了這個特性, 這也是高可靠性的議題之一.

二段式提交(Two-phase commit :2PC)
又稱為"二相提交", 也簡稱為 2PC.
在單一的伺服器情況下, Transaction 的功能即可滿足對事務交易的控制.
但在分散式處理的環境下, 若要達到二部以上的伺服器因網路而造成的延遲或其它因素, 事務交易必須同時在各各分點均完成登記才算 Commited, 若其中的一部分點 Server 未能完成則本次的 Transaction 為 Rollback, 一般大多用在要求嚴謹的銀行資金往來的作業上.

新的語法包括:
PREPARE TRANSACTION
COMMIT/ROLLBACK PREPARED

相關的系統檢視表
pg_prepared_xacts

企業級複寫系統 Slony-1 on Debian(一)安裝

更新:2007-03-12
對映章節:

內容:
體會您使用的 OS 自身的系統架構哲學~
Debian 的設計哲學, 雖然使用者可以採用自行編譯二進制來安裝, 但筆者仍建議在目前的各式 Unix-Like 都漸建議 user 採用本身系統的二進制系統來安裝與自動構建還境(除非您的系統不提供二進制包, 或您想更進一步的自訂化條件).
理由是"通常系統不是您一個人管理的, 配合 OS 的方式可以在每次進行系統更新時, 達到最大的相容度, 況且 OS 會採用最佳的二進制化與安全性議題與相關組態檔的 Script 分佈與如何和本系統達到最佳度的"融合"來設計, 也方便其它系統管理員接管工作".



來欣賞一下 Debian Etch 4.0 對 Slony1 的"融合"設計哲學:
Debian Source - Slony-1

1. Master DB 必需要有 PL/pgSQL.
2. 將 Master DB pg_dump to Slaver DB.
3. 組態檔編輯
4. slonik_init_cluster | slonik
5. slonik_create_set set1 | slonik
6. slony start
   

slony1 預設宣告:
/etc/default/slony1
# Set to a space-separated list of node names configured in
# /etc/slony1/slon_tools.conf that should be handled in the init script.
SLON_TOOLS_START_NODES="1 2"

slony1 scripts :
/usr/share/slony1

slony1 doc and sample:
/usr/share/doc/slony-bin


組態設定:
/etc/slony1/slon_tools.conf

slony1 服務主要開關控制
/etc/init.d/slony1

slony1 logs
/var/log/slony1/*


題外話:
大赦國際在報告中指出，俄羅斯存在著多種不人道的酷刑。其中有一種對待犯人的酷刑叫做「slonik」（套大象），讓犯人戴上防毒面具，然後突然切斷一切空氣來源，讓犯人活活窒息；
(未寫完)

PostgreSQL 當前 HA 和叢集(Cluster)化系統專案介紹

更新:2007-03-12內容:
PostgreSQL 擁有著豐富的擴展性系統, 來達到商業型 DBMS 具備的特性, 甚至超越商業型 DBMS, 亦在日本嚴格的測試高 HA 下, 達到非常棒的成績單.

Replication

• Slony-I
  
  
  是一個主點到多數從點(Master to Multiple-slaves)的複寫系統包括串接(cascading)及failover.
• pgPool
  是 PostgreSQL 伺服器的連接池/複寫擴展. 允許負載平衡(load balancing)及擴展協議(extended protocol).
• pgCluster
  PostgreSQL 多主點(multi-master)架構下的同步複寫系統.

PostgreSQL 複寫叢集系統 Slony-I(一)

更新:2007-03-10
對映章節:
http://gborg.postgresql.org/project/slony1/projdisplay.php
http://main.slony.info/

內容:
PostgreSQL 的HA(High Availability)擁有多種複寫(replication system)叢集系統的替代方案,
Slony-I 就是其中的一個主力, 目前也搭配著 PostgreSQL 版本的發佈.
主要功能是當主DB(Master)更新時, 所有的副DB(Slaver)同時異步複寫.

不同於 pgcluster 是 multi-master 模式，每個節點的資料會完全一樣，使用同步的方式保證資料一致，因此更新及新增資料時的效率會降低；而 slony-I 是 master-slave 模式的，使用異步方式，slave 節點的資料會稍有少許（一般幾秒）延時。)

在 GUI 管理介面部份 phpPgAdmin, pgAdmin-III 更提供相對映功能可供建立 Slony-I.


Slony-I 在官方站點就表明了, 這是一個企業級的"主-對多從屬點"的複寫系統.
PostgreSQL 的開發團隊不同於其它 DBMS 的一個特點,
就是一直以來堅持不納入特定的複寫叢集在 DBMS 中,
理由是複寫叢集系統是會隨著時代和技術的演進而有更多替代方案,
所以這不屬於 DBMS 的 ISO-SQL 標準的範圍之內,
只留下"接口/插座"的方式來接軌複寫機制,
也因此造就了 PostgreSQL 有更豐富的複寫和叢集技術發展,
各各都有其特色, 也間接增加了 PostgreSQL 的高擴展性.

PostgreSQL 清楚的交易鎖與學習監看 Locks

更新:2007-03-09
對應章節:C12.3

內容:
PostgreSQL 提供了豐富的鎖(Lock)模式用於控制對表中的數據的併發存取。
這些模式可以用於在 MVCC 無法給出期望行為的時候。 同樣，大多數 PostgreSQL 命令均會自動附加適當的鎖以保證被引用的表在命令執行的時候不會以一種不相容的方式被刪除或者修改。 （比如，在存在其它併發操作的時候，ALTER TABLE 是不能在同一個表上面執行的。）

種類:
1.互斥鎖定:鎖定期限制其它操作與參照.
2.共享鎖定:鎖定期限制其它操作.

pg_locks:這張系統視表提供了系統目前持有的鎖清單.

使用 pgAdmin III 可以得到更好的顯示介面


另外 pgAdmin III 更提供了很方便的狀態檢視介面
(工具---->伺服器狀態----->鎖清單)



使用必須注意出現:
死結(deadlock)
明確鎖定的使用可能會增加死鎖的可能性， 死鎖是是指兩個（或多個）事務相互持有對方期待的鎖。

事務一企圖在指定行上請求一個行級鎖，但是它得不到：事務二已經持有這樣的鎖了。 所以它等待事務二完成。因此，事務一被事務二阻塞住了，而事務二也被事務一阻塞住了：這就是一個死鎖條件。 PostgreSQL 將偵測這樣的條件並退出其中一個事務。

防止死鎖的最好方法通常是保證所有使用一個數據庫的應用都以一致的順序在多個對象上請求鎖定。 在上面的例子裡，如果兩個事務以同樣的順序更新那些行，那麼就不會發生死鎖。 我們也要保證在一個對象上請求的第一個鎖是該對象需要的最高的鎖模式。 如果我們無法提前核實這些問題，那麼我們可以通過在現場重新嘗試因死鎖而退出的事務的方法來處理。

只要沒有檢測到死鎖條件，一個等待表級鎖或者行級鎖的事務將等待衝突鎖的釋放不確定的時間。 這就意味著一個應用持有打開的事務時間太長可不是什麼好事情（比如鎖，等待用戶輸入）。

PostgreSQL 的事務交易(Transaction)功能層級探討(一)

更新:2007-03-10
對映章節:C12
Concurrency Control - Transaction Isolation Level

內容:

SQL 定義的4種事務隔離(Transaction Isolation)層級

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

在 PostgreSQL 裡，你可以請求四種可能的事務隔離級別中的任意一種。
但是在內部，實際上只有兩種獨立的隔離級別，分別對應讀已提交(Read Committed)和可序列化(Serializable)。 如果你選擇了讀未提交的級別，實際上你用的是讀已提交， 在你選擇可重複的讀級別的時候，實際上你用的是可序列化，所以實際的隔離級別可能比你選擇的更嚴格。 這是 SQL 標準允許的：四種隔離級分別定義了何種現象不能發生，但是沒有定義那種現象一定要發生。
PostgreSQL 只提供 2種隔離級別的， 原因是聰明的把標準的隔離級別映射(mapping)到PG 獨有的多版本併發控制(MVCC)架構中來實作更嚴謹的 4種層級。

http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html

#BEGIN TRANSACTION ISOLATION [Level]; --宣告
BEGIN
...
SAVEPOINT []
ROLLBACK
ROLLBACK TO SAVEPOINT []
#END; --結束
COMMIT

PostgreSQL 中文(正/簡體)詞彙翻譯參考對照表

更新:2007-03-27

(持續改進中...)
這篇是用來盡可能同化 PostgreSQL 大中文(正/簡體)化相關詞彙的對照表
例如對 pgAdmin, phpPgAdmin 和 翻譯一些有關文件的參考表.
(很歡迎您的指正與提供)

正體::簡體
---------------------------------------
視觀表::視圖 ---> Views 考慮統一翻成"視觀表", 來同化使用習慣.
二岸用詞還有 檢視表 視表 虛擬表 ...
---------------------------------------
預設::缺省
模組::模塊
源始碼::源代碼
使用者::用戶
叢集::集群
資料::數據
專案::項目
檔案::文件
稿本::腳本(script)
伺服器::服務器
伺服端::服務端
資訊::信息
物件::對象
開啟::打開
檢視::查看
重新整理::刷新
快取::緩存
記憶體::內存
組態::配置
說明::幫助
欄位::字段(Field, Column)
運算子::操作符
訊息::信息
字串::字符

類型(TYPE)
尋找並取代::替換

SQL 書籍: 深入資料庫之美學(Database in Depth)

更新:2007-03-06
對映章節:


內容:
這是 O'reilly 出版的
原文版介紹:http://www.oreilly.com/catalog/databaseid/
中文版介紹:http://www.oreilly.com.tw/product_tip.php?id=a188_toc

天瓏資訊裡寫的介紹很棒 http://www.tenlong.com.tw/Read/readForm.php?i=10
...在 RDBMS 界，當今地位最崇高的就是 C. J. Date，他是我（以及許多人）的神。C. J. Date師承關連式資料庫的祖師爺E. F. Codd，並對許多現今的資料庫技術產生重大的影響。
修過大學資訊系資料庫課程的人，都知道C. J. Date的名號。C. J. Date所寫的《An Introduction to Database System》更是長期以來被資料庫的教授採用當作課本，這本書在去年（2004）已經出版到第八版。...

上個月我才才拿到英文版, 這個月到手的是中文版.(目前正研讀中...有心得再寫)

Universal SQL Client 跨平台 DBMS 通用前端 GUI 工具

更新:2007-03-07

Universal SQL Client Version 2.4.1
http://www.squirrelsql.org/
http://squirrel-sql.sourceforge.net/



特點:
當您的 DBMS 沒有更適用的 SQL Client GUI Tool 時, Universal SQL Client 可能會是您最方便與實用的 DBMS 瀏覽工具, 加上是用 JAVA 構工而成, 只要能提供 JDBC 給它, 您的 DBMS 就能完整呈現出在 Universal SQL Client 裡.

主要的特點如下:

1. 自由軟體, 但軟體品質不亞於商業型.
   
2. 有提供 JDBC Driver 的 DBMS 均支持使用, 當然包括 PostgreSQL.
3. 支持匯出 Data 到 Excel 格式檔.

PostgreSQL GUI 管理工具彙報

更新:2007-03-07

內文:
PostgreSQL 的 GUI 管理工具可以有多重的選擇與各有特色,
個人認為除了以開放源碼的為主外, 亦可善用商業版本的特定功能,
而非固守一項, 必竟各有所長.

個人評註和心得如下:

1. pgAdmin III (Free) 多國語(含 正/簡體中文)
http://www.pgadmin.org
當前版本: 1.6.2
是 PostgreSQL 原生的 GUI 管理工具專案, 同時會發佈各種平台版本供免費下載使用.
功能上足以勝任必要的管理工作, 且會忠實的承現相關操作的 Script 順便學習它的語法架構.

• 可直接管理 Service 的啟用與停用.
• 擁有系統管理的狀態檢視介面.
  
• SQL 查詢工具具 TAB 鍵自動完成功能.
  
• 能輸出各種有關的管理報告.
• *具有自己的備份與還原的管理與二進制壓縮封裝檔及明文等三種模式可選.(COPY)
• *支持 Slon-I 的複寫叢集管理功能.
• *支持 Maintain Database

2. phpPgAdmin (Free) 多國語(含 正/簡體中文)
http://phppgadmin.sourceforge.net
當前版本: 4.1
Web 介面的管理工具, 前提是要有 php 可執行的環境, 便於 Web 使用.
*運用 pg_dump 創建完整語法和運用 COPY 來導資料的純文字檔.(效率好)
*支持 Slon-I 的複寫叢集管理功能.
*支持 Maintain Database




3. Navicat PostgreSQL (約 USD $200) (目前無中文)
http://pgsql.navicat.com
當前版本: 7.2.11
目前屬難易適中且易於操作的 的商業型 GUI 管理.
但目前僅有 Windows和 MAC OS平台, 無 Linux.
*具有自己的備份與還原的管理與二進制檔亦有純 pg_dump script.(運用 INSERT )
*不支持 Slon-I 的複寫叢集管理功能.
*支持 Maintain Database
*有較多的資料(轉入/出)格式易於遷移.




4. SQL Manager 2005 for PostgreSQL (約 USD $700) (目前無中文)
http://sqlmanager.net/products/postgresql/manager/
當前版本: 3.8.0.2
承襲其家族(EMS)對 SQL 管理的專業血統, 功能強大, 也比較複雜, 不適合入門者使用.
但對想深入了解 PostgreSQL 的運作和架構者, 是不錯的剖析研究輔助工具.
但只有 for Windows 版本可以使用.
(對於 data 的語系顯示在 3.8.0.2 版不支持 Client_encoding = UTF-8)
*圖形化的系統設計與剖析工具.
*SQL 查詢工具具 TAB 鍵自動完成功能及更豐富的GUI設計工具.
*運用 pg_dump 創建完整語法和運用 INSERT 來導資料的純文字檔(效率差).
*不支持 Slon-I 的複寫叢集管理功能.
*有較多的資料(轉入/出)格式易於遷移.
*支持 Maintain Database


建議:
在"管理"系統方面, pgAdmin 最為強項, 在 SQL "設計"方面 EMS PostgreSQL 為強, 在行動性上以 phpPgAdmin 為最方便, 在易於了解與入門學習性則以 Navicat 為強.
以目前的情況來論, 進行 Backup/Restore 還是採用 pgAdmin > phppgadmin > Other, 因為採用的還原備份是用 COPY 而非 INSERT, 當資料量巨大時, 還原的時間差距達 70%以上.

pgAdmin III 的備份功能與 GUI 操作

更新:2007-03-06
對映章節:

內容:
pgAdmin III 是 PostgreSQL 重點發展的 GUI 管理工具, 幾乎所有的 OS 平台都有可以使用它. 比起其它由商業型發展的 PostgreSQL 工具, 在目前上功能可能不及, 但 pgAdmin 的專案卻週週都在進行對下個版本的功能開發與修正, CVS 的活動率算是相當勤奮, 在不久的將來便可以達到商業水準, 目前正在進行 1.8版的開發預計隨著 PostgreSQL 8.3 版的來到同時發佈.

pgAdmin 內建有專屬的備份/還原(Backup/restore)功能和GUI, 可選用的參數也很齊全.
操作時有二個方法:

1. 先點選要進行備份的 DB 在到功能列上: 工具------>備份 或者 備份回存
2. 點選要備份的 DB 再按 mouse 右鍵出現小選單: 備份 或者 備份回存.
   

再給予檔名, 路徑和指定的參變數就可以了, 會存為 *.Backup 的 pgAdmin 專屬備份包.

mysql2pgsql :給 MySQL 用戶遷移至 PostgreSQL 的轉換程序稿本

更新:2007-03-06

專案及下載網址:
http://pgfoundry.org/projects/mysql2pgsql/

目前版本:1.2 (2007.03.04 更新)

內容:
這是個很成熟的 PgFoundry 的子專案,
perl 稿本程序用來轉換 mysqlDump.sql 文件檔成 postgresDump.sql 文件檔。
然後您再裝入到 psql 。所有 datatypes 都能被處理並且有著 7 個指令旗標可被利用。

下載後只要有 perl 的執行環境就可利用
在 Linux 下的話先變更執行權限

#chmod a+x mysql2pgsql.perl

下面能輸出詳細使用說明

#perl mysql2pgsql.perl --help
==========================================
Usage:

perl ./mysql2pgsql.perl {--help --debug --preserve_case --char2varchar -- nodrop --schema --sepfile --enc_in --enc_out } mysql.sql pg.sql

* OPTIONS WITHOUT ARGS
--help: prints this message
--debug: output the mysql line above the postgres line
--preserve_case: prevents automatic case-lowering of column and table na mes
If you want to preserve case, you must set this flag. For exampl e,
If your client application quotes table and column-names and the y have cases in them, set this flag
--char2varchar: converts all char fields to varchar
--nodrop: strips out DROP TABLE statements
warnings are printed by psql for DROP TABLE statements if the ta ble does not exist

* OPTIONS WITH ARGS
--schema: outputs a line into the postgres sql file setting search_path
--sepfile: output foreign key constraints and indexes to a separate file so that it can be
imported after large data set is inserted from another dump file
--enc_in: encoding of mysql in file (default utf8)
--enc_out: encoding of postgres out file (default utf8)

* REQUIRED ARGUMENTS
mysql.sql (undefined)
pg.sql (undefined)
#

PostgreSQL Windows版安裝程式下載數大約是UNIX/Linux的7倍

更新:2007-03-05
原文:
日經 ITpro - 2007-02-28
「Windows版のダウンロード数はUNIX/Linux版の約7倍」
---PostgreSQL開発メンバーMagnus Hagander氏

內容翻譯:
PostgreSQL 在版本 8.0 正式發佈 Windows 原生安裝程式至今大約 2年過去了。
Windows 版 PostgreSQL 是到哪裡普及了的？今後的功能與性能強化呢？
PostgreSQL 開發成員 Magnus Hagander先生為了日本 PostgreSQL 用戶會的研討會的從瑞典來到日本了, 記者訪問了 Magnus Hagander先生(聽者 ITpro 編輯-高橋信賴)。

Magnus Hagander:


---Windows 版 PostgreSQL 可以說普及多少了？

　從官方網站被下載的 PostgreSQL 二進制安裝檔的下載大部分已經變成 Windows 版。上月來自(2007年1月)官方網站的下載, Windows 版的二進制安裝檔大約為 17萬2000 次, Linux 的 UNIX的二進制安裝檔約 2萬5000 次。 Windows成為UNIX的約7倍。再者原始碼的下載以約 8萬次了(編輯註:原始碼是編譯成所有的OS共同的文件包)。

　雖然說, 這個不一定是應該意味著 Windows 版使用人數多的事實。PostgreSQL 是根基上適合 UNIX/Linux 被開發了的 DBMS。但也有製作 UNIX 用的系統的技術人員, 必須為了開發測試方便使用著 Windows 版這樣的事吧。因為大多數的 Linux distribution 都已標準收錄 PostgreSQL, 不需要從官方網站下載安裝, 如果為了版本升級等下載, 從 distribution 的網站包裝也被下載的話, 官方網站也統計不來。

與---UNIX 版比較了的 Windows 版的處理性能呢。

　根據處理事項的內容來論, Windows 版大約從慢 5％ 到變得慢 50％ 左右。原因是 Windows 版本調換了 UNIX 版本的線程模型, 及其經過的事項...等。

　預計今後目標, 強化共用的全部記憶體的處理效率的事項...等,使之提高性能。

有關---今後的Windows版的改良的計劃呢。

　想強化跟 Active Directory 的結合使由於 SSPI 的安全性認證成為可能。並同時, Visual Studio編譯將切斷。

PostgreSQL 表的結合(JOIN)種類與語法

更新:2007-03-04
對映章節:II,C7

內容:
PostgreSQL 支持表的結合(Joined)種類有五種:

1. 交差結合(CROSS JOIN)
2. 內部結合(INNER JOIN) <---- 預設值
3. 左外部結合(LEFT OUTER JOIN)
4. 右外部結合(RIGHT OUTER JOIN)
5. 完全外部結合(FULL OUTER JOIN)

CROSS JOIN

FROM t1 CROSS JOIN t1

用 pgAdmin III 1.6.2 正體中文查詢工具做的範例:


INNER JOIN ...... ON
t1 INNER JOIN t2 ON ....



LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

PostgreSQL 子專案介紹: pgmemcache

更新:2007-03-04
對映章節:

內容:
pgmemcache 是一個用來給將 PostgreSQL 使用者自定義的函數元件設定 memcached.
安裝很簡易, 但會有些有瑣細的要求.
那什麼是 memcached 呢? 這可是很棒的 Linux 增強用的 Daemon 哦@@"
請轉看這篇簡介...

"PostgreSQL 結合 memcached 進行快取資料與多主機同步"

在昨天它更新到了 1.2 Bata1 版, 原始檔包小巧到只有 13KB.
卻功能強大.
專案網址:http://pgfoundry.org/projects/pgmemcache/

現在開發工作是由Opten技術集團贊助開發的
http://www.otg-nc.com
一家以特定的開放源碼導入服務的國際公司

和我們有關的是它採用 PostgreSQL 為主要資料庫的選擇.

日本-IPA OSS: PostgreSQL 的性能不再是議題

更新:2007-03-03
原文:2007-02-07
「PostgreSQLは16CPUまでスケール，もう性能はOSSミドルの課題ではない」
---IPA OSSセンターが検証データ公開

內容:(日翻中, 翻的不好請指正)
"PostgreSQL的規模測試, 在 16 路 CPU 下性能已經不是開放源代碼軟體的課題"
---IPA OSS中心驗證數據公開

「最新版的 PostgreSQL 16 路 CPU 比例圖 (CPU數有比例性能提高)。性能已經不是開放源代碼· IPA 的課題」---獨立行政法人資訊處理推進機構(IPA)2月7日, 公開了開放源代碼·軟體·中心(OSS中心)實施了的「OSS性能·可靠性評價項目」的結果。

PostgreSQL的可擴展性。評估使用 DBT-1 (網路書局的Transaction)的測試基準


MySQL 5.0大規模資料庫對應。對約 100GB 的資料庫的 DBT-1(Transaction)基準


　可擴展性評價, 利用了 The Linux Foundation 開發的工具 DBT-1。是模擬線上書局的基準工具。測試基準, 在評價的時候以最新版的 PostgreSQL 8.1 patch 版本。「作為現在的最新版的 8.2 基準」(IPA OSS中心研究員日立製作所的鈴木友峰先生)。2005年把 8.1 作為對象實施了的評價到 8 路 CPU 以外比例圖沒做, 此次的評價到 16 路 CPU 比例尺做的事被確認了。因為是 lock 關聯的瓶頸被解除了。

　根據能得到了有關 MySQL 5.0, 基於施給恰當的調校的後, 100GB 的大規模資料庫也能獲得這樣的結果。但默認設定值實用的性能不能得, 調校不可缺少。作為在多重處理器環境, 可認可的只有MySQL 5.0.32 - 4 路 CPU 比例尺做的測試。

PostgreSQL 的叢集(clustering)工具 Slony-I 和 pgpool 的評測


　PostgreSQL 的 叢集(clustering)工具的驗證也進行了。使用pgpool的話以, 2台能得到了最大約 4倍的檢索性能。

　有關 Java 應用服務器, 實施了 JBoss, Gernomino 的 clustering 機能等的評價。據說明白了如果 JBoss 用 8 台構成進行性能評價, 使用了 session 複製機能(Buddy Repulication)也根據台數的性能提高能圖謀的事。在 JBoss 的評價時, 開發了對數(記錄)分析工具 JBoss Profiler 的擴張工具。使得負荷很輕, 實在運用也能取得對數(記錄)。JBoss.叫在 org 網站被公開的預定。

JBoss 4.0和 Tomcat 5.5clustering 評價。在 8台構成的可擴展性


　進行了所說的對確認 Geronimo 1.1上下文水平的設定連鎖障礙的時候 session 不承繼這樣的問題 wo, Geronimo 項目報告的受地方自治團體的影響。

於 WAScluster 的 Geronimo 1.1 做的可靠性評價項目


　從這些的驗證結果, OSS 中心的鈴木先生認為:
「性能已經不是開放源代碼的議題」。更重要的是, 對高負載, 大容量的系統 te , 瓶頸分析和調校是不可缺的。

　評價結果,IPA OSS中心公開的開放源代碼信息數據庫「iPedia」公開著。

　評價專案, 由 SRA OSS日本分公司, NTT數據尖端技術, 住商信息系統, 日本惠普, 野村綜合研究所, 日立系統 and 服務, 日立製作所, 奇蹟·ＬＩＮＵＸ,yuniadekkusu 擔任。

Sun-PostgreSQL 勝利獲得 Oracle 的客戶

更新:2007-03-02
對映章節:[新聞]2007-02-22
LinuxWorld : Sun-PostgreSQL win takes swing at Linux, Oracle



內容:
(擷取相關重點如下)
從 SUN 在2005 年底時, 宣佈它的意圖貢獻並支持開放來源碼的 PostgreSQL 資料庫在它的旗艦產品 Solaris 10 作業系統上, Sun Microsystems 的發佈勝利獲得了 Linux 和 Oracle 的顧客。

SUN 發表了一份文件在網站上, 公司概述一客戶從 ORACLE 到 PostgreSQL 資料庫和起因於一個未通過的測試項目使它選擇了 Solaris 10 而結束 Linux。

這個顧客, 是"一家大資料庫行銷公司", 是基於開放源碼 Maryland-based 的顧問 OmniTI.

在它的報告中, SUN 宣稱 OmniTI 的遷移從自有的應用程序到 PostgreSQL 替它的顧客由"受到軟體成本扶搖直上威脅" 的資料庫給了警示。

該公司現有近一半TG(terabyte)的 OLTP 資料在尖峰時每秒有 10,000 筆事務交易並且它的資料倉儲消耗 1.2 TG(terabytes)的資料。

"要求稱對 OLTP 和資料倉庫操作的自有的資料庫申請依據應用的每 CPU 數執照會是極端昂貴的," SUN : "這個開放來源碼 PostgreSQL 資料庫應用, 另一方面, 沒有使用執照的成本。"

連結: PostgreSQL for Solars 10

PostgreSQL 中文排序在各種編碼轉換下的結果

更新:2007-03-02
對映章節:

內容:
這些圖排序背後的意義!? (有空再研究....)
平台:Win32 XP
DB 編碼: EUC_TW

原始資料:


按中文字欄位排序後:


編碼轉換成 UTF-8 再排序:


從 BIG5 編碼成 UTF-8 排序:


一個轉換失敗:
select * from sort
order by convert(char using utf8_to_euc_tw);
========================================================
ERROR: character 0xc4a1 of encoding "UTF8" has no equivalent in "EUC_TW"
SQL state: 22P05

PostgreSQL DB伺服器中文編碼字元集使用與注意事項

更新:2007-03-02
對映章節:III,C21

內容:
PostgreSQL 是個很擁有豐富的編碼系統選擇與彈性的DBMS.
隨著時代的演進, PostgreSQL 對中文的系統編碼也跟著改變.

在創建DB的部份
CREATE DATABASE .... ENCODING = '[encoding]'...)
createdb -e [encoding]
已不再支持使用 BIG5, GBK, GB18030 這三個編碼系統均為無效值.

舉例:
=# CREATE DATABASE testg51 ENCODING = 'BIG5';
ERROR: BIG5 is not a valid encoding name

目前均建議採用 UTF-8 或者考慮使用 EUC_TW, EUC_CN,
再次才考慮 MULE_INTERNAL.
(但請注意並非所有程序 API 都支持 PostgreSQL 能建立的伺服器字元編碼。比如， PostgreSQL JDBC 驅動就不支持 MULE_INTERNAL， LATIN6，LATIN8 和 LATIN10。)

SQL_ASCII 設置與其它設置表現得相當不同。
如果伺服器字元集是 SQL_ASCII， 伺服器把字節值 0-127 的數值根據 ASCII 標準解析，而字節值未 128-255 的則當作未解析的字符。 如果設置為 SQL_ASCII，就不會有編碼轉換。因此，這個設置基本不是用來宣告所使用的編碼的， 因為這個宣告會忽略編碼。在大多數情況下，如果你使用了任何非 ASCII 資料，那麼使用 SQL_ASCII 設置都是不明智的，因為 PostgreSQL 會無法幫助你轉換或者校驗非 ASCII 字符。

PostgreSQL Server/Client 字元編碼設置與用途

更新:2007-03-03
對映章節:

內容:
二個有關的系統變數 server_encoding & client_encoding
變數的值決定著"目前"DB的 Server 和 Client 的字元編碼
預設值原則上會等同創建 DB 時所指定的編碼系統.

一般情況下, 我們只會去變動 client_encoding
目的是為了要配合從DB取出的 Data 和 在 Client 應用程序上
能 match 讓 data 能正確轉換成 Client 端的編碼
例如用在單一DB, 但多 client 不同編碼時

透過 SHOW 可以觀察當前的系統值
#SHOW server_encoding ;
server_encoding
-----------------
UTF8
(1 row)


透過 SET 可以改變當前的系統值
#SET client_encoding TO UTF8 ;
SET
#

PostgreSQL 中文編碼 "碁" 字已知的問題討論

更新:2007-03-01
對映章節:

內容:
首先感謝 Dylan. Kuo 提出的討論題, 也感謝 PostgreSQL 中國社群朋友 阿弟, wolfman 協助測試.

PostgreSQL 雖然支援編碼轉換系統, 但這問題我初步認為是"EUC_TW"
在做了 client_encoding 變動成 big5 OR utf8 時
"BIG-5"<----EUC_TW."碁"---->"UTF8"
Mapping 不到導致單一表 Select error.

阿弟:
server->utf8,client->big5也是同样的问题，如果
server->utf8,client->gbk就没有问题

wolfman:
erver是euc_tw的时候, 错误跟你的一样.
如果server是utf8的时候,
gbk/utc_tw/utf8都能执行, 但在psql下只有gbk正确显示.
big5时,错误跟你的一样

擷取信件內容如下:
問題:

小弟的環境是裝 PostgreSQL 7.2.4, Server Encoding 是 EUC_TW,

client encoding 是設成 BIG5, 已經用了很多年了, 最近想將DB migration 到 8.2,

Server 跟 Client 的 encoding 還是跟原來的一樣, 轉入的時候, 看起來是沒問題, 但是

當使用 PGAdmin III 的時候, "碁"這個字就無法查詢出來, 會出現底下的錯誤訊息

ERROR: character 0x8ea3c3b7 of encoding "EUC_TW" has no equivalent in "UTF8"
SQL 狀態: 22P05

經過了小弟使用 pgAdmin 1.6.2+, phpPgAdmin4.1+, Navicat PostgreSQL 7.2.9
Win32 Dos Client and Debian 的來回測試得到以下的結論, 供您參考:

原因應該是出在 encoding 非 PostgreSQL 身上
"碁"字在現行的 EUC_TW 版本無法 Mapping "big5" & "unicode" 上
導致這錯誤, 細節就可能要查 編碼系統 是否為同時期一致的問題,
也許在您的使用基點時間上 big5<----->euc_tw"碁"字是可行,
但在我的 OS & DBMS 上 ERROR 是因為編碼也隨著年代在改版
但轉換間 mapping 不到造成的問題 (目前先這樣自圓其說@@"")

1.pgAdmin III 在 GUI 的介面上全使用 UTF-8 來輸出, 等同 pgAdmin 在背後自己對

DB "set client_encoding = utf8", 這同樣發生在 phpPgAdmin and Navicat 有雷同的結果.

2.在win dos client and debian 當 user login 時, client_encoding 均會採用與 server_encoding
的值來當default, 不會產生錯誤.

但是我直接在 shell 上面使用 psql 進去查詢的時候(client_encoding 是設成 BIG5), 這時是正常的,

您正常, 但我仍是ERROR ....

我知道 PGAdmin III 是建議使用 UNICODE的編碼方式, 我強迫設 set client_encoding=BIG5

我使用的時間並長, UTF8一直都是我的預設值
只能建議您可能的話轉存成 UTF8, 若直的需要用到 BIG5 OR EUC_TW
再使用 PostgreSQL 豐富的 convert() 函數內容配合 Views 來實作.
簡易的參考小弟有寫一篇簡單的

PostgreSQL 編碼轉換(Convert)與應用技巧

更詳細的就要請您參考官方文件囉 :-)

PostgreSQL 書籍: Beginning Databases with PostgreSQL

更新:2007-03-07

Beginning Databases with PostgreSQL :
From Novice to Professional, Second Edition



內容:
這本書是 Apress 2005 年出版, 很適合入門到專業過程的學習.
(也是我第一本研讀書哦! 不要小看是二年前的書,
Unix-Like 尤其是 Server 都是很堅持穩定安全中求發展的老先生.)