pg_analyze_database
Analyze PostgreSQL database configuration, performance, and security to optimize database settings and identify potential issues for improved efficiency.
Instructions
Analyze PostgreSQL database configuration and performance
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| analysisType | No | Type of analysis to perform | |
| connectionString | No | PostgreSQL connection string (optional if POSTGRES_CONNECTION_STRING environment variable or --connection-string CLI option is set) |
Implementation Reference
- src/tools/analyze.ts:30-56 (handler)The main tool handler 'analyzeDatabaseTool' with the execute function that validates input, resolves connection string, calls the analysis function, and returns JSON output.export const analyzeDatabaseTool: PostgresTool = { name: toolDefinition.name, description: toolDefinition.description, inputSchema: toolDefinition.inputSchema, execute: async (args: { connectionString?: string; analysisType?: 'configuration' | 'performance' | 'security'; }, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> => { const { connectionString: connStringArg, analysisType } = args; if (!analysisType || !['configuration', 'performance', 'security'].includes(analysisType)) { return { content: [{ type: 'text', text: 'Error: analysisType is required and must be one of [\'configuration\', \'performance\', \'security\'].' }], isError: true, }; } const resolvedConnString = getConnectionString(connStringArg); const result = await originalAnalyzeDatabase(resolvedConnString, analysisType); return { content: [ { type: 'text', text: JSON.stringify(result, null, 2) } ] }; }, };
- src/tools/analyze.ts:19-28 (schema)Tool definition including the Zod input schema for parameters: connectionString and analysisType.const toolDefinition = { name: 'pg_analyze_database', description: 'Analyze PostgreSQL database configuration and performance', inputSchema: z.object({ connectionString: z.string().optional() .describe('PostgreSQL connection string (optional if POSTGRES_CONNECTION_STRING environment variable or --connection-string CLI option is set)'), analysisType: z.enum(['configuration', 'performance', 'security']).optional() .describe('Type of analysis to perform') }) };
- src/index.ts:225-257 (registration)Registration of all available tools in the allTools array, passed to PostgreSQLServer constructor for MCP capabilities.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/analyze.ts:58-80 (helper)Core helper function performing the actual database analysis: connects, gathers version, settings, metrics, generates recommendations.export async function analyzeDatabase( connectionString: string, analysisType: 'configuration' | 'performance' | 'security' = 'configuration' ): Promise<AnalysisResult> { const db = DatabaseConnection.getInstance(); await db.connect(connectionString); try { const version = await getVersion(); const settings = await getSettings(); const metrics = await getMetrics(); const recommendations = await generateRecommendations(analysisType, settings, metrics); return { version, settings, metrics, recommendations, }; } finally { await db.disconnect(); } }
- src/tools/analyze.ts:179-216 (helper)Helper function generating specific recommendations based on analysis type, settings, and metrics.async function generateRecommendations( type: 'configuration' | 'performance' | 'security', settings: Record<string, string>, metrics: AnalysisResult['metrics'] ): Promise<string[]> { const recommendations: string[] = []; if (type === 'configuration' || type === 'performance') { if (metrics.cacheHitRatio < 0.99) { recommendations.push('Consider increasing shared_buffers to improve cache hit ratio'); } if (metrics.connections > Number.parseInt(settings.max_connections) * 0.8) { recommendations.push('High connection usage detected. Consider increasing max_connections or implementing connection pooling'); } } if (type === 'security') { const db = DatabaseConnection.getInstance(); // Check for superusers const superusers = await db.query<{ count: string }>( "SELECT count(*) FROM pg_user WHERE usesuper = true" ); if (Number.parseInt(superusers[0].count) > 1) { recommendations.push('Multiple superuser accounts detected. Review and reduce if possible'); } // Check SSL const ssl = await db.query<{ ssl: string }>("SHOW ssl"); if (ssl[0].ssl !== 'on') { recommendations.push('SSL is not enabled. Consider enabling SSL for secure connections'); } } return recommendations; }