Postgresql - PGBOUNCER

PgBouncer is a PostgreSQL connection pooler that is lightweight and aids in database connection management and optimization. By reusing existing connections, it lowers overhead, which makes it beneficial for databases with numerous short-lived connections and high-traffic applications.

Installation Of PgBouncer :

    yum install pgbouncer
    cd /etc/pgbouncer

Modify below parameters in pgbouncer.ini

    [postgres@dv2025 pgbouncer]$ vi pgbouncer.ini
    [databases]
    db1=dbname=db1 host=dv2025 port=5430 user=postgres auth_user=postgres
    password=postgres
    [users]
    [pgbouncer]
    logfile = /var/log/pgbouncer/pgbouncer.log
    pidfile = /var/run/pgbouncer/pgbouncer.pid
    listen_addr =0.0.0.0
    listen_port = 6432
    auth_type = trust
    auth_file = /etc/pgbouncer/userlist.txt
    #auth_hba_file =/var/lib/pgsql/17/data/pg_hba.conf
    admin_users = postgres
    stats_users = stats, postgres

    pool_mode = session
    ignore_startup_parameters = extra_float_digits

Get password from pg_shadow :

    [postgres@dv2025 pgbouncer]$ cat userlist.txt "postgres" 
    "SCRAM-SHA- 256$4096:beVU4JKP5nKaTzun8cu85A==$39XwoN6oRID5kVjsLBLzG0SMVS mOIv7imcuHczWIMUo
                                            =:22SIqCmRxNbypsJRcWzHzD9vjZjN4r6yVfpjNJhKzA8";

Start pgbouncer using :

    $pgbouncer pgbouncer.ini

Connect pgbouncer using port 6432 :

 
    [postgres@dv2025 pgbouncer]$ psql -p 6432 -h dv2025 db1
    psql (17.2)
    Type "help" for help.
    db1=#

« Previous (Postgresql - Chapter 16 HA-DR with postgres)