PG數(shù)據(jù)庫(kù)管理

配置文件

首先要告訴你的就是這個(gè)配置文件正在postgresql數(shù)據(jù)庫(kù)配置文件data目錄下

postgresql.conf

在9.4的版本里引入了一個(gè)新的名為postgresql.auto.conf的配置文件，其中配置項(xiàng)會(huì)覆蓋postgresql.conf的同名配置項(xiàng)。所以建議不要修改postgresql.conf，而是優(yōu)先修改postgresql.auto.conf

該文件中包含一些通用的設(shè)置，比如內(nèi)存分配 ，新建database的默認(rèn)存儲(chǔ)位置，postgresql服務(wù)器的ip地址，日志以及許多其他設(shè)置。

查看postgresql.conf視圖即可查看所有配置項(xiàng)內(nèi)容，無需打開配置文件。

SELECT
    name,context,unit,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('listen_addressed','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context,name;

• context字段代表配置項(xiàng)的作用范圍。
• user表示用戶級(jí)配置項(xiàng)，他可以被每個(gè)用戶單獨(dú)修改，也就是說該配置項(xiàng)針對(duì)每個(gè)用戶都可以有不同的值，用戶修改后會(huì)在自己的所有會(huì)話中生效。如果是超級(jí)用戶修改了一個(gè)user級(jí)的配置項(xiàng)，那么此后鏈接上的用戶都會(huì)將這個(gè)修改過的值作為默認(rèn)值。
• context值為superuser表示是超級(jí)用戶級(jí)配置項(xiàng)，只能由超級(jí)用戶修改，修改并且重新加載后會(huì)在所有用戶會(huì)話中生效。非超級(jí)用戶不能在自己的會(huì)話中修改覆蓋這個(gè)值。
• context值為postmaster表示是整個(gè)服務(wù)實(shí)例級(jí)配置項(xiàng)，更改后需要重啟postgresSQL服務(wù)才能生效。
• context為usr和superuser的配置項(xiàng)可以在database級(jí)，用戶級(jí)，會(huì)話級(jí)和函數(shù)級(jí)分別設(shè)置。比如說如果會(huì)寫很長(zhǎng)sql的用戶來說，work_mem參數(shù)應(yīng)該設(shè)置大一些；再比如有密集的排序操作，也就可以調(diào)大work_mem的值。database級(jí)，用戶級(jí)，會(huì)話級(jí)和函數(shù)級(jí)的參數(shù)設(shè)置不需要執(zhí)行重新加載操作。數(shù)據(jù)庫(kù)級(jí)的參數(shù)設(shè)置會(huì)在用戶下次連接到該數(shù)據(jù)庫(kù)時(shí)生效。會(huì)話級(jí)和函數(shù)級(jí)的參數(shù)設(shè)置立即生效。
• 我們要注意內(nèi)存相關(guān)參數(shù)所用的單位。在上圖我們可以看到，內(nèi)存相關(guān)參數(shù)，有些是8KB有些是KB
• setting是指當(dāng)前設(shè)置；boot_val是指默認(rèn)設(shè)置；reset_val是指重新啟動(dòng)服務(wù)器或重新加載設(shè)置之后的新設(shè)置。修改 了設(shè)置后，一定要去查看setting和reset_val并確保二者是一致的，否則就說明設(shè)置并未生效，需要重啟服務(wù)器或者重新加載設(shè)置。
• pg的9.5版本引入了一個(gè)新的pg_file_settings視圖，通過該視圖也可以進(jìn)行配置信息查詢。查詢?cè)撘晥D會(huì)列出每個(gè)配置項(xiàng)所屬的配置文件。其中applied字段表示該配置項(xiàng)是否已經(jīng)生效，如果值為f，表示需要重啟服務(wù)器或者重新加載配置文件。如果postgresql.conf和postgresql.auto.conf中存在同名配置，那么后者會(huì)覆蓋前者，并且前者在pg_file_settings中對(duì)應(yīng)的條目會(huì)顯示applied字段為f。

SELECT name,sourcefile,sourceline,setting,applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;

以下幾個(gè)配置項(xiàng)需要注意，不然可能導(dǎo)致客戶端無法連接：

• listen_addresses

表示postgresql服務(wù)使用的ip地址，我這里設(shè)置的*，表示使用任意的ip均可連接到POSTgresql數(shù)據(jù)庫(kù)。

• port

pg庫(kù)的偵聽端口，默認(rèn)值為5432.

• max_connections

系統(tǒng)允許的最大并發(fā)連接數(shù)

• log_destination

定義日志文件的輸出格式，默認(rèn)值是stderr。如果是要記日志的話切記要講logging_collection配置項(xiàng)設(shè)為on。

下面介紹的這些配置項(xiàng)會(huì)影響系統(tǒng)的整體性能，其默認(rèn)值一般不是最優(yōu)的。建議根據(jù)實(shí)際情況進(jìn)行調(diào)整。

• shared_buffers

此設(shè)置定義了用于緩存最近訪問過的數(shù)據(jù)頁(yè)的內(nèi)存區(qū)大小，所有用戶會(huì)話均可共享此緩存區(qū)。此設(shè)置對(duì)查詢速度有著重大影響，一般來說設(shè)置的越大越好，至少應(yīng)該達(dá)到系統(tǒng)的總內(nèi)存的25% ，但不適宜超過8GB，因?yàn)槌^會(huì)出現(xiàn)邊際收益遞減效應(yīng)，即消耗的內(nèi)存很多，但得到的速度提升卻很少，這是得不償失的，意義不大的。

• effective_cache_size

該配置是一個(gè)估算值，表示操作系統(tǒng)分配多少內(nèi)存給PG專用。系統(tǒng)并不會(huì)根據(jù)這個(gè)值來真實(shí)地分配這么多內(nèi)存，但是規(guī)劃器會(huì)根據(jù)這個(gè)值來判斷系統(tǒng)是否能提供查詢執(zhí)行過程所需的內(nèi)存。如果將此值設(shè)置的過小，遠(yuǎn)遠(yuǎn)小于系統(tǒng)的真實(shí)可用內(nèi)存量，那么可能會(huì)給規(guī)劃器造成誤導(dǎo)，讓規(guī)劃器認(rèn)為系統(tǒng)可用內(nèi)存有限，從而選擇不使用索引而是執(zhí)行全表掃描（因?yàn)槭褂盟饕m然速度快，但需要占用更多的中間內(nèi)存）。在一臺(tái)專用于 運(yùn)行pgsql數(shù)據(jù)庫(kù)服務(wù)的機(jī)器上，建議是將effective_cache_size的值設(shè)為系統(tǒng)總內(nèi)存的一般或者更多。

• work_mem

此設(shè)置指定了用于執(zhí)行排序，散列關(guān)聯(lián)，表掃描等操作的最大內(nèi)存大小。要得到此設(shè)置的最優(yōu)值需要考慮以下因素：數(shù)據(jù)庫(kù)的使用方式，需要預(yù)留多少內(nèi)存給除數(shù)據(jù)庫(kù)系統(tǒng)外的程序，以及服務(wù)器是否專用于pgsql服務(wù)等問題。如果使用場(chǎng)景僅僅是有很多用戶并發(fā)執(zhí)行簡(jiǎn)單查詢，那么這個(gè)值可以設(shè)得小一點(diǎn)，這樣每個(gè)用戶都得以較為公平的使用內(nèi)存，否則第一個(gè)用戶就可能會(huì)把內(nèi)存占光。這個(gè)值該設(shè)多大同樣取決于你的機(jī)器總共有多少內(nèi)存可用

• maintenance_work_mem

此設(shè)置指定了可用于vaccum(即清空已標(biāo)記為“被刪除”狀態(tài)的記錄)這類系統(tǒng)內(nèi)部維護(hù)操作的內(nèi)存總量。其值不應(yīng)大于1GB，此設(shè)置的更改可動(dòng)態(tài)生效，執(zhí)行重新加載既可。

• max_parallel_workers_per_gather

這是9.6版本新引入的一個(gè)配置項(xiàng)，用于控制語(yǔ)句執(zhí)行的并行度。該配置項(xiàng)決定了執(zhí)行計(jì)劃的每個(gè)gather節(jié)點(diǎn)中最多允許啟動(dòng)多少個(gè)worker進(jìn)程并行工作。默認(rèn)值為0，表示不啟用并行功能。

• 修改postgresql.conf中配置項(xiàng)的值

ALTER SYSTEM

這是9.4版本之后引入的一個(gè)命令，這個(gè)命令可以更改設(shè)置。

??例如：設(shè)置一個(gè)全局生效的work_mem：

ALTER SYSTEM work_mem = '500MB';

這個(gè)命令呢是不會(huì)直接修改postgresql.conf文件本身，而是去修改postgresql.auto.conf文件。

每個(gè)設(shè)置有何各自不同的特性，有的更改后必須重啟數(shù)據(jù)庫(kù)服務(wù)才能生效，有的只要重新加載一次就可以了，下面這個(gè)命令可以實(shí)現(xiàn)設(shè)置重新加載：

SELECT pg_reload_conf();

如果你需要時(shí)常修改很多配置項(xiàng)，那么可以嘗試將他們分門別類存放到多個(gè)配置文件中，然后通過在postgresql.conf中使用include或者include_if_exists前綴來引入這些配置文件。具體語(yǔ)法如下：

include '配置文件名'

這里的配置文件可以是絕對(duì)路徑也可以是相對(duì)路徑，相對(duì)路徑的起始位置就是postgresql.conf文件本身所在的位置。

• 修改了postgresql。conf文件，結(jié)果數(shù)據(jù)庫(kù)無法啟動(dòng)，該如何？

  首要的解決方式為：日志文件，該文件位于postgresql數(shù)據(jù)文件夾的更目錄或者pg_log子文件下。去查看日志文件的最后一行。常見的錯(cuò)誤有：shared_buffers設(shè)得太大了。還有一個(gè)常見問題是由于上次系統(tǒng)異常關(guān)閉導(dǎo)致遺留了一個(gè)沒來得及刪除的postmaster.pid文件，該文件處于數(shù)據(jù)文件下，你可以手動(dòng)刪除該文件并啟動(dòng)postgresql。

pg_hba.conf

該文件用于控制Postgresql服務(wù)器的訪問權(quán)限，具體包括：允許哪些用戶連接到哪個(gè)數(shù)據(jù)庫(kù)，允許哪些ip地址連接到本服務(wù)器，以及指定連接時(shí)使用的身份驗(yàn)證模式。該文件的修改是可動(dòng)態(tài)生效的，執(zhí)行一次配置重加載既可。一個(gè)典型的pg_hba.conf文件如下圖所示：

1??身份驗(yàn)證模式。一般有以下幾種常用選項(xiàng)：ident，trust，MD5，peer以及passwd。

2??用于定義ipv6網(wǎng)段。軋制油服務(wù)器支持ipv6才可以配置該項(xiàng)，如果在非ipv6網(wǎng)絡(luò)環(huán)境下配置了這樣的條目，會(huì)導(dǎo)致pg_hba.conf文件無法加載，從而進(jìn)一步導(dǎo)致任何客戶端都無法連接。

3??用于定義ipv4網(wǎng)段。第一部分是網(wǎng)絡(luò)地址，后面跟著子網(wǎng)掩碼。

4??這是針對(duì)SSL連接的規(guī)則。在本圖中，任何使用SSL方式連接的客戶端都可以連接到PGSQL實(shí)例上去。

SSL相關(guān)配置在postgresql.conf和postgresql.auto.conf中，包含一下幾項(xiàng)：ssl，ssl_cert_file,ssl_key_file。一旦確認(rèn)客戶端支持SSL，postgresql服務(wù)端就會(huì)接受其連接請(qǐng)求，并且該連接上的所有傳輸內(nèi)容都會(huì)使用ssl key加密。

5??這是允許與本節(jié)點(diǎn)構(gòu)成復(fù)制關(guān)系的其他Postgresql服務(wù)器節(jié)點(diǎn)的IP網(wǎng)段。

對(duì)于每一個(gè)連接請(qǐng)求，就像防火墻一樣，這玩意是從上到下匹配規(guī)則鏈的。

PostgreSQL10 中引入了一個(gè)新的名為pg_hba_file_rules的視圖，通過查詢?cè)撘晥D可以直接看到pg_hba.conf中的內(nèi)容。

• 修改pg_hba.conf文件，結(jié)果服務(wù)器奔潰了、

don't害怕，因?yàn)槿绻鹥ostgres服務(wù)無法正確的解析pg_hba.conf文件，那么為確保系統(tǒng)安全，他會(huì)禁止所有的連接請(qǐng)求甚至是禁止系統(tǒng)啟動(dòng)。你去看一手日志，然后將錯(cuò)誤的內(nèi)榮改正就好，如果你經(jīng)常會(huì)改錯(cuò)，那就事先記得備份一手。

• 身份驗(yàn)證方法

最常用的身份驗(yàn)證方法有以下這些：

• trust

這是最不安全的身份驗(yàn)證模式，用戶無需提供密碼就可以連接到數(shù)據(jù)庫(kù)，只要源端IP地址，連接用戶名，要訪問的databases名都與該條規(guī)則匹配，用戶就可以連上來。trust模式很不安全，因此要有所限制。

• MD5

該模式很常用，要求連接發(fā)起者攜帶MD5算法加密的密碼。

• password

該模式要求連接發(fā)起者攜帶明文密碼進(jìn)行身份驗(yàn)證。

• ident

該身份驗(yàn)證模式下，系統(tǒng)會(huì)將請(qǐng)求發(fā)起者的操作系統(tǒng)用戶映射為postgresSQL數(shù)據(jù)庫(kù)內(nèi)部用戶，并以該內(nèi)部用戶的權(quán)限登陸，且此時(shí)無需提供登陸密碼。Windows上是不支持ident驗(yàn)證方式。

• peer

該模式下系統(tǒng)會(huì)直接從操作系統(tǒng)內(nèi)核獲取當(dāng)前連接發(fā)起者的操作系統(tǒng)用戶名，如果與其請(qǐng)求連接的postgresql用戶名一致，即可連接成功。該模式僅可用于Linux，BSD，macOS和Solaris，并且僅可用于本地服務(wù)器發(fā)起的連接。

• cert

該模式要求客戶端必須使用SSL方式進(jìn)行連接。連接發(fā)起者必須提供一個(gè)合法的SSL做證書。該模式使用一個(gè)身份認(rèn)證文件來講SSL證書文件映射為postgresql數(shù)據(jù)庫(kù)的內(nèi)部用戶 ，該模式支持只在所有支持SSL連接的平臺(tái)上都可用。

pg_ident.conf

如果該文件存在，則系統(tǒng)會(huì)基于文件內(nèi)容將當(dāng)前登錄的操作系統(tǒng)用戶映射為一個(gè)postgreSQL數(shù)據(jù)庫(kù)內(nèi)部用戶的身份來登錄。有些人會(huì)把操作系統(tǒng)的root用戶映射為postgreSQL的postgres超級(jí)用戶賬號(hào)。

在PG的官方術(shù)語(yǔ)體系中，角色role就表示用戶，但并不是所有的角色都需要具備登錄權(quán)限，比如組角色通常就不需要。

如果你在安裝過程中使用了默認(rèn)配置，則上述的文件會(huì)位于Postgresql主數(shù)據(jù)文件夾中。當(dāng)然，如果你不確定這些文件的具體位置，以超級(jí)用戶身份連接到任何一個(gè)數(shù)據(jù)庫(kù)上并執(zhí)行下面的查詢語(yǔ)句就會(huì)查到：

SELECT name ,setting FROM pg_settings WHERE category='File Locations';

讓配置文件生效

有些配置項(xiàng)修改后會(huì)需要重啟pgsql服務(wù)實(shí)例，但是有的配置項(xiàng)修改只需要重新加載一下配置文件既可生效，如何判斷呢？

我們可以查看該配置項(xiàng)的context屬性，如果是postmaster ，那么需要重啟；如果是user，那么重新加載配置文件既可。

你說怎么查context屬性？？？上面有，你可以看看那個(gè)select pg_setting那個(gè)語(yǔ)句。

• 重新加載配置文件

    pg_ctl reload -D  /usr/local/pgsql/data  (后面跟的是你的數(shù)據(jù)目錄)

重新加載pg服務(wù)

pg_ctl  restart -D /usr/local/pgsql/data  (后面跟的是你的數(shù)據(jù)目錄)

以上就是postgresql數(shù)據(jù)庫(kù)配置文件postgresql.conf，pg_hba.conf,pg_ident.conf的詳細(xì)內(nèi)容，更多關(guān)于postgresql 配置文件的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！