• simplest config file /etc/pgbouncer/pgbouncer:
    * = host= port=5432
    ;;user binding like this - add all users it is safer
    postgres = host=localhost dbname=postgres
    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