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:
Application starts successfully.
Database connection succeeds.
Authentication succeeds.
Normal queries work.
Transactions work.
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 client can establish the connection.
The expected certificate configuration is being used.
Invalid TLS configurations are rejected as expected.
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
Confirms that the new version is actually running.
Detects authentication problems early.
Identifies application compatibility issues.
Verifies connection pooling behavior.
Helps detect unexpected database connection growth.
Provides confidence before normal production traffic resumes.
Limitations
Testing cannot guarantee that every possible production scenario will work.
Real workloads can contain:
Different connection patterns
Higher traffic
Long-running transactions
Unusual queries
Application-specific session behavior
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.
Join the conversation! Your thoughts help the community grow.