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;
  }