快速解決PostgreSQL中的Permission denied問題

想開始學(xué)習(xí)SQL和Excel那本書，覺得自己親手去輸入才是正道。發(fā)現(xiàn)程序后續(xù)會(huì)用到窗口函數(shù)，可是我的mysql沒有窗口函數(shù)，這本書所提供的數(shù)據(jù)腳本分別是MS SQL Sever和PostreSQL。

上午我先安裝的sql sever，可是由于比較大且在安裝時(shí)出現(xiàn)了一些小的問題（安裝緩慢，服務(wù)啟動(dòng)不了）。無奈選擇了PostreSQL，體積小，安裝順利。

導(dǎo)入數(shù)據(jù)比較特別，先建一個(gè)表，然后把同名txt導(dǎo)入進(jìn)去。一定要用unix方式的路徑。

copy這個(gè)語句先前在mysql上沒有遇到過。學(xué)習(xí)下。。。。

根據(jù)我的錯(cuò)誤代碼發(fā)現(xiàn)是權(quán)限不夠，之前用mysql的時(shí)候并沒有注意到權(quán)限這個(gè)問題。

哇，被權(quán)限搞的真的頭皮發(fā)麻。中間的曲折過程就不表了，第一次了解到數(shù)據(jù)庫超級(jí)用戶這個(gè)概念，還有就是postresql的結(jié)構(gòu)和

mysql也很不同。數(shù)據(jù)庫-模式-表。最后是用postres創(chuàng)建了一個(gè)超級(jí)用戶。但是發(fā)現(xiàn)還是報(bào)錯(cuò)，拒絕訪問。我就想到可能是txt放在了C盤不能隨便訪問，所以我將data文件放到了D盤，導(dǎo)入成功。

成功了！今天一個(gè)下午算是折在這上面了，不過總算有收獲。了解了用戶，postresql中的copy。明天開始照著書做吧。

PS：（雖然這回的數(shù)據(jù)沒有中文）

補(bǔ)充：PostgreSQL的幾種常見問題和解決方法

1. 前言

1.1 概述

本文介紹了postgresql的幾種常見問題，并從現(xiàn)象出發(fā)，逐步排查問題，分析導(dǎo)致問題的原因并給出解決方案。

本文介紹的問題分為兩大類：一類是關(guān)于PostgreSQL無法啟動(dòng)的問題，另一類是PostgreSQL啟動(dòng)后，部分?jǐn)?shù)據(jù)庫對(duì)象無法訪問的問題。

1.2 軟件環(huán)境

本文使用的 PostgreSQL 版本是 9.6。

1.3 一些約定術(shù)語

PostgreSQL安裝路徑：默認(rèn)是 “D:\Program Files\PostgreSQL\9.6”

bin 文件夾：PostgreSQL安裝路徑下的bin文件夾。

data 文件夾：PostgreSQL安裝路徑下的data文件夾。

2. 問題和解決方法

2.1 PostgreSQL無法啟動(dòng)

PostgreSQL 沒有正常啟動(dòng)時(shí)，在 “服務(wù)”中再次啟動(dòng)失敗。

2.1.1 端口占用

我們首先需要判斷是不是該服務(wù)的端口被占用。PostgreSQL服務(wù)的默認(rèn)端口是5432，那么我們在命令行中執(zhí)行如下命令

netstat -ano | find /i "5432"

如果發(fā)現(xiàn)了某個(gè)進(jìn)程使用了5432這個(gè)端口，這說明是端口占用導(dǎo)致服務(wù)無法啟動(dòng)：

這個(gè)進(jìn)程的pid是2364，你想查看它是什么進(jìn)程，可以執(zhí)行：

tasklist | findstr "2364"

執(zhí)行結(jié)果如下：

你可以在任務(wù)管理器-進(jìn)程頁面中，或者通過下面的命令結(jié)束這個(gè)進(jìn)程：

taskkill /f /pid 5432

小知識(shí)：

PostgreSQL 是多進(jìn)程模型的數(shù)據(jù)庫。它在運(yùn)行時(shí)，會(huì)啟動(dòng)一個(gè)名為“pg_ctl”進(jìn)程和若干個(gè)名為“postgres” 的進(jìn)程。其中，進(jìn)程pg_ctl是“祖先”進(jìn)程，它表示數(shù)據(jù)庫處于運(yùn)行狀態(tài)，占用的內(nèi)存很少；其他所有工作進(jìn)程的名稱都是postgres。

在 Windows 操作系統(tǒng)上，如果 pg_ctl.exe 被異常關(guān)閉了，進(jìn)程 postgres.exe 還會(huì)存在。數(shù)據(jù)庫運(yùn)行端口仍然被占用。會(huì)導(dǎo)致數(shù)據(jù)庫無法啟動(dòng)。

2.1.2 文件 postmaster.pid 殘留

進(jìn)入 PostgreSQL的data 文件夾，查看是否有殘留的文件 postmaster.pid。正常情況下，PostgreSQL 在啟動(dòng)時(shí)會(huì)創(chuàng)建這個(gè)文件，其內(nèi)容是 PostgreSQL 的主進(jìn)程的 pid。如果它存在，則數(shù)據(jù)庫會(huì)認(rèn)為自己已經(jīng)啟動(dòng)了，所以啟動(dòng)失敗。

因此需要?jiǎng)h除這個(gè)文件，再嘗試啟動(dòng)數(shù)據(jù)庫。

2.1.3 could not open control file “global/pg_control”：Permission denied

如果端口沒有被占用，那么你可以用PostgreSQL原生的命令啟動(dòng)它。

進(jìn)入postgresql安裝路徑下的 bin 文件夾，在這里打開命令行，執(zhí)行下面的命令：

.\pg_ctl start -D ..\data

如果程序報(bào)出如下錯(cuò)誤：

ERROR: could not open control file “global/pg_control”: Permission denied

則說明當(dāng)前操作系統(tǒng)用戶丟失了data文件夾及其內(nèi)容的權(quán)限。

下面是解決方法：

1. 首先，進(jìn)入postgresql 的安裝路徑，右鍵data文件夾，依次點(diǎn)擊屬性——安全——編輯，你能看到所有用戶或用戶組的權(quán)限。

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)再添加“修改”和 “寫入”。

3. 保存并嘗試再次在bin 文件夾下執(zhí)行：

.\pg_ctl start -D ..\data

觀察PostgreSQL數(shù)據(jù)庫能否啟動(dòng)。

2.1.4 could not locate a valid checkpoint record

如果啟動(dòng)數(shù)據(jù)庫時(shí)，提示“正在啟動(dòng)服務(wù)器進(jìn)程”，且長時(shí)間無法啟動(dòng)成功，如下圖所示，需要查看數(shù)據(jù)庫運(yùn)行日志，它們位于data文件夾下的pg_log中的。

打開問題發(fā)生時(shí)的數(shù)據(jù)庫運(yùn)行日志，查看信息。

如果日志中出現(xiàn)類似下面黑體字的信息，說明是PostgreSQL數(shù)據(jù)庫中的預(yù)寫式日志（write ahead log，簡稱WAL，又稱事務(wù)日志，簡稱xlog）損壞了：

LOG: could not open file "pg_xlog/0000000100000000000000E7" (log file 0, segment 231): No such file or directory
LOG: invalid primary checkpoint record
LOG: could not open file "pg_xlog/0000000100000000000000E7" (log file 0, segment 231): No such file or directory
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record

解決方法如下：

進(jìn)入bin 文件夾，在這里打開命令行，執(zhí)行下面的命令：

.\pg_resetxlog.exe -f ..\data

在日志重置后，再嘗試啟動(dòng)數(shù)據(jù)庫。

2.1.5 failed to re-find parent key in index "227236" for split pages 370/371

有時(shí)，數(shù)據(jù)庫無法啟動(dòng)時(shí)，我們查看位于data文件夾下的pg_log中的數(shù)據(jù)庫運(yùn)行日志，會(huì)發(fā)現(xiàn)類似下面的信息：

LOG: redo starts at 270/55E04AE8
LOG: could not open file pg_xlog/0000000100000270000000CC" (log file 624, segment 204): No such file or directory
LOG: redo done at 270/CBFFE940
LOG: last completed transaction was at log time 2018-11-26 01:55:01.259996-02
FATAL: failed to re-find parent key in index "227236" for split pages 370/371
LOG: startup process (PID 5011) exited with exit code 1
LOG: aborting startup due to startup process failure

上面黑體字的信息，同樣說明是PostgreSQL數(shù)據(jù)庫中的預(yù)寫式日志文件損壞了。

該問題的解決方法和2.1.3節(jié)的問題的解決方法相同。

2.1.6 無法找到來自源 PostgreSQL 的事件 ID 0 的描述。

如果上面的方法沒有解決問題，那么我們需要進(jìn)入事件管理器中查看是否有錯(cuò)誤日志：

在事件查看器-Windows日志-應(yīng)用程序中，查看是否有如下錯(cuò)誤日志：

無法找到來自源 PostgreSQL 的事件 ID 0 的描述。本地計(jì)算機(jī)上未安裝引發(fā)此事件的組件,或者安裝已損壞?？梢园惭b或修復(fù)本地計(jì)算機(jī)上的組件。

如果出現(xiàn)了這樣的信息，則說明PostgreSQL軟件已經(jīng)損壞，需要重新安裝。不過，數(shù)據(jù)文件不一定損壞了，因此如果上次備份至今，數(shù)據(jù)庫中產(chǎn)生過非常重要的數(shù)據(jù)（比如賬單信息），你應(yīng)該將data文件夾復(fù)制到另一個(gè)目錄，然后重新安裝平臺(tái)，并恢復(fù)data文件夾。

2.1.7 Could not read from file "pg_clog/000E" at offset 172032

還有一種不常見的情況。如果日志中出現(xiàn)類似下面的信息：

ERROR: could not access status of transaction 710708
DETAIL: Could not read from file "pg_clog/000E" at offset 172032: No error.

則表示位于data文件夾下pg_clog中的名為 000E 的提交日志文件丟失了。

解決方法如下：

在linux 操作系統(tǒng)中，執(zhí)行下列命令：

dd if=/dev/zero of=/root/000E bs=256k count=1

或者在windows中安裝 dd，隨后執(zhí)行：

dd if=/dev/zero of=D:\000E bs=256k count=1

然后將創(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)行日志。

2.2.1 permission denied for relation tb_door

如果運(yùn)行日志出現(xiàn)類似下面的信息，這說明是當(dāng)前訪問用戶沒有表tb_door的某些權(quán)限：

ERROR: permission denied for relation tb_door

如果你希望當(dāng)前用戶（以myuser為例）擁有特定訪問權(quán)限（以SELECT，INSERT，UPDATE ，DELETE為例），可以這樣解決：

首先，通過postgres用戶或擁有tb_door 相應(yīng)訪問權(quán)限即授予權(quán)限的用戶登錄數(shù)據(jù)庫；

執(zhí)行如下命令，為用戶授予權(quán)限：

grant SELECT,INSERT,UPDATE,DELETE on tb_door to myuser

2.2.2 must be owner of relation tb_door

如果運(yùn)行日志出現(xiàn)類似下面的信息，這說明是當(dāng)前用戶沒有表ac_door的所有權(quán)：

ERROR: must be owner of relation tb_door

你可以使用管理員postgres登錄相應(yīng)數(shù)據(jù)庫，手動(dòng)執(zhí)行下面命令將tb_door的屬主你希望的用戶，以myuser為例：

Alter table tb_door owner to myuser;

2.2.3 invalid page header in block 120 of relation base/272816/309624

如果日志中出現(xiàn)類似下面的信息：

ERROR: invalid page header in block 120 of relation base/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)發(fā)生損壞的表以及損壞的頁面數(shù)量較少時(shí)，解決方法如下：

確定發(fā)生問題的數(shù)據(jù)庫。連接任意數(shù)據(jù)庫，執(zhí)行下面的sql語句：

select datname from pg_database where oid = 272816;

查詢結(jié)果如下：

testdb

這表示發(fā)生問題的數(shù)據(jù)庫名是testdb

2. 查找損壞的數(shù)據(jù)庫對(duì)象。連接發(fā)生問題的數(shù)據(jù)庫，執(zhí)行下面的sql語句：

select relname,relkind from pg_class where relfilenode = 309624

如果查詢結(jié)果中 relkind = r，表示損壞的是表。

例如：

tb_door, r

relname = tb_door這表示損壞的表是tb_door。

如果查詢結(jié)果中relkind = i，表示損壞的是一個(gè)索引。

例如：

dept_number_index, i

或者：

tb_dept_pkey, i

需要注意，損壞的可能是普通索引，也可能是主鍵或唯一鍵。如果索引的名稱中有“_pkey”等很可能屬于主鍵，而名稱中含有 “_key”則很可能屬于唯一鍵。

還需要格外注意一點(diǎn)，表/索引可修復(fù)的前提條件是損壞的表是應(yīng)用程序創(chuàng)建的表/索引，而不是PostgreSQL的系統(tǒng)表和建立在其上的索引。如果系統(tǒng)表/建立在其上的索引發(fā)生損壞，則需要從備份中恢復(fù)數(shù)據(jù)庫。判斷一個(gè)表是否是系統(tǒng)表，最簡單的方法是：如果表名是“pg_”開頭的，則說明它是系統(tǒng)表。

小知識(shí)

pgclass.relkind 的值有下面幾種：

r： 表示ordinary table（普通表）；

i： 表示index（索引）；

S： 表示sequence（序列）；

V： 表示view（視圖）；

m： 表示materialized view（物化視圖）；

c： 表示composite type（復(fù)合類型）;

t： 表示TOAST table（TOAST 表）；

f： 表示foreign table（外部表）

3. 修復(fù)損壞的數(shù)據(jù)庫對(duì)象。連接發(fā)生損壞的數(shù)據(jù)庫，執(zhí)行修復(fù)命令。

如果損壞的是表，以tb_door為例，則依次執(zhí)行下列命令即可完成修復(fù)：

set zero_damaged_pages = on;
vacuum full tb_door;
reindex table tb_door;

如果損壞的是普通索引，以dept_number_index為例， 則依次執(zhí)行：

set zero_damaged_pages = on;
reindex index dept_number_index;

如果損壞的是主鍵或唯一鍵，則首先需要找到它所在的表，以tb_dept_pkey為例：

Select tablename,indexname from pg_indexes where indexname = ‘tb_dept_pkey';

查詢結(jié)果：

tb_dept, tb_dept_pkey

然后獲取索引的定義：

select pg_get_constraintdef((select oid from pg_constraint where conname = ' tb_dept_pkey '));

查詢結(jié)果：

PRIMARY KEY (dept_id)

然后重新創(chuàng)建這個(gè)約束：

Alter table drop constriant tb_dept_pkey;
Alter table add constraint tb_dept_pkey PRIMARY KEY (dept_id);

2.2.4 could not read block 190 in file "base/272816/309624"

該問題的解決方法與2.2.2 節(jié)的問題完全相同。

2.2.5 could not open file "base/272816/379923": No such file or directory

如果日志中出現(xiàn)類似下面的信息：

2019-01-21 14:28:03 HKT ERROR: could not open file "base/272816/379923": No such file or directory

則說明，oid為272816的數(shù)據(jù)庫中，oid為379923的表對(duì)應(yīng)的文件被刪除了。

解決方法如下：

1. 首先判斷是哪一個(gè)數(shù)據(jù)庫中發(fā)生了此問題。連接任意數(shù)據(jù)庫，執(zhí)行如下sql：

select datname from pg_database where oid = 272816

查詢結(jié)果如下：

testdb

2. 從備份中恢復(fù)該數(shù)據(jù)庫。

2.3 數(shù)據(jù)庫啟動(dòng)后，部分?jǐn)?shù)據(jù)庫或表無法訪問

2.3.1 No buffer space available

有時(shí)，在服務(wù)管理器中，PostgreSQL 顯示為 正在運(yùn)行狀態(tài)，但是使用客戶端連接使，提示“could not connect to server: No buffer space available”，在postgresql運(yùn)行日志中，也能看到類似日志。

解決方法如下：

首先，在服務(wù)管理器中關(guān)閉 PostgreSQL 服務(wù)。

嘗試用 PostgreSQL 自身的命令啟動(dòng)它。進(jìn)入postgresql安裝路徑下的 bin 文件夾，在這里打開命令行，執(zhí)行下面的命令：

.\pg_ctl start -D ..\data

3. 觀察提示信息。如果提示PostgreSQL啟動(dòng)成功，則用客戶端連接數(shù)據(jù)庫；如果啟動(dòng)不成功，則參考 2.1 章的內(nèi)容。

4. 如果仍然有這樣的錯(cuò)誤提示，那說明很可能是內(nèi)存不足。你需要在操作系統(tǒng)中查看內(nèi)存，如果發(fā)現(xiàn)可用內(nèi)存較少，那你需要觀察是否有服務(wù)內(nèi)存異常過高，并處理它。在 Windows 上 有一種特殊的情況，各種服務(wù)起來正常，但內(nèi)存很少，這是因?yàn)?windows Socket 連接關(guān)閉后，內(nèi)存不釋放。對(duì)于windows 2008，解決方法是打 windows補(bǔ)丁 KB2577795。

2.3.2 no pg_hba.conf entry for host

如果日志中出現(xiàn)類似下面的信息:

FATAL: no pg_hba.conf entry for host "192.168.0.123", user "testuser", database "testdb"

則表示數(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ù)庫，可以添加下面這一行：

host all    all   0.0.0.0/0     md5

如果你只希望192.168.0.123 上的用戶可以訪問此數(shù)據(jù)庫，則添加：

host all    all   192.168.0.123/32     md5

或者你僅僅希望192.168.0.123 上的 testuser 用戶可以訪問此數(shù)據(jù)庫，則添加：

host testuser    all   192.168.0.123/32     md5

重啟數(shù)據(jù)庫即可。

小知識(shí)：

pg_hba.conf 是 postgresql 服務(wù)端的訪問權(quán)限控制文件，控制來自哪里的什么用戶，以什么建立方式連接，以什么方法認(rèn)證，訪問哪一個(gè)數(shù)據(jù)庫。每行是一個(gè)訪問控制條目，內(nèi)容的示例如下：

host all    all   127.0.0.1/32     md5

每列的含義如下：

第一列表示訪問域的類型，其值有l(wèi)ocal，host，hostssl，hostnossl。一般選擇 host，表示使用 TCP/IP 建立的連接。

第二列表示允許訪問的數(shù)據(jù)庫用戶，“all” 表示所有用戶可以訪問。

第三列表示允許被訪問的數(shù)據(jù)庫名，“all” 表示所有數(shù)據(jù)庫都允許被訪問。

第四列表示允許訪問的ip地址，127.0.0.1/32表示本地IP地址，192.168.0.123/32 表示地址192.168.0.123，192.168.0.0/24表示子網(wǎng)192.168.0.0 ~ 192.168.0.255，0.0.0.0/0表示任何IP地址。

第五列是認(rèn)證的方式?！癿d5” 表示MD5密碼認(rèn)證，trust表示無密碼認(rèn)證。

2.3.3 No connection could be made because the target machine actively refused it.

如果日志中出現(xiàn)類似下面的信息:

LOG: could not receive data from client: No connection could be made because the target machine actively refused it.

則表示有一些因素使數(shù)據(jù)庫服務(wù)器拒絕了客戶端的連接。

解決的思路如下：

首先檢查平臺(tái)有沒有單點(diǎn)登錄。如果有，關(guān)閉。

檢查有沒有安裝防火墻，如果有，允許5432端口連接。

檢查 data 目錄中的配置文件 postgresql.conf 中的參數(shù) listen_addresses，把它的值改為 '*', 或者包含客戶端的IP。

修改data 目錄中的訪問權(quán)限配置文件 pg_hba.conf。如果你希望所有地址的所有用戶可以訪問此服務(wù)器中的全部數(shù)據(jù)庫，可以添加下面這一行：

host all    all   0.0.0.0/0     md5

如果你只希望192.168.0.123 上的用戶可以訪問此數(shù)據(jù)庫，則添加：

host all    all   192.168.0.123/32     md5

5. 重啟數(shù)據(jù)庫，觀察能否訪問。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。