Postgresql學習筆記（二）配置檔案 1、配置檔案 2、檢視配置檔案的位置： 3、postgresql.conf 4、pg_hba.conf 5、配置檔案的重新加載

配置檔案控制着一個PostgreSQL伺服器執行個體的基本行為，主要包含postgresql.conf、pg_hba.conf、pg_ident.conf

（1）postgresql.conf

   該檔案包含一些通用設定，比如記憶體配置設定，建立database的預設存儲位置，PostgreSQL伺服器的IP位址，日志的位置以及許多其他設定。9.4版引入了

   一個新的postgresql.auto.conf檔案，任何時候執行Altersystem SQL指令，都會建立或重寫該檔案。該檔案中的設定會替代postgresql.conf檔案中的設定。

（2）pg_hba.conf

    該檔案用于控制通路安全性，管理用戶端對Postgresql伺服器的通路權限，内容包括：允許哪些使用者連接配接到哪個資料庫，允許哪些IP或者哪個網段的IP連

    接到本伺服器，以及指定連接配接時使用的身份驗證模式

（3）pg_ident.conf

   pg_hba.conf的權限控制資訊中的身份驗證模式字段如果指定為ident方式，則使用者連接配接時系統會嘗試通路pg_ident檔案，如果該檔案存在，則系統會基于

    檔案内容将目前執行登入操作的作業系統使用者映射為一個PostgreSQL資料庫内部使用者的身份來登入。

<code>postgres=</code><code># selectname,setting from pg_settings where category='File Locations';</code>

<code>       </code><code>name        |                 setting                 </code>

<code>-------------------+-----------------------------------------</code>

<code> </code><code>config_file       |</code><code>/var/lib/pgsql/9</code><code>.6</code><code>/data/postgresql</code><code>.conf</code>

<code> </code><code>data_directory    | </code><code>/var/lib/pgsql/9</code><code>.6</code><code>/data</code>

<code> </code><code>external_pid_file | </code>

<code> </code><code>hba_file          | </code><code>/var/lib/pgsql/9</code><code>.6</code><code>/data/pg_hba</code><code>.conf</code>

<code> </code><code>ident_file        | </code><code>/var/lib/pgsql/9</code><code>.6</code><code>/data/pg_ident</code><code>.conf</code>

3.1、關鍵的設定

<code>postgres=</code><code># selectname,context,unit,setting,boot_val,reset_val from pg_settings where namein('listen_addresses','max_connections','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')order by context,name;</code>

<code>         </code><code>name         | context   | unit | setting |boot_val  | reset_val </code>

<code>----------------------+------------+------+---------+-----------+-----------</code>

<code> </code><code>listen_addresses     | postmaster |      | *      | localhost | *</code>

<code> </code><code>max_connections      | postmaster |      | 100    | 100       | 100</code>

<code> </code><code>shared_buffers       | postmaster | 8kB  | 16384  | 1024      | 16384</code>

<code> </code><code>effective_cache_size | user       | 8kB | 524288  | 524288    | 524288</code>

<code> </code><code>maintenance_work_mem | user       | kB  | 65536   | 65536     | 65536</code>

<code> </code><code>work_mem             | user       | kB  | 4096    | 4096      | 4096</code>

<code>(6 rows)</code>

context 設定為postmaster，更改此形參後需要重新開機PostgreSQL服務才能生效；

設定為user，那麼隻需要執行一次重新加載即可全局生效。重新開機資料庫服務會終止活動連接配接，但重新加載不會。  

unit 字段表示這些設定的機關

setting是指目前設定；boot_val是指預設設定；reset_val是指重新啟動伺服器或重新加載設定之後的新設定

在postgresql.conf中修改了設定後，一定記得檢視一下setting和reset_val并確定二者是一緻，否則說明設定并未生效，需要重新啟動伺服器或者重新加載設定

3.2、postgresql.auto.conf與postgresql.conf差別

對于9.4版及之後的版本來說，Postgresql.auto.conf的優先級是高于postgresql.conf的，如果這兩個檔案中存在同名配置項，則系統會優先選擇前者設定的值。

3.3、postgresql.conf以下網絡設定，修改這些值是一定要重新啟動資料庫服務的

listen_addresses 一般設定為localhost或者local，但也有很多人會設為*，表示使用本機任一IP位址均可連接配接到Postgresql服務

port 預設值 為5432

max_connections

3.4、以下四個設定對系統性能有着全局性的影響，建議你在實際環境下通過實測來找到最優值

(1)share_buffers

    用于緩存最近通路過的資料頁的記憶體區大小，所有使用者會話均可共享此緩存區

    一般來說越大越好，至少應該達到系統總記憶體的25%，但不宜超過8GB，因為超過後會出現“邊際收益遞減”效應。

    需重新開機postgreSQL服務

（2）effective_cache_size

一個查詢執行過程中可以使用的最大緩存，包括作業系統使用的部分以及PostgreSQL使用部分，系統并不會根據這個值來真實地配置設定這麼多記憶體，但是規劃器會根據這個值來判斷系統能否提供查詢執行過程中所需的記憶體。如果将此設定設得過小，遠遠小于系統真實可用記憶體量，那麼可能會給規劃器造成誤導，讓規劃器認為系統可用記憶體有限，進而選擇不使用索引而是走全表掃描（因為使用索引雖然速度快，但需要占用更多的中間記憶體）。

在一台專用于運作PostgreSQL資料庫服務的伺服器上，建議将effective_cache_size的值設為系統總記憶體的一半或者更多。

此設定可動态生效，執行重新加載即可。

（3）work_mem

此設定指定了用于執行排序，哈希關聯，表掃描等操作的最大記憶體量。

   （4）mintenance_work_mem

     此設定指定可用于vaccum操作（即清空已标記為“被删除”狀态的記錄）這類系統内部維護操作的記憶體總量。

     其值不應大于1GB

3.5修改參數指令

<code>Alter system </code><code>set</code> <code>work_mem=8192;</code>

設定重新加載指令

<code>Select pg_reload_conf();</code>

3.6、遇到修改了postgresql.conf檔案，結果伺服器崩潰了這種情況

定位這種問題最簡單的方法是檢視日志檔案，該檔案位于postgresql資料檔案夾的根目錄或者pg_log子檔案夾下。

cat /var/lib/pgsql/9.6/data/pg_hba.conf

<code># TYPE  DATABASE        USER            ADDRESS                 METHOD</code>

<code> </code> 

<code># "local" isfor Unix domain socket connections only</code>

<code>local</code>   <code>all             all                                     peer</code>

<code># IPv4 localconnections:</code>

<code>host    all             all             0.0.0.0</code><code>/0</code>               <code>trust</code>

<code># IPv6 localconnections:</code>

<code>host    all             all             ::1</code><code>/128</code>                 <code>ident</code>

<code># Allow replicationconnections from localhost, by a user with the</code>

<code># replication privilege.</code>

<code>#local   replication     postgres                                peer</code>

<code>#host    replication     postgres        127.0.0.1/32            ident</code>

<code>#host    replication     postgres        ::1/128                 ident</code>

(1)   身份驗證模式，一般以下幾種常用選項：ident、trust、md5以及password

1版本開始引入了peer身份驗證模式。

Ident和peer模式公适用于Linux，Unix和Mac,不适用于windwos

Reject模式，其作用是拒絕所有請求。

(2)   如果你将+0.0.0./0 reject+規則放到+127.0.0.1/32 trust+的前面，那麼此時本地使用者全都無法連接配接，即使下面有規則允許也不行。

（3）各模式

trust最不安全的身份驗證模式，該模式允許使用者“自證清白”，即可以不用密碼就連到資料庫

md5該模式最常用，要求連接配接發起者攜帶用md5算法加密的密碼

password 不推薦，因為該模式使用明文密碼進行身份驗證，不安全

ident：該身份驗證模式下，系統會将請求發起的作業系統使用者映射為PostgreSQL資料庫内部使用者，并以該内部使用者的權限登入，且此時無需提供登入密碼。作業系統使用者與資料庫内部使用者之間的映射關系會記錄在pg_ident.conf檔案中。

peer使用發起端的作業系統名進行身份驗證

<code>/usr/pgsql-9</code><code>.6</code><code>/bin/pg_ctlreload</code> <code>-D </code><code>/var/lib/pgsql/9</code><code>.6</code><code>/data/</code> 

<code>systemctlreload postgresql-9.6.service </code>

<code>selectpg_reload_conf();</code>

<code></code>

本文轉自 corasql 51CTO部落格，原文連結：http://blog.51cto.com/corasql/1910485，如需轉載請自行聯系原作者