資料庫技術叢書
postgresql修煉之道：從小工到專家
唐成著
圖書在版編目（cip）資料
postgresql修煉之道：從小工到專家/唐成著. —北京：機械工業出版社，2015.4
（資料庫技術叢書）
isbn 978-7-111-49872-8
i. p… ii. 唐… iii. 關系資料庫系統 iv. tp311.132.3
中國版本圖書館cip資料核字（2015）第063966号
出版發行：機械工業出版社（北京市西城區百萬莊大街22号 郵政編碼：100037）
責任編輯：楊繡國陳佳媛 責任校對：董紀麗
印 刷： 版 次：2015年4月第1版第1次印刷
開 本：186mm×240mm 1/16 印 張：33.5
書 号：isbn 978-7-111-49872-8 定 價：79.00元
凡購本書，如有缺頁、倒頁、脫頁，由本社發行部調換
客服熱線：（010）88378991 88361066 投稿熱線：（010）88379604
購書熱線：（010）68326294 88379649 68995259 讀者信箱：[email protected]
版權所有·侵權必究
封底無防僞标均為盜版
本書法律顧問：北京大成律師事務所 韓光/鄒曉東
preface 前 言
為什麼要寫這本書
postgresql資料庫是目前功能最強大的開源資料庫，它基本包含了其他所有商業或開源的資料庫中能找到的功能，甚至還包含了一些商業資料庫中沒有的功能。它是最接近工業标準sql92的查詢語言，并且正在實作新的功能以相容最新的sql标準：sql2003。postgresql也獲得數個獎項，曾三次被評為linux journal雜志編輯評選的“最佳資料庫獎”（2000年、2003年和2004年），并獲2004年度的linux新媒體最佳資料庫系統獎。
postgresql目前在國外很流行，特别是近兩年，使用postgresql資料庫的公司越來越多，如提供網絡電話功能的skype和著名的圖檔分享網站instagram。2012年，美國聯邦機構全面轉向postgresql陣營；法國也正推動政府機構積極采用postgresql資料庫取代商業資料庫；世界最大的crm軟體服務提供商salesforce同樣開始大量使用 postgresql。在db-engine 釋出的曆年資料庫排名中，postgresql自從2013年3月上升到第四名後，一直穩定在第四名，排在很多知名的商業資料庫如db2、sybase之前，也排在所有nosql資料庫如cassandra、redis等之前。
雖然在國外使用postgresql 資料庫的人很多，但在國内，postgresql中文的學習資料并不多，是以我就想到寫一本關于postgresql的書，讓國内更多的人加入到學習postgresql資料庫的隊伍中來。
讀者對象
适合閱讀本書的使用者：
資料庫入門者。學習本書和相關的資料庫知識，可以讓一個對資料庫了解不是很深的資料庫愛好者成為資料庫專家。
非postgresql資料庫的dba。可以讓非postgresql的dba 快速掌握postgresql資料庫相關知識，成為一名合格的postgresql dba。
postgresql dba。本書的一些章節對熟悉postgresql資料庫的dba也有很大指導作用，可以使讀者的知識更充實。
開發人員。通過此書可以快速掌握postgresql資料庫方面的知識，提高開發人員的資料庫水準。
如何閱讀本書
本書分為四大部分，分别為準備篇、基礎篇、提高篇和第三方開源軟體及架構篇。準備篇是為沒有資料庫基礎的讀者準備的，如果你已經具備了一定的資料庫基礎，可以跳過其中的一些内容。基礎篇介紹了postgresql資料庫中的一些基礎内容，學完此篇可以完成基本的postgresql資料庫的日常操作。提高篇講解了一些更深的内容，如postgresql的一些技術内幕、特色功能、優化等方面的内容，仔細閱讀此篇可使你早日成為postgresql資料庫高手。第三方開源軟體及架構篇講解了與postgresql資料庫配套使用的一些常用的開源軟體及架構設計方面的内容，通過閱讀此篇，可以開闊大家的眼界，提高資料庫架構設計能力。
本書中有大量的例子，讀者邊閱讀此書邊按例子進行實際的操作，将獲得最佳的學習效果。
勘誤和支援
由于作者的水準有限，編寫的時間也很倉促，書中難免會出現一些錯誤或者不準确的地方，不妥之處懇請讀者批評指正。你可以将書中的錯誤，遇到的問題及寶貴意見發送郵件至我的郵箱[email protected]，我很期待聽到你們的真摯回報。
緻謝
首先要感謝國内postgresql資料庫的愛好者，他們已經整理了很多postgresql的文章，翻譯了postgresql的官方手冊，讓我可以站在前人的肩膀上。大家可以在postgresql在中國的維基首頁http://www.pgsqldb.org/mwiki/index.php/上看到前人的成果。
感謝機械工業出版社華章公司的編輯楊繡國老師，感謝她一年多來始終支援我的寫作，她的鼓勵和幫助引導我能順利完成全部書稿。
最後要感謝我的妻子，她一直支援和鼓勵我，讓我能堅持把這本書寫完。
謹以此書，獻給衆多熱愛postgresql的朋友們。
唐成（osdba）
中國，杭州，2015年1月
contents 目 錄
前言
第一篇準備篇
第1章postgresql簡介2
1.1什麼是postgresql2
1.1.1postgresql概述2
1.1.2postgresql的發展曆史2
1.1.3 postgresql資料庫的優勢3
1.1.4 postgresql應用現狀和發展趨勢4
1.2 postgresql資料庫與其他資料庫的對比4
1.2.1postgresql與mysql資料庫的對比4
1.2.2 postgresql與oracle資料庫的對比6
1.3 小結6
第2章postgresql安裝與配置7
2.1 從發行版本安裝7
2.1.1 在debian或ubuntu下的安裝7
2.1.2在redhat、centos或fedora下的安裝9
2.1.3 在windows下的安裝12
2.1.4 發行版安裝總結16
2.2 從源碼安裝16
2.2.1 編譯安裝過程介紹16
2.2.2 下載下傳源代碼17
2.2.3 編譯及安裝18
2.2.4 安裝後的配置20
2.2.5 建立資料庫簇21
2.2.6 安裝contrib目錄下的工具21
2.2.7 啟動和停止資料庫21
2.2.8 編譯安裝時的常見問題及解決方法22
2.3 安裝技巧介紹24
2.3.1 在redhat、centos下使用二進制包安裝較新版本的方法24
2.3.2 如何使用較大的資料塊提高i/o性能25
2.4 postgresql的簡單配置25
2.4.1 修改監聽的ip和端口25
2.4.2 與資料庫log相關的參數25
2.4.3 記憶體參數的設定26
2.5 小結26
第3章sql語言入門27
3.1 sql語句文法簡介27
3.1.1 語句的分類27
3.1.2 詞法結構27
3.2 ddl語句28
3.2.1 建表語句28
3.2.2 删除表語句30
3.3 dml語句30
3.3.1 插入語句30
3.3.2 更新語句31
3.3.3 删除語句31
3.4 查詢語句31
3.4.1 單表查詢語句31
3.4.2 過濾條件的查詢32
3.4.3 排序32
3.4.4 分組查詢33
3.4.5 表join34
3.5 其他sql語句36
3.5.1 insert into... select語句36
3.5.2 union語句36
3.5.3 truncate table語句37
3.6 小結37
第二篇基礎篇
第4章psql工具的使用介紹40
4.1 psql介紹40
4.2 psql的簡單使用40
4.3 psql的常用指令42
4.3.1 \d指令42
4.3.2 指定字元集編譯的指令45
4.3.3 \pset指令46
4.3.4 \x指令46
4.3.5 執行存儲在外部檔案中的sql指令47
4.3.6 顯示資訊的指令48
4.3.7 更多的指令49
4.4 psql的使用技巧和注意事項50
4.4.1 曆史指令與補全的功能50
4.4.2 自動送出方面的技巧50
4.4.3 如何得到psql中指令實際執行的sql51
4.5 小結53
第5章資料類型54
5.1 類型介紹54
5.1.1 類型的分類54
5.1.2 類型輸入與轉換55
5.2 布爾類型56
5.2.1 布爾類型解釋56
5.2.2 布爾類型的操作符58
5.3 數值類型59
5.3.1 數值類型解釋59
5.3.2 整數類型 59
5.3.3 精确的小數類型59
5.3.4 浮點數類型60
5.3.5 序列類型61
5.3.6 貨币類型61
5.3.7 數學函數和操作符62
5.4 字元串類型64
5.4.1 類型解釋64
5.4.2 字元串函數和操作符65
5.5 二進制資料類型67
5.5.1 二進制資料類型解釋67
5.5.2 二進制資料類型轉義表示67
5.5.3 二進制資料類型的函數68
5.6 位串類型69
5.6.1 位串類型解釋69
5.6.2 位串類型的使用69
5.6.3 位串的操作符及函數70
5.7 日期/時間類型71
5.7.1 日期/時間類型詳解71
5.7.2 日期輸入72
5.7.3 時間輸入73
5.7.4 特殊值75
5.7.5 函數和操作符清單75
5.7.6 時間函數77
5.7.7 extract和date_part函數80
5.8 枚舉類型81
5.8.1 枚舉類型的使用81
5.8.2 枚舉類型的說明82
5.8.3 枚舉類型的函數83
5.9 幾何類型84
5.9.1 幾何類型概況84
5.9.2 幾何類型的輸入84
5.9.3 幾何類型的操作符89
5.9.4 幾何類型的函數97
5.10 網絡位址類型98
5.10.1 網絡位址類型概況98
5.10.2 inet與cidr類型98
5.10.3 macaddr類型101
5.10.4 網絡位址類型的操作符101
5.10.5 網絡位址類型的函數102
5.11 複合類型103
5.11.1 複合類型的定義103
5.11.2 複合類型的輸入104
5.11.3 通路複合類型105
5.11.4 修改複合類型105
5.11.5 複合類型的輸入與輸出106
5.12 xml類型107
5.12.1 xml類型的輸入107
5.12.2 字元集的問題108
5.12.3 xml類型的函數109
5.13 json類型114
5.13.1 json類型簡介115
5.13.2 json類型的輸入與輸出115
5.13.3 json類型的操作符116
5.13.4 json類型的函數118
5.13.5 json類型的索引121
5.14 range類型125
5.14.1 range類型簡介125
5.14.2 建立range類型126
5.14.3 range類型的輸入與輸出127
5.14.4 range類型的操作符130
5.14.5 range類型的函數130
5.14.6 range類型的索引和限制131
5.15 數組類型132
5.15.1 數組類型的聲明132
5.15.2 如何輸入數組值133
5.15.3 通路數組135
5.15.4 修改數組137
5.15.5 數組的操作符138
5.15.6 數組的函數139
5.16 僞類型142
5.17 其他類型143
5.17.1 uuid類型143
5.17.2 pg_lsn 類型143
第6章邏輯結構管理145
6.1 資料庫邏輯結構介紹145
6.2 資料庫基本操作145
6.2.1 建立資料庫145
6.2.2 修改資料庫146
6.2.3 删除資料庫147
6.2.4 常見問題及解答147
6.3 模式148
6.3.1 模式的定義148
6.3.2 模式的使用148
6.3.3 公共模式150
6.3.4 模式的搜尋路徑150
6.3.5 模式的權限151
6.3.6 模式的移植性151
6.4 表152
6.4.1 建立表152
6.4.2 表的存儲屬性154
6.4.3 臨時表156
6.4.4 預設值158
6.4.5 限制159
6.4.6 修改表163
6.4.7 表繼承及分區表167
6.4.8 分區表168
6.5 觸發器173
6.5.1 建立觸發器173
6.5.2 語句級觸發器與行級觸發器175
6.5.3 before觸發器與after觸發器177
6.5.4 删除觸發器178
6.5.5 觸發器的行為179
6.5.6 觸發器函數中的特殊變量180
6.6 事件觸發器180
6.6.1 建立事件觸發器183
6.6.2 修改事件觸發器186
6.7 表空間186
6.7.1 表空間的定義186
6.7.2 表空間的使用186
6.8 視圖187
6.8.1 視圖的定義187
6.8.2 建立視圖188
6.8.3 可更新視圖189
6.9 索引191
6.9.1 索引簡介191
6.9.2 索引的分類192
6.9.3 建立索引192
6.9.4 并發建立索引193
6.9.5 修改索引196
6.9.6 删除索引196
6.10 使用者及權限管理197
6.10.1 使用者和角色197
6.10.2 建立使用者和角色198
6.10.3 權限的管理199
6.10.4 函數和觸發器的權限202
6.10.5 權限的總結202
6.10.6 權限的示例202
6.11 事務、并發、鎖203
6.11.1 acid203
6.11.2 ddl事務204
6.11.3 事務的使用204
6.11.4 savepoint205
6.11.5 事務隔離級别206
6.11.6 兩階段送出207
6.11.7 鎖機制209
6.11.8 死鎖及防範212
6.11.9 表級鎖指令lock table213
6.11.10 行級鎖指令213
6.11.11 鎖的檢視214
第7章postgresql的核心架構221
7.1 應用程式的通路接口221
7.1.1 通路接口總體圖221
7.1.2 不同編輯語言的postgresql驅動介紹222
7.2 程序及記憶體結構223
7.2.1 程序和記憶體架構圖223
7.2.2 主程序postmaster224
7.2.3 syslogger（系統日志）程序224
7.2.4 bgwriter（背景寫）程序225
7.2.5 walwriter（預寫式日志寫）程序225
7.2.6 pgarch（歸檔）程序225
7.2.7 autovacuum（自動清理）程序225
7.2.8 pgstat（統計資料收集）程序226
7.2.9 共享記憶體226
7.2.10 本地記憶體226
7.3 目錄結構227
7.3.1 安裝目錄的結構227
7.3.2 資料目錄的結構227
7.3.3 表空間的目錄228
第8章服務管理229
8.1 服務的啟停和建立229
8.1.1 啟停方法229
8.1.2 pg_ctl230
8.1.3 信号234
8.1.4 postgres及單使用者模式234
8.2 服務配置介紹235
8.2.1 配置參數235
8.2.2 連接配接配置項237
8.2.3 記憶體配置項240
8.2.4 預寫式日志的配置項241
8.2.5 錯誤報告和日志項243
8.3 通路控制配置檔案246
8.3.1 pg_hba.conf 檔案247
8.3.2 認證方法介紹248
8.3.3 認證方法實戰249
8.4 備份和還原249
8.4.1 邏輯備份249
8.4.2 pg_dump指令250
8.4.3 pg_restore指令254
8.4.4 pg_dump和pg_restore使用舉例257
8.4.5 實體備份258
8.4.6 使用lvm快照進行熱備份259
8.5 常用的管理指令261
8.5.1 檢視系統資訊的常用指令261
8.5.2 系統維護常用指令267
第三篇提高篇
第9章postgresql中執行計劃270
9.1 執行計劃的解釋270
9.1.1 explain指令270
9.1.2 explain輸出結果解釋271
9.1.3 explain使用示例272
9.1.4 全表掃描275
9.1.5 索引掃描275
9.1.6 位圖掃描275
9.1.7 條件過濾276
9.1.8 nestloop join277
9.1.9 hash join277
9.1.10 merge join278
9.2 與執行計劃相關的配置項279
9.2.1 enable_*參數 279
9.2.2 cost基準值參數279
9.2.3 基因查詢優化的參數280
9.2.4 其他執行計劃配置項281
9.3 統計資訊的收集282
9.3.1 統計資訊收集器的配置項282
9.3.2 sql執行的統計資訊輸出283
9.3.3 手工收集統計資訊283
第10章postgresql中的技術内幕285
10.1 表中的系統字段285
10.1.1 oid286
10.1.2 ctid288
10.1.3 xmin、xmax、cmin、cmax289
10.2 多版本并發控制290
10.2.1 多版本并發控制的原理290
10.2.2 postgresql中的多版本并發控制291
10.2.3 postgresql多版本的優劣分析293
10.3 實體存儲結構293
10.3.1 postgresql中的術語293
10.3.2 資料塊結構293
10.3.3 tuple結構294
10.3.4 資料塊空閑空間管理296
10.3.5 可見性映射表檔案298
10.4 技術解密298
10.4.1 index-only scans298
10.4.2 heap-only tuples300
第11章postgresql的特色功能302
11.1 規則系統302
11.1.1 select規則302
11.1.2 更新規則303
11.1.3 規則和權限306
11.1.4 規則和指令狀态307
11.1.5 規則與觸發器的比較308
11.2 模式比對和正規表達式308
11.2.1 postgresql中的模式比對和正規表達式介紹308
11.2.2 傳統sql的like 操作符309
11.2.3 similar to 正規表達式310
11.2.4 posix 正規表達式312
11.2.5 模式比對函數 substring313
11.3 listen與notify315
11.3.1 listen與notify的簡單示例315
11.3.2listen與notify的相關指令316
11.3.3 listen與notify的使用詳解317
11.4 索引的特色320
11.4.1 表達式上的索引320
11.4.2 部分索引320
11.4.3 gist索引323
11.4.4 sp-gist索引325
11.4.5 gin索引326
11.5 序列的使用328
11.5.1 序列的建立328
11.5.2 序列的使用及相關的函數329
11.5.3 常見問題及解答331
11.6 咨詢鎖的使用333
11.6.1 咨詢鎖的定義333
11.6.2 咨詢鎖的函數及使用333
11.6.3 常見問題及解答337
11.7 sql/med338
11.7.1 sql/med的介紹338
11.7.2 外部資料包裝器對象339
11.7.3 外部伺服器對象340
11.7.4 使用者映射對象341
11.7.5 外部表對象341
11.7.6 file_fdw使用執行個體342
11.7.7 postgres_fdw使用執行個體345
第12章資料庫優化347
12.1 優化準則和方法347
12.1.1 優化準則347
12.1.2 優化方法348
12.2 硬體知識348
12.2.1 cpu及伺服器體系結構348
12.2.2 記憶體349
12.2.3 硬碟350
12.3 檔案系統及i/o調優352
12.3.1 檔案系統的崩潰恢複352
12.3.2 ext2檔案系統353
12.3.3 ext3檔案系統353
12.3.4 ext4檔案系統354
12.3.5 xfs檔案系統355
12.3.6 barriers i/o355
12.3.7 i/o調優的方法356
12.4 性能監控359
12.4.1 資料庫性能視圖359
12.4.2 linux監控工具362
12.5資料庫配置優化364
12.5.1記憶體配置優化364
12.5.2 關于雙緩存的優化366
12.5.3 vacuum中的優化367
12.5.4 預寫式日志寫優化369
第13章standby資料庫的搭建371
13.1 standby資料庫原理371
13.1.1pitr原理371
13.1.2wal日志歸檔372
13.1.3流複制372
13.1.4standby的運作原理373
13.1.5 建立standby的步驟373
13.2 pg_basebackup指令行工具374
13.2.1 pg_basebackup介紹374
13.2.2 pg_basebackup的指令行參數375
13.2.3 pg_basebackup使用示例376
13.3 異步流複制hot standby的示例377
13.3.1 配置環境377
13.3.2 主資料庫的配置378
13.3.3 在standby上生成基礎備份378
13.3.4 啟動standby379
13.4 同步流複制的standby資料庫380
13.4.1 同步流複制的架構380
13.4.2 同步複制的配置381
13.4.3 配置執行個體381
13.5 檢查備庫及流複制情況383
13.5.1 檢查異步流複制的情況383
13.5.2 檢查同步流複制的情況384
13.5.3 視圖pg_stat_replication詳解385
13.5.4 檢視備庫的狀态385
13.6 hot standby的限制387
13.6.1 hot standby的查詢限制387
13.6.2 hot standby的查詢沖突處理389
13.7 恢複配置詳解390
13.7.1 歸檔恢複配置的配置項390
13.7.2 recovery target配置391
13.7.3 standby server配置 391
13.8 流複制的注意事項392
13.8.1 wal_keep_segments參數的配置392
13.8.2 vacuum_defer_cleanup_age參數的配置392
第四篇第三方開源軟體及架構篇
第14章pgbouncer394
14.1 pgbouncer 介紹394
14.2 pgbouncer中的概念395
14.3 pgbouncer的安裝方法395
14.4 pgbouncer的簡單使用395
14.4.1 簡單配置方法395
14.4.2 啟動pgbouncer396
14.4.3 停止pgbouncer397
14.4.4 檢視連接配接池資訊397
14.5 pgbouncer的配置檔案詳解399
14.5.1 “[databases]”部分的配置項399
14.5.2 “[pgbouncer]”部分的配置項399
14.5.3 使用者密碼檔案403
第15章slony-i的使用404
15.1slony-i中的概念404
15.1.1 叢集404
15.1.2 節點405
15.1.3 複制集合405
15.1.4 資料原始生産者、資料提供者和資料訂閱者405
15.1.5 slon守護程式405
15.1.6 slonik配置程式405
15.2 slony-i複制的限制405
15.3 在windows下使用pgadminiii安裝配置slony-i406
15.3.1 windows下安裝slony-i406
15.3.2windows配置 slony-i同步執行個體407
15.4在linux下安裝配置slony-i419
15.4.1編譯安裝slony-i419
15.4.2配置slony-i複制421
第16章bucardo的使用426
16.1bucardo中的概念426
16.1.1bucardo介紹426
16.1.2bucardo faq426
16.1.3 bucardo 同步中定義的概念427
16.2bucardo的安裝方法427
16.2.1bucardo的安裝步驟427
16.2.2安裝test-simple、extutils-makemaker、version428
16.2.3安裝dbi及dbd::pg428
16.2.4安裝dbix-safe429
16.2.5安裝bucardo源碼包429
16.3bucardo同步配置429
16.3.1示例環境429
16.3.2配置同步的簡要過程430
16.3.3 bucardo_ctl install431
16.3.4bucardo_ctl add db433
16.3.5 bucardo_ctl add table433
16.3.6 bucardo_ctl add herd433
16.3.7 bucardo_ctl add sync433
16.3.8 bucardo_ctl start434
16.4bucardo的日常維護434
16.4.1bucardo的觸發器日志清理434
16.4.2臨時停止和啟動同步的方法435
16.4.3新增表到同步的方法435
16.4.4移除某個表或序列的方法435
第17章pl/proxy的使用436
17.1pl/proxy中的概念436
17.1.1pl/proxy的定義436
17.1.2pl/proxy的特性說明438
17.2pl/proxy安裝及配置438
17.2.1編譯安裝438
17.2.2安裝規劃439
17.2.3配置過程439
17.3pl/proxy的叢集配置詳解443
17.3.1cluster configuration api方式444
17.3.2sql/med方式配置叢集446
17.4pl/proxy語言詳解446
17.4.1connect447
17.4.2cluster447
17.4.3run on447
17.4.4split448
17.4.5target448
17.5pl/proxy的一個高可用方案448
17.5.1方案介紹448
17.5.2方案架構449
17.5.3具體實施步驟449
第18章pgpool-ii的使用458
18.1 pgpool-ii中的概念458
18.1.1 pgpool-ii的定義458
18.1.2 pgpool-ii的架構459
18.1.3 pgpool-ii的工作模式460
18.1.4 pgpool-ii的程式子產品461
18.2 pgpool-ii安裝方法462
18.2.1 源碼安裝462
18.2.2 安裝 pgpool_regclass463
18.2.3 建立 insert_lock 表463
18.2.4 安裝c語言函數463
18.3 pgpool-ii配置快速入門463
18.3.1 pgpool-ii的配置檔案及啟停方法464
18.3.2 複制和負載均衡的示例466
18.3.3 使用流複制的主備模式的示例467
18.3.4 show指令468
18.4 pgpool-ii高可用配置方法471
18.4.1 pgpool-ii高可用切換及恢複的原理471
18.4.2 pgpool-ii的健康檢查473
18.4.3 複制和負載均衡模式的高可用示例473
18.4.4 使用流複制的主備模式下的高可用示例488
18.5 pgpool-ii的總結492
第19章postgres-xc的使用493
19.1 postgres-xc中的概念493
19.1.1 postgres-xc的定義493
19.1.2 postgres-xc的特點493
19.1.3 postgres-xc的性能494
19.1.4 postgres-xc的元件494
19.2 postgres-xc的安裝495
19.2.1 源碼安裝方法495
19.2.2 postgres-xc目錄及程式說明496
19.3 配置postgres-xc叢集497
19.3.1 叢集規劃497
19.3.2 初始化gtm498
19.3.3 初始化gtm的備庫498
19.3.4 初始化gtm proxy499
19.3.5 初始化coordinators、資料節點499
19.3.6 啟動叢集500
19.3.7 停止叢集501
19.3.8 配置叢集節點資訊502
19.4 postgres-xc的使用503
19.4.1 建表詳解503
19.4.2 使用限制509
19.4.3 重新分布資料510
19.4.4 增加coordinator節點的方法512
19.4.5 移除coordinator節點的方法513
19.4.6 增加datanode節點的方法513
19.4.7 移除datanode節點的方法514
第20章高可用性方案設計516
20.1 高可用架構基礎516
20.1.1 各種高可用架構介紹516
20.1.2 服務的可靠性設計517
20.1.3 資料可靠性設計517
20.2 基于共享存儲的高可用方案517
20.2.1 san存儲的方案517
20.2.2 drbd的方案518
20.3 wal日志同步或流複制同步的方案519
20.3.1 持續複制歸檔的standby的方法519
20.3.2 異步流複制的方案519
20.3.3 基于同步流複制方案519
20.4 基于觸發器的同步方案520
20.4.1 方案的特點520
20.4.2 基于觸發器方案的同步軟體介紹520
20.5 基于語句中間件的高可用方案520
20.5.1 方案的特點520
20.5.2 基于語句中間件的開源軟體介紹521
第一篇
準 備 篇
第1章postgresql簡介
第2章postgresql安裝與配置
第3章sql語言入門
第1章
postgresql簡介
本章将着重介紹postgresql資料庫的相關知識，讓沒有接觸過postgresql的讀者對它有一個初步的了解。
1.1什麼是postgresql
1.1.1postgresql概述
postgresql資料庫是目前功能最強大的開源資料庫，支援豐富的資料類型（如json和jsonb類型、數組類型）和自定義類型。而且它提供了豐富的接口，可以很容易地擴充它的功能，如可以在gist架構下實作自己的索引類型等，它還支援使用c語言寫自定義函數、觸發器，也支援使用流行的語言寫自定義函數，比如其中的pl/perl提供了使用perl語言寫自定義函數的功能，當然還有pl/python、pl/tcl，等等。
1.1.2postgresql的發展曆史
前身ingres：postgresql的前身是伯克利源于1977 年的 ingres 項目。這個項目是由著名的資料庫科學家michael stonebraker上司的。1982年，michael stonebraker離開伯克利大學，把ingres商業化，使之成為 relational technologies 公司的一個産品。後來 relational tecchnologies被computer associates（ca）收購。ingres 是一個非關系型的資料庫。
伯克利的 postgres 項目：20世紀80年代，資料庫系統中的一個主要問題是資料關系維護。在1985年michael stonebraker回到伯克利後，為了解決ingres中的資料關系維護問題，啟動了一個後ingres（post-ingres）的項目，這就是 postgres 的開端。 postgres項目是由防務進階研究項目局（darpa）、陸軍研究辦公室（aro）、國家科學基金（nsf） 以及 esl公司共同贊助的。從1986年開始，michael stonebraker 教授發表了一系列論文，探讨了新的資料庫的結構設計和擴充設計。第一個“示範性”系統在 1987 年便可使用了，并且在 1988 年的資料管理國際會議（acm-sigmod）上展出。1989年6月釋出了版本 1給一些外部的使用者使用。由于源代碼維護的時間日益增加，占用了太多本應用于資料庫研究的時間，為減少支援的負擔，伯克利的postgres 項目在版本 4.2 時正式終止。
postgres95：在 1994 年，來自中國香港的兩名伯克利的研究所學生andrew yu 和 jolly chen 向 postgres 中增加了現在sql 語言的解釋器，将postgres改名為 postgres95，随後将 postgres95 源代碼釋出到網際網路上供大家使用。它成為一個開放源碼的postgres 代碼的繼承者。
postgresql6.x：到了 1996 年，很明顯地看出“postgres95”這個名字已經經不起時間的考驗。于是起了一個新名字 postgresql，為postgres與sql的縮寫，即增加了sql功能的postgres的意思。同時版本号也沿用伯克利 postgres 項目的順序，從6.0開始。
postgresql7.1：postgresql 7.1是繼6.5版本之後又一個有巨大變化的版本，首先它引入了預寫式日志的功能。這樣，事務就擁有了完善的日志機制，可以提供更好的性能，還可以實作更優良的備份和災難恢複的能力（比如聯機熱備份和當機後的自動恢複）。其次是不再限制文本類型的資料段長度，這從很大程度上解決了postgresql大對象的問題。
postgres8.x：該版本可以在windows下運作，它具有一些新的特性。比如具有事務儲存點功能、改變字段的類型、表空間、即時恢複（即時恢複允許對伺服器進行連續的備份。既可以恢複到失敗那個點，也可以恢複到以前的任意事務）等功能。并且開始支援perl 伺服器端程式設計語言。
postgresql9.x：進入9.x版本，辨別着postgresql進入了黃金發展階段。2010年9月20日釋出了postgresql 9.0，大大增強了複制的功能（replication），比如增加了流複制功能（stream replicaction）和hot standby功能。從9.0開始，可以很友善地搭建主從資料庫。2011年9月12日釋出了postgresql9.1，在該版本中增加了同步複制功能（synchronous replication）；2012年9月10釋出了postgresql9.2，增加了級連複制的功能。
1.1.3 postgresql資料庫的優勢
postgresql有以下優勢：
postgresql資料庫是目前功能最強大的開源資料庫，它是最接近工業标準sql92的查詢語言，并且正在實作新的功能以相容最新的sql标準：sql2003。
穩定可靠：postgresql是唯一能做到資料零丢失的開源資料庫。有報道稱國外的部分銀行也在使用postgresql資料庫。
開源省錢： postgresql資料庫是開源的、免費的，而且是bsd協定，在使用和二次開發上基本沒有限制。
支援廣泛：postgresql 資料庫支援大量的主流開發語言，包括c、c++、perl、python、java、tcl，以及php等。
postgresql社群活躍：postgresql基本上每三個月推出一個更新檔版本，這意味着已知的bug很快會被修複，有應用場景的需求也會及時得到響應。
1.1.4 postgresql應用現狀和發展趨勢
postgresql目前在國外很流行，特别是近幾年使用postgresql資料庫的公司越來越多。比如，日本電信（ntt） 大量使用postgresql替代oracle資料庫，并且在 postgresql之上二次開發了postgres-xc，postgres-xc是對使用者完全相容postgresql接口的share-nothing 架構的資料庫叢集。網絡電話公司skype 也大量使用postgresql，并貢獻了一些與postgresql資料庫配套的開源軟體：
pl/proxy：postgresql中的資料水準拆分軟體
pgq：使用postgresql的消息隊列軟體
londiste：用c語言實作的在postgresql資料庫之間進行邏輯同步的軟體
全球最大的crm軟體服務提供商salesforce也開始使用postgresql，并招募了postgresql核心開發者tom lane。
2012年，美國聯邦機構全面轉向postgresql陣營；法國也正積極推動政府機構采用postgresql資料庫，進而取代商業資料庫。
在國内，越來越多的公司開始使用postgresql，如斯凱網絡（股票代碼：mobi）的背景資料庫基本使用的都是postgresql資料庫，去哪兒網（qunar.com）也大量使用了postgresql資料庫。
主流的雲服務提供商如亞馬遜、阿裡雲的rds（關系型資料庫服務）同樣提供了postgresql的支援。
更多的使用postgresql資料庫的情況可以見postgresql官方網站（http://www.postgresql.org/about/users/）。
1.2 postgresql資料庫與其他資料庫的對比
1.2.1postgresql與mysql資料庫的對比
可能有人會問，既然已經有一個人氣很高的開源資料庫mysql了，為什麼還要使用postgresql？這主要是因為在一些應用場景中，使用mysql有以下幾個缺點：
功能不夠強大：mysql的多表連接配接查詢方式隻支援“nest loop”，不支援“hash join”和“sort merge join”。不僅如此，還有很多sql文法它也不支援，子查詢性能比較低。由于它不支援sequence，有公司還為此專門開發了統一序号分發中心的軟體。
性能優化工具和度量資訊不足：mysql在運作過程中如果出現問題，隻産生很少的性能資料，很難讓維護人員準确定位問題産生的原因。mysql的複制是異步的，無法通過master/slave做到資料零丢失。一些第三方公司也有改造mysql源代碼實作同步複制，但這些方案要麼是沒有開源，要麼已開源卻又不是很穩定，是以，對于普通大衆來說，如何實作同步複制成了一個令人頭疼的問題。
線上操作功能較弱：如果在mysql表中加列，基本上是建立一個表，而且建索引時也會鎖定整張表，即在建索引的過程中，表不能做任何操作。一些大的網際網路公司或者是修改mysql源碼來實作線上ddl的功能，或者是通過上層架構來解決這個問題，如先在slave資料庫上把ddl做完，然後把應用從master庫切換到slave，再把原先的master上把ddl做完。第一種方法，需要公司有很強的mysql研發能力，第二種方法需要公司有較強的開發能力，能設計出較強的應用架構。這對于一些中小型公司來說不太容易實作。
相對這些mysql的弱點，postgresql有以下幾個優點：
postgresql功能強大：支援所有主流的多表連接配接查詢的方式（如：“nest loop”、“ hash join”“sort merge join”等）；支援絕大多數的sql文法（如：with子句）。postgresql是筆者見過的對正規表達式支援最強、内置函數也是最豐富的資料庫。字段類型還支援數組類型。除了可以使用pl/pgsql寫存儲過程外，還可以使用各種主流開發語言的文法（如：python語言的pl/python、perl語言的pl/perl來寫存儲過程）。這些強大的功能可以大大地節約開發資源。很多開發人員在postgresql上做開發時，會發現資料庫已幫自己實作了很多功能，甚至有一些業務功能都可直接使用資料庫的功能解決，不再需要寫代碼來實作了。
性能優化工具與度量資訊豐富：postgresql資料庫中有大量的性能視圖，可友善地定位問題（比如：可看到正在執行的sql，可通過鎖視圖看到誰在等待、哪條記錄被鎖定等）。postgresql中設計了專門的架構和程序用于收集性能資料，既有實體i/o方面的統計，也有表掃描及索引掃描方面的性能資料。
線上操作功能好：postgresql增加空值的列時，本質上隻是在系統表上把列定義上，無須對實體結構做更新，這就讓postgresql在加列時可以做到瞬間完成。postgresql還支援線上建索引的功能，建索引的過程可以不鎖更新操作。
從postgresql9.1開始，支援同步複制功能（synchronous replication），通過master和slave之間的複制可以實作零資料丢失的高可用方案。
另外，由于mysql對sql文法支援的功能較弱，基本上不适合做資料倉庫。雖然也有些廠商開發了基于mysql的資料倉庫存儲引擎（如infobright），但這個方案隻是解決了部分資料倉庫的問題，sql功能弱的問題仍無法完全解決。另外，infobright的社群版本功能上還有很多的限制，如不支援資料更新，不支援太多的并發執行（最多支援十幾個）等。而postgresql不僅支援複雜的sql，還支援大量的分析函數，非常适合做資料倉庫。
postgresql資料庫中還有一些支援移動網際網路時代的新功能，如空間索引。postgis是最著名的一個開源gis系統，它是postgresql中的一個插件，通過它可以很友善地解決lbs中的一些位置計算問題。
綜上所述，postgresql資料庫是一個功能強大，又帶有移動網際網路特征的開源資料庫。如果你僅僅是想把資料庫作為一個簡單的存儲功能使用（如一些大的網際網路公司），一些較複雜的功能都想放在應用中來實作，那麼選擇mysql或一些nosql産品都是合适的；如果你應用的資料通路很簡單（如大多數的blog系統），那麼後端使用mysql也是很合适的。但如果你的應用不像blog系統那麼簡單，而你又不想消耗太多的開發資源，那麼postgresql是一個明智的選擇。最有說服力的例子就是圖檔分享公司instagram，在使用python+postgresql架構後，隻是十幾個人就支援起了整個公司的業務。在資料庫中使用postgresql的感覺，就像在開發語言中使用python，會讓你的工作變得簡潔和高效。
1.2.2 postgresql與oracle資料庫的對比
從功能上說，postgresql要比oracle資料庫稍弱，如不支援索引組織表等。畢竟oracle資料庫是目前功能最強大的商業資料庫，但postgresql算是功能最強大的開源資料庫。
postgresql與oracle有很多相似之處：都是使用共享記憶體的程序結構，用戶端與資料庫伺服器建立一個連接配接後，資料庫伺服器就啟動一個程序為這個連接配接服務，這與mysql的線程模型不一樣。此外，postgresql的wal日志與oracle的redo日志都是記錄實體塊資料變化的，這與mysql的binlog也不同。
postgresql與oracle的不同之處在于：postgresql有更多的支援網際網路特征的功能。如postgresql資料類型支援網絡位址類型、xml類型、json類型、uuid類型，以及數組類型，有強大的正規表達式函數，where條件中可以使用正規表達式比對，可以使用python、perl等語言寫存儲過程等。
另外，postgresql更小巧。oracle安裝包動則幾個gb以上，postgresql安裝包隻有幾十mb大小。在任何一個環境都可以容易地安裝postgresql。
1.3 小結
本章主要給大家介紹了什麼是postgresql資料庫，它有哪些強大的功能，以及目前的一些應用情況，以便大家對postgresql有一個初步的認識。
第2章
postgresql安裝與配置
本章将着重介紹postgresql資料庫的安裝和配置方法。安裝方法分為兩類：一種是從二進制安裝包進行安裝，另一種是從源碼安裝。各個linux的發行版本中，都内置了postgresql的二進制安裝包，但内置的版本可能較舊。二進制包安裝的方法一般都是通過不同發行版本的linux下的包管理器進行的，如在redhat下是yum，在ubuntu下是apt-get。使用源碼安裝更靈活，使用者可以有更多的選擇，可以選擇較新的版本、配置不同的編譯選項，編譯出使用者需要的功能。
2.1 從發行版本安裝
2.1.1 在debian或ubuntu下的安裝
在debian和ubuntu下一般使用apt-get指令或aptitude指令來安裝軟體，指令如下：
sudo apt-get install postgresql
安裝完畢後，postgresql資料庫就啟動了，如果想進入資料庫，需要切換到postgres使用者下：
su - postgres
然後使用psql連接配接到資料庫中，從作業系統下的“postgres”使用者（注意這裡指的不是資料庫中的postgres使用者）連接配接資料庫是不需要密碼的，如下：
root@osdba-laptop:~# sudo su - postgres
postgres@osdba-laptop:~$ psql
psql (9.1.9)
type "help" for help.
postgres=# \l
list of databases
name | owner | encoding | collate | ctype | access
privileges
-----------+----------+----------+------------+------------+--------------------
postgres | postgres | utf8 | en_us.utf8 | en_us.utf8 |
template0 | postgres | utf8 | en_us.utf8 | en_us.utf8 | =c/postgres
+
| | | | |
postgres=ctc/postgres
template1 | postgres | utf8 | en_us.utf8 | en_us.utf8 | =c/postgres
(3 rows)
postgres=# \q
在上面的示例中，使用psql指令連接配接postgresql資料庫，psql是postgresql中的用戶端工具。“\l”是列出所有資料庫的指令，“\q”是退出psql的指令，在後面的章節中還會詳細介紹psql的使用方法。
在debian或ubuntu下，使用apt-get安裝完成的postgresql資料庫的資料目錄在/var/lib/postgresql/<dbversion>/main目錄下：
postgres@osdba-laptop:~$ cd /var/lib/postgresql/9.1/main
postgres@osdba-laptop:~/9.1/main$ ls -l
total 56
drwx------ 5 postgres postgres 4096 jun 29 11:55 base
drwx------ 2 postgres postgres 4096 jun 29 11:55 global
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_clog
drwx------ 4 postgres postgres 4096 jun 29 11:55 pg_multixact
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_notify
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_serial
drwx------ 2 postgres postgres 4096 jun 29 12:00 pg_stat_tmp
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_subtrans
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_tblspc
drwx------ 2 postgres postgres 4096 jun 29 11:55 pg_twophase
-rw------- 1 postgres postgres 4 jun 29 11:55 pg_version
drwx------ 3 postgres postgres 4096 jun 29 11:55 pg_xlog
-rw------- 1 postgres postgres 133 jun 29 11:55 postmaster.opts
-rw------- 1 postgres postgres 100 jun 29 11:55 postmaster.pid
lrwxrwxrwx 1 root root 36 jun 29 11:55 server.crt ->
/etc/ssl/certs/ssl-cert-snakeoil.pem
lrwxrwxrwx 1 root root 38 jun 29 11:55 server.key ->
/etc/ssl/private/ssl- cert-snakeoil.key
安裝完成後，可以使用linux下的服務管理指令service來啟停資料庫：
osdba@osdba-laptop:~$ sudo service postgresql status
9.1/main (port 5432): online
osdba@osdba-laptop:~$ sudo service postgresql stop
* stopping postgresql 9.1 database server
[ ok ]
osdba@osdba-laptop:~$ sudo service postgresql start
* starting postgresql 9.1 database server
[ ok ]
osdba@osdba-laptop:~$
2.1.2在redhat、centos或fedora下的安裝
在redhat、centos或fedora下可使用yum工具來安裝postgresql，但這些系統的軟體庫中自帶的postgresql版本較低，其版本情況見表2-1。
表2-1linux發行版本自帶的postgresql版本
linux發行版本 自帶的postgresql版本
rhel/centos/sl 5 8.1 (also supplies package postgresql84)
rhel/centos/sl 6 8.4
fedora 16、fedora 17 9.1
fedora 18 9.2
如果上面的postgresql版本能滿足你的需要，可按下面的步驟進行安裝，否則就參照2.3.1節中的内容進行安裝。
安裝指令如下：
yum install postgresql-server.x86_64
具體安裝過程如下：
[root@localhost ~]# yum install postgresql-server.x86_64
loaded plugins: fastestmirror
loading mirror speeds from cached hostfile
* base: mirrors.163.com
* extras: mirrors.163.com
* updates: mirrors.163.com
setting up install process
resolving dependencies
--> running transaction check
---> package postgresql-server.x86_64 0:8.4.13-1.el6_3 will be installed
--> processing dependency: postgresql-libs(x86-64) = 8.4.13-1.el6_3 for package: postgresql-server-8.4.13-1.el6_3.x86_64
--> processing dependency: postgresql(x86-64) = 8.4.13-1.el6_3 for package: postgresql-server-8.4.13-1.el6_3.x86_64
--> processing dependency: libpq.so.5()(64bit) for package: postgresql-server-8.4.13-1.el6_3.x86_64
---> package postgresql.x86_64 0:8.4.13-1.el6_3 will be installed
---> package postgresql-libs.x86_64 0:8.4.13-1.el6_3 will be installed
--> finished dependency resolution
dependencies resolved
================================================================================
package arch
version repository size
installing:
postgresql-server x86_64
8.4.13-1.el6_3 base 3.4 m
installing for dependencies:
postgresql x86_64
8.4.13-1.el6_3 base 2.8 m
postgresql-libs x86_64
8.4.13-1.el6_3 base 200 k
transaction summary
install 3 package(s)
total size: 6.4 m
installed size: 29 m
is this ok [y/n]: y
yum還會讓你選擇是否把一些依賴的包也安裝上，當然要選擇“y”：
downloading packages:
warning: rpmts_hdrfromfdno: header v3 rsa/sha1 signature, key id c105b9de: nokey
retrieving key from file:///etc/pki/rpm-gpg/rpm-gpg-key-centos-6
importing gpg key 0xc105b9de:
userid : centos-6 key (centos 6 official signing key) <[email protected]>
package: centos-release-6-4.el6.centos.10.x86_64
(@anaconda-centos-201303020151.x86_64/6.4)
from : /etc/pki/rpm-gpg/rpm-gpg-key-centos-6
running rpm_check_debug
running transaction test
transaction test succeeded
running transaction
installing : postgresql-libs-8.4.13-1.el6_3.x86_64
1/3
installing : postgresql-8.4.13-1.el6_3.x86_64
2/3
installing : postgresql-server-8.4.13-1.el6_3.x86_64
3/3
verifying : postgresql-server-8.4.13-1.el6_3.x86_64
verifying : postgresql-libs-8.4.13-1.el6_3.x86_64
verifying : postgresql-8.4.13-1.el6_3.x86_64
installed:
postgresql-server.x86_64 0:8.4.13-1.el6_3
dependency installed:
postgresql.x86_64 0:8.4.13-1.el6_3
postgresql-libs.x86_64 0:8.4.13-1.el6_3
complete!
[root@localhost ~]#
這樣就安裝好了。
在redhat下，安裝好後，postgresql服務并沒有啟動：
[root@localhost ~]# service postgresql status
postmaster is stopped
直接啟動會報錯：
[root@localhost ~]# service postgresql start
/var/lib/pgsql/data is missing. use "service postgresql initdb" to initialize the cluster first.
[failed]
上面是在提示資料庫還沒有初使用化，請按提示把資料庫初使用化：
[root@localhost ~]# service postgresql initdb
initializing database: [ ok ]
再啟動資料庫：
postmaster (pid 1345) is running...
切換到作業系統下的“postgres”使用者，登入資料庫：
[root@localhost ~]# su - postgres
-bash-4.1$ psql
psql (8.4.13)
list of databases
name | owner | encoding | collation | ctype | access
-----------+----------+----------+-------------+-------------+------------------
postgres | postgres | utf8 | en_us.utf-8 | en_us.utf-8 |
template0 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres
:
template1 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres
postgres=#
這樣就可以了。
也可以使用下面的指令把第三方貢獻的軟體包安裝上：
yum install postgresql-contrib.x86_64
在redhat或centos下，預設安裝上的postgresql的資料目錄在/var/lib/pgsql/data目錄下，如下：
-bash-4.1$ ls -l /var/lib/pgsql/data
total 80
drwx------. 5 postgres postgres 4096 jun 30 11:07 base
drwx------. 2 postgres postgres 4096 jun 30 11:08 global
drwx------. 2 postgres postgres 4096 jun 30 11:07 pg_clog
-rw-------. 1 postgres postgres 3411 jun 30 11:07 pg_hba.conf
-rw-------. 1 postgres postgres 1631 jun 30 11:07 pg_ident.conf
圖 2-1 postgresql官方網站drwx------. 4 postgres postgres 4096 jun 30 11:07 pg_multixact
drwx------. 2 postgres postgres 4096 jun 30 11:13 pg_stat_tmp
drwx------. 2 postgres postgres 4096 jun 30 11:07 pg_subtrans
drwx------. 2 postgres postgres 4096 jun 30 11:07 pg_tblspc
drwx------. 2 postgres postgres 4096 jun 30 11:07 pg_twophase
-rw-------. 1 postgres postgres 4 jun 30 11:07 pg_version
drwx------. 3 postgres postgres 4096 jun 30 11:07 pg_xlog
-rw-------. 1 postgres postgres 16886 jun 30 11:07 postgresql.conf
-rw-------. 1 postgres postgres 57 jun 30 11:08 postmaster.opts
-rw-------. 1 postgres postgres 45 jun 30 11:08 postmaster.pid
2.1.3 在windows下的安裝
在windows安裝時，先要到官網上下載下傳postgresql的windows安裝包，如圖2-1所示。
圖 2-1 postgresql官方網站
點頁面中的“download”，進入下載下傳頁面，如圖2-2所示。
圖2-2 postgresql官方下載下傳頁面
然後選擇下載下傳安裝包的類型為“windows”，進入“windows”安裝包的下載下傳界面，如圖2-3所示。
圖2-3 postgresql windows版本下載下傳頁面
這裡需要根據你的windows是32位的還是64位來選擇下載下傳合适的安裝包，如圖2-4所示。
圖2-4 postgresql windows版本下的各種類型下載下傳頁面
因為windows版本的安裝包，是enterprise db公司制作的，是以下載下傳時會顯示enterprisedb公司的界面，如圖2-5所示。
圖2-5 postgresql windows版本下載下傳轉到enterprisedb公司頁面
如果在可以下載下傳時就選擇運作，那麼在下載下傳後，就會直接運作安裝程式，當然也可以在下載下傳完後，再輕按兩下安裝程式，如圖2-6所示。
安裝程式運作後，即會顯示出安裝向導，點“next”即可，如圖2-7所示。
進入選擇安裝目錄界面，一般選擇預設安裝目錄即可，可直接點“next”，如圖2-8所示。
進入選擇資料目錄的界面，根據實際需要，選擇具體的目錄，然後點“next”，如圖2-9所示。
進入資料庫超級使用者postgres的密碼設定界面，以後可以使用這個超級使用者增加更多的資料庫使用者，設定完後點“next”，如圖2-10所示。
然後進入選擇資料庫監聽端口的界面，如果5432這個端口沒有被别的應用程式占用，那麼可使用這個預設端口，點“next”，如圖2-11所示。
選擇語言，使用預設設定就可以了，點“next”，如圖2-12所示。
進入準備安裝的界面，點“next”，如圖2-13所示。
開始安裝，如圖2-14所示。
安裝完畢後，會問你是否需要使用“stack builder”安裝一些附加的軟體。stack builder是一個安裝postgresql附加軟體的圖形化工具。如果不需要，可以把選擇框中的勾去掉，直接結束安裝。
如果沒有去掉，則會出現“stack builder”的界面，在該界面中會提示你為哪一個postgresql安裝附加軟體（出現此提示的原因是可以安裝多個不同版本的postgresql），如圖2-16所示。
這時會出現一個可以選擇安裝附加軟體的界面，根據需要選擇了附加軟體後，點“next”，如圖2-17所示。
如果是第一次安裝，不知道要安裝哪些附加軟體，可以點取消。以後需要時可以再次運作“stack builder”安裝相應的附加軟體。
2.1.4 發行版安裝總結
windows下的安裝是比較簡單的，隻需要運作圖形界面，基本上在安裝的過程中點選“next”項就可以完成安裝。
linux下各發行版本則使用相應版本的包管理器來進行安裝即可。
前面曾提到，從發行版本安裝postgresql，所安裝的一般不是最新版本，如果想安裝最新版本的postgresql，則要看下面介紹的方法或見2.3.1節的技巧。
2.2 從源碼安裝
2.2.1 編譯安裝過程介紹
這裡先把大緻的安裝過程介紹一下。
第一步：下載下傳源代碼。
第二步：編譯安裝。過程與linux下其他軟體的編譯安裝過程相同，都是“三闆斧”：
./configure
make
make install
第三步：編譯安裝完成後執行如下步驟。
1）使用initdb指令初使用化資料庫簇。
2）啟動資料庫執行個體。
3）建立一個應用使用的資料庫。
資料庫簇是指資料庫執行個體管理的系統檔案和各個資料庫檔案的一個集合。
2.2.2 下載下傳源代碼
打開postgresql的官方網站www.postgresql.org，如圖2-18所示。
圖2-18 postgresql官方網站界面
點選網站菜單中的download，進入下載下傳頁面，如圖2-19所示。
圖2-19 postgresql官方網站中的源代碼下載下傳界面
在下載下傳頁面中點左側的“source”，進入源代碼下載下傳頁面，如圖2-20所示。
圖2-20 postgresql官方源碼下載下傳中的選擇版本界面
在源代碼頁面中選擇合适的版本，比如v9.2.4，如圖2-21所示。
圖2-21 postgresql官方源碼下載下傳中的選擇v9.2.4版本界面
然後，在上面的頁面中選擇合适的壓縮包下載下傳就可以了，一般選擇bz2的壓縮包，因為這種格式體積較小。
2.2.3 編譯及安裝
預設情況下，安裝會用到資料庫中的壓縮功能，而這個功能的實作需要第三方的壓縮開發包zlib支援，在不同的linux發行版本下，此包的名字會不太一樣，但包的名字一般都含有“zlib”和“dev”兩個字元串，“dev”是“develop”即開發包的意思。如在ubuntu12.04下，可以使用下面的方法查找包的名稱：
osdba@osdba-laptop:~$ aptitude search zlib |grep dev
p libghc-bzlib-dev - haskell bindings to the bzip2 library
p libghc-bzlib-dev:i386 - haskell bindings to the bzip2 library
v libghc-bzlib-dev-0.5.0.3-77459: -
v libghc-bzlib-dev-0.5.0.3-f7d98 -
p libghc-zlib-bindings-dev - low-level bindings to zlib
p libghc-zlib-bindings-dev:i386 - low-level bindings to zlib
v libghc-zlib-bindings-dev-0.1.0. -
p libghc-zlib-conduit-dev - streaming compression/decompression via co
p libghc-zlib-conduit-dev:i386 - streaming compression/decompression via co
v libghc-zlib-conduit-dev-0.4.0.1 -
p libghc-zlib-dev - compression and decompression in the gzip
p libghc-zlib-dev:i386 - compression and decompression in the gzip
v libghc-zlib-dev-0.5.3.3-78ddb:i -
v libghc-zlib-dev-0.5.3.3-7baa4 -
p libghc-zlib-enum-dev - enumerator interface for zlib compression
p libghc-zlib-enum-dev:i386 - enumerator interface for zlib compression
v libghc-zlib-enum-dev-0.2.2.1-16 -
v libghc-zlib-enum-dev-0.2.2.1-f8 -
p libghc6-bzlib-dev - transitional dummy package
p libghc6-zlib-dev - transitional dummy package
p lua-zlib-dev - zlib development files for the lua languag
p lua-zlib-dev:i386 - zlib development files for the lua languag
p zlib1g-dbg - compression library - development
p zlib1g-dbg:i386 - compression library - development
i a zlib1g-dev - compression library - development
p zlib1g-dev:i386 - compression library - development
從上面列出的包來看，隻有“zlib1g-dev”的名稱與我們需要的zlib開發包最接近，進而确定在ubuntu12.10上（當然這還需要一些經驗）應該安裝這個包。
如果想要友善地在psql中使用上下鍵翻查曆史指令，按照postgresql官方手冊的說明，還需要安裝readline的開發包。與上面的方法類似，先查找包含“readline”和“dev”的包：
osdba@osdba-laptop:~$ aptitude search readline |grep dev
v lib32readline-dev -
p lib32readline-gplv2-dev - gnu readline and history libraries, develo
p lib32readline6-dev - gnu readline and history libraries, develo
v lib64readline-dev:i386 -
p lib64readline-gplv2-dev:i386 - gnu readline and history libraries, develo
p lib64readline6-dev:i386 - gnu readline and history libraries, develo
p libghc-readline-dev - interface to the gnu readline library
p libghc-readline-dev:i386 - interface to the gnu readline library
v libghc-readline-dev-1.0.1.0-52b -
v libghc-readline-dev-1.0.1.0-69f -
i libreadline-dev - gnu readline and history libraries, develo
p libreadline-dev:i386 - gnu readline and history libraries, develo
p libreadline-gplv2-dev - gnu readline and history libraries, develo
p libreadline-gplv2-dev:i386 - gnu readline and history libraries, develo
i a libreadline6-dev - gnu readline and history libraries, develo
p libreadline6-dev:i386 - gnu readline and history libraries, develo
從上面列出的包來看，隻有“libreadline6-dev”的名稱與我們需要的readline開發包最接近，是以應該安裝這個包。
把前面下載下傳的壓縮包解壓，如果該壓縮包名稱為postgresql-9.2.4.tar.bz2，解壓指令則為：
tar xvf postgresql-9.2.4.tar.bz2
對于postgresql8.x的版本，一般編譯安裝的第一闆斧是使用configure指令，如下：
./configure --prefix=/usr/local/pgsql8.4.17 --enable-thread-safety --with-perl --with-python
對于postgresql9.x的版本，一般編譯安裝的指令如下：
./configure --prefix=/usr/local/pgsql9.2.4 --with-perl --with-python
對比可以發現，在postgresql8.x中，編譯指令裡有“--enable-thread-safety”選項，而在postgresql9.x中沒有這個選項。因為在日常使用中，一般要求用戶端是線程安全的，postgresql9.x考慮到這個問題，預設為線程安全的了，而postgresql8.x沒有，是以要加上這個選項。
再看看下面兩個選項：
--with-perl： 加上這個選項，才能使用perl文法的pl/perl過程語言寫自定義函數，一般都需要。使用這個選項要求先安裝perl開發包，該包在ubuntu或debian下的名稱為：libperl-dev，可使用apt-get install libperl-dev安裝。
--with-python：加上這個選項，才能使用python文法的pl/python過程語言寫自定義函數，一般都需要。使用這個選項要求先安裝python-dev開發包，該包在ubuntu或debian下的名稱為：python-dev，可使用apt-get install python-dev安裝。
編譯安裝的第二闆斧是運作make指令，這個指令沒有什麼好說的，直接運作就可以了：
按官方文檔要求，使用make指令時，其版本要在gmake3.8以上，目前絕大多數的linux發行版本都滿足要求，是以在linux下一般不需要檢查make的版本，但如果是在其他非linux的unix平台上，還是先檢查一下make的版本比較好，指令如下：
osdba@osdba-laptop:~$ make --version
gnu make 3.81
copyright (c) 2006 free software foundation, inc.
在其他的unix平台上，有可能存在非gnu的make，這時gnu的make的名稱會是gmake。
編譯安裝的第三闆斧是運作make install指令。如果是在一般使用者下進行編譯的，可能對/usr/local目錄沒有寫的權限，而運作make install指令時是需要使用root權限的，是以在debian或ubuntu下可以使用sudo:
sudo make install
在前面我們看到--prefix設定的路徑為“/usr/local/pgsql9.2.4”，如果不設定這個路徑，預設的路徑将是“/usr/local”，為什麼要在此路徑上加上postgresql的版本号呢？這是為了更新友善。make install指令運作完之後，還要進入/usr/local目錄下，為/usr/local/pgsql9.2.4建立一個/usr/local/pgsql的連結：
cd /usr/locall9.2.
sudo ln -sf /usr/local/pgsql9.2.4 /usr/local/pgsql
如果postgresql9.2.5釋出了，在編譯postgresql9.2.5後，隻需把現有的資料庫停掉，然後把連結/usr/local/pgsql指向先前的版本/usr/local/pgsql9.2.5即可完成更新。是不是覺得很友善呢？
2.2.4 安裝後的配置
安裝完後，需要設定postgresql可執行檔案的路徑：
export path=/usr/local/pgsql/bin:$path
還要設定共享庫的路徑：
export ld_library_path=/usr/local/pgsql/lib
如果想讓以上配置對所有的使用者生效，可以把以上内容加到/etc/profile檔案中，/etc/profile中的内容看起來類似如下：
...
if [ -d /etc/profile.d ]; then
for i in /etc/profile.d/*.sh; do
if [ -r $i ]; then
. $i
fi
done
unset i
fi
export ld_library_path=/usr/local/pgsql/lib:$ld_library_path
如果想讓以上配置對目前使用者生效，在linux下可以把以上内容加到.bashrc檔案中，在其他unix下可以加到.profile檔案中。
有人問在linux下為何不加到.profile檔案或.bash_profile檔案中，這是因為有時在圖形界面下打開一個終端，.profile或.bash_profile不會生效。
2.2.5 建立資料庫簇
先設定資料庫中資料目錄的環境變量：
export pgdata=/home/osdba/pgdata
執行下面的指令建立資料庫簇：
initdb
這樣就完成了。
2.2.6 安裝contrib目錄下的工具
contrib下面有一些工具比較實用，一般都會安裝上，其安裝方法也與linux下的編譯過程相同，如下：
cd postgresql-9.2.3/contrib
2.2.7 啟動和停止資料庫
啟動資料庫的指令為：
pg_ctl start -d $pgdata
其中，環境變量$pgdata指向具體的postgresql資料庫的資料目錄，看以下示例：
osdba@osdba-laptop:~$ pg_ctl start -d /home/osdba/pgdata
server starting
停止資料庫的指令如下：
pg_ctl stop -d $pgdata [-m shutdown-mode]
其中-m是指定資料庫的停止方法，有以下三種：
smart：等所有的連接配接中止後，關閉資料庫。如果用戶端連接配接不終止，則無法關閉資料庫。
fast：快速關閉資料庫，斷開用戶端的連接配接，讓已有的事務復原，然後正常關閉資料庫。相當于oracle資料庫關閉時的immediate模式。
immediate：立即關閉資料庫，相當于資料庫程序立即停止，直接退出，下次啟動資料庫需要進行恢複。相當于oracle資料庫關閉時的abort模式。
postgresql下的immediate關機模式是相當于oracle中的abort的關機模式，而oracle下的immediate關機模式實際上對應的是postgresql下的fast，oracle dba尤其要注意這一點。
其中，比較常用的關閉資料庫的方法是“fast”方法。
2.2.8 編譯安裝時的常見問題及解決方法
問題一：./configure時報“error: zlib library not found”錯誤是怎麼回事？報錯資訊如下：
osdba@ubuntu01:~/src/postgresql-9.2.3$ ./configure
--prefix=/usr/local/pgsql9.2.3 --with-perl --with-python
checking build system type... x86_64-unknown-linux-gnu
....
checking for inflate in -lz... no
configure: error: zlib library not found
if you have zlib already installed, see config.log for details on the failure. it is possible the compiler isn't looking in the proper directory.
use --without-zlib to disable zlib support.
答：這是因為沒有安裝zlib開發包，安裝後将不再報錯。
問題二：已安裝了“libreadline6”的包，但./configure時仍報“error: readline library not found”錯誤是怎麼回事？報錯資訊如下：
checking for library containing readline... no
configure: error: readline library not found
if you have readline already installed, see config.log for details on the failure. it is possible the compiler isn't looking in the proper directory.
use --without-readline to disable readline support.
答：包安裝錯了，是需要安裝開發包，即安裝libreadline6-dev這個包，而不是libreadline6這個包。
問題三：在運作./configure指令時報以下警告，是否會導緻編譯出來的postgresql的功能缺失？警告資訊如下：
checking for bison... no
configure: warning:
*** without bison you will not be able to build postgresql from git nor
*** change any of the parser definition files. you can obtain bison from
*** a gnu mirror site. (if you are using the official distribution of
*** postgresql then you do not need to worry about this, because the bison
*** output is pre-generated.)
checking for flex... no
*** without flex you will not be able to build postgresql from git nor
*** change any of the scanner definition files. you can obtain flex from
*** postgresql then you do not need to worry about this because the flex
答：不會影響編譯出來的postgresql功能。這個警告的意思是沒有bison和flex是無法使用git方式編譯的。這裡沒有使用git，是以沒有關系。bison是自動生成文法分析器的程式，flex則是自動生成詞法分析器的程式，在postgresql主要用于sql的詞法解析和文法解析。因為在源碼包中已經生成了詞法解析和文法解析的c源代碼，是以沒有bison和flex，也能正常編譯。當然也可以把bison和flex這兩個工具安裝上，指令如下：
sudo aptitude install bison flex
問題四：在運作make時報“cannot find -lperl”的錯誤為什麼？報錯資訊如下：
gcc -o2 -wall -wmissing-prototypes -wpointer-arith
-wdeclaration-after-statement -wendif-labels -wmissing-format-attribute -wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-fpic -shared -o plperl.so plperl.o spi.o util.o -l../../../src/port
-wl,--as-needed -wl,-rpath,'/usr/lib/perl/5.14/core',--enable-new-dtags
-fstack-protector -l/usr/local/lib -l/usr/lib/perl/5.14/core -lperl -ldl -lm
-lpthread -lc -lcrypt
/usr/bin/ld: cannot find -lperl
collect2: error: ld returned 1 exit status
make[3]: *** [plperl.so] error 1
make[3]: leaving directory `/home/osdba/src/postgresql-9.2.3/src/pl/plperl'
make[2]: *** [all-plperl-recurse] error 2
make[2]: leaving directory `/home/osdba/src/postgresql-9.2.3/src/pl'
make[1]: *** [all-pl-recurse] error 2
make[1]: leaving directory `/home/osdba/src/postgresql-9.2.3/src'
make: *** [all-src-recurse] error 2
答：這是在./configure時加了--with-perl，卻沒有安裝perl開發包導緻的。注意，若沒有安裝perl開發包，在運作./configure時并不報錯，而是到運作make指令的時候才報錯。在debian或ubuntu下，隻要安裝libperl-dev包即可：
sudo aptitude install libperl-dev
2.3 安裝技巧介紹
2.3.1 在redhat、centos下使用二進制包安裝較新版本的方法
如果認為redhat或centos上自帶的postgresql版本太低，想要使用新版本，可以使用下面的方法安裝。
使用postgresql官方的rpm包，将新版本資訊加到版本庫中：
rpm -i
http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
然後使用yum install 指令安裝新版本：
yum install postgresql92-server.x86_64
再使用1.1.2節介紹的方法安裝新版本的postgresql:
[root@localhost ~]# service postgresql-9.2 status
is stopped
[root@localhost ~]# service postgresql-9.2 initdb
[root@localhost ~]# service postgresql-9.2 start
starting postgresql-9.2 service: [ ok ]
登入資料庫：
-bash-4.1$ psql
psql (9.2.4)
name | owner | encoding | collate | ctype | access
template0 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres
| | | | |
template1 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres
使用下面指令把相關的第三方貢獻包也安裝上：
yum install postgresql92-contrib.x86_64
新版本的postgresql的資料目錄在/var/lib/pgsql/<version>/data目錄下，“version”代表postgresql的版本，如9.2版本就安裝在/var/lib/pgsql/9.2/data目錄下。
2.3.2 如何使用較大的資料塊提高i/o性能
在資料倉庫中使用postgresql時，若希望使用較大的資料塊提高i/o性能怎麼辦？要解決這類問題，隻能采用從源碼安裝的方法，在執行./configure指令時指定較大的資料塊，一般也需要指定較大的wal日志塊和wal日志檔案的大小。如想指定128kb的資料塊、128kb的wal日志塊、64mb的wal日志檔案，則configure指令如下：
./configure --prefix=/usr/local/pgsql9.2.4 --with-perl --with-python
--with-blocksize=128 --with-wal-blocksize=128 --with-wal-segsize=64
使用此時編譯出來的postgresql程式建立的postgresql資料庫，不能使用其他塊大小的postgresql程式啟動。
2.4 postgresql的簡單配置
本節将簡單介紹postgresql的配置方法，更具體的配置操作會在後面的章節中介紹。postgresql資料庫的配置主要是通過修改資料目錄下的postgresql.conf檔案來實作的。
2.4.1 修改監聽的ip和端口
在資料目錄下編輯postgresql.conf檔案，找到如下内容：
#listen_addresses = 'localhost' # what ip address(es) to listen on;
#port = 5432 # (change requires restart)
其中，參數“listen_addresses”表示監聽的ip位址，預設是在“localhost”處監聽，也就是“127.0.0.1”的ip位址上監聽，這會讓遠端的主機無法登入這台資料庫，如果想從其他的機器上登入這台資料庫，需要把監聽位址改成實際網絡的位址，一種簡單的方法是，把這個位址改成“*”，表示在本地的所有位址上監聽。
參數“port”表示監聽的資料庫端口，預設為“5432”，可以不更改。如果一台機器上安裝了幾個資料庫執行個體（如安裝了幾個不同版本的postgresql），可以設定為不同的端口。
修改了這兩個參數後，需要重新開機資料庫才能生效。
2.4.2 與資料庫log相關的參數
來看看與log相關的幾個參數：
日志的收集一般是要打開的，是以需要設定：
logging_collector = on
日志的目錄一般使用預設值就可以了：
log_directory = 'pg_log'
日志的切換和是否選擇覆寫則可以使用如下幾種方案。
方案一：每天生成一個新的日志檔案。
配置方法如下：
log_filename = 'postgresql-%y-%m-%d_%h%m%s.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
方案二：每當日志寫滿一定的大小（如10mb空間），則切換一個日志。
log_rotation_age = 0
log_rotation_size = 10m
方案三：隻保留7天的日志，進行循環覆寫。
log_filename = 'postgresql-%a.log' #
log_truncate_on_rotation = on
log_rotation_size = 0
2.4.3 記憶體參數的設定
postgresql安裝完畢後，可以修改以下兩個主要記憶體參數。
shared_buffers：共享記憶體的大小，主要用于共享資料塊。
work_mem：單個sql執行時，排序、hash join所使用的記憶體，sql運作完後，記憶體就釋放了。
shared_buffers預設值為32mb，work_mem為1mb，如果你的機器上有足夠的記憶體，可以把這個參數改得大一些，這樣資料庫就可以緩存更多的資料塊，當讀取資料時，就可以從共享記憶體中讀，而不需要再從檔案上去讀取。
work_mem設定大一些，會讓排序操作快一些。
2.5 小結
本章講解了postgresql在不同平台下的二進制安裝方法，以及從源代碼進行安裝的方法。從前面的叙述中可以看出，在多數linux發行版本中都自帶了postgresql的二進制安裝包，可以直接使用linux發行版本中的包管理器進行安裝。不過這些自帶的postgresql版本都比較舊，如果想安裝較新的postgresql版本，可以從源代碼進行編譯安裝。源代碼編譯安裝也比較簡單，要注意的是，需要先把一些依賴的開發包安裝上。
第3章
sql語言入門
sql是結構化查詢語言（structured query language）的簡稱，它是最重要的關系型資料庫操作語言，并且它的影響已經超出了資料庫領域。比如，在hadoop中的hive就是一個sql接口。
本章将介紹一些通用的、最基礎的sql文法知識，以便于沒有接觸過資料庫的讀者能掌握最基礎的資料庫知識。這些使用文法不僅适用于postgresql資料庫，也适用于其他關系型資料庫，如mysql、oracle。本章是為從沒有接觸過sql的讀者準備的，對于已有sql基礎的讀者，可以略過此章。
3.1 sql語句文法簡介
3.1.1 語句的分類
sql指令一般分為dql、dml、ddl幾類。
dql：資料查詢語句，基本就是select查詢指令，用于資料查詢。
dml：data manipulation language的簡稱，即資料操縱語言，主要用于插入、更新、删除資料，是以也分為insert、update、delete三種語句。
ddl：data definition language的縮寫，即資料定義語言，主要用于建立、删除，以及修改表、索引等資料庫對象語言。
3.1.2 詞法結構
每次執行的sql可以由多條sql指令組成。多條sql指令之間由分号（“;”）分隔。
每個sql指令由一系列的記号組成，這些記号可以由關鍵字、辨別符、雙引号包圍的辨別符、常量、單引号包圍的文本常量和特殊的字元等組成。在 sql 指令裡可以有注釋，這些注釋在postgresql中等效于空白。
舉個例子，下面的指令從sql的文法上來說是合法的：
select * from osdba_table01;
update osdba_table set col1 = 614;
insert into osdba_table values (232, 'hello osdba');
該sql由三條指令組成。在sql中，多行指令可以在一行中，也可以在多行中。此外，單條指令也可以占用多行。
sql指令并未嚴格地像計算機語言一樣明确辨別哪些是指令、哪些是操作數或參數。sql的文法主要是讓你比較直覺地了解其意思。比如，查詢一個表的資料，就是由“select”+“要查詢的各列”+“from 表”這樣的文法組成的。後面的幾節會詳細叙述sql的用法。
3.2 ddl語句
ddl語句是建立、修改和删除表的語句，想要掌握sql語言，必須對它有一定的了解。
3.2.1 建表語句
表是關系型資料庫中最基本的對象，資料庫中的表與實際生活中的二維表格很相似，有很多列也有很多行，每一列有一個名稱，不同的列有不同的資料類型，比如，列可能是數字、文本字元串，也可能是日期類型。建表語句的一個簡單文法如下：
create table table_name (
col01_namme data_type,
col02_namme data_type,
col03_namme data_type,
col04_namme data_type,
};
其中“create”、“table”為關鍵字，是不變的，從字面上也很好了解，表示建立表。“table_name”表示表名，“col01_name”、 “col02_name”、 “col03_name”、 “col04_name”分别表示列名。“data_type”表示資料類型，不同的資料庫系統有不同的資料類型名稱，即使是相同意思的整數類型，在不同的資料庫系統中也有不同的類型名稱。變長的字元串在大多數資料庫中都可使用 “varchar”類型，比如postgresql、mysql和oracle資料庫等。整形資料在postgresql和mysql都可以使用“int”類型。日期類型的名稱一般為“date”。例如，要建立一張分數表score，包括“學生名稱（student_name）”、“國文成績（chinese_score）”、“數學成績（math_score）”、“考試日期（test_date）”四列，則建立這個表的sql如下：
create table score (
student_name varchar(40),
chinese_score int,
math_score int,
test_date date
);
如果按前面的安裝步驟安裝完了資料庫，之後就可以使用psql工具連接配接到postgresql資料庫了，執行上面的建表語句，如下：
osdba=# create table score (
osdba(# student_name varchar(40),
osdba(# chinese_score int,
osdba(# math_score int,
osdba(# test_date date
osdba(# );
create table
osdba=#
在psql中，可使用\d顯示資料庫中有哪些表，如下：
osdba=# \d
list of relations
schema | name | type | owner
--------+-------+-------+-------
public | score | table | osdba
(1 row)
這樣就看到了我們建的表。
使用“\d score”可以顯示這張表的定義情況：
osdba=# \d score
table "public.score"
column | type | modifiers
---------------+-----------------------+-----------
student_name | character varying(40) |
chinese_score | integer |
math_score | integer |
test_date | date |
顯示列的類型“character varying(40)”實際上與“varchar(40)”的意思是完全一樣的，“int”與“integer”的意思也是一樣的。
在建表的時候，可以指定表的主鍵，主鍵是表中行的唯一辨別，這個唯一辨別是不能重複的。在建立表的語句中，可以在列定義後面用“primary key”來指定這一列為主鍵，如下面的學生表：
create table student(no int primary key, student_name varchar(40), age int);
在該表中，學号(no)為主鍵，則在該列的定義後面加了“primary key”。在psql中示範如下：
osdba=# create table student(no int primary key, student_name varchar(40), age int);
notice: create table / primary key will create implicit index "student_pkey" for table "student"
細心的讀者會注意到“notice: create table / primary key will create implicit index "student_pkey" for table "student"”這句提醒，它表示系統為主鍵自動建立了一個隐含的索引“student_pkey”。
3.2.2 删除表語句
删除表的文法比較簡單，如下：
drop table table_name;
其中“table_name”表示要删除的表名。假設要删除前面建立的表student，則可以使用下面的sql：
drop table student;
3.3 dml語句
dml用于插入、更新和删除資料。主要包含insert語句、update語句和delete語句。
3.3.1 插入語句
可以使用下面的語句往前面建立的學生表（student）中插入資料：
insert into student values(1, '張三', 14);
由此可以看出，insert語句的文法為：以“insert into”關鍵字為首，後面跟表名，然後再跟“values”關鍵字，最後是由小括号包圍起來的以逗号分隔的各列資料，資料的順序與表定義時表列的順序是一樣的。當然也可以在表名後指定要插入資料列的順序，如下所示：
insert into student(no, age, student_name) values(2, 13, '李四');
在插入資料時，也可以指定某些列不插入資料，這時這些列的資料會被置為空，如下：
insert into student(no, student_name) values(2, '王二');
如果在psql中執行了下面的語句，使用select語句就可以查詢出資料，查詢的語句為：
select * from student;
select語句的具體用法會在後面的章節中介紹，現在隻需要知道這麼用就可以了。
我們檢視到的資料如下：
osdba=# select * from student;
no | student_name | age
----+--------------+-----
1 | 張三 | 14
2 | 李四 | 13
3 | 王二 |
從上面可以看出，在插入資料時，沒有提供的列資料會被置為null。
3.3.2 更新語句
假設要把student表中所有學生的年齡（age）更新為“15”，則更新語句如下：
update student set age = 15;
從上面的語句可以看出，更新語句以“update” 關鍵字開始，後面跟表名，然後是“set”關鍵字，表示要設定的資料，再後面就是要設定的資料的表達式“age = 15”，設定資料的表達式也很簡單，就是“列名=資料”的格式。
實際執行的效果如下：
osdba=# update student set age = 15;
update 3
1 | 張三 | 15
2 | 李四 | 15
3 | 王二 | 15
在更新資料時，還可以指定過濾表達式“where”，進而指定更新哪條資料或哪些資料，比如，要更新學号(no)為3的學生年齡為14歲，則使用下面的語句：
update student set age =14 where no = 3;
在set子句中，還可以同時更新多個列的值，如下所示：
update student set age =13, student_name='王明充' where no = 3;
3.3.3 删除語句
如果想删除學号（no）為3的記錄，語句如下：
delete from student where no = 3;
由此可見删除語句比較簡單，以“delete from”開始，後面跟表名，然後加“where”子句用于指定要删除的記錄。
當然也可以沒有“where”子句，這表明要删除整個表的資料，删除表student中所有資料的語句如下：
delete from student;
3.4 查詢語句
3.4.1 單表查詢語句
查詢student表中所有資料的語句為：
select no, student_name, age from student;
其中“select”是關鍵字，表示要查詢，後面跟多個列名，各列之間使用逗号分隔。其後的“from”是關鍵字，後面跟表的名稱。各個列可以是表的列名，也可以是一個表達式，如下：
select age+5 from student;
表達式中可以包括表的列，也可以隻是一個與表列無關的表達式，如下：
select no, 3+5 from student;
當表達式與表的列無關時，在postgresql和mysql中可以不使用“from 表名”，這樣一來，就可以當作電腦使用了：
osdba=# select 55+88;
?column?
----------
143
osdba=# select 10*2,3*5+2;
?column? | ?column?
----------+----------
20 | 17
如果想查詢表中所有列的資料，則可以使用“*”代表所有列，如下：
3.4.2 過濾條件的查詢
select語句後面可以通過指定where子句來指定要查詢哪條記錄或哪些記錄。比如，要查詢學号為3的記錄，其sql語句為：
osdba=# select * from student where no=3;
3 | 王明充 | 13
在where條件中也可以使用大于、小于的表達式。比如，想查詢年齡大于等于15歲的學生，其語句如下：
osdba=# select * from student where age >= 15;
1 | 張三 | 15
2 | 李四 | 15
(2 rows)
3.4.3 排序
使用排序子句可以對查詢出的資料進行排序，排序子句是在select語句後面再加上“order by”子句。比如，希望查詢出來的結果按年齡排序，則查詢語句如下：
osdba=# select * from student order by age;
排序子句“order by”應該在“where”子句之前，如果順序錯了，會報錯：
osdba=# select * from student order by age where age >= 15;
error: syntax error at or near "where"
line 1: select * from student order by age where age >= 15;
把“order by”子句放到“where”子句後面就不報錯了：
osdba=# select * from student where age >= 15 order by age;
1 | 張三 | 15
還可以按多個列進行排序。比如，根據“age”和“student_name”兩個列來排序：
osdba=# select * from student order by age,student_name;
也可以在排序子句的列名後加“desc”進行倒序排序：
osdba=# select * from student order by age desc;
osdba=# select * from student order by age desc,student_name;
3.4.4 分組查詢
如果需要統計不同年齡的學生人數，可以使用分組查詢，分組查詢子句的關鍵字為“group by”，用法如下：
osdba=# select age, count(*) from student group by age;
age | count
-----+-------
15 | 2
13 | 1
從上面可以看出，使用“group by”語句時，需要使用聚合函數，常用的聚合函數為“count”、“sum”等。
3.4.5 表join
表join也稱為多表關聯查詢。假設有一張班級表class，建表語句為：
create table class(no int primary key, class_name varchar(40));
表中的“no”表示班級編号，“class_name”表示班級名稱。
插入一些測試資料：
osdba=# insert into class values(1,'初二(1)班');
insert 0 1
osdba=# insert into class values(2,'初二(2)班');
osdba=# insert into class values(3,'初二(3)班');
osdba=# insert into class values(4,'初二(4)班');
osdba=# select * from class;
no | class_name
----+------------
1 | 初二(1)班
2 | 初二(2)班
3 | 初二(3)班
4 | 初二(4)班
(4 rows)
還有另一張學生表student，建表語句為：
create table student(no int primary key, student_name varchar(40), age int, class_no int);
也插入一些資料：
osdba=# insert into student values(1, '張三', 14, 1);
osdba=# insert into student values(2, '吳二', 15, 1);
osdba=# insert into student values(3, '李四', 13, 2);
osdba=# insert into student values(4, '吳三', 15, 2);
osdba=# insert into student values(5, '王二', 15, 3);
osdba=# insert into student values(6, '李三', 14, 3);
osdba=# insert into student values(7, '吳二', 15, 4);
osdba=# insert into student values(8, '張四', 14, 4);
no | student_name | age | class_no
----+--------------+-----+----------
1 | 張三 | 14 | 1
2 | 吳二 | 15 | 1
3 | 李四 | 13 | 2
4 | 吳三 | 15 | 2
5 | 王二 | 15 | 3
6 | 李三 | 14 | 3
7 | 吳二 | 15 | 4
8 | 張四 | 14 | 4
若現在想查詢出每個學生與班級的關系，那麼此時就需要關聯查詢兩張表：
select student_name, class_name from student, class
where student.class_no = class.no;
查詢出來的結果如下：
osdba=# select student_name, class_name from student, class
student_name | class_name
--------------+------------
張三 | 初二(1)班
吳二 | 初二(1)班
李四 | 初二(2)班
吳三 | 初二(2)班
王二 | 初二(3)班
李三 | 初二(3)班
吳二 | 初二(4)班
張四 | 初二(4)班
(8 rows)
表關聯查詢就是在where條件上加上需要關聯的條件（兩張表關聯）：
where student.class_no = class.no;
由于在兩張表中，有一些列的名稱是重複的，如在表student中no表示學生号，而在表class中表示班級号，是以在關鍵條件中要明确使用“表名”加“列名”來唯一定位這個列。如果輸入的表名比較長，不是很友善，這時可以給表起個别名，如下所示：
select student_name, class_name from student a, class b
where a.class_no = b.no;
上面的語句中，給表“student”起的别名為“a”，表“class”的别名為“b”，這時條件表達式中“b.no”就代表了表“class”中的“no”列。
在關聯查詢的where子句中可以再加上其他的過濾條件，如下：
osdba=# select student_name, class_name from student a, class b
where a.class_no = b.no and a.age > 14;
student_name | class_name
---------------+------------
吳二 | 初二(1)班
吳三 | 初二(2)班
王二 | 初二(3)班
吳二 | 初二(4)班
3.5 其他sql語句
3.5.1 insert into... select語句
使用insert into... select語句可以把資料從一張表插入到另一張表中，這個語句屬于dml語句。
假設建了一張學生表的備份表：student_bak
create table student_bak(no int primary key, student_name varchar(40), age int, class_no int);
可以使用下面的語句把資料備份到下面這張備份表中：
insert into student_bak select * from student;
實際示範如下：
osdba=# insert into student_bak select * from student;
insert 0 8
osdba=# select * from student_bak;
3.5.2 union語句
可以将從兩張表查詢出來的資料整合在一個結果集下，如：
select * from student where no = 1 union select * from student_bak where no = 2;
這裡的文法比較簡單，把兩個sql用“union”關鍵字連接配接起來就可以了。
結果如下：
osdba=# select * from student where no = 1 union select * from student_bak where no = 2;
注意，union可以把結果集中相同的兩條記錄合并成一條：
osdba=# select * from student where no = 1 union select * from student_bak where no = 1;
如果不想合并，請使用union all，如下：
osdba=# select * from student where no = 1 union all select * from student_bak where no = 1;
3.5.3 truncate table語句
truncate table語句的用途是清空表内容。不帶where條件子句的delete 語句也表示清空表的内容。從執行結果看，兩者實作了相同的功能，但兩者實作的原理是不一樣的。
truncate table是ddl語句，即資料定義語句，相當于用重新定義一個新表的方法把原先表的内容直接丢棄了，是以truncate table執行起來很快，而delete 是dml語句，可以認為delete 是把資料一條一條地删除，若要删除很多行資料，就會比較慢。
如果想把表student_bak中的資料清理掉，則可以使用如下指令：
truncate table student_bak;
3.6 小結
從前面的叙述可以看出，sql語言是一種聲明式程式設計語言，與指令式程式設計語言有較大的差異。聲明式程式設計語言主要是描述使用者需要做什麼，需要得到什麼結果的，不像指令式編輯需要描述怎麼做，過程是什麼。sql語言能夠智能地實作使用者的需要，而不需要使用者去關心具體的運作過程。完全用sql寫一個應用程式是不可能的，但非計算機專業人士也可以使用sql，因為sql是直覺易懂的。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)