快速解決PostgreSQL中的Permissiondenied問題

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

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

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

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

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

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

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

8、容是PostgreSQL的主進(jìn)程的pid。如果它存在，則數(shù)據(jù)庫會(huì)認(rèn)為自己已經(jīng)啟動(dòng)了，所以啟動(dòng)失敗。因此需要?jiǎng)h除這個(gè)文件，再嘗試啟動(dò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原生的命令啟動(dòng)它。進(jìn)入postgresql安裝路徑下的bin文件夾，在這里打開命令行，執(zhí)行下面的命令:.pg_ctlstart-D.data如果程序報(bào)出如下錯(cuò)誤：ERROR:couldnotopencontrolfile“global/pg_control”:PermissiondeniedC:screiscibator2019-81-215:3144HETPftMC：couldrotfileyl口lb己ml,i!?Petnissicndenied則說明當(dāng)前操作系統(tǒng)用戶丟失了data文

10、件夾及其內(nèi)容的權(quán)限。下面是解決方法：1.首先，進(jìn)入postgresql的安裝路徑，右鍵data文件夾，依次點(diǎn)擊屬性安全編輯，你能看到所有用戶或用戶組的權(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上蹄說帝二嚴(yán)匪i唸取消Li冃A)2確保System和Administrator擁有“完全控制”權(quán)限。Users用戶組默認(rèn)只擁有“讀取和執(zhí)行”，咧出文件夾內(nèi)容”和“讀取”3種權(quán)限。當(dāng)啟動(dòng)數(shù)據(jù)庫提示“權(quán)限不足”時(shí)，應(yīng)再添加“修改”和“寫入”。cats圧赦限安全至稱：I1VFrogTdiiFlLseG：33)ySun-Eill:=r.ce

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

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

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

15、heckpointrecordPANIC:couldnotlocateavalidcheckpointrecord解決方法如下：進(jìn)入bin文件夾，在這里打開命令行，執(zhí)行下面的命令：.pg_resetxlog.exe-f.data在日志重置后，再嘗試啟動(dòng)數(shù)據(jù)庫。failedtore-findparentkeyinindex227236forsplitpages370/371有時(shí)，數(shù)據(jù)庫無法啟動(dòng)時(shí)，我們查看位于data文件夾下的pg_log中的數(shù)據(jù)庫運(yùn)行日志，會(huì)發(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的描述。如果上面的方法沒有解決問題，那么我們需要進(jìn)入事件管理器中查看是否有錯(cuò)誤日志：在事件查看器-Windows日志-應(yīng)用程序中，查看是否有如下錯(cuò)誤日志：無法找到來自源PostgreSQL的事件ID0的描述。本地計(jì)算機(jī)上未安裝引發(fā)此事件的組件,或者安裝已損壞?？梢园惭b或修復(fù)本地計(jì)算機(jī)上的組件。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ù)制到另一個(gè)目錄，然后重新安裝平臺(tái)，并恢復(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ù)庫啟動(dòng)后，部分?jǐn)?shù)據(jù)庫或表無法訪問這種情況下，你需要進(jìn)入data文件夾下的pg_log文件夾，查看問題發(fā)生時(shí)刻產(chǎn)生的運(yùn)行日志。permissiondeniedforrelationtb_door如果運(yùn)行日志出現(xiàn)類似下面的信息，這說明是當(dāng)前訪問用戶沒有表b_door的某些權(quán)限：ERROR:permissiondeniedforrelationtb_door如果你希望當(dāng)前用戶(以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如果運(yùn)行日志出現(xiàn)類似下面的信息，這說明是當(dāng)前用戶沒有表ac_door的所有權(quán)：ERROR:mustbeownerofrelationtb_door你可以使用管理員postgres登錄相應(yīng)數(shù)據(jù)庫，手動(dòng)執(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ù)庫的對(duì)象id(oid),“309624”表示發(fā)生問題的表的文件結(jié)點(diǎn)(filenode)如果發(fā)生損壞的表以及損壞的頁面數(shù)量較少，我們可以以犧牲部分?jǐn)?shù)據(jù)的代價(jià)恢復(fù)整體；如果損壞的表數(shù)量過多，或者損失的數(shù)據(jù)非常重要,就需要從備份中恢復(fù)數(shù)據(jù)了。當(dāng)

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

24、kind=i,表示損壞的是一個(gè)索引。例如：dept_number_index,i或者：tb_dept_pkey,i矮垃E期需要注意，損壞的可能是普通索引，也可能是主鍵或唯一鍵。如果索引的名稱中有_pkey”等很可能屬于主鍵，而名稱中含有“_key”則很可能屬于唯一鍵。還需要格外注意一點(diǎn)，表/索引可修復(fù)的前提條件是損壞的表是應(yīng)用程序創(chuàng)建的表/索引，而不是PostgreSQL的系統(tǒng)表和建立在其上的索引。如果系統(tǒng)表/建立在其上的索引發(fā)生損壞，則需要從備份中恢復(fù)數(shù)據(jù)庫。判斷一個(gè)表是否是系統(tǒng)表，最簡(jiǎn)單的方法是：如果表名是“pg_開頭的，則說明它是系統(tǒng)表。小知識(shí)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ù)庫對(duì)象。連接發(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)建這個(gè)約束: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的表對(duì)應(yīng)的文件被刪除了。解決方法如下:首先判斷是哪一個(gè)數(shù)據(jù)庫中發(fā)生了此問題。連接任意數(shù)據(jù)庫，執(zhí)行如下sql:selectdatnamefrompg_databasewhereoid=272816查詢結(jié)果如下：testdb從備份中恢復(fù)該數(shù)據(jù)庫。2.3數(shù)據(jù)庫啟動(dòng)后，部分?jǐn)?shù)據(jù)庫或表無法訪問Nobufferspaceavailable有時(shí)，在服務(wù)管理器中，PostgreSQL顯示為正在運(yùn)行狀態(tài)，但是使用客戶端連接使，提示couldn

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

30、存異常過高，并處理它。在Windows上有一種特殊的情況，各種服務(wù)起來正常，但內(nèi)存很少，這是因?yàn)閣indowsSocket連接關(guān)閉后，內(nèi)存不釋放。對(duì)于windows2008，解決方法是打windows補(bǔ)丁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ù)庫