/**
* PostgreSQL Monitoring Tools
*
* Database health, sizes, connections, and replication status.
* 11 tools total.
*/
import type { PostgresAdapter } from '../PostgresAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../types/index.js';
import { z } from 'zod';
import { readOnly } from '../../../utils/annotations.js';
import { getToolIcons } from '../../../utils/icons.js';
import { DatabaseSizeSchema, TableSizesSchema, ShowSettingsSchema } from '../schemas/index.js';
/**
* Get all monitoring tools
*/
export function getMonitoringTools(adapter: PostgresAdapter): ToolDefinition[] {
return [
createDatabaseSizeTool(adapter),
createTableSizesTool(adapter),
createConnectionStatsTool(adapter),
createReplicationStatusTool(adapter),
createServerVersionTool(adapter),
createShowSettingsTool(adapter),
createUptimeTool(adapter),
createRecoveryStatusTool(adapter),
createCapacityPlanningTool(adapter),
createResourceUsageAnalyzeTool(adapter),
createAlertThresholdSetTool(adapter)
];
}
function createDatabaseSizeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_database_size',
description: 'Get the size of a database.',
group: 'monitoring',
inputSchema: DatabaseSizeSchema,
annotations: readOnly('Database Size'),
icons: getToolIcons('monitoring', readOnly('Database Size')),
handler: async (params: unknown, _context: RequestContext) => {
const { database } = DatabaseSizeSchema.parse(params);
const sql = database
? `SELECT pg_database_size($1) as bytes, pg_size_pretty(pg_database_size($1)) as size`
: `SELECT pg_database_size(current_database()) as bytes, pg_size_pretty(pg_database_size(current_database())) as size`;
const result = await adapter.executeQuery(sql, database ? [database] : []);
return result.rows?.[0];
}
};
}
function createTableSizesTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_table_sizes',
description: 'Get sizes of all tables with indexes and total.',
group: 'monitoring',
inputSchema: TableSizesSchema,
annotations: readOnly('Table Sizes'),
icons: getToolIcons('monitoring', readOnly('Table Sizes')),
handler: async (params: unknown, _context: RequestContext) => {
const { schema, limit } = TableSizesSchema.parse(params);
const schemaClause = schema ? `AND n.nspname = '${schema}'` : '';
const limitClause = limit !== undefined && limit > 0 ? ` LIMIT ${String(limit)}` : ' LIMIT 50';
const sql = `SELECT n.nspname as schema, c.relname as table_name,
pg_size_pretty(pg_table_size(c.oid)) as table_size,
pg_size_pretty(pg_indexes_size(c.oid)) as indexes_size,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size,
pg_total_relation_size(c.oid) as total_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
${schemaClause}
ORDER BY pg_total_relation_size(c.oid) DESC${limitClause}`;
const result = await adapter.executeQuery(sql);
return { tables: result.rows };
}
};
}
function createConnectionStatsTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_connection_stats',
description: 'Get connection statistics by database and state.',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Connection Stats'),
icons: getToolIcons('monitoring', readOnly('Connection Stats')),
handler: async (_params: unknown, _context: RequestContext) => {
const sql = `SELECT datname, state, count(*) as connections
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY datname, state
ORDER BY datname, state`;
const result = await adapter.executeQuery(sql);
const maxResult = await adapter.executeQuery(`SHOW max_connections`);
const maxConnections = maxResult.rows?.[0]?.['max_connections'];
const totalResult = await adapter.executeQuery(
`SELECT count(*) as total FROM pg_stat_activity`
);
return {
byDatabaseAndState: result.rows,
totalConnections: totalResult.rows?.[0]?.['total'],
maxConnections
};
}
};
}
function createReplicationStatusTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_replication_status',
description: 'Check replication status and lag.',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Replication Status'),
icons: getToolIcons('monitoring', readOnly('Replication Status')),
handler: async (_params: unknown, _context: RequestContext) => {
const recoveryResult = await adapter.executeQuery(`SELECT pg_is_in_recovery() as is_replica`);
const isReplica = recoveryResult.rows?.[0]?.['is_replica'];
if (isReplica === true) {
const sql = `SELECT
now() - pg_last_xact_replay_timestamp() as replay_lag,
pg_last_wal_receive_lsn() as receive_lsn,
pg_last_wal_replay_lsn() as replay_lsn`;
const result = await adapter.executeQuery(sql);
return { role: 'replica', ...result.rows?.[0] };
} else {
const sql = `SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
now() - backend_start as connection_duration
FROM pg_stat_replication`;
const result = await adapter.executeQuery(sql);
return { role: 'primary', replicas: result.rows };
}
}
};
}
function createServerVersionTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_server_version',
description: 'Get PostgreSQL server version information.',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Server Version'),
icons: getToolIcons('monitoring', readOnly('Server Version')),
handler: async (_params: unknown, _context: RequestContext) => {
const sql = `SELECT version() as full_version,
current_setting('server_version') as version,
current_setting('server_version_num') as version_num`;
const result = await adapter.executeQuery(sql);
return result.rows?.[0];
}
};
}
function createShowSettingsTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_show_settings',
description: 'Show current PostgreSQL configuration settings. Filter by name pattern or exact setting name. Accepts: pattern, setting, or name parameter.',
group: 'monitoring',
inputSchema: ShowSettingsSchema,
annotations: readOnly('Show Settings'),
icons: getToolIcons('monitoring', readOnly('Show Settings')),
handler: async (params: unknown, _context: RequestContext) => {
const { pattern } = ShowSettingsSchema.parse(params);
// Auto-detect if user passed exact name vs LIKE pattern
// If no wildcards, try exact match first, fall back to LIKE with wildcards
let whereClause = '';
let queryParams: string[] = [];
if (pattern !== undefined) {
if (pattern.includes('%') || pattern.includes('_')) {
// User specified LIKE pattern explicitly
whereClause = 'WHERE name LIKE $1';
queryParams = [pattern];
} else {
// Exact name - try exact match first, or pattern match with auto-wildcards
whereClause = 'WHERE name = $1 OR name LIKE $2';
queryParams = [pattern, `%${pattern}%`];
}
}
const sql = `SELECT name, setting, unit, category, short_desc
FROM pg_settings
${whereClause}
ORDER BY category, name`;
const result = await adapter.executeQuery(sql, queryParams);
return {
settings: result.rows,
count: result.rows?.length ?? 0
};
}
};
}
function createUptimeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_uptime',
description: 'Get server uptime and startup time.',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Server Uptime'),
icons: getToolIcons('monitoring', readOnly('Server Uptime')),
handler: async (_params: unknown, _context: RequestContext) => {
const sql = `SELECT pg_postmaster_start_time() as start_time,
now() - pg_postmaster_start_time() as uptime`;
const result = await adapter.executeQuery(sql);
return result.rows?.[0];
}
};
}
function createRecoveryStatusTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_recovery_status',
description: 'Check if server is in recovery mode (replica).',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Recovery Status'),
icons: getToolIcons('monitoring', readOnly('Recovery Status')),
handler: async (_params: unknown, _context: RequestContext) => {
const sql = `SELECT pg_is_in_recovery() as in_recovery,
CASE WHEN pg_is_in_recovery()
THEN pg_last_xact_replay_timestamp()
ELSE NULL
END as last_replay_timestamp`;
const result = await adapter.executeQuery(sql);
return result.rows?.[0];
}
};
}
/**
* Capacity planning analysis
*/
function createCapacityPlanningTool(adapter: PostgresAdapter): ToolDefinition {
// Schema with alias support
const CapacityPlanningSchema = z.object({
projectionDays: z.number().optional().describe('Days to project growth (default: 90)'),
days: z.number().optional().describe('Alias for projectionDays')
}).transform((data) => ({
projectionDays: data.projectionDays ?? data.days ?? 90
}));
return {
name: 'pg_capacity_planning',
description: 'Analyze database growth trends and provide capacity planning forecasts. Note: Growth estimates are based on pg_stat_user_tables counters since last stats reset; accuracy depends on how long stats have been accumulating.',
group: 'monitoring',
inputSchema: z.object({
projectionDays: z.number().optional().describe('Days to project growth (default: 90)'),
days: z.number().optional().describe('Alias for projectionDays')
}),
annotations: readOnly('Capacity Planning'),
icons: getToolIcons('monitoring', readOnly('Capacity Planning')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = CapacityPlanningSchema.parse(params ?? {});
const projectionDays = parsed.projectionDays;
const [dbSize, tableStats, connStats, statsAge] = await Promise.all([
adapter.executeQuery(`
SELECT
pg_database_size(current_database()) as current_size_bytes,
pg_size_pretty(pg_database_size(current_database())) as current_size
`),
adapter.executeQuery(`
SELECT
count(*) as table_count,
sum(n_live_tup) as total_rows,
sum(n_tup_ins) as total_inserts,
sum(n_tup_del) as total_deletes
FROM pg_stat_user_tables
`),
adapter.executeQuery(`
SELECT
current_setting('max_connections')::int as max_connections,
count(*) as current_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
`),
// Get time since stats reset for accurate daily rate calculation
// Use pg_stat_database.stats_reset (works in all PG versions including 17+)
// Fall back to server start time if stats_reset is NULL
adapter.executeQuery(`
SELECT
COALESCE(
(SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()),
pg_postmaster_start_time()
) as stats_since,
EXTRACT(EPOCH FROM (now() - COALESCE(
(SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()),
pg_postmaster_start_time()
))) / 86400.0 as days_of_data
`)
]);
const currentBytes = Number(dbSize.rows?.[0]?.['current_size_bytes'] ?? 0);
const tableData = tableStats.rows?.[0];
const connData = connStats.rows?.[0];
const ageData = statsAge.rows?.[0];
const totalInserts = Number(tableData?.['total_inserts'] ?? 0);
const totalDeletes = Number(tableData?.['total_deletes'] ?? 0);
const netRowGrowth = totalInserts - totalDeletes;
const totalRows = Number(tableData?.['total_rows'] ?? 1);
const avgRowSize = currentBytes / Math.max(totalRows, 1);
// Use actual days of data for accurate daily growth rate
const daysOfData = Number(ageData?.['days_of_data'] ?? 1);
const dailyRowGrowth = daysOfData > 0.01 ? netRowGrowth / daysOfData : 0;
const dailyGrowthBytes = dailyRowGrowth * avgRowSize;
const projectedGrowthBytes = dailyGrowthBytes * projectionDays;
const projectedTotalBytes = currentBytes + projectedGrowthBytes;
// Determine estimation quality based on data availability
const estimationQuality = daysOfData < 1
? 'Low confidence - less than 1 day of data'
: daysOfData < 7
? 'Moderate confidence - less than 1 week of data'
: daysOfData < 30
? 'Good confidence - more than 1 week of data'
: 'High confidence - more than 30 days of data';
return {
current: {
databaseSize: dbSize.rows?.[0],
tableCount: tableData?.['table_count'],
totalRows: tableData?.['total_rows'],
connections: `${String(Number(connData?.['current_connections'] ?? 0))}/${String(Number(connData?.['max_connections'] ?? 0))}`
},
growth: {
totalInserts: tableData?.['total_inserts'],
totalDeletes: tableData?.['total_deletes'],
netRowGrowth,
daysOfData: daysOfData.toFixed(1),
statsSince: ageData?.['stats_since'],
estimatedDailyRowGrowth: Math.round(dailyRowGrowth),
estimatedDailyGrowthBytes: Math.round(dailyGrowthBytes),
estimationQuality
},
projection: {
days: projectionDays,
projectedSizeBytes: Math.round(projectedTotalBytes),
projectedSizePretty: `${(projectedTotalBytes / (1024 * 1024 * 1024)).toFixed(2)} GB`,
growthPercentage: currentBytes > 0 ? ((projectedGrowthBytes / currentBytes) * 100).toFixed(1) : '0.0'
},
recommendations: [
projectedTotalBytes > 100 * 1024 * 1024 * 1024 ? 'Consider archiving old data or implementing table partitioning' : null,
(Number(connData?.['current_connections'] ?? 0)) > (Number(connData?.['max_connections'] ?? 100)) * 0.7 ? 'Connection usage is high, consider increasing max_connections' : null,
daysOfData < 7 ? 'Wait for more data accumulation for more accurate projections' : null
].filter(Boolean)
};
}
};
}
/**
* Resource usage analysis
*/
function createResourceUsageAnalyzeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_resource_usage_analyze',
description: 'Analyze current resource usage including CPU, memory, and I/O patterns.',
group: 'monitoring',
inputSchema: z.object({}),
annotations: readOnly('Resource Usage Analysis'),
icons: getToolIcons('monitoring', readOnly('Resource Usage Analysis')),
handler: async (_params: unknown, _context: RequestContext) => {
// Detect PostgreSQL version for checkpoint stats compatibility
const versionResult = await adapter.executeQuery(`SELECT current_setting('server_version_num')::int as version_num`);
const versionNum = Number(versionResult.rows?.[0]?.['version_num'] ?? 0);
const isPg17Plus = versionNum >= 170000;
const [bgWriter, checkpoints, connections, buffers, activity] = await Promise.all([
// PG17+ moved buffers_checkpoint to pg_stat_checkpointer as buffers_written
isPg17Plus
? adapter.executeQuery(`
SELECT
buffers_clean, maxwritten_clean, buffers_alloc
FROM pg_stat_bgwriter
`)
: adapter.executeQuery(`
SELECT
buffers_checkpoint, buffers_clean, buffers_backend,
maxwritten_clean, buffers_alloc
FROM pg_stat_bgwriter
`),
// PG17+ moved checkpoint stats to pg_stat_checkpointer with renamed columns
isPg17Plus
? adapter.executeQuery(`
SELECT
num_timed as checkpoints_timed,
num_requested as checkpoints_req,
write_time as checkpoint_write_time,
sync_time as checkpoint_sync_time,
buffers_written as buffers_checkpoint
FROM pg_stat_checkpointer
`)
: adapter.executeQuery(`
SELECT
checkpoints_timed, checkpoints_req,
checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter
`),
adapter.executeQuery(`
SELECT
state, wait_event_type, wait_event,
count(*) as count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type, wait_event
`),
adapter.executeQuery(`
SELECT
sum(heap_blks_read) as heap_reads,
sum(heap_blks_hit) as heap_hits,
sum(idx_blks_read) as index_reads,
sum(idx_blks_hit) as index_hits
FROM pg_statio_user_tables
`),
adapter.executeQuery(`
SELECT
count(*) FILTER (WHERE state = 'active') as active_queries,
count(*) FILTER (WHERE state = 'idle') as idle_connections,
count(*) FILTER (WHERE wait_event_type = 'Lock') as lock_waiting,
count(*) FILTER (WHERE wait_event_type = 'IO') as io_waiting
FROM pg_stat_activity
WHERE backend_type = 'client backend'
`)
]);
const bufferData = buffers.rows?.[0];
const heapHits = Number(bufferData?.['heap_hits'] ?? 0);
const heapReads = Number(bufferData?.['heap_reads'] ?? 0);
const indexHits = Number(bufferData?.['index_hits'] ?? 0);
const indexReads = Number(bufferData?.['index_reads'] ?? 0);
// Calculate hit rates
const heapHitRate = heapHits + heapReads > 0
? (heapHits / (heapHits + heapReads)) * 100
: null;
const indexHitRate = indexHits + indexReads > 0
? (indexHits / (indexHits + indexReads)) * 100
: null;
// Interpret buffer hit rates
const getHitRateAnalysis = (rate: number | null, type: string): string => {
if (rate === null) return `No ${type} activity recorded yet - run some queries first`;
if (rate >= 99) return `Excellent (${rate.toFixed(2)}%) - nearly all ${type} data served from cache`;
if (rate >= 95) return `Good (${rate.toFixed(2)}%) - most ${type} reads from cache`;
if (rate >= 80) return `Fair (${rate.toFixed(2)}%) - consider increasing shared_buffers`;
return `Poor (${rate.toFixed(2)}%) - significant disk I/O; increase shared_buffers or optimize queries`;
};
return {
backgroundWriter: bgWriter.rows?.[0],
checkpoints: checkpoints.rows?.[0],
connectionDistribution: connections.rows,
bufferUsage: {
...bufferData,
heapHitRate: heapHitRate !== null ? heapHitRate.toFixed(2) + '%' : 'N/A',
indexHitRate: indexHitRate !== null ? indexHitRate.toFixed(2) + '%' : 'N/A'
},
activity: activity.rows?.[0],
analysis: {
heapCachePerformance: getHitRateAnalysis(heapHitRate, 'heap'),
indexCachePerformance: getHitRateAnalysis(indexHitRate, 'index'),
checkpointPressure: Number(checkpoints.rows?.[0]?.['checkpoints_req'] ?? 0) > Number(checkpoints.rows?.[0]?.['checkpoints_timed'] ?? 0)
? 'HIGH - More forced checkpoints than scheduled'
: 'Normal',
ioPattern: Number(activity.rows?.[0]?.['io_waiting'] ?? 0) > 0
? 'Some queries waiting on I/O'
: 'No I/O wait bottlenecks detected',
lockContention: Number(activity.rows?.[0]?.['lock_waiting'] ?? 0) > 0
? `${String(Number(activity.rows?.[0]?.['lock_waiting'] ?? 0))} queries waiting on locks`
: 'No lock contention'
}
};
}
};
}
/**
* Alert threshold recommendations (informational)
*/
function createAlertThresholdSetTool(_adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_alert_threshold_set',
description: 'Get recommended alert thresholds for monitoring key database metrics. Note: This is informational only - returns suggested warning/critical thresholds for external monitoring tools. Does not configure alerts in PostgreSQL itself.',
group: 'monitoring',
inputSchema: z.object({
metric: z.enum([
'connection_usage',
'cache_hit_ratio',
'replication_lag',
'dead_tuples',
'long_running_queries',
'lock_wait_time'
]).optional().describe('Specific metric to get thresholds for, or all if not specified')
}),
annotations: readOnly('Get Alert Thresholds'),
icons: getToolIcons('monitoring', readOnly('Get Alert Thresholds')),
// eslint-disable-next-line @typescript-eslint/require-await
handler: async (params: unknown, _context: RequestContext) => {
const parsed = ((params ?? {}) as { metric?: string });
const thresholds: Record<string, { warning: string; critical: string; description: string }> = {
connection_usage: {
warning: '70%',
critical: '90%',
description: 'Percentage of max_connections in use'
},
cache_hit_ratio: {
warning: '< 95%',
critical: '< 80%',
description: 'Buffer cache hit ratio - lower is worse'
},
replication_lag: {
warning: '> 1 minute',
critical: '> 5 minutes',
description: 'Replication lag from primary to replica'
},
dead_tuples: {
warning: '> 10% of live tuples',
critical: '> 25% of live tuples',
description: 'Dead tuples indicating need for VACUUM'
},
long_running_queries: {
warning: '> 5 minutes',
critical: '> 30 minutes',
description: 'Queries running longer than threshold'
},
lock_wait_time: {
warning: '> 30 seconds',
critical: '> 5 minutes',
description: 'Time spent waiting for locks'
}
};
if (parsed.metric && thresholds[parsed.metric]) {
return {
metric: parsed.metric,
threshold: thresholds[parsed.metric]
};
}
return {
thresholds,
note: 'These are recommended starting thresholds. Adjust based on your specific workload and requirements.'
};
}
};
}
