內容簡介
《PostgreSQL 9 Administration Cookbook （第2版）中文版》提供了線上生產環境的PostgreSQL資料庫的常見問題和故障快速解答，這些都基於作者作為PostgreSQL資料庫培訓師、用戶和核心開發人員時的經驗積累。每個技術都被拆分為小的方法或建議，先給出包含可工作代碼的演示解決方案，然後解釋這樣做的原因及工作原理。《PostgreSQL 9 Administration Cookbook （第2版）中文版》意在成為新用戶和技術專家的桌面參考手冊。
《PostgreSQL 9 Administration Cookbook （第2版）中文版》涵蓋了PostgreSQL 9 所有的新功能。
編輯推薦
PostgreSQL是一款功能豐富的、支持大量平台的高級資料庫服務軟體，而本書是一本能幫你快速成長為一名合格的PostgreSQL DBA的經典管理書籍。
本書的作者是PostgreSQL的主要開發人員，譯者是PostgreSQL中國社區和中國用戶會的核心成員。
本書前6章介紹了PostgreSQL基礎，後6章由淺入深地介紹了PostgreSQL日常維護技巧、性能分析、備份恢復和重量級的複製技術。是一本不可多得的管理類書籍。
內容提要
《PostgreSQL 9 Administration Cookbook （第2版）中文版》提供了線上生產環境的PostgreSQL資料庫的常見問題和故障快速解答，這些都基於作者作為PostgreSQL資料庫培訓師、用戶和核心開發人員時的經驗積累。每個技術都被拆分為小的方法或建議，先給出包含可工作代碼的演示解決方案，然後解釋這樣做的原因及工作原理。《PostgreSQL 9 Administration Cookbook （第2版）中文版》意在成為新用戶和技術專家的桌面參考手冊。
《PostgreSQL 9 Administration Cookbook （第2版）中文版》涵蓋了PostgreSQL 9 所有的新功能。
目錄
第1章 邁出第一步 1
1.1 介紹 1
1.2 獲取PostgreSQL 7
1.3 連線到PostgreSQL服務 8
1.4 啟用網路/遠程用戶訪問 11
1.5 使用圖形化管理工具 12
1.6 使用 psql 查詢和腳本工具 16
1.7 安全地修改你的密碼 19
1.8 避免硬編碼你的密碼 20
1.9 使用連線服務檔案 22
1.10 連線失敗故障排除 23
第2章 瀏覽資料庫 25
2.1 介紹 25
2.2 當前服務程式的版本號是多少 26
2.3 服務程式運行時間是多少？ 27
2.4 定位資料庫服務的數據檔案 28
2.5 定位資料庫服務的日誌檔案 31
2.6 定位資料庫的系統標識 33
2.7 列出資料庫服務中的資料庫 35
2.8 資料庫中有多少張表 38
2.9 一個資料庫占用了多少磁碟空間 40
2.10 一張表占用了多少磁碟空間 41
2.11 哪張表是最大的表 42
2.12 表里有多少行記錄 43
2.13 快速估算表里的記錄總數 44
2.14 列出資料庫中的擴展模組 48
2.15 理解對象的依賴關係 49
第3章 配置 52
3.1 介紹 52
3.2 閱讀手冊（RTFM） 53
3.3 規劃一個新資料庫 54
3.4 在程式中更改參數 55
3.5 查看當前的配置參數 57
3.6 哪些參數為非默認設定 58
3.7 更新參數檔案 60
3.8 為特定用戶組設定參數 62
3.9 基本服務配置清單 63
3.10 添加一個外部模組到PostgreSQL服務中 64
3.11 使用已安裝的模組 68
3.12 管理已安裝的擴展 70
第4章 服務控制 74
4.1 介紹 74
4.2 手動啟動資料庫服務 75
4.3 安全快速地停止服務 77
4.4 在緊急情況下關閉服務 77
4.5 重新載入服務配置檔案 78
4.6 快速重啟服務 80
4.7 阻止新的連線 81
4.8 限制每個用戶只允許一個會話 83
4.9 斷開用戶連線 84
4.10 為多租戶進行資料庫設計 86
4.11 使用多個模式（schema） 87
4.12 單獨給用戶分配資料庫 89
4.13 在一個系統上運行多個服務 90
4.14 配置連線池 92
4.15 在相同的主機和連線埠上訪問多個服務 95
第5章 表和數據 98
5.1 介紹 98
5.2 為資料庫對象選擇恰當的名稱 99
5.3 處理使用雙引號括起來的對象名 100
5.4 確保相同的名稱和相同的列定義 102
5.5 標記和刪除重複數據 106
5.6 避免出現重複行 110
5.7 為一組數據找到一個唯一主鍵 115
5.8 生成測試數據 117
5.9 隨機抽樣數據 120
5.10 從電子表格導入數據 122
5.11 從扁平檔案導入數據 124
第6章 安全 128
6.1 介紹 128
6.2 PostgreSQL的超級用戶 129
6.3 收回用戶對表的訪問權 130
6.4 賦予用戶對表的訪問權 134
6.5 建立一個新用戶 135
6.6 臨時阻止一個用戶的連線 137
6.7 刪除用戶而不刪除其數據 138
6.8 檢查是否所有的用戶都是使用安全的密碼 139
6.9 將受限的超級用戶許可權賦予指定用戶 140
6.10 審計DDL的改變 143
6.11 審計數據的改變 145
6.12 總是認清登錄的用戶 150
6.13 與LDAP集成 152
6.14 使用SSL進行連線 153
6.15 使用SSL證書來驗證客戶端 155
6.16 映射外部用戶為資料庫角色 158
6.17 加密敏感數據 159
第7章 資料庫管理 164
7.1 介紹 164
7.2 編寫一個腳本，要么全部執行成功，要么全部執行失敗 165
7.3 編寫一個psql腳本，一遇到錯誤就退出 168
7.4 在多張表上執行多個操作 169
7.5 添加/刪除表上的列 174
7.6 更改列的數據類型 175
7.7 更改數據類型的定義 179
7.8 添加/刪除模式（schema） 180
7.9 在不同schema之間移動對象 182
7.10 添加/刪除表空間 183
7.11 在不同表空間之間移動對象 186
7.12 訪問其他PostgreSQL資料庫中的對象 189
7.13 訪問其他外部資料庫中的對象 197
7.14 可更新視圖 199
7.15 使用物化視圖 204
第8章 監控和診斷 207
8.1 介紹 207
8.2 檢查用戶是否已經連線上來 211
8.3 檢查哪個查詢在運行 213
8.4 檢查哪個查詢正在運行或被阻塞 216
8.5 確定誰阻塞了一個查詢 217
8.6 殺掉指定會話 219
8.7 探測未決的準備事務 221
8.8 確定是否某人在使用某張表 222
8.9 確定一張表最後被使用的時間 223
8.10 臨時數據使用的磁碟空間 225
8.11 理解查詢變慢的原因 227
8.12 調查和報告一個缺陷 230
8.13 生成每日的日誌檔案錯誤信息摘要 231
8.14 分析你的查詢的實時性能 234
第9章 常規維護 236
9.1 介紹 236
9.2 控制自動資料庫維護 237
9.3 避免自動凍結和頁損壞 241
9.4 避免事務重疊 243
9.5 移除過期的準備事務 245
9.6 對於大量使用臨時表時需要的操作 247
9.7 識別和修復膨脹的表和索引 249
9.8 維護索引 253
9.9 添加約束而不檢查已有行 256
9.10 尋找未使用的索引 258
9.11 謹慎刪除不必要的索引 260
9.12 維護規劃 261
第10章 性能和並發 263
10.1 介紹 263
10.2 尋找執行緩慢的SQL語句 264
10.3 從pg_stat* 視圖收集常規統計信息 267
10.4 研究是什麼讓SQL緩慢 269
10.5 減少返回的行 273
10.6 簡化複雜的SQL 274
10.7 加速查詢的執行，而無須重寫 281
10.8 為什麼一個查詢不使用索引 284
10.9 強制讓一個查詢使用一個索引 285
10.10 使用樂觀鎖 287
10.11 報告性能問題 288
第11章 備份和恢復 290
11.1 介紹 290
11.2 理解和控制崩潰恢復 291
11.3 計畫備份 293
11.4 對一個資料庫進行熱邏輯備份 294
11.5 對所有資料庫進行熱邏輯備份 296
11.6 對一個表空間中的所有表進行熱邏輯備份 297
11.7 備份資料庫對象的定義 298
11.8 獨立的資料庫熱物理備份 299
11.9 線上物理備份和持續歸檔 302
11.10 恢復所有的資料庫 305
11.11 恢復到一個時間點 308
11.12 恢復一個被刪除/損壞的表 310
11.13 恢復一個被刪除/損壞的表空間 312
11.14 恢復一個被刪除/損壞的資料庫 314
11.15 為備份/恢復提速 315
11.16 增量/差異備份和恢復 317
11.17 使用Barman進行熱物理備份 319
11.18 使用Barman進行恢復 326
第12章 複製和升級 330
12.1 介紹 330
12.2 複製最佳實踐 337
12.3 基於日誌檔案傳送的複製——不推薦使用 338
12.4 建立流複製 341
12.5 建立流複製的安全機制 346
12.6 熱備機及讀可擴展性 347
12.7 管理流複製 351
12.8 使用repmgr 353
12.9 使用複製槽位 355
12.10 監控複製 357
12.11 性能和同步複製 360
12.12 延遲、暫停和同步複製 362
12.13 邏輯複製 364
12.14 雙向複製 367
12.15 歸檔事務日誌數據 370
12.16 升級——小版本 371
12.17 主版本原地升級 372
12.18 主版本線上升級 374
作者簡介
Simon Riggs 是2ndQuadrant 的CTO，PostgreSQL 社區活躍的代碼提交者。作為PostgreSQL 的主要開發人員，他為PostgreSQL 的貢獻超過十年時間，期間，他為每個發行版本都設計開發了很多新功能。他擅長的領域包括複製、性能調優、商業智慧型、管理和安全。在他的領導下，2ndQuadrant 現在已經成為開源PostgreSQL 的領導開發者和PostgreSQL 項目的白金贊助商，並廣泛服務於歐美、亞太、中東和非洲地區成百上千的用戶。
Simon 在很多交流會上都是活躍的發言者，並因在PostgreSQL 功能和複製方面的發言而被廣泛知曉。作為多種資料庫的開發工程師、架構師、數據分析師和系統設計師，他在近三十年間為遍布歐美的公司提供過服務。
Gianni Ciolli 是2ndQuadrant 義大利分公司的首席諮詢師，從2008 年開始就從事開發、諮詢和培訓工作。他在歐洲和海外的PostgreSQL 峰會中都有發言，他還擅長於函式語言和符號計算（Symbolic Computing）等IT 技能。
Gianni 擁有數學博士學位，並在代數幾何、理論物理和形式證明理論方面發表過研究報告。他之前在佛羅倫斯大學任教師和研究員。
Gianni 在自由軟體和開源軟體領域工作了近二十年。在2001 到2004 年期間，他是PLUG（Prato Linux User Group）公司的聯合創始人和總裁。他曾多次組織義大利的PostgreSQL 交流大會，在2013 年，他獲選加入ITPUG（Italian PostgreSQL Users Group）董事會。
現在，他和他的兒子居住在倫敦。他興趣廣泛，主要包括音樂、戲劇、詩歌，尤其喜歡體育競技，他還參加過全能競賽。
Hannu Krosing 是2ndQuadrant 的首席諮詢師以及Ambient Sound Investments 的技術顧問。作為Skype 公司的最初資料庫架構師，他負責設計了用於複製和擴展技術的SkyTools套件，為PostgreSQL 項目工作和貢獻十二餘年。
Gabriele Bartolini 是一個資深開源開發人員，2ndQuadrant 首席諮詢師， 國際PostgreSQL 社區的活躍成員。
Gabriele 擁有佛羅倫斯大學的統計學學位。他擅長於數據挖掘和數據倉庫，在澳大利亞和義大利從事過網頁流量分析工作。
他現在居住於普拉托（Prato），一個位於義大利托斯卡納（Tuscany）地區北部的小而充滿活力的城市。他的另一個家位於澳大利亞的墨爾本，他在那裡的莫納什大學學習，並在那裡的信息通信行業工作。
Gabriele 的興趣包括演奏他的Fender Stratocaster 電吉他，以及踢卡爾喬（calcio）足球聯賽。
前言
PostgreSQL 是一款支持大量平台的高級SQL 資料庫服務軟體，並快速成為世界上最流行的服務端資料庫之一，尤其是在性能、穩定性以及豐富功能方面享譽盛名。PostgreSQL 是歷史非常悠久的開源資料庫之一，完全免費使用，由一個非常多元化的全球性社區開發。總之，它就是一個很酷的東西！
PostgreSQL 最明顯的一個好處是，它是開源的，也就是說，你擁有一個非常寬鬆的許可證來安裝、使用以及發布PostgreSQL，而不需要付給任何人任何費用或版稅。另外，眾所周知，PostgreSQL 是能長時間穩定運行的資料庫，而且在很多情況下只需要少量維護甚至不需要維護。
總之，PostgreSQL 的保有成本很低。
《PostgreSQL 9 Administration Cookbook（ 第2 版）》提供了管理你的線上生產環境的PostgreSQL 資料庫所需要的信息。本書包含的一些見解直接來自於PostgreSQL 複製和恢復功能的主要作者，以及來自最成功的創業公司Skype 的使用PostgreSQL 資料庫的架構師。這本最實際的指南將幫助開發人員操作線上資料庫，以及通過任何開發框架使用Java、Python、Ruby 以及.Net 支撐Web 或者企業軟體套用。《PostgreSQL 9 Administration Cookbook（第2 版）》在手，資料庫管理工作萬事不愁。
本實用指南為你的常見問題和故障提供快速解答，這些都基於作者作為PostgreSQL 資料庫培訓師、用戶和核心開發人員的經驗積累。
每個技術方面都被拆分為小的方法或建議，先給出包含可工作代碼的演示解決方案，然後解釋這樣做的原因及工作原理。本書意在成為新用戶和技術專家的桌面參考手冊。
本書涵蓋了PostgreSQL 9 所有的新功能。很快，你將能輕鬆自如地運行資料庫了。
本書包含哪些內容
第1 章，邁出第一步。包含的主題有介紹PostgreSQL 9，下載及安裝，連線到PostgreSQL服務，為網路/ 遠程用戶啟用服務，使用圖形化管理工具，使用psql 這個查詢及腳本工具，安全地修改你的密碼，避免硬編碼你的密碼，使用連線服務檔案，以及在連線失敗時進行排錯。
第2 章，瀏覽資料庫。幫助你獲得你正在使用的資料庫服務版本以及服務時長。本章幫助你定位資料庫服務的檔案，資料庫服務的訊息日誌，以及資料庫的系統標識符。這裡還為你展示如何列出資料庫服務中的資料庫，並提供一些方法，讓你知道資料庫中表的數量，資料庫和表占用的磁碟空間，以及誰是最大的表，表中有多少行，如何估計表中的行數，以及如何理解對象的依賴關係。
第3 章，配置。包含的主題包括閱讀手冊（RTFM，Read The Fine Manual），規劃一個新資料庫，在你的程式中更改參數，查看當前的配置參數，非默認的參數，更新參數檔案，為特定用戶組設定參數，基本服務配置清單，以及添加一個外部模組到PostgreSQL 服務中。
第4 章，服務控制。主要內容包括手動啟動資料庫服務，快速安全地停止服務，緊急情況下停止服務，重新載入服務配置檔案，快速重啟服務，拒絕新連線，限制用戶只用一個會話，以及強制用戶從系統中退出。這裡包含一些方法，用於幫助你確定你的多租戶系統設計。你可以學習如何使用多模式（schema），給用戶他們自己的私有資料庫，在一個系統中運行多個資料庫服務，以及建立連線池。
第5 章，表和數據。引導你貫穿資料庫設計的整個過程，包括選擇恰當的資料庫對象名，處理使用引號引用的名稱的對象，確保列（指在不同表中相同意義的列）的名稱和定義相同，標記和刪除重複數據，避免重複的行，為一組數據找到唯一鍵，生成測試數據，隨機採樣數據，從電子表格中導入數據，以及從扁平檔案（txt、csv 一類的檔案）中導入數據。
第6 章，安全。提供的方法包括撤銷用戶對表的訪問許可權，賦予用戶對表的訪問，建立一個新用戶，臨時避免用戶連線，刪除用戶而不刪除其數據，檢查是否所有的用戶都使用安全的密碼，給指定用戶受限的超級用戶許可權，審計DDL 變化，審計數據變化，與LDAP 集成，使用SSL 進行連線，以及加密敏感數據。
第7 章，資料庫管理。涵蓋很多有用的方法，包括寫一個要么全部執行成功要么全部失敗的腳本，編寫一個psql 腳本，一遇到錯誤就退出，在多張表上執行操作，添加/ 刪除表上的列，更改列的數據類型，添加/ 刪除模式，在不同模式之間移動對象，添加/ 刪除表空間，在不同表空間之間移動對象，訪問其他 PostgreSQL 資料庫中的對象，並使視圖可更新。
第8 章，監控和診斷。提供用於回答一些問題的方法，例如：“某個用戶是否已經連線上來？他們在運行什麼？他們正在運行還是被阻塞了？是誰阻塞了他們？有人在使用某張表嗎？最後一次使用它是什麼時候？臨時數據使用了多少磁碟空間？為什麼我的查詢變慢了？”。它還幫助你調查和報告bug，生成每日的日誌檔案錯誤信息摘要，殺掉一個指定的會話，以及處理未決的準備事務。
第9 章，日常維護。包含一些有用的方法，包括控制自動資料庫維護，避免自動凍結和頁損壞，避免事務重疊，移除過期的準備事務（prepared transactions），對於大量使用臨時表時需要的操作，識別和修復膨脹的表和索引，維護索引，尋找未使用的索引，謹慎刪除不需要的索引以及規劃維護。
第10 章，性能和並發。包含的主題包括發現執行緩慢的SQL 語句，從pg_stat* 視圖收集常規統計信息，研究是什麼讓SQL 變得緩慢，減少返回的行數，簡化複雜的 SQL，提高查詢速度而無須重寫它，找出為什麼某個查詢不使用索引，強制讓一個查詢使用一個索引，使用樂觀鎖並報告性能問題。
第11 章，備份及恢復。提供非常有用的用於備份和恢復你的PostgreSQL 資料庫的信息，包括理解和控制崩潰恢復，計畫備份，對一個資料庫進行熱邏輯備份，對所有資料庫進行熱邏輯備份，對一個表空間中的所有表進行熱邏輯備份，備份資料庫對象的定義，獨立的資料庫熱物理備份，熱物理備份和持續歸檔。它還包括以下主題：恢復所有的資料庫，恢復到一個時間點，恢復一個被刪除/ 損壞的表、資料庫或表空間，為備份/ 恢復提速，增量/ 差異備份和恢復。
第12 章，複製和升級。涵蓋複製最佳實踐，建立基於檔案的複製和流複製，流複製安全設定，熱備機及讀可擴展性，管理流複製，使用repmgr，使用複製槽位，監控複製，性能和同步複製，延遲、暫停和同步複製，邏輯複製，雙向複製（Bi-Directional Replication），歸檔事務日誌數據，升級到一個新的小版本，主版本原地升級和線上升級。
你需要為本書準備什麼
你需要為本書準備以下軟體：
PostgreSQL 9.4 服務端軟體
psql客戶端工具（9.4 自帶）
pgAdmin3 1.20
這本書的讀者對象是誰
本書為系統管理員、資料庫管理員、架構師、開發人員以及任何對其開始感興趣並計畫使用、或已經在運行，甚至已經用於線上生產的人員而準備。本書特別適用於有一些技術經驗的人。
小節
在本書中，你會發現有幾個標題會經常出現（準備，該如何做，如何實現的，更多內容以及參考）。
為明確給出某個技術建議該怎么做，我們使用了以下小節。
準備
這部分將告訴你你期待的技術建議會是什麼樣的，以及描述如何設定軟體或任何其他本建議需要的初步設定。
該如何做……
這部分包含本技術建議需要進行的步驟。
如何實現的……
本節通常包含前一節發生的內容的詳細解釋。
更多內容……
本節包含一些關於本技術建議的附加信息，讓你能深入理解本建議。
參考
本節提供一些有用的連結或者其他有用的信息。
約定
在本書中，你會發現很多文本樣式，用以區分不同類型的信息。以下是一些樣式的示例以及對它們的解釋。
文本的代碼單詞、資料庫表名、目錄名、檔案名稱、檔案擴展名、路徑名、假設的URL、用戶輸入以及Twitter 的句柄通常以以下方式顯示：“The service can also be set using an environment variable named PGSERVICE.”
代碼段的格式為：
[dbservice1]
host=postgres1
port=5432
dbname=postgres
當我們想提醒你注意一個代碼段的特定部分時，相關的行或項目會被設定為粗體：
Database system identifier: 5805760367713220187
Database cluster state: in production
命令行的輸入和輸出通常是這樣的：
$ psql -c “SELECT current_time”
新的術語和重要的詞語會以粗體顯示。例如，你在螢幕上的選單或對話框中看到的某些文字，會以以下方式出現：“確保Guru Hints 選項已開啟。”
警告或重要注釋會出現在這樣一個框中。
提示或技巧會是這樣。
