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

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

---

# 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

---

# 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

---

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

[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

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

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

References

PostgreSQL » PgBouncer » Horizontal Scaling Advantage PostgreSQL » Increase the max connections PostgreSQL » SHOW max_connections PostgreSQL » PgBouncer » Ubuntu PostgreSQL » PgBouncer » Config PostgreSQL » PgBouncer » Install PostgreSQL » PgBouncer PostgreSQL » PgLoader PostgreSQL » Helm PostgreSQL	PostgreSQL » PgBouncer » extra_float_digits PostgreSQL » PgBouncer » reserve_pool_size PostgreSQL » Find data directory
Academia JavaEE Workspace in Ubuntu IntelliJ IDEA OpenLDAP OpenVPN MariaDB HAProxy Helm CIDR UFW K8s	K8s » Swiss Knife K8s » Ingress K8s » Service K8s » Run