PG數據庫管理

配置文件

首先要告訴你的就是這個配置文件正在postgresql數據庫配置文件data目錄下

postgresql.conf

在9.4的版本里引入了一個新的名為postgresql.auto.conf的配置文件，其中配置項會覆蓋postgresql.conf的同名配置項。所以建議不要修改postgresql.conf，而是優先修改postgresql.auto.conf

該文件中包含一些通用的設置，比如內存分配 ，新建database的默認存儲位置，postgresql服務器的ip地址，日志以及許多其他設置。

查看postgresql.conf視圖即可查看所有配置項內容，無需打開配置文件。

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

以下幾個配置項需要注意，不然可能導致客戶端無法連接：

• listen_addresses

表示postgresql服務使用的ip地址，我這里設置的*，表示使用任意的ip均可連接到POSTgresql數據庫。

• port

pg庫的偵聽端口，默認值為5432.

• max_connections

系統允許的最大并發連接數

• log_destination

定義日志文件的輸出格式，默認值是stderr。如果是要記日志的話切記要講logging_collection配置項設為on。

下面介紹的這些配置項會影響系統的整體性能，其默認值一般不是最優的。建議根據實際情況進行調整。

• shared_buffers

此設置定義了用于緩存最近訪問過的數據頁的內存區大小，所有用戶會話均可共享此緩存區。此設置對查詢速度有著重大影響，一般來說設置的越大越好，至少應該達到系統的總內存的25% ，但不適宜超過8GB，因為超過會出現邊際收益遞減效應，即消耗的內存很多，但得到的速度提升卻很少，這是得不償失的，意義不大的。

• effective_cache_size

該配置是一個估算值，表示操作系統分配多少內存給PG專用。系統并不會根據這個值來真實地分配這么多內存，但是規劃器會根據這個值來判斷系統是否能提供查詢執行過程所需的內存。如果將此值設置的過小，遠遠小于系統的真實可用內存量，那么可能會給規劃器造成誤導，讓規劃器認為系統可用內存有限，從而選擇不使用索引而是執行全表掃描（因為使用索引雖然速度快，但需要占用更多的中間內存）。在一臺專用于 運行pgsql數據庫服務的機器上，建議是將effective_cache_size的值設為系統總內存的一般或者更多。

• work_mem

此設置指定了用于執行排序，散列關聯，表掃描等操作的最大內存大小。要得到此設置的最優值需要考慮以下因素：數據庫的使用方式，需要預留多少內存給除數據庫系統外的程序，以及服務器是否專用于pgsql服務等問題。如果使用場景僅僅是有很多用戶并發執行簡單查詢，那么這個值可以設得小一點，這樣每個用戶都得以較為公平的使用內存，否則第一個用戶就可能會把內存占光。這個值該設多大同樣取決于你的機器總共有多少內存可用

• maintenance_work_mem

此設置指定了可用于vaccum(即清空已標記為“被刪除”狀態的記錄)這類系統內部維護操作的內存總量。其值不應大于1GB，此設置的更改可動態生效，執行重新加載既可。

• max_parallel_workers_per_gather

這是9.6版本新引入的一個配置項，用于控制語句執行的并行度。該配置項決定了執行計劃的每個gather節點中最多允許啟動多少個worker進程并行工作。默認值為0，表示不啟用并行功能。

• 修改postgresql.conf中配置項的值

ALTER SYSTEM

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

??例如：設置一個全局生效的work_mem：

ALTER SYSTEM work_mem = '500MB';

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

每個設置有何各自不同的特性，有的更改后必須重啟數據庫服務才能生效，有的只要重新加載一次就可以了，下面這個命令可以實現設置重新加載：

SELECT pg_reload_conf();

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

include '配置文件名'

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

• 修改了postgresql。conf文件，結果數據庫無法啟動，該如何？

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

pg_hba.conf

該文件用于控制Postgresql服務器的訪問權限，具體包括：允許哪些用戶連接到哪個數據庫，允許哪些ip地址連接到本服務器，以及指定連接時使用的身份驗證模式。該文件的修改是可動態生效的，執行一次配置重加載既可。一個典型的pg_hba.conf文件如下圖所示：

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

2??用于定義ipv6網段。軋制油服務器支持ipv6才可以配置該項，如果在非ipv6網絡環境下配置了這樣的條目，會導致pg_hba.conf文件無法加載，從而進一步導致任何客戶端都無法連接。

3??用于定義ipv4網段。第一部分是網絡地址，后面跟著子網掩碼。

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

SSL相關配置在postgresql.conf和postgresql.auto.conf中，包含一下幾項：ssl，ssl_cert_file,ssl_key_file。一旦確認客戶端支持SSL，postgresql服務端就會接受其連接請求，并且該連接上的所有傳輸內容都會使用ssl key加密。

5??這是允許與本節點構成復制關系的其他Postgresql服務器節點的IP網段。

對于每一個連接請求，就像防火墻一樣，這玩意是從上到下匹配規則鏈的。

PostgreSQL10 中引入了一個新的名為pg_hba_file_rules的視圖，通過查詢該視圖可以直接看到pg_hba.conf中的內容。

• 修改pg_hba.conf文件，結果服務器奔潰了、

don't害怕，因為如果postgres服務無法正確的解析pg_hba.conf文件，那么為確保系統安全，他會禁止所有的連接請求甚至是禁止系統啟動。你去看一手日志，然后將錯誤的內榮改正就好，如果你經常會改錯，那就事先記得備份一手。

• 身份驗證方法

最常用的身份驗證方法有以下這些：

• trust

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

• MD5

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

• password

該模式要求連接發起者攜帶明文密碼進行身份驗證。

• ident

該身份驗證模式下，系統會將請求發起者的操作系統用戶映射為postgresSQL數據庫內部用戶，并以該內部用戶的權限登陸，且此時無需提供登陸密碼。Windows上是不支持ident驗證方式。

• peer

該模式下系統會直接從操作系統內核獲取當前連接發起者的操作系統用戶名，如果與其請求連接的postgresql用戶名一致，即可連接成功。該模式僅可用于Linux，BSD，macOS和Solaris，并且僅可用于本地服務器發起的連接。

• cert

該模式要求客戶端必須使用SSL方式進行連接。連接發起者必須提供一個合法的SSL做證書。該模式使用一個身份認證文件來講SSL證書文件映射為postgresql數據庫的內部用戶 ，該模式支持只在所有支持SSL連接的平臺上都可用。

pg_ident.conf

如果該文件存在，則系統會基于文件內容將當前登錄的操作系統用戶映射為一個postgreSQL數據庫內部用戶的身份來登錄。有些人會把操作系統的root用戶映射為postgreSQL的postgres超級用戶賬號。

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

如果你在安裝過程中使用了默認配置，則上述的文件會位于Postgresql主數據文件夾中。當然，如果你不確定這些文件的具體位置，以超級用戶身份連接到任何一個數據庫上并執行下面的查詢語句就會查到：

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

讓配置文件生效

有些配置項修改后會需要重啟pgsql服務實例，但是有的配置項修改只需要重新加載一下配置文件既可生效，如何判斷呢？

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

你說怎么查context屬性？？？上面有，你可以看看那個select pg_setting那個語句。

• 重新加載配置文件

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

重新加載pg服務

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