PGBOUNCER.INI(5) Databases PGBOUNCER.INI(5) (NAME) pgbouncer - pgbouncer (DESCRIPTION) "ini" . "[" "]" . ";" "#" . ";" "#" . (GENERIC SETTINGS) logfile (log file) . (-d) syslog . (log rotation) kill -HUP RELOAD; . . logfile stderr . -q -d . : pidfile PID . pidfile (-d) . : listen_addr ( ) TCP . * " " . . (IPv4/IPv6) . : listen_port . TCP . : 6432 unix_socket_dir . . . @ (abstract namespace) ( ). (-R) . : /tmp ( ) unix_socket_mode . . . : 0777 unix_socket_group . . . : user . PgBouncer (root) . . : pool_mode . session . . transaction . statement . . max_client_conn . (file descriptor) . max_client_conn . : max_client_conn + (max pool_size * total databases * total users) ( ) : max_client_conn + (max pool_size * total databases) . . ulimit . : ulimit . : 100 default_pool_size /. pool_size pool_size . : 20 min_pool_size . . (pool size) . : o [database] user ( ). o . : 0 () reserve_pool_size ( reserve_pool_timeout ). 0 . : 0 () reserve_pool_timeout . 0 . [] : 5.0 max_db_connections ( ). PgBouncer PostgreSQL . [databases] . . ( ) . : 0 () max_db_client_connections PgBouncer ( ). PgBouncer PostgreSQL . max_db_connections . . [databases] . : 0 () max_user_connections ( ). PgBouncer . [users] . . ( ) . : 0 () max_user_client_connections ( ). max_user_connections . max_user_connections max_user_client_connections . [users] . : 0 () server_round_robin PgBouncer LIFO ( ) . . (round-robin) (TCP DNS ) PgBouncer . : 0 track_extra_parameters PgBouncer client_encoding datestyle timezone standard_conforming_strings application_name . PgBouncer . ( default_transaction_read_only, IntervalStyle) : . Postgres . . Postgres Postgres . Citus 12.0+ Postgres search_path . Postgres (startup packet) options . track_extra_parameters . options track_extra_parameters options . : IntervalStyle ignore_startup_parameters PgBouncer : client_encoding datestyle timezone standard_conforming_strings. . PgBouncer . ( options,extra_float_digits) Postgres options . ignore_startup_parameters . options track_extra_parameters options . : peer_id (peer id) PgBouncer PgBouncer . peer_id PgBouncer . 0 PgBouncer . [peers] . peer_id 16383 . : 0 disable_pqexec (Simple Query protocol PQexec). (Extended Query protocol) SQL (SQL-injection) . . . : 0 application_name_add_host application name . . . application_name SET PgBouncer . : 0 conffile . PgBouncer RELOAD SIGHUP . : service_name win32. : pgbouncer job_name service_name. stats_period SHOW ( log_stats ). [] : 60 max_prepared_statements PgBouncer (prepared statements) (transaction) (statement) . PgBouncer . PgBouncer PGBOUNCER_{unique_id} . ( ) . PgBouncer PostgreSQL ( ) . PgBouncer . ( my_prepared_statement PGBOUNCER_123) . ( ) PgBouncer . : SQL PREPARE EXECUTE DEALLOCATE Postgres . DEALLOCATE ALL DISCARD ALL PgBouncer . LRU . 0 . . PgBouncer PostgreSQL . PgBouncer . PgBouncer : - . - . pkt_buf . pkt_buf . : - - - - pkt_buf 4096 ( ) PgBouncer : 200 x 5kB + 1000 x 4 x 4kB = ~17MB . CPU PgBouncer . PgBouncer so_reuseport . . PostgreSQL . PgBouncer . pool_size 20 PostgreSQL ( ) . . PostgreSQL : ERROR: cached plan must not change result type . DDL . RECONNECT PgBouncer . : 200 scram_iterations SCRAM-SHA-256 . (brute-force) . : 4096 (AUTHENTICATION SETTINGS) PgBouncer . . auth_type . cert TLS . CommonName . md5 MD5. . auth_file MD5 . md5 SCRAM SCRAM . scram-sha-256 SCRAM-SHA-256. auth_file SCRAM . plain . . trust . auth_file . any trust . . . hba auth_hba_file . : peer TCP TLS . ldap LDAP PostgreSQL ( ). LDAP auth_ldap_options auth_hba_file . pam PAM auth_file . auth_user . PAM "pgbouncer" . pam HBA . auth_hba_file HBA auth_type hba . HBA . : auth_ident_file (identity map) auth_type hba . Ident . : auth_file . . ( ) auth_file auth_user . : auth_user auth_user auth_file auth_query pg_authid auth_user . auth_user auth_file . ( auth_user auth_file .) pg_authid (admin) . SECURITY DEFINER . : auth_query . pg_authid . SECURITY DEFINER . . . : SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END FROM pg_authid WHERE rolname=$1 AND rolcanlogin auth_dbname [database] . . auth_ldap_options LDAP auth_type ldap . ( auth_hba_file .) : auth_ldap_options = ldapurl="ldap://127.0.0.1:12345/dc=example,dc=net?uid?sub" (LOG SETTINGS) syslog syslog. event log . : 0 syslog_ident syslog . : pgbouncer ( ) syslog_facility (facility) syslog. : auth authpriv daemon user local0-7. : daemon log_connections . : 1 log_disconnections . : 1 log_pooler_errors . : 1 log_stats stats_period. SHOW . : 1 verbose (verbosity). "-v" . "-v -v" verbose=2 . 3 . : 0 (CONSOLE ACCESS CONTROL) admin_users . auth_type any . : stats_users . SHOW SHOW FDS . : (CONNECTION SANITY CHECKS, TIMEOUTS) server_reset_query . ABORT ROLLBACK . . DISCARD ALL . DEALLOCATE ALL . (transaction pooling) server_reset_query . : DISCARD ALL server_reset_query_always server_reset_query . () server_reset_query (session pooling) . . PgBouncer . : . : 0 server_check_delay server_check_query . 0 . : 30.0 server_check_query . . . : server_fast_close "close_needed" ( RECONNECT RELOAD DNS) . . . . . . . : 0 server_lifetime ( ) . 0 . [] [databases] . : 3600.0 server_idle_timeout . 0 . [] : 600.0 server_connect_timeout . [] : 15.0 server_login_retry . . [] . . PAUSE . : 15.0 client_login_timeout . SUSPEND . [] : 60.0 autodb_idle_timeout ( "*") . . [] : 3600.0 dns_max_ttl DNS . TTL DNS . [] : 15.0 dns_nxdomain_ttl DNS NXDOMAIN DNS . [] : 15.0 dns_zone_check_period (zone serial) . PgBouncer DNS ( ) . . IP . c-ares ( --with-cares configure) . : 0.0 () resolv_conf resolv.conf. DNS . evdns ( 2.0.3 ) c-ares ( 1.15.0 ) . DNS PgBouncer . : ( ) query_wait_notify PgBouncer . [] 0 . : 5 TLS (TLS SETTINGS) (cert) (key) RELOAD . . RELOAD RECONNECT . TLS RECONNECT . client_tls_sslmode TLS . TLS . client_tls_key_file client_tls_cert_file PgBouncer . PgBouncer PEM . disable TCP . TLS . . allow TLS . TCP . . prefer allow. require TLS . . . verify-ca TLS . verify-full verify-ca. client_tls_key_file PgBouncer . : client_tls_cert_file . . : client_tls_ca_file (Root CA) . : client_tls_protocols TLS . : tlsv1.0 tlsv1.1 tlsv1.2 tlsv1.3. : all (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3) secure (tlsv1.2,tlsv1.3). : secure client_tls_ciphers TLS OpenSSL. : o default/secure/fast/normal ( OpenSSL ) o all ( ) TLS 1.2 . 1.3 client_tls13_ciphers . : default client_tls13_ciphers TLS 1.3. client_tls_ciphers . : o TLS_AES_256_GCM_SHA384 o TLS_CHACHA20_POLY1305_SHA256 o TLS_AES_128_GCM_SHA256 o TLS_AES_128_CCM_8_SHA256 o TLS_AES_128_CCM_SHA256 TLS 1.3 . 1.2 client_tls_ciphers . : client_tls_ecdhcurve ECDH. : none ( DH) auto (ECDH ) : auto client_tls_dheparams DHE. : none ( DH) auto (DH ) legacy (DH ) : auto server_tls_sslmode TLS PostgreSQL. prefer . disable TCP . TLS . allow TLS . prefer TLS PostgreSQL . TCP . . . require TLS . TCP . . verify-ca TLS server_tls_ca_file . . verify-full TLS server_tls_ca_file . . server_tls_ca_file PostgreSQL. : server_tls_key_file PgBouncer PostgreSQL. : server_tls_cert_file . PostgreSQL . : server_tls_protocols TLS . : tlsv1.0 tlsv1.1 tlsv1.2 tlsv1.3. : all (tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3) secure (tlsv1.2,tlsv1.3) legacy (). : secure server_tls_ciphers TLS OpenSSL. : o default/secure/fast/normal ( OpenSSL ) o all ( ) TLS 1.2 . 1.3 server_tls13_ciphers . : default server_tls13_ciphers TLS 1.3. server_tls_ciphers . : o TLS_AES_256_GCM_SHA384 o TLS_CHACHA20_POLY1305_SHA256 o TLS_AES_128_GCM_SHA256 o TLS_AES_128_CCM_8_SHA256 o TLS_AES_128_CCM_SHA256 TLS 1.3 . 1.2 client_tls_ciphers . : (DANGEROUS TIMEOUTS) . query_timeout . statement_timeout . [] : 0.0 () query_wait_timeout . . 0 . . [] . . : 120.0 cancel_wait_timeout . . 0 . . [] . : 10.0 client_idle_timeout . . [] : 0.0 () idle_transaction_timeout "idle in transaction" ( ) . [] : 0.0 () transaction_timeout "in transaction" ( ) . [] : 0.0 () suspend_timeout SUSPEND (-R). . [] : 10 (LOW-LEVEL NETWORK SETTINGS) pkt_buf . TCP . libpq . : 4096 max_packet_size PostgreSQL PgBouncer . . . : 2147483647 listen_backlog backlog listen(2). . . : 128 sbuf_loopcnt . PgBouncer . pkt_buf . 0 . : 5 so_reuseport SO_REUSEPORT TCP . PgBouncer . PgBouncer (CPU) . (PgBouncer CPU .) . ( ) DragonFlyBSD FreeBSD . ( FreeBSD SO_REUSEPORT_LB .) : . setsockopt() . . PgBouncer unix_socket_dir pidfile logfile . PgBouncer TCP/IP . PgBouncer PgBouncer . peer_id peers . so_reuseport . : 0 tcp_defer_accept TCP_DEFER_ACCEPT man 7 tcp . ( : 1 . .) . : 1 0 tcp_socket_buffer : tcp_keepalive keepalive . tcp_keepidle=7200 tcp_keepintvl=75 tcp_keepcnt=9. . : 1 tcp_keepcnt : tcp_keepidle : tcp_keepintvl : tcp_user_timeout TCP_USER_TIMEOUT . TCP . 0 . . : 0 [DATABASES] (SECTION [DATABASES]) [databases] PgBouncer . key=value : dbname = connection string key=value ( libpq libpq ). : foodb = host=host1.example.com port=5432 bardb = host=localhost dbname=bazdb _0-9A-Za-z . SQL : "" . "pgbouncer" . "*" : . ( ) * = host=foo PgBouncer "bar" bar = host=foo dbname=bar ( dbname ). autodb_idle_timeout . dbname . : host IP . (resolve) dns_max_ttl . ( ) . DNS . / . @ . . . ( DNS . .) : . ( libpq .) . server_round_robin . : host=localhost host=127.0.0.1 host=2001:0db8:85a3:0000:0000:8a2e:0370:7334 host=/var/run/postgresql host=192.168.0.1,192.168.0.2,192.168.0.3 : port : 5432 user user= . PgBouncer . password auth_file . auth_query . auth_user auth_user . auth_query auth_query . SQL (single quote) . auth_dbname auth_dbname . pool_size . default_pool_size . min_pool_size . min_pool_size . : o [database] user ( ). o . reserve_pool_size . reserve_pool_size . reserve_pool . connect_query . . pool_mode . pool_mode . load_balance_hosts host load_balance_hosts . : DNS IP . . round-robin . disable . server_login_retry . : round-robin max_db_connections ( ). max_db_client_connections . max_client_conn PgBouncer . server_lifetime server_lifetime . server_lifetime . client_encoding client_encoding . datestyle datestyle . timezone timezone . [USERS] (SECTION [USERS]) key=value : user1 = settings key=value . : user1 = pool_mode=session . auth_file auth_file PgBouncer auth_user auth_query . auth_user PgBouncer "no such user" . pool_size . default_pool_size . reserve_pool_size . reserve_pool_size . pool_mode . pool_mode . max_user_connections ( ). query_timeout . query_timeout . idle_transaction_timeout . idle_transaction_timeout . transaction_timeout . transaction_timeout . client_idle_timeout PgBouncer . client_idle_timeout . . max_user_client_connections . max_client_conn . [PEERS] (SECTION [PEERS]) [peers] PgBouncer . PgBouncer peer_id [peers] PgBouncer . PgBouncer . PgBouncer ( ) TCP (TCP load balancer) . TCP TCP . . . key=value : peer_id = connection string peer_id key=value ( libpq libpq ). : 1 = host=host1.example.com 2 = host=/tmp/pgbouncer-2 port=5555 : peer_id PgBouncer . [peers] . . [peers] peer_id PgBouncer . [peers] . : v1.21.0 . v1.21.0 . host IP . dns_max_ttl . DNS . IP ( ). / . @ . : host=localhost host=127.0.0.1 host=2001:0db8:85a3:0000:0000:8a2e:0370:7334 host=/var/run/pgbouncer-1 port : 6432 pool_size . (bursts) Postgres . pool_size . default_pool_size . INCLUDE (INCLUDE DIRECTIVE) PgBouncer include . . include : %include filename . (AUTHENTICATION FILE FORMAT) auth_file . : "username1" "password" ... "username2" "md5abcdef012342345" ... "username2" "SCRAM-SHA-256$:$:" . MD5 SCRAM . PgBouncer . . MD5 PostgreSQL: "md5" + md5(password + username) admin 1234 MD5 md545f2603610af569b6155c45067268c6b . SCRAM PostgreSQL: SCRAM-SHA-256$:$: PostgreSQL RFC 5803 . . . ( ) . (Limitations) MD5 SCRAM ( ). MD5 MD5 ( MD5 ). SCRAM SCRAM PgBouncer SCRAM PgBouncer PostgreSQL ( (salt) ). SCRAM : SCRAM . . pg_authid ./etc/mkauth.py . auth_query auth_file . (Note on managed servers) SCRAM PgBouncer ) ) SCRAM . ( AWS RDS) PostgreSQL . ( rds_superuser) select * from pg_authid; ERROR: permission denied for table pg_authid. . ( ). SCRAM PgBouncer SCRAM . SCRAM : SCRAM . psql --echo-hidden \password SCRAM : $ psql --echo-hidden postgres=# \password Enter new password for user "": Enter it again: ********* QUERY ********** ALTER USER PASSWORD 'SCRAM-SHA-256$:$:' ************************** SCRAM QUERY userlist.txt PgBouncer . psql --echo-hidden SCRAM ( alter role password '' ). HBA (HBA FILE FORMAT) HBA auth_hba_file . auth_type hba . pg_hba.conf PostgreSQL ( ). o : local host hostssl hostnossl. o : all replication sameuser @file . : samerole samegroup. o : all @file . : +groupname. o : all IPv4 IPv6 . : samehost samenet DNS . o (Auth-method): auth_type PgBouncer peer reject any pam . o (map=) auth_type cert peer . IDENT (IDENT MAP FILE FORMAT) ident auth_ident_file . auth_type hba . ident PostgreSQL ( ). o : map-name system-username database-username. o / . o (System-username): (regular expressions) . o (Database-username): all Postgres . : +groupname . (EXAMPLES) : [databases] template1 = host=localhost dbname=template1 auth_user=someuser [pgbouncer] pool_mode = session listen_port = 6432 listen_addr = localhost auth_type = md5 auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = someuser stats_users = stat_collector : [databases] ; foodb foodb = ; bardb bazdb localhost bardb = host=localhost dbname=bazdb ; forcedb = host=localhost port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO auth_query: CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text) RETURNS record AS $$ BEGIN SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END FROM pg_authid WHERE rolname=i_username AND rolcanlogin INTO uname, phash; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER -- Set a secure search_path: trusted schema(s), then 'pg_temp'. SET search_path = pg_catalog, pg_temp; REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer; GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer; PgBouncer PgBouncer so_reuseport. : [databases] postgres = host=localhost dbname=postgres [peers] 1 = host=/tmp/pgbouncer1 2 = host=/tmp/pgbouncer2 [pgbouncer] listen_addr=127.0.0.1 auth_file=auth_file.conf so_reuseport=1 unix_socket_dir=/tmp/pgbouncer1 peer_id=1 : [databases] postgres = host=localhost dbname=postgres [peers] 1 = host=/tmp/pgbouncer1 2 = host=/tmp/pgbouncer2 [pgbouncer] listen_addr=127.0.0.1 auth_file=auth_file.conf so_reuseport=1 ; unix_socket_dir peer_id unix_socket_dir=/tmp/pgbouncer2 peer_id=2 (SEE ALSO) pgbouncer(1) - 1.25.2 PGBOUNCER.INI(5)