- simplest config file /etc/pgbouncer/pgbouncer:
[databases] * = host=127.0.0.1 port=5432 ;;user binding like this - add all users it is safer postgres = host=localhost dbname=postgres [pgbouncer] logfile = /var/log/postgresql/pgbouncer.log pidfile = /var/run/postgresql/pgbouncer.pid listen_addr = * listen_port = 6432 unix_socket_dir = /var/run/postgresql auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt admin_users = postgres, .... stats_users = postgres, .... pool_mode = transaction max_client_conn = 1000 ;see bellow JDBC error: ignore_startup_parameters = extra_float_digits default_pool_size = 20 ;;must be lower than max_connections
- you must add all users allowed to connect via pgbouncer into /etc/pgbouncer/userlist.txt file in form “username” “password” (both with ” quotes and every user on separate line)
- restart service after all changes – it is generally better to use 2 steps:
sudo service pgbouncer stop sudo service pgbouncer start
- check if you can connect – easiest way is using psql command line tool with proper -h hostip -p port
- if you can connect to postgresql (usually on 5432 port) but you cannot connect into pgbouncer (usually on 6432 port) try following:
- check if user is properly added in /etc/pgbouncer/userlist.txt file
- if pgbouncer runs on GCE instance check firewall rules/ labels on instance – if proper port is opened
- check if there is some firewall running on instance – ufw, iptables
- test iptables firewall rules using “sudo iptables -L”
- check if pgbouncer really listens on proper port:
sudo netstat -l
you should see something like this:
Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 *:postgresql *:* LISTEN tcp 0 0 *:6432 *:* LISTEN tcp6 0 0 [::]:postgresql [::]:* LISTEN tcp6 0 0 [::]:6432 [::]:* LISTEN
this is OK, but if you would see:
Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 localhost:6432 *:* LISTEN
then it means pgbouncer listens still only on localhost
- sometimes it is necessary to restart pgbouncer twice – so try it…
- to check pgbouncer log into special database pgbouncer on port pgbouncer is listening:
psql -U username -d pgbouncer -p 6432
- use “SHOW HELP;” to see commands – see on https://pgbouncer.github.io/usage.html
- use “RELOAD;” to refresh configuration if you made changes in pgbouncer config file
- Sometimes you can get error from a new JDBC connection – “unsupported startup parameter: extra_float_digits” (see here – https://postgresinfo.wordpress.com/category/postgresql-connection-pooling/ )
- if it happens you have to uncomment line “ignore_startup_parameters = extra_float_digits” in pgbouncer.ini file
- Warning – if you use some specific (not default) tcp_keepalive settings on database you will have to set it on pgbouncer too. See – tcp_keepalive=1 + tcp_keepcnt, tcp_keepidle, tcp_keepintvl
