PostgreSQL 數據庫實戰派

本書是基於作者多年的教學與實踐撰寫的，重點介紹PostgreSQL的核心原理與體系架構，涉及開發、運維、管理與架構等內容。本書共12章，涉及以下幾方面內容：PostgreSQL基礎，安裝與配置PostgreSQL，管理數據庫與數據庫實例，管理數據庫對象，並行查詢，事務與並發控制，應用程序開發，管理數據庫安全，備份與恢復，監控、診斷與優化數據庫，PostgreSQL的高可用架構，以及從Oracle遷移到PostgreSQL。

目錄

=
第1章 PostgreSQL基礎 1
1.1 PostgreSQL簡介 1
1.1.1 什麽是PostgreSQL 1
1.1.2 PostgreSQL的功能特性 2
1.2 PostgreSQL的體系架構 3
1.2.1 邏輯存儲結構 4
1.2.2 物理存儲結構 9
1.2.3 進程結構 17
1.2.4 內存結構 23
=
第2章 安裝與配置PostgreSQL 25
2.1 安裝CentOS操作系統 25
2.2 安裝PostgreSQL服務器 35
2.3 【實戰】使用PostgreSQL客戶端工具 37
2.3.1 命令行客戶端psql 38
2.3.2 圖形化客戶端pgAdmin 4 41
2.4 PostgreSQL服務器端的主配置文件詳解 45
2.4.1 基礎文件 46
2.4.2 連接和認證 46
2.4.3 資源使用 47
2.4.4 預寫日誌 49
2.4.5 復制 50
2.4.6 查詢調優 51
2.4.7 錯誤報告和日誌 53
2.4.8 進程標題 54
2.4.9 運行時統計數據 55
2.4.10 自動清理 55
2.4.11 客戶端連接默認值 56
2.4.12 鎖管理 57
2.4.13 版本和平臺兼容性 58
2.4.14 錯誤處理 58
2.4.15 預置選項 59
=
第3章 管理數據庫與數據庫實例 60
3.1 【實戰】管理數據庫和數據庫模板 60
3.1.1 管理數據庫的基本操作 60
3.1.2 管理數據庫模板 64
3.2 【實戰】管理數據庫的擴展 65
3.2.1 使用擴展訪問外部數據源 65
3.2.2 數據預熱擴展pg_prewarm 75
3.2.3 共享緩沖區監控擴展pg_buffercache 79
3.2.4 預寫日誌解析擴展pg_walinspect 81
3.3 管理數據庫實例 83
3.4 基於PostgreSQL的分佈式數據庫——Citus 85
3.4.1 Citus基礎 85
3.4.2 安裝與配置Citus 86
3.4.3 Citus中表的類型 91
3.4.4 Citus的配置參數 96
3.5 基於PostgreSQL的分佈式數據庫——Greenplum 96
3.5.1 Greenplum簡介 96
3.5.2 安裝與配置Greenplum 98
3.5.3 Greenplum中表的類型 101
=
第4章 管理數據庫對象 105
4.1 數據庫與模式 105
4.2 創建與管理表 107
4.2.1 PostgreSQL的數據類型 107
4.2.2 【實戰】PostgreSQL表的基本操作 110
4.2.3 數據的約束條件 111
4.2.4 表中的碎片 115
4.2.5 統計信息 118
4.2.5.1 查看數據庫的統計信息 118
4.2.5.2 使用analyze命令手動收集數據庫的統計信息 120
4.2.5.3 瞭解統計信息收集器的配置項 121
4.2.6 【實戰】使用PostgreSQL的臨時表 122
4.3 在查詢時使用索引 123
4.3.1 索引的基礎知識 123
4.3.2 索引的類型 127
4.3.3 【實戰】索引的維護 130
4.3.3.1 使用僅索引掃描 130
4.3.3.2 避免重復索引 131
4.3.3.3 清除未使用的索引 132
4.4 使用視圖簡化查詢語句 133
4.4.1 什麽是視圖 133
4.4.2 視圖的基本操作 133
4.4.3 【實戰】在PostgreSQL中使用臨時視圖 135
4.4.4 物化視圖 136
4.5 序列 138
=
第5章 並行查詢 141
5.1 並行查詢是如何工作的 141
5.2 何時會用到並行查詢 142
5.3 【實戰】查看並行查詢的執行計劃 143
5.3.1 並行掃描 144
5.3.2 並行連接 146
5.4 並行查詢的限制 151
=
第6章 事務與並發控制 152
6.1 PostgreSQL的事務 152
6.1.1 事務簡介 152
6.1.2 控制事務 154
6.1.2.1 事務的控制語句 154
6.1.2.2 【實戰】使用事務的控制語句 154
6.1.3 事務的並發 157
6.1.3.1 事務隔離級別 157
6.1.3.2 【實戰】事務的臟讀 158
6.1.3.3 【實戰】事務的不可重復讀 159
6.2 PostgreSQL的鎖 161
6.2.1 鎖的類型 162
6.2.2 死鎖 168
6.2.2.1 【實戰】模擬死鎖的產生 169
6.2.2.2 如何避免死鎖 171
=
第7章 應用程序開發 173
7.1 PL/pgSQL基礎 173
7.1.1 【實戰】開發第一個PL/pgSQL程序 174
7.1.2 使用PL/pgSQL的基本數據類型 175
7.1.3 使用PL/pgSQL的高級數據類型 176
7.2 PL/pgSQL面向過程編程 177
7.2.1 在PL/pgSQL程序中使用條件判斷 177
7.2.2 在PL/pgSQL程序中使用循環 178
7.2.3 在PL/pgSQL程序中使用游標 180
7.2.4 在PL/pgSQL程序中處理例外 182
7.2.4.1 處理系統預定義例外 182
7.2.4.2 處理用戶自定義例外 183
7.3 【實戰】綜合案例——基於員工表統計各工資段的
員工人數及各部門的工資總額 184
7.4 使用PL/pgSQL開發存儲過程與存儲函數 187
7.4.1 存儲過程與存儲函數 187
7.4.2 【實戰】創建和使用存儲過程 187
7.4.3 【實戰】創建和使用存儲函數 189
7.4.4 【實戰】設置存儲過程中的inout參數 189
7.4.5 【實戰】在inout參數中使用游標 191
7.5 使用PL/pgSQL開發觸發器 192
7.5.1 常規觸發器 192
7.5.1.1 常規觸發器的定義 192
7.5.1.2 【實戰】利用常規觸發器實現安全性檢查 193
7.5.1.3 【實戰】利用常規觸發器進行數據確認 194
7.5.1.4 【實戰】利用常規觸發器實現審計 195
7.5.2 事件觸發器 197
=
第8章 管理數據庫安全 199
8.1 用戶管理 199
8.1.1 用戶與角色 199
8.1.2 管理用戶的密碼 204
8.1.2.1 使用SQL語句管理用戶的密碼 205
8.1.2.2 使用passwordcheck插件檢查密碼的復雜度 206
8.1.3 預定義角色 207
8.2 權限管理 207
8.2.1 【實戰】在創建用戶時指定的權限 207
8.2.2 使用grant命令和revoke命令管理的權限 210
8.2.3 在授權時使用admin option選項和grant option選項 215
8.2.4 使用組角色管理權限 216
8.2.5 使用set role命令顯示啟用角色的權限 219
8.3 審計管理 222
8.3.1 PostgreSQL的審計日誌功能 222
8.3.2 PostgreSQL的審計擴展插件pgaudit 224
=
第9章 備份與恢復 228
9.1 備份與恢復的基本概念 228
9.1.1 數據庫的故障類型 228
9.1.2 備份的分類 231
9.2 設置PostgreSQL的日誌歸檔 232
9.2.1 【實戰】設置PostgreSQL的歸檔模式 232
9.2.2 【實戰】管理過期的歸檔日誌文件 235
9.2.2.1 通過配置腳本定期刪除歸檔日誌文件 235
9.2.2.2 通過手動方式刪除歸檔日誌文件 236
9.3 通過SQL轉儲實現邏輯備份與恢復 238
9.3.1 【實戰】使用pg_dump完成SQL轉儲 238
9.3.2 【實戰】使用pg_dumpall完成SQL轉儲 244
9.4 文件系統級別的備份與恢復 247
9.4.1 【實戰】第一個PostgreSQL文件系統級別的備份與恢復 248
9.4.2 【實戰】使用pg_basebackup完成熱備份與恢復 250
9.4.2.1 在主數據庫服務器上使用pg_basebackup 251
9.4.2.2 在從數據庫服務器上使用pg_basebackup 253
9.4.2.3 使用pg_basebackup的備份進行恢復 254
9.5 【實戰】連續歸檔與基於時間點的恢復 257
9.5.1 創建基礎備份與連續歸檔 257
9.5.2 執行基於時間點的數據恢復 260
9.5.3 執行恢復到指定恢復點的數據恢復 261
9.5.4 恢復到指定事務上 262
9.5.5 恢復到最近時間點上 263
9.6 使用第三方備份恢復工具pg_rman 264
9.6.1 安裝與配置pg_rman 264
9.6.2 初始化pg_rman 267
9.6.3 使用pg_rman進行備份 269
9.6.3.1 使用pg_rman進行全量備份 269
9.6.3.2 使用pg_rman進行增量備份 270
9.6.4 使用pg_rman進行恢復 272
9.6.4.1 原地覆蓋式恢復 273
9.6.4.2 設置新的$PGDATA目錄式恢復 275
=
第10章 監控、診斷與優化數據庫 276
10.1 使用pgbench進行基準測試 276
10.1.1 數據庫基準測試簡介 276
10.1.2 【實戰】使用pgbench進行數據庫基準測試 276
10.1.2.1 使用pgbench進行基準測試 278
10.1.2.2 使用pgbench內置腳本進行基準測試 281
10.1.2.3 使用自定義腳本進行基準測試 284
10.2 使用擴展監控和診斷數據庫 285
10.2.1 使用pg_top擴展監控數據庫 285
10.2.1.1 pg_top擴展的安裝與配置 285
10.2.1.2 【實戰】使用pg_top擴展監控數據庫 287
10.2.1.3 pg_top擴展的常用參數 289
10.2.2 使用pg_stat_statements擴展監控SQL運行 290
10.2.2.1 pg_stat_statements擴展的安裝與配置 290
10.2.2.2 【實戰】使用pg_stat_statements擴展監控SQL語句的執行 293
10.2.3 使用pg_stat_monitor擴展查詢性能監控 296
10.2.3.1 pg_stat_monitor擴展的安裝與配置 296
10.2.3.2 【實戰】使用pg_stat_monitor擴展監控SQL語句的執行 298
10.2.3.3 pg_stat_monitor視圖和pg_stat_statements視圖的差異 301
10.2.4 使用auto_explain擴展監控慢查詢 302
10.2.4.1 【實戰】使用auto_explain擴展記錄慢查詢 302
10.2.4.2 auto_explain擴展的配置參數 304
10.2.5 使用pg_profile擴展生成數據庫性能報告 306
10.2.5.1 pg_profile擴展的安裝與配置 306
10.2.5.2 【實戰】使用性能報告診斷數據庫 308
10.2.5.3 分析數據庫性能報告 310
10.3 【實戰】使用PostgreSQL的分區 312
10.3.1 範圍分區 313
10.3.2 列表分區 315
10.3.3 哈希分區 317
10.4 優化PostgreSQL服務器 319
10.4.1 數據庫性能優化基礎 320
10.4.1.1 數據庫性能優化的三大問題 320
10.4.1.2 數據庫的性能指標 320
10.4.1.3 影響數據庫性能的外部因素 321
10.4.2 使用監控工具監控Linux操作系統的性能 321
10.4.2.1 磁盤監控工具 321
10.4.2.2 CPU監控工具 324
10.4.2.3 內存監控工具 325
10.4.2.4 網絡監控工具 326
10.4.3 優化數據庫存儲性能 327
10.4.3.1 數據庫的主從復制 327
10.4.3.2 數據庫分片 328
10.4.4 優化PostgreSQL的配置參數 329
10.4.5 PostgreSQL的性能視圖 333
=
第11章 PostgreSQL的高可用架構 335
11.1 基於Keepalived的高可用架構 335
11.1.1 基於Keepalived的PostgreSQL高可用架構 335
11.1.2 【實戰】基於Keepalived部署PostgreSQL的高可用架構 336
11.1.2.1 部署基於流復制的主從復制集群 336
11.1.2.2 手動完成主從復制集群的主從切換 341
11.1.2.3 基於Keepalived自動完成主從復制集群的主從切換 343
11.2 基於pg_auto_failover擴展的高可用架構 349
11.2.1 pg_auto_failover擴展的體系架構 349
11.2.1.1 單備節點架構 350
11.2.1.2 雙備節點架構 350
11.2.1.3 三備節點架構 350
11.2.1.4 異步節點多備架構 351
11.2.2 搭建基於pg_auto_failover擴展的高可用集群 352
11.2.2.1 安裝與配置pg_auto_failover擴展 352
11.2.2.2 配置pg_auto_failover擴展的開機自啟動 357
11.2.3 pg_auto_failover擴展的故障轉移 359
11.2.3.1 集群的主從切換 359
11.2.3.2 客戶端的故障轉移 362
11.2.4 pg_auto_failover擴展故障轉移的狀態機 366
11.2.4.1 狀態機中狀態的轉換 366
11.2.4.2 pg_auto_failover集群的調度過程 367
11.3 基於數據庫中間件pgpool-II的高可用架構 368
11.3.1 pgpool-II簡介 368
11.3.1.1 數據庫中間件的定義 368
11.3.1.2 pgpool-II的功能特性 369
11.3.1.3 基於pgpool-II的PostgreSQL高可用集群 370
11.3.2 【實戰】配置基於pgpool-II的PostgreSQL高可用集群 370
11.3.2.1 準備pgpool-II環境 371
11.3.2.2 配置基於pgpool-II的讀/寫分離 372
11.3.2.3 配置pgpool-II的高可用架構——看門狗 377
=
第12章 從Oracle遷移到PostgreSQL 381
12.1 從Oracle遷移到PostgreSQL基礎 381
12.1.1 Oracle的體系架構 381
12.1.1.1 客戶端組件和服務器端組件 381
12.1.1.2 Oracle的邏輯存儲結構 382
12.1.1.3 Oracle的物理存儲結構 382
12.1.1.4 Oracle的進程結構 383
12.1.1.5 Oracle的內存結構 385
12.1.2 對比Oracle和PostgreSQL的數據庫對象的差異 385
12.1.2.1 數據庫和數據庫實例的差異 386
12.1.2.2 用戶、方案與角色的差異 386
12.1.2.3 表空間的差異 387
12.1.2.4 表的差異 387
12.1.2.5 數據類型的差異 388
12.1.3 確定數據遷移方案 390
12.1.3.1 數據遷移前的準備 390
12.1.3.2 遷移數據庫對象 390
12.1.3.3 改造系統代碼 391
12.1.3.4 遷移工具的選擇 391
12.1.3.5 功能測試和性能測試 392
12.1.3.6 生產割接 392
12.2 【實戰】使用Ora2Pg完成數據遷移 392
12.2.1 安裝Ora2Pg 393
12.2.2 數據遷移過程 395
12.2.2.1 Oracle表結構的遷移 395
12.2.2.2 Oracle表中數據的遷移 397
12.2.2.3 Oracle中視圖的遷移 399
12.2.2.4 Oracle中存儲過程的遷移 401