一、配置文件

postgresql.conf
該文件包含一些通用設(shè)置，比如內(nèi)存分配、新建database的默認(rèn)存儲(chǔ)位置、PostgresSQL服務(wù)器的IP地址，日志的位置以及許多其他設(shè)置。
pg_hba.conf
該文件用于控制PostgreSQL服務(wù)器的訪問權(quán)限，具體包括：允許哪些用戶連接到哪個(gè)數(shù)據(jù)庫，允許哪些IP連接到本服務(wù)器，以及指定連接時(shí)使用的身份驗(yàn)證模式。
pg_ident.conf
如果該文件存在，則系統(tǒng)會(huì)基于文件內(nèi)容將當(dāng)前登錄的操作系統(tǒng)用戶映射為一個(gè)PostgreSQL數(shù)據(jù)庫內(nèi)部用戶的身份來登錄。有些人會(huì)把操作系統(tǒng)的root用戶映射為PostgreSQL的postgres超級(jí)用戶賬號(hào)。
查找上述配置文件的具體位置，以超級(jí)用戶的身份鏈接到任何一個(gè)數(shù)據(jù)庫上并執(zhí)行如下查詢語句即可找到。

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

圖1-1.png

1.1讓配置文件生效

有些配置項(xiàng)修改后需要重啟PostgreSQL服務(wù)實(shí)例才能生效，重啟時(shí)會(huì)關(guān)閉所有客戶端連接。有的配置項(xiàng)只需重新加載一下配置文件即可生效，伺候連接上來的新用戶都會(huì)自動(dòng)讀取到新的配置。重新加載配置文件時(shí)，原來已連接的用戶會(huì)話不會(huì)受到影響。如果你不確定修改了某個(gè)配置后是否需要重啟，請(qǐng)查看下該配置項(xiàng)的context屬性，如果是postmaster，那么需要重啟；如果是user，那么重新加載配置文件即可。

1. 重新加載配置文件

pg_ctl reload -D 你的數(shù)據(jù)目錄

如果再redhat linux、centos或者ubuntu等操作系統(tǒng)中以服務(wù)的形式安裝了PostgreSQL,那么可以使用以下命令：

service postgresql-9.5 reload

命令中的postgresql-9.5是你的服務(wù)名。
還有一種加載配置文件的的方法是以超級(jí)用戶身份登錄到任何數(shù)據(jù)庫并執(zhí)行一下SQL：

SELECT pg_reload_conf();

2. 重啟PostgreSQL運(yùn)行實(shí)例
   一些底層的配置修改后必須重啟PostgreSQL服務(wù)實(shí)例才能生效。PostgreSQL運(yùn)行實(shí)例無法通過執(zhí)行PostgreSQL本身的命令行來實(shí)現(xiàn)重啟，只能通過操作系統(tǒng)的命令來實(shí)現(xiàn)。在linux/Unix系統(tǒng)上，如果PostgreSQL實(shí)例是以服務(wù)形式運(yùn)行的，執(zhí)行如下命令：

service postgresql-9.6 restart

如果不是以服務(wù)形式運(yùn)行，請(qǐng)執(zhí)行：

pg_ctl restart -D 你的數(shù)據(jù)目錄

1.2 postgresql.conf

postgreSQL9.4引入一個(gè)重要的變更，該版本引入一個(gè)新的名為postgresql.auto.conf的配置文件，其中的配置項(xiàng)會(huì)覆蓋postgresql.conf的同名配置項(xiàng)。建議：不要直接修改postgresql.conf,而是優(yōu)先修改postgresql.auto.conf。

1. 查看postgresql.conf中的配置
   通過查詢pg_settings視圖即可查看所有配置項(xiàng)內(nèi)容，無須打開配置文件。

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

圖1-2.png

(1) context字段代表配置項(xiàng)的作用范圍。
context值為usr表示是用戶級(jí)配置項(xiàng)，它可以被每個(gè)用戶單獨(dú)修改，也就是說該配置項(xiàng)針對(duì)每個(gè)用戶都可以是不同的值，用戶修改后會(huì)在自己的所有會(huì)話中生效。如果是一個(gè)超級(jí)用戶修改了一個(gè)usr級(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)（postmaster就代表了PostgreSQL服務(wù)實(shí)例），更改后需要重啟PostgreSQL服務(wù)才能生效。
(2)setting指當(dāng)前設(shè)置，boot_val是指默認(rèn)設(shè)置，reset_val是指重新啟動(dòng)服務(wù)器或重新加載設(shè)置之后的新設(shè)置。修改了設(shè)置后，一定要記得查看一下seting和rest_val并確保二者是一致的，否則說明設(shè)置并未生效，需要重新啟動(dòng)服務(wù)器或許重新加載設(shè)置。
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字段為false。

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;

image.png

2. 修改postgresql.conf中的配置項(xiàng)的值
   使用ALTER SYSTEM_SQL命令。例如：

ALTER SYSTEM SET work_mem = '500MB';

該命令不會(huì)直接修改postgresql.conf文件本身，而是會(huì)去修改postgresql.auto.conf。
如果需要時(shí)常修改很多配置項(xiàng)，可以嘗試分門別類的存放到多個(gè)配置文件中，然后通過postgresql.conf中使用include或者include_if_exists前綴來引入這些配置文件。

include '配置文件名'

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

1.3 pg_hba.conf

pg_hba.conf文件指定了哪些IP地址和哪些用戶可以連接到PostgreSQL數(shù)據(jù)庫，同時(shí)還規(guī)定了用戶必須使用何種身份驗(yàn)證方式登錄。針對(duì)該文件的修改可動(dòng)態(tài)生效，執(zhí)行一次配置重加載即可。

• 身份驗(yàn)證模式。一般有以下幾種常用選項(xiàng)：ident、trust、md5、peer以及password
• 第二項(xiàng)配置IPV6
• 第三項(xiàng)配置IPV4
• 第四項(xiàng)是ssl
• 第五項(xiàng)是允許與本節(jié)點(diǎn)構(gòu)成復(fù)制關(guān)系的其他PostgreSQL服務(wù)器節(jié)點(diǎn)的IP網(wǎng)段。

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

trust
用戶無需提供密碼就可以連接到數(shù)據(jù)庫。只要源端IP地址、連接用戶名、要訪問的database名正確，用戶就可以連接上。
md5
該模式很常用，要求連接發(fā)起者攜帶用md5算法加密的密碼。
password
該模式要求連接發(fā)起者攜帶明文密碼進(jìn)行身份驗(yàn)證。
ident
系統(tǒng)會(huì)將請(qǐng)求發(fā)起者的操作系統(tǒng)用戶映射為PostgreSQL數(shù)據(jù)庫內(nèi)部用戶，并以該內(nèi)部用戶的權(quán)限登錄，且無需提供密碼。windows不支持。
peer
該模式下系統(tǒng)會(huì)直接從操作系統(tǒng)內(nèi)核獲取當(dāng)前發(fā)起者的操作系統(tǒng)用戶名，如果與其請(qǐng)求連接的PostgreSQL用戶名一致，即可連接成功。
cert
該模式要求客戶端必須使用SSL方式進(jìn)行連接。

二、連接管理

2.1. 有些時(shí)候我們會(huì)需要?dú)⒌粽谶\(yùn)行的操作，要想終止正在執(zhí)行的語句并殺掉連接，請(qǐng)使用以下步驟。
(1). 查詢出活動(dòng)連接列表及其進(jìn)程ID。

SELECT * FROM pg_stat_activity;

pg_stat_activity視圖包含每個(gè)連接上最近一次執(zhí)行的語句、使用的用戶名(usename字段)、所在的database名（datname字段）以及語句開始執(zhí)行的時(shí)間。通過查詢?cè)撘晥D可以找到需要終止的會(huì)話所對(duì)應(yīng)的進(jìn)程ID。
(2). 取消連接（假設(shè)對(duì)應(yīng)的進(jìn)程號(hào)是1234）上的活動(dòng)查詢。

SELECT pg_cancel_backend(1234);

該操作不會(huì)終止連接本身。
(3).終止該連接。

SELECT pg_terminate_backend(1234);

如果你未停止某個(gè)連接上正在執(zhí)行的語句就直接終止該連接，那么語句也會(huì)被停止掉。
PostgreSQL支持在SELECT查詢語句中調(diào)用函數(shù)。例如：如果你希望一次性終止某個(gè)用戶的所有連接，那么可以執(zhí)行以下語句。

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE usename = 'some_role';

2.2 查看被阻塞語句的情況
pg_stat_activity視圖中有關(guān)于等待鎖的語句信息。
wait_even_type 和 wait_even字段記錄看當(dāng)前會(huì)話上的語句在等待什么資源。使用wait_event IS NOT NULL 過濾條件來查找被阻塞的語句。

三、角色

PostgreSQL中使用角色(role)機(jī)制來處理用戶身份認(rèn)證。擁有登錄數(shù)據(jù)庫權(quán)限的角色稱為可登錄角色。
3.1創(chuàng)建可登錄角色
在PostgreSQL安裝過程中數(shù)據(jù)初始化階段，系統(tǒng)會(huì)默認(rèn)創(chuàng)建一個(gè)名為postgres的可登錄角色(同時(shí)會(huì)創(chuàng)建一個(gè)名為postgres的同名database)?？梢杂眠^前面介紹的ident和peer身份驗(yàn)證機(jī)制來將操作系統(tǒng)的root用戶映射到數(shù)據(jù)庫的postgres角色，這樣可以實(shí)現(xiàn)root用戶免密登錄。以postgres角色登錄，然后創(chuàng)建其他已經(jīng)規(guī)劃好的角色。
(1). 創(chuàng)建具備登錄權(quán)限的角色。

CREATE ROLE leo LOGIN PASSWORD 'king' VALID UNTIL 'infinity' CREATEDB;

VALID 字句是可選的，其功能是為此角色的權(quán)限設(shè)定有效期，如果不寫則該角色永久有效。CREATEDB 子句表明為此角色賦予了創(chuàng)建新數(shù)據(jù)庫的權(quán)限。
(2). 創(chuàng)建一個(gè)具備超級(jí)用戶權(quán)限的角色

CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2020-1-1 00:00' SUPERUSR;

要想創(chuàng)建一個(gè)超級(jí)用戶，創(chuàng)建者自身也必須是一個(gè)超級(jí)用戶。