PostgreSQL Connection Pooling with PgBouncer on FreeBSD

Why PgBouncer helps a busy PostgreSQL server

If your application opens too many PostgreSQL sessions at once, the database spends more time managing connections than running queries. PostgreSQL connection pooling with PgBouncer reduces that pressure by keeping a small, reusable pool of backend sessions and handing clients to the pool as needed.

This tutorial shows how to deploy PgBouncer on FreeBSD, point it at an existing PostgreSQL server, and verify that it holds up under real traffic spikes. It is a practical fit for smaller SaaS apps, agency sites, and staging environments that need steadier database response times without buying a larger database host right away. If you are planning a broader VPS buildout, Hostperl VPS hosting at Hostperl VPS hosting is a good match for this kind of application layer.

We will use FreeBSD because it works well for service isolation, jails, and pf-based network control. The steps below are specific to FreeBSD 14.x, PostgreSQL 15/16, and PgBouncer 1.20 or newer from the FreeBSD packages repository.

What you need before you start

• A FreeBSD 14.x server with root SSH access.
• An existing PostgreSQL server reachable from the FreeBSD host.
• A non-root administrator account for day-to-day changes.
• Open network paths for PgBouncer on port 6432 and PostgreSQL on port 5432 where required.

Connect to the FreeBSD server and identify the OS

On your local computer, start with the documented SSH example. Replace 203.0.113.10 with the public IP assigned to your server.

ssh root@203.0.113.10

That address is only an example. If your provider gave you a default non-root account, connect with that account first, then switch to root with su -.

On the VPS as root, confirm the FreeBSD release.

freebsd-version

You should see a FreeBSD 14.x release string. If you do not, stop here and follow the matching FreeBSD branch for your version.

Create a non-root administrator for maintenance

Keep the root session open until the new login works. That way you avoid locking yourself out if you mistype a permission or SSH setting.

On the VPS as root, create the admin account, add it to wheel, and set a password for initial access.

adduser deploy

Follow the prompts and set the login shell to /bin/sh. When asked for membership, add the user to wheel. If you prefer to script it instead of using prompts, you can create the account and set its password directly:

pw useradd deploy -m -G wheel -s /bin/sh

passwd deploy

On the VPS as root, prepare SSH access for the new user.

mkdir -p /home/deploy/.ssh
chmod 700 /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh
chmod 600 /home/deploy/.ssh/authorized_keys

If root uses a different key path, replace the source file with the actual authorized key file. The permissions above matter; a loose .ssh directory or key file causes SSH to ignore the key.

On your local computer, open a second terminal and test the new login. Keep the root session open until this works.

ssh deploy@203.0.113.10

After login, verify privilege escalation.

sudo -i

You should land in a root shell after entering the password for deploy. If that fails, check group membership with id deploy and confirm the user is in wheel.

Install PgBouncer and PostgreSQL client tools

On the VPS as root, update package metadata and install the required packages.

pkg update
pkg install -y pgbouncer postgresql16-client

Use the client package version that matches your environment as closely as possible. The PostgreSQL client tools are useful for testing connectivity and running smoke checks from the PgBouncer host.

Confirm the installed version.

pgbouncer -V
psql --version

You should see PgBouncer and psql version output. If pgbouncer is missing, re-run pkg info pgbouncer and check the package repository status.

Create PgBouncer configuration and user list

PgBouncer needs three main pieces: a database mapping, an authentication file, and a service configuration. In this example, PgBouncer listens on the FreeBSD host at port 6432 and forwards to PostgreSQL on 203.0.113.10 port 5432. Replace 203.0.113.10 with your real database server address if your PostgreSQL server lives elsewhere.

On the VPS as root, create a dedicated service user and working directory.

pw useradd pgbouncer -m -s /usr/sbin/nologin

mkdir -p /var/db/pgbouncer
chown -R pgbouncer:pgbouncer /var/db/pgbouncer

On the VPS as root, create the user authentication file.

cat > /var/db/pgbouncer/userlist.txt <<'EOF'
"appuser" "SCRAM-SHA-256$REPLACE_WITH_REAL_HASH"
EOF
chown pgbouncer:pgbouncer /var/db/pgbouncer/userlist.txt
chmod 600 /var/db/pgbouncer/userlist.txt

Replace appuser with the PostgreSQL username your application uses. PgBouncer can store plain MD5 or SCRAM entries, but you should use the same password format your PostgreSQL server already uses. If you need to generate a hash, do that on the database side and copy the exact line into this file.

On the VPS as root, create the main PgBouncer configuration.

cat > /usr/local/etc/pgbouncer.ini <<'EOF'
[databases]
appdb = host=203.0.113.10 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /var/db/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
ignore_startup_parameters = extra_float_digits
admin_users = deploy
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
unix_socket_dir = /var/run/pgbouncer
EOF

Create the log directory and fix ownership.

mkdir -p /var/log/pgbouncer
chown -R pgbouncer:pgbouncer /var/log/pgbouncer
chown -R pgbouncer:pgbouncer /usr/local/etc/pgbouncer.ini

These settings are conservative. transaction pooling works well for web apps that do not depend on long-lived session state. If your app uses prepared statements heavily or needs session affinity, review the application behavior before using PgBouncer in transaction mode.

Enable PostgreSQL connection pooling with PgBouncer safely

On the VPS as root, add PgBouncer to rc.conf.

sysrc pgbouncer_enable="YES"
sysrc pgbouncer_config="/usr/local/etc/pgbouncer.ini"

Before starting the service, test the config file syntax by running PgBouncer in verbose config-check mode.

su -m pgbouncer -c 'pgbouncer -t /usr/local/etc/pgbouncer.ini'

A successful check returns silently or with a short confirmation. Fix any syntax errors before moving on. Common problems include missing quotes around database names and wrong file ownership on the auth file.

On the VPS as root, start the service.

service pgbouncer start

Then confirm that it is running and listening.

service pgbouncer status
sockstat -4 -l | grep 6432

You should see the PgBouncer process and a listener on port 6432. If the service fails immediately, check the log file next.

tail -n 50 /var/log/pgbouncer/pgbouncer.log

Open the firewall and keep the change reversible

If the host uses pf, add a rule for PgBouncer before closing any older paths. That keeps the change safe while you test connectivity.

On the VPS as root, edit /etc/pf.conf.

ee /etc/pf.conf

Add a rule similar to this in the appropriate anchor or rule section:

pass in on egress proto tcp from any to any port 6432 keep state

Save and exit the editor, then test the pf configuration before reloading it.

pfctl -nf /etc/pf.conf

If the syntax check passes, load the rules.

service pf reload

If you are temporarily allowing PostgreSQL direct access for testing, keep the 5432 rule in place until PgBouncer is confirmed functional. Remove the old path only after the application is switched over.

Test PostgreSQL access through PgBouncer

On the VPS as the non-root sudo user, connect with psql through PgBouncer. Replace the database name and user with the values from your deployment.

psql -h 127.0.0.1 -p 6432 -U appuser -d appdb

If the database is remote, use the PgBouncer host address instead of 127.0.0.1. A working connection should land you at the PostgreSQL prompt. Run a simple query to verify the path end to end.

SELECT now();
SELECT current_user;
\q

Next, confirm that PgBouncer is actually pooling connections instead of opening one backend session per client. Connect to the PgBouncer admin database and inspect active pools.

psql -h 127.0.0.1 -p 6432 -U deploy -d pgbouncer

Then run:

SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;

Healthy output should show a small number of server connections compared with client connections during tests. If server connections grow too quickly, review the pool mode and your application behavior.

Point your application at PgBouncer

Switch application connection strings from PostgreSQL port 5432 to PgBouncer port 6432. Keep the same database name, username, and password unless you are changing credentials at the same time.

For a web application on the same host, the new connection string often looks like this:

postgresql://appuser:REDACTED@127.0.0.1:6432/appdb

That is usually the only change your app needs. If your application uses its own connection pool, reduce the pool size so you do not stack multiple pools on top of each other. Two pools fighting each other is a common cause of wasted connections.

For practical production maintenance, pair this setup with safer update habits. Hostperl’s Linux package updates on VPS guide is a useful companion when you schedule service restarts and OS patching around peak traffic.

Restart behavior, logs, and rollback

After the app cutover, monitor the PgBouncer log and the database server log for failed logins, SSL mismatches, or pool exhaustion.

tail -f /var/log/pgbouncer/pgbouncer.log

If you need to roll back, point the application back at PostgreSQL port 5432 and restart the app service. PgBouncer itself can stay installed; leaving it in place gives you a fast recovery path for the next traffic spike.

To stop PgBouncer cleanly during rollback testing:

service pgbouncer stop

Then confirm the port is closed.

sockstat -4 -l | grep 6432

Common failures and how to fix them

Authentication fails with SCRAM or MD5 errors. Check the username and hash in /var/db/pgbouncer/userlist.txt.

cat /var/db/pgbouncer/userlist.txt

If the hash format does not match PostgreSQL’s password method, regenerate it on the database side and update the file.

PgBouncer starts but clients cannot connect. Check the listener and firewall.

sockstat -4 -l | grep 6432
pfctl -sr | grep 6432

If the port is missing, review the PgBouncer config and the pf rule. If the port is present but blocked, reload pf after a syntax check.

Connections reach PgBouncer but queries hang. Look for exhausted server pools or backend rejects.

psql -h 127.0.0.1 -p 6432 -U deploy -d pgbouncer -c 'SHOW POOLS;'
psql -h 127.0.0.1 -p 6432 -U deploy -d pgbouncer -c 'SHOW SERVERS;'

If all server slots are busy, raise default_pool_size carefully or reduce application concurrency. Do not increase limits blindly; first confirm whether the app is holding transactions open for too long.

The service fails after reboot. Check that the service is enabled and that file permissions survived startup.

service pgbouncer status
ls -ld /var/db/pgbouncer /var/log/pgbouncer
ls -l /var/db/pgbouncer/userlist.txt

Fix ownership with chown and restart the service if needed.

Final verification checklist

Finish by testing the full path from a client and from the server.

On the VPS as root, confirm the service and listener.

service pgbouncer status
sockstat -4 -l | grep 6432

On the VPS as the non-root sudo user, run a real query through PgBouncer.

psql -h 127.0.0.1 -p 6432 -U appuser -d appdb -c 'SELECT version();'

On your local computer, test the application endpoint that depends on the database. A simple smoke test should show a live page, a successful API response, or a checkout/cart action, depending on your app.

For database-driven workloads on Hostperl infrastructure, this pattern works well on a right-sized VPS or a dedicated database host. If your application is growing, managed VPS hosting and dedicated server hosting are natural next steps when you need more predictable connection capacity.

FAQ

Does PgBouncer replace PostgreSQL?

No. PgBouncer sits in front of PostgreSQL and reduces connection overhead. Your database server still handles storage, queries, and permissions.

Should I use transaction pooling for every app?

No. Transaction pooling fits many web apps, but not workloads that depend on session state, session-level prepared statements, or temporary state across transactions.

Can I run PgBouncer and PostgreSQL on the same FreeBSD host?

Yes. Many small deployments do this. Just keep the firewall rules clear and size the pool conservatively so PgBouncer does not compete with PostgreSQL for memory.

What should I monitor after cutover?

Watch active client counts, server pool size, connection errors, and query latency. If the pool is saturated during normal use, your app may need fewer concurrent workers or a larger database host.