postgresql從入門到精通 - 第35講：中介軟體PgBouncer部署|PostgreSQL教程

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第35講： 中介軟體PgBouncer部署

11月25日(週六)19:30-20:30，往期公開課的文件及影片，聯絡CUUG

內容1 : PgBouncer 簡介

內容2 : PgBouncer 安裝

內容3 : PgBouncer 啟動與關閉

內容4 : 透過PgBouncer 連線到資料庫

PgBouncer簡介

· PgBouncer 是 PostgreSQL的輕量的連線池，支援三種模式

->Session pooling/會話連線池

最禮貌的方法。在客戶端連線的時候，在它的連線生命期內，會給它賦予一個伺服器連線。在客戶端斷開的時候，伺服器連線會放回到連線池中。

->Transaction pooling/事務連線池

伺服器連線只有在一個事務裡的時候才賦予客戶端。在 PgBouncer 注意到事務結束的時候，伺服器將會放回連線池中。這是一個 hack，因為它打破了應用對後端連線的看法。只有在應用配合這樣的使用模式，沒有使用會破壞這種使用模式的時候才能用這個連線方式。

->Statement pooling/語句連線池  最激進的模式。這是事務連線池的一個扭曲的變種 - 不允許多語句的事務。這就意味著是在客戶端強制 “autocomit”模式，主要是給 PL/Proxy 用的。

· PgBouncer 特點

記憶體需求低(預設的時候每個連線 2k)。

它不是和單個後端伺服器繫結的，目標資料庫可以位於不同的主機上。

對大多數設定，都支援線上的重新配置，無需重啟。

支援線上的重啟/升級，而不會退出客戶端的連線。

應用程式如果直接與PostgreSQL連線,每次連線pg都會克隆出一個服務程式來為應用程式提供服務，關閉連線後pg會自動將服務程式給停掉.頻繁的建立和銷燬程式，會消耗比較多的系統資源，pgbouncer會將與pg的連線快取住,當有請求進來,只是分配一個空閒的連線,這樣降低了系統資源的消耗。

使用pgbouncer可以控制住資料庫上的活動連線,一般配置為CPU核數的1.5-2.5倍之間。

pgbouncer使用libevent進行socket通訊,效率很高。

PgBouncer部署

· Pgbouncer 安裝

1、安裝libevent(root使用者)：

/soft/libevent-2.1.12-stable

./configure --prefix=/usr/local/libevent

make

makeinstall

2、安裝pgbouncer (postgres使用者)

export PKG_CONFIG_PATH=/usr/local/libevent/lib/pkgconfig

cd /soft/pgbouncer-1.17.0

./configure --prefix=/usr/local/pgbouncer

make && make install

· Pgbouncer 配置

1、建立/usr/local/pgbouncer/conf目錄，把pgbouncer.ini檔案放在此目錄下

2、複製模板檔案

cp /usr/local/pgbouncer/share/doc/pgbouncer/pgbouncer.ini /usr/local/pgbouncer/conf/pgbouncer.ini

3、編輯pgbouncer.ini檔案

logfile = /var/log/pgbouncer/pgbouncer.log #目錄需要root使用者建立，然後授權

pidfile = /var/run/pgbouncer/pgbouncer.pid #目錄需要root使用者建立，然後授權

listen_addr = *

listen_port = 6432

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

stats_users = hr

編輯/etc/pgbouncer/userlist.txt檔案

該檔案包含將來透過中介軟體連線到資料庫的使用者名稱和密碼，登入時需要用它來驗證，如果修改，需要重啟pgbouncer生效。

1)、先查詢密碼：

select usename, passwd from pg_shadow order by 1;

2)、編輯userlist.txt

"hr" "md5e661e439b4757f4595b811b4a196182d"

"postgres" "md53175bce1d3201d16594cebf9d7eb3f9d“

"c1" "md574fc5f6f852574d24cbca8153258784c"

Pgbouncer 啟動與關閉

· 啟動pgbouncer

/usr/local/pgbouncer/bin/pgbouncer -d /usr/local/pgbouncer/conf/pgbouncer.ini

· 關閉pgbouncer

目前pgbouncer還沒有自主停止的指令碼或者命令，只能透過kill命令來停止。格式是：

cat /var/run/pgbouncer/pgbouncer.pid | xargs kill -9

· 重啟pgbouncer

/usr/local/pgbouncer/bin/pgbouncer -R -d /usr/local/pgbouncer/conf/pgbouncer.ini

引數：

-d ：後臺執行

-R ：重啟程式

Pgbouncer 連線

· 登入pgbouncer

pgbouncer對外提供了一個虛擬資料庫pgbouncer，之所以成為虛擬資料庫，是因為它可以提供像PostgreSQL那樣的資料庫操作介面，但是這個資料庫卻並不是真實存在的。而是pgbouncer虛擬出來的一個命令列介面。

$ psql -p 6432 -d pgbouncer -U postgres

幫助命令，用show命令檢視當前的配置

Show help;

pgbouncer.ini配置檔案詳解

· 配置主要分為兩部分：

第一部分是[databases]區域，是用來配置資料庫連線相關資訊的。

第二部分是[pgbouncer]，是pgbouncer自身的配置。

· [databases]部分的配置

它的基本格式是：

對外提供的資料庫名 = host=主機IP port=埠 user=使用者 password=密碼

其他的規則都類似，資料庫名後面的等號旁邊要有空格隔開，後面每個成對的數值之間用空格隔開。這裡面的主機和埠指的是PostgreSQL監聽的地址和埠，而使用者和密碼就是用來連線PostgreSQL資料庫的使用者名稱和密碼。

testdb = host=127.0.0.1 port=1922 user=hr password=hr connect_query='SELECT 1 '

pool_size 用來配置連線池的大小。如果沒有這個值則使用[pgbouncer]部分的default_pool_size的值。

connect_query ：後面跟一個SQL語句字串，用於探測這個連線是否正常，如果執行SQL語句出錯，則換一個連線。

client_encoding：指定連線的客戶端使用的字符集編碼

datestyle：指定日期型別

timezone：指定時區。

· 使用中介軟體來連線到testdb資料庫

$ psql -p 6432 -d testdb -U hr

-p ：指定pgbouncer監聽的埠

-d ：指定在配置檔案中宣告的資料庫，如果要切換到其它資料庫，則需要在配置檔案中宣告。

· [pgbouncer]部分的配置

logfile：指定日誌檔案，預設值是/var/log/pgbouncer/pgbouncer.log

pidfile：指定pid檔案位置，預設值是/var/run/pgbouncer/pgbouncer.pid

listen_addr：監聽的地址，預設值是127.0.0.1，可以使用*號表示監聽所有IP地址。

listen_port：監聽的埠，預設值是6432

unix_socket_dir：指定unix socket檔案的目錄，預設為/tmp目錄

unix_socket_mode：指定unix socket檔案的許可權，預設值為0777

unix_socket_group：指定unix socket檔案的組，預設無

user：指定啟動PgBouncer的使用者名稱，windows系統不支援此設定

auth_type：認證的型別，預設是trust，其他值包括md5，crypt，plain，any。用得較多的是md5

auth_file：認證檔案的位置，預設值是/etc/pgbouncer/userlist.txt

pool_mode：指定池的模式，預設是session模式，還可以是transaction和statement

max_client_conn：允許的最大連線數

default_pool_size：預設的池大小，預設值20

min_pool_size：最小的池大小，每個連線池至少會向後端資料庫保持多少個連線

reserve_pool_size：連線池的保留連線數

reserve_pool_timeout：保留連線的超時時間

· 日誌配置項：

syslog：是否開啟syslog，windows下開啟eventlog，預設值為0，表示不開啟。

syslog_ident：預設為PgBouncer

syslog_facility

log_connections：是否記錄連線成功的日誌，預設值為1，表示記錄

log_disconnection：是否記錄斷開連線的日誌，預設值為1，表示記錄

log_pooler_errors：連線池法網客戶端的錯誤是否記錄在日誌中，預設值為1，表示記錄

stats_period：把彙總的統計資訊寫入日誌的時間週期，預設是60s

· 控制介面訪問控制配置項：

admin_users：管理使用者名稱，預設值是postgres

stats_users：允許連線到控制介面，檢視連線池只讀資訊的使用者列表。可以執行除“SHOW FDS”以外的其他“SHOW”命令

server_reset_query

server_check_delay：空閒的連線多長時間進行一次健康檢測，判斷連線是否可用。如果設定為0，則立即檢測，預設值為30s

server_check_query：進行健康檢查的SQL語句，如果為0，表示不檢測，預設值為“select 1;”

server_lifetime：連線的存活時間，連線超過這個時間就會被關閉，預設為3600，設定為0表示只使用一次。

server_idle_timeout：連線的idle時間，超過此時間，連線會被關閉。預設為600

server_connect_timeout：後端資料庫的login時間超過這個值就會被關閉。預設為15s

server_login_retry：傳送到後端資料庫的連線失敗後，等多長時間後重試，預設為15s

client_login_timeout：客戶端與PgBouncer建立連線後，如果無法在這段時間內完成登入，那麼連線會斷開，預設為60s

· 危險超時配置項：

指的是為防止一些未知錯誤或者原因導致系統卡住的針對性配置。

query_timeout：允許超過該時間值的SQL會被斷開，應該比SQL實際的執行時間稍長，也需要比資料庫的statement_timeout引數值更大。為了應付一些未知的網路問題。預設為0.0，禁止使用

query_wait_timeout：請求在佇列中等待被執行的最長時間，如果超過該時間還沒有分配到連線，就會斷開。預設為0，禁止使用。

client_idle_timeout：客戶端連線空閒，超過該時間，則斷開連線。預設值為0，禁止使用

idle_transaction_timeout：客戶端啟動事務後，超過這個時間沒有結束事務，則關閉這個客戶端連線。預設值為0，禁止使用。

· 底層網路連線配置：

pkt_buf：用於網路包的內部緩衝區大小，會影響發出的TCP包的大小即記憶體的使用，預設值為2048，一般保持預設值。

max_packet_size：透過PgBouncer最大的包大小，包可以是一個SQL，也可以是返回的結果，預設值是2147483647。

listen_backlog：TCP監聽函式listen的Backlog引數，預設值為128。

sbuf_loopcnt：處理過程中，每個連線處理多少資料就切換到下一個連線。預設為5，如果設定為0，表示不限制。不限制時，一個連線傳送大量資料，另外的連線可能就會空閒，導致被結束掉。

tcp_defer_accept：linux下，預設為45，其他平臺為0。tcp_socket_buffer 預設沒有設定。

tcp_keepalive：是否以作業系統的預設值開啟基本的keepalive 設定，在linux下，作業系統的keepalive裡，預設值是tcp_keepidle=7200, tcp_keepintvl-75,tcp_keepcnt=9，其他作業系統型別，預設值為1

tcp_keepcnt：預設未設定。

tcp_keepidle：預設未設定。

tcp_keepintvl：預設未設定。

CUUG PostgreSQL技術大講堂系列公開課，往期影片及文件，請聯絡CUUG客服。