PostgreSQL/PgBouncer: Difference between revisions
Jump to navigation
Jump to search
| Line 60: | Line 60: | ||
sudo sed -z "s|${PG_DB_POOL_FIND}|$(echo "${PG_DB_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini | sudo sed -z "s|${PG_DB_POOL_FIND}|$(echo "${PG_DB_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini | ||
</syntaxhighlight> | </syntaxhighlight> | ||
===Configure » users=== | |||
---- | |||
<syntaxhighlight lang="bash"> | |||
# 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 | |||
</syntaxhighlight> | |||
---- | ---- | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
Revision as of 03:02, 19 September 2024
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
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=localhost port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_audit_staging = host=localhost port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_qoute_staging = host=localhost port=5432 user=chorke password='sadaqah!' client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'\n\
academia_users_staging = host=localhost 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
systemctl status pgbouncer.service
sudo systemctl reload pgbouncer.service
sudo systemctl restart pgbouncer.service
Playground
ls -lah /etc/pgbouncer/
ls -lah /var/log/postgresql/
ls -lah /var/run/postgresql/
|
||
|
| ||
References
|
| ||