PostgreSQL/PgBouncer: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
No edit summary
 
(37 intermediate revisions by the same user not shown)
Line 1: Line 1:
{|
{|class='wikitable' style='width:100%;margin:-11px 0 6px 0'
|valign="top"|
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
cat <<-'EXE'|sudo bash
cat <<-'EXE'|sudo bash
apt-get update;echo
apt-get update;echo
apt list -a --upgradable
apt list -a --upgradable
sudo apt-get install -y pgbouncer
apt-get install -y pgbouncer
EXE
EXE
</syntaxhighlight>
</syntaxhighlight>


|valign="top"|
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
     systemctl status  pgbouncer.service
     systemctl status  pgbouncer.service


Line 17: Line 17:
sudo systemctl restart pgbouncer.service
sudo systemctl restart pgbouncer.service
</syntaxhighlight>
</syntaxhighlight>
 
|-
|valign="top"|
|valign='top'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
  ls -lah /etc/pgbouncer/
  ls -lah /etc/pgbouncer/


Line 26: Line 26:
sudo cat /etc/pgbouncer/pgbouncer.ini|less
sudo cat /etc/pgbouncer/pgbouncer.ini|less
</syntaxhighlight>
</syntaxhighlight>
|valign='top'|
|}
|}


==Configure==
==Configure==
<syntaxhighlight lang="bash">
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
Configure
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
sudo rsync -avz /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bkp
sudo rsync -avz /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bkp
sudo rsync -avz /etc/pgbouncer/userlist.txt  /etc/pgbouncer/userlist.txt.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>
</syntaxhighlight>


===Configure » Databases===
|valign='top' style='width:50%'|
----
|}
<syntaxhighlight lang="bash">
 
# find pg pgbouncer db pool
===Configure » Pool » Database===
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left'|
Configure » Pool » Database
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
# find pgbouncer db pool
PG_DB_POOL_FIND=$(cat <<QRY
PG_DB_POOL_FIND=$(cat <<QRY
\[databases\]\n\
\[databases\]\n\
Line 46: Line 64:
)
)


# fill pg pgbouncer db pool
# fill pgbouncer db pool
PG_DB_POOL_FILL=$(cat <<UPD
PG_DB_POOL_FILL=$(cat <<UPD
[databases]\n\
[databases]\n\
Line 58: Line 76:
)
)


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===
===Configure » Pool » User===
----
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
<syntaxhighlight lang="bash">
!scope='col' style='text-align:left'|
# find pg pgbouncer user pool
Configure » Pool » Database
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
# find pgbouncer user pool
PG_USER_POOL_FIND=$(cat <<QRY
PG_USER_POOL_FIND=$(cat <<QRY
\[users\]\n\
\[users\]\n\
Line 72: Line 95:
)
)


# fill pg pgbouncer user pool
# fill pgbouncer user pool
PG_USER_POOL_FILL=$(cat <<UPD
PG_USER_POOL_FILL=$(cat <<UPD
[users]\n\
[users]\n\
Line 81: Line 104:
)
)


sudo sed -z "s|${PG_USER_POOL_FIND}|$(echo "${PG_USER_POOL_FILL}")|" -i /etc/pgbouncer/pgbouncer.ini
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' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
Configure
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' 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>
</syntaxhighlight>


----
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
     systemctl status  pgbouncer.service
     systemctl status  pgbouncer.service
sudo systemctl reload  pgbouncer.service
sudo systemctl reload  pgbouncer.service
sudo systemctl restart pgbouncer.service
sudo systemctl restart pgbouncer.service
</syntaxhighlight>
</syntaxhighlight>
 
|-
==Playground==
{|
|valign='top'|
|valign='top'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
ls -lah /etc/pgbouncer/
psql -hlocalhost -p6432 -Uchorke -dacademia_users_staging
ls -lah /var/log/postgresql/
psql -hlocalhost -p6432 -Uchorke -dacademia_audit_staging
ls -lah /var/run/postgresql/
psql -hlocalhost -p6432 -Uchorke -dacademia_flair_staging
</syntaxhighlight>
</syntaxhighlight>


|valign='top'|
|valign='top'|
|}


|valign='top'|
==PgBouncer.ini==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
PgBouncer.ini
|-
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' 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 style='margin:3px 0' lang='bash'>
sudo su
su postgres
pgbouncer -R /etc/pgbouncer/pgbouncer.ini
</syntaxhighlight>
|valign='top' style='width:50%'|
|}


==Monitoring==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
Monitoring
|-
|-
|colspan='3'|
|valign='top' style='width:50%'|
----
<syntaxhighlight style='margin:3px 0' 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'|
|valign='top'|
 
<syntaxhighlight style='margin:3px 0' 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 style='margin:3px 0' 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'|
|valign='top'|
|}


|valign='top'|
==Playground==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
Playground
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
ls -lah /etc/pgbouncer/
ls -lah /var/log/postgresql/
ls -lah /var/run/postgresql/
</syntaxhighlight>


|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang="ini">
[pgbouncer]
; ERROR: unsupported startup parameter
ignore_startup_parameters = extra_float_digits
</syntaxhighlight>
|}
|}


== References==
==References==
{|
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
|valign="top"|
!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 146: Line 279:
* [[K8s]]
* [[K8s]]


|valign="top"|
|valign='top'|
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Ingress|K8s » Ingress]]
* [[K8s/Ingress|K8s » Ingress]]
Line 152: Line 285:
* [[K8s/Run|K8s » Run]]
* [[K8s/Run|K8s » Run]]


|valign="top"|
|valign='top'|


|}
|}

Latest revision as of 01:11, 16 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

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=16124"