Skip to content

pgBouncer

pgBouncer is a lightweight connection pooler for PostgreSQL. It is used to manage database connections efficiently, allowing multiple clients to share a smaller number of database connections. This greatly reduces the overhead of establishing new connections and can improve the performance of applications that connect to PostgreSQL databases.

Configuration

The configuration of pgBouncer is set at the pgbouncer.ini file, which can be edited using sudo nano /etc/pgbouncer/pgbouncer.ini. Below are the most important settings you may want to adjust:

  • pool_mode: Defines how connections are pooled. Common values are session, transaction, and statement. The default is session, which means a connection is assigned to a client for the duration of the session.
  • default_pool_size: Sets the number of server connections to be maintained in the pool for each database.
  • max_client_conn: Limits the maximum number of client connections that pgBouncer will accept.
  • server_idle_timeout: Specifies how long a server connection can remain idle before it is closed. This helps to free up resources.
  • client_idle_timeout: Defines how long a client connection can remain idle before it is closed. This is useful to prevent stale connections from consuming resources.
  • server_lifetime: Sets the maximum lifetime of a server connection. After this time, the connection will be closed and a new one will be established.
  • auth_type: Determines the authentication method used by pgBouncer. We are using scram-sha-256 for secure password storage.
  • auth_file: Specifies the path to the file containing user credentials. We are using /etc/pgbouncer/userlist.txt for this purpose.

User Management

As pgBouncer is another layer of authentication, you need to manage users separately from PostgreSQL. The user credentials are stored in the userlist.txt file, which can be edited using sudo nano /etc/pgbouncer/userlist.txt. The format of the file is:

"username" "password_hash"

The user and passwords must match those in your PostgreSQL database, so we need to get the password hashes from PostgreSQL. To do so, run SELECT * FROM pg_authid and copy the password hashes for the users you want to add to pgBouncer. Then, add them to the userlist.txt file in the format shown above.

Restarting pgBouncer

After making changes to the configuration or user management files, you need to restart pgBouncer for the changes to take effect. You can do this with the following command:

sudo systemctl restart pgbouncer

You can check the status of pgBouncer to ensure it is running correctly with:

sudo systemctl status pgbouncer