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

第35講：中間件PgBouncer部署

第35講：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客服。