PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Presuming that our calculated pool size for our pgha1 PostgreSQL server was 25, with a memory-imposed maximum of 350, follow these steps to properly configure PgBouncer:

1. Execute this query as the postgres user while connected to any database within PostgreSQL:

COPY ( 
    SELECT '"' || rolname || '" "' ||  
           coalesce(rolpassword, '') || '"' 
      FROM pg_authid 
) 
TO '/tmp/userlist.txt'; 

2. Copy the /tmp/userlist.txt output file to the /etc/pgbouncer directory where PgBouncer is installed, and then remove /tmp/userlist.txt.
3. Open the /etc/pgbouncer/pgbouncer.ini file as the postgres system user.
4. Under the section labeled [databases], create the following entry:

* = host=pgha1 

5. Under the section labeled [pgbouncer], locate each of these ...