In my previous article, I discussed Docker from a developer’s perspective and how containers can help isolate applications and their dependencies.
This time, I want to get a little more technical and walk through something I implemented in one of my own projects: running PostgreSQL in Docker and introducing PgBouncer to solve a database connection problem.
I originally started using Docker for PostgreSQL because managed database services, such as Google Cloud SQL, were becoming too expensive for a solo developer project. Running PostgreSQL in a container on a remote virtual machine gave me more control over the environment and its costs.
However, I eventually ran into another problem. As application traffic increased, PostgreSQL began running out of available connections.
Here’s how I approached the problem and what I changed.
Why I Chose PostgreSQL in Docker
I wanted a database environment that I could reproduce locally and remotely without installing PostgreSQL directly on every machine.
My remote VM runs PostgreSQL in a Docker container, with a persistent Docker volume for database storage. I use a similar arrangement on my local Mac.
This gives me a consistent way to manage the database without having to maintain separate native PostgreSQL installations.
It also means I can recreate the container without losing the actual database files, provided I preserve the persistent volume.
Docker handles the environment, PostgreSQL handles the database, and the volume keeps the data separate from the container’s lifecycle.
That setup worked well, until I started experiencing connection problems.
The Problem: PostgreSQL Was Running Out of Connections
As application traffic increased, I started seeing database connection errors.
One of the errors I recorded was:
FATAL: remaining connection slots are reserved
This indicates that PostgreSQL had reached its configured connection limit. The remaining reserved connection slots were no longer available to ordinary database users.
I tried adjusting PostgreSQL parameters, connection timeouts, and other settings. Unfortunately, none of those changes provided a lasting solution.
When enough traffic accumulated, PostgreSQL would eventually stop accepting new connections.
I needed a way to manage database connections more efficiently rather than simply continuing to adjust PostgreSQL settings.
That’s when I started researching connection pooling and came across PgBouncer.
Introducing PgBouncer
PgBouncer is a lightweight connection pooler for PostgreSQL.
Instead of having every application connection establish and maintain its own connection directly to PostgreSQL, PgBouncer sits between the application and the database.
It accepts client connections and manages a smaller pool of connections to PostgreSQL. When a client needs database access, PgBouncer can assign it an available server connection rather than opening another PostgreSQL connection for every request.
This was the approach I needed.
My application could continue using SQLAlchemy as before, but instead of connecting directly to PostgreSQL, it would connect to PgBouncer.
The connection flow became:
Application → SQLAlchemy → PgBouncer → PostgreSQL
PgBouncer handles the connection pooling behind the scenes, allowing multiple application clients to share a smaller number of PostgreSQL server connections.
Choosing a Pooling Mode
PgBouncer supports several pooling modes:
- Session pooling: A PostgreSQL server connection remains assigned to a client for the duration of its session.
- Transaction pooling: A server connection is assigned to a client for the duration of a transaction and returned to the pool when the transaction completes.
- Statement pooling: A server connection is assigned for an individual statement. This mode does not allow multi-statement transactions.
I configured PgBouncer to use transaction pooling:
pool_mode = transaction
This suited my application because I wanted PostgreSQL connections to be reused between transactions rather than tied to individual client sessions.
There are some important considerations when using transaction pooling. Features that depend on session state can behave differently, so applications need to be compatible with the selected pooling mode.
Running PgBouncer in a Separate Docker Container
I decided to run PgBouncer in its own container rather than installing it directly alongside PostgreSQL.
This keeps the responsibilities separate and makes the configuration easier to manage.
Both containers communicate over a shared Docker network.
Here is an excerpt from my docker-compose.yml configuration:
networks:
my-net:
name: my-shared-network
driver: bridge
volumes:
postgres_data:
name: data-postgres-volume
services:
postgres:
image: postgres:15
container_name: data-postgres
networks:
- my-net
ports:
- "5432:5432"
environment:
POSTGRES_USER: ${DB_USER}
POSTGRES_PASSWORD: ${DB_PASSWORD}
POSTGRES_DB: ${DB_NAME}
volumes:
- postgres_data:/var/lib/postgresql/data
restart: always
pgbouncer:
image: edoburu/pgbouncer:latest
container_name: data-pgbouncer
networks:
- my-net
ports:
- "6432:6432"
volumes:
- ./pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini
- ./pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt
depends_on:
- postgres
restart: always
The PostgreSQL container listens on port 5432, while PgBouncer listens on port 6432.
Both containers are connected to the same Docker network, allowing PgBouncer to reach PostgreSQL using its container hostname.
Notice that the PgBouncer configuration files are mounted from the project directory. This lets me maintain the configuration outside the container instead of having to modify files inside a running container.
The database itself uses a named Docker volume:
volumes: - postgres_data:/var/lib/postgresql/data
This is important because removing or recreating a container should not mean losing the database.
The volume has its own lifecycle, separate from the container.
Configuring PgBouncer
I keep my pgbouncer.ini file in the project’s pgbouncer directory.
Here is a simplified version of the configuration:
[databases] your_db = host=data-postgres port=5432 dbname=your_db [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 20
The database section tells PgBouncer where the actual PostgreSQL database is located.
The important settings in this example are:
listen_port: The port applications use to connect to PgBouncer.auth_type: The authentication method.auth_file: The location of the user authentication file.pool_mode: The pooling strategy.max_client_conn: The maximum number of client connections PgBouncer accepts.default_pool_size: The default number of server connections PgBouncer maintains per user/database pool.
Notice that the client connection limit and the PostgreSQL connection pool size are different settings. PgBouncer can accept many client connections while maintaining a considerably smaller number of connections to PostgreSQL.
Configuring Authentication
One additional step was configuring authentication between the application, PgBouncer, and PostgreSQL.
I used SCRAM-SHA-256 authentication.
PostgreSQL stores password verifiers in its system catalogs. During setup, I connected to the PostgreSQL container and queried the pg_shadow table to retrieve the verifier for my database user.
For example:
docker exec -it <container-name> bash
Then, from inside the container:
psql -U your_db_username -d your_db
Once connected, I ran:
SELECT usename, passwd FROM pg_shadow WHERE usename = 'your_db_username';
The result contains the username and its stored password verifier, which uses the SCRAM-SHA-256 format.
Initially, I tried using this verifier in PgBouncer’s userlist.txt file, but encountered authentication failures. What ultimately worked in my configuration was supplying the database user’s actual password instead:
"your_db_username" "your_password"
PgBouncer supports both plaintext passwords and SCRAM-SHA-256 verifiers in its authentication file when configured for SCRAM authentication. When supplied with the plaintext password, PgBouncer can perform the SCRAM authentication process without sending the password as plaintext during the authentication exchange.
The important distinction is that the password and the stored verifier are different credential formats, even though both can be used by PgBouncer for SCRAM authentication.
Security note: The userlist.txt file contains sensitive credentials. Protect it carefully and restrict access to it. Never commit it to a public source repository.
If you’re using Git or another source control system, make sure files containing credentials or secrets are excluded from commits. Files such as .env, userlist.txt, and other configuration files containing passwords, API keys, or authentication credentials should be included in your .gitignore.
Connecting SQLAlchemy Through PgBouncer
One of the things I liked about this solution is that I didn’t need to rewrite my database operations.
I already used SQLAlchemy in my Python application, so the main change was configuring the database connection to point to PgBouncer instead of PostgreSQL.
I keep my database credentials in a .env file, which is loaded using Python’s python-dotenv package.
For example:
DB_USER=your_db_username DB_PASSWORD=your_password DB_HOST=data-pgbouncer DB_PORT=6432 DB_NAME=your_db
The important change is the hostname and port.
Instead of connecting to PostgreSQL on port 5432, the application now connects to the PgBouncer container on port 6432.
In my config.py file, I read those environment variables and construct the SQLAlchemy connection string:
# Database configuration
db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')
# Format: postgresql://user:password@host:port/dbname
SQLALCHEMY_DATABASE_URI = (
f"postgresql+psycopg2://{db_user}:{db_password}"
f"@{db_host}:{db_port}/{db_name}"
)
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLALCHEMY_ENGINE_OPTIONS = {
"poolclass": NullPool,
"pool_pre_ping": True,
}
I use SQLAlchemy’s NullPool to disable its own connection pooling. Since PgBouncer is already managing the PostgreSQL connection pool, I don’t need another persistent connection pool at the application level.
The pool_pre_ping option checks whether a connection is still alive before SQLAlchemy uses it.
After that, I can continue using SQLAlchemy normally for my database operations.
The application doesn’t need to know how PgBouncer manages the underlying PostgreSQL connections. It simply connects to the configured database endpoint.
The Result
Introducing PgBouncer solved the connection exhaustion problem I was experiencing.
Instead of allowing application traffic to consume PostgreSQL connections directly, PgBouncer manages the connections between the application and the database.
The application can open client connections to PgBouncer, while PgBouncer reuses a smaller pool of PostgreSQL server connections.
This doesn’t eliminate PostgreSQL’s connection limits, but it helps make much better use of the connections available.
It also means I can tune the application-facing connection capacity separately from the number of actual PostgreSQL connections.
A Few Things Worth Remembering
There are a few lessons I took away from this implementation.
Connection pooling is not the same as increasing the connection limit. Increasing PostgreSQL’s connection limit may help in some situations, but it doesn’t necessarily address inefficient connection usage.
Don’t run multiple connection pools without understanding their behavior. SQLAlchemy and PgBouncer can both manage connections, but their settings need to work together. In my case, I chose NullPool so PgBouncer could handle the server-side pooling.
Persistent volumes and backups are two different things. A Docker volume protects database files from the normal lifecycle of a container, but it isn’t a substitute for a backup strategy.
Authentication deserves attention. PgBouncer introduces another layer between your application and database, and that layer needs to be configured securely.
Encrypted PostgreSQL Backups
Having a database running reliably is only part of the equation. I also wanted an automated way to create backups and protect them in case the files were ever accessed by someone who shouldn’t have them.
For this, I used PostgreSQL’s pg_dump utility together with GPG (GNU Privacy Guard) to create encrypted database backups.
Preparing GPG
GPG uses public-key encryption. The public key is used to encrypt a file, while the corresponding private key is required to decrypt it.
A new key can be generated interactively with:
gpg --full-generate-key
GPG walks through the key-generation options, including the key type, size, expiration, and identifying information.
To view available public keys and their fingerprints:
gpg --list-keys --fingerprint
The recipient can be identified using a key ID, fingerprint, email address, or other associated user ID. I use the key ID in my backup script.
The private key and its passphrase are especially important. If the private key is lost, the encrypted backups cannot be recovered. Keep a secure backup of the private key and its associated passphrase somewhere separate from the encrypted database backups.
The Backup Script
I created a Bash script named backup.sh and scheduled it to run automatically through cron every day at midnight.
The script runs pg_dump inside the PostgreSQL Docker container and pipes the resulting database dump directly into GPG for encryption.
#!/bin/bash
set -o pipefail
# Configuration
PROJECT_DIR="/home/user/myapp"
BACKUP_DIR="/home/user/backups"
TIMESTAMP=$(date -u +"%Y%m%d_%H%M%SZ")
FILENAME="your_db_${TIMESTAMP}.dump.gpg"
CONTAINER_NAME="postgres"
# Ensure backup directory exists
mkdir -p "${BACKUP_DIR}"
# Run pg_dump via Docker Compose and encrypt the output with GPG
docker compose -f "${PROJECT_DIR}/docker-compose.yml" exec -T "${CONTAINER_NAME}" \
pg_dump -U your_db_username -F c -d your_db \
| gpg --encrypt --recipient "<your-key-id>" --trust-model always \
> "${BACKUP_DIR}/${FILENAME}"
# Delete backups older than 30 days
find "${BACKUP_DIR}" -type f -name "your_db_*.dump.gpg" -mtime +30 -delete
A few details are worth explaining:
- UTC timestamps: The filename includes a UTC timestamp, making it easier to identify when each backup was created regardless of the server’s local timezone.
- Docker Compose: The
exec -Toption runs the command without allocating a pseudo-terminal, which is useful for scripts and automated jobs. - Custom-format dump: The
-F coption tellspg_dumpto create a PostgreSQL custom-format archive. This format works withpg_restoreand supports restoring individual database objects. - GPG encryption: The output from
pg_dumpis piped directly into GPG. The unencrypted dump is not first written as a separate file to the backup directory. - Retention: The final command removes older backup files to keep the backup directory from growing indefinitely.
- Pipeline error handling:
set -o pipefailensures that a failure inpg_dumpis reflected in the script’s exit status, rather than being hidden by a successful command later in the pipeline.
The --trust-model always option tells GPG to use the specified recipient without requiring its normal trust confirmation. This assumes the recipient key has already been verified. I use the key ID to identify the intended encryption key.
Restoring a Backup
Creating backups is only useful if they can be restored.
When restoring a backup, I want the destination database to match the contents of the archive rather than merging the backup with existing data.
I start by dropping the existing database and creating a fresh one:
docker compose exec -T postgres dropdb -U your_db_username --if-exists your_db docker compose exec -T postgres createdb -U your_db_username your_db
The destination database must not have active connections when it is dropped. I also make sure the application isn’t running or attempting to reconnect during this process.
Once the database has been recreated, I decrypt the backup with GPG and pipe the output directly into pg_restore inside the PostgreSQL container:
gpg --decrypt "${BACKUP_DIR}/your_db_20261112_000000Z.dump.gpg" \
| docker compose -f "${PROJECT_DIR}/docker-compose.yml" exec -T "${CONTAINER_NAME}" \
pg_restore -U your_db_username -d your_db --exit-on-error
This avoids creating an intermediate unencrypted database dump on disk.
Because the destination database is freshly created, I don’t need the --clean or --if-exists options. Those options are useful when restoring into an existing database because they tell pg_restore to remove existing objects before recreating them.
The --exit-on-error option stops the restore if an error occurs.
Handling Roles and Permissions
One issue I encountered while testing the backup was restoring a production database onto a separate development server.
The restore initially failed with an error indicating that a PostgreSQL role named admin5 did not exist.
The production database contained ownership and permission references to this role, but the development server did not have the same role configuration.
To resolve this, I added two options to the pg_restore command:
--no-ownerskips restoring the original object ownership.--no-privilegesskips restoring the original GRANT and REVOKE permissions.
The updated restore command became:
gpg --decrypt "${BACKUP_DIR}/your_db_20261112_000000Z.dump.gpg" \
| docker compose -f "${PROJECT_DIR}/docker-compose.yml" exec -T "${CONTAINER_NAME}" \
pg_restore -U your_db_username -d your_db \
--no-owner --no-privileges --exit-on-error
This allowed me to restore the database structure and data without requiring the production roles and permissions to exist on the development server.
It’s important to understand that pg_dump backs up an individual database, not the entire PostgreSQL cluster. Roles and tablespaces are cluster-wide objects and are not included in a regular database dump.
If you need to preserve those objects during a complete recovery, they can be exported separately using pg_dumpall --globals-only and restored before the database archive.
For development environments or migrations between servers with different role configurations, --no-owner and --no-privileges can make the restore process considerably easier.
A Note About Security
The GPG public key is used for encryption, but the private key is what makes recovery possible. Protecting the private key is just as important as protecting the database itself.
The backup directory contains encrypted database archives, but configuration files and credentials used by the application and PgBouncer still need to be protected separately. Files such as .env and userlist.txt should never be committed to a public source repository.
Automating encrypted backups gives me another layer of protection without requiring a managed database backup service. The process runs on a schedule, keeps a rolling collection of backups, and encrypts the database before writing the archive to disk.
Final Thoughts
PgBouncer wasn’t something I originally planned to introduce when I started running PostgreSQL in Docker. It became necessary when my application began experiencing connection exhaustion.
Sometimes the best solution isn’t another configuration tweak or a higher limit. Sometimes it’s introducing a tool that handles a problem more efficiently.
For my project, PgBouncer was that tool.
It allowed me to keep using PostgreSQL and SQLAlchemy while managing database connections more efficiently, without having to redesign the way my application performs database operations.
It’s another example of how a little infrastructure knowledge and the right tools can make a big difference when you’re building and maintaining software yourself.
Leave a Reply