Use the PgBouncer connection pooler

Select a documentation version:

This page describes how to enable and use the PgBouncer connection pooler for AlloyDB Omni using the AlloyDB Omni Kubernetes operator.

Many applications, especially web applications, open and close database connections frequently, which can put a significant load on the database instance. PgBouncer helps reduce instance load by managing connections more efficiently. By reusing connections, PgBouncer minimizes the number of connections to the database instance, freeing up resources on the instance.

Create a PgBouncer service

The AlloyDB Omni Kubernetes operator lets you create a dedicated PgBouncer service for your database. You can then access the database through the PgBouncer service to benefit from connection pooling.

There is a dedicated custom resource definition (CRD) for configuring your PgBouncer service as needed.

To create a PgBouncer service for your database, follow these steps:

Create a PgBouncer custom resource in your Kubernetes cluster as follows:
```
 apiVersion 
 : 
  
 alloydbomni.dbadmin.goog/v1 
 kind 
 : 
  
 PgBouncer 
 metadata 
 : 
  
 name 
 : 
  
  PGBOUNCER_NAME 
 
 spec 
 : 
  
 dbclusterRef 
 : 
  
  DB_CLUSTER_NAME 
 
  
 allowSuperUserAccess 
 : 
  
 true 
  
 podSpec 
 : 
  
 resources 
 : 
  
 memory 
 : 
  
 1Gi 
  
 cpu 
 : 
  
 1 
  
 image 
 : 
  
 "gcr.io/alloydb-omni/operator/pgbouncer:1.23.1" 
  
 parameters 
 : 
  
 pool_mode 
 : 
  
  POOL_MODE 
 
  
 ignore_startup_parameters 
 : 
  
  IGNORE_STARTUP_PARAMETERS 
 
  
 default_pool_size 
 : 
  
  DEFAULT_POOL_SIZE 
 
  
 max_client_conn 
 : 
  
  MAXIMUM_CLIENT_CONNECTIONS 
 
  
 max_db_connections 
 : 
  
  MAXIMUM_DATABASE_CONNECTIONS 
 
  
 serviceOptions 
 : 
  
 type 
 : 
  
 "ClusterIP" 
```
Replace the following:
- PGBOUNCER_NAME : the name of your PgBouncer custom resource.
- DB_CLUSTER_NAME : the name of your AlloyDB Omni database cluster. It's the same database cluster name you declared when you created it .
- POOL_MODE : specifies when a database connection can be reused by other clients. Set the parameter to one of the following:
  - session : database connection is released back to the pool after client disconnects. Used by default.
  - transaction : database connection is released back to pool after transaction finishes.
  - statement : database connection is released back to pool after query finishes. Transactions spanning multiple statements are disallowed in this mode.
- IGNORE_STARTUP_PARAMETERS : specifies parameters that PgBouncer does not allow so that they are ignored during startup—for example, extra_float_digits . For more information, see PgBouncer configuration .
- DEFAULT_POOL_SIZE : the number of database connections to allow per user-database pair—for example, 8 .
- MAXIMUM_CLIENT_CONNECTIONS : the maximum number of client connections—for example, 800 .
- MAXIMUM_DATABASE_CONNECTIONS : the maximum number of database connections—for example, 160 .
Apply the manifest:
```
 kubectl  
apply  
-f  
 PATH_TO_MANIFEST 
  
-n  
 NAMESPACE 
 
```
Replace PATH_TO_MANIFEST with the path to your manifest file—for example, /fleet/config/pgbouncer.yaml .

To verify that the PgBouncer object you created is ready, execute the following query:

 kubectl  
get  
pgbouncers.alloydbomni.dbadmin.goog  
 PGBOUNCER_NAME 
  
-n  
 NAMESPACE 
  
-w

Replace NAMESPACE with the name of the Kubernetes namespace for your PgBouncer object.

The output is similar to the following:

 NAMESPACE   NAME          ENDPOINT        STATE
dbv2        mypgbouncer
dbv2        mypgbouncer
dbv2        mypgbouncer
dbv2        mypgbouncer                   WaitingForDeploymentReady
dbv2        mypgbouncer                   Acquiring IP
dbv2        mypgbouncer   10.138.15.231   Ready

Connect to the connection pooler endpoint

You can connect to the PgBouncer connection pooler from inside or outside a Kubernetes cluster.

Connect from within a Kubernetes cluster

To connect to the connection pooler endpoint using the psql client, follow these steps:

Create a Pod as follows:

  apiVersion 
 : 
  
 v1 
 kind 
 : 
  
 Pod 
 metadata 
 : 
  
 name 
 : 
  
 postgres 
 spec 
 : 
  
 containers 
 : 
  
 - 
  
 image 
 : 
  
 "docker.io/library/postgres:latest" 
  
 command 
 : 
  
 - 
  
 "sleep" 
  
 - 
  
 "604800" 
  
 name 
 : 
  
 db-client

Apply the manifest:

 kubectl  
apply  
-f  
 PATH_TO_MANIFEST 
  
-n  
 NAMESPACE

Connect to the containerized application:

 kubectl  
 exec 
  
-it  
postgres  
-n  
 NAMESPACE 
  
--  
bash

Verify the SSL connection to the PgBouncer endpoint using the psql client:
```
  export 
  
 PGSSLMODE 
 = 
 "require" 
 ; 
  
psql  
-h  
 HOST 
  
-d  
postgres  
-U  
postgres  
-p  
 PORT 
 
```
Replace the following:
- HOST : the connection pooler endpoint that you obtain using kubectl get pgbouncers.alloydbomni.dbadmin.goog -n NAMESPACE command. If PgBouncer isn't exposed as a service, use its IP address.
- PORT : the port PgBouncer listens on.
The terminal window displays psql login text that ends with a postgres=# prompt.

Connect from outside a Kubernetes cluster

To access the PgBouncer connection pooler from outside a Kubernetes cluster, set the type field in the serviceOptions attribute to LoadBalancer , which creates a loadbalancer service.

Create a PgBouncer custom resource in your Kubernetes cluster as follows:

 apiVersion 
 : 
  
 alloydbomni.dbadmin.goog/v1 
 kind 
 : 
  
 PgBouncer 
 metadata 
 : 
 name 
 : 
  
  PGBOUNCER_NAME 
 
 spec 
 : 
 dbclusterRef 
 : 
  
  DB_CLUSTER_NAME 
 
 allowSuperUserAccess 
 : 
  
 true 
 replicaCount 
 : 
  
 2 
 parameters 
 : 
  
 pool_mode 
 : 
  
  POOL_MODE 
 
  
 ignore_startup_parameters 
 : 
  
  IGNORE_STARTUP_PARAMETERS 
 
  
 default_pool_size 
 : 
  
  DEFAULT_POOL_SIZE 
 
  
 max_client_conn 
 : 
  
  MAXIMUM_CLIENT_CONNECTIONS 
 
  
 max_db_connections 
 : 
  
  MAXIMUM_DATABASE_CONNECTIONS 
 
 podSpec 
 : 
  
 resources 
 : 
  
 memory 
 : 
  
 1Gi 
  
 cpu 
 : 
  
 1 
  
 image 
 : 
  
 "gcr.io/alloydb-omni/operator/pgbouncer:1.23.1" 
  serviceOptions 
 : 
  
  type 
 : 
  
 "LoadBalancer"

Apply the manifest:

 kubectl  
apply  
-f  
 PATH_TO_MANIFEST

To verify that the PgBouncer object you created is ready, execute the following query:

 kubectl  
get  
pgbouncers.alloydbomni.dbadmin.goog  
 PGBOUNCER_NAME 
  
-n  
 NAMESPACE 
  
-w

The output is similar to the following:

 NAME          ENDPOINT       STATE
mypgbouncer   10.138.15.207   Ready

Configure PgBouncer settings

Use the following parameters to configure PGBouncer settings:

Parameter

Description

Default value

pool_mode

Specifies when a database connection can be reused by other clients.
Allowed values are as follows:

session : database connection is released back to the pool after client disconnects.
transaction : database connection is released back to pool after transaction finishes.
statement : database connection is released back to pool after query finishes.
Transactions spanning multiple statements are disallowed in this mode.

session

ignore_startup_parameters

Specifies parameters that PgBouncer does not allow so that they are ignored during startup.

default_pool_size

The number of database connections to allow per user-database pair.

20

max_client_conn

The maximum number of client connections.

100

max_db_connections

The maximum number of database connections.

0

Customize PgBouncer deployment

AlloyDB Omni uses custom resources to manage its components. To customize the PgBouncer deployment within your AlloyDB Omni on Kubernetes, you modify the PgBouncer custom resource as follows:

List the PgBouncer custom resources:
```
 kubectl  
get  
pgbouncers  
-n  
 NAMESPACE 
 
```
Replace NAMESPACE with the namespace where you deployed AlloyDB Omni.
To modify the resource, open the declaration file for the PgBouncer resource in your default editor:
```
 kubectl  
edit  
pgbouncers  
 PGBOUNCER_NAME 
  
-n  
 NAMESPACE 
 
```

In the declaration file, find the podSpec section that contains the configuration and modify any of the following fields as needed:

resources : the cpu and memory defined for your container:

  apiVersion 
 : 
  
 alloydbomni.dbadmin.goog/v1 
 kind 
 : 
  
 PgBouncer 
 metadata 
 : 
  
 name 
 : 
  
  PGBOUNCER_NAME 
 
 spec 
 : 
  
 dbclusterRef 
 : 
  
  DB_CLUSTER_NAME 
 
  
 replicaCount 
 : 
  
 2 
  
 parameters 
 : 
  
 pool_mode 
 : 
  
  POOL_MODE 
 
  
 ignore_startup_parameters 
 : 
  
  IGNORE_STARTUP_PARAMETERS 
 
  
 default_pool_size 
 : 
  
  DEFAULT_POOL_SIZE 
 
  
 max_client_conn 
 : 
  
  MAXIMUM_CLIENT_CONNECTIONS 
 
  
 max_db_connections 
 : 
  
  MAXIMUM_DATABASE_CONNECTIONS 
 
  
  podSpec 
 : 
  
  resources 
 : 
  
  memory 
 : 
  
 1Gi 
  
  cpu 
 : 
  
 1 
 ...

image : the path to the PgBouncer image tag:

  ... 
  
  podSpec 
 : 
  
 resources 
 : 
  
 memory 
 : 
  
 1Gi 
  
 cpu 
 : 
  
 1 
  
  image 
 : 
  
  IMAGE 
 
 ...

schedulingconfig : include the nodeaffinity section to control where the PgBouncer Pods are scheduled:
```
  ... 
  
  podSpec 
 : 
  
 resources 
 : 
  
 memory 
 : 
  
 1Gi 
  
 cpu 
 : 
  
 1 
  
 image 
 : 
  
  IMAGE 
 
  
  schedulingconfig 
 : 
  
  nodeaffinity 
 : 
  
   NODE_AFFINITY_TYPE 
 
 : 
  
  nodeSelectorTerms 
 : 
  
  - 
  
 matchExpressions 
 : 
  
  - 
  
 key 
 : 
  
  LABEL_KEY 
 
  
  operator 
 : 
  
  OPERATOR_VALUE 
 
  
  values 
 : 
  
  - 
  
 pgbouncer 
 ... 
 
```
Replace the following:
- NODE_AFFINITY_TYPE : set the parameter to one of the following:
  - requiredDuringSchedulingIgnoredDuringExecution : Kubernetes schedules the Pod based exactly on the defined rules.
  - preferredDuringSchedulingIgnoredDuringExecution : the Kubernetes scheduler tries to find a node that meets the defined rule for scheduling. However, if there is no such node, Kubernetes schedules to a different node in the cluster.
- LABEL_KEY : the node's label for the key that serves as a location indicator and facilitates even Pod distribution across the cluster—for example, nodetype .
- OPERATOR_VALUE : represents a key's relationship to a set of values. Set the parameter to one of the following:
  - In : the values array must be non-empty.
  - NotIn : the values array must be non-empty.
  - Exists : the values array must be empty.
  - DoesNotExist : the values array must be empty.
  - Gt : the values array must have a single element, which is interpreted as an integer.
  - Lt : the values array must have a single element, which is interpreted as an integer.

After making the changes, save the declaration file. The AlloyDB Omni Kubernetes operator automatically applies the changes to your PgBouncer deployment.

Delete the PgBouncer resource

To delete a PgBouncer custom resource, run the following command:

 kubectl  
delete  
pgbouncers.alloydbomni.dbadmin.goog  
 PGBOUNCER_NAME 
  
-n  
 NAMESPACE

The output is similar to the following:

 pgbouncer.alloydbomni.dbadmin.goog "mypgbouncer" deleted

View PgBouncer logs

You can view and analyze logs of any PgBouncer replica instance in your AlloyDB Omni deployment on Kubernetes as follows:

Get a list of all PgBouncer Pods in your namespace:

 kubectl  
get  
pods  
-n  
 NAMESPACE

The output is similar to the following:

 NAME                                          READY   STATUS    RESTARTS   AGE
al-092d-dbcluster-sample-0                    3/3     Running   0          3d1h
mypgbouncer-pgb-deployment-659869f95c-4kbgv   1/1     Running   0          27m

View logs for a particular Pod:

 kubectl  
logs  
-f  
 POD_NAME 
  
-n  
 NAMESPACE

The output is similar to the following:

 2025-01-21 06:57:39.549 UTC [7] LOG kernel file descriptor limit: 1048576 (hard: 1048576); max_client_conn: 800, max expected fd use: 812
2025-01-21 06:57:39.550 UTC [7] LOG listening on 0.0.0.0:6432
2025-01-21 06:57:39.550 UTC [7] LOG listening on [::]:6432
2025-01-21 06:57:39.550 UTC [7] LOG listening on unix:/tmp/.s.PGSQL.6432
2025-01-21 06:57:39.550 UTC [7] LOG process up: PgBouncer 1.23.0, libevent 2.1.12-stable (epoll), adns: evdns2, tls: OpenSSL 3.0.13 30 Jan 2024
2025-01-21 06:58:17.012 UTC [7] LOG C-0x55f2b1b322a0: (nodb)/(nouser)@10.138.15.215:48682 registered new auto-database: alloydbadmin
2025-01-21 06:58:17.012 UTC [7] LOG S-0x55f2b1b4ecb0: alloydbadmin/alloydbpgbouncer@10.138.0.48:5432 new connection to server (from 10.12.1.113:53156)
2025-01-21 06:58:17.042 UTC [7] LOG S-0x55f2b1b4ecb0: alloydbadmin/alloydbpgbouncer@10.138.0.48:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2025-01-21 06:58:17.052 UTC [7] LOG C-0x55f2b1b322a0: pgbouncer/statsuser@10.138.15.215:48682 login attempt: db=pgbouncer user=statsuser tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no
2025-01-21 06:58:19.526 UTC [7] LOG C-0x55f2b1b322a0: pgbouncer/statsuser@10.138.15.215:48682 closing because: client close request (age=2s)
2025-01-21 06:58:20.344 UTC [7] LOG C-0x55f2b1b322a0: pgbouncer/statsuser@10.138.15.215:46796 login attempt: db=pgbouncer user=statsuser tls=TLSv1.3/TLS_AES_256_GCM_SHA384 replication=no

Monitor PgBouncer performance and activity

You can view PgBouncer connection pool metrics using only a dedicated statsuser to access PgBouncer internal stats database. The statsuser is used for authentication when connecting to the PgBouncer database.

Connect to your AlloyDB Omni as a superuser or a user with the CREATE ROLE privilege using the psql client:

  export 
  
 PGPASSWORD 
 = 
 "ChangeMe123" 
 ; 
  
 export 
  
 PGSSLMODE 
 = 
 "require" 
 ; 
  
psql  
-h  
 HOST 
  
-d  
postgres  
-U  
postgres  
-p  
 PORT

The output is similar to the following:

 psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1), server 15.7)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

Create the statsuser in your AlloyDB Omni:

  postgres 
 = 
 # CREATE USER "statsuser" WITH PASSWORD 'tester';

The output is similar to the following:

 CREATE ROLE
postgres=#

Connect to the PgBouncer database as the statsuser :

  export 
  
 PGPASSWORD 
 = 
 "ChangeMe123" 
 ; 
  
 export 
  
 PGSSLMODE 
 = 
 "require" 
 ; 
  
psql  
-h  
 HOST 
  
-d  
pgbouncer  
-U  
statsuser  
-p  
 PORT

The output is similar to the following:

 psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1), server 1.23.0/bouncer)
WARNING: psql major version 16, server major version 1.23.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

Run the SHOW STATS command to view PgBouncer performance and identify potential issues:

  pgbouncer 
 = 
 # SHOW STATS;

The output is similar to the following:

 database   | total_server_assignment_count | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_server_assignment_count | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
   -------------+-------------------------------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+-----------------------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
   alloydbadmin |                             1 |                0 |                 0 |              0 |        330 |               0 |                0 |           41730 |                           0 |              0 |               0 |        0 |        2 |             0 |              0 |         41730
   pgbouncer    |                             0 |                5 |                 5 |              0 |          0 |               0 |                0 |               0 |                           0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
   (2 rows)

Disable the PgBouncer connection pooler

If you need to deactivate or rollback the PgBouncer connection pooler, follow these steps:

Check the status of the connection pooler:

 kubectl  
get  
deployment  
fleet-controller-manager  
--namespace  
alloydb-omni-system  
-o  
json  
 | 
  
jq  
 '.spec.template.spec.containers[0].args'

This command shows the deployment manifest of the primary controller for managing the AlloyDB Omni fleet. Find the --enable-pgbouncer option in the args section of the containers array:

  ... 
  
 spec 
 : 
  
 containers 
 : 
  
 - 
  
 name 
 : 
  
 fleet-controller-manager 
  
 image:... 
  
 args 
 : 
  
 --health-probe-bind-address=:8081", 
  
 --metrics-bind-address=127.0.0.1:8080", 
  
 --leader-elect", 
  
 --image-registry=gcr.io", 
  
 --data-plane-image-repository=alloydb-omni-staging", 
  
 --control-plane-agents-image-repository=aedi-gbox", 
  
 --control-plane-agents-tag=jan19v3", 
  
 --additional-db-versions-for-test-only=latest", 
  
 --enable-multiple-backup-solutions=true", 
  
  --enable-pgbouncer=true

Edit the configuration of the controller deployment to disable the connection pooler:

 kubectl  
edit  
deployment  
fleet-controller-manager  
--namespace  
alloydb-omni-system

In the deployment manifest, change the value of the --enable-pgbouncer option from true to false :
```
  ... 
  --enable-pgbouncer=false 
 
```
Save the file and exit the editor. kubectl automatically applies the change.

What's next

Manage and monitor AlloyDB Omni

Use the PgBouncer connection pooler Stay organized with collections Save and categorize content based on your preferences.

Create a PgBouncer service

Connect to the connection pooler endpoint

Connect from within a Kubernetes cluster

Connect from outside a Kubernetes cluster

Configure PgBouncer settings

Customize PgBouncer deployment

Delete the PgBouncer resource

View PgBouncer logs

Monitor PgBouncer performance and activity

Disable the PgBouncer connection pooler

What's next

Use the PgBouncer connection pooler