Selecting a database

When a configuration file defines more than one connection, every operation accepts an optional connection argument naming one of its sections; omitting it uses the default section. listConnections reports what is configured. An operation may instead take instance, replication_group or group and run once per member; see Topology: instances, replication groups, and groups.

Connection strings

A connection string is handed to libpq unchanged, so both accepted forms work:

postgresql://user:pass@host:5432/dbname?sslmode=require
host=localhost port=5432 dbname=mydb user=daniel

A single database needs nothing beyond DATABASE_URL.

The connections file

To reach several databases from one server, list them as INI sections:

[default]
port    = 6432
dbname  = pglicht
user    = daniel
sslmode = prefer
; no password here — use ~/.pgpass or a service file

[prod]
; a libpq service name, resolved from ~/.pg_service.conf,
; $PGSERVICEFILE, or $PGSYSCONFDIR/pg_service.conf
service = db01_ro

[staging]
service = db01_ro        ; the service supplies the defaults
dbname  = db01_staging   ; and explicit keys override them

Pass a section name as the connection argument of any operation to run it against that database; omit it to use [default], or, if there is no [default], the first section in the file.

Topology: instances, replication groups, and groups

Three optional keys per section say how the configured databases relate. They are deliberately separate, because only the first two license a conclusion:

[billing_prod]
service           = billing-prod
instance          = pg-prod-01      ; one postmaster
replication_group = billing-ha      ; a primary and its replicas
group             = prod, billing   ; an operator label

[billing_ro]
service           = billing-replica
instance          = pg-prod-02
replication_group = billing-ha
group             = prod, billing, reporting

instance

One postmaster. Its databases share shared_buffers, WAL, autovacuum workers, max_connections and disk, so one database's checkpoint storm really is another's latency. PostgreSQL's glossary calls this a database cluster.

replication_group

A primary and its replicas: the same data on different servers, each keeping its own statistics counters.

group

An arbitrary label, repeatable as a comma-separated list. It implies nothing, which is the point — it is how an agent asks about "dev" without knowing any connection name.

There is no cluster key. PostgreSQL uses that word for the first sense and RDS and Aurora for the second, so it means opposite things to the two people most likely to read the file. There is no role key either: primary or replica is observed on every connection through pg_is_in_recovery() and never cached, because failover swaps it and failover is exactly when this server gets used.

An instance is inferred when two sections share a host and port, and reported as ‘inferred’ rather than ‘declared’. Behind a pooler one endpoint can front several instances and one instance can be reached through several pooler ports, so an inferred instance groups output without licensing a contention claim; verifyTopology settles it. A section using service is never inferred, since the service file is deliberately not expanded and its host and port are therefore unknown.

Sweeping several connections

Passing instance, replication_group or group in place of connection runs the operation once per member and returns one result per member, in file order. A member that cannot be reached is reported in place rather than failing the sweep.

Which of the three an operation accepts depends on where its answer actually varies, and its input schema says which. Catalog and bloat readings vary between the databases of one instance but not between members of a replication group, because a physical replica is byte-identical. currentActivity, currentLocks, statementStats and the buffer cache operations are the other way round: they report the whole instance from any one of its databases, so sweeping them across it would repeat one answer, and that is refused with -32602.

duplicateIndexes, indexBloat and tableIOStats are the case worth knowing: they carry idx_scan, which is each server's own. An index that reads as unused on the primary may be carrying a replica's entire reporting workload, and only that replica's scan counts show it. role set to ‘primary’ or ‘replica’ narrows a sweep to one side. connection, instance, replication_group, group and role are read by pg_licht_mcp itself out of a call's arguments. An operation that declares an argument of its own by one of those names keeps it — roleDependencies takes a role, naming the role to ask about — and cannot be targeted by that selector. The tool's argument wins, in the published schema and in the call alike.

A sweep is sequential — the server is a single-threaded loop — so its width is wall-clock time. listTopology reads the configuration file and opens no connection, so it is cheap to call first.

A worked example covering all of this is shipped as cpp/test/connections.example.ini in the source tree.

libpq passthrough and services

Keys are passed through to libpq, so anything libpq accepts works, including service, which may be used on its own in place of host, port, dbname, and user. A service name is resolved from ~/.pg_service.conf, PGSERVICEFILE, or $PGSYSCONFDIR/pg_service.conf.

Each section must set either service or at least dbname; a section with neither is reported by name at startup rather than failing later with an obscure libpq error.

The key options is rejected. PgBouncer refuses GUCs in the startup packet, so pg_licht_mcp sets what it needs per transaction instead — the read-only guard always, and statement_timeout from statement_timeout_ms below; see SECURITY CONSIDERATIONS.

The statement ceiling

Every transaction sets statement_timeout, so no single call can run without an upper bound. The default is 120000 milliseconds. Set it per section:

[prod]
service              = billing-prod
statement_timeout_ms = 300000

[wan]
service              = analytics-eu
statement_timeout_ms = 15000

A value of 0 removes the ceiling and leaves the server's own statement_timeout untouched, which is the behaviour of 3.2.0 and earlier. With a single DATABASE_URL, use PG_LICHT_STATEMENT_TIMEOUT_MS instead.

Most operations are catalog reads that finish in milliseconds and never approach it. The ones that can are those whose cost scales with the server rather than with the query: tableBloat with exact true, indexBloat on a btree or hash index, and bufferCacheContents, which scans every buffer in shared_buffers. Raise it for the connection when such a scan is the point of the call.

A statement that reaches the ceiling is reported as the ceiling being reached, with the value and the key to change, rather than as an execution error: nothing went wrong, the answer simply needs longer than the connection allows.

Like the read-only guard, the ceiling is transaction-scoped rather than session-scoped, which is what makes it survive a transaction-mode pooler. It cannot be carried in the conninfo: statement_timeout is a GUC rather than a libpq keyword, and the options keyword that could carry it is the one PgBouncer rejects.

Host capacity

Total RAM and vCPU count belong to the machine, not to the cluster. PostgreSQL has no catalog for them, and a backend could not read them portably in any case — it would read the client's host, which is the wrong machine. They are therefore injected, and hostCapacity correlates them with shared_buffers, work_mem, and the rest. Four optional keys per section carry them:

[prod]
service      = db01_ro
host_ram_mb  = 65536
host_vcpus   = 16
host_storage = local nvme          ; free text, never interpreted
host_note    = shared with the app ; free text, never interpreted

These keys are consumed by pg_licht_mcp and never reach libpq, which would reject the whole connection string over an unrecognised keyword. host_ram_mb and host_vcpus must be positive integers; anything else is rejected at startup naming the section and the key, because ‘64GB’ where megabytes are meant would be wrong by three orders of magnitude and would silently invalidate every derived ratio.

There are two other ways in. The environment variables under ENVIRONMENT apply to the single-connection DATABASE_URL form only, where there is no question which host they describe; with a connections file each section declares its own, since the sections may well live on different machines. And an agent that inspects the host at run time — over SSH, say — can pass ram_mb and vcpus straight to hostCapacity, which takes precedence over both.

With several databases on one postmaster, a reserved [instance:name] section carries the same keys once and its members inherit them; an explicit per-connection value still wins:

[instance:pg-prod-01]
host_ram_mb  = 65536
host_vcpus   = 16

[app]
service  = app_ro
instance = pg-prod-01

An [instance:name] section is not a connection: it never appears in listConnections and never becomes the default. It accepts the four host capacity keys and nothing else, and is rejected at startup if no section declares that instance. Inheritance follows instance only, never replication_group: replicas routinely run on smaller machines, and inheriting the primary's RAM would give every replica a confidently wrong shared_buffers ratio.

Budgets

Limits pg_licht_mcp applies to itself live in an optional budgets.ini. Today there is one: what explainQuery may execute when analyze is combined with explicit settings, as shares of the host capacity declared for the connection.

[analyze]
memory_percent   = 10   ; worst-case plan memory, % of host_ram_mb
vcpus_per_worker = 4    ; one parallel worker per this many host_vcpus

Those are the built-in values, used when no file is found. memory_percent takes 1 to 100 and vcpus_per_worker 1 to 1024. The first of these is read: PG_LICHT_BUDGETS, which must then exist; budgets.ini in the same directory as the connections file in use; and ~/.config/pg_licht/budgets.ini. The last two are used only if they exist.

The file is strict: an unknown section or key, or a value out of range, stops the server at startup rather than leaving a limit in force that the operator did not write. It holds no credentials and may be readable by anyone, but it must not be writable by anyone but its owner, since a file another user can edit is a limit another user can raise. Each refusal and each permitted run reports the values and the file they came from under execution_budget.

Resolution order

The connection source is resolved in this order:

1. --config file
2. PG_LICHT_CONFIG
3. ~/.config/pg_licht/connections.ini, only if it exists
4. DATABASE_URL
5. the positional database_url argument

File permissions

Because sections may carry passwords, the configuration file must not be group- or world-accessible. pg_licht_mcp refuses to load it otherwise, the same rule libpq applies to ~/.pgpass:

chmod 600 ~/.config/pg_licht/connections.ini

Note that libpq enforces that rule on ~/.pgpass but not on pg_service.conf, so if a service file holds a password its permissions are yours to manage.

Extension schemas

Operations backed by an extension (statementStats, explainQuery, tableBloat and indexBloat) locate its objects through pg_extension and reference them schema-qualified. The extension therefore does not have to be installed in public, and does not have to be on any search_path: ‘CREATE EXTENSION pgstattuple SCHEMA extensions’ works exactly as an installation into public does.

This cannot be solved with a search_path instead. pg_licht_mcp sets no session state, because the target deployment is a transaction-mode pooler that discards it, and PgBouncer rejects a GUC passed through the options connection keyword outright.

The location of each extension is resolved once per configured connection and remembered for the life of the process, since one connection addresses one database. It is looked up again after any failure to find the object, so an extension installed (or moved with ‘ALTER EXTENSION ... SET SCHEMA’) while pg_licht_mcp is running is picked up on the next call. An extension that is absent is reported as not installed, with the hint naming the setup steps; see DIAGNOSTICS.

Privileges

checkPrivileges

Which operations the current role can actually use on this connection, and how the rest fall short.

Most of this server works for any role that can connect, because the catalog is world-readable. Measured on PostgreSQL 18: a bare LOGIN role with no grants runs 50 of the 62 operations at full fidelity, the monitoring role takes that to 54, and the three that remain are exactly the three that read row data — the tableStats column histograms, checkKey and explainQuery.

Worth calling first against an unfamiliar connection or a restricted role. The alternative is discovering the limits one operation at a time, and the discovery misleads: tableStats under a role without SELECT returns every column present with null statistics, which is what a table that was never analyzed looks like, and statementStats returns rows whose query text is <insufficient privilege> with no count of what was hidden.

Operations named in neither list are fully available; available is a count rather than a list, because the exceptions are the answer. denied means the operation cannot answer at all, degraded that it answers less than a privileged role would see. The three that read row data are always degraded and never denied, because privilege there is per object: a role without blanket read access may still hold SELECT on some tables and not others.

An absent extension is distinguished from a missing privilege, because the two send an operator to different fixes.

No role memberships and no GRANT statements appear in the output. Which predefined role gates an operation is PostgreSQL's business; the caller needs to know what works. Naming a grant in the hint of an operation that failed is diagnosis; listing grants beside every unavailable operation would be a standing recommendation to escalate privilege, which is not this server's to make.

checkRoleAccess grantee schema object

Whether one role holds privileges on one table, view, sequence, function or procedure, answered by has_table_privilege() and its relatives rather than reconstructed from ACLs, so inheritance, grants to PUBLIC, ownership and superuser fold in the way the server folds them. Needs no grant of its own: these functions and the catalog are world-readable, so any role that can connect may ask about any other. The argument is grantee because role is reserved server-wide for narrowing a sweep to a primary or a replica.

Schema USAGE and database CONNECT come back beside the object privileges, since a grant on the table is inert without them and the resulting error names the table. Where a table-level privilege is absent but individual columns carry it, those columns are listed. A routine name reports every overload with its signature and security_definer.

Row-level security is not folded in, because has_table_privilege() does not consider it: a true here can still return no rows. The row_level_security block carries whether RLS is enabled, whether it is forced, whether this particular role is subject to it — the owner is exempt unless forced — and every policy with whether it applies to the role. Enabled with no applicable permissive policy denies everything.

Not to be confused with checkPrivileges, which reports which of this server's own operations the connecting role can run.

roleDependencies role

What depends on one role, from pg_shdepend. by_kind separates owner, which blocks DROP ROLE and is cleared by REASSIGN OWNED, from acl and policy, which DROP OWNED clears.

pg_shdepend is shared across the cluster, so the counts cover every database; an object id resolves only from the database it lives in, so objects names this database and the shared catalogs while others appear as counts. Tables, functions, schemas and types are named as schema.name; every other class, a policy or a default privilege included, by pg_identify_object(), so a policy reads as ‘p on public.t’.

defaultPrivileges [schema]

ALTER DEFAULT PRIVILEGES entries: what grants the next object of each type will get. Scope global overrides the hard-wired defaults; per-schema entries are added to them. A default applies only to objects created by granted_by. Naming a schema returns its entries and the global ones, since both decide what the next object there gets; a schema that does not exist is an error.

Schema exploration

listSchemas

All schemas with their table and view names and role grants.

listTables schema

Structure of the tables and views in schema: kind, description, per-table reloptions, columns (name, description, per-column index count), and index and constraint counts. Full index and constraint definitions live in tableDetails, not here; this operation is deliberately light for browsing a schema. Carries no statistics and no sizes: see listTableStats and listTableSizes.

tableDetails schema table

Structure of one table: columns (types, nullability, defaults, per-column TOAST storage strategy and compression method), TOAST table name (null if the table has no toastable columns), primary key as an ordered column list, index definitions with valid, constraints, foreign keys, inbound foreign keys (referenced_by), triggers (timing, events, when condition, language, enabled), rules (excluding the implicit view _RETURN rule), row-level security status and policies, view definition, and role grants.

Two fields report an object that is not doing what its definition suggests. An index with valid false is what a CREATE INDEX CONCURRENTLY that failed leaves behind: it occupies disk and is maintained on every write, and the planner never uses it. A trigger with enabled ‘disabled’ still carries its whole definition, because ALTER TABLE ... DISABLE TRIGGER changes no text; ‘replica’ and ‘always’ are the session_replication_role states, which is how a trigger can be off for the application and on for a replication apply worker.

Everything here changes only when someone issues DDL, and none of it is sampled row data. For row counts, scan counters, autovacuum state and the pg_stats histograms see tableStats; for measured sizes see tableSize.

tableStats schema table

Statistics PostgreSQL already keeps for one table: estimated row count, size_estimate (relpages * block_size with the estimated_from timestamp that produced it), block_size is the server's compile-time BLCKSZ, 8192 unless it was built otherwise. seq_scan and idx_scan, live and dead tuples, mods-since-analyze, inserts-since-vacuum, the four pg_stat_user_tables timestamps (last_vacuum, last_autovacuum, last_analyze, last_autoanalyze), per-index scan counts, and the per-column pg_stats histograms (null_frac, avg_width, n_distinct, physical order correlation, most_common_vals and their frequencies). Reads the catalog and the statistics collector only: no relation is opened and no file is measured.

most_common_vals is literal values sampled from the column, up to default_statistics_target of them. This is the only operation that returns them.

The manual and automatic timestamps are separate, and mean what the catalog means by them. A recent last_autovacuum says autovacuum is reaching the table; a recent last_vacuum beside a null last_autovacuum says the opposite — somebody is keeping the table alive by hand and the cron job is hiding the finding rather than being it. Through 4.1.1 both were returned merged under last_vacuum, which made those two indistinguishable. estimated_from remains the latest of all four, since any of them refreshes relpages and reltuples.

columnHistogram schema table column

The whole value distribution of one column: the most common values with their frequencies, and the full histogram of everything else. tableStats carries three points off that histogram — low, mid and high — which is enough to pick parameters to re-plan a statement with; this is for when the shape of the distribution is itself the question.

The two halves are complements, not alternatives. ANALYZE puts the most frequent values in most_common_vals and builds the histogram only from what is left, so a value in the MCV list never appears in the bounds however common it is, and reading either alone misdescribes the column. The bounds are equal-frequency: consecutive entries delimit buckets holding roughly the same number of rows, so bounds bunched together are a dense region and a wide gap a sparse one. statistics_target says why the histogram is the width it is, and is the knob that changes it.

Costs nothing to read, and returns more literal column values than any other operation here: the bounds and the most common values are rows sampled out of the table. pg_stats filters on has_column_privilege(), so a role without SELECT on the column gets nulls rather than data.

listTableStats schema

The same statistics for every table in schema, without the per-column histograms. Name one table to tableStats for those.

tableSize schema table

Measured size of one table: main fork, table size including TOAST and the free space and visibility maps, index size, grand total, the TOAST relation, and each index individually.

Costs more than it looks. pg_table_size() and its relatives are not physical reads — they stat(2) one file per 1 GB segment and read no blocks — but each opens the relation with AccessShareLock, so a table being rewritten by an ALTER TABLE holds the call behind AccessExclusiveLock until statement_timeout_ms fires. Prefer size_estimate from tableStats, which is free, and call this when the estimate is too stale to act on. A partitioned table reports its own storage, which is zero; measure the partitions.

listTableSizes schema

Measured table, index, and total size for every relation in schema. The lock caveat above applies with more force here: one relation is opened per table, so a single table under AccessExclusiveLock blocks the whole call rather than one row of it, and a wide schema means thousands of file-metadata calls. This is one of the operations worth raising statement_timeout_ms for.

listPartitions schema

Every partitioned table in a schema: strategy, partition key, partition count, the combined estimated rows and size of every leaf partition at any depth, whether a DEFAULT partition exists and how many rows it holds, and how many partitions are themselves partitioned. default_rows is null, not zero, when the default partition has never been analyzed: reltuples stays at -1 until then even after rows arrive, and has_default says whether a default exists at all. rows and size_estimate cover only the leaves that have been analyzed; never_analyzed says how many of the leaf_partitions they could not see, and both are null when none has been measured. A sub-partitioned child holds no rows itself, so its leaves are what is summed. Reads reltuples and relpages, so no relation is opened and no lock is taken.

A growing default partition is the finding: rows land there when they match no bound, so it is a missing partition that has not failed loudly yet.

partitionDetails schema, table

One parent and every partition: the bound expression verbatim, whether it is the DEFAULT, whether it is itself partitioned, estimated rows and size, and the per-partition live and dead tuples, scan counters and vacuum and analyze timestamps. Rows and size are null for a partition that has never been analyzed, rather than a zero that would read as empty.

Autovacuum runs per partition, so a parent has no vacuum state of its own and ranking parents finds nothing while one child falls behind. Bounds are verbatim rather than parsed: a bound carries whatever types the key columns have, and a misparsed boundary is worse than an unparsed one. For a range parent, the highest upper bound against now() says whether the next period's partition exists.

largeObjects

How many large objects this database holds and who owns them. Large objects live in a catalog rather than a relation, so every size tool here is blind to them while diskUsage counts their bytes. No sizes: the bytes are in pg_largeobject, which is not publicly readable.

listFunctions schema

Functions and procedures in schema with kind, language, return type, arguments, volatility, security_definer, and is_strict.

functionDetails schema function

Source code, full definition, arguments, volatility, trigger usage, and role grants.

listEnums schema

Enum types in schema with their ordered values and descriptions.

enumDetails schema enum

Ordered values and every column, across all tables, that references the enum.

listTypes schema

Composite types, domains, and range types in schema. Excludes enums, implicit table and view row types, and the auto-generated multirange companion of each range type. Composites include their attribute list, domains include base type, nullability, default, and constraints, and ranges include the subtype and multirange type name.

typeDetails schema type

Attributes, constraints, or subtype of a composite type, domain, or range type, and which columns use it.

listSequences schema

Sequences in schema with data type, range, increment, cycle, cache size, current value, and owning table and column for SERIAL and IDENTITY columns.

listExtendedStatistics schema

Extended statistics objects (CREATE STATISTICS) for schema with target table, columns, statistics kinds (ndistinct, dependencies, mcv), and description.

Catalog search

searchTables web_search

Full-text search across table and schema names, descriptions, column names and descriptions, enum values used by columns, and role names. Returns the same fields as listTables plus roles. Structure only, and there is no statistics counterpart: a text search is how a table is found, not how a counter is read. Name a match to tableStats or tableSize for those.

searchFunctions web_search

Full-text search across function names, source code, language, trigger names, and descriptions.

searchEnums web_search

Full-text search across enum names, values, and descriptions.

Cluster-wide objects

listRoles

Cluster-wide roles with kind (login or group), attributes (superuser, create_role, create_db, replication, bypass_rls, connection_limit, valid_until), and group memberships.

listTablespaces

Cluster-wide tablespaces with owner, filesystem location, options, and description.

listEventTriggers

Cluster-wide event triggers with event type, tags, function, owner, enabled status, and description.

listLanguages

Procedural languages installed in the current database, such as plpgsql or plpython3u, with owner, trusted and procedural flags, handler function, and description.

listAccessMethods

Index and table access methods available in the cluster (btree, gist, gin, heap, and so on) with type and handler function.

listCasts

Type casts involving at least one user-defined type, excluding built-in-to-built-in casts, with source and target types, context, and method.

listExtensions

Installed extensions with version, schema, relocatable flag, and description.

Extensibility and text search

listCollations schema

Collations usable in the current database's encoding for schema, with provider, locale settings, and determinism flag.

listOperators schema

Custom operators in schema with left and right operand types, result type, and implementing function. Mostly relevant for schemas using extensions with custom types, such as PostGIS.

listOperatorClasses schema

Operator classes in schema with their index access method, input type, and default flag. Describes which index types a given type supports.

listTextSearchConfigs schema

Full-text search configurations for schema with parser and the token-type-to-dictionary mapping.

Foreign data and replication

listForeignTables schema

Foreign tables in schema with their foreign server, foreign data wrapper, options, and columns. User mapping credentials are never exposed.

listForeignServers

Cluster-wide foreign servers with foreign data wrapper, owner, and options; host, port, and dbname-style options only, never user mapping credentials.

listPublications

Logical replication publications with owner, all-tables flag, per-operation flags, and member tables.

listSubscriptions

Logical replication subscriptions for the current database with owner, enabled status, publications, slot name, and sync settings. Structure only: what CREATE SUBSCRIPTION declared, changing on DDL and not otherwise. For whether the subscriber is keeping up, call subscriptionStats.

The connection string is never exposed, as it may contain credentials. PostgreSQL agrees: the catalog revokes subconninfo from public and grants every one of its other seventeen columns, so selecting it would fail for any non-superuser as well as leaking a password.

subscriptionStats

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 not yet ready listed individually against counts for the rest; and the apply and sync error counters with the per-conflict-type counters beside them.

Answers whether a subscriber is keeping up and, if not, whether it is stuck copying a table or failing to apply. Reports no byte lag, because a subscriber cannot measure it: received_lsn and latest_end_lsn track each other rather than the publisher, so their difference is zero even when the subscriber is far behind. For lag in bytes call replicationSlots on the publisher and read retained_wal_bytes.

When a table is still copying, the worker pid is a real backend pid: pass it to progressStats for the byte and tuple counts of that exact copy. bytes_total is zero there, and so bytes_percent is null: a table sync streams from the publisher rather than reading a file of known size.

errors is absent entirely on PostgreSQL 14, which has no pg_stat_subscription_stats, and its conflicts are PostgreSQL 18. An absent key means the server cannot answer, which is not the same as no errors.

replicationSlots

Replication slots with retained WAL bytes. A lagging or unused slot holds back WAL indefinitely and is a common cause of disk bloat incidents.

wal_status is the verdict that retained WAL bytes only hint at: ‘extended’ means the slot is already past max_wal_size, and ‘lost’ means the WAL it needs is gone and the slot is unusable. safe_wal_size is how much more WAL may be written before that happens.

The spill and stream counters come from pg_stat_replication_slots, a different view: they show logical decoding spilling large transactions to disk, which is invisible in the slot's own row and is a common, silent throughput cliff. PostgreSQL 16 adds conflicting, and 17 adds invalidation_reason and inactive_since.

replicationStats

Every WAL sender on this server with state, sync state, the LSNs, the byte gap to replay — on a cascading standby measured from the WAL it has received — and write_lag, flush_lag and replay_lag in seconds — plus replication origin progress. The only source of replication lag as a time. Senders are keyed by application_name plus pid: a walreceiver's default name is its cluster_name, which Debian sets per major rather than per host, so two standbys routinely share one and a name-only key would fold them into a single entry.

Two readings that are routinely misread. The view is security restricted per row rather than refused, so a role without pg_read_all_stats sees the senders exist with columns null, which looks like an idle replica and is a permission answer. And the lag columns revert to NULL a short time after a standby has entirely caught up and WAL activity stops, so a null lag there means caught up while a non-null one is the last measurement rather than the current state.

Monitoring and statistics

databaseSize

Current database name and total disk size.

serverSettings

All server settings (pg_settings) grouped by category, each with current value, unit, description, context, type, source, and pending_restart flag.

currentActivity [pid, query_id, min_duration_s, state]

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.

query_id is the join key to statementStats and explainQuery, which is what turns “this is running now” into “this is its plan”. It is returned as text, since a 64-bit value does not survive JSON number precision, and is null unless compute_query_id is enabled; the default ‘auto’ enables it when pg_stat_statements is loaded. On PostgreSQL 17 and newer each wait event also carries its prose description from pg_wait_events.

All four filters are optional and combine; with none of them the whole view is returned, which on a busy server is mostly idle connections and internal processes.

pid

A single backend, together with its parallel workers, matched on leader_pid. Being shown a leader without its workers hides where the work is happening, which is the reason for asking.

query_id

Only backends running this query_id, as a decimal string. This is how a statement identified by statementStats is traced to whoever is running it now.

min_duration_s

Only backends whose current query has been running at least this many seconds. Plain idle backends are excluded, since their query_start dates a statement that already finished.

state

Only backends in this state, e.g. ‘active’ or ‘idle in transaction’.

currentLocks [pid]

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.

pid

Restrict the result to this backend and every backend blocking it, transitively, resolved through pg_blocking_pids(). Each row is tagged with chain_depth: 0 is the backend asked about, and the largest depth is the one at the root of the pile-up, which is the one to look at first. Omit it for every lock in the cluster.

databaseStats

Per-database statistics (pg_stat_database) for every database in the cluster: connections, commits and rollbacks, block hit ratio inputs, tuple counts, conflicts, deadlocks, temp file usage, and checksum failures.

The session counters (session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed) distinguish a database that is busy from one merely holding transactions open, which the commit and rollback counts alone cannot tell apart. PostgreSQL 18 adds parallel_workers_to_launch and parallel_workers_launched; a shortfall between them means queries planned for parallelism ran without it.

statementStats [limit, query_id, order_by, min_calls]

The slowest tracked queries by total execution time (pg_stat_statements), with calls, timing, row counts, buffer usage, temporary block I/O, and WAL volume, under statements, alongside an info block from pg_stat_statements_info. Returns a clear error with setup instructions if the extension is not installed.

info.dealloc counts how often the extension has evicted its least-used entries because pg_stat_statements.max was exceeded. A non-zero value means this list is not the slowest queries in the cluster but the slowest of those that survived eviction — a distinction that cannot be drawn from the rows themselves, which is why it is returned beside them.

query_id is text, not a number: it is a 64-bit value that a client parsing JSON numbers as doubles would round, and the rounded value would then not be found by explainQuery.

limit

Maximum number of statements to return. Defaults to 20.

query_id

Only statements with this queryid, as a decimal string; their query text comes back whole rather than truncated. pg_stat_statements keeps one entry per user and database, so a queryid can match more than one row.

order_by

Ranking column: total_exec_time (the default), mean_exec_time, max_exec_time, calls, rows, shared_blks_read, temp_blks_written, or wal_bytes. Ranking by total time buries a statement called twice at 40 seconds under one called ten million times at 2 milliseconds; mean_exec_time is the other question, and there was previously no way to ask it. Anything outside this list is rejected: the sort column cannot be a bind parameter, so it is resolved through a fixed table rather than reaching the SQL.

min_calls

Ignore statements called fewer times than this, to keep one-off maintenance queries out of a mean_exec_time ranking.

progressStats [pid, relation]

Every long-running maintenance command currently reporting progress: VACUUM, ANALYZE, CREATE INDEX, CLUSTER, COPY, and base backups. Each carries its phase, blocks or tuples done against the total, a completion percentage, and how long it has been running. Use it to decide whether a VACUUM will finish before wraparound — the natural companion to wraparoundStatus — or whether a CREATE INDEX is stuck waiting on a locker.

All six categories are always present, empty when nothing is running, so an absent key never has to be read as either “idle” or “unsupported here”. PostgreSQL 17 renamed the vacuum dead-tuple columns from counts to bytes; both are reported under their own names, with dead_tuple_unit saying which the server produced, because they measure different things.

pid

Only the command running in this backend; pair it with the pid from currentActivity.

relation

Only commands operating on this table, named bare or schema-qualified. A base backup has no relation, so this excludes that category entirely. A name that matches nothing yields an empty result rather than an error.

ioStats [pid, backend_type, object, context]

Cumulative I/O statistics per backend type, object, and context (pg_stat_io): reads, writes, extends, hits, evictions, reuses, fsyncs, their timings, and a hit percentage. This is where buffers written directly by backends, vacuum's ring-buffer reuse, and bulk read and write I/O become visible separately from the aggregate counters in checkpointStats. Rows with no activity at all are omitted, since the view is a dense matrix of combinations most of which are structurally impossible. Byte counters are reported on PostgreSQL 18 and newer, where they replaced the former op_bytes column.

Requires PostgreSQL 16 or newer; older servers get a clear error naming the alternatives.

pid

Report this one backend instead of the cluster-wide aggregate, via pg_stat_get_backend_io(), together with its WAL volume from pg_stat_get_backend_wal(): a backend can be quiet in I/O and still be generating WAL heavily. pg_stat_io has no pid column at all, so this is a different source rather than a filter, and it requires PostgreSQL 18.

backend_type

Only this backend type, e.g. ‘client backend’ or ‘autovacuum worker’.

object

Only this object class, e.g. ‘relation’ or ‘temp relation’.

context

Only this I/O context, e.g. ‘normal, ‘vacuum, ‘bulkread’’’, or ‘bulkwrite’.

wraparoundStatus [schema, limit]

Transaction id and multixact wraparound headroom. For every database: age(datfrozenxid), age(datminmxid), and each as a percentage of its own freeze_max_age and of the limit of 2144483647, at which the cluster stops accepting commands that assign a new transaction id or multixact. Both axes carry both budgets: multixacts have the same three-million stop limit and the same forty-million warning as transaction ids, and members-space exhaustion is its own incident. That figure is not 2^31: PostgreSQL refuses new transaction ids three million short of wraparound (xidStopLimit = xidWrapLimit - 3000000), and begins warning in the log forty million short of it. Both are reported — wraparound_limit and wraparound_warn_limit under limits, with xids_until_wraparound_limit and xids_until_warn_limit per database. The warning threshold is the one an operator has usually already seen fire. For tables: the oldest by age(relfrozenxid), with the effective autovacuum_freeze_max_age and whether it came from the table's storage parameters or the server, plus last vacuum times and dead tuple counts.

TOAST tables are included, labelled with the table they belong to. They carry their own relfrozenxid, do not appear in pg_stat_user_tables, and are frequently the relation actually holding the horizon back.

On PostgreSQL 18 and newer each table also reports frozen_percent (pg_class.relallfrozen) and its cumulative vacuum and autovacuum times. Those turn an age into an estimate of the work remaining: an old relfrozenxid on a table that is already almost entirely frozen is a different problem from the same age with nothing frozen, and the timings say whether autovacuum has been trying and failing to keep up or has simply never run.

schema

Restrict the table list to one schema. Omit it to cover the whole database, which is the scope wraparound risk is actually measured over.

limit

Maximum number of tables to return, oldest first. Defaults to 20.

diskUsage

What PostgreSQL is holding on disk, without a shell on the server: WAL size and file count, the archive status backlog, temporary files currently on disk, the log directory, and sizes per tablespace and per database across the cluster.

How much room is left is not knowable from SQL. PostgreSQL exposes no function for total or free space, so this reports what is consuming space and how it divides, never the headroom. A tablespace also carries its location, which matters because the database that is growing and the volume that is full need not be the same device.

A climbing .ready count in the archive status is a failing archive_command retaining every segment it has not archived — indistinguishable from an abandoned replication slot by WAL size alone, and a different fix.

The pg_ls_(*) sections need pg_monitor. Each section is guarded on its own savepoint, so one refusal returns an error in that key and leaves the rest answered: pg_ls_logdir() raises rather than returning empty when logging_collector is off.

The four directory sections are trivial. The tablespace and database sizes are the whole cost: they walk the tree and stat every segment file, so they scale with file count rather than with bytes, and the two overlap because pg_tablespace_size() on pg_default covers the same subtree the per-database sizes do. Measured at 314 ms for the whole call against a cluster of roughly a terabyte, where a bare databaseSize is already 163 ms. Nothing is read, no relation is opened and no lock is taken — this is metadata rather than I/O — but that was measured with directory entries warm in the page cache, and a filling disk is when they are not. statement_timeout bounds it, so the failure mode is a timeout that names itself.

checkpointStats

Checkpoint, write-ahead log, and background writer activity: timed against requested checkpoint counts and the ratio between them, write and sync time, buffers written by the checkpointer, by the background writer, and directly by backends, the pg_stat_wal record, full-page-image, and byte counters, and the settings that govern all of it (checkpoint_timeout, max_wal_size, checkpoint_completion_target, the bgwriter knobs).

The source views differ by major version: PostgreSQL 17 split the checkpointer counters into pg_stat_checkpointer and moved the backend-written buffer counts to pg_stat_io, while earlier versions keep everything in pg_stat_bgwriter. Field names are normalized across both, so a caller never branches on the version; the source field names the views that produced the numbers.

tableIOStats schema [table, limit]

Buffer cache hit ratios per object (pg_statio_all_tables): heap_blks_read against heap_blks_hit, idx_blks_read against idx_blks_hit, the TOAST and TOAST-index pairs, and a combined ratio, with relation size and scan counts for context.

A ratio is null, not zero, for an object that has seen no reads at all, so “no traffic” is never mistaken for “every read missed the cache”.

table

A single table. Only a named table gets the per-index breakdown from pg_statio_all_indexes; over a whole schema it would multiply the payload by the index count without making the table-level ratios any easier to read.

limit

Maximum number of tables to return, most physical reads first. Defaults to 20.

bufferCacheSummary

How much of shared_buffers is used, dirty and pinned, with the usage-count histogram, from pg_buffercache.

tableIOStats counts only what shared_buffers served, so a miss there may still have come from the operating system page cache at RAM speed; this is the only in-core view of that split. Read the histogram rather than a hit ratio: mass at usage count 2 to 5 is a stable working set, everything at 0 to 1 with no unused buffers is clock-sweep churn, and those are the same ratio with opposite diagnoses. A single sample is weak evidence; two samples minutes apart are the method.

Requires the pg_buffercache extension at version 1.4 or later — the summary functions arrived there — and a role with pg_monitor. Version 1.4 ships with PostgreSQL 16; on 14 and 15 there is no 1.4 to update to, and the operation says so and points at bufferCacheContents, which reads the view and works on every version. On 16 and newer an out-of-date extension is reported as such rather than as absent, and a missing grant names the grant.

bufferCacheContents [limit]

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 usage count and pins. Never raw per-buffer rows — a machine with 16GB of shared_buffers has two million of them.

Answers which relation drives checkpoint writeback (pair with checkpointStats), whether the visibility map fork is resident enough for index-only scans to pay off, and which database's working set is displacing the others.

Only buffers belonging to this database and to the shared catalogs are resolved to names. Buffers held by other databases on the same instance are visible to PostgreSQL but not reported, because they cannot be resolved locally and unnamed rows would invite being read as this database's.

limit

Maximum number of relation and fork rows to return. Defaults to 20, capped at 200.

hostCapacity [ram_mb, vcpus, storage]

Memory and parallelism settings correlated with the capacity of the machine PostgreSQL runs on. Returns every memory-related setting resolved to bytes, whatever unit it is counted in, and derived ratios: shared_buffers and effective_cache_size as a percentage of RAM, work_mem times max_connections, maintenance_work_mem times autovacuum_max_workers, their combined total, and parallel workers per vCPU.

Total RAM and vCPU count are properties of the host, not of the cluster: no catalog holds them, so they have to be injected. See Host capacity. The reported source says which of the three paths supplied them. Every ratio that needs RAM is null when none was supplied, together with a hint naming the three ways to provide it. Nothing is ever guessed: a wrong memory figure would silently invalidate every number derived from it.

ram_mb

Total host memory in megabytes, overriding any configured value.

vcpus

Number of vCPUs or cores available to the host, overriding any configured value.

storage

Free-text description of the storage, echoed back and never interpreted.

Diagnostics and query planning

duplicateIndexes schema [table]

Indexes that duplicate, or are covered by, another index on the same table.

identical groups indexes whose key columns, operator classes, collations, sort order, INCLUDE columns, and partial predicate all match. redundant reports an index whose key columns are a leading prefix of a wider index that also covers its INCLUDE columns.

Comparison is by column expression rather than attribute number, so two different expression indexes are not confused with each other, and (a DESC) is not treated as covered by (a, b). A unique index is never reported as redundant for being a prefix: dropping it would drop a constraint the wider index does not enforce. Each entry carries size, idx_scan, the name of any backing constraint, and the replica identity and validity flags, since those decide whether the index can be dropped at all.

table

Restrict the check to one table. Omit it to check every table in the schema.

evaluateIndex sql [create, hide]

Plan a statement as if the indexes were different, using hypopg. create takes CREATE INDEX statements to plan against without building them; hide takes the names of existing indexes to plan without, which is how to ask whether an index is safe to drop — the question duplicateIndexes raises and cannot settle, since neither redundancy nor an unused idx_scan proves the planner would not miss it.

Nothing is built, no lock is taken, no catalog row is written, and the statement is never executed: hypopg cannot serve EXPLAIN ANALYZE, so this is plan-only and safer than explainQuery with analyze. Reports the plan and total cost before and after, and for each index whether the planner actually used it — a proposed index the planner ignores is the common case, and a cost figure alone hides it. The cost is the planner's estimate, not a measurement.

A hypothetical index lives in backend-local memory for the whole session and is cleared by neither ROLLBACK, a new transaction, nor DISCARD ALL, PgBouncer's default server_reset_query. A transaction-mode pooler cannot be asked to help either: it runs no reset query at all unless server_reset_query_always is set, and even then DISCARD ALL does not clear it. This operation therefore resets explicitly on the way in and on the way out, so that behind a transaction pooler one caller's hypothetical index cannot reshape the next caller's plans.

hide requires hypopg 1.4.0 or later, gated on the extension version.

Takes settings and plan_as_role exactly as explainQuery does, and for a reason of its own: the whole output is a before/after cost comparison, so an environment that does not match production makes both halves answer a different question. Both plans are built under the same environment, and planning_environment reports it.

tableBloat schema table [exact]

Physical storage bloat for a table (pgstattuple or pgstattuple_approx): table size, live and dead tuple counts and percentages, free space and percentage. More accurate than the ANALYZE-time estimates in listTableStats and tableStats. Returns a clear error with setup instructions if the extension is not installed.

exact

Run a precise but I/O-heavy full table scan instead of the cheap visibility-map-based approximation. Default false.

indexBloat schema index

Physical statistics for one index, from whichever pgstattuple function fits its access method. The access method is resolved from the catalog, so the caller does not need to know it in advance.

btree

pgstatindex: version, tree_level, root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages, avg_leaf_density and leaf_fragmentation. Reads the whole index.

gin

pgstatginindex: version, pending_pages and pending_tuples, reported together with the fastupdate setting and the effective pending_list_limit_kb that bound them. Reads only the metapage, so it is cheap on any size of index.

hash

pgstathashindex: version, bucket_pages, overflow_pages, bitmap_pages, unused_pages, live_items, dead_items and free_percent. Reads the whole index.

The metrics are deliberately not normalized across access methods, unlike tableBloat's exact and approximate pair: leaf_fragmentation and pending_tuples are not two spellings of one quantity. access_method names which set was returned.

index_size and idx_scan accompany every result, because they are what the metrics are weighed against: a fragmented index that nothing has scanned since the last statistics reset is a candidate for dropping rather than for ‘REINDEX’.

gist, spgist and brin have no pgstattuple function at all and are reported as unsupported by name; their page-level detail is in the pageinspect extension instead. A partitioned index has no storage of its own and is reported as such.

checkKey schema table values

Check whether a row exists by primary key, single or composite. Value types are validated against the primary key column types before querying.

values

Ordered array of primary key values, matching the key columns in order.

explainQuery [queryid | sql]

Raw ‘EXPLAIN (FORMAT JSON)’ plan for a statement, recovered from pg_stat_statements by queryid or supplied directly as sql. Runs in a read-only transaction bounded by statement_timeout. Returns the plan verbatim plus generic, analyzed, and read_only flags and the pg_stat_statements row. There are no heuristics and no generated DDL; the plan is yours to interpret.

queryid

A pg_stat_statements queryid, as a decimal string. It is a 64-bit value and does not survive JSON number precision. Mutually exclusive with sql.

sql

A single SELECT, INSERT, UPDATE, DELETE, MERGE, WITH, TABLE, or VALUES statement. Utility statements are rejected. Mutually exclusive with queryid.

params

Concrete values for the statement's $1 through $n placeholders, in order. Statements recovered from pg_stat_statements are normalized, so without params they are planned with ‘GENERIC_PLAN’; supplying params has the statement PREPAREd and planned with real values.

analyze

Run ‘EXPLAIN (ANALYZE, BUFFERS)’, which really executes the statement. Honoured only after the plan is proven free of any ModifyTable node, so data-modifying statements, including data-modifying CTEs, are never executed. Requires timeout_ms. Default false.

timeout_ms

Statement timeout in milliseconds, clamped to 100 through 30000. Defaults to 5000 for plan-only calls.

settings

Planner settings to apply for this plan only, as an object such as ‘{ work_mem: 512MB }’. Applied with set_config(name, value, true), so they revert with the transaction and cannot leak to another session. Allowlisted to the settings that change a plan: work_mem, hash_mem_multiplier, the cost and parallelism knobs, every enable_*, the join-search limits, constraint_exclusion, plan_cache_mode and the jit knobs. An unknown name is refused and nothing is planned, rather than ignored. search_path is excluded on purpose: it changes which objects the statement resolves to rather than how they are joined.

With analyze the statement is executed under these settings only within a budget taken from the host capacity declared for the connection, as host_ram_mb and host_vcpus. The worst case the plan's memory limits permit must stay within a share of the declared RAM, 10% unless budgets.ini says otherwise: each sort-like node at work_mem, each hash node at work_mem times hash_mem_multiplier, times the processes running it. The plan may use at most one parallel worker per four declared vCPUs, again unless budgets.ini says otherwise; see Budgets. With no declared capacity, no settings change is executed at all. A refused plan is still returned with analyzed false, and execution_budget under planning_environment carries the arithmetic. The read-only guard and the timeout bound what the statement writes and how long it runs, but not what it allocates, and an out-of-memory kill of one backend restarts every connection on the instance.

plan_as_role

Plan under what this role carries in pg_db_role_setting, filtered to the same allowlist. A per-database entry (ALTER ROLE ... IN DATABASE) overrides the role-wide one, as the server itself applies them. Entries that are not planner settings, such as search_path or statement_timeout, are reported under skipped_from_role rather than dropped silently. Explicit settings win over what the role carries. On its own it is not held to the execution budget, because it applies what the role already runs with in production.

When either is given the result carries planning_environment, saying what was applied and, for plan_as_role, what was skipped. The plan's own Settings block reports what the server saw.

Topology

listTopology [pattern]

Every instance, replication group and group with its members, plus the connections carrying no label at all. Reads the configuration file and opens no database connection. An instance whose source is ‘inferred’ was derived from a shared host and port rather than declared. Takes no connection argument.

pattern

Case-insensitive substring, matched against each topology name and against the connection names belonging to it, so ‘where does billing_prod live’ and ‘show me the ha group’ are the same argument. An unlabelled connection is matched by its own name. A pattern matching nothing returns empty lists rather than an error.

verifyTopology

Connects to every configured connection and reports what each server actually is: its role, system identifier, database, address, port and version. Then checks the declared topology against them.

The whole operation turns on one fact: a system identifier names a replication lineage, not a postmaster. A physical replica began as a copy of its primary and carries the same value forever, so the identifier alone cannot separate the two axes and the endpoint has to be read alongside it. The same identifier on the same host and port is one instance; the same identifier on different hosts is a replication group.

Reports declarations the servers contradict, connections that share an identifier without being declared together, a replication group with no reachable primary, and split brain — where it names every primary it found and chooses none. Logical replication cannot be verified this way, because a logical replica has its own identifier, and is reported as ‘cannot verify’ rather than as a mismatch. Takes no connection argument.

Connections

listConnections [pattern]

The configured database connections by name, with the libpq service name or host, port, dbname, and user for each, and which is the default. Passwords are never returned and a service file is never expanded. Each entry also carries its instance, replication_group and group labels where they are configured. listConnections, listTopology and verifyTopology are the only operations that do not take a connection argument.

pattern

Case-insensitive substring, matched against the connection name and against its instance, replication_group and group labels. This answer carries every configured connection otherwise and has no cursor, so on a registry holding hundreds this is how to ask for one. A pattern matching nothing returns an empty list rather than an error.

Caching and pagination

Results that revision 2026-07-28 marks cacheable — server/discover, tools/list, prompts/list, resources/list, resources/templates/list and resources/read — carry ttlMs and cacheScope. Both are emitted only alongside resultType, so a legacy result is unchanged.

Anything compiled in or read from the configuration file at startup is hinted at one hour: none of it can change while the process lives, and no listChanged capability is declared, so there is no mechanism by which it could. Anything read from a live catalog is hinted at one minute. This matters most for tools/list, which is roughly 93 kB once 62 operations carry descriptions, annotations, titles and output schemas, and which a client would otherwise re-fetch whenever it needed the list.

tools/list is scoped private rather than public. Its content varies by negotiated revision, while the cache key is the method and its parameters and the revision travels in _meta; and its connection property names the configured default connection. Neither belongs in a cache a shared gateway may serve to another caller. A private cache still serves the calling client, which is where the saving lands.

The list operations accept an opaque cursor and return nextCursor when more results remain; an unusable cursor is refused with -32602. The page size is larger than any list this server currently produces, so a client that ignores cursors still receives every operation. resources/list is the one that grows, being connections multiplied by schemas.

Connections

Startup opens no connection. The registry is parsed and validated, so a malformed conninfo still fails immediately, but reachability is left to the first call that needs it — otherwise one unreachable database prevents the server from starting at all, taking every other database with it.

A connection is held between calls and closed after 60 seconds idle, one per configured connection, keyed on the connection name. Every registry entry carries its own user, so a reused connection never crosses identities. Idle connections belonging to pg_licht_mcp therefore appear in pg_stat_activity for up to a minute after use.

A cached connection can be closed by the server underneath — idle_session_timeout, a restart, a firewall that forgot the flow. There is no way to know but to use it, so a failure discards it and reconnects once; the retry is safe because only BEGIN and the setup batch have run at that point.

A fan-out sweep visits up to 16 members at once. This is safe where general concurrency would not be: members of a replication group are distinct servers by definition, and a group sweep already collapses members that would answer identically, so the width is one connection per machine rather than several onto one. The payload stays in configuration order however the members finish.

Running behind a connection pooler remains supported and tested, and is still worth it across a WAN: a pooler amortises the TLS handshake across restarts of this process. Note that PgBouncer reads neither .pgpass nor a libpq service definition, so its [databases] section duplicates whatever the connections file already says; and in transaction mode it does not run server_reset_query at all unless server_reset_query_always is set.

Transactions

Every session runs inside a transaction opened with SET TRANSACTION READ ONLY and ends in ROLLBACK. Nothing is ever committed: there is nothing a commit could preserve, and rolling back leaves the server provably as it was found.

Rollback does not undo backend-local state set by an extension. A hypopg hypothetical index survives ROLLBACK, the next transaction, and DISCARD ALL, which is why evaluateIndex resets it explicitly rather than relying on the transaction ending.

Standalone

Run against a single database given by the environment:

DATABASE_URL="postgresql://user:pass@host/dbname" pg_licht_mcp

The libpq key-value form works too, as does a positional argument or a configuration file:

DATABASE_URL="host=localhost port=5432 dbname=mydb" pg_licht_mcp
pg_licht_mcp "postgresql://user:pass@host/dbname"
pg_licht_mcp --config ~/.config/pg_licht/connections.ini

Claude Code

Register the server with claude mcp add:

claude mcp add --transport stdio pg-licht \
  -e DATABASE_URL="postgresql://user:pass@host/dbname" \
  -- /usr/local/bin/pg_licht_mcp

The --scope flag controls where the configuration is written:

Local scope is personal and limited to the current project, project scope is shared with the team through version control, and user scope applies across all of your projects.

Project configuration

A project-scoped server lives in .mcp.json at the repository root; commit it to share it with the team:

{
  "mcpServers": {
    "pg-licht": {
      "command": "/usr/local/bin/pg_licht_mcp",
      "args": [],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@host/dbname"
      }
    }
  }
}

Local- and user-scoped servers live in ~/.claude.json instead. Edit that file with claude mcp add or claude mcp add-json rather than by hand, since it also holds per-project state.

Claude Desktop

Add the same JSON block under mcpServers in claude_desktop_config.json.

Grok Build

Grok Build reads .mcp.json and ~/.claude.json for compatibility, so a server already configured for Claude Code is picked up automatically. To configure it directly:

grok mcp add pg-licht -- /usr/local/bin/pg_licht_mcp

Environment variables for stdio servers are set in the configuration file rather than on the command line. --scope project writes to .grok/config.toml in the project; omit it for the user-level ~/.grok/config.toml:

[mcp_servers.pg-licht]
command = "/usr/local/bin/pg_licht_mcp"
env = { DATABASE_URL = "postgresql://user:pass@host/dbname" }

Verify with grok mcp list or grok mcp doctor pg-licht.

What reaches the caller

pg_licht_mcp never writes, and it never returns rows from your tables. Every call is a read-only transaction, so nothing it runs can change data or schema. Two operations read a user table, and neither returns its rows: checkKey answers only whether a row with the given primary key exists, though that answer is itself a fact about the table; and explainQuery with analyze executes a statement the caller supplied, only once its plan is proven not to write, and returns the plan with row counts and timings rather than the rows.

Values that came from your data can still reach the caller, in these places:

currentActivity, currentLocks

The text of statements other sessions are running now, exactly as sent, including any literal a WHERE clause or a statement carries — a password in ALTER ROLE ... PASSWORD among them. Statements of other roles are hidden unless the connecting role holds pg_read_all_stats.

statementStats, explainQuery

Statement text recorded by pg_stat_statements, in which the constants of a planned statement are replaced by $n placeholders: up to 500 characters in a list, whole when one queryid is named. Statements of other roles read ‘<insufficient privilege>’ without pg_read_all_stats.

tableStats, columnHistogram

Column statistics from pg_stats: the most common values with their frequencies, and histogram bounds, which are sample values from the column. PostgreSQL returns them only for columns the connecting role may SELECT.

explainQuery, evaluateIndex

Plans, which repeat the literals of the statement the caller wrote.

functionDetails, tableDetails

Definitions as written: function source, view definitions, column defaults, check constraints and policy expressions, carrying whatever literals were written into them.

serverSettings

Setting values. For a role allowed to read superuser-only settings that includes a standby's primary_conninfo, which may contain a password.

No operation returns a password pg_licht_mcp itself holds: listConnections never returns one and never expands a service file, listForeignServers returns server options but never user mappings, and listSubscriptions omits each subscription's connection string.

The connecting role decides the rest. A role without SELECT on a column gets none of its statistics, and a role without pg_read_all_stats sees only its own statements. checkPrivileges reports what the current role can reach, so the narrowest role that answers the question is the one to connect as.