PostgreSQL實例參考

陳景峰(netkiller)

前言

經過三個月的努力《PostgreSQL 實用實例參考》正式版終於推出了。因為最近換了工作，新公司的工作也很忙所以文檔進展很慢，從最初幾十頁寫到現在200頁的文檔，每天寫文檔的時間越來越少，有時一周也就只寫2頁，甚至一週一字未對。

正式版推出了，然後就是不斷的修正。可能這段時間《PostgreSQL 實用實例參考》更新會更慢些。因為我還有其他文檔要寫：《OpenLDAP 文檔》、《PHP + Corba + Python文檔》、《JBuilder + Weblogic + PostgreSQL 開發EJB》。。。。。

文檔中所有例子，都是在工作總結出來的，如有錯誤請指正。本人愛寫錯別字（哈哈）如果你發現了有錯字，請發郵件給我netkiller(at)9812(dot)net修正文檔。

300頁之後不再推出HTML格式的文檔了，之後的文檔以PDF、PS（PostScript）格式為主，我是使用Microsoft Word寫文檔，處理300頁的文檔很困難，在保存文檔或將doc檔轉成其他格式的檔時經常會出現無回應。我也考慮過使用docbook / latex，或Page Maker。前者非所見即所得，要用戶使用XML撰寫，通過make一類的命令可以生成多種格式的文檔，docbook也是UNIX手冊的標準格式。後者Page Maker不用說了，Adobe出品，生成PDF更好些。

這是我第一次寫一篇如此長的文檔，沒有經驗，寫的不好，不敢稱為“書”，所以我叫它“文檔”。

1.1 本文檔的讀者對象

文檔面向有一定資料庫基礎用戶。在這裏我假設你對資料有一定認識，能夠使用create創建資料與表，能夠使用select、insert、update等語句操作資料庫記錄。

不管是誰，我希望這本文檔都能對你有所幫助。

1.2 本文檔主要內容

第一章主要介紹PostgreSQL

第二章是開發中遇到的一些問題

附錄中一些SQL腳本文件，可供用戶參考。

1.3 怎樣使用本文檔

邊看、邊做、邊試驗，然後總結，多動腦。有問題先查查這本文檔，如果文檔中沒有提到，再考慮其他方式，或與我聯繫。

作者簡介

作者資訊：

陳景峰，昵稱：netkiller, UNIX like愛好者,研究方向群集系統、網路安全、資料倉庫與資料挖掘、LDAP、J2EE，Corba，企業解決方案。

主頁地址：

http://www.9812.net/

ICQ:101888222

Yahoo:snetkiller

AIM:xnetkiller

網易泡泡：openunix@163.com

E-Mail: netkiller@9812.net

有問題最好給我發Email或去下面的Newsgroup裏討論

news://news.cdut.edu.cn/cn.lang.java

news://news.cdut.edu.cn/cn.lang.python

Web Newsgroup:

http://202.103.190.130:8080/news

我常去的BBS：

http://www.pgsqldb.org

http://www.chinaunix.com

http://www.linuxforum.net

第一章 PostgreSQL

PostgreSQL Wins Linux Journal Editors Choice Award
Posted on 2004-08-02
Posted by press at postgresql.org

PostgreSQL has won the 2004 Linux Journal Editors' Choice Award for the best DBMS!

Linux Journal's Editors' Choice Awards are well-known as the premiere forum recognizing outstanding product developments and achievements in the Linux market, and winners of the sixth annual awards are featured in the August 2004 issue of Linux Journal.

Check out the full article.

1 簡介

我接觸PostgreSQL是2000年，但項目中使用PostgreSQL是2003年，2000當時應該是5.x，6.x版本我並沒有深入地研究這個資料庫，還是主要使用MS Sql Server 7/2000 、Oracle 8。

因為很多企業難以支付MS Sql Server 7/2000 、Oracle 8這筆費用，所以Free Database是最佳選擇。但大多免費的資料庫，功能有限、性能也差，跟本不能滿足我們的需求。

1.4 關於性能

有一段時間裏我們使用MySQL，實在不好用，功能太少，它只實現了SQL92 中不到30%的功能。除了select、insert、update、delete還有什麼功能？一味強調速度快，真的是這樣嗎？MySQL資料量增加很大時，速度下劃很快。

幾萬條記錄時速度最快，幾十萬記錄時速度不同了，幾百萬時就開始慢了。PostgreSQL 隨著資料量增大時，速度變化差距不象MySQL那麼大。

有些朋友在網上說（觸發器、游標、外鍵、視圖）影響性能。這裏要說明一下如果適當的使用視圖、子查詢、觸發器、游標……會讓你開發更輕鬆。

注：關於游標，很多SQL書中這樣寫“游標就是指向一行的指標”在PostgreSQL有些不同，它是返回一個結果集，對結果集next 操作返回一行。

Phpbuilder上有一篇文章是寫PostgreSQL 與 MySQL 大家可以去看看。

1.5 為什麼說postgresql是最先進的開源資料庫？

1. 技術領先：
很多新技術都是它提出的
如：pl過程語言．在其他資料系統中都有自己的專用PL語言。而PostgreSQL中支援很多種PL語言（pl/tcl,pl/python,pl/perl,pl/php,pl/shell/pl/pgsql,pl/java.......）
還有面象物件(ORDBMS)他實現的也很早．
他的資料類型支援很全．如幾何型,陣列．．．在其他RDBMS中是沒有的．
總是有新的技術、思想加入其中

2. 在開源ORDBMS中PostgreSQL功能最強．也最完善

1.6 PostgreSQL對SQL99的支持

SQL-3/SQL99	√
PRIMARY KEY主鍵	√
FOREIGN KEY外鍵	√
Schema 模式	√
TOAST大對象	√
View視圖	√
正則運算式	√
subquery子查詢	√
TRIGGER觸發器	√
RULE規則	√
FUNCTION過程/函數	√
CURSOR游標	√
PLSQL 過程語言	√（PL/pgSQL,PL/Tcl,PL/Perl,PL/Python,plPHP等等）
OLTP表的鎖定、事務隔離	√
許可權	√（用戶、組）
Object對象支援	√（ORDBMS）
	√

其他：

連接	進程方式
SSL	√
群集（HA，資料同步複製。。。）	√
ODBC	√
JDBC	√
裸設備	目前不支持
下面是一些限制：一行，一個表，一個庫的最大尺寸是多少？
一個資料庫最大尺寸？	無限制（存在 32TB 的資料庫）
一個表的最大尺寸？	32TB
一行的最大尺寸？	1.6TB
一個欄位的最大尺寸?	1GB
一個表裏最大行數？	無限制
一個表裏最大列數？	跟列類型有關,250-1600
一個表裏的最大索引數量？	無限制

當然，實際上沒有真正的無限制，還是要受可用磁碟空間、可用記憶體/交換區的制約。表的最大尺寸 32 TB 不需要作業系統對大檔的支援。大表用多個 1 GB 的檔存儲，因此檔系統尺寸的限制是不重要的。如果缺省的塊大小增長到 32K ，最大的表尺寸和最大列數可以增加。

這裏引用http://www.pgsqldb.org/postgres-faq.html4.5詳細請登錄網站查看。

2 PostgreSQL 資料庫

2.1 PostgreSQL分區

PostgreSQL 最好自己單獨一個分區，如果你有兩塊硬碟建議你給它單獨一塊硬碟。

[chen@linux chen]$ df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda9 1004024 99892 853128 11% /

/dev/sda1 101089 9498 86372 10% /boot

/dev/sda2 120952116 7648124 107159936 7% /home

none 515400 0 515400 0% /dev/shm

/dev/sda10 2522048 33260 2360672 2% /tmp

/dev/sda7 5036284 2238244 2542208 47% /usr

/dev/sda6 5036284 1919140 2861312 41% /var

/dev/sda5 40313964 99444 38166636 1% /var/lib/pgsql

/dev/sda3 60476068 212532 57191508 1% /cvsroot

[chen@linux chen]$

[chen@linux chen]$ df -m

Filesystem 1M-blocks Used Available Use% Mounted on

/dev/sda9 980 98 833 11% /

/dev/sda1 99 10 84 10% /boot

/dev/sda2 118117 7469 104648 7% /home

none 503 0 503 0% /dev/shm

/dev/sda10 2463 33 2305 2% /tmp

/dev/sda7 4918 2186 2482 47% /usr

/dev/sda6 4918 1875 2794 41% /var

/dev/sda5 39369 98 37272 1% /var/lib/pgsql

/dev/sda3 59059 208 55851 1% /cvsroot

[chen@linux chen]$

2.2 RPM包安裝

[root@linux software]# ls -1

postgresql-7.3.4-1PGDG.i386.rpm

postgresql-contrib-7.3.4-1PGDG.i386.rpm

postgresql-debuginfo-7.3.4-1PGDG.i386.rpm

postgresql-devel-7.3.4-1PGDG.i386.rpm

postgresql-docs-7.3.4-1PGDG.i386.rpm

postgresql-jdbc-7.3.4-1PGDG.i386.rpm

postgresql-libs-7.3.4-1PGDG.i386.rpm

postgresql-pl-7.3.4-1PGDG.i386.rpm

postgresql-python-7.3.4-1PGDG.i386.rpm

postgresql-server-7.3.4-1PGDG.i386.rpm

postgresql-tcl-7.3.4-1PGDG.i386.rpm

postgresql-test-7.3.4-1PGDG.i386.rpm

[root@linux software]# rpm -Uvh --nodeps `ls -1`

Preparing... ########################################### [100%]

1:postgresql-test ########################################### [ 8%]

2:postgresql ########################################### [ 17%]

3:postgresql-contrib ########################################### [ 25%]

4:postgresql-debuginfo ########################################### [ 33%]

5:postgresql-devel ########################################### [ 42%]

6:postgresql-docs ########################################### [ 50%]

7:postgresql-jdbc ########################################### [ 58%]

8:postgresql-libs ########################################### [ 67%]

9:postgresql-pl ########################################### [ 75%]

10:postgresql-python ########################################### [ 83%]

11:postgresql-server ########################################### [ 92%]

12:postgresql-tcl ########################################### [100%]

[root@linux software]# rpm -qa|grep postgre

postgresql-devel-7.3.4-1PGDG

postgresql-7.3.4-1PGDG

postgresql-python-7.3.4-1PGDG

postgresql-contrib-7.3.4-1PGDG

postgresql-jdbc-7.3.4-1PGDG

postgresql-server-7.3.4-1PGDG

postgresql-debuginfo-7.3.4-1PGDG

postgresql-libs-7.3.4-1PGDG

postgresql-tcl-7.3.4-1PGDG

postgresql-test-7.3.4-1PGDG

postgresql-pl-7.3.4-1PGDG

postgresql-docs-7.3.4-1PGDG

[root@linux software]#

[root@linux software]# service postgresql start

Starting postgresql service: [ OK ]

[root@linux software]# su postgres

bash-2.05b$ createdb

CREATE DATABASE

bash-2.05b$ psql

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

postgres=# \q

bash-2.05b$

bash-2.05b$ vi /var/lib/pgsql/data/postgresql.conf

#========================================================================

# Connection Parameters

#tcpip_socket = false

tcpip_socket = true