How to install and configure PG Bouncer on Linux x86_64

Install the PGBouncer software

List packages allready installed on the system:

yum list installed |grep bounce

Search repository:

 yum search bounce
=========================================================== Name & Summary Matched: bounce ============================================================
pgbouncer.x86_64 : Lightweight connection pooler for PostgreSQL
=============================================================== Summary Matched: bounce ===============================================================

Install:

 yum install pgbouncer.x86_64

Verify installation:

 yum list installed |grep bounce
pgbouncer.x86_64                                              1.24.0-42PGDG.rhel8                     @YOURREPOSITORY_PostgreSQL_PostgreSQL_common_RHEL8_x86_64

Done with the installation!

Configure the pgbouncer for connections made by the user "music" to my database called "musicdb"

[root] chown postgres:postgres /etc/pgbouncer/ -R

Create the file /etc/pgbouncer/pgbouncer.ini:

;
; pgbouncer config file
;
[databases]
postgres    = port=5432 dbname=musicdb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
admin_users = music
auth_type   = scram-sha-256
auth_file   = /postgres_data/config/users.txt
logfile     = /postgres_data/config/pgbouncer.log
pidfile     = /postgres_data/config/pgbouncer.pid
server_reset_query = DISCARD ALL;
pool_mode = session
default_pool_size = 5
log_pooler_errors = 0

Find the SCRAM authenticatio key:

psql
select '"'||rolname||'" "'||rolpassword||'"' from pg_authid where rolname='music';

  
Create the file /postgres_data/config/users.txt

vi /postgres_data/config/users.txt

Add the following, and paste the value of the key you found in the query above, in between the second pair of quotation marks:

"music" "SCRAM-SHA-256_your_SCRAM_key_here"

Make sure port 6432 isn't occupied

 ss -ltpn | grep 6432 --> no output? Go go ahead! 

As the postgres user, start the pgbouncer process

pgbouncer -d /etc/pgbouncer/pgbouncer.ini

The port will now be occupied:

ss -ltpn | grep 6432
LISTEN 0      128        127.0.0.1:6432      0.0.0.0:*    users:(("pgbouncer",pid=392231,fd=12))

You can now connect to your pgbouncer instance. There are a number of different show-commands:

psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "show config"
Password for user music:
             key             |                         value                          |                        default                         | changeable
-----------------------------+--------------------------------------------------------+--------------------------------------------------------+------------
 admin_users                 | music                                                  |                                                        | yes
 application_name_add_host   | 0                                                      | 0                                                      | yes
 auth_dbname                 |                                                        |                                                        | yes
 auth_file                   | /var/lib/pgsql/users.txt                               |                                                        | yes

And some control commands:

psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "reload"
Password for user music:
RELOAD

To connect through the pgbouncer rather than a direct connection, use the syntax below, where you exchange the database name ("musicdb") with "pgbouncer":

  psql -p 6432 -h 127.0.0.1 -U music -d pgbouncer
Password for user music:
psql (15.10, server 1.24.0/bouncer)
WARNING: psql major version 15, server major version 1.24.
         Some psql features might not work.
Type "help" for help.

pgbouncer=# \conninfo
You are connected to database "pgbouncer" as user "music" on host "127.0.0.1" at port "6432".