postgresql pgbouncer - ghdrako/doc_snipets GitHub Wiki

• https://www.pgbouncer.org/
• https://medium.com/swlh/pgbouncer-installation-configuration-and-use-cases-for-better-performance-1806316f3a22

pgBouncer is an open-source connection pooling application that helps manage connection usage for high-traffic applications. PostgreSQL creates one new process (not thread) per connection.

pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.

pgBouncer działa jako niezależny proxy po stronie klienta, który jest odpowiedzialny za zarządzanie połączeniami dla wielu klientów i aplikacji. Rożni sie to w porównaniu z bibliotekami używanymi wewnątrz aplikacji typu HikariCP dla java które służą do pulowania połączeń wewnątrz aplikacji. pgBouncer może działać jednocześnie z biblioteka do pulowania.

New connection creates a new backend process. server side connection pooler

Install

1. Install PgBouncer

sudo yum install -y pgbouncer

2. Folder permissions to postgres user

chown postgres:postgres /etc/pgbouncer/ -R

3. Create Service file for PgBouncer and paste config into it

vi /usr/lib/systemd/system/pgbouncer.service[Unit]
Description=A lightweight connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
After=syslog.target network.target[Service]
RemainAfterExit=yesUser=postgres
Group=postgres# Path to the init file
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.iniExecStart=/usr/bin/pgbouncer -q ${BOUNCERCONF}
ExecReload=/usr/bin/pgbouncer -R -q ${BOUNCERCONF}# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300[Install]
WantedBy=multi-user.target

4. Open PgBouncer config/init file and paste the config below - https://www.pgbouncer.org/config.html

[databases]
* = port=5432 auth_user=postgres[pgbouncer]
logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = hba
auth_hba_file = /path/to/pg_hba.conf
admin_users = postgres
stats_users = postgres
pool_mode = session
ignore_startup_parameters = extra_float_digits
max_client_conn = 200
default_pool_size = 50
reserve_pool_size = 25
reserve_pool_timeout = 3
server_lifetime = 300
server_idle_timeout = 120
server_connect_timeout = 5
server_login_retry = 1
query_timeout = 60
query_wait_timeout = 60
client_idle_timeout = 60
client_login_timeout = 60

5. Start PgBouncer service

systemctl start pgbouncer

The last part of the config is PgBouncer config/init file(pgbouncer.ini). There are some different types of authentication for PgBouncer.

• https://www.pgbouncer.org/config.html#authentication-settings

Let’s consider the default auth_file = users.txt.

md5 : Default auth method for PgBouncer.

1.Run the query below.

    SELECT CONCAT('"',pg_shadow.usename, '" "', passwd, '"') FROM pg_shadow;

2.Copy output and paste into /etc/pgbouncer/users.txt 3.In pgbouncer.ini make changes below

    auth_type = md5
    auth_file = /etc/pgbouncer/userlist.txt

hba

Don't forget to delete replication db configurations in your ''hba.conf'' file, because pgbouncer doesn’t support replication. You can use a fake ''hba.conf'' file instead of your original file.

1. Check your pg_hba.conf and comment replication db parts. After that make sure that PgBouncer can parse it.
2. In ‘pgbouncer.ini’ make changes below

auth_type = hba
auth_file = /path/to/pg_hba.conf