快速解決PostgreSQL中的Permissiondenied問題

1、快速解決PostgreSQ中的Permissiondeni問題想開始學(xué)習(xí)sql和另e本書，覺得自己親手去輸入才是正道。發(fā)現(xiàn)程序后續(xù)會用到窗口函數(shù)，可是我的ms沒有窗口函數(shù)，這本書所提供的數(shù)據(jù)腳本分別是SSQLseVPostreSQL?LoadPcstgres2015/11/1014:07SQLTextFile22KB?LaadSQLJSeRrer2015/11/1115:24SQLTelFile25KB上午我先安裝的ss,可是由于比較大且在安裝時出現(xiàn)了一些小的問題（安裝緩慢，服務(wù)啟動不了）。無奈選擇TPostreSQL體積小，安裝順利。丄Jsr41COPYCalsndarFF.CM5LCADD

2、IR?/Calendar.txt1WITHHEZjERNULLNULLrDELIMITED11匚SV；44iElCREATETAELECampaigns（CampainldintMOWNULLKEY,導(dǎo)入數(shù)據(jù)比較特別，先建一個表，然后把同名t-導(dǎo)入進去。一定要用ni方式的路徑。o這個語句先前在ms上沒有遇到過。學(xué)習(xí)下。少結(jié)：ostgreSQL表名，字段名都是驗劉謁的鋰形化異面可罰瑞新建.用5QL語句誼抿需要扣雙引邑如剁恥潼詢等此砸轉(zhuǎn)殳符號.呸或gr亦QL在SQL語句中對大U謁是不曲蝴selectIDfromtuser和selectidfromtuser根據(jù)我的錯誤代碼發(fā)現(xiàn)是權(quán)限不夠謾前用鼬$的

3、時候并沒有注意到權(quán)限這個問題。羊要力吐陰號selectIDfromtuser哇，被權(quán)限搞的真的頭皮發(fā)麻。中間的曲折過程就不表了，第一次了解到數(shù)據(jù)庫超級用戶這個概念，還有就是ostres的結(jié)構(gòu)和ms也很不同。數(shù)據(jù)庫模式表。最后是用ostre創(chuàng)建了一個超級用戶。但是發(fā)現(xiàn)還是報錯，拒絕訪問。我就想到可能是t放在了盤不能隨便訪問，所以我將dt文件放到了盤，導(dǎo)入成功。已localhost_33067辺localhost_W-32會postgres7MSQLBook中黑ITVt；昌表g觀園,/rj函教孑旬創(chuàng)建丄貝背誼軸補弦OPYCalendarFRGM1D:/PosterSQL/WileyCompaniQ

4、rL/dat/Calendar,txt12WITHHEADERNULENULLDELIMITER11：SV；|SQLJCOPYCaFendarFROfvlDVosterSQL/WileyCompanioiVdat/Caleiidar.txtrWITHHEADERNULLNULL1DELIMITER1CSV;時Pl:0.275s受影洞的行：36890成功了！今天一個下午算是折在這上面了，不過總算有收獲。了解了用戶，中的。明天開始照著書做吧。:(雖然這回的數(shù)據(jù)沒有中文)在使Spostgrescl逬行數(shù)據(jù)插入時岀現(xiàn)錯渓INSERTINTOtalel(namepunter)VALUES(t,123)據(jù)

5、示T1這個列錯謀數(shù)堀庫將”王數(shù)據(jù)識另喊了列數(shù)據(jù)烏査看官方文檔得琨postgresqlT字符串需要用單引號JE左標識.補充：的幾種常見問題和解決方法1.前言1.1概述本文介紹了的幾種常見問題，并從現(xiàn)象出發(fā)，逐步排查問題，分析導(dǎo)致問題的原因并給出解決方案。本文介紹的問題分為兩大類：一類是關(guān)于無法啟動的問題，另一類是啟動后，部分數(shù)據(jù)庫對象無法訪問的問題。1.2軟件環(huán)境本文使用的版本是.6些約定術(shù)語安裝路徑：默認是文件夾：安裝路徑下的文件夾。文件夾：安裝路徑下的文件夾。2問題和解決方法2.1無法啟動沒有正常啟動時，在服務(wù)中再次啟動失敗。2.1.1端口占用我們首先需要判斷是不是該服務(wù)的端口被占用。Pos

6、tgreSQL服務(wù)的默認端口是5432，那么我們在命令行中執(zhí)行如下命令netstat-ano|find/i5432如果發(fā)現(xiàn)了某個進程使用了5432這個端口，這說明是端口占用導(dǎo)致服務(wù)無法啟動：MJscr-sTCPnctstentffiq1find/i討醜2364k*上占洛磁1LiJSTENIKrepie.e7.i83.2ar?：54S2EEIABIISHE3475&11：LlSnHlHC這個進程的pid是2364,你想查看它是什么進程，可以執(zhí)行:tasklist|findstr2364執(zhí)行結(jié)果如下：你可以在任務(wù)管理器-進程頁面中，或者通過下面的命令結(jié)束這個進程：taskkill/f/pid543

7、2小知識：PostgreSQL是多進程模型的數(shù)據(jù)庫。它在運行時，會啟動一個名為pg_ctl”進程和若干個名為“postgres”的進程。其中，進程pg_ctI是“祖先”進程，它表示數(shù)據(jù)庫處于運行狀態(tài)，占用的內(nèi)存很少；其他所有工作進程的名稱都是Dostgres。在Windows操作系統(tǒng)上，如果pg_ctl.exe被異常關(guān)閉了，進程postgres.exe還會存在。數(shù)據(jù)庫運行端口仍然被占用。會導(dǎo)致數(shù)據(jù)庫無法啟動。文件postmaster.pid殘留進入PostgreSQL的data文件夾，查看是否有殘留的文件postmaster.pid。正常情況下，PostgreSQL在啟動時會創(chuàng)建這個文件，其內(nèi)

8、容是PostgreSQL的主進程的pid。如果它存在，則數(shù)據(jù)庫會認為自己已經(jīng)啟動了，所以啟動失敗。因此需要刪除這個文件，再嘗試啟動數(shù)據(jù)庫。-pg_hba.cont20；0；5/li17:OBCONFOT5KB二pgjdent.corf2017/V1015:43CDNF丈件2KB_PGJ/E那ION2017/V1OI5：43文樣1KBi二postgresql.ccnf2019/5/1615:30CONF文件20KBpostmastEr.oEfe血斬72i942OP巧沸1KBpostnaster.pd200,7/21942PJDxn1KBcouldnotopencontrolfile“global

9、/pg_control”：Permissiondenied如果端口沒有被占用，那么你可以用PostgreSQL原生的命令啟動它。進入postgresql安裝路徑下的bin文件夾，在這里打開命令行，執(zhí)行下面的命令:.pg_ctlstart-D.data如果程序報出如下錯誤：ERROR:couldnotopencontrolfile“global/pg_control”:PermissiondeniedC:screiscibator2019-81-215:3144HETPftMC：couldrotfileyl口lb己ml,i!?Petnissicndenied則說明當前操作系統(tǒng)用戶丟失了data文

10、件夾及其內(nèi)容的權(quán)限。下面是解決方法：1.首先，進入postgresql的安裝路徑，右鍵data文件夾，依次點擊屬性安全編輯，你能看到所有用戶或用戶組的權(quán)限。矮垃E期Ibin：dcta2019/3Gdata屆性篇抗共專EI扶胡酊扳*自是丈腐無.皂稱.D:Proa：-aTiFiles(xJE6)5urveillulceSysteI14:26文呻夾152丈硼data牝較反対象名稱：E:Ik:grajtiFlL&s(jcB6JKSoriLLoTiCoSyEtc務(wù)或用戶堆G):：).7-pAuiheiLinco.tcdITstrssJSISTEftAdnansstrLtdteAdrninistrator

11、c)iiJVsers血昭)更更改孜喂清甲擊“狷輯”*Vsers的跟眼旳讀取和執(zhí)行列岀文件夾內(nèi)咨AutheiitcitedIfsersXdnlni5trilors)JKTENdriiriiztr-itars允洋拒絕ZIEHEN的校隕眄-ttoffi).|鳩田】gL回T上蹄說帝二嚴匪i唸取消Li冃A)2確保System和Administrator擁有“完全控制”權(quán)限。Users用戶組默認只擁有“讀取和執(zhí)行”，咧出文件夾內(nèi)容”和“讀取”3種權(quán)限。當啟動數(shù)據(jù)庫提示“權(quán)限不足”時，應(yīng)再添加“修改”和“寫入”。cats圧赦限安全至稱：I1VFrogTdiiFlLseG：33)ySun-Eill:=r.ce

12、Syst昨脛吵:一Autbier.titwlEd.Users汎SYSIH加AiJnir.iEtrttorsi.Xluiriisti-ytors)UserE|皿中)安全對象名稲：I：JtcgxanFies(;86JxSarzeiLLanceSyt組或羽口窯Y.VitherLticatmdUscrESfSrEil龜AinirdEtratarEAilrimistratDrs)diiinistfE.tcrE的板眼0!魁鵬廻允詐拒絕劭上反件夾內(nèi)吝4-E_T00HDJ叵匡匡zI:第護iEISBns!甘保存并嘗試再次在bin_)gctlstart-D-.data!文件夾下執(zhí)行：圧冃也以瀆09列岀文件夾內(nèi)罟、

13、丨見1卜M汀嚀:的酬的L璧魅L.5?.J觀察PostgreSQL數(shù)據(jù)庫能否啟動。couldnotlocateavalidcheckpointrecord如果啟動數(shù)據(jù)庫時，提示“正在啟動服務(wù)器進程”，且長時間無法啟動成功，如下圖所示，需要查看數(shù)據(jù)庫運行日志，它們位于data文件夾下的pgog中的。tlFniPiuPL1ICxl-adjiviillui：EyRejFillMat-hlua*!ii.n_:ll!aeartD.E丘啟凱矍膏國世粗b：-PpFHPLhlWiEM：ILlrrfrliHIk-ljiln打開問題發(fā)生時的數(shù)據(jù)庫運行日志，查看信息。如果日志中出現(xiàn)類似下面黑體字的信息，說明是Post

14、greSQL數(shù)據(jù)庫中的預(yù)寫式日志(writeaheadlog,簡稱WAL,又稱事務(wù)日志，簡稱xlog)損壞了：LOG:couldnotopenfilepg_xlog/0000000100000000000000E7(logfile0,segment231):NosuchfileordirectoryLOG:invalidprimarycheckpointrecordLOG:couldnotopenfilepg_xlog/0000000100000000000000E7(logfile0,segment231):NosuchfileordirectoryLOG:invalidsecondaryc

15、heckpointrecordPANIC:couldnotlocateavalidcheckpointrecord解決方法如下：進入bin文件夾，在這里打開命令行，執(zhí)行下面的命令：.pg_resetxlog.exe-f.data在日志重置后，再嘗試啟動數(shù)據(jù)庫。failedtore-findparentkeyinindex227236forsplitpages370/371有時，數(shù)據(jù)庫無法啟動時，我們查看位于data文件夾下的pg_log中的數(shù)據(jù)庫運行日志，會發(fā)現(xiàn)類似下面的信息:LOG:redostartsat270/55E04AE8LOG:couldnotopenfilepg_xlog/000

16、0000100000270000000CC(logfile624,segment204):NosuchfileordirectoryLOG:redodoneat270/CBFFE940LOG:lastcompletedtransactionwasatlogtime2018-11-2601:55:01.259996-02FATAL:failedtore-findparentkeyinindex227236forsplitpages370/371LOG:startupprocess(PID5011)exitedwithexitcode1LOG:abortingstartupduetostartup

17、processfailure上面黑體字的信息，同樣說明是PostgreSQL數(shù)據(jù)庫中的預(yù)寫式日志文件損壞了。該問題的解決方法和2.1.3節(jié)的問題的解決方法相同。2.1.6無法找到來自源PostgreSQL的事件ID0的描述。如果上面的方法沒有解決問題，那么我們需要進入事件管理器中查看是否有錯誤日志：在事件查看器-Windows日志-應(yīng)用程序中，查看是否有如下錯誤日志：無法找到來自源PostgreSQL的事件ID0的描述。本地計算機上未安裝引發(fā)此事件的組件,或者安裝已損壞?？梢园惭b或修復(fù)本地計算機上的組件。MUIUm出ifriQ魁沖I*4*!-Q-；亠難掰相坤日立12觀.亠1曲sikiimi必w

18、Fudu0元IjlLJ!sHNtlh*皿0壬HllnisuiLnIL:M0D起皿WUiUIItHWFhiprAB無|114niMin11加X衛(wèi)衆(zhòng)0忑IfIMSlILHii1J.12Fni-iiPL0無矮垃E期矮垃E期州H1*-4卅戶Mi0鞘咻=邂兇觀：帕如果出現(xiàn)了這據(jù)庫中產(chǎn)生過IIH火蹲此c科知削紺Ami樣的信息，則說明PostgreSQL軟件已經(jīng)損壞，需要重新安裝。不過，數(shù)據(jù)文件不一定損壞了，因此如果上次備份至今，數(shù)書常重要的數(shù)據(jù)（比如賬單信息）；你應(yīng)該將data文件夾復(fù)制到另一個目錄，然后重新安裝平臺，并恢復(fù)data文件夾。2.1.7Couldnotreadfromfilepg_clog/

19、000Eatoffset172032還有一種不常見的情況。如果日志中出現(xiàn)類似下面的信息:ERROR:couldnotaccessstatusoftransaction710708DETAIL:Couldnotreadfromfilepg_clog/000Eatoffset172032:Noerror.則表示位于data文件夾下pg_clog中的名為000E的提交日志文件丟失了。解決方法如下：在linux操作系統(tǒng)中，執(zhí)行下列命令：ddif=/dev/zeroof=/root/000Ebs=256kcount=1或者在windows中安裝dd,隨后執(zhí)行:ddif=/dev/zeroof=D:000

20、Ebs=256kcount=1矮垃E期然后將創(chuàng)建好的000E文件拷貝至data文件夾下的pg_clog中。2.2數(shù)據(jù)庫啟動后，部分數(shù)據(jù)庫或表無法訪問這種情況下，你需要進入data文件夾下的pg_log文件夾，查看問題發(fā)生時刻產(chǎn)生的運行日志。permissiondeniedforrelationtb_door如果運行日志出現(xiàn)類似下面的信息，這說明是當前訪問用戶沒有表b_door的某些權(quán)限：ERROR:permissiondeniedforrelationtb_door如果你希望當前用戶(以myuser為例)擁有特定訪問權(quán)限(以SELECT,INSERT,UPDATE,DELETE為例)，可以這樣

21、解決：首先，通過postgres用戶或擁有tb_door相應(yīng)訪問權(quán)限即授予權(quán)限的用戶登錄數(shù)據(jù)庫；執(zhí)行如下命令,為用戶授予權(quán)限：grantSELECT,INSERT,UPDATE,DELETEontb_doortomyusermustbeownerofrelationtb_door如果運行日志出現(xiàn)類似下面的信息，這說明是當前用戶沒有表ac_door的所有權(quán)：ERROR:mustbeownerofrelationtb_door你可以使用管理員postgres登錄相應(yīng)數(shù)據(jù)庫，手動執(zhí)行下面命令將tb_door的屬主你希望的用戶，以myuser為例：Altertabletb_doorownertomyu

22、ser;invalidpageheaderinblock120ofrelationbase/272816/309624如果日志中出現(xiàn)類似下面的信息：ERROR:invalidpageheaderinblock120ofrelationbase/272816/309624則表示數(shù)據(jù)表文件損壞。這通常是由于異常斷電或誤操作導(dǎo)致的。這里272816”是發(fā)生問題的數(shù)據(jù)庫的對象id(oid),“309624”表示發(fā)生問題的表的文件結(jié)點(filenode)如果發(fā)生損壞的表以及損壞的頁面數(shù)量較少，我們可以以犧牲部分數(shù)據(jù)的代價恢復(fù)整體；如果損壞的表數(shù)量過多，或者損失的數(shù)據(jù)非常重要,就需要從備份中恢復(fù)數(shù)據(jù)了。當

23、發(fā)生損壞的表以及損壞的頁面數(shù)量較少時，解決方法如下：確定發(fā)生問題的數(shù)據(jù)庫。連接任意數(shù)據(jù)庫，執(zhí)行下面的feql語句：selectdatnamefrompg_databasewhereoid=272816;查詢結(jié)果如下：testdb這表示發(fā)生問題的數(shù)據(jù)庫名是testdb查找損壞的數(shù)據(jù)庫對象。連接發(fā)生問題的數(shù)據(jù)庫，執(zhí)行下面的sql語句：selectrelname,relkindfrompg_classwhererelfilenode=309624如果查詢結(jié)果中relkind=r,表示損壞的是表。例如：tb_door,rrelname=tb_door這表示損壞的表是tb_door。如果查詢結(jié)果中rel

24、kind=i,表示損壞的是一個索引。例如：dept_number_index,i或者：tb_dept_pkey,i矮垃E期需要注意，損壞的可能是普通索引，也可能是主鍵或唯一鍵。如果索引的名稱中有_pkey”等很可能屬于主鍵，而名稱中含有“_key”則很可能屬于唯一鍵。還需要格外注意一點，表/索引可修復(fù)的前提條件是損壞的表是應(yīng)用程序創(chuàng)建的表/索引，而不是PostgreSQL的系統(tǒng)表和建立在其上的索引。如果系統(tǒng)表/建立在其上的索引發(fā)生損壞，則需要從備份中恢復(fù)數(shù)據(jù)庫。判斷一個表是否是系統(tǒng)表，最簡單的方法是：如果表名是“pg_開頭的，則說明它是系統(tǒng)表。小知識pgclass.relkind的值有下面幾種

25、：r:表示ordinarytable（普通表）；i:表示index（索I）;S:表示sequence（序列）；V:表示view（視圖）；m:表示materializedview（物化視圖）；c:表示compositetype（復(fù)合類型）；t:表示TOASTtable（TOAST表）；f:表示foreigntable（外部表）3修復(fù)損壞的數(shù)據(jù)庫對象。連接發(fā)生損壞的數(shù)據(jù)庫，執(zhí)行修復(fù)命令。如果損壞的是表，以tb_door為例，則依次執(zhí)行下列命令即可完成修復(fù)：setzero_damaged_pages=on；vacuumfulltb_door；reindextabletb_door；如果損壞的是普通索

26、引，以dept_number_index為例，則依次執(zhí)行：setzero_damaged_pages=on；reindexindexdept_number_index；如果損壞的是主鍵或唯一鍵，則首先需要找到它所在的表，以tb_dept_pkey為例：Selecttablename,indexnamefrompg_indexeswhereindexname=tb_dept_pkey；查詢結(jié)果:tb_dept,tb_dept_pkey然后獲取索引的定義:selectpg_get_constraintdef（selectoidfrompg_constraintwhereconname=tb_dep

27、t_pkey）；查詢結(jié)果:PRIMARYKEY（dept_id）然后重新創(chuàng)建這個約束:Altertabledropconstrianttb_dept_pkey；Altertableaddconstrainttb_dept_pkeyPRIMARYKEY（dept_id）；couldnotreadblock190infilebase/272816/309624該問題的解決方法與2.2.2節(jié)的問題完全相同。couldnotopenfilebase/272816/379923:Nosuchfileordirectory如果日志中出現(xiàn)類似下面的信息：2019-01-2114:28:03HKTERROR:

28、couldnotopenfilebase/272816/379923:Nosuchfileordirectory則說明，oid為272816的數(shù)據(jù)庫中，oid為379923的表對應(yīng)的文件被刪除了。解決方法如下:首先判斷是哪一個數(shù)據(jù)庫中發(fā)生了此問題。連接任意數(shù)據(jù)庫，執(zhí)行如下sql:selectdatnamefrompg_databasewhereoid=272816查詢結(jié)果如下：testdb從備份中恢復(fù)該數(shù)據(jù)庫。2.3數(shù)據(jù)庫啟動后，部分數(shù)據(jù)庫或表無法訪問Nobufferspaceavailable有時，在服務(wù)管理器中，PostgreSQL顯示為正在運行狀態(tài)，但是使用客戶端連接使，提示couldn

29、otconnecttoserver:Nobufferspaceavailable，在postgresql運行日志中，也能看到類似日志。解決方法如下：首先，在服務(wù)管理器中關(guān)閉PostgreSQL服務(wù)。嘗試用PostgreSQL自身的命令啟動它。進入postgresql安裝路徑下的bin文件夾，在這里打開命令行，執(zhí)行下面的命令：.pg_ctlstart-D.data觀察提示信息。如果提示PostgreSQL啟動成功，則用客戶端連接數(shù)據(jù)庫；如果啟動不成功，則參考2.1章的內(nèi)容。如果仍然有這樣的錯誤提示，那說明很可能是內(nèi)存不足。你需要在操作系統(tǒng)中查看內(nèi)存，如果發(fā)現(xiàn)可用內(nèi)存較少，那你需要觀察是否有服務(wù)內(nèi)

30、存異常過高，并處理它。在Windows上有一種特殊的情況，各種服務(wù)起來正常，但內(nèi)存很少，這是因為windowsSocket連接關(guān)閉后，內(nèi)存不釋放。對于windows2008，解決方法是打windows補丁KB2577795。nopg_hba.confentryforhost如果日志中出現(xiàn)類似下面的信息:FATAL:nopg_hba.confentryforhost192.168.0.123,usertestuser,databasetestdb則表示數(shù)據(jù)庫服務(wù)器沒有允許來自地址192.168.0.123的testuser用戶訪問數(shù)據(jù)庫testdb。解決的方法如下：檢查data目錄中的配置文件postgresql.conf中的參數(shù)listen_addresses,把它的值改為*,或者包含客戶端的IP。修改data目錄中的訪問權(quán)限配置文件pg_hba.conf。如果你希望所有地址的所有用戶可以訪問此服務(wù)器中的全部數(shù)據(jù)庫，可以添加下面這一行：hostallall0.0.0.0/0md5如果你只希望192.168.0.123上的用戶可以訪問此數(shù)據(jù)庫，則添加：hostallall192.168.0.123/32md5或者你僅僅希望192.168.0.123上的testuser用戶可以訪問此數(shù)據(jù)庫