PostgreSQL/PgBouncer

cat <<-'EXE'|sudo bash apt-get update;echo apt list -a --upgradable apt-get install -y pgbouncer EXE	systemctl status pgbouncer.service sudo systemctl stop pgbouncer.service sudo systemctl start pgbouncer.service sudo systemctl restart pgbouncer.service
ls -lah /etc/pgbouncer/ sudo cat /etc/pgbouncer/userlist.txt sudo cat /etc/pgbouncer/pgbouncer.ini sudo cat /etc/pgbouncer/pgbouncer.ini|less
Diagram

Configure

Configure
sudo rsync -avz /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bkp sudo rsync -avz /etc/pgbouncer/userlist.txt /etc/pgbouncer/userlist.txt.bkp # restore default configuration sudo rsync -avz /etc/pgbouncer/pgbouncer.ini.bkp /etc/pgbouncer/pgbouncer.ini sudo cat /etc/pgbouncer/pgbouncer.ini

Configure » Pool » Database

Configure » Pool » Database

# find pgbouncer db pool PG_DB_POOL_FIND=$(cat <<QRY \[databases\]\n\ \n\ ;; foodb over Unix socket\n\ ;foodb = QRY ) # fill pgbouncer db pool PG_DB_POOL_FILL=$(cat <<UPD [databases]\n\ \n\ ;; future db pool here\n\ academia_flair_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\ academia_audit_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\ academia_qoute_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\ academia_users_staging = host=10.110.110.155 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' UPD ) sudo sed -z "s|${PG_DB_POOL_FIND}|$(echo "${PG_DB_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini

Configure » Pool » User

Configure » Pool » Database

# find pgbouncer user pool PG_USER_POOL_FIND=$(cat <<QRY \[users\]\n\ \n\ ;user1 = pool_mode=transaction max_user_connections=10 QRY ) # fill pgbouncer user pool PG_USER_POOL_FILL=$(cat <<UPD [users]\n\ \n\ ;; future user pool here\n\ chorke = pool_mode=transaction max_user_connections=10 UPD ) sudo sed -z "s|${PG_USER_POOL_FIND}|$(echo "${PG_USER_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini sudo sed -z "s|auth_type = trust|auth_type = md5|" -i /etc/pgbouncer/pgbouncer.ini

Configure » User » Secret

Configure
cat << USR | sudo tee -a /etc/pgbouncer/userlist.txt >/dev/null "chorke" "md5$(printf '%s%s' 'p@$$w0rd' 'chorke'|md5sum|awk '{print $1}')" "shahed" "md5$(printf '%s%s' 'sadaqah!' 'shahed'|md5sum|awk '{print $1}')" USR	systemctl status pgbouncer.service sudo systemctl reload pgbouncer.service sudo systemctl restart pgbouncer.service
psql -hlocalhost -p6432 -Uchorke -dacademia_users_staging psql -hlocalhost -p6432 -Uchorke -dacademia_audit_staging psql -hlocalhost -p6432 -Uchorke -dacademia_flair_staging

PgBouncer.ini

PgBouncer.ini
[databases] academia_flair_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' academia_audit_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' academia_qoute_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' academia_users_staging = host=127.0.0.1 port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1' [users] chorke = pool_mode=transaction max_user_connections=20 [pgbouncer] auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt pool_mode = session ignore_startup_parameters = extra_float_digits min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 5 max_db_connections = 10 max_user_connections = 10
sudo su su postgres pgbouncer -R /etc/pgbouncer/pgbouncer.ini

Monitoring

Monitoring
SHOW max_connections; RESET max_connections; ALTER SYSTEM SET max_connections = 200; SELECT current_setting('max_connections'); SELECT sum(numbackends) FROM pg_stat_database; SELECT * FROM pg_settings WHERE name = 'max_connections'; SELECT state, count(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1 ORDER BY 1;
-- pg_lsclusters SHOW data_directory; SELECT setting FROM pg_settings WHERE name = 'data_directory'; SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';
WITH conn_limit AS ( SELECT setting::int "max_conn_size" FROM pg_settings WHERE name=$$max_connections$$ ), conn_super AS ( SELECT setting::int "max_conn_admin" FROM pg_settings WHERE name=$$superuser_reserved_connections$$ ) SELECT count(*) "max_conn_used", (SELECT * FROM conn_super), (SELECT * FROM conn_limit) FROM pg_stat_activity;

Playground

Playground
ls -lah /etc/pgbouncer/ ls -lah /var/log/postgresql/ ls -lah /var/run/postgresql/	[pgbouncer] ; ERROR: unsupported startup parameter ignore_startup_parameters = extra_float_digits