PostgreSQL/PgBouncer

cat <<-'EXE'|sudo bash apt-get update;echo apt list -a --upgradable sudo 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

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 vim /etc/pgbouncer/pgbouncer.ini

Configure » Databases

---

# find pg pgbouncer db pool
PG_DB_POOL_FIND=$(cat <<QRY
\[databases\]\n\
\n\
;; foodb over Unix socket\n\
;foodb =
QRY
)

# fill pg 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 » Users

---

# find pg pgbouncer user pool
PG_USER_POOL_FIND=$(cat <<QRY
\[users\]\n\
\n\
;user1 = pool_mode=transaction max_user_connections=10
QRY
)

# fill pg 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

cat << USR | sudo tee -a /etc/pgbouncer/userlist.txt >/dev/null
"chorke" "md5$(printf '%s%s' 'sadaqah!' 'chorke'|md5sum|awk '{print $1}')"
"shahed" "md5$(printf '%s%s' 'p@$$w0rd' '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

Playground

ls -lah /etc/pgbouncer/ ls -lah /var/log/postgresql/ ls -lah /var/run/postgresql/

References

PostgreSQL » PgBouncer » Ubuntu PostgreSQL » PgBouncer » Config PostgreSQL » PgBouncer » Install PostgreSQL » PgBouncer PostgreSQL » Helm PostgreSQL
Academia JavaEE Workspace in Ubuntu IntelliJ IDEA OpenLDAP OpenVPN MariaDB HAProxy Helm CIDR UFW K8s	K8s » Swiss Knife K8s » Ingress K8s » Service K8s » Run