Pgbouncer

Usage

• https://www.pgbouncer.org/usage.html
• https://devcenter.heroku.com/articles/best-practices-pgbouncer-configuration

Install

apt install pgbouncer

create role someuser;
create role someadmin;
ALTER ROLE someadmin WITH PASSWORD 'test';
ALTER ROLE someuser WITH PASSWORD 'test';
ALTER ROLE someuser WITH login;
ALTER ROLE someadmin WITH login SUPERUSER;

Update userlist.txt

sudo -u postgres psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow" > /etc/pgbouncer/userlist.txt

/etc/pgbouncer/pgbouncer.ini

[databases]

# pgb = dbname=pgb host=127.0.0.1 port=5432
template1 = dbname=template1 host=127.0.0.1 port=5432
auth_type = md5 # any, trust, plain, md5, cert, hba, pam
admin_users = someadmin, otheradmin

Connecting through proxy to database

psql -p 6432 -h 127.0.0.1 -U someuser template1;

Connecting to and using admin console

psql -p 6432 -h 127.0.0.1 -U someadmin pgbouncer;
SHOW STATS_TOTALS;
SHOW CLIENTS;
SHOW POOLS;
SHOW DATABASES;
SHOW USERS;
SHOW LISTS;
SHOW SOCKETS; 
SHOW ACTIVE_SOCKETS;
SHOW CONFIG;
SHOW FDS;
...

Reestart

service restart pgbouncer

https://stackoverflow.com/questions/46198870/how-to-determine-max-client-conn-for-pgbouncer