Skip to main content

Access PgBouncer statistics for Aiven for PostgreSQL®

PgBouncer is used at Aiven as a connection pooler to lower the performance impact of opening new connections to Aiven for PostgreSQL®.

After connecting to PgBouncer, you can display statistics available from PgBouncer, such as:

  • total_xact_count
  • total_query_count
  • total_received
  • total_sent
  • total_xact_time
  • total_query_time
  • total_wait_time
  • avg_xact_count
  • avg_query_count
  • avg_recv
  • avg_sent
  • avg_xact_time
  • avg_query_time
  • avg_wait_time
note

You have the read-only access to PgBouncer statistics since PgBouncer pools are automatically managed by Aiven.

Get PgBouncer URI​

To get the PgBouncer URI, you can use either the Aiven Console or the Aiven CLI client.

PgBouncer URI in the console​

  1. Log in to the Aiven Console, and go to a desired organization, project, and service.
  2. Click Connection pools, and find a desired pool.
  3. Click Actions > Info > Primary Connection URI.

PgBouncer URI with the Aiven CLI​

Use jq to parse the JSON response. Execute the following command replacing SERVICE_NAME and PROJECT_NAME as needed:

avn service get SERVICE_NAME --project PROJECT_NAME --json | jq -r '.connection_info.pgbouncer'

Expect to receive an output similar to the following:

postgres://avnadmin:xxxxxxxxxxx@demo-pg-dev-advocates.aivencloud.com:13040/pgbouncer?sslmode=require

Connect to PgBouncer​

To connect to PgBouncer, use the extracted URI:

psql 'EXTRACTED_PGBOUNCER_URI'
Troubleshoot connection issues

Password encryption migration to SCRAM and compatibility with PGBouncer​

Aiven for PostgreSQL now defaults to scram-sha-256 password encryption for enhanced security. MD5 password encryption will be deprecated in future PostgreSQL versions.

Database users managed by Aiven can be upgraded from MD5 with a single button.

Organizations with existing PGBouncer pools may need to take action to ensure compatibility. If you have PGBouncer connection pools configured and are experiencing authentication issues, this may be related to password encryption methods.

Organization with database users that aren't managed by Aiven can follow the guidance below to re-hash their passwords.

Who is affected​

  • Organizations with PGBouncer connection pools that are tied to specific database users
  • Services that have created additional database users

Update your user config to enforce scram-sha-256 for your service​

Update the password encryption value in your service's user_config:

{
  "pg": {
    "password_encryption": "scram-sha-256"
  }
}

This maintains MD5 compatibility: you may re-hash the password (shown below) at a later point. New managed users' password will be hashed and authenticated using scram-sha-256.

Re-hash database user password to upgrade to scram-sha-256​

Re-hash user passwords: Existing passwords supported by MD5 need to be re-hashed to use the new encryption.

This can be done using the following SQL statement:

ALTER ROLE <rolename> PASSWORD 'new_password';

Here is example Python code to list all database users and upgrade them to SCRAM:

# Use avn-client to fetch the avnadmin service user connection details
# Then provide a script that can be run using uv to pack all dependencies

Update your pgBouncer connection pool configuration​

When connection pools are configured with specific user names, attempting to connect using another role will fail with a permission denied error.

This is due to the challenge-response flow initiated by the PG client, that ensures

Troubleshooting connection issues​

If you experience authentication failures:

  1. Check client library support: Ensure your PostgreSQL client supports scram-sha-256
  2. Verify PGBouncer pool configuration: Check
  3. Review connection logs: Look for authentication method mismatches

Need help?​

Contact Aiven support if you need assistance with:

  • PGBouncer configuration updates
  • Client library compatibility
  • Migration planning for large deployments

Access statistics​

  1. Enable the expanded display by running:

    pgbouncer=# \x

  2. Show the statistics by running:

    pgbouncer=# SHOW STATS;

Depending on the load of your database, expect an output similar to the following:

database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
pgbouncer |                1 |                 1 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
(1 row)
tip

Run SHOW HELP to see all pgbouncer commands.