Use the PgBouncer connection pooler

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

For information about access to this release, see the access request page.

Select a documentation version: 18.1.0

• Current (18.1.0)
• 18.1.0

This page describes how to configure and bootstrap the PgBouncer connection pooler for an AlloyDB Omni cluster using the RPM 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

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.

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 deployment specification and resource specification:

   - name: Bootstrap PgBouncer
     hosts: localhost
     vars:
       ansible_become: true
       ansible_user: SSH_USER
       ansible_ssh_private_key_file: SSH_KEY_PATH
     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.
   • SSH_KEY_PATH: 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 PGBOUNCER_BOOTSTRAP_PLAYBOOK -i "DEPLOYMENT_SPECIFICATION" \
       -e resource_spec="PGBOUNCER_SPECIFICATION"

   Replace the following variables:

   • PGBOUNCER_BOOTSTRAP_PLAYBOOK: the path to the playbook that you created to bootstrap PgBouncer.
   • DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.
   • PGBOUNCER_SPECIFICATION: the path to the resource specification you created in Prepare resource specification.

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 deployment specification and the PgBouncer resource specification:

alloydbctl apply -d "DEPLOYMENT_SPECIFICATION" -r "PGBOUNCER_SPECIFICATION"

Replace the following variables:

• DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.
• PGBOUNCER_SPECIFICATION: the path to the resource specification you created in Prepare resource specification.

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
     vars:
       ansible_become: true
       ansible_user: SSH_USER
       ansible_ssh_private_key_file: SSH_KEY_PATH
     roles:
       - role: google.alloydbomni_orchestrator.status

   Replace the following:

   • SSH_USER: the username that you use to connect to the nodes in your cluster using SSH.
   • SSH_KEY_PATH: the local path to the SSH private key file used to connect to your node.

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

   ansible-playbook PGBOUNCER_VERIFY_PLAYBOOK -i "DEPLOYMENT_SPECIFICATION" \
       -e resource_type=PgBouncer -e resource_name="PGBOUNCER_RESOURCE_NAME"

   Replace the following variables:

   • PGBOUNCER_VERIFY_PLAYBOOK: the path to the playbook that you created to verify the PgBouncer deployment.
   • DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.
   • PGBOUNCER_RESOURCE_NAME: the name of your PgBouncer resource.

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_SPECIFICATION"

Replace the following variables:

• PGBOUNCER_RESOURCE_NAME: the name of your PgBouncer resource.
• DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.

Verify the connection pooler

After the RPM orchestrator completes the setup, verify that you can connect to the database cluster through the connection pooler:

psql -h VIRTUAL_IP -p 6432 -U postgres

Replace VIRTUAL_IP with the virtual IP address that you configured when you created a cluster.

PgBouncer routes your connection to the appropriate database node.

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: SSH_KEY_PATH
     roles:
       - role: google.alloydbomni_orchestrator.delete

   Replace the following:

   • SSH_USER: the username that you use to connect to the nodes in your cluster using SSH.
   • SSH_KEY_PATH: the local path to the SSH private key file used to connect to your node.

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

   ansible-playbook PGBOUNCER_DELETE_PLAYBOOK -i "DEPLOYMENT_SPECIFICATION" \
       -e resource_type=PgBouncer -e resource_name="PGBOUNCER_RESOURCE_NAME"

   Replace the following variables:

   • PGBOUNCER_DELETE_PLAYBOOK: the path to the playbook that you created to delete the PgBouncer connection pooler.
   • DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.
   • PGBOUNCER_RESOURCE_NAME: the name of your PgBouncer resource.

alloydbctl

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

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

Replace the following variables:

• DEPLOYMENT_SPECIFICATION: the path to the deployment specification that you created in Install AlloyDB Omni components.
• PGBOUNCER_RESOURCE_NAME: the name of your PgBouncer resource.