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

📋 Table of Contents

🏗️ 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