PostgreSQL數據庫的日常維護工作

PostgreSQL數據庫的日常維護工作日常數據庫維護工作作者：小p來自：LinuxSir.Org摘要：為了保持所安裝的PostgreSQL服務器平穩(wěn)運行,我們必須做一些日常性的維護工作。我們在這里討論的這些工作都是經常重復的事情，可以很容易地使用標準的Unix工具，比如cron腳本來實現；目錄綜述；日常清理；2.1VACUUM;2.1.1語法結構；2.1.2描述；2.1.3參數；2.1.4為什么要用VACUUM；2.2恢復磁盤空間；2.2.1概述；2.2.2VACUUMFULL;2.3更新規(guī)劃器統(tǒng)計；2.4避免事務ID重疊造成的問題；2.5auto-vacuum守護進程；經常重建索引；日志文件維護；關于本文；更新日志；參考文檔；&相關文檔；+++++++++++++++++++++++++++++++++++++++++++正文+++++++++++++++++++++++++++++++++++++++++++1.綜述；為了保持所安裝的PostgreSQL服務器平穩(wěn)運行,我們必須做一些日常性的維護工作。我們在這里討論的這些工作都是經常重復的事情，可以很容易地使用標準的Unix工具，比如cron腳本來實現。不過，設置合適的腳本以及檢查它們是否成功執(zhí)行則是數據庫管理員的責任，一件很明顯的維護工作就是經常性地創(chuàng)建數據的備份拷貝。如果沒有最近的備份，那么您就沒有從災難中恢復的機會（比如磁盤壞了，失火，誤刪了表等等）。其它主要的維護范疇的工作包括周期性的"vacuuming"（清理）數據庫。其它需要周期性注意的東西是日志文件的管理。PostgreSQL和其它數據庫產品比較起來是低維護量的。但是，適當在這些任務上放一些注意將更加能夠確保我們的愉快工作和獲取對這個系統(tǒng)富有成效的經驗。操作環(huán)境：PostgreSQL8.2+Ubuntu7.042.日常清理；VACUUM;語法結構；VACUUM[FULL|FREEZE][VERBOSE][table]VACUUM[FULL|FREEZE][VERBOSE]ANALYZE[table[（column[,...]）]]2.1.2描述；VACUUM回收已刪除元組占據的存儲空間。在一般的PostgreSQL操作里，那些已經DELETE的元組或者被UPDATE過后過時的元組是沒有從它們所屬的表中物理刪除的；在完成VACUUM之前它們仍然存在。因此我們有必須周期地運行VACUUM,特別是在常更新的表上，如果沒有參數，VACUUM處理當前數據庫里每個表，如果有參數，VACUUM只處理那個表，簡單的VACUUM（沒有FULL）只是簡單地回收空間并且令其可以再次使用；參數；FULL 選擇"完全"清理，這樣可以恢復更多的空間，但是花的時間更多并且在表上施加了排它鎖。FREEZE 選擇激進的元組"凍結"。VERBOSE 為每個表打印一份詳細的清理工作報告。ANALYZE 更新用于優(yōu)化器的統(tǒng)計信息，以決定執(zhí)行查詢的最有效方法。table 要清理的表的名稱（可以有模式修飾）。缺省時是當前數據庫中的所有表。column 要分析的具體的列/字段名稱。缺省是所有列/字段。為什么要用VACUUM；VACUUM命令的含義為：垃圾收集以及可選地分析一個數據庫。VACUUM回收已刪除元組占據的存儲空間。在一般的PostgreSQL操作里，那些已經DELETE的元組或者被UPDATE過后過時的元組是沒有從它們所屬的表中物理刪除的；在完成VACUUM之前它們仍然存在。由于以下幾個原因，我們必須周期性運行PostgreSQL的VACUUM命令：1?恢復那些由已更新的或已刪除的行占據的磁盤空間。更新PostgreSQL查詢規(guī)劃器使用的數據統(tǒng)計信息。避免因為事務ID重疊造成的老舊數據的丟失。對上面每個條件進行VACUUM操作的頻率和范圍因不同的節(jié)點而不同。因此，數據庫管理員必須理解這些問題并且開發(fā)出合適的維護策略。建議在經常VACUUM（清理）（至少每晚一次）生產數據庫，以保證不斷地刪除失效的行。尤其是在增刪了大量記錄之后，對受影響的表執(zhí)行VACUUMANALYZE命令是一個很好的習慣。例如：sir=#VACUUMVERBOSEANALYZEaccess;信息：正在清理(vacuum)""public.access""信息：index""access_pkey"nowcontains0rowversionsin1pagesDETAIL:0indexrowversionswereremoved?0indexpageshavebeendeleted,0arecurrentlyreusable?CPU0?00s/0?00usecelapsed0?00sec.信息：""access":found0removable,0nonremovablerowversionsin0pagesDETAIL:0deadrowversionscannotberemovedyet?Therewere0unuseditempointers?0pagescontainusefulfreespace?0pagesareentirelyempty.CPU0?00s/0?00usecelapsed0?00sec.信息：正在清理(vacuum)""pg_toast?pg_toast_16464""信息：index""pg_toast_16464_index"nowcontains0rowversionsin1pagesDETAIL:0indexrowversionswereremoved?0indexpageshavebeendeleted,0arecurrentlyreusable?CPU0?00s/0?00usecelapsed0?00sec.信息： ""pg_toast_16464"":found0removable,0nonremovablerowversionsin0pagesDETAIL:0deadrowversionscannotberemovedyet?Therewere0unuseditempointers?0pagescontainusefulfreespace?0pagesareentirelyempty.CPU0?00s/0?00usecelapsed0?00sec.信息：正在分析""public.access""信息：""access":scanned0of0pages,containing0liverowsand0deadrows;0rowsinsample,0estimatedtotalrowsVACUUM這樣做將更新系統(tǒng)目錄為最近的更改，并且允許PostgreSQL查詢優(yōu)化器在規(guī)劃用戶查詢時有更好的選擇。不建議日常使用FULL選項，但是可以在特殊情況下使用。一個例子就是在您刪除了一個表的大部分行之后，希望從物理上縮小該表以減少磁盤空間占用。VACUUMFULL通常要比單純的VACUUM收縮更多表的尺寸;2.2恢復磁盤空間;概述;在正常的PostgreSQL操作里，對一行的UPDATE或DELETE并未立即刪除舊版本的數據行。這個方法對于獲取多版本并行控制的好處是必要的：如果一個行的版本仍有可能被其它事務看到，那么您就不能刪除它。但到了最后，不會有任何事務對過期的或者已經刪除的元組感興趣。而它占據的空間必須為那些新的元組使用而回收，以避免對磁盤空間增長的無休止的需求。這件事是通過運行VACUUM實現的。很明顯，那些經常更新或者刪除元組的表需要比那些較少更新的表清理的更頻繁一些。所以，設置一個周期性的cron任務VACUUM那些選定的表，而忽略那些已經知道變化比較少的表．這個方法只是在您擁有大量更新頻繁的表和大量很少更新的表的時候有意義—清理一個小表的額外開銷根本不值得擔心；VACUUM命令有兩個變種:第一種形式，叫做"懶漢vacuum"或者只是VACUUM,在表和索引中標記過期的數據為將來使用；它并不試圖立即恢復這些過期數據使用的空間。因此，表文件不會縮小，并且任何文件中沒有使用的空間都不會返回給操作系統(tǒng)。這個變種的VACUUM可以和通常的數據庫操作并發(fā)執(zhí)行;第二種形式是VACUUMFULL命令。這個形式使用一種更加激進的算法來恢復過期的的行版本占據的空間。任何VACUUMFULL釋放的空間都立即返回給操作系統(tǒng)。但是，這個形式的VACUUM命令在進行VACUUMFULL;VACUUMFULL一個表的時候在其上要求一個排他鎖。因此，經常使用VACUUMFULL會對并發(fā)數據庫查詢有著非常糟糕的影響;標準形式的VACUUM最適合用于維護相當程度的磁盤用量的穩(wěn)定狀態(tài)。如果您需要把磁盤空間歸還給操作系統(tǒng)，那么您可以使用VACUUMFULL—不過如果釋放的磁盤空間又會很快再次被分配又怎樣？如果維護更新頻繁的表，那么中等頻率的多次標準VACUUM運行方法比很低頻率的VACUUMFULL更好;對于大多數節(jié)點而言，我們推薦的習慣是在一天中的低使用的時段安排一次整個數據庫的VACUUM，必要時外加對更新頻繁的表的更經常的清理。（有些環(huán)境下，對那些更新非常頻繁的表可能會每幾分鐘就VACUUM一次。）如果您的集群中有多個數據庫，別忘記對每個庫進行清理；vacuumdb腳本可能會幫您的忙;如果您知道自己剛刪除掉一個表中大部分的行，那么我們建議使用VACUUMFULL，這樣該表的穩(wěn)定態(tài)尺寸可以因為VACUUMFULL更富侵略性的方法而顯著減小。日常的磁盤空間清理，請使用VACUUM，而不是VACUUMFULL;如果您有一個表，它的內容經常被完全刪除，那么可以考慮用TRUNCATE，而不是后面跟著VACUUM的DELETE。TRUNCATE立即冊U除整個表的內容，而不要求隨后的VACUUM或者VACUUMFULL來恢復現在沒有用的磁盤空間;更新規(guī)劃器統(tǒng)計;PostgreSQL的查詢規(guī)劃器依賴一些有關表內容的統(tǒng)計信息用以為查詢生成好的規(guī)劃。這些統(tǒng)計是通過ANALYZE命令獲得的，您可以直接調用這條命令，也可以把它當做VACUUM里的一個可選步驟來調用。擁有合理準確的統(tǒng)計是非常重要的，否則，選擇了惡劣的規(guī)劃很可能會降低數據庫的性能；和為了回收空間做清理一樣，經常更新統(tǒng)計信息也是對更新頻繁的表更有用。不過，即使是更新非常頻繁的表，如果它的數據的統(tǒng)計分布并不經常改變，那么也不需要更新統(tǒng)計信息。一條簡單的拇指定律就是想想表中字段的最大很最小值改變的幅度。比如，一個包含行更新時間的timestamp字段將是隨著行的追加和更新穩(wěn)定增長最大值的；這樣的字段可能需要比那些包含訪問網站的URL的字段更頻繁一些更新統(tǒng)計信息。那些URL字段可能改變得一樣頻繁，但是其數值的統(tǒng)計分布的改變相對要緩慢得多；我們可以在特定的表，甚至是表中特定的字段上運行ANALYZE，所以如果您的應用有需求的話，我們是可以對某些信息更新得比其它信息更頻繁的。不過，在實際中，這種做法的實用性是值得懷疑的。ANALYZE是一項相當快的操作，即時在大表上也很快，因為它使用了統(tǒng)計學上的隨機采樣的方法進行行采樣，而不是把每一行都讀取進來。因此，每隔一段時間對整個數據庫運行一便這條命令可能更簡單；注：盡管用ANALYZE按字段進行挖掘的方式可能不是很實用，但您可能還是會發(fā)現值得按字段對ANALYZE收集的統(tǒng)計信息的詳細級別進行調整。那些經常在WHERE子句里使用的字段如果有非常不規(guī)則的數據分布，那么就可能需要比其它字段更細致的數據圖表．參閱ALTERTABLESETSTATISTICS．我們對大多數節(jié)點都建議在每天的低使用時段安排一次數據庫范圍的ANALYZE：這個任務可以有效地和每天的VACUUM組合在一起。不過，這對那些表統(tǒng)計信息改變相對緩慢的節(jié)點可能會過于夸張，而且少一些的ANALYZE也足夠了；避免事務ID重疊造成的問題;PostgreSQL的MVCC事務語意依賴于比較事務ID（XID）的數值：一條帶有大于當前事務的XID的插入XID的行版本是"屬于未來的"，并且不應為當前事務可見。但是因為事務ID的大小有限（在我們寫這些的時候是32位），如果一次集群如果運行的時間很長（大于4十億次事務），那么它就要受到事務ID重疊的折磨：XID計數器回到零位，然后突然間所有以前的事務就變成看上去是在將來的—這意味著它們的輸出將變得可見。簡而言之，可怕的數據丟失，（實際上數據仍然在那里，但是如果您無法獲取數據，這么說也只是幸災樂禍。）在PostgreSQL7.2之前，防御XID重疊的唯一辦法就是至少每40億事務就重新做一次initdb。這種做法對高流量的節(jié)點而言當然不是令人滿意的做法，所以我們設計了更好的方法。新的方法允許某個服務器仍然保持運行狀態(tài)，不需要initdb或者任何類型的重啟。代價就是下面這樣的維護要求：數據庫中的每個表都必須在每十億次事務中至少清理一次．從實際角度出發(fā)，這個要求不算一個很繁重的要求，但是因為如果我們沒能滿足這個要求的后果是全部數據的丟失（而不僅僅是磁盤空間的浪費或者性能的下降），我們制作了一些特殊的東西來幫助數據庫管理員避免災難的發(fā)生。對于集群中的每個數據庫，PostgreSQL都跟蹤自上次全數據庫范圍VACUUM以來的時間。如果任何數據庫接近了十億次事務的危險級別，系統(tǒng)就開始發(fā)出警告信息。如果什么都不干，那么系統(tǒng)最終會停止正常的操作，直到進行了合適的手工操作。本節(jié)剩下的部分給出這方面的細節(jié)。XID比較的新方法剝離出兩個特殊的XID，數字1和2（BootstrapXID和FrozenXID）。這兩個XID總是被認為表任何普通的XID舊。普通的XID（那些大于2的）使用模-231運算進行比較。這就意味著對于每個普通的XID，總是有二十億個XID是"更舊"以及二十億個XID"更新"；表達這個意思的另外一個方法是普通的XID空間是沒有終點的環(huán)。因此，一旦一條元組帶著特定的普通XID創(chuàng)建出來，那么該元組將在以后的二十億次事務中表現得是"在過去"，而不管我們說的是哪個普通XID。如果該元組在超過二十億次事務之后仍然存在，那么它就會突然變成在將來的元組。為了避免數據丟失，老的元組必須在到達二十億次事務的年齡之前的某個時候賦予XIDFrozenXID。一旦它被賦予了這個特殊的XID，那么它們在所有普通事務面前表現為"在過去"，而不管事務ID是否重疊，因此這樣的元組直到刪除之前都會完好，不管要保存多長時間?這個XID的重新賦值是VACUUM控制的.VACUUM的正常策略是給任何其普通XID有超過十億次已過去事務行版本重新賦值為FrozenXID。這個策略保留了原來的插入XID直到該數值不再令人感興趣為止。（實際上，大多數行版本將可能在還沒有"凍結"之前就完成生存和消亡了）。在這個策略下，任何表在兩次VACUUM運行之間的最大的安全間隔是十億次事務：如果您等的時間更長，那么最后就可能就會有一條不夠老的行版本在重新賦值時變成比二十億次事務更老，并因此重疊到了未來—也就是說，您失去它了。（當然，它在另外二十億次事務之后會重新出現，不過那樣也無濟于事。）因為上面的原因，我們需要周期性地運行VACUUM，所以很難有哪個表會到十億次事務還沒有清理過。但是，為了幫助管理員確保滿足了這個要求，VACUUM在系統(tǒng)表pg_database里存儲了事務ID統(tǒng)計。尤其是一個數據庫的pg_database行中的datfrozenxid字段在任何數據庫范圍的VACUUM操作（也就是沒有聲明任何表的VACUUM）之后將會被更新。這個字段里存儲的數值是該VACUUM命令使用的凍結終止的XID。系統(tǒng)保證在該數據庫中所有比這個終止XID老的普通XID都被FrozenXID代替。檢查這個信息的一個便利的方法是執(zhí)行下面的查詢SELECTdatname,age（datfrozenxid）FROMpg_database;age字段用于測量從中止XID到當前事務的XID的數目。使用了這種標準的凍結策略，對一個剛清理過的數據庫而言，age字段將從十億處開始。當age到達二十億次的時候，數據庫必須再次清理以避免事務標識重疊造成的問題。我們建議的策略是至少每半個十億次（5億次）事務VACUUM一次數據庫，這樣就可以保證足夠的安全邊界范圍.為了幫助滿足這條規(guī)則，如果有任何pg_database記錄顯示出超過15億次事務的age，那么每次數據庫范圍的VACUUM都會自動發(fā)出一條警告，比如：play=#VACUUM;WARNING:database"mydb"mustbevacuumedwithin177009986transactionsHINT:Toavoidadatabaseshutdown,executeafull-databaseVACUUMin"mydb".VACUUM如果忽略了上面這樣的VACUUM信息，如果距離事務ID重疊小于1千萬次，那么PostgreSQL就會在每次事務開始前發(fā)出類似上面的警告。如果這些警告還是被忽略了，那么系統(tǒng)將在距離重疊小于1百萬次的時候關閉，并且拒絕執(zhí)行任何新的事務：play=#select2+2;ERROR:databaseisshutdowntoavoidwraparounddatalossindatabase"mydb"HINT:StopthepostmasteranduseastandalonebackendtoVACUUMin"mydb".這個1百萬的事務安全邊界留下來用于讓管理員在不丟失數據的情況下進行恢復，方法是手工執(zhí)行所需要的VACUUM命令。不過，因為一旦進入了安全關閉模式，系統(tǒng)就不能再執(zhí)行命令，做這件事情的唯一的方法是停止postmaster，使用一個單獨運行的后端來執(zhí)行VACUUM。關閉模式不會強制于獨立運行的后端。帶著FREEZE選項的VACUUM使用了更大膽的凍結策略：如果行版本已經老得被所有打開的事務看做是良好的，那么就都凍結.特別是如果在一個空閑的數據庫上運行VACUUMFREEZE，那么就保證該數據庫中所有的行版本都被凍結。因此，只要該數據庫沒有其它的變化，那么它就不需要后續(xù)的清理以避免事務ID重疊問題。這個技巧被initdb用于準備template0數據庫。我們也應該用這個方法對所有在pg_database表里標記著datallowconn=false的數據庫進行初始化，因為我們還沒有任何便利的方法VACUUM一個您無法聯接的數據庫。2.5auto-vacuum守護進程;從PostgreSQL8.1開始，系統(tǒng)帶有一個額外的可選服務進程，叫做autovacuum守護進程，它的目的是自動執(zhí)行VACUUM和ANALYZE命令。在打開這個選項之后，autovacuum守護進程將周期性運行并且檢查那些有大量插入，更新或者刪除元組操作的表。這些檢查使用行級別的統(tǒng)計收集設施；因此，除非把statsrowlevel和statsrowlevel設置為true，否則無法使用autovacuum守護。還有，在為superuserreservedconnections選擇數值的時候，不要忘記給autovacuum進程保留一個槽位。如果打開了autovacuum守護，那么它會每隔autovacummnatime秒鐘運行一次，并且檢查應該處理哪個數據庫。任何臨近事務ID重疊的數據庫都會被立即處理。這個時候，autovacuum發(fā)出一個數據庫范圍的VACUUM調用，如果是模板數據庫，則發(fā)出VACUUMFREEZE，然后終止。如果沒有數據庫復合這個標準，則選擇被上次autovacuum處理時間最遠的那個數據庫。這種情況下，該數據庫里的表被檢查，然后根據需要發(fā)出獨立的VACUUM或者ANALYZE命令。對于每個表，用兩個條件來判斷應該使用哪個操作。如果上次VACUUM之后的過期元組的數量超過了"清理閾值(vacuumthreshold)"，那么就清理改表。清理閾值是定義為：清理閾值=清理基本閾值+清理縮放系數*元組數(vacuumthreshold=vacuumbasethreshold+vacuumscalefactor*numberoftuples)這里的清理基本閾值是autovacuum_vacuum_threshold，清理的縮放系數是autovacuum_vacuum_scale_factor，元組的數目是失效的元組數目是從統(tǒng)計收集器里面獲取的；這事一個半精確的計數，由每次UPDATE和DELETE操作更新。(它只是半精確的是因為在重載下，有些信息可能會丟失。)為了分析，使用了一個類似的條件：分析閾值，定義為分析閾值=分析基本閾值+分析縮放系數*元組數目(analyzethreshold=analyzebasethreshold+analyzescalefactor*numberoftuples)它會和上次ANALYZE插入，更新，或者刪除的元組總數進行比較。缺省的閾值和伸縮系數是從postgresql.conf里面取得的，不過，我們可以以每個表獨立設置的方式覆蓋它，方法就是在系統(tǒng)表pg_autovacuum里輸入記錄。如果pg_autovacuum里面存在對某個特定表的行，那么就使用它聲明的設置；否則使用全局設置。除了基本閾值和縮放系數之外，在pg_autovacuum里還有三個參數可以為每個表進行設置。首先，pg_autovacuum.enabled可以設置為false,讓autovacuum守護進程完全忽略某個表。這種情況下，autovacuum只有在為了避免事務ID重疊清理整個數據庫的時候才會動那個表。另外兩個參數，清理開銷延遲（pg_autovacuum.vac_cost_delay）和清理開銷限制（pg_autovacuum.vac_cost_limit），用于為基于開銷的清理延遲特性設置表相關的數值。如果在pg_autovacuum里任何數值設置為負數，或者在pg_autovacuum里就根本沒有出現特定表的數據行，那么使用postgresql.conf里面對應的數值。目前沒有任何制作pg_autovacuum記錄的支持，只能手工向該系統(tǒng)表中INSERT。這個特性將在以后的版本中改進，并且這個系統(tǒng)表的定義也很有可能會改變。3.經常重建索引;有時候我們值得用REINDEX命令周期的重建索引;在PostgreSQL版本7.4之前，我們經常有必要避免"索引膨脹"，因為缺乏在B-tree索引內部的空間恢復機制。一個情況是就是索引健字的范圍隨著時間而變化—比如，一個在某個表的時間戳上的索引，隨著時間的推移，舊的記錄會最終被刪除—就會導致膨脹，因為那些用于不再使用的鍵字范圍的索引頁面不回得到重復使用。隨著時間的推移，索引的尺寸可能會變得比里面的有用的數據大得多。從PostgreSQL7.4開始，那些已經完全清空的索引頁會得到重復使用。不過這樣仍然會有不充分使用空間的可能：如果一個頁面中大多數索引鍵字被刪除，只留下很少的部分呢，那么該頁仍然將被分配。所以，如果使用模式是這樣的：每個范圍里除了少數鍵字之外，其他大部分鍵字最終都被刪除；那么這樣也會導致空間的低效使用。膨脹的可能性不是無窮的—最差的情況是每個頁面至少還有一個鍵字—但是對這樣的使用模式，我們可能仍然值得安排周期性的重新索引;對于非B-tree索引的膨脹可能還沒有很好地定量分析。在使用非B-tree索引的時候保持對索引的物理尺寸的監(jiān)控是個很好的主意;還有，對于B-tree索引，一個新建立的索引從某種意義上比更新了多次的訪問起來要快，因為在新建立的索引上，邏輯上連接的頁面通常物理上也連接在一起。（這樣的考慮目前并不適用于非B-tree索引。）僅僅從提高訪問速度角度出發(fā)，可能我們也值得周期性的重建索引。4.日志文件維護;把數據庫服務器的日志輸出保存在一個地方是個好主意。在碰到危險的問題的時候，日志輸出是非常寶貴的。不過，日志輸出可能很龐大（特別是在比較高的調試級別上），而且您不會無休止地保存它們．您需要"旋轉"日志文件，這樣生成新的日志文件并且經常拋棄老的；如果您簡單地把postmaster的stderr定向到一個文件中，您會有日志輸出，但是截斷日志文件的唯一的方法是停止并重起postmaster。這樣做對于開發(fā)環(huán)境中使用PostgreSQL可能是可以的，但是您肯定不想在生產環(huán)境上這么干；一個更好的辦法是把postmaster的stderr（錯誤流■s