Production PostgreSQL with pgAdmin4 and PgBouncer

This project provides a production-ready Docker setup for PostgreSQL database with PgBouncer connection pooling and pgAdmin4 web interface. It supports both single and multiple database configurations with unified credentials management.

Key Features

• Flexible Database Mode: Switch between single or multiple database modes with environment variables
• Unified Credentials: Same username/password for all databases managed via .env configuration
• Dynamic Configuration: PgBouncer configuration generated automatically based on requirements
• Management Commands: Simple commands for single (make setup-single) or multi (make setup-multi) mode
• Easy Administration: View configurations, connection strings, and database lists with make commands

Features

• Production-Ready: Optimized for production use with secure defaults and error handling
• PgBouncer Connection Pooling: Efficient connection management for multiple applications
• Flexible Database Management: Support both single and multiple database scenarios
• pgAdmin4 Web Interface: Full-featured database administration tool
• Auto-Configuration: pgAdmin4 and PgBouncer pre-configured automatically
• Health Monitoring: Built-in health checks and monitoring capabilities
• FastAPI Optimized: Perfect for single or multiple FastAPI applications

Architecture

FastAPI App 1 (virtualenv + Gunicorn) ──┐
FastAPI App 2 (virtualenv + Gunicorn) ──┼── → PgBouncer (port 6432) → PostgreSQL (internal)
FastAPI App 3 (virtualenv + Gunicorn) ──┘
pgAdmin4 (Docker) ────────────────────────→ PostgreSQL (internal)

Prerequisites

• Docker and Docker Compose
• Make utility

Quick Start

Option 1: Single Database Mode

For single applications or simple setups:

# Setup single database mode
make setup-single

# This creates one database accessible via:
# postgresql://postgres:your_password@localhost:6432/main_db

Option 2: Multiple Database Mode

For microservices or multiple FastAPI applications:

# Setup multiple database mode  
make setup-multi

# This creates multiple databases accessible via PgBouncer

Configuration Customization

# Copy and edit configuration
cp .env.example .env
# Edit .env with your credentials and database settings

# Apply configuration  
make setup  # Uses your custom .env settings

Management Commands

Database Mode Commands

make setup-single       # Setup single database mode
make setup-multi        # Setup multiple database mode
make show-config        # Show current database configuration
make show-connections   # Show connection strings for all databases
make list-databases     # List all available databases

Service Management

make setup              # Complete setup (uses .env configuration)
make up                 # Start all services
make down               # Stop all services  
make restart            # Restart all services
make status             # Show service status
make logs               # Show service logs
make health-check       # Check service health
make clean              # Remove all containers/volumes

Database Management (PostgreSQL + PgBouncer)

make setup-database     # Setup Database with PgBouncer
make up-database        # Start Database services
make down-database      # Stop Database services
make logs-database      # Show Database logs
make status-database    # Show Database status
make clean-database     # Remove Database data

pgAdmin4 Management

make setup-pgladmin      # Setup pgladmin
make up-pgladmin         # Start pgladmin
make down-pgladmin       # Stop pgladmin
make logs-pgladmin       # Show pgladmin logs
make clean-pgladmin      # Remove pgladmin data

FastAPI Application Configuration

Database Connection String

Connect your FastAPI applications through PgBouncer:

DATABASE_URL = "postgresql://your_prod_user:your_secure_db_password@localhost:6432/your_production_db"

Recommended SQLAlchemy Configuration

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Connection pool settings optimized for PgBouncer
engine = create_engine(
    DATABASE_URL,
    pool_size=5,           # Lower pool size (PgBouncer handles pooling)
    max_overflow=10,       # Conservative overflow
    pool_pre_ping=True,    # Verify connections
    pool_recycle=3600,     # Recycle connections every hour
    echo=False             # Disable SQL logging in production
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Access Information

After running make setup:

• PgBouncer: localhost:6432 (for FastAPI applications)
• pgAdmin4: http://localhost:9090 (web interface)
• PostgreSQL: localhost:5432 (direct access, if needed)

pgAdmin4 Login

• Email: admin@yourcompany.com
• Password: your_secure_admin_password

PgBouncer Configuration

Pool Settings

• Pool Mode: transaction (recommended for FastAPI)
• Max Client Connections: 200
• Default Pool Size: 50 connections per database
• Max DB Connections: 75 total PostgreSQL connections

Performance Benefits

• Connection Reuse: Eliminates connection establishment overhead
• Resource Efficiency: 200 FastAPI clients → 50 PostgreSQL connections
• Protection: Prevents PostgreSQL connection exhaustion
• Scalability: Easy to handle more applications

Production Deployment

Migration from Direct PostgreSQL

1. Deploy containers (keep existing PostgreSQL running)
2. Export data: pg_dump your_current_db > backup.sql
3. Import to containerized DB: docker exec -i postgres-prod psql -U prod_user production_db < backup.sql
4. Update FastAPI apps to use PgBouncer connection string
5. Test and verify functionality
6. Decommission old PostgreSQL (optional)

Security Considerations

• Firewall: Restrict PgBouncer (6432) to local connections only
• PostgreSQL: Keep PostgreSQL internal (no external access)
• pgAdmin4: Restrict to admin users only
• Passwords: Use strong, unique passwords

Monitoring

# Check all services
make status

# Database-specific monitoring  
make status-database

# View logs
make logs
make logs-database

Backup Strategy

# Backup
docker exec postgres-prod pg_dump -U prod_user production_db > backup.sql

# Restore
docker exec -i postgres-prod psql -U prod_user production_db < backup.sql

# List volumes
docker volume ls | grep postgres

Troubleshooting

Common Issues

1. PgBouncer Connection Issues

   • Check PostgreSQL is healthy: make status-database
   • Verify environment variables in .env
   • Check logs: make logs-database

2. pgAdmin4 Can't Connect

   • Ensure servers.json is properly mounted
   • Check container networking: docker network ls
   • Verify credentials in .env

3. Performance Issues

   • Monitor connection pools
   • Adjust PGBOUNCER_DEFAULT_POOL_SIZE if needed
   • Check PostgreSQL connections: SELECT count(*) FROM pg_stat_activity;

Log Access

# All services
make logs

# Database only  
make logs-database

# pgAdmin4 only
make logs-pgladmin

# Follow logs in real-time
docker compose -f docker-compose-postgres.yml logs -f

File Structure

pgladmin4/
├── .env                           # Production configuration
├── .env.example                   # Configuration template
├── docker-compose-postgres.yml    # PostgreSQL + PgBouncer
├── docker-compose-pgadmin.yml      # pgAdmin4 web interface
├── Dockerfile                     # Custom pgAdmin4 build
├── Makefile                       # Management commands
├── servers.json                   # pgAdmin4 server configuration
└── README.md                      # This file

Support

This setup is optimized for production use with multiple FastAPI applications. The unified database management approach treats PostgreSQL + PgBouncer as a single entity, simplifying operations while providing enterprise-grade connection pooling.

For issues or questions, check the troubleshooting section above or review the logs using the provided make commands.
make setup-postgres

Setup and start both services

make setup-all

#### Container Management Commands

**PostgreSQL Container:**
```bash
make up-postgres          # Start PostgreSQL container
make down-postgres        # Stop PostgreSQL container
make restart-postgres     # Restart PostgreSQL container
make logs-postgres        # View PostgreSQL logs
make status-postgres      # Check PostgreSQL container status
make clean-postgres       # Stop and remove PostgreSQL container + volumes

pgladmin Container:

make up-pgladmin          # Start pgladmin container
make down-pgladmin        # Stop pgladmin container
make restart-pgladmin     # Restart pgladmin container
make logs-pgladmin        # View pgladmin logs
make status-pgladmin      # Check pgladmin container status
make clean-pgladmin       # Stop and remove pgladmin container + volumes

Combined Management:

make up-all              # Start both containers
make down-all            # Stop both containers
make restart-all         # Restart both containers
make logs-all            # View logs from both containers
make status-all          # Check status of both containers
make clean-all           # Stop and remove both containers + volumes

Utility Commands

make help                # Show all available commands
make ps                  # Show running containers

Legacy Commands (Backward Compatibility)

make setup               # Same as setup-all
make up                  # Same as up-all
make down                # Same as down-all
make restart             # Same as restart-all
make logs                # Same as logs-all
make clean               # Same as clean-all

Configuration

Environment Variables

Create a .env file (copy from .env.example) to customize settings:

# pgAdmin4 Configuration
PGADMIN_EMAIL=admin@example.com
PGADMIN_PASSWORD=admin123
PGADMIN_PORT=9090
PGADMIN_CONTAINER_NAME=pgladmin4-container

# PostgreSQL Configuration
POSTGRES_DB=mydatabase
POSTGRES_USER=myuser
POSTGRES_PASSWORD=mypassword123
POSTGRES_PORT=5432
POSTGRES_CONTAINER_NAME=postgres-container

Available Environment Variables:

Variable	Default	Description
PGADMIN_EMAIL	pgladmin@admin.com	pgladmin login email
PGADMIN_PASSWORD	pgadmin123	pgladmin login password
PGADMIN_PORT	8080	Host port for pgladmin web interface
PGADMIN_CONTAINER_NAME	pgladmin4-container	pgladmin container name
POSTGRES_DB	postgres	PostgreSQL database name
POSTGRES_USER	postgres	PostgreSQL username
POSTGRES_PASSWORD	postgres123	PostgreSQL password
POSTGRES_PORT	5432	Host port for PostgreSQL
POSTGRES_CONTAINER_NAME	postgres-container	PostgreSQL container name

Access Information

pgAdmin4 Web Interface:

• URL: http://localhost:${PGADMIN_PORT:-8080}
• Default Email: ${PGADMIN_EMAIL:-pgladmin@admin.com}
• Default Password: ${PGADMIN_PASSWORD:-pgadmin123}

PostgreSQL (when using container):

• Host: postgres (from pgladmin) or localhost (from host)
• Port: ${POSTGRES_PORT:-5432}
• Database: ${POSTGRES_DB:-postgres}
• Username: ${POSTGRES_USER:-postgres}
• Password: ${POSTGRES_PASSWORD:-postgres123}

Usage Scenarios

Scenario 1: pgladmin + Local PostgreSQL Container

Perfect for development and testing:

# Start both services
make setup-all

# Access pgladmin at http://localhost:8080
# Connect to PostgreSQL using host: postgres, port: 5432

Scenario 2: pgladmin Only (Connect to External PostgreSQL)

Ideal when connecting to remote PostgreSQL servers or existing local installations:

# Start only pgladmin
make setup-pgladmin

# Access pgladmin at http://localhost:8080
# Add external PostgreSQL servers through the web interface

Scenario 3: PostgreSQL Container Only

When you need a local PostgreSQL instance:

# Start only PostgreSQL
make setup-postgres

# Connect using any PostgreSQL client to localhost:5432

Connecting to External PostgreSQL Servers

When using pgladmin to connect to PostgreSQL running on other machines:

1. Start pgladmin: make setup-pgladmin
2. Access pgladmin web interface at http://localhost:8080
3. Add new server with the external server's details:
   • Host: External server IP or hostname
   • Port: Usually 5432
   • Database: Your database name
   • Username/Password: Your credentials

Docker Compose Files

• docker-compose.yml: pgAdmin4 service configuration
• docker-compose-postgres.yml: PostgreSQL service configuration
• Both files use a shared network for inter-container communication

Data Persistence

• pgladmin data: Stored in pgladmin-data volume
• PostgreSQL data: Stored in postgres-data volume
• Initialization scripts: Place SQL files in ./init-scripts/ directory

Port Configuration

Default ports can be changed in the respective docker-compose files:

• pgladmin: Port 8080 (external) → 80 (internal)
• PostgreSQL: Port 5432 (external) → 5432 (internal)

Troubleshooting

Check Container Status

make status-all          # Check both containers
make ps                  # List all running containers

View Logs

make logs-all            # View logs from both services
make logs-pgladmin        # View only pgladmin logs
make logs-postgres       # View only PostgreSQL logs

Connection Issues

1. Verify containers are running:

   make status-all

2. Check PostgreSQL health:

   make logs-postgres

3. Test PostgreSQL connection from host:

   docker exec -it postgres-container psql -U postgres -d mydb

Reset Everything

make clean-all           # Remove all containers and volumes
make setup-all           # Fresh start

Security Considerations

• Change default passwords in production environments
• Use environment variables for sensitive data
• Configure proper firewall rules when exposing services
• Use SSL/TLS connections for remote PostgreSQL connections
• Regularly update containers for security patches

Updating

Update pgAdmin4

make down-pgladmin
docker pull dpage/pgladmin4:latest
make up-pgladmin

Update PostgreSQL

make down-postgres
docker pull postgres:15-alpine
make up-postgres

Update Both

make down-all
docker-compose -f docker-compose.yml pull
docker-compose -f docker-compose-postgres.yml pull
make up-all

Development Workflow

# Development setup
make setup-all

# Make changes to configuration
# Restart specific service
make restart-pgladmin     # or restart-postgres

# View logs during development
make logs-all

# Clean up when done
make clean-all

Support

For issues and questions:

1. Check container logs: make logs-all
2. Verify container status: make status-all
3. Review Docker and PostgreSQL documentation
4. Check firewall and network connectivity

Run make help to see all available commands and their descriptions.