/**
* PostgreSQL Admin Tools
*
* Database maintenance: VACUUM, ANALYZE, REINDEX, configuration.
* 10 tools total.
*/
import type { PostgresAdapter } from '../PostgresAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../types/index.js';
import { z } from 'zod';
import { admin, destructive } from '../../../utils/annotations.js';
import { getToolIcons } from '../../../utils/icons.js';
import {
VacuumSchema,
VacuumSchemaBase,
AnalyzeSchema,
AnalyzeSchemaBase,
ReindexSchema,
ReindexSchemaBase,
TerminateBackendSchema,
TerminateBackendSchemaBase,
CancelBackendSchema,
CancelBackendSchemaBase
} from '../schemas/index.js';
/**
* Get all admin tools
*/
export function getAdminTools(adapter: PostgresAdapter): ToolDefinition[] {
return [
createVacuumTool(adapter),
createVacuumAnalyzeTool(adapter),
createAnalyzeTool(adapter),
createReindexTool(adapter),
createTerminateBackendTool(adapter),
createCancelBackendTool(adapter),
createReloadConfTool(adapter),
createSetConfigTool(adapter),
createResetStatsTool(adapter),
createClusterTool(adapter)
];
}
function createVacuumTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_vacuum',
description: 'Run VACUUM to reclaim storage and update visibility map. Use analyze: true to also update statistics. Verbose output goes to PostgreSQL server logs.',
group: 'admin',
inputSchema: VacuumSchemaBase,
annotations: admin('Vacuum'),
icons: getToolIcons('admin', admin('Vacuum')),
handler: async (params: unknown, _context: RequestContext) => {
const { table, schema, full, verbose, analyze } = VacuumSchema.parse(params);
const fullClause = full === true ? 'FULL ' : '';
const verboseClause = verbose === true ? 'VERBOSE ' : '';
const analyzeClause = analyze === true ? 'ANALYZE ' : '';
const target = table !== undefined ? (schema !== undefined ? `"${schema}"."${table}"` : `"${table}"`) : '';
const sql = `VACUUM ${fullClause}${verboseClause}${analyzeClause}${target}`;
await adapter.executeQuery(sql);
// Build accurate message reflecting all options used
const parts: string[] = ['VACUUM'];
if (full === true) parts.push('FULL');
if (analyze === true) parts.push('ANALYZE');
const message = `${parts.join(' ')} completed`;
return {
success: true,
message,
...(table !== undefined && { table }),
...(schema !== undefined && { schema }),
...(verbose === true && { hint: 'Verbose output written to PostgreSQL server logs' })
};
}
};
}
function createVacuumAnalyzeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_vacuum_analyze',
description: 'Run VACUUM and ANALYZE together for optimal performance. Verbose output goes to PostgreSQL server logs.',
group: 'admin',
inputSchema: VacuumSchemaBase,
annotations: admin('Vacuum Analyze'),
icons: getToolIcons('admin', admin('Vacuum Analyze')),
handler: async (params: unknown, _context: RequestContext) => {
const { table, schema, verbose, full } = VacuumSchema.parse(params);
const fullClause = full === true ? 'FULL ' : '';
const verboseClause = verbose === true ? 'VERBOSE ' : '';
const target = table !== undefined ? (schema !== undefined ? `"${schema}"."${table}"` : `"${table}"`) : '';
const sql = `VACUUM ${fullClause}${verboseClause}ANALYZE ${target}`;
await adapter.executeQuery(sql);
// Build accurate message
const message = full === true ? 'VACUUM FULL ANALYZE completed' : 'VACUUM ANALYZE completed';
return {
success: true,
message,
...(table !== undefined && { table }),
...(schema !== undefined && { schema }),
...(verbose === true && { hint: 'Verbose output written to PostgreSQL server logs' })
};
}
};
}
function createAnalyzeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_analyze',
description: 'Update table statistics for the query planner.',
group: 'admin',
inputSchema: AnalyzeSchemaBase,
annotations: admin('Analyze'),
icons: getToolIcons('admin', admin('Analyze')),
handler: async (params: unknown, _context: RequestContext) => {
const { table, schema, columns } = AnalyzeSchema.parse(params);
// Validate: columns requires table
if (columns !== undefined && columns.length > 0 && table === undefined) {
throw new Error('table is required when columns is specified');
}
const target = table !== undefined ? (schema !== undefined ? `"${schema}"."${table}"` : `"${table}"`) : '';
const columnClause = columns !== undefined && columns.length > 0 ? `(${columns.map(c => `"${c}"`).join(', ')})` : '';
const sql = `ANALYZE ${target}${columnClause}`;
await adapter.executeQuery(sql);
return {
success: true,
message: 'ANALYZE completed',
...(table !== undefined && { table }),
...(schema !== undefined && { schema }),
...(columns !== undefined && columns.length > 0 && { columns })
};
}
};
}
function createReindexTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_reindex',
description: 'Rebuild indexes to improve performance. For target: database, name defaults to the current database if omitted.',
group: 'admin',
inputSchema: ReindexSchemaBase,
annotations: admin('Reindex'),
icons: getToolIcons('admin', admin('Reindex')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = ReindexSchema.parse(params) as { target: string; name?: string; concurrently?: boolean };
const concurrentlyClause = parsed.concurrently === true ? 'CONCURRENTLY ' : '';
// Auto-default to current database when target is 'database' and name is not provided
let effectiveName = parsed.name;
if (parsed.target === 'database' && effectiveName === undefined) {
const dbResult = await adapter.executeQuery('SELECT current_database()');
const dbName = dbResult.rows?.[0]?.['current_database'];
effectiveName = typeof dbName === 'string' ? dbName : '';
}
// name should always be defined at this point (refine ensures it for non-database targets)
if (effectiveName === undefined) {
throw new Error('name is required');
}
const sql = `REINDEX ${parsed.target.toUpperCase()} ${concurrentlyClause}"${effectiveName}"`;
await adapter.executeQuery(sql);
return { success: true, message: `Reindexed ${parsed.target}: ${effectiveName}` };
}
};
}
function createTerminateBackendTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_terminate_backend',
description: 'Terminate a database connection (forceful, use with caution).',
group: 'admin',
inputSchema: TerminateBackendSchemaBase,
annotations: destructive('Terminate Backend'),
icons: getToolIcons('admin', destructive('Terminate Backend')),
handler: async (params: unknown, _context: RequestContext) => {
const { pid } = TerminateBackendSchema.parse(params);
const sql = `SELECT pg_terminate_backend($1)`;
const result = await adapter.executeQuery(sql, [pid]);
const terminated = result.rows?.[0]?.['pg_terminate_backend'] === true;
return { success: terminated, pid, message: terminated ? 'Backend terminated' : 'Failed to terminate' };
}
};
}
function createCancelBackendTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_cancel_backend',
description: 'Cancel a running query (graceful, preferred over terminate).',
group: 'admin',
inputSchema: CancelBackendSchemaBase,
annotations: admin('Cancel Backend'),
icons: getToolIcons('admin', admin('Cancel Backend')),
handler: async (params: unknown, _context: RequestContext) => {
const { pid } = CancelBackendSchema.parse(params);
const sql = `SELECT pg_cancel_backend($1)`;
const result = await adapter.executeQuery(sql, [pid]);
const cancelled = result.rows?.[0]?.['pg_cancel_backend'] === true;
return { success: cancelled, pid, message: cancelled ? 'Query cancelled' : 'Failed to cancel' };
}
};
}
function createReloadConfTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_reload_conf',
description: 'Reload PostgreSQL configuration without restart.',
group: 'admin',
inputSchema: z.object({}),
annotations: admin('Reload Configuration'),
icons: getToolIcons('admin', admin('Reload Configuration')),
handler: async (_params: unknown, _context: RequestContext) => {
const sql = `SELECT pg_reload_conf()`;
const result = await adapter.executeQuery(sql);
return { success: result.rows?.[0]?.['pg_reload_conf'], message: 'Configuration reloaded' };
}
};
}
/**
* Preprocess set_config parameters:
* - Alias: param/setting → name
*/
function preprocessSetConfigParams(input: unknown): unknown {
if (typeof input !== 'object' || input === null) {
return input;
}
const result = { ...input as Record<string, unknown> };
// Alias: param → name
if (result['param'] !== undefined && result['name'] === undefined) {
result['name'] = result['param'];
}
// Alias: setting → name
if (result['setting'] !== undefined && result['name'] === undefined) {
result['name'] = result['setting'];
}
return result;
}
// Base schema for MCP visibility (shows all parameters and aliases)
const SetConfigSchemaBase = z.object({
name: z.string().optional().describe('Configuration parameter name'),
param: z.string().optional().describe('Alias for name'),
setting: z.string().optional().describe('Alias for name'),
value: z.string().describe('New value'),
isLocal: z.boolean().optional().describe('Apply only to current transaction')
});
// Preprocess schema for handlers
const SetConfigSchema = z.preprocess(
preprocessSetConfigParams,
z.object({
name: z.string().describe('Configuration parameter name'),
value: z.string().describe('New value'),
isLocal: z.boolean().optional().describe('Apply only to current transaction')
})
);
function createSetConfigTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_set_config',
description: 'Set a configuration parameter for the current session.',
group: 'admin',
inputSchema: SetConfigSchemaBase,
annotations: admin('Set Configuration'),
icons: getToolIcons('admin', admin('Set Configuration')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = SetConfigSchema.parse(params);
const local = parsed.isLocal ?? false;
const sql = `SELECT set_config($1, $2, $3)`;
const result = await adapter.executeQuery(sql, [parsed.name, parsed.value, local]);
return { success: true, parameter: parsed.name, value: result.rows?.[0]?.['set_config'] };
}
};
}
/**
* Handle undefined/null params for tools with optional-only parameters
*/
function normalizeOptionalParams(input: unknown): Record<string, unknown> {
if (typeof input !== 'object' || input === null) {
return {};
}
return input as Record<string, unknown>;
}
const ResetStatsSchema = z.preprocess(
normalizeOptionalParams,
z.object({
type: z.enum(['database', 'all']).optional()
})
);
function createResetStatsTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_reset_stats',
description: 'Reset statistics counters (requires superuser).',
group: 'admin',
inputSchema: ResetStatsSchema,
annotations: admin('Reset Statistics'),
icons: getToolIcons('admin', admin('Reset Statistics')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = ResetStatsSchema.parse(params);
let sql: string;
if (parsed.type === 'all') {
sql = `SELECT pg_stat_reset()`;
} else {
sql = `SELECT pg_stat_reset()`;
}
await adapter.executeQuery(sql);
return { success: true, message: 'Statistics reset' };
}
};
}
/**
* Preprocess cluster parameters:
* - Alias: tableName → table
* - Alias: indexName → index
* - Handle undefined input for database-wide CLUSTER
*/
function preprocessClusterParams(input: unknown): unknown {
if (typeof input !== 'object' || input === null) {
return {};
}
const result = { ...input as Record<string, unknown> };
// Alias: tableName → table
if (result['tableName'] !== undefined && result['table'] === undefined) {
result['table'] = result['tableName'];
}
// Alias: indexName → index
if (result['indexName'] !== undefined && result['index'] === undefined) {
result['index'] = result['indexName'];
}
return result;
}
// Base schema for MCP visibility (shows all parameters and aliases)
const ClusterSchemaBase = z.object({
table: z.string().optional().describe('Table name (all previously-clustered tables if omitted)'),
tableName: z.string().optional().describe('Alias for table'),
index: z.string().optional().describe('Index to cluster on (required when table specified)'),
indexName: z.string().optional().describe('Alias for index'),
schema: z.string().optional().describe('Schema name')
});
// Preprocess schema for handlers (table/index are optional for database-wide CLUSTER)
const ClusterSchema = z.preprocess(
preprocessClusterParams,
z.object({
table: z.string().optional().describe('Table name (all previously-clustered tables if omitted)'),
index: z.string().optional().describe('Index to cluster on (required when table specified)'),
schema: z.string().optional()
})
).refine((data) => {
// table and index must both be specified or both be omitted
const parsed = data as { table?: string; index?: string };
const hasTable = parsed.table !== undefined;
const hasIndex = parsed.index !== undefined;
// Both must be present or both absent
return hasTable === hasIndex;
}, {
message: 'table and index must both be specified together, or both omitted for database-wide re-cluster'
});
function createClusterTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_cluster',
description: 'Physically reorder table data based on an index. Call with no args to re-cluster all previously-clustered tables.',
group: 'admin',
inputSchema: ClusterSchemaBase,
annotations: admin('Cluster Table'),
icons: getToolIcons('admin', admin('Cluster Table')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = ClusterSchema.parse(params) as { table?: string; index?: string; schema?: string };
// Database-wide CLUSTER (all previously clustered tables)
if (parsed.table === undefined) {
await adapter.executeQuery('CLUSTER');
return { success: true, message: 'Re-clustered all previously-clustered tables' };
}
// Table-specific CLUSTER
// index is guaranteed by schema refine when table is specified
if (parsed.index === undefined) {
throw new Error('table and index must both be specified together');
}
const tableName = parsed.schema !== undefined ? `"${parsed.schema}"."${parsed.table}"` : `"${parsed.table}"`;
const sql = `CLUSTER ${tableName} USING "${parsed.index}"`;
await adapter.executeQuery(sql);
return { success: true, table: parsed.table, index: parsed.index };
}
};
}
