PostgreSQL修煉之道

《PostgreSQL修煉之道》是2015年機械工業出版社出版的圖書，作者是唐成。

基本介紹

• 中文名：PostgreSQL修煉之道
• 作者：唐成
• 出版社：機械工業出版社
• 出版時間：2015年4月1日
• ISBN：9787111498728

內容簡介

　　《PostgreSQL修煉之道：從小工到專家》是目前有關PostgreSQL資料庫領域比較全面和系統的中文著作，內容十分豐富。全書共20章，分為四個部分。

　　準備篇（1～3章）針對PostgreSQL資料庫的總體概況、安裝配置步驟及SQL基礎知識進行了介紹。基礎篇（4～8章）的內容是為了幫助對PostgreSQL資料庫不是很了解的讀者打好基礎。這部分介紹了psql工具的使用方法， PostgreSQL資料庫的數據類型和資料庫對象的操作方法，以及PostgreSQL資料庫的架構和資料庫服務的管理等知識。提高篇（9～13章）講解PostgreSQL的執行計畫、技術內幕及特色功能，最後還介紹了解資料庫最佳化方法和PostgreSQL Standby資料庫的搭建過程。若想早日成為PostgreSQL資料庫高手，請仔細閱讀這一部分內容。第三方開源軟體及架構篇（14～20章）介紹PostgreSQL中最常用開源連線池軟體PgBouncer、邏輯複製軟體Slony-I、邏輯同步軟體Bucardo、數據水平拆分功能的開源軟體 PLProxy、資料庫中間件軟體pgpool-II、集群資料庫軟體Postgres-XC的使用方法。最後還介紹了與PostgreSQL資料庫配套使用的一些開源軟體和搭建各種實用的高可用方案方面的知識。

作者簡介

　　唐成，擁有十幾年資料庫、作業系統、存儲領域的工作經驗。目前任沃趣科技首席資料庫架構師，從事資料庫高可用、高性能以及資料庫雲化等方面的研究工作。歷任阿里巴巴資料庫專家、高級資料庫專家，從事過阿里巴巴的線上Oracle、Greenplum、PostgreSQL資料庫的架構設計和運維工作，為阿里巴巴的PostgreSQL資料庫的布道者，推動阿里雲的關係型資料庫服務（RDS）中提供PostgreSQL資料庫服務，為阿里巴巴PostgreSQL資料庫方面的技術帶頭人。也曾任網易杭州研究院開發專家，主導了網易雲計算中的雲硬碟產品（類似有EBS）的設計和開發。

目錄

前言

第一篇準備篇

第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.5　PgBouncer的配置檔案詳解399

第15章Slony-I的使用404

15.1Slony-I中的概念404

15.2　Slony-I複製的限制405

15.3　在Windows下使用pgAdminIII安裝配置Slony-I406

15.4在Linux下安裝配置Slony-I419

第16章Bucardo的使用426

16.1Bucardo中的概念426

16.2Bucardo的安裝方法427

16.3Bucardo同步配置429

16.4Bucardo的日常維護434

第17章PL/Proxy的使用436

17.1PL/Proxy中的概念436

17.2PL/Proxy安裝及配置438

17.3PL/Proxy的集群配置詳解443

17.4PL/Proxy語言詳解446

17.5PL/Proxy的一個高可用方案448

17.5.3具體實施步驟449

第18章pgpool-II的使用458

18.1　pgpool-II中的概念458

18.2　pgpool-II安裝方法462

18.3　pgpool-II配置快速入門463

18.4　pgpool-II高可用配置方法471

18.5　pgpool-II的總結492

第19章Postgres-XC的使用493

19.1　Postgres-XC中的概念493

19.2　Postgres-XC的安裝495

19.3　配置Postgres-XC集群497

19.4　Postgres-XC的使用503

第20章高可用性方案設計516

20.1　高可用架構基礎516

20.2　基於共享存儲的高可用方案517

20.3　WAL日誌同步或流複製同步的方案519

20.4　基於觸發器的同步方案520

20.5　基於語句中間件的高可用方案520