Use the PgBouncer connection pooler

Select a documentation version:

This page describes how to configure and bootstrap the PgBouncer connection pooler for an AlloyDB Omni cluster using the AlloyDB Omni orchestrator.

Before you begin

Make sure that you have a functioning AlloyDB Omni cluster deployed and that the orchestrator is installed on your control node. You must also make sure that port 6432 is open on your load balancer or database nodes to allow incoming traffic to PgBouncer.

Prepare resource specification

Create a resource specification file, for example, pgbouncer_spec.yaml that defines the PgBouncer resource. This file describes the connection pooler and its association with your database cluster.

PgBouncer:
  metadata:
    name: PGBOUNCER_RESOURCE_NAME
  spec:
    dbclusterRef: CLUSTER_NAME
    allowSuperUserAccess: ALLOW_SUPERUSER_ACCESS
    serverTLS:
      certSecret:
        name: CERT_SECRET_NAME

Replace the following:

  • PGBOUNCER_RESOURCE_NAME: the name of your PgBouncer resource.
  • CLUSTER_NAME: the name of your AlloyDB Omni database cluster. Use the same name you declared when you bootstrapped your cluster.
  • ALLOW_SUPERUSER_ACCESS: specifies whether to allow superuser access to the database. Set this to true or false.
  • CERT_SECRET_NAME: the name of the certificate secret. The secret must contain entries for the ca.crt CA certificate, the tls.key private key, and the tls.crt leaf certificate.
    • The values in this secret are used to populate the server_tls_ca_file, server_tls_cert_file, and server_tls_key_file in the pgbouncer.ini configuration.
    • The CA certificate must match the CA that signed the database cluster's leaf certificate.
    • The leaf certificate must contain the Common Name alloydbpgbouncer.

Bootstrap PgBouncer

To bootstrap the PgBouncer connection pooler, use Ansible or the AlloyDB Omni orchestrator CLI (alloydbctl).:

Ansible

To bootstrap PgBouncer using Ansible, you use a playbook that uses the google.alloydbomni_orchestrator.bootstrap role:

  1. Create an Ansible playbook, for example, bootstrap_pgbouncer.yaml, that points to your Ansible inventory and resource specification: 
    - name: Bootstrap PgBouncer
  hosts: all
  vars:
    ansible_user: SSH_USER
    ansible_ssh_private_key_file: PATH_TO_KEY
  roles:
   - role: google.alloydbomni_orchestrator.bootstrap

    Replace the following:

    • SSH_USER: the username that you use to connect to the nodes in your cluster using SSH.
    • PATH_TO_KEY: the local path to the SSH private key file used to connect to your node.
  2. Run the playbook from your control node, passing the resource specification as an extra variable: 
    ansible-playbook -i inventory.yaml bootstrap_pgbouncer.yaml \
    -e resource_spec=pgbouncer_spec.yaml

alloydbctl

You can use the alloydbctl command-line utility to apply the PgBouncer configuration directly.

To apply the configuration, execute the apply command and specify both your Ansible inventory and the PgBouncer resource specification:

alloydbctl apply -d deployment_spec.yaml -r pgbouncer_spec.yaml

Verify the deployment

Choose the interface you want to use to verify the status and configuration of your PgBouncer resource:

Ansible

To verify the deployment using Ansible, follow these steps:

  1. Create a verification playbook, for example, verify_pgbouncer.yaml, that uses the google.alloydbomni_orchestrator.status role:

    - name: Verify PgBouncer
  hosts: localhost
  connection: local
  vars:
    resource_type: PgBouncer
    resource_name: PGBOUNCER_RESOURCE_NAME
  roles:
    - role: google.alloydbomni_orchestrator.status

  2. Run the playbook from your control node, passing your inventory file:

    ansible-playbook -i inventory.yaml verify_pgbouncer.yaml

alloydbctl

You can use the get command to retrieve the status of your PgBouncer resource:

alloydbctl get -t PgBouncer -n PGBOUNCER_RESOURCE_NAME -d deployment_spec.yaml

Replace PGBOUNCER_RESOURCE_NAME with the name of your PgBouncer resource.

Delete a PgBouncer connection pooler

You can delete the PgBouncer connection pooler using Ansible or the AlloyDB Omni orchestrator command-line interface (CLI):

Ansible

To delete a PgBouncer connection pooler using Ansible, follow these steps:

  1. Create an Ansible playbook—for example, teardown.yaml, that uses the google.alloydbomni_orchestrator.delete role:

    - name: Delete PgBouncer
  hosts: localhost
  vars:
    ansible_become: true
    ansible_user: SSH_USER
    ansible_ssh_private_key_file: PATH_TO_KEY
  roles:
    - role: google.alloydbomni_orchestrator.delete

  2. Run the playbook using the ansible-playbook command. You must pass the following resource_name:

    ansible-playbook -i "DEPLOYMENT_SPEC" teardown.yaml \
    -e resource_type=PgBouncer -e resource_name="PGBOUNCER_RESOURCE_NAME"

alloydbctl

To delete your PgBouncer resource, run the alloydbctl delete command:

alloydbctl delete -d "DEPLOYMENT_SPEC" -t PgBouncer -n "PGBOUNCER_RESOURCE_NAME"