Repository files navigation

A complete PostgreSQL monitoring solution with automated performance analysis and reporting.

Infrastructure:

• Linux machine with Docker installed (separate from your database server)
• Docker access - the user runningpostgres_ai must have Docker permissions
• Access (network and pg_hba) to the PostgreSQL database(s) you want to monitor

Database:

• Supports PostgreSQL versions 14-17

WARNING: Security is your responsibility!

This monitoring solution exposes several ports thatMUST be properly firewalled:

• Port 3000 (Grafana) - Contains sensitive database metrics and dashboards
• Port 58080 (PGWatch Postgres) - Database monitoring interface
• Port 58089 (PGWatch Prometheus) - Database monitoring interface
• Port 59090 (Prometheus) - Metrics storage and queries
• Port 59091 (PGWatch Prometheus endpoint) - Metrics collection
• Port 55000 (Flask API) - Backend API service
• Port 55432 (Demo DB) - When using--demo option
• Port 55433 (Metrics DB) - PostgreSQL metrics storage

Configure your firewall to:

• Block public access to all monitoring ports
• Allow access only from trusted networks/IPs
• Use VPN or SSH tunnels for remote access

Failure to secure these ports may expose sensitive database information!

Create a new DB user in database to be monitored (skip this if you want just to check outpostgres_ai monitoring with a syntheticdemo database):

-- Create a user for Postgres AI monitoringcreateuserpostgres_ai_mon with password'<password>';grant connecton database<database_name> to postgres_ai_mon;grant pg_monitor to postgres_ai_mon;grantselecton pg_stat_statements to postgres_ai_mon;grantselecton pg_stat_database to postgres_ai_mon;grantselecton pg_stat_user_tables to postgres_ai_mon;-- Create a public view for pg_statistic access (required for bloat metrics on user schemas)createviewpublic.pg_statisticasselectn.nspnameas schemaname,c.relnameas tablename,a.attname,s.stanullfracas null_frac,s.stawidthas avg_width,    falseas inheritedfrom pg_statistic sjoin pg_class conc.oid=s.starelidjoin pg_namespace nonn.oid=c.relnamespacejoin pg_attribute aona.attrelid=s.starelidanda.attnum=s.staattnumwherea.attnum>0and nota.attisdropped;grantselectonpublic.pg_statistic to pg_monitor;alteruser postgres_ai_monset search_path="$user", public, pg_catalog;

One command setup:

# Download the CLIcurl -o postgres_ai https://gitlab.com/postgres-ai/postgres_ai/-/raw/main/postgres_ai \&& chmod +x postgres_ai

Now, start it and wait for a few minutes. Two optional adjustments:

• remove--demo unless you want to see it in action without monitoring an actual Postgres DB (this option creates a demo DB)
• get an Postgres AI access token for your organization athttps://console.postgres.ai (Your org name → Manage → Access tokens)

# Production setup with your Access token./postgres_ai quickstart --api-key=your_access_token

Or if you want to just check out how it works:

# Complete setup with demo database./postgres_ai quickstart --demo

That's it! Everything is installed, configured, and running.

• Grafana Dashboards - Visual monitoring athttp://localhost:3000
• PostgreSQL Monitoring - PGWatch with comprehensive metrics
• Automated Reports - Daily performance analysis
• API Integration - Automatic upload to PostgreSQL AI
• Demo Database - Ready-to-use test environment

For developers:

./postgres_ai quickstart --demo

Get a complete monitoring setup with demo data in under 2 minutes.

For production:

./postgres_ai quickstart --api-key=your_key# Then add your databases./postgres_ai add-instance"postgresql://user:pass@host:port/db"

# Instance management./postgres_ai add-instance"postgresql://user:pass@host:port/db"./postgres_ai list-instances./postgres_ai test-instance my-db# Service management./postgres_ai status./postgres_ai logs./postgres_ai restart# Health check./postgres_ai health

After running quickstart:

• 🚀 MAIN: Grafana Dashboard:http://localhost:3000 (demouser/demopwd)

Technical URLs (for advanced users):

• Demo DB: postgresql://postgres:postgres@localhost:55432/target_database
• Monitoring:http://localhost:58080 (PGWatch)
• Metrics:http://localhost:59090 (Prometheus)

./postgres_aihelp

Get your key atPostgres AI for automated report uploads and advanced analysis.