# pg-licht

> A read-only PostgreSQL server for the Model Context Protocol: 68 tools and 12 guided prompts for exploring schemas, reading statistics and diagnosing a live server. Version 4.3.3.

Every tool call runs in its own READ ONLY transaction bounded by statement_timeout, and every catalog query is parameterized, so no argument is ever concatenated into SQL. The one operation that can execute a statement, explainQuery with analyze, does so only after the plan is proven free of any data-modifying node. It speaks MCP over stdio and runs as a single binary; connections are libpq connection strings or a connections file.

Install with `brew tap sqlambda/pg-licht && brew install pg-licht`, or from the Debian, Rocky Linux and tarball packages on the releases page: https://github.com/sqlambda/pg_licht/releases

## Docs

- [README](https://github.com/sqlambda/pg_licht/blob/v4.3.3/README.md): what it is, the safety model, topology, host capacity
- [INSTALL](https://github.com/sqlambda/pg_licht/blob/v4.3.3/INSTALL.md): Homebrew, deb, rpm, tarball, verifying, uninstalling
- [Reference](https://sqlambda.github.io/pg_licht/reference/index.html): every tool and prompt, grouped by what it answers, with parameters, output and an example
- [Manual](https://sqlambda.github.io/pg_licht/pg_licht_mcp.1.html): configuration, connection strings, security considerations, every tool and prompt in full

## Tools

- [listSchemas](https://sqlambda.github.io/pg_licht/reference/listSchemas.html): Return schema list with basic summaries: table_count, up to 25 table names, and role grants per schema.
- [listTables](https://sqlambda.github.io/pg_licht/reference/listTables.html): Return the structure of every table, view and materialised view in a schema: kind, comment, storage options, columns and their per-column index counts, index count and constraint count.
- [tableDetails](https://sqlambda.github.io/pg_licht/reference/tableDetails.html): Return the structure of one table: columns with types, defaults, storage and compression, primary key, indexes with their definition and whether each is valid, constraints, foreign keys, inbound foreign keys (referenced_by), triggers with their enabled state, rules, row-level…
- [searchTables](https://sqlambda.github.io/pg_licht/reference/searchTables.html): Find tables across every non-system schema by full-text search over table names, comments, column names and comments, enum labels and grantee names, and return their structure.
- [evaluateIndex](https://sqlambda.github.io/pg_licht/reference/evaluateIndex.html): Plan a statement as if the indexes were different, using hypopg.
- [checkPrivileges](https://sqlambda.github.io/pg_licht/reference/checkPrivileges.html): Report which tools the current role can actually use on this connection, and how the rest fall short.
- [tableStats](https://sqlambda.github.io/pg_licht/reference/tableStats.html): Return the statistics PostgreSQL keeps for one table: estimated row count, seq_scan and idx_scan counts, live and dead tuples, rows modified since the last analyze, rows inserted since the last vacuum, the manual and automatic vacuum and analyze times as four separate fields…
- [roleDependencies](https://sqlambda.github.io/pg_licht/reference/roleDependencies.html): Return what depends on one role, cluster-wide, from pg_shdepend.
- [defaultPrivileges](https://sqlambda.github.io/pg_licht/reference/defaultPrivileges.html): Return ALTER DEFAULT PRIVILEGES entries (pg_default_acl): what grants the NEXT object of each type will get, per granting role and per schema.
- [largeObjects](https://sqlambda.github.io/pg_licht/reference/largeObjects.html): Return how many large objects this database holds and who owns them (pg_largeobject_metadata).
- [replicationStats](https://sqlambda.github.io/pg_licht/reference/replicationStats.html): Return every WAL sender on this server (pg_stat_replication) with its state, sync_state, sent/write/flush/replay LSNs, the byte gap to replay (on a cascading standby, measured from the WAL it has received), and write_lag/flush_lag/replay_lag as SECONDS -- plus replication…
- [listPartitions](https://sqlambda.github.io/pg_licht/reference/listPartitions.html): Return every partitioned table in a schema with its partitioning strategy (range/list/hash), the partition key, how many partitions it has, the combined estimated row count and size of every leaf partition at any depth (a sub-partitioned child holds nothing itself), whether a…
- [partitionDetails](https://sqlambda.github.io/pg_licht/reference/partitionDetails.html): Return one partitioned table with every partition: its bound expression verbatim, whether it is the DEFAULT, whether it is itself partitioned, estimated rows and size (both null, not 0, for a partition never analyzed), and the per-partition live/dead tuples, scan counters and…
- [listTableStats](https://sqlambda.github.io/pg_licht/reference/listTableStats.html): Return the statistics PostgreSQL keeps for every table in a schema: estimated row count, seq_scan and idx_scan counts, live and dead tuples, rows modified since the last analyze, rows inserted since the last vacuum, and the manual and automatic vacuum and analyze times as four…
- [tableSize](https://sqlambda.github.io/pg_licht/reference/tableSize.html): Measure one table on disk: main fork, total table size including TOAST and the free space and visibility maps, index size, grand total, the TOAST relation and each index individually.
- [listTableSizes](https://sqlambda.github.io/pg_licht/reference/listTableSizes.html): Measure every table in a schema on disk: table size, index size and grand total per relation.
- [listFunctions](https://sqlambda.github.io/pg_licht/reference/listFunctions.html): Return function and procedure list for a schema.
- [functionDetails](https://sqlambda.github.io/pg_licht/reference/functionDetails.html): Return detailed function or procedure info including source and trigger usage.
- [searchFunctions](https://sqlambda.github.io/pg_licht/reference/searchFunctions.html): Search functions and procedures by name, source, language, trigger name, or description.
- [listEnums](https://sqlambda.github.io/pg_licht/reference/listEnums.html): Return enum type list for a schema with their values and descriptions.
- [enumDetails](https://sqlambda.github.io/pg_licht/reference/enumDetails.html): Return enum type details including values and which columns use it.
- [searchEnums](https://sqlambda.github.io/pg_licht/reference/searchEnums.html): Search enum types by name, values, or description.
- [listTypes](https://sqlambda.github.io/pg_licht/reference/listTypes.html): Return composite type, domain, and range type list for a schema (excludes enums and implicit table/view row types); composites include their attribute list, domains include base type/nullability/default/constraints, ranges include subtype and the auto-generated multirange type…
- [typeDetails](https://sqlambda.github.io/pg_licht/reference/typeDetails.html): Return composite type, domain, or range type details including attributes/constraints/subtype and which columns use it.
- [listRoles](https://sqlambda.github.io/pg_licht/reference/listRoles.html): Return cluster-wide roles with kind (login/group), attributes (superuser, create_role, create_db, replication, bypass_rls, connection_limit, valid_until), and group memberships.
- [listForeignTables](https://sqlambda.github.io/pg_licht/reference/listForeignTables.html): Return foreign tables in a schema with their foreign server, FDW, options, and columns (does not expose user mapping credentials).
- [listForeignServers](https://sqlambda.github.io/pg_licht/reference/listForeignServers.html): Return cluster-wide foreign servers with their FDW, owner, and options (host/port/dbname-style options only, never user mapping credentials).
- [listTablespaces](https://sqlambda.github.io/pg_licht/reference/listTablespaces.html): Return cluster-wide tablespaces with owner, filesystem location, options, and description.
- [listCollations](https://sqlambda.github.io/pg_licht/reference/listCollations.html): Return collations usable in the current database's encoding for a schema, with provider, locale settings, and determinism flag.
- [listEventTriggers](https://sqlambda.github.io/pg_licht/reference/listEventTriggers.html): Return cluster-wide event triggers with event type, tags, function, owner, enabled status, and description.
- [listPublications](https://sqlambda.github.io/pg_licht/reference/listPublications.html): Return logical replication publications with owner, all-tables flag, per-operation flags (insert/update/delete/truncate), table_count, up to 50 member table names, and on PostgreSQL 15+ the schemas published wholesale via FOR TABLES IN SCHEMA.
- [listSubscriptions](https://sqlambda.github.io/pg_licht/reference/listSubscriptions.html): Return logical replication subscriptions for the current database with owner, enabled status, publications, slot name, and sync settings (never exposes the connection string, which may contain credentials).
- [diskUsage](https://sqlambda.github.io/pg_licht/reference/diskUsage.html): Report what PostgreSQL is holding on disk without needing a shell on the server: WAL directory size and file count, the archive status backlog, temporary files currently on disk, log directory size, per-tablespace sizes and per-database sizes across the whole cluster.
- [columnHistogram](https://sqlambda.github.io/pg_licht/reference/columnHistogram.html): Return the whole value distribution of one column: the most common values with their frequencies, and the full histogram of everything else.
- [checkRoleAccess](https://sqlambda.github.io/pg_licht/reference/checkRoleAccess.html): Answer whether one role holds privileges on one table, view, sequence, function or procedure -- as PostgreSQL evaluates it, via has_table_privilege and its relatives, so role inheritance, grants to PUBLIC, ownership and superuser are all folded in the way the server folds them…
- [subscriptionStats](https://sqlambda.github.io/pg_licht/reference/subscriptionStats.html): Return the runtime state of every logical replication subscription in this database: each worker with its type, pid, the relation it is syncing and how long since it last heard from the publisher; per-table sync state, with the tables that are not yet ready listed individually…
- [listLanguages](https://sqlambda.github.io/pg_licht/reference/listLanguages.html): Return procedural languages installed in the current database (e.g. plpgsql, plpython3u) with owner, trusted/procedural flags, handler function, and description.
- [listExtendedStatistics](https://sqlambda.github.io/pg_licht/reference/listExtendedStatistics.html): Return extended statistics objects (CREATE STATISTICS) for a schema with target table, columns, the statistics kinds declared (ndistinct, dependencies, mcv, expressions), and description -- plus whether they have actually been BUILT.
- [listOperators](https://sqlambda.github.io/pg_licht/reference/listOperators.html): Return custom operators in a schema with left/right operand types, result type, and implementing function; mostly relevant for schemas using extensions with custom types (e.g. PostGIS).
- [listOperatorClasses](https://sqlambda.github.io/pg_licht/reference/listOperatorClasses.html): Return operator classes in a schema with their index access method, input type, and default flag; describes what index types (btree/gist/gin/etc) a type supports.
- [listAccessMethods](https://sqlambda.github.io/pg_licht/reference/listAccessMethods.html): Return index and table access methods available in the cluster (btree, gist, gin, heap, etc) with type and handler function.
- [listCasts](https://sqlambda.github.io/pg_licht/reference/listCasts.html): Return type casts involving at least one user-defined type (excludes built-in-to-built-in casts) with source/target types, context (implicit/assignment/explicit), and method.
- [listTextSearchConfigs](https://sqlambda.github.io/pg_licht/reference/listTextSearchConfigs.html): Return full-text search configurations for a schema with parser and the token-type-to-dictionary mapping.
- [listSequences](https://sqlambda.github.io/pg_licht/reference/listSequences.html): Return sequence list for a schema with type, range, increment, cycle, cache, current value, and owning table.column (for SERIAL/IDENTITY columns).
- [listExtensions](https://sqlambda.github.io/pg_licht/reference/listExtensions.html): Return installed PostgreSQL extensions with version, schema, relocatable flag, and description.
- [databaseSize](https://sqlambda.github.io/pg_licht/reference/databaseSize.html): Return the current database name and its total disk size.
- [serverSettings](https://sqlambda.github.io/pg_licht/reference/serverSettings.html): Return PostgreSQL server settings (pg_settings) grouped by category, each with current value, unit, description, context, type, source, and pending_restart flag.
- [currentActivity](https://sqlambda.github.io/pg_licht/reference/currentActivity.html): Return current server connections and running queries (pg_stat_activity) across all databases: pid, database, user, application_name, backend_type, state, wait event, query text, transaction and query duration, leader_pid for parallel workers, and the backend's xid and xmin.
- [currentLocks](https://sqlambda.github.io/pg_licht/reference/currentLocks.html): Return current locks (pg_locks) joined with the holding backend's query and user, plus which pids are blocking each waiting lock; use to diagnose lock contention.
- [replicationSlots](https://sqlambda.github.io/pg_licht/reference/replicationSlots.html): Return replication slots with retained WAL bytes; a lagging or unused slot holds back WAL indefinitely and is a common cause of disk bloat incidents.
- [databaseStats](https://sqlambda.github.io/pg_licht/reference/databaseStats.html): Return per-database statistics (pg_stat_database) for every database in the cluster: connections, commits/rollbacks, block hit ratio inputs, tuple counts, conflicts, deadlocks, temp file usage, and checksum failures.
- [statementStats](https://sqlambda.github.io/pg_licht/reference/statementStats.html): Return tracked queries from pg_stat_statements under 'statements', with calls, timing, row counts, buffer usage, temporary block I/O and WAL volume, alongside an 'info' block from pg_stat_statements_info whose dealloc counter says whether entries are being evicted -- if it is…
- [wraparoundStatus](https://sqlambda.github.io/pg_licht/reference/wraparoundStatus.html): Return transaction id and multixact wraparound headroom: age(datfrozenxid) and age(datminmxid) for every database, the oldest tables by age(relfrozenxid) including TOAST tables (often the relation actually holding the horizon back), each xid and multixact age as a percentage of…
- [progressStats](https://sqlambda.github.io/pg_licht/reference/progressStats.html): Return every long-running maintenance command currently reporting progress (pg_stat_progress_vacuum, _analyze, _create_index, _cluster, _copy, _basebackup), with the phase, the blocks or tuples done against the total, a completion percentage, and how long it has been running.
- [ioStats](https://sqlambda.github.io/pg_licht/reference/ioStats.html): Return cumulative I/O statistics under 'io', per backend type, object and context (pg_stat_io, PostgreSQL 16+): reads, writes, extends, hits, evictions, reuses, fsyncs and their timings, with a hit percentage.
- [checkpointStats](https://sqlambda.github.io/pg_licht/reference/checkpointStats.html): Return checkpoint, WAL, and background writer activity (pg_stat_checkpointer and pg_stat_bgwriter on PostgreSQL 17+, pg_stat_bgwriter alone before that, plus pg_stat_wal) with field names normalized across both shapes: timed versus requested checkpoint counts and the ratio…
- [tableIOStats](https://sqlambda.github.io/pg_licht/reference/tableIOStats.html): Return per-object buffer cache hit ratios (pg_statio_all_tables): heap_blks_read versus heap_blks_hit, idx_blks_read versus idx_blks_hit, the TOAST and TOAST-index pairs, and a combined ratio, with relation size and scan counts.
- [hostCapacity](https://sqlambda.github.io/pg_licht/reference/hostCapacity.html): Correlate memory and parallelism settings with the capacity of the machine PostgreSQL runs on.
- [duplicateIndexes](https://sqlambda.github.io/pg_licht/reference/duplicateIndexes.html): Return indexes that duplicate or are covered by another index on the same table.
- [tableBloat](https://sqlambda.github.io/pg_licht/reference/tableBloat.html): Return physical storage bloat for a table (pgstattuple/pgstattuple_approx): table size, live/dead tuple counts and percentages, free space and percentage.
- [indexBloat](https://sqlambda.github.io/pg_licht/reference/indexBloat.html): Return physical statistics for one index, from whichever pgstattuple function matches its access method: pgstatindex for btree (tree level, leaf/internal/empty/deleted pages, average leaf density, leaf fragmentation), pgstatginindex for GIN (pending list pages and tuples…
- [checkKey](https://sqlambda.github.io/pg_licht/reference/checkKey.html): Check if a row exists by primary key; validates value types against the PK column types before querying.
- [explainQuery](https://sqlambda.github.io/pg_licht/reference/explainQuery.html): Return the raw EXPLAIN (FORMAT JSON) plan for a statement, either recovered from pg_stat_statements by queryid (full untruncated text) or supplied directly as sql.
- [verifyTopology](https://sqlambda.github.io/pg_licht/reference/verifyTopology.html): Connect to every configured connection and report what each server actually is: its role (primary or replica, from pg_is_in_recovery(), observed now rather than configured), its system identifier, database, address, port and version -- then check the declared topology against…
- [bufferCacheSummary](https://sqlambda.github.io/pg_licht/reference/bufferCacheSummary.html): Return how much of shared_buffers is used, dirty and pinned, with the usage-count histogram from pg_buffercache.
- [bufferCacheContents](https://sqlambda.github.io/pg_licht/reference/bufferCacheContents.html): Return which relations own shared_buffers, aggregated per relation and fork and ranked by buffers held: cached bytes, percent of that fork resident, percent of shared_buffers consumed, dirty buffers, average usagecount and pins.
- [listTopology](https://sqlambda.github.io/pg_licht/reference/listTopology.html): Return the configured topology: which connections share an instance (one postmaster, so they share shared_buffers, WAL, autovacuum workers and disk), which belong to the same replication_group (a primary and its replicas, holding the same data on different servers), and which…
- [listConnections](https://sqlambda.github.io/pg_licht/reference/listConnections.html): Return the configured database connections by name, with the libpq service name or host/port/dbname/user for each, its instance, replication_group and group labels where configured, and which is the default; passwords are never returned and a service file is never expanded.

## Prompts

- [diagnose-slow-query](https://sqlambda.github.io/pg_licht/reference/diagnose-slow-query.html): Trace a slow statement from pg_stat_statements to a plan and a fix.
- [triage-lock-contention](https://sqlambda.github.io/pg_licht/reference/triage-lock-contention.html): Find what is blocking what, and who to look at first.
- [diagnose-deadlock](https://sqlambda.github.io/pg_licht/reference/diagnose-deadlock.html): Work out why a logged deadlock happened, from the log entry plus the schema.
- [triage-active-sessions](https://sqlambda.github.io/pg_licht/reference/triage-active-sessions.html): Find why a server is running more sessions at once than it has CPUs to run them on.
- [bloat-and-vacuum-review](https://sqlambda.github.io/pg_licht/reference/bloat-and-vacuum-review.html): Decide whether bloat is real, and whether autovacuum is keeping up.
- [triage-disk-space](https://sqlambda.github.io/pg_licht/reference/triage-disk-space.html): Find what is filling the disk, and what can safely be freed right now.
- [buffer-cache-review](https://sqlambda.github.io/pg_licht/reference/buffer-cache-review.html): See what is occupying shared buffers and whether it is the right thing.
- [capacity-check](https://sqlambda.github.io/pg_licht/reference/capacity-check.html): Check memory and parallelism settings against the machine.
- [replication-slot-review](https://sqlambda.github.io/pg_licht/reference/replication-slot-review.html): Find what a replication slot is holding back, and what it costs to release it.
- [plan-schema-change](https://sqlambda.github.io/pg_licht/reference/plan-schema-change.html): Choose how to apply a DDL change by measuring the table it targets.
- [check-role-access](https://sqlambda.github.io/pg_licht/reference/check-role-access.html): Work out whether a role can use a table, view, function or procedure, and which rows.
- [explain-and-fix](https://sqlambda.github.io/pg_licht/reference/explain-and-fix.html): Explain one statement, establish why it is slow, and propose a fix that is verified rather than guessed.

## Optional

- [Changelog](https://github.com/sqlambda/pg_licht/blob/v4.3.3/CHANGES.md): every release, with the reasoning behind each change