pgSentinel

Professional Web-Based Monitoring & Management for pgbalancer

🚀 Overview

pgSentinel is a comprehensive, enterprise-grade monitoring and management platform for pgbalancer - the PostgreSQL connection pooler. Built with modern technologies, it provides real-time metrics, interactive dashboards, and intelligent alerting for your PostgreSQL infrastructure.

Key Features

⚡ Real-time Monitoring - WebSocket-based live updates with sub-second latency
📊 Beautiful Dashboards - Interactive visualizations built with React and Recharts
🔔 Smart Alerting - Prometheus-based alerts with configurable thresholds
📈 Historical Analysis - Long-term metrics storage and trend analysis
🎯 Node Management - Attach/detach backend nodes with one click
🔒 Secure & Scalable - Production-ready architecture with Docker support
🎨 Modern UI/UX - Responsive design with Tailwind CSS
🔧 Configuration Management - Edit and reload pgbalancer config via UI

🏗️ Architecture

┌─────────────────────────────────────────────────────────────┐
│                     pgSentinel Platform                       │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  ┌─────────────┐    ┌──────────────┐    ┌───────────────┐  │
│  │   Frontend  │    │   Backend    │    │  Prometheus   │  │
│  │  (Next.js)  │◄───│  (FastAPI)   │◄───│  (Metrics)    │  │
│  └─────────────┘    └──────────────┘    └───────────────┘  │
│         │                   │                      │         │
│         │                   │                      │         │
│         ▼                   ▼                      ▼         │
│  ┌─────────────────────────────────────────────────────┐   │
│  │            Real-time WebSocket Connection            │   │
│  └─────────────────────────────────────────────────────┘   │
│                              │                              │
└──────────────────────────────┼──────────────────────────────┘
                               │
                               ▼
            ┌──────────────────────────────────┐
            │        pgbalancer Cluster         │
            ├──────────────────────────────────┤
            │  ┌──────┐  ┌──────┐  ┌──────┐  │
            │  │ Node │  │ Node │  │ Node │  │
            │  │  1   │  │  2   │  │  3   │  │
            │  └──────┘  └──────┘  └──────┘  │
            └──────────────────────────────────┘

Component Overview

Component	Technology	Purpose
Frontend	Next.js 14, React 18, TypeScript	Interactive web dashboard
Backend API	FastAPI, Python 3.12, WebSocket	RESTful API & real-time updates
Metrics	Prometheus, Node Exporter	Metrics collection & storage
Visualization	Grafana, Recharts	Data visualization & dashboards
Database	PostgreSQL 17	Metadata & configuration storage
Cache	Redis 7	Session management & caching
Monitoring	Alertmanager	Alert routing & notification

🛠️ Technology Stack

Frontend

Framework: Next.js 14 (React 18)
Language: TypeScript
Styling: Tailwind CSS 3
Charts: Recharts 2
State Management: React Hooks
HTTP Client: Axios
WebSocket: socket.io-client
Icons: Lucide React

Backend

Framework: FastAPI 0.115
Language: Python 3.12
Async Runtime: uvicorn, asyncio
Database ORM: SQLAlchemy 2
WebSocket: Native WebSocket support
Metrics: Prometheus Client
Validation: Pydantic 2

Monitoring Stack

Metrics: Prometheus 2.x
Visualization: Grafana 10.x
Alerts: Alertmanager 0.26
System Metrics: Node Exporter

Infrastructure

Containerization: Docker 24+
Orchestration: Docker Compose 3.9
Database: PostgreSQL 17
Cache: Redis 7

⚡ Quick Start

Prerequisites

Docker 24+ and Docker Compose
Git
4GB+ RAM
10GB+ disk space

One-Command Start

# Clone the repository
git clone https://github.com/your-org/pgsentinel.git
cd pgSentinel

# Start all services
docker-compose up -d

# View logs
docker-compose logs -f

# Access the dashboard
open http://localhost:3000

Access Points

Service	URL	Credentials
pgSentinel Dashboard	http://localhost:3000	-
Backend API	http://localhost:8000	-
API Documentation	http://localhost:8000/docs	-
Prometheus	http://localhost:9090	-
Grafana	http://localhost:3001	admin / admin
Alertmanager	http://localhost:9093	-

📥 Installation

Option 1: Docker (Recommended)

# Production deployment
docker-compose -f docker-compose.yml up -d

# Development with hot reload
docker-compose -f docker-compose.dev.yml up

Option 2: Manual Installation

Backend Setup

cd backend

# Create virtual environment
python3 -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

# Set environment variables
export DATABASE_URL="postgresql://user:pass@localhost:5432/pgsentinel"
export REDIS_URL="redis://localhost:6379/0"

# Run backend
uvicorn main:app --reload --host 0.0.0.0 --port 8000

Frontend Setup

cd frontend

# Install dependencies
npm install

# Set environment variables
echo "NEXT_PUBLIC_API_URL=http://localhost:8000" > .env.local
echo "NEXT_PUBLIC_WS_URL=ws://localhost:8000/ws/live" >> .env.local

# Run development server
npm run dev

# Or build for production
npm run build
npm start

⚙️ Configuration

Environment Variables

Backend (`backend/.env`)

# Database
DATABASE_URL=postgresql://user:pass@localhost:5432/pgsentinel

# Redis
REDIS_URL=redis://localhost:6379/0

# pgbalancer
PGBALANCER_CONFIG=/path/to/pgbalancer.conf

# Logging
LOG_LEVEL=INFO

# Security
SECRET_KEY=your-secret-key-here
ALLOWED_HOSTS=*

Frontend (`frontend/.env.local`)

NEXT_PUBLIC_API_URL=http://localhost:8000
NEXT_PUBLIC_WS_URL=ws://localhost:8000/ws/live
NEXT_TELEMETRY_DISABLED=1

pgbalancer Configuration

Update pgbalancer.conf to enable metrics export:

# Enable metrics endpoint
enable_metrics = on
metrics_port = 9187

# Connection pool settings
num_init_children = 32
max_pool = 4

📖 Usage

Dashboard Overview

The main dashboard provides:

System Metrics
- CPU usage
- Memory utilization
- Network I/O
- Disk I/O
Connection Pool Stats
- Total connections
- Active connections
- Idle connections
- Pool utilization %
Backend Nodes
- Node status (up/down)
- Connection count per node
- Replication lag
- Primary/Replica roles
Real-time Charts
- System resource trends
- Connection trends
- Query performance
- Replication lag graphs

Managing Backend Nodes

Detach a Node

# Via UI: Click "Detach" button on node card

# Via API:
curl -X POST http://localhost:8000/api/v1/backends/1/detach

Attach a Node

# Via UI: Click "Attach" button on node card

# Via API:
curl -X POST http://localhost:8000/api/v1/backends/1/attach

Configuration Management

# Get current configuration
curl http://localhost:8000/api/v1/config

# Update configuration
curl -X PUT http://localhost:8000/api/v1/config \
  -H "Content-Type: application/json" \
  -d '{"num_init_children": 64}'

# Reload configuration
curl -X POST http://localhost:8000/api/v1/reload

📊 Monitoring & Alerts

Predefined Alerts

Alert	Threshold	Severity	Description
HighPoolUtilization	>80%	Warning	Pool is heavily utilized
PoolExhausted	Waiting clients > 0	Critical	No available connections
BackendNodeDown	Status = 0	Critical	Backend node is unreachable
HighReplicationLag	>10MB	Warning	Replica is lagging
CriticalReplicationLag	>100MB	Critical	Severe replication delay
HighCPUUsage	>80%	Warning	System CPU is high
HighMemoryUsage	>85%	Warning	System memory is high

Custom Dashboards

Import pre-built Grafana dashboards:

# Import dashboard
curl -X POST http://localhost:3001/api/dashboards/import \
  -u admin:admin \
  -H "Content-Type: application/json" \
  -d @monitoring/grafana/dashboards/pgbalancer-overview.json

🔧 Development

Project Structure

pgSentinel/
├── backend/                 # FastAPI backend
│   ├── main.py             # Main application
│   ├── requirements.txt    # Python dependencies
│   └── Dockerfile          # Backend container
├── frontend/               # Next.js frontend
│   ├── app/                # Next.js 14 app directory
│   ├── components/         # React components
│   │   ├── common/         # Reusable components
│   │   ├── dashboard/      # Dashboard-specific
│   │   ├── charts/         # Chart components
│   │   └── nodes/          # Node management
│   ├── hooks/              # Custom React hooks
│   ├── services/           # API service layer
│   ├── types/              # TypeScript types
│   ├── utils/              # Utility functions
│   └── Dockerfile          # Frontend container
├── monitoring/             # Monitoring stack
│   ├── prometheus/         # Prometheus config
│   │   ├── prometheus.yml  # Main config
│   │   └── rules/          # Alert rules
│   ├── grafana/            # Grafana dashboards
│   └── alertmanager/       # Alert routing
├── docker/                 # Docker configs
├── docs/                   # Documentation
└── docker-compose.yml      # Orchestration

Running Tests

# Backend tests
cd backend
pytest

# Frontend tests
cd frontend
npm test

# E2E tests
npm run test:e2e

Code Quality

# Backend linting
cd backend
ruff check .
black --check .

# Frontend linting
cd frontend
npm run lint
npm run type-check

🚀 Deployment

Production Deployment

# Build and deploy
docker-compose -f docker-compose.prod.yml up -d

# Scale services
docker-compose up -d --scale backend=4

# Update services
docker-compose pull
docker-compose up -d

Kubernetes Deployment

# Apply Kubernetes manifests
kubectl apply -f k8s/

# Check status
kubectl get pods -n pgsentinel

🤝 Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

Development Workflow

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

pgbalancer team for the excellent connection pooler
PostgreSQL community
FastAPI and Next.js teams
All contributors and users

📞 Support

Documentation: docs.pgsentinel.io
Issues: GitHub Issues
Discussions: GitHub Discussions
Email: support@pgsentinel.io

Built with ❤️ by pgElephant, Inc.

Website • Documentation • Blog

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
backend		backend
docs		docs
frontend		frontend
monitoring		monitoring
website		website
.env		.env
.gitignore		.gitignore
COMPLETE_FEATURES.md		COMPLETE_FEATURES.md
INDEX.md		INDEX.md
Makefile		Makefile
PROJECT_SUMMARY.md		PROJECT_SUMMARY.md
README.md		README.md
WEBSITE_COMPLETE.md		WEBSITE_COMPLETE.md
docker-compose.yml		docker-compose.yml
env.template		env.template
setup.sh		setup.sh

pgElephant/pgSentinel

Folders and files

Latest commit

History

Repository files navigation