# agr_postgres_mcp

MCP server for interacting with PostgreSQL databases via `psql`. Designed for use with Claude Code as a Model Context Protocol (MCP) server.

## Configuration

The server is configured entirely through environment variables:

| Variable | Required | Default | Description |
|---|---|---|---|
| `PG_HOST` | Yes | — | PostgreSQL hostname |
| `PG_USER` | No | `postgres` | Database username |
| `PG_PASSWORD` | Yes | — | Database password |
| `PG_DATABASE` | Yes | — | Database name |
| `PG_PORT` | No | `5432` | Database port |
| `PG_SERVER_NAME` | No | `postgres` | MCP server name (used in logs and tool prefixes) |
| `PSQL_PATH` | No | auto-detect | Full path to the `psql` binary |

If `PSQL_PATH` is not set, the server searches common locations:
- `/opt/homebrew/opt/postgresql@15/bin/psql`
- `/opt/homebrew/opt/postgresql@13/bin/psql`
- `/Applications/Postgres.app/Contents/Versions/15/bin/psql`
- `/Applications/Postgres.app/Contents/Versions/14/bin/psql`
- `/usr/local/bin/psql`
- `/usr/bin/psql`

## Tools

| Tool | Description |
|---|---|
| `query` | Execute arbitrary SQL with configurable output format (table/csv/expanded) and timeout |
| `list_tables` | List all tables in a schema |
| `describe_table` | Describe a table's columns, types, constraints, and indexes (`\d+` equivalent) |
| `list_indexes` | List indexes, optionally filtered by table |
| `table_sizes` | Show table sizes (data, index, total) sorted by size |
| `table_stats` | Show live/dead tuples, last vacuum/analyze times |
| `active_connections` | Show active database connections from `pg_stat_activity` |
| `locks` | Show current locks with blocking information |
| `explain` | Show query execution plan (`EXPLAIN` / `EXPLAIN ANALYZE`) |
| `database_size` | Show total database size and object counts |
| `row_counts` | Estimated row counts for all tables |
| `foreign_keys` | List foreign key constraints, optionally filtered by table |
| `index_usage` | Index scan statistics for identifying unused indexes |
| `schema_search` | Search for tables or columns by LIKE pattern |

## Setup

```bash
npm install
npm run build
```

## MCP Configuration

Add an entry to `.mcp.json` for each database instance:

```json
{
  "mcpServers": {
    "pg-alpha": {
      "type": "stdio",
      "command": "node",
      "args": ["/path/to/agr_postgres_mcp/dist/index.js"],
      "env": {
        "PG_SERVER_NAME": "alpha",
        "PG_HOST": "your-db-host.example.com",
        "PG_USER": "postgres",
        "PG_PASSWORD": "your-password",
        "PG_DATABASE": "your-database",
        "PSQL_PATH": "/path/to/psql"
      }
    }
  }
}
```

Multiple instances can be configured by adding additional entries with different `PG_SERVER_NAME` and `PG_HOST` values.