Configuring PgBouncer v8

You must configure PgBouncer to work with the PEM database server. This example runs PgBouncer as the enterprisedb system user and outlines the process of configuring pgBouncer version 1.9 or later.

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

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

   Toggle WrapCopy

   $ chown enterprisedb:enterprisedb /etc/edb/pgbouncer1.9
   $ chmod 0700 /etc/edb/pgbouncer1.9

3. Change the contents of the pgbouncer.ini or edb-pgbouncer.ini file:

   Toggle WrapCopy

   [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=/tmp dbname=pem auth_user=pgbouncer
   pool_size=80 pool_mode=transaction
   * = port=5444 host=/tmp dbname=pem auth_user=pgbouncer
   pool_size=10
   
   [pgbouncer]
   logfile = /var/log/edb/pgbouncer1.9/edb-pgbouncer-1.9.log
   pidfile = /var/run/edb/pgbouncer1.9/edb-pgbouncer-1.9.pid
   listen_addr = *
   ;; Agent needs to use this port to connect the pem database now
   listen_port = 6432
   ;; Require to support for the SSL Certificate authentications
   ;; 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/as11/data/root.crt
   client_tls_key_file = /var/lib/edb/as11/data/server.key
   client_tls_cert_file = /var/lib/edb/as11/data/server.crt
   ;; Use hba file for client connections
   auth_type = hba
   ;; Authentication file, Reference:
   ;; https://pgbouncer.github.io/config.html#auth_file
   auth_file = /etc/edb/pgbouncer1.9/userlist.txt
   ;; HBA file
   auth_hba_file = /etc/edb/pgbouncer1.9/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
   ;; 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

4. Create and update the /etc/edb/pgbouncer1.9/userlist.txt authentication file for PgBouncer:

   Toggle WrapCopy

   pem=# COPY (
   SELECT 'pgbouncer'::TEXT, 'pgbouncer_password'
   UNION ALL
   SELECT 'pem_admin1'::TEXT, 'pem_admin1_password'
   TO '/etc/edb/pgbouncer1.9/userlist.txt'
   WITH (FORMAT CSV, DELIMITER ' ', FORCE_QUOTE *);
   
   COPY 2

   Note

   A superuser can't invoke the PEM authentication query function pem.get_proxy_auth(text). If the pem_admin user is a superuser, you must add the password to the authentication file (enterprisedb in the example).

5. Create an HBA file (/etc/edb/pgbouncer1.9/hba_file) for PgBouncer that contains the following content:

   Toggle WrapCopy

   # Use authentication method md5 for the local connections to
   # connect pem database & pgbouncer (virtual) database.
   local pgbouncer all md5
   # Use authentication method md5 for the remote connections to
   # connect to pgbouncer (virtual database) using enterprisedb
   # user.
   
   host pgbouncer,pem pem_admin1 0.0.0.0/0 md5

   Toggle WrapCopy

   # Use authentication method cert for the TCP/IP connections to
   # connect the pem database using pem_agent_user1
   
   hostssl pem pem_agent_user1 0.0.0.0/0 cert

6. Change the owner of the HBA file (/etc/edb/pgbouncer1.9/hba_file) to enterprisedb, and change the directory permissions to 0600:

   Toggle WrapCopy

   $ chown enterprisedb:enterprisedb /etc/edb/pgbouncer1.9/hba_file
   $ chmod 0600 /etc/edb/pgbouncer1.9/hba_file

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

   Toggle WrapCopy

   $ systemctl enable edb-pgbouncer-1.9
   
   Created symlink from
   /etc/systemd/system/multi-user.target.wants/edb-pgbouncer-1.9.service
   to /usr/lib/systemd/system/edb-pgbouncer-1.9.service.
   
   $ systemctl start edb-pgbouncer-1.9