@tejasanik/postgres-mcp-server

List all configured PostgreSQL servers. Call this FIRST to discover available server names before using list_databases or switch_server_db. Returns server names and connection status.

List databases in a specific PostgreSQL server. REQUIRES serverName parameter - use list_servers first to get valid server names. Do NOT guess server names.

Connect to a PostgreSQL server and database. MUST be called before executing queries. Use list_servers to find server names, list_databases to find database names.

Get current connection status. Returns server name, database, schema, and access mode (readonly/full). Call this to verify your connection before running queries.

List all schemas in the current database. Requires active connection (use switch_server_db first). Optionally use server/database/schema params for one-time execution on a different server without changing the main connection.

List tables, views, materialized views, sequences, or extensions in a schema. Requires active connection. Optionally use server/database/targetSchema params for one-time execution on a different server.

Get detailed info about a table/view/sequence: columns, data types, constraints, indexes, size, row count. Use this to understand table structure before writing queries. Optionally use server/database/targetSchema params for one-time execution on a different server.

Execute SQL queries. Supports SELECT, INSERT, UPDATE, DELETE (if not in readonly mode). Use $1, $2 placeholders with params array to prevent SQL injection. Use allowMultipleStatements to run multiple statements separated by semicolons. Use transactionId to run within a transaction. Optionally use server/database/schema params for one-time execution on a different server without changing the main connection.

Execute a .sql file from the filesystem. Useful for running migration scripts, schema changes, or data imports. Supports transaction mode for atomic execution. Max file size: 50MB. Use validateOnly=true to preview without executing. Use stripPatterns to remove delimiters like '/' (Liquibase) or 'GO' (SQL Server). Optionally use server/database/schema params for one-time execution on a different server.

Preview a SQL file without executing it. Shows statement count, types breakdown, and warnings for potentially dangerous operations (DROP, TRUNCATE, DELETE/UPDATE without WHERE). Similar to mutation_preview but for SQL files. Use this before execute_sql_file to understand what a migration will do.

Preview the effect of INSERT/UPDATE/DELETE without executing. Shows estimated rows affected and sample of rows that would be modified. Use this before running destructive queries to verify the impact. Optionally use server/database/schema params for one-time execution on a different server.

Execute INSERT/UPDATE/DELETE in dry-run mode - actually runs the SQL within a transaction, captures REAL results (exact row counts, actual errors, before/after data), then ROLLBACK so nothing persists. More accurate than mutation_preview. Use this to verify mutations will work correctly before committing. Returns detailed PostgreSQL error info (code, constraint, hint) on failure. Optionally use server/database/schema params for one-time execution on a different server.

Execute a SQL file in dry-run mode - actually runs ALL statements within a transaction, captures REAL results for each (row counts, errors with line numbers, constraint violations), then ROLLBACK so nothing persists. Perfect for testing migrations before deploying. Returns detailed error info including PostgreSQL error codes, constraint names, and hints to help quickly fix issues. Warns about non-rollbackable operations (sequences, VACUUM, etc.). Optionally use server/database/schema params for one-time execution on a different server.

Execute multiple SQL queries in parallel. Returns all results keyed by query name. Efficient for fetching multiple independent pieces of data in one call. Optionally use server/database/schema params for one-time execution on a different server.

Start a new database transaction. Returns a transactionId to use with execute_sql, commit_transaction, or rollback_transaction. Transactions allow atomic execution of multiple statements.

Commit an active transaction, making all changes permanent.

Rollback an active transaction, undoing all changes made within it.

Get information about an active transaction, including its name, server, database, and when it started.

List all active transactions. Returns transaction details including name, server, database, and start time.

Show PostgreSQL's execution plan for a query. Use this to understand query performance and identify missing indexes. analyze=true runs the query to get actual timings (SELECT only). Optionally use server/database/schema params for one-time execution on a different server.

Find slowest queries from pg_stat_statements. Requires pg_stat_statements extension enabled. Use this to identify performance bottlenecks.

Analyze database workload and recommend indexes. Uses pg_stat_statements to find slow queries and suggests indexes to improve them.

Recommend indexes for specific SQL queries. Provide up to 10 SELECT queries and get index recommendations.

Export schema (DDL) and/or data from the connected database to a .sql file. Supports four content kinds via the 'what' parameter: 'objects' (DDL of a list of objects), 'data' (INSERT statements for tables), 'schema_dump' (full schema, optionally with data), 'query_result' (SELECT result emitted as INSERTs into a target table). Mode is 'append' (default, appends to existing file with separator banner) or 'overwrite'. The header banner records timestamp and source server alias (host/port hidden). Use this before transfer_objects or for migration script generation.

Transfer schema (DDL) and/or data from one configured server/database to another (same server, different DB, or fully remote). Builds on the introspection module for DDL extraction with topological ordering. Modes: include='ddl'|'data'|'both'. Behavior on existing target objects: if_exists='skip'|'replace'|'error'. dry_run=true emits the would-be SQL to output_file or returns inline (no target writes). Both endpoints must be configured servers (PG_NAME_*); ad-hoc connection strings are not accepted (security). Refuses if target's effective access mode is readonly. FK constraints between tables are emitted as ALTER TABLE statements appended after tables to handle inter-table dependency cycles.

Single rich call describing a table: columns (type/nullable/default + null %/distinct ratio from pg_stats), primary key, foreign keys going OUT (this table → others) AND coming IN (others → this table), all indexes (with definitions), table size, row-count estimate, and sample rows. Replaces ~5 separate calls (get_object_details + LIMIT 5 + COUNT(*) + pg_stats).

Find what depends on a database object before dropping it. Recursively walks pg_depend, classifies dependents (views, foreign keys, functions, materialized views, indexes, rules) and reports each with its depth from the target. Use this BEFORE running DROP CASCADE to understand the blast radius. Returns the dependent objects flattened with `depth` (1 = directly depends, 2 = depends on a depth-1 dependent, etc).

Compute the DDL delta between two { server, database, schema } endpoints. Returns objects to CREATE (in source but not target), DROP (in target but not source), and MODIFY (in both, but DDL differs), plus a single `migrationSql` script that, when applied to the TARGET, converges its schema with the SOURCE. CREATE OR REPLACE is used for views/functions/procedures; DROP+CREATE for everything else. Source is the source of truth.

Static analysis of a SQL statement to determine the PostgreSQL lock level it will require, whether it forces a full table rewrite, and an estimated duration based on target table size. Returns warnings for ACCESS EXCLUSIVE locks on busy production tables and concrete recommendations (e.g., use CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE CONSTRAINT, etc). Use BEFORE running DDL on production. Knows lock semantics for ALTER TABLE variants, CREATE/DROP INDEX (concurrent vs not), VACUUM, CLUSTER, REFRESH MATERIALIZED VIEW, and more.

Probe the database for migration tool tracker tables (Liquibase, Flyway, Alembic, Prisma, Knex, Sequelize, Django, Rails, Goose, TypeORM). Returns which tools are detected, the schema and table holding their state, the count of applied migrations, and the latest version. AI agents use this to immediately understand whether the DB is managed by a migration tool before suggesting changes.

Convert a high-level intent ('add NOT NULL column with default', 'add NOT NULL', 'add foreign key', 'add CHECK', 'create index', 'drop index') into a multi-step zero-downtime DDL recipe. Each step has its own SQL, expected lock level, and notes. Pipe the resulting `scriptSql` through `dry_run_sql_file` for verification, then through `executeSqlFile(useTransaction=false)` for the production rollout (CONCURRENTLY operations cannot run inside a transaction).

Single-pass profile per column: null %, distinct count, top-K values with frequencies, min/max, and type-aware stats (avg/stddev for numeric, length distribution for text, range for temporal). Uses TABLESAMPLE BERNOULLI for tables larger than `sample_threshold` (default 1M rows) to keep latency bounded. Replaces a dozen separate exploratory queries an AI agent would otherwise run to understand a column's shape.

Generate schema-aware fake seed data for a table. Respects NOT NULL, UNIQUE/PK (with retry-with-collision-suffix), enum types (cycles through labels), defaults (uses DEFAULT for unknown types), text length limits, and FK columns (skipped or filled — caller's choice). Generates type-appropriate values for numeric, text, boolean, uuid, date/timestamp, bytea, JSON, inet, cidr, macaddr. Per-column overrides via `column_values`. Apply directly (default) or return SQL only via `apply: false`.

Show currently-blocking sessions in a friendly tree (blocker → blocked) using pg_stat_activity ⨝ pg_blocking_pids(). Replaces the gnarly join an AI agent struggles to write. Returns each session's pid, user, database, application name, state, current query, time in state, and wait_event. Use to diagnose slowdowns and pick a candidate for kill_query.

Cancel or terminate a backend session by PID. mode='cancel' (soft, pg_cancel_backend) interrupts the current statement; mode='terminate' (hard, pg_terminate_backend) kills the entire backend. Both require confirm:true. Refused if the target server's effective access mode is readonly. Returns a snapshot of the target session before signaling.

Run comprehensive database health checks: cache hit rates, connection usage, index health (invalid/unused/duplicate), vacuum status, sequence limits, unvalidated constraints. Returns issues with severity levels.