DBPilot.PostgreSql 0.5.1

DBPilot

Self-hosted database autonomous diagnostics platform — bring Alibaba Cloud DAS-style database performance diagnostics to your own infrastructure.

DBPilot continuously samples your database instances and turns the data into the day-to-day DBA workflow in a single web console: performance trends, performance insight (AAS load decomposition), Top SQL, query plan change tracking, missing-index advice, index usage & fragmentation, blocking analysis, deadlock analysis, and slow query logs.

简体中文

Contents

• Highlights
• Screenshots
• Quick start
• Architecture
• Features
• Engine support matrix
• AI diagnostics (MCP Server)
• Overhead on monitored instances
• Configuration (appsettings.json)
• Embedding via NuGet
• Development
• FAQ
• License

Highlights

• Single-process deployment — one .NET 10 process + one metadata database (schema auto-created on startup). Choose SQL Server, MySQL, PostgreSQL or SQLite as the metadata store.
• Monitors three engines — SQL Server 2008–2022, MySQL 8.0+, PostgreSQL 13+. Monitoring engine and metadata-store engine are independent axes and can be combined freely (e.g. monitor SQL Server while storing history in PostgreSQL).
• Very low overhead — <1% CPU on monitored instances in normal operation. Only in-memory metadata views (DMVs / pg_stat_* / performance_schema) are read; business tables are never scanned. See Overhead on monitored instances.
• AI-ready — built-in read-only MCP Server exposes 11 diagnostic tools to AI agents (Claude Code, Codex CLI, ...), so you can ask questions like "did anything slow down on this instance in the last hour?"
• Capability-driven UI — features without an equivalent data source on a given engine are hidden or degraded automatically (e.g. PostgreSQL gets a deadlock trend instead of deadlock event details); the API answers with explicit, actionable messages.

Screenshots

Performance insight (AAS)	Performance trends

Top SQL	Deadlock analysis

Blocking analysis	Slow query log

Quick start

The fastest path is the NuGet packages (frontend assets are embedded — no Node.js needed):

mkdir dbpilot && cd dbpilot
dotnet new web
dotnet add package DBPilot          # metapackage: AspNetCore + all four engine packages

Program.cs:

using DBPilot.AspNetCore.Extension;
using DBPilot.Core.Providers;

var builder = WebApplication.CreateBuilder(args);
builder.AddDBPilot(o => o.PlatformEngine = DbpilotEngine.SqlServer);  // metadata-store engine

var app = builder.Build();
app.UseDBPilot();
app.Run();

appsettings.json — one connection string pointing at an empty database (schema is created automatically):

{
  "DBPilot": {
    "ConnectionString": "Server=...;Database=dbpilot;User Id=...;Password=...;TrustServerCertificate=true"
  }
}

dotnet run    # → http://localhost:5000

Log in with the default account admin / dbpilot@2026 (change it after deployment — see Configuration), then register your first monitored instance: Instances → Add (host + credentials, stored encrypted) → Test connection → Enable. Real-time pages work immediately; history accumulates over time.

A ready-made copy of this setup lives in samples/DBPilot.Sample.SqlServer (plus MySQL :5201 / SQLite :5203 / PostgreSQL :5204 variants) — if you prefer building from source:

dotnet build
cd samples/DBPilot.Sample.SqlServer
cp appsettings.template.json appsettings.json   # fill in your connection string
dotnet run                                      # → http://localhost:5200

Building from source additionally requires Node.js 20+ (cd web && npm install && npm run build) — but only if you modify the frontend; committed builds ship inside the NuGet packages.

Architecture

flowchart LR
    subgraph browser["Browser"]
        ui["Web console"]
    end

    subgraph host["DBPilot service (single process)"]
        api["Web API + MCP Server"]
        sched["Background collectors (Quartz)"]
        webhost["Static UI hosting"]
    end

    subgraph platform["Metadata DB (schema auto-created)"]
        tables["Historical data"]
    end

    subgraph monitored["Monitored instances (any number)"]
        dmv["DMV / XE / pg_stat_* / performance_schema"]
    end

    ui --> webhost
    ui -->|API| api
    sched -->|scheduled sampling| dmv
    sched --> tables
    api --> tables
    api -->|real-time queries| dmv

The browser talks only to the DBPilot service. Real-time pages query instances directly; history pages read the metadata DB — both paths share the same data conventions (noise exclusion, statement fingerprinting, time windows).

Features

Engine support matrix

Metadata store (platform DB): SQL Server / MySQL / PostgreSQL / SQLite. Independent from the monitored engines — any combination works (SQLite gives you a single-executable + single-file embedded deployment; see samples/DBPilot.Sample.Sqlite).

Prerequisites by engine

MySQL (monitored): performance_schema=ON, an account with PROCESS + SELECT on performance_schema/mysql. Slow SQL requires slow_query_log=ON and log_output containing TABLE. On managed MySQL (e.g. Alibaba Cloud RDS) parameter groups often deviate from defaults in ways that cause silent empty data — the connection test checks each parameter and tells you exactly what to change. SP body statements never appear in the digest leaderboard (MySQL design boundary; CALL itself does).

PostgreSQL (monitored): account able to read pg_stat_activity / pg_stat_database / pg_locks / pg_stat_user_indexes with CONNECT on target databases. The single hard requirement is the pg_stat_statements extension with track ≠ none — without it Top SQL stays empty (the wizard's self-check reports this with fix instructions).

SQL Server (monitored): works from 2008 up. Deadlock capture rides the built-in system_health session by default; slow SQL uses an auto-created XE session (or an existing one you configure).

AI diagnostics (MCP Server)

A read-only MCP Server (/mcp, Streamable HTTP + API key) hands the platform's evidence — metrics, slow SQL, deadlocks, blocking, indexes — to AI agents as 11 read-only tools. Tools share the same query conventions as the web UI; SQL text is truncated by default to protect the context window; every call is audit-logged.

Enable it (off by default):

"DBPilot": {
  "Modules": { "Mcp": { "Enabled": true } },
  "Mcp": { "ApiKey": "a sufficiently random key" }
}

Claude Code:

claude mcp add --transport http dbpilot http://localhost:5200/mcp --header "X-Api-Key: <your-key>"

Verify with claude mcp list (or /mcp in a session), then just ask: "use dbpilot to check the instance load over the last hour — any SQL getting slower?"

Codex CLI (~/.codex/config.toml):

[mcp_servers.dbpilot]
url = "http://localhost:5200/mcp"

[mcp_servers.dbpilot.http_headers]
X-Api-Key = "<your-key>"

See examples/ for a command-line diagnostic console (DBPilot.McpConsole) and a fault-drill project (DBPilot.Scenarios) that reference the published NuGet packages.

Overhead on monitored instances

<1% CPU, zero disk pressure in normal operation. Collectors read in-memory metadata views (DMVs, extended event files, pg_stat_*, performance_schema) — no business-table scans, no physical IO, no locks on user objects.

Mitigations built in: XE predicates exclude the platform's own sessions, self-monitoring statements are tagged out of Top SQL, every cron is configurable, instances can be disabled individually, failed connections back off automatically. Sub-second DMV polling is the industry-standard path (SQL Server's own system_health, Alibaba Cloud DAS, AWS Performance Insights all work this way).

You can verify it yourself — after running for a day, the monitoring account's accumulated CPU seconds on the monitored instance is the true cost:

SELECT login_name, SUM(cpu_time)/1000 AS cpu_seconds_total
FROM sys.dm_exec_sessions
WHERE host_process_id IS NOT NULL
GROUP BY login_name;

Configuration (appsettings.json)

One required key (DBPilot:ConnectionString) + an explicit platform-engine choice (enum in code or DBPilot:PlatformEngine in config; missing → startup error). Everything else is optional with sane defaults.

Connection info for monitored instances never lives in config files — it is maintained in the UI and stored encrypted in the metadata DB.

Embedding via NuGet

Published on nuget.org:

dotnet add package DBPilot            # metapackage: everything, one line (AspNetCore + all engines)
# or pick exactly what you need:
dotnet add package DBPilot.AspNetCore # main package: API / MCP / auth / scheduling / embedded frontend
dotnet add package DBPilot.SqlServer  # engine packages, pick any combination:
dotnet add package DBPilot.MySql      #   SqlServer / MySql / PostgreSql (monitor + platform storage)
dotnet add package DBPilot.Sqlite     #   Sqlite (platform storage only, embedded deployments)

using DBPilot.AspNetCore.Extension;
using DBPilot.Core.Providers;

var builder = WebApplication.CreateBuilder(args);
builder.AddDBPilot(o =>
{
    o.PlatformEngine = DbpilotEngine.SqlServer;  // no default — set explicitly (or via DBPilot:PlatformEngine)
    // o.WebOnly();                               // delegate sets only what you want to change
});

var app = builder.Build();
app.UseDBPilot();
app.Run();

Notes:

• Zero wiring for engines — referenced engine packages are discovered by scanning output DBPilot.*.dll assemblies; instances route by their engine column. Explicit registration (AddDbpilotSqlServer() etc.) can be mixed in (first registration wins per engine). Single-file publishes pack assemblies into the host, so use explicit registration there.
• Package graph: DBPilot.AspNetCore → Core → Storage → Common; engine packages depend on Core + Storage.
• Frontend static assets ship through two channels: buildTransitive targets copy them into the consumer's wwwroot, and an embedded manifest in the DLL serves as fallback — the UI works even with an empty wwwroot.
• Fine-grained methods (AddDbpilotWeb / AddDbpilotMcp / AddDbpilotQuartz / ...) remain available for advanced compositions.

Development

scripts\run\dev.bat      # or two terminals:
dotnet watch --project samples/DBPilot.Sample.SqlServer   # backend (Swagger at /swagger)
cd web && npm run dev                                     # frontend → http://localhost:5173

• scripts\run\test.bat — build + unit tests + frontend build in one go
• scripts/test/ — self-test load scripts (SQL Server / MySQL; generate load → verify → clean up). Never run them against production databases. See scripts/README.md
• Frontend: dark shell + light workbench, colors from a single source (web/src/theme/palette.ts), charts via a shared initChart() theme, lazy-rendered through composables/useChart.ts

FAQ

License

Apache-2.0