下面几个pgbouncer的参数,我觉得比较有实战意思。
==== syslog ====
Toggles syslog on/off
As for windows environment, eventlog is used instead.
Default: 0
# 设置syslog = 1
# 可以在/var/log/messages里面查看到pgbouncer的日志.
# 否则的话需要设置logfile参数,logfile需要自己去些rotation,否则会不断增长。
==== disable_pqexec ====
Disable Simple Query protocol (PQexec). Unlike Extended Query protocol, Simple Query
allows multiple queries in one packet, which allows some classes of SQL-injection
attacks. Disabling it can improve security. Obviously this means only clients that
exclusively use Extended Query protocol will stay working.
Default: 0
# 设置 disable_pqexec = 1 可以防止SQL注入工具,方法是禁用simple query 协议 。
# 使用pgbench -M simple 来验证.
# pgbench报错Client xxx aborted in state 1: ERROR: PQexec disallowed
# pgbouncer日志
# Aug 26 11:11:37 db-192-168-173-55 pgbouncer[2435]: C-0x15b818f0: digoal/digoal@::ffff:127.0.0.1:40118 Client used 'Q' packet type.
# Aug 26 11:11:37 db-192-168-173-55 pgbouncer[2438]: C-0x8d3eed0: digoal/digoal@::ffff:127.0.0.1:59611 closing because: PQexec disallowed (age=9)
==== ignore_startup_parameters ====
By default, PgBouncer allows only parameters it can keep track of in startup
packets - `client_encoding`, `datestyle`, `timezone` and `standard_conforming_strings`.
All others parameters will raise an error. To allow others parameters, they can be
specified here, so that pgbouncer knows that they are handled by admin and it can ignore them.
Default: empty
# 这个参数是pgbouncer特有用的参数,如果有些客户端连接过来的适合,使用了不被pgbouncer支持的连接参数,那么需要在这里设置忽略。否则应用程序会报错。
==== server_reset_query ====
Query sent to server on connection release, before making it
available to other clients. At that moment no transaction is in
progress so it should not include `ABORT` or `ROLLBACK`.
Good choice for Postgres 8.2 and below is:
server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;
for 8.3 and above its enough to do:
server_reset_query = DISCARD ALL;
When transaction pooling is used, the `server_reset_query` should be empty,
as clients should not use any session features.
Default: DISCARD ALL
# 这个在pool_mode = transaction时,比较有意思,可以选择性的设置server_reset_query = 0
# DISCARD ALL表示丢弃SESSION连接时或者连接之后配置的SESSION级参数,丢失掉之后这些设置将复位。
# 这个也可以通过pgbench和pg_stat_statements来验证。