PgBouncer Configurations
Basic Configurations​
PgBouncer ne work karva mate, aapde configuration file aapvi jaruri 6e.
Jo aapde scratch thi configuration file na create karvi hoi to aapde pgbouncer.ini file no use kari sakie 6ie.
Linux ma usually aa file /etc/pgbouncer folder ma mali jai 6e.
Aa default file real life scenarios ma useful nathi pan ek basic start mate good 6e.
aapde aa file ne edit karie aeni pehla aa file ni ek backup file create kari laie.
cd /etc/pgbouncer
sudo cp ./pgbouncer.ini ./pgbouncer.ini.bkp
have aapde pgboucer.ini file ne nano athva vim koi bhi text editor ni madad thi open kari laie.
Aa file ma ghani configurations mate parameters correct karva ni jarur 6e.
sauthi pehla aave [databases] section ma jasu ane ek alias aapsu.
test_db=
tyar pa6i upar connect string params comment karela 6e aeni jem values provide karsu.
test_db= dbname=postgres host=localhost port=5433 auth_user=
PgBouncer client authentication bhi handle kare 6e. authentication ek broad topic 6e. aa practice mate aapde simple authentication no use karsu. ek auth_file kari ne bhi use kari sakie 6ie je more traditional 6e, jo user and password bau vadhare change thata rehta hoi to. Aapde LDAP and PAM ni madad thi pan authentication kari sakie 6ie. Aa practice ma simple authentication use karva mate aapde ek superuser ni jarur 6e je aapde thodi vaar ma create karsu, haal ma aapde just aenu name ahi aapi desu.
test_db= dbname=postgres host=localhost port=5433 auth_user=postgres
Have aapde ae user create karva ni jarur 6e. For that, lets connect to database first.
psql --port=5433 postgres
tyar pa6i postgres ma ek user create karo.
create user postgres superuser;
have jyare aapdi pase ek basic configuration 6e, to aapde PgBouncer ne start kari sakie 6ie. start karva mate aapde pgbouncer command sathe configuration file provide karvi padse.
pgbouncer /etc/pgboucer/pgbouncer.ini
jo koi permission error aave to chown command ni madad thi solve karo.
pgbouncer.ini file ma port number aapde set kari sakie 6ie. by default aema 6432 port set hase.
mane authentication ni bau problem aaveli, aene solve karva mate, me notice karyu pgbouncer.ini file ma auth_type = md5 hatu tya auth_type = trust karva nu rehse.
Jo pgbouncer server start thai gayu hoi to tame new terminal ma jai ne aa try kari sako
psql -p 6432 test_db -U postgres
jo connect thai gayu, means tame PgBouncer thi connect thaya 6o, direct real database sathe nahi.
Advanced Configurations​
Jyare PgBouncer start thay tyare aapde make sure karvu joie k ae ek minimum number of connections pool ma rakhej.
aa setup mate pgbouncer.ini ma min_pool_size ni value aapde set kari sakie 6ie.
Connection to user thi thatu hoi 6e and ae pan database sathe. to jo aapde default connection limit aapvi hoi per database mate, like 5 database 6e 2 users 6e, koi bhi case ma 20 thi vadhare connection na banva joie. to aa case ma aapde Maximum number of server connections set karva hoi to default_pool_size ne set kari sakie 6ie.
jo default pool size karta vadhare connection request aave to ae request queue ma store thai 6e. pan jo aapde ae queue ni pan limit set karvi hoi to aena mate aapde max_client_conn set kari sakie 6ie.
Industry standard and recommeded ae pan 6e k aapda setup ma jetla pan nodes participate thai 6e ae badha ek bija thi najik hoi jethi latency no issue na aave.