Skip to content

liciomatos/pgdba

BranchesTags

Repository files navigation

pgdba-cli

Terminal UI for PostgreSQL DBAs — interactive diagnostics and management directly in the terminal.

Installation

Homebrew (macOS and Linux)

brew tap liciomatos/tap
brew trust liciomatos/tap
brew install pgdba-cli

Scoop (Windows)

scoop bucket add liciomatos https://github.com/liciomatos/scoop-bucket
scoop install pgdba-cli

Pre-built binary

Download the latest release from Releases for your OS and architecture.

# Linux (amd64)
tar -xzf pgdba-cli_*_linux_amd64.tar.gz
chmod +x pgdba-cli
sudo mv pgdba-cli /usr/local/bin/

Build from source

git clone https://github.com/liciomatos/pgdba.git
cd pgdba/pgdba-cli
go build -o pgdba-cli .

Usage

# Via URI (recommended)
pgdba-cli --url="postgres://user:password@host:5432/dbname?sslmode=disable"

# Via individual flags
pgdba-cli --host=<host> --user=<user> --password=<password> --dbname=<dbname>

Flags

Flag Env var Default Description
--url DATABASE_URL — PostgreSQL connection URI (overrides all flags below)
--host PGHOST — Server host
--port PGPORT 5432 Port
--user PGUSER postgres Username
--password PGPASSWORD — Password
--dbname PGDATABASE mydb Database name
--sslmode PGSSLMODE disable SSL mode (disable, require, verify-ca, verify-full)
--slow-ms PG_SLOW_MS 1000 Threshold in ms to classify a query as slow
--mcp — — Start MCP server mode (HTTP/SSE)
--mcp-port — 8811 Port for MCP server

All flags fall back to their environment variable counterpart. If --password is not set and PGPASSWORD is empty, ~/.pgpass is consulted automatically.

Examples

# Via environment variables
export PGHOST=db.example.com PGUSER=admin PGPASSWORD=secret
pgdba-cli --dbname=production

# Custom slow query threshold
pgdba-cli --url="postgres://admin:secret@db.example.com/production" --slow-ms=500

MCP Server Mode

pgdba-cli can run as an MCP server, exposing all diagnostic screens as tools callable by LLMs (Claude Code, Claude Desktop).

# Start the MCP server with DB credentials
pgdba-cli --mcp --url "postgres://user:pass@host/db"

# Or via environment variables (no credentials on the command line)
PGHOST=host PGUSER=user PGPASSWORD=pass PGDATABASE=db pgdba-cli --mcp

# Or via the Makefile, against the local dev environment
make mcp-up        # local dev database (mydb), requires `make dev-up` first

On startup, the server prints the claude mcp add command and the equivalent .mcp.json snippet for the port it's actually listening on, so you don't have to look them up by hand.

Configure Claude Code (.mcp.json in your project — credentials never go here):

{
  "mcpServers": {
    "pgdba": {
      "url": "http://localhost:8811/sse"
    }
  }
}

The server exposes tools covering every TUI screen — slow queries, connections, autovacuum, replication slots, freeze status, streaming standbys, replication config, database sizes, temp file usage, memory & checkpoint stats, pub/sub monitoring, and more. Every tool only runs SELECT queries and is annotated readOnlyHint/non-destructive, so MCP clients don't need to treat calls as risky.

Screenshots

Dashboard

Dashboard

Slow Queries

Slow Queries

Index Usage

Index Usage

Autovacuum Monitor

Autovacuum

Autovacuum Detail

Autovacuum Detail

Freeze Monitor

Freeze Monitor

Replication Slots

Replication Slots

Streaming Standbys

Streaming Standbys

Replication Config

Replication Config

Database Sizes

Database Sizes

Temp Files

Temp Files

Memory & Checkpoint Stats

Memory & Checkpoint Stats

Pub/Sub Monitoring

Pub/Sub

Config Parameters

Config Parameters

More screenshots

Long Running Queries

Long Running Queries

Blocked Queries

Blocked Queries

Connections

Connections

Cache Hit Ratio

Cache Hit Ratio

Users

Users

Roles

Roles

Schema Browser

Schema Browser

Extensions

Extensions

Query Load

Query Load

Wait Events

Wait Events

Features

Navigate with ↑↓ or j/k. Press r to refresh and q/esc to go back.

From the main dashboard, open each screen with its shortcut key:

Key Screen Description Actions
1 Slow Queries Top queries by average execution time¹ —
2 Long Running Queries Active queries running longer than 5 seconds k kill session
3 Replication Slots Slots, plugin, WAL lag, and safe WAL size d drop slot, s streaming standbys, p replication config
4 Blocked Queries Blocked sessions and their blockers t terminate session, a terminate all
5 Connections Connections by state with % of limit used —
6 Autovacuum Tables with most dead tuples and bloat estimate enter detail view, v VACUUM ANALYZE
7 Index Usage Indexes sorted by scan count enter index detail
8 Cache Hit Ratio Buffer cache hit ratio per table —
9 Users Login roles and their privileges —
0 Roles Group roles and members —
p Config PostgreSQL parameters (pg_settings) —
s Schema Browser Tables and columns by schema enter describe table
e Extensions Installed extensions —
D Switch Database Switch database without restarting enter connect
L Query Load Top queries by total execution time with load % bar enter full query
w Wait Events Active wait events grouped by type with distribution bar —
f Freeze Monitor XID age by database and top tables approaching wrap-around f VACUUM FREEZE selected table
S Database Sizes On-disk size of every database and tablespace, plus cluster total —
t Temp Files Temp file spill activity per database (pg_stat_database) —
m Memory & Checkpoint Stats Memory-related config, cache hit ratio, checkpoint/bgwriter activity —
R Pub/Sub Publications and subscriptions with table drill-down and live stats tab switch section, enter table detail
T TOAST Tables Tables with TOAST heap data — size, dead tuples, cache hit ratio, and the columns causing TOAST storage v vacuum TOAST heap, enter parent detail

All list screens support live filtering via /.

Autovacuum Detail

Press enter on any row in the Autovacuum screen to open the detail view for that table:

  • Stats — live/dead tuple counts, table and total size
  • Vacuum History — last vacuum, autovacuum, analyze, and autoanalyze timestamps with counters
  • Freeze Status — relfrozenxid age with a visual progress bar
  • Custom Parameters — per-table autovacuum settings vs. global defaults
  • Precise Bloat — press b to run pgstattuple for an exact bloat measurement (full table scan)

Pub/Sub Monitoring

Press R from the main dashboard to open the Pub/Sub screen:

  • Publications — name, table count, and operation flags (Insert/Update/Delete/Truncate/Via Root)
  • Subscriptions — name, status (active/down/disabled), subscribed publications, worker PID, received LSN, and error counts
  • Press enter on any row to open a detail view with per-table sync state, row counts, and DML stats
  • Press tab to switch between the Publications and Subscriptions sections

TOAST Tables

Press T from the main dashboard to see which tables have data stored in their TOAST heap:

  • Toast Size — on-disk size of the TOAST relation (pg_relation_size on the TOAST table itself)
  • Toast % — TOAST size as a percentage of the table's total size (main + TOAST + indexes)
  • Dead Tuples — dead tuples in the TOAST heap, which accumulate independently of the parent table when large columns are updated
  • Cache Hit % — buffer cache hit ratio for TOAST I/O (toast_blks_hit / (toast_blks_hit + toast_blks_read) from pg_statio_user_tables)
  • Toast Columns — comma-separated list of columns whose storage strategy can produce TOAST data (EXTENDED/EXTERNAL/MAIN — PLAIN columns like int are excluded)
  • Press v to VACUUM the TOAST heap directly (VACUUM pg_toast.<toast_table>) with a confirmation prompt — useful when dead tuples accumulate after heavy updates on large columns
  • Press enter to open the Autovacuum Detail screen for the parent table

Streaming Standbys

Press s from the Replication Slots screen to see all connected streaming standbys from pg_stat_replication: write/flush/replay lag, sync mode, and client address. Press k to terminate the walsender for the selected standby.

Replication Config

Press p from the Replication Slots screen for a read-only view of all replication-related pg_settings parameters with contextual hints — highlights parameters that require attention (e.g., archive_mode=off, wal_log_hints=off).

Freeze Monitor

Press f from the main dashboard to open the Freeze Monitor:

  • Database XID Status — age and percentage toward XID wrap-around for every database
  • Top Tables by XID Age — tables closest to needing a freeze, with % Freeze colored green/yellow/red by proximity to autovacuum_freeze_max_age
  • Press f on a selected table to run VACUUM (FREEZE, ANALYZE) with confirmation

Database Sizes

Press S from the main dashboard for on-disk sizing:

  • Per-database size — owner, encoding, and size (pg_database_size), sorted largest first
  • Total across databases — sum of every non-template database
  • Tablespaces — size of each tablespace (pg_tablespace_size) and its on-disk location

Temp Files

Press t from the main dashboard to see temp file spill activity per database (pg_stat_database.temp_files/temp_bytes, accumulated since the last stats reset). Non-zero values are highlighted — persistent growth is a sign work_mem may be too low for the workload.

Memory & Checkpoint Stats

Press m from the main dashboard for a SQL-only view of memory health — no OS-level access required, so it works against remote servers:

  • Memory config — shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers, huge_pages
  • Buffer cache hit ratio — cluster-wide, from pg_stat_database (colored red/yellow/green)
  • Checkpoint & background writer activity — from pg_stat_bgwriter; flags when more checkpoints happen on-demand than on schedule (a signal to raise max_wal_size)

¹ Requires the pg_stat_statements extension. Enable it with:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Development

# Start the full local environment (primary + replica + logical subscriber)
make dev-up

# Connect to each node
make run          # pg-main (port 5432) — primary, publications, scenarios
make run-replica  # pg-replica (port 5433) — physical streaming standby
make run-sub      # pg-sub (port 5434) — logical subscriber

# Tear down all containers and volumes
make dev-down

# Unit tests only (no Docker required)
cd pgdba-cli && go test ./... -short

# All tests including integration (requires Docker)
cd pgdba-cli && go test ./... -timeout 120s

make dev-up starts three containers in a single docker-compose stack and seeds all diagnostic scenarios automatically:

Container Port Role
pg-main 5432 Primary — publications, slow queries, lock scenarios
pg-replica 5433 Physical streaming standby from pg-main
pg-sub 5434 Logical subscriber to pg-main's publications

Compatibility testing across PostgreSQL versions

pgdba-cli is tested against PostgreSQL 13 through 18. To run the full integration suite locally against a specific version:

# Test against a single version
cd pgdba-cli && PGDBA_TEST_PG_VERSION=17-alpine go test ./... -timeout 120s

# Test against every supported version (13-18) locally, one after another
make test-pg-matrix

This mirrors the pg-compat.yml CI workflow, which runs the same matrix in GitHub Actions (triggered manually via workflow_dispatch or automatically on release-tag pushes, since running all 6 versions on every push/PR would be too slow for routine development).

Requirements

  • PostgreSQL 13 or later. New major versions are added to the CI compatibility matrix as they're released (currently 14–18 fully supported; PostgreSQL 13 is past its community end-of-life on 2025-11-13 but pgdba-cli still targets it on a best-effort basis).
  • Slow Queries and Query Load screens require the pg_stat_statements extension
  • Precise Bloat (b in Autovacuum Detail) requires the pgstattuple extension

About

Terminal UI for PostgreSQL DBAs — interactive diagnostics and management directly in the terminal.

Resources

Readme
Activity

Stars

6 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases 5

v0.5.0 Latest
Jul 21, 2026
+ 4 releases

Packages

 
 
 

Contributors

Languages