Updating PgBouncer to a newer version is only the first part of a security upgrade. The next step is confirming that applications can still connect, queries work correctly, pooling behaves as expected, and no new errors appear.

A good post-upgrade test should cover both security-related configuration and normal database operations.

Start With the Version Check

First, confirm that the expected PgBouncer version is actually running.

pgbouncer --version

For a containerized deployment:

docker exec pgbouncer pgbouncer --version

If PgBouncer runs in Kubernetes, check the running container rather than only checking the deployment manifest.

The goal is to verify the version of the process serving production traffic.

Check Whether PgBouncer Is Running

After the upgrade, verify that the service is healthy.

On a Linux system:

systemctl status pgbouncer

You can also check whether PgBouncer is listening on the expected port:

ss -lntp | grep 6432

A healthy basic state should look like:

PgBouncer
   |
   +-- Process running
   +-- Expected port listening
   +-- Configuration loaded

A running process, however, does not prove that applications can successfully use the connection pool.

Test a PostgreSQL Connection Through PgBouncer

The next test should go through PgBouncer rather than connecting directly to PostgreSQL.

For example:

psql \
  -h localhost \
  -p 6432 \
  -U app_user \
  -d application_db

Once connected, run a simple query:

SELECT current_database();

Then check the connection:

SELECT current_user;

These simple checks confirm that the application credentials and basic database routing are working.

Test Authentication

Authentication should be tested explicitly after the upgrade.

Test with:

Valid username + valid password
Valid username + invalid password
Unknown username

The expected behavior is:

Valid Credentials
      |
      v
Connection Allowed

Invalid Credentials
      |
      v
Connection Rejected

Do not test only successful authentication. A security update should also confirm that invalid credentials are still rejected.

Test Basic CRUD Operations

A connection that succeeds is not enough.

Run representative database operations.

Insert

INSERT INTO orders
    (customer_id, status)
VALUES
    (101, 'Pending');

Read

SELECT
    customer_id,
    status
FROM orders
WHERE customer_id = 101;

Update

UPDATE orders
SET status = 'Completed'
WHERE customer_id = 101;

Delete

Use a controlled test record if deletion needs to be tested:

DELETE FROM orders
WHERE customer_id = 101;

Production tests should use appropriate test data and should not modify real customer records unnecessarily.

Test Transactions

Transaction behavior is particularly important when PgBouncer uses transaction pooling.

A simple transaction test can be performed with:

BEGIN;

INSERT INTO orders
    (customer_id, status)
VALUES
    (200, 'Testing');

UPDATE orders
SET status = 'Completed'
WHERE customer_id = 200;

COMMIT;

Then verify the result:

SELECT *
FROM orders
WHERE customer_id = 200;

Also test rollback:

BEGIN;

UPDATE orders
SET status = 'Failed'
WHERE customer_id = 200;

ROLLBACK;

Then verify that the previous value remains.

Check the Pooling Mode

Confirm that the configured pooling mode has not changed unexpectedly.

For example:

pool_mode = transaction

The common modes are:

Pool Mode

Connection Returned

Session

When the client session ends

Transaction

When the transaction ends

Statement

After each statement

The correct choice depends on the application.

Do not change the pooling mode simply because you are upgrading PgBouncer.

Test Session-Dependent Features

Applications can sometimes depend on PostgreSQL session state.

Examples include:

With transaction pooling, a client may not keep the same PostgreSQL connection between transactions.

Therefore, applications that depend on session state should be tested carefully.

For example:

SET application_name = 'test-client';

Then verify the behavior expected by the application.

The exact behavior depends on the pooling mode and application design.

Check PgBouncer Pool Statistics

PgBouncer provides administrative commands that help inspect connection behavior.

For example:

SHOW POOLS;

You can also inspect general statistics:

SHOW STATS;

Look for values related to:

Client connections
Server connections
Waiting clients
Transactions
Requests

A pool with a large number of waiting clients may indicate that the configured pool size is not sufficient for the workload.

Do not change pool sizes based on one observation. Compare the results with normal workload patterns.

Check PostgreSQL Connections

PgBouncer should reduce unnecessary PostgreSQL connection overhead, so inspect the database after testing.

SELECT
    state,
    count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY state;

You can also check total connections:

SELECT count(*)
FROM pg_stat_activity;

Compare these values with the application's normal behavior.

Test Application Startup

The database client test is useful, but the application itself should also be tested.

For example:

Application
     |
     v
PgBouncer
     |
     v
PostgreSQL

Restart the application in the test environment and verify:

  1. Application starts successfully.

  2. Database connection succeeds.

  3. Authentication succeeds.

  4. Normal queries work.

  5. Transactions work.

  6. Application shutdown is clean.

This can reveal configuration problems that a simple psql test will not catch.

Test Connection Pool Exhaustion

A useful test is to determine what happens when the application approaches its configured connection limit.

For example:

100 Client Connections
        |
        v
PgBouncer
        |
        +-- 20 Server Connections

If the server pool is full, additional clients may need to wait.

Monitor:

SHOW POOLS;

The purpose is not to force production into failure.

Instead, perform a controlled test in a non-production environment to understand how the system behaves under connection pressure.

Check Logs

Review PgBouncer logs after the upgrade.

Look for:

Authentication failures
Connection failures
Database connection errors
TLS errors
Pool exhaustion
Unexpected disconnects
Configuration errors

Application logs should also be reviewed.

For example:

Application
   |
   +-- Database timeout
   +-- Authentication failure
   +-- Connection reset

A successful deployment should not be judged only by whether the service starts.

Test TLS If Enabled

If PgBouncer uses TLS, test both the connection and certificate configuration.

For example:

psql "host=localhost port=6432 dbname=application_db user=app_user sslmode=require"

Verify that:

The exact test depends on the TLS setup.

Test Failover and Recovery

If PostgreSQL has a high-availability setup, test what happens when the database endpoint changes.

The general flow is:

PgBouncer
   |
   v
Primary PostgreSQL
   |
   X
   |
   v
Recovery / New Primary

The test should verify that the application can recover according to the architecture's expected behavior.

Do not perform failover tests against production unless they are part of an approved operational procedure.

Common Testing Mistakes

Testing Only psql

A successful manual connection does not prove that the application works correctly.

Checking Only the PgBouncer Process

A running process can still have authentication or database connectivity problems.

Ignoring Transaction Behavior

Pooling changes can expose application assumptions about database sessions.

Testing Only Successful Authentication

Security testing should also confirm that invalid credentials are rejected.

Changing Configuration During Testing

Keep the security upgrade test focused. Otherwise, it becomes difficult to identify which change caused a problem.

Recommended Post-Upgrade Test Plan

Use the following sequence:

1. Verify Version
       |
       v
2. Check Service
       |
       v
3. Test Authentication
       |
       v
4. Test PostgreSQL Connection
       |
       v
5. Test CRUD Operations
       |
       v
6. Test Transactions
       |
       v
7. Check Pool Statistics
       |
       v
8. Test Application
       |
       v
9. Review Logs
       |
       v
10. Monitor Production

This provides progressively deeper validation without immediately putting unnecessary load on the production database.

Post-Upgrade Checklist

[ ] Expected PgBouncer version is running
[ ] PgBouncer service is healthy
[ ] Expected port is listening
[ ] Valid credentials work
[ ] Invalid credentials are rejected
[ ] PostgreSQL connection works
[ ] SELECT operations work
[ ] INSERT operations work
[ ] UPDATE operations work
[ ] Transactions work
[ ] Rollback works
[ ] Pool mode is correct
[ ] Pool statistics look normal
[ ] PostgreSQL connection count is normal
[ ] Application starts successfully
[ ] Application database operations work
[ ] Logs show no unexpected errors
[ ] TLS works if enabled
[ ] Recovery behavior has been tested where applicable

Advantages of Post-Upgrade Testing

Limitations

Testing cannot guarantee that every possible production scenario will work.

Real workloads can contain:

For this reason, post-upgrade testing should be combined with monitoring after deployment.

Conclusion

Testing PgBouncer after a security update should cover more than checking whether the service starts.

Start with the version and service status, then verify authentication, database connectivity, CRUD operations, transactions, pooling behavior, application compatibility, and logs.

A simple connection test confirms that the basic path works:

Application
    |
    v
PgBouncer
    |
    v
PostgreSQL

A complete post-upgrade test confirms that the entire path continues to behave correctly under realistic application operations.

The goal is to verify both security and functionality before considering the upgrade complete.