PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/debug.ts:78-110 (handler)

  Handler implementation for the pg_debug_database tool, including input validation, execution of core debug logic, and formatting of output.
  export const debugDatabaseTool: PostgresTool = { name: 'pg_debug_database', description: 'Debug common PostgreSQL issues', inputSchema: DebugDatabaseInputSchema, 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/tools/debug.ts:41-45 (schema)

  Zod input schema defining parameters for the pg_debug_database tool.
  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/index.ts:227-228 (registration)

  Registration of debugDatabaseTool (pg_debug_database) in the allTools array, which is passed to the MCP PostgreSQLServer constructor.
  analyzeDatabaseTool, debugDatabaseTool,
• src/tools/debug.ts:49-76 (helper)

  Core helper function that handles database connection and dispatches to specific debug functions based on the issue type.
  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)

  Helper function for debugging connection issues, including max connections usage and idle connections.
  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; }