PostgreSQL/PgBouncer

From Chorke Wiki
Jump to navigation Jump to search
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

Playground

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

References