PgBouncer connection pooling

PgBouncer installation from the PGDG repository: transaction or session pool mode, scram-sha-256 authentication through auth_query (a SECURITY DEFINER function) or userlist.txt, pool sizes calculated from the database's max_connections and CPU count, systemd, and optional multi-process mode with so_reuseport. Ends with the admin console (SHOW POOLS/STATS), the application connection string and a transaction mode compatibility checklist.

When to use it: applications open hundreds or thousands of connections, sorry, too many clients already appears, or opening connections is expensive. PgBouncer is not an HA or backup solution: failover needs streaming replication or Patroni, backups need pgBackRest.

Topology: PgBouncer on the database server or on a separate server; with Patroni, in front of HAProxy/VIP

Before you start

• PostgreSQL is installed with the pg-install guide and running; the … database and the … user (with a scram-sha-256 password) exist.
• PostgreSQL …Patroni cluster (pg-patroni) is running; … is currently Leader…primary (pg-streaming) is running…is installed with the pg-install guide and running…; the … database and the … user (with a scram-sha-256 password) exist.
• The Patroni cluster … is healthy: one Leader, replicas streaming; the HAProxy write port (…:…) routes only to the leader.
• The application user's password is stored as SCRAM-SHA-256 (users with old md5 hashes must have their password set again while password_encryption = scram-sha-256).
• The max_connections (…) and CPU (…) values in the form match the real values on the database server (the pool sizes are calculated from them).
• The PgBouncer server can reach the database (…HAProxy/VIP ……:…); the application servers can reach PgBouncer (…:…).
• Port … is free.
• The pool mode has been agreed with the application team: in transaction mode, features that rely on session state (checklist below) are not used.
• scp access (root or a user with sudo rights) is available to copy the password hashes from the database server to the PgBouncer server, or you will move the file by another secure means.

Phases of the guide

• PgBouncer installation — Add the PGDG repository · Add the PGDG repository · Install PgBouncer
• Database side — Verify the role: is this server the …Leader…primary…? · Create the admin and authentication users · Create the admin and authentication users · Set the passwords · Set the passwords · Add the PgBouncer server to pg_hba.conf …
• PgBouncer configuration — Add the auth_user password · Write pgbouncer.ini · Set the file descriptor limit · Start PgBouncer
• Multiple processes (so_reuseport) — Per-process configuration files · systemd template service · Stop the single-process service and start the processes
• Verification — Connect as the application user · Verify that connections reach the leader · Check the admin console · Open the PgBouncer port in the firewall
• Application side and post-installation — Application connection string · Transaction mode compatibility checklist · Monitor the pool · Changing settings and reloading