PostgreSQL/PgBouncer: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
No edit summary
 
(51 intermediate revisions by the same user not shown)
Line 1: Line 1:
== References==
{|class='wikitable'
{|
|valign='top' style='width:50%'|
|valign="top"|
<syntaxhighlight lang='bash'>
cat <<-'EXE'|sudo bash
apt-get update;echo
apt list -a --upgradable
apt-get install -y pgbouncer
EXE
</syntaxhighlight>
 
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
    systemctl status  pgbouncer.service
 
sudo systemctl stop    pgbouncer.service
sudo systemctl start  pgbouncer.service
sudo systemctl restart pgbouncer.service
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang='bash'>
ls -lah /etc/pgbouncer/
 
sudo cat /etc/pgbouncer/userlist.txt
sudo cat /etc/pgbouncer/pgbouncer.ini
sudo cat /etc/pgbouncer/pgbouncer.ini|less
</syntaxhighlight>
 
|valign='top'|
 
|-
|valign='top' colspan='2'|
{|class='wikitable mw-collapsible mw-collapsed'
!scope='col' style='text-align:left'|
Diagram
|-
|valign='top'|
<kroki lang='plantuml'>
@startuml
!theme plain
 
' Styling for Transparency and Layout
skinparam backgroundColor transparent
skinparam DefaultFontName Helvetica
skinparam componentStyle  rectangle
skinparam packageStyle    rectangle
skinparam actorStyle      awesome
skinparam monochrome      true
skinparam shadowing      false
 
actor "Application\n(Clients)" as App
participant "PgBouncer\n(Connection Pooler)" as PGB
database "PostgreSQL\n(Backend)" as DB
 
== Connection Phase ==
App -> PGB : Connect (TCP/Unix Socket)
PGB -> PGB : Check Pool for available\nbackend connection
 
alt No idle connection in pool
    PGB -> DB : Establish new backend connection
    DB -> PGB : Connection Ready
end
 
== Execution Phase ==
App -> PGB : SQL Query (SELECT/INSERT...)
PGB -> DB : Forward Query on pooled connection
DB -> PGB : Result Set
PGB -> App : Return Results
 
== Disconnection Phase ==
App -> PGB : Close Connection
note right of PGB
  PgBouncer keeps the DB connection
  OPEN in its pool for the next client.
end note
PGB -> PGB : Return backend connection to pool
 
@enduml
</kroki>
|}
|}
 
==Configure==
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
Configure
|-
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
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
</syntaxhighlight>
 
|valign='top' style='width:50%'|
|}
 
===Configure » Pool » Database===
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left'|
Configure » Pool » Database
|-
|valign='top'|
<syntaxhighlight lang='bash'>
# 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
</syntaxhighlight>
|}
 
===Configure » Pool » User===
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left'|
Configure » Pool » Database
|-
|valign='top'|
<syntaxhighlight lang='bash'>
# 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
</syntaxhighlight>
|}
 
===Configure » User » Secret===
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
Configure
|-
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
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
</syntaxhighlight>
 
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
    systemctl status  pgbouncer.service
 
sudo systemctl reload  pgbouncer.service
sudo systemctl restart pgbouncer.service
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang='bash'>
psql -hlocalhost -p6432 -Uchorke -dacademia_users_staging
psql -hlocalhost -p6432 -Uchorke -dacademia_audit_staging
psql -hlocalhost -p6432 -Uchorke -dacademia_flair_staging
</syntaxhighlight>
 
|valign='top'|
|}
 
==PgBouncer.ini==
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
PgBouncer.ini
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="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
</syntaxhighlight>
|-
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
sudo su
su postgres
pgbouncer -R /etc/pgbouncer/pgbouncer.ini
</syntaxhighlight>
|valign='top' style='width:50%'|
|}
 
==Monitoring==
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
Monitoring
|-
|valign='top' style='width:50%'|
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
|valign='top' style='width:50%'|
|-
|valign='top'|
<syntaxhighlight lang='sql'>
-- pg_lsclusters
SHOW data_directory;
SELECT setting FROM pg_settings WHERE name = 'data_directory';
SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';
</syntaxhighlight>
|valign='top'|
|-
|valign='top'|
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
|valign='top'|
|}
 
==Playground==
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
Playground
|-
|valign='top' style='width:50%'|
<syntaxhighlight lang='bash'>
ls -lah /etc/pgbouncer/
ls -lah /var/log/postgresql/
ls -lah /var/run/postgresql/
</syntaxhighlight>
 
|valign='top' style='width:50%'|
<syntaxhighlight lang="ini">
[pgbouncer]
; ERROR: unsupported startup parameter
ignore_startup_parameters = extra_float_digits
</syntaxhighlight>
|}
 
==References==
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='3'|
References
|-
|valign='top' style='width:33%'|
* [https://stackoverflow.com/questions/43903485/ PostgreSQL » PgBouncer » Horizontal Scaling Advantage]
* [https://stackoverflow.com/questions/30778015/ PostgreSQL » Increase the max connections]
* [https://stackoverflow.com/questions/8288823/ PostgreSQL » <code>SHOW max_connections</code>]
* [https://sip-projects.com/en/blog/pgbouncer-ubuntu PostgreSQL » PgBouncer » Ubuntu]
* [https://sip-projects.com/en/blog/pgbouncer-ubuntu PostgreSQL » PgBouncer » Ubuntu]
* [https://www.pgbouncer.org/config.html PostgreSQL » PgBouncer » Config]
* [https://www.pgbouncer.org/config.html PostgreSQL » PgBouncer » Config]
* [https://www.scaleway.com/en/docs/tutorials/install-pgbouncer/ PostgreSQL » PgBouncer » Install]
* [https://www.scaleway.com/en/docs/tutorials/install-pgbouncer/ PostgreSQL » PgBouncer » Install]
* [https://www.pgbouncer.org/ PostgreSQL » PgBouncer]
* [https://www.pgbouncer.org/ PostgreSQL » PgBouncer]
* [[PostgreSQL/PgLoader|PostgreSQL » PgLoader]]
* [[Helm/PostgreSQL|PostgreSQL » Helm]]
* [[Helm/PostgreSQL|PostgreSQL » Helm]]
* [[PostgreSQL]]
* [[PostgreSQL]]


|valign="top"|
|valign='top' style='width:34%'|
* [https://stackoverflow.com/questions/36495062/ PostgreSQL » PgBouncer » <code>extra_float_digits</code>]
* [https://github.com/pgbouncer/pgbouncer/issues/174 PostgreSQL » PgBouncer » <code>reserve_pool_size</code>]
* [https://dba.stackexchange.com/questions/1350/ PostgreSQL » Find data directory]


|valign="top"|
|valign='top' style='width:33%'|


|-
|-
|colspan="3"|
|valign='top'|
----
|-
|valign="top"|
* [[Academia JavaEE Workspace in Ubuntu]]
* [[Academia JavaEE Workspace in Ubuntu]]
* [[IntelliJ IDEA]]
* [[IntelliJ IDEA]]
Line 29: Line 330:
* [[K8s]]
* [[K8s]]


|valign="top"|
|valign='top'|
 
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
|valign="top"|
* [[K8s/Ingress|K8s » Ingress]]
* [[K8s/Service|K8s » Service]]
* [[K8s/Run|K8s » Run]]


|valign='top'|
|}
|}

Latest revision as of 14:40, 25 January 2026

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

References

References

Retrieved from "https://wiki.chorke.org/index.php?title=PostgreSQL/PgBouncer&oldid=16446"