PgBouncer - Lightweight Connection Pooling And Failover

Giriş

Açıklaması şöyle. 

A lightweight connection pooler for PostgreSQL that can help manage connections and improve performance, but lacks advanced features like replication and failover support. PGBouncer is a good option if you need a simpler solution focused primarily on connection pooling.

Açıklaması şöyle

PostgreSQL was built to handle a small number of long-lasting connections.

That’s why it has a relatively small default limit of connections and it’s not uncommon for people to get the error “connection limit exceeded for non-superusers”.

...

The answer is using a connection pool. Sometimes the framework you’re using offers that (like SQLAlchemy), other times you might need a specialized tool.

But if you’re using multiple machines to serve your API (as you probably are), you will run into the previous problem again since your framework pooler will only be responsible for managing the instance it’s running on.

When you have multiple servers, a good solution is using PGBouncer.

This amazing tool will allow you to connect multiple servers to it instead of connecting directly to Postgres.

It will queue incoming connections when the concurrent amount is greater than the one specified by you.

PgBouncer Neden Lazım

Belki SpringBoot + Hikari bizim için "connecton pooling" yapıyor. Ancak merkezi veri tabanımıza bağlanan 100 tane micro service olsun. Her birisi 2 tane bağlantı açsa yine de 200 tane eder. Bu durumda Hikari yerine PgBouncer kullanmak çok daha iyi.

Şeklen şöyle

Eğer PgBouncer önüne de HAProxy yani bir Load Balancer koyarsak şeklen şöyle

pgbouncer komutu

Başlatmak için şöyle yaparız

pgbouncer -d pgbouncer.ini

reload için şöyle yaparız

pgbouncer -d -R pgbouncer.ini

Log dosyaları şöyle

tail -f pgbouncer.log

pgbouncer.ini Dosyası

1. databases Başlığı

[databases] başlığı altında arka taraftaki veri tabanı için bağlantılar belirtilir.

Örnek

Şöyle yaparız

* = host=localhost port=5432

Örnek

Şöyle yaparız

# Configuration details:
[databases]
writer = host=<primary_db_host> port=5432 dbname=postgres
reader = host=<secondary_db_host> port=5432 dbname=postgres

Örnek

Şöyle yaparız

[databases]
mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase

2. pgbouncer başlığı

[pgbouncer] başlığı altında PgBouncer sunucusu için ayarlar belirtilir

admin_users Alanı

Açıklaması şöyle

This is the database user who will have permission to make PgBouncer settings in the database.

Örnek

Şöyle yaparız

;;;
;;; Users allowed into database 'pgbouncer'
;;;

;; comma-separated list of users who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
admin_users = my_db_user
...

auth_file Alanı

Açıklaması şöyle

The auth_file setting in the pgbouncer configuration file specifies the path to a file that contains a list of users and their passwords for authentication. When a client attempts to connect to the pgbouncer, it checks the user’s credentials against the list of users in the userlist.txt file.

"username" "password"

This file contains a single user named username with a password of password. You can add additional users to the file by adding additional lines with the format “username” “password”.

It is important to note that the userlist.txt file should be secured with appropriate permissions, as it contains sensitive information. By default, pgbouncer expects the userlist.txt file to be owned by the same user as the pgbouncer process and only readable by that user. As per your environment, you can modify the ownership and permissions of the file.

Moreover, it’s advisable to refrain from using a plain text file for storing user credentials. In production environments, it is recommended to use a more secure authentication method, such as SSL or GSSAPI, instead of plain text passwords.

Örnek

Şöyle yaparız

[databases]
mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

auth_type Alanı

Açıklaması şöyle

In the “Authentication settings” section, set auth_type = md5 to authenticate users by a password. The file with the database login and password will be located at /etc/pgbouncer/userlist.txt

listen_port Alanı

Örnek

Şöyle yaparız. Burada pgbouncer 6432 portunu dinliyor

$ cat /etc/pgbouncer/pgbouncer.ini
 
[databases]
world = host=127.0.0.1 port=5432 dbname=world
 
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admindb

Örnek

Şöyle yaparız. Burada pgbouncer 6432 portunu dinliyor

[databases]
* = host = free-tier.gcp-us-central1.cockroachlabs.cloud  dbname = artem-freetier-2924.defaultdb user = artem port = 26257

 [pgbouncer]
 listen_port = 6432
 listen_addr = *
 auth_type = trust
 auth_file = userlist.txt
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = artem
 ignore_startup_parameters=extra_float_digits,options

# free tier provided cert
server_tls_sslmode = verify-full
server_tls_ca_file = /Users/artem/.postgresql/root.crt

max_client_conn Alanı

Kaç tane bağlantı olabileceğini belirtir

Örnek

Şöyle yaparız

;;;
;;; Connection limits
;;;

;; Total number of clients that can connect
max_client_conn = 5000
...

pool_mode Alanı

Sanırım hep transaction olarak tanımlamıyor

Örnek

Şöyle yaparız

;;;
;;; Pooler personality questions
;;;

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
pool_mode = transaction
...

userlist.txt Dosyası

Authentication için gerekir. Açıklaması şöyle

It takes the form of "username" and "password" separated by a space on each new line.

Örnek

Şöyle yaparız

$ cat /etc/pgbouncer/userlist.txt
"admindb" "root123"

 Açıklaması şöyle

So, in this case, I have installed PgBouncer in the same database node, listening in all IP addresses, and it connects to a PostgreSQL database called “world”. I am also managing the allowed users in the userlist.txt file with a plain-text password that can be encrypted if needed.

PostgreSQL 13 ve Öncesine Kadar

Açıklaması şöyle

If you are using PostgreSQL versions up to 13 inclusively, then the default password_encryption method is md5.

Place the username in double quotes and the md5 password hash (in one line):

Örnek

Şöyle yaparız

"my_db_user" "md5badc318d987f61146c6ad8e15d84a111"

md5 hesaplamak için şöyle yaparız

echo "md5"$(echo -n 'YourdbpasswordYourdbusername' | md5sum | awk ' { print $1 } ')

PostgreSQL 14 ve Sonrası

Açıklaması şöyle

If you are using PostgreSQL versions starting from 14, then the default password_encryption method is scram-sha-256.

Örnek

Şöyle yaparız

"my_db_user" "SCRAM-SHA-256$4096:lLN4+i05+kpeffD4s3rRiw==$Oq62iUGamAaF5cpB+agWV4u3xfc5cZCRtvMhmA+Zm3E=:hHkCesEi0p0wLWk1uUEeTtJTYLXHKDLdy2te3VAOe8s="

scram-sha-256 hesaplamak için şöyle yaparız

psql -h  -p  -Atq -U postgres -d postgres 
  -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"