PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/debug.ts:41-45 (schema)

  Zod input schema defining parameters for the pg_debug_database tool: optional connectionString, required issue type, optional logLevel.
  const DebugDatabaseInputSchema = z.object({ connectionString: z.string().optional(), issue: z.enum(['connection', 'performance', 'locks', 'replication']), logLevel: z.enum(['info', 'debug', 'trace']).optional().default('info'), });
• src/tools/debug.ts:82-109 (handler)

  The main execute handler for pg_debug_database: validates input with schema, calls internal executeDebugDatabase, formats DebugResult to ToolOutput.
  async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> { const validationResult = DebugDatabaseInputSchema.safeParse(params); if (!validationResult.success) { const errorDetails = validationResult.error.errors.map(e => `${e.path.join('.')}: ${e.message}`).join(', '); return { content: [{ type: 'text', text: `Invalid input: ${errorDetails}` }], isError: true, }; } try { const result = await executeDebugDatabase(validationResult.data, getConnectionString); // Convert DebugResult to ToolOutput format return { content: [ { type: 'text', text: `Debug Result for Issue: ${result.issue}` }, { type: 'text', text: `Status: ${result.status}` }, { type: 'text', text: `Details:\n${result.details.join('\n')}` }, { type: 'text', text: `Recommendations:\n${result.recommendations.join('\n')}` }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); return { content: [{ type: 'text', text: `Error debugging database: ${errorMessage}` }], isError: true, }; } }
• src/index.ts:225-257 (registration)

  pg_debug_database is registered by including debugDatabaseTool in the allTools array (line 228), passed to PostgreSQLServer constructor which registers tools in MCP capabilities and handles execution.
  const allTools: PostgresTool[] = [ // Core Analysis & Debugging analyzeDatabaseTool, debugDatabaseTool, // Schema & Structure Management (Meta-Tools) manageSchemaTools, manageFunctionsTool, manageTriggersTools, manageIndexesTool, manageConstraintsTool, manageRLSTool, // User & Security Management manageUsersTool, // Query & Performance Management manageQueryTool, // Data Operations (Enhancement Tools) executeQueryTool, executeMutationTool, executeSqlTool, // Documentation & Metadata manageCommentsTool, // Data Migration & Monitoring exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool, monitorDatabaseTool ];
• src/tools/debug.ts:49-76 (helper)

  Helper function that connects to DB, dispatches to issue-specific debug functions (debugConnection, debugPerformance, etc.), and ensures disconnect.
  async function executeDebugDatabase( input: DebugDatabaseInput, getConnectionString: GetConnectionStringFn ): Promise<DebugResult> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); try { await db.connect(resolvedConnectionString); switch (input.issue) { case 'connection': return await debugConnection(db); case 'performance': return await debugPerformance(db); case 'locks': return await debugLocks(db); case 'replication': return await debugReplication(db); default: // This case should be unreachable due to Zod validation throw new McpError(ErrorCode.InvalidParams, `Unsupported issue type: ${input.issue}`); } } finally { // Ensure disconnect is called even if connect fails or other errors occur await db.disconnect(); } }
• src/tools/debug.ts:112-165 (helper)

  Example helper for 'connection' issue: checks max connections usage, idle connections, provides recommendations.
  async function debugConnection(db: DatabaseConnection): Promise<DebugResult> { const result: DebugResult = { issue: 'connection', status: 'ok', details: [], recommendations: [] }; try { // Check max connections const maxConns = await db.query<{ setting: string }>( "SELECT setting FROM pg_settings WHERE name = 'max_connections'" ); const currentConns = await db.query<{ count: string }>( 'SELECT count(*) FROM pg_stat_activity' ); const max = Number.parseInt(maxConns[0].setting); const current = Number.parseInt(currentConns[0].count); const percentage = (current / max) * 100; result.details.push( `Current connections: ${current}/${max} (${percentage.toFixed(1)}%)` ); if (percentage > 80) { result.status = 'warning'; result.recommendations.push( 'High connection usage. Consider implementing connection pooling', 'Review application connection handling', 'Monitor for connection leaks' ); } // Check for idle connections const idleConns = await db.query<{ count: string }>( "SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'" ); const idleCount = Number.parseInt(idleConns[0].count); if (idleCount > 5) { result.details.push(`High number of idle connections: ${idleCount}`); result.recommendations.push( 'Consider implementing connection timeouts', 'Review connection pool settings' ); } } catch (error: unknown) { result.status = 'error'; result.details.push(`Connection error: ${error instanceof Error ? error.message : String(error)}`); } return result; }