Configuring PgBouncer
=====================

You must configure PgBouncer to work with the PEM database server.

Prerequisites
-------------

- If you’re running EDB Postgres Advanced Server, install `EDB PgBouncer <https://www.enterprisedb.com/docs/pgbouncer/latest/installing>`_ 
  .

- If you’re running EDB Postgres Extended Server or PostgreSQL, install
  community `PgBouncer <https://www.pgbouncer.org/install.html>`_  .

EDB PgBouncer and PgBouncer installation considerations
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The name and location of the directories and files in the configuration
steps, as well as the user, depend on whether you installed the
community version of PgBouncer or EDB PgBouncer. If you installed
community PgBouncer (whether you install it from the community repo or
the EDB repo), replace the names of the files and directories in the
worked example with the values for PgBouncer.

.. csv-table::
  :header: Name,PgBouncer,EDB PgBouncer
  :widths: 12,25,15
  :align: left
  :class: longtable

  PgBouncer directory,`/etc/pgbouncer`,`/etc/edb/pgbouncer<1.x>`
  ini file,`pgbouncer.ini`,`edb-pgbouncer.ini`
  HBA file,`/etc/pgbouncer/hba_file`,`/etc/edb/pgbouncer<1.x>/hba_file`
  Service file,`pgbouncer`,`edb-pgbouncer-<1.x>`
  User,`postgres`,`enterprisedb`

.. _configuring-pgbouncer-1:

Configuring PgBouncer
---------------------

This example configures EDB PgBouncer with the enterprisedb system user.

If you’re running community PgBouncer, replace the names of the
directories, files, and user as explained in :ref:`EDB PgBouncer and PgBouncer installation considerations <EDB PgBouncer and PgBouncer installation considerations>`  .

1. Open a terminal window and navigate to the PgBouncer directory.

2. Change the owner of the ``etc`` directory for PgBouncer (where
   ``edb-pgbouncer.ini`` resides) to ``enterprisedb`` , and change the
   directory permissions to ``0700`` :

.. code:: shell

       chown -R enterprisedb:enterprisedb /etc/edb/pgbouncer<1.x>
       chmod 0700 /etc/edb/pgbouncer<1.x>

1. Change the contents of the ``edb-pgbouncer.ini`` file:

.. code:: ini

       [databases]
       ;; Change the pool_size according to maximum connections allowed
       ;; to the PEM database server as required.
       ;; auth_user will be used for authenticate the db user (proxy
       ;; agent user in our case)
       pem = port=5444 host=127.0.0.1 dbname=pem auth_user=pgbouncer pool_size=80 pool_mode=transaction
     - = port=5444 host=127.0.0.1 dbname=pem auth_user=pgbouncer pool_size=10

       [pgbouncer]
       logfile = /var/log/edb/pgbouncer<1.x>/edb-pgbouncer-<1.x>.log
       pidfile = /var/run/edb/pgbouncer<1.x>/edb-pgbouncer-<1.x>.pid
       listen_addr = *
       ;; Agent needs to use this port to connect the pem database now
       listen_port = 6432
       ;; Set to require to ensure SSL certificates are used for connections
       ;; for PEM Agents
       client_tls_sslmode = require
       ;; These are the root.crt, server.key, server.crt files present
       ;; in the present under the data directory of the PEM database
       ;; server, used by the PEM Agents for connections.
       client_tls_ca_file = /var/lib/edb/as16/data/root.crt
       client_tls_key_file = /var/lib/edb/as16/data/server.key
       client_tls_cert_file = /var/lib/edb/as16/data/server.crt
       ;; Allow pgBouncer to use pem_agent_pool certificate
       ;; and key for connections to the server.
       server_tls_key_file = /var/lib/edb/.postgresql/pem_agent_pool.key
       server_tls_cert_file = /var/lib/edb/.postgresql/pem_agent_pool.crt
       ;; Use hba file for client connections
       auth_type = hba
       ;; HBA file
       auth_hba_file = /etc/edb/pgbouncer<1.x>/hba_file
       ;; Use pem.get_agent_pool_auth(TEXT) function to authenticate
       ;; the db user (used as a proxy agent user).
       auth_query = SELECT * FROM pem.get_agent_pool_auth($1)
       ;; DB User for administration of the pgbouncer
       admin_users = pem_admin1
       ;; auth_dbname and auth_user allow
       ;; admin console login by admin_users and stats_users
       auth_dbname = pem
       auth_user = pgbouncer
       ;; DB User for collecting the statistics of pgbouncer
       stats_users = pem_admin1
       server_reset_query = DISCARD ALL
       ;; Change based on the number of agents installed/required
       max_client_conn = 500
       ;; Close server connection if its not been used in this time.
       ;; Allows to clean unnecessary connections from pool after peak.
       server_idle_timeout = 60

!!!note For more information on `auth_user` , see  `Authentication settings <https://www.pgbouncer.org/config.html#authentication-settings>`_  . .. ::
   1.  Create an HBA file (`/etc/edb/pgbouncer<1.x>/hba_file` ) for PgBouncer that contains the following content:

.. code:: ini

       # Use the authentication method scram-sha-256 for local connections
       # between the pem database & the pgbouncer (virtual) database.
       local pgbouncer all scram-sha-256
       # Use the authentication method scram-sha-256 for remote connections
       # to pgbouncer (virtual database) using the enterprisedb user.
       host pgbouncer,pem pem_admin1 0.0.0.0/0 scram-sha-256
       # Use the authentication method cert for TCP/IP connections
       # to the pem database using pem_agent_user1
       hostssl pem pem_agent_user1 0.0.0.0/0 cert

1. Change the owner of the HBA file
   ``(/etc/edb/pgbouncer<1.x>/hba_file)`` to ``enterprisedb`` , and
   change the directory permissions to ``0600`` :

.. code:: shell

       chown enterprisedb:enterprisedb /etc/edb/pgbouncer<1.x>/hba_file
       chmod 0600 /etc/edb/pgbouncer<1.x>/hba_file

1. Enable the PgBouncer service, and start the service:

.. code:: shell

       systemctl enable edb-pgbouncer-<1.x>
       __OUTPUT__
       Created symlink from
       /etc/systemd/system/multi-user.target.wants/edb-pgbouncer-<1.x>.service
       to /usr/lib/systemd/system/edb-pgbouncer-<1.x>.service.

.. code:: shell

       systemctl start edb-pgbouncer-<1.x>