Postgresql學習筆記（二）設定檔

標籤：ip地址   資料庫   database   設定檔   

1、設定檔

設定檔控制著一個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資料庫內部使用者的身份來登入。

2、查看設定檔的位置：    

postgres=# selectname,setting from pg_settings where category=‘File Locations‘;       name        |                 setting                 -------------------+----------------------------------------- config_file       |/var/lib/pgsql/9.6/data/postgresql.conf data_directory    | /var/lib/pgsql/9.6/data external_pid_file |  hba_file          | /var/lib/pgsql/9.6/data/pg_hba.conf ident_file        | /var/lib/pgsql/9.6/data/pg_ident.conf

 

3、postgresql.conf

3.1、關鍵的設定

postgres=# 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;         name         | context   | unit | setting |boot_val  | reset_val ----------------------+------------+------+---------+-----------+----------- listen_addresses     | postmaster |      | *      | localhost | * max_connections      | postmaster |      | 100    | 100       | 100 shared_buffers       | postmaster | 8kB  | 16384  | 1024      | 16384 effective_cache_size | user       | 8kB | 524288  | 524288    | 524288 maintenance_work_mem | user       | kB  | 65536   | 65536     | 65536 work_mem             | user       | kB  | 4096    | 4096      | 4096(6 rows)

 

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修改參數命令

Alter system set work_mem=8192;

設定重新載入命令

Select pg_reload_conf();

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

定位這種問題最簡單的方法是查看記錄檔，該檔案位於postgresql資料檔案夾的根目錄或者pg_log子檔案夾下。

4、pg_hba.conf

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" isfor Unix domain socket connections onlylocal   all             all                                     peer# IPv4 localconnections:host    all             all             0.0.0.0/0               trust# IPv6 localconnections:host    all             all             ::1/128                 ident# Allow replicationconnections from localhost, by a user with the# replication privilege.#local   replication     postgres                                peer#host    replication     postgres        127.0.0.1/32            ident#host    replication     postgres        ::1/128                 ident

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

1. 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使用發起端的作業系統名進行身分識別驗證

5、設定檔的重新載入

/usr/pgsql-9.6/bin/pg_ctlreload -D /var/lib/pgsql/9.6/data/ systemctlreload postgresql-9.6.service selectpg_reload_conf();

 

本文出自 “corasql” 部落格，請務必保留此出處http://corasql.blog.51cto.com/5908329/1910485

Postgresql學習筆記（二）設定檔