PostgreSQL數(shù)據(jù)庫實戰(zhàn)派

目錄

=
第1章 PostgreSQL基礎 1
1.1 PostgreSQL簡介 1
1.1.1 什么是PostgreSQL 1
1.1.2 PostgreSQL的功能特性 2
1.2 PostgreSQL的體系架構(gòu) 3
1.2.1 邏輯存儲結(jié)構(gòu) 4
1.2.2 物理存儲結(jié)構(gòu) 9
1.2.3 進程結(jié)構(gòu) 17
1.2.4 內(nèi)存結(jié)構(gòu) 23
=
第2章 安裝與配置PostgreSQL 25
2.1 安裝CentOS操作系統(tǒng) 25
2.2 安裝PostgreSQL服務器 35
2.3 【實戰(zhàn)】使用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 查詢調(diào)優(yōu) 51
2.4.7 錯誤報告和日志 53
2.4.8 進程標題 54
2.4.9 運行時統(tǒng)計數(shù)據(jù) 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章 管理數(shù)據(jù)庫與數(shù)據(jù)庫實例 60
3.1 【實戰(zhàn)】管理數(shù)據(jù)庫和數(shù)據(jù)庫模板 60
3.1.1 管理數(shù)據(jù)庫的基本操作 60
3.1.2 管理數(shù)據(jù)庫模板 64
3.2 【實戰(zhàn)】管理數(shù)據(jù)庫的擴展 65
3.2.1 使用擴展訪問外部數(shù)據(jù)源 65
3.2.2 數(shù)據(jù)預熱擴展pg_prewarm 75
3.2.3 共享緩沖區(qū)監(jiān)控擴展pg_buffercache 79
3.2.4 預寫日志解析擴展pg_walinspect 81
3.3 管理數(shù)據(jù)庫實例 83
3.4 基于PostgreSQL的分布式數(shù)據(jù)庫——Citus 85
3.4.1 Citus基礎 85
3.4.2 安裝與配置Citus 86
3.4.3 Citus中表的類型 91
3.4.4 Citus的配置參數(shù) 96
3.5 基于PostgreSQL的分布式數(shù)據(jù)庫——Greenplum 96
3.5.1 Greenplum簡介 96
3.5.2 安裝與配置Greenplum 98
3.5.3 Greenplum中表的類型 101
=
第4章 管理數(shù)據(jù)庫對象 105
4.1 數(shù)據(jù)庫與模式 105
4.2 創(chuàng)建與管理表 107
4.2.1 PostgreSQL的數(shù)據(jù)類型 107
4.2.2 【實戰(zhàn)】PostgreSQL表的基本操作 110
4.2.3 數(shù)據(jù)的約束條件 111
4.2.4 表中的碎片 115
4.2.5 統(tǒng)計信息 118
4.2.5.1 查看數(shù)據(jù)庫的統(tǒng)計信息 118
4.2.5.2 使用analyze命令手動收集數(shù)據(jù)庫的統(tǒng)計信息 120
4.2.5.3 了解統(tǒng)計信息收集器的配置項 121
4.2.6 【實戰(zhàn)】使用PostgreSQL的臨時表 122
4.3 在查詢時使用索引 123
4.3.1 索引的基礎知識 123
4.3.2 索引的類型 127
4.3.3 【實戰(zhàn)】索引的維護 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 【實戰(zhàn)】在PostgreSQL中使用臨時視圖 135
4.4.4 物化視圖 136
4.5 序列 138
=
第5章 并行查詢 141
5.1 并行查詢是如何工作的 141
5.2 何時會用到并行查詢 142
5.3 【實戰(zhàn)】查看并行查詢的執(zhí)行計劃 143
5.3.1 并行掃描 144
5.3.2 并行連接 146
5.4 并行查詢的限制 151
=
第6章 事務與并發(fā)控制 152
6.1 PostgreSQL的事務 152
6.1.1 事務簡介 152
6.1.2 控制事務 154
6.1.2.1 事務的控制語句 154
6.1.2.2 【實戰(zhàn)】使用事務的控制語句 154
6.1.3 事務的并發(fā) 157
6.1.3.1 事務隔離級別 157
6.1.3.2 【實戰(zhàn)】事務的臟讀 158
6.1.3.3 【實戰(zhàn)】事務的不可重復讀 159
6.2 PostgreSQL的鎖 161
6.2.1 鎖的類型 162
6.2.2 死鎖 168
6.2.2.1 【實戰(zhàn)】模擬死鎖的產(chǎn)生 169
6.2.2.2 如何避免死鎖 171
=
第7章 應用程序開發(fā) 173
7.1 PL/pgSQL基礎 173
7.1.1 【實戰(zhàn)】開發(fā)第一個PL/pgSQL程序 174
7.1.2 使用PL/pgSQL的基本數(shù)據(jù)類型 175
7.1.3 使用PL/pgSQL的高級數(shù)據(jù)類型 176
7.2 PL/pgSQL面向過程編程 177
7.2.1 在PL/pgSQL程序中使用條件判斷 177
7.2.2 在PL/pgSQL程序中使用循環(huán) 178
7.2.3 在PL/pgSQL程序中使用游標 180
7.2.4 在PL/pgSQL程序中處理例外 182
7.2.4.1 處理系統(tǒng)預定義例外 182
7.2.4.2 處理用戶自定義例外 183
7.3 【實戰(zhàn)】綜合案例——基于員工表統(tǒng)計各工資段的
員工人數(shù)及各部門的工資總額 184
7.4 使用PL/pgSQL開發(fā)存儲過程與存儲函數(shù) 187
7.4.1 存儲過程與存儲函數(shù) 187
7.4.2 【實戰(zhàn)】創(chuàng)建和使用存儲過程 187
7.4.3 【實戰(zhàn)】創(chuàng)建和使用存儲函數(shù) 189
7.4.4 【實戰(zhàn)】設置存儲過程中的inout參數(shù) 189
7.4.5 【實戰(zhàn)】在inout參數(shù)中使用游標 191
7.5 使用PL/pgSQL開發(fā)觸發(fā)器 192
7.5.1 常規(guī)觸發(fā)器 192
7.5.1.1 常規(guī)觸發(fā)器的定義 192
7.5.1.2 【實戰(zhàn)】利用常規(guī)觸發(fā)器實現(xiàn)安全性檢查 193
7.5.1.3 【實戰(zhàn)】利用常規(guī)觸發(fā)器進行數(shù)據(jù)確認 194
7.5.1.4 【實戰(zhàn)】利用常規(guī)觸發(fā)器實現(xiàn)審計 195
7.5.2 事件觸發(fā)器 197
=
第8章 管理數(shù)據(jù)庫安全 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 權(quán)限管理 207
8.2.1 【實戰(zhàn)】在創(chuàng)建用戶時指定的權(quán)限 207
8.2.2 使用grant命令和revoke命令管理的權(quán)限 210
8.2.3 在授權(quán)時使用admin option選項和grant option選項 215
8.2.4 使用組角色管理權(quán)限 216
8.2.5 使用set role命令顯示啟用角色的權(quán)限 219
8.3 審計管理 222
8.3.1 PostgreSQL的審計日志功能 222
8.3.2 PostgreSQL的審計擴展插件pgaudit 224
=
第9章 備份與恢復 228
9.1 備份與恢復的基本概念 228
9.1.1 數(shù)據(jù)庫的故障類型 228
9.1.2 備份的分類 231
9.2 設置PostgreSQL的日志歸檔 232
9.2.1 【實戰(zhàn)】設置PostgreSQL的歸檔模式 232
9.2.2 【實戰(zhàn)】管理過期的歸檔日志文件 235
9.2.2.1 通過配置腳本定期刪除歸檔日志文件 235
9.2.2.2 通過手動方式刪除歸檔日志文件 236
9.3 通過SQL轉(zhuǎn)儲實現(xiàn)邏輯備份與恢復 238
9.3.1 【實戰(zhàn)】使用pg_dump完成SQL轉(zhuǎn)儲 238
9.3.2 【實戰(zhàn)】使用pg_dumpall完成SQL轉(zhuǎn)儲 244
9.4 文件系統(tǒng)級別的備份與恢復 247
9.4.1 【實戰(zhàn)】第一個PostgreSQL文件系統(tǒng)級別的備份與恢復 248
9.4.2 【實戰(zhàn)】使用pg_basebackup完成熱備份與恢復 250
9.4.2.1 在主數(shù)據(jù)庫服務器上使用pg_basebackup 251
9.4.2.2 在從數(shù)據(jù)庫服務器上使用pg_basebackup 253
9.4.2.3 使用pg_basebackup的備份進行恢復 254
9.5 【實戰(zhàn)】連續(xù)歸檔與基于時間點的恢復 257
9.5.1 創(chuàng)建基礎備份與連續(xù)歸檔 257
9.5.2 執(zhí)行基于時間點的數(shù)據(jù)恢復 260
9.5.3 執(zhí)行恢復到指定恢復點的數(shù)據(jù)恢復 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章 監(jiān)控、診斷與優(yōu)化數(shù)據(jù)庫 276
10.1 使用pgbench進行基準測試 276
10.1.1 數(shù)據(jù)庫基準測試簡介 276
10.1.2 【實戰(zhàn)】使用pgbench進行數(shù)據(jù)庫基準測試 276
10.1.2.1 使用pgbench進行基準測試 278
10.1.2.2 使用pgbench內(nèi)置腳本進行基準測試 281
10.1.2.3 使用自定義腳本進行基準測試 284
10.2 使用擴展監(jiān)控和診斷數(shù)據(jù)庫 285
10.2.1 使用pg_top擴展監(jiān)控數(shù)據(jù)庫 285
10.2.1.1 pg_top擴展的安裝與配置 285
10.2.1.2 【實戰(zhàn)】使用pg_top擴展監(jiān)控數(shù)據(jù)庫 287
10.2.1.3 pg_top擴展的常用參數(shù) 289
10.2.2 使用pg_stat_statements擴展監(jiān)控SQL運行 290
10.2.2.1 pg_stat_statements擴展的安裝與配置 290
10.2.2.2 【實戰(zhàn)】使用pg_stat_statements擴展監(jiān)控SQL語句的執(zhí)行 293
10.2.3 使用pg_stat_monitor擴展查詢性能監(jiān)控 296
10.2.3.1 pg_stat_monitor擴展的安裝與配置 296
10.2.3.2 【實戰(zhàn)】使用pg_stat_monitor擴展監(jiān)控SQL語句的執(zhí)行 298
10.2.3.3 pg_stat_monitor視圖和pg_stat_statements視圖的差異 301
10.2.4 使用auto_explain擴展監(jiān)控慢查詢 302
10.2.4.1 【實戰(zhàn)】使用auto_explain擴展記錄慢查詢 302
10.2.4.2 auto_explain擴展的配置參數(shù) 304
10.2.5 使用pg_profile擴展生成數(shù)據(jù)庫性能報告 306
10.2.5.1 pg_profile擴展的安裝與配置 306
10.2.5.2 【實戰(zhàn)】使用性能報告診斷數(shù)據(jù)庫 308
10.2.5.3 分析數(shù)據(jù)庫性能報告 310
10.3 【實戰(zhàn)】使用PostgreSQL的分區(qū) 312
10.3.1 范圍分區(qū) 313
10.3.2 列表分區(qū) 315
10.3.3 哈希分區(qū) 317
10.4 優(yōu)化PostgreSQL服務器 319
10.4.1 數(shù)據(jù)庫性能優(yōu)化基礎 320
10.4.1.1 數(shù)據(jù)庫性能優(yōu)化的三大問題 320
10.4.1.2 數(shù)據(jù)庫的性能指標 320
10.4.1.3 影響數(shù)據(jù)庫性能的外部因素 321
10.4.2 使用監(jiān)控工具監(jiān)控Linux操作系統(tǒng)的性能 321
10.4.2.1 磁盤監(jiān)控工具 321
10.4.2.2 CPU監(jiān)控工具 324
10.4.2.3 內(nèi)存監(jiān)控工具 325
10.4.2.4 網(wǎng)絡監(jiān)控工具 326
10.4.3 優(yōu)化數(shù)據(jù)庫存儲性能 327
10.4.3.1 數(shù)據(jù)庫的主從復制