PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/setup.ts:7-105 (handler)

  Core handler function that generates platform-specific PostgreSQL setup instructions, including installation steps, configuration settings, and post-install actions based on version and use case.

  export function getSetupInstructions(
    platform: 'linux' | 'macos' | 'windows',
    version = 'latest',
    useCase: 'development' | 'production' = 'development'
  ): SetupInstructions {
    const instructions: SetupInstructions = {
      steps: [],
      configuration: [],
      postInstall: []
    };
  
    // Installation steps
    switch (platform) {
      case 'linux':
        instructions.steps = [
          '# Add PostgreSQL repository',
          'sudo sh -c \'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list\'',
          'wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -',
          'sudo apt-get update',
          `sudo apt-get install postgresql${version === 'latest' ? '' : `-${version}`}`,
          '# Start PostgreSQL service',
          'sudo systemctl start postgresql',
          'sudo systemctl enable postgresql'
        ];
        break;
  
      case 'macos':
        instructions.steps = [
          '# Install PostgreSQL using Homebrew',
          'brew update',
          `brew install postgresql${version === 'latest' ? '' : '@' + version}`,
          '# Start PostgreSQL service',
          'brew services start postgresql'
        ];
        break;
  
      case 'windows':
        instructions.steps = [
          '# Download PostgreSQL installer',
          'Download the installer from https://www.postgresql.org/download/windows/',
          'Run the installer and follow the setup wizard',
          'Ensure to remember the superuser password you set during installation'
        ];
        break;
    }
  
    // Basic configuration
    instructions.configuration = [
      '# Edit postgresql.conf with recommended settings',
      useCase === 'production' ? 'max_connections = 100' : 'max_connections = 20',
      useCase === 'production' ? 'shared_buffers = 25% of RAM' : 'shared_buffers = 128MB',
      'work_mem = 4MB',
      'maintenance_work_mem = 64MB',
      'effective_cache_size = 50% of RAM',
      'synchronous_commit = on',
      'fsync = on',
      useCase === 'production' ? 'full_page_writes = on' : 'full_page_writes = off',
      'log_destination = \'csvlog\'',
      'logging_collector = on',
      'log_min_duration_statement = 250ms'
    ];
  
    // Security configuration for production
    if (useCase === 'production') {
      instructions.configuration.push(
        '# Security settings',
        'ssl = on',
        'ssl_cert_file = \'server.crt\'',
        'ssl_key_file = \'server.key\'',
        'password_encryption = scram-sha-256',
        'authentication_timeout = 1min'
      );
    }
  
    // Post-installation steps
    instructions.postInstall = [
      '# Create a new database and user',
      'sudo -u postgres psql',
      'CREATE DATABASE myapp;',
      'CREATE USER myuser WITH ENCRYPTED PASSWORD \'mypass\';',
      'GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;',
      '\\q'
    ];
  
    if (useCase === 'production') {
      instructions.postInstall.push(
        '# Additional production security steps',
        'pg_hba.conf configuration:',
        'hostssl all all 0.0.0.0/0 scram-sha-256',
        'host all all 127.0.0.1/32 scram-sha-256',
        '',
        '# Setup automated backups',
        '# Add to crontab:',
        '0 0 * * * pg_dump -U postgres myapp > /backup/myapp_$(date +%Y%m%d).sql'
      );
    }
  
    return instructions;
  }

• src/index.ts:35-58 (schema)

  Input schema defining parameters for the get_setup_instructions tool: platform (required), version, and useCase.

  {
    name: 'get_setup_instructions',
    description: 'Get step-by-step PostgreSQL setup instructions',
    inputSchema: {
      type: 'object',
      properties: {
        version: {
          type: 'string',
          description: 'PostgreSQL version to install'
        },
        platform: {
          type: 'string',
          enum: ['linux', 'macos', 'windows'],
          description: 'Operating system platform'
        },
        useCase: {
          type: 'string',
          enum: ['development', 'production'],
          description: 'Intended use case'
        }
      },
      required: ['platform']
    }
  },

• src/index.ts:102-107 (registration)

  Registers the get_setup_instructions tool in the MCP server capabilities using TOOL_DEFINITIONS.

    tools: {
      analyze_database: TOOL_DEFINITIONS[0],
      get_setup_instructions: TOOL_DEFINITIONS[1],
      debug_database: TOOL_DEFINITIONS[2]
    },
  },

• src/index.ts:145-169 (handler)

  MCP CallToolRequest handler that extracts arguments, calls getSetupInstructions, and formats the response as markdown text.

  case 'get_setup_instructions': {
    const { platform, version, useCase } = request.params.arguments as {
      platform: 'linux' | 'macos' | 'windows';
      version?: string;
      useCase?: 'development' | 'production';
    };
    const instructions = getSetupInstructions(platform, version, useCase);
    return {
      content: [
        {
          type: 'text',
          text: [
            '# Installation Steps',
            ...instructions.steps,
            '',
            '# Configuration',
            ...instructions.configuration,
            '',
            '# Post-Installation Steps',
            ...instructions.postInstall
          ].join('\n')
        }
      ]
    };
  }