Skip to content

Extension Setup

Jump to bottom
Temp edited this page Oct 3, 2025 · 1 revision

Extension Setup

PostgreSQL extensions required and optional for PostgreSQL MCP Server tools.

📊 Extension Overview

Extension Status Purpose Tools Affected
pg_stat_statements Required Query tracking Performance, Monitoring
pg_trgm Required Text similarity Text Processing
fuzzystrmatch Required Fuzzy matching Text Processing
hypopg Optional Hypothetical indexes Performance
pgvector Optional Vector similarity Vector Search (8 tools)
postgis Optional Geospatial operations GIS (7 tools)

🔧 Required Extensions

pg_stat_statements

Query performance tracking and statistics.

Installation:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Configuration (postgresql.conf):

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Restart PostgreSQL after configuration changes.

Verification:

SELECT * FROM pg_stat_statements LIMIT 5;

Tools Using This Extension:

  • get_top_queries
  • workload_analysis
  • monitor_real_time
  • resource_usage_analyze

pg_trgm

Trigram-based text similarity search.

Installation:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Verification:

SELECT similarity('hello', 'hallo');
-- Expected: 0.6 (or similar)

Tools Using This Extension:

  • text_similarity
  • text_search_advanced

fuzzystrmatch

Fuzzy string matching (Levenshtein distance, Soundex, Metaphone).

Installation:

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Verification:

SELECT levenshtein('hello', 'hallo');
-- Expected: 1

Tools Using This Extension:

  • fuzzy_match
  • text_similarity

⚙️ Optional Extensions

hypopg (Hypothetical Indexes)

Test index performance without creating them.

Installation:

From Source (requires PostgreSQL development headers):

git clone https://github.com/HypoPG/hypopg.git
cd hypopg
make
sudo make install

Enable Extension:

CREATE EXTENSION IF NOT EXISTS hypopg;

Verification:

SELECT * FROM hypopg_list_indexes();

Tools Using This Extension:

  • hypothetical_index_test

Note: If not installed, hypothetical_index_test will return informative error.

pgvector (Vector Similarity)

Vector embeddings and similarity search for AI/ML applications.

Installation:

From Source:

git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

Using Package Manager (Ubuntu/Debian):

sudo apt install postgresql-16-pgvector

Enable Extension:

CREATE EXTENSION IF NOT EXISTS vector;

Verification:

SELECT '[1,2,3]'::vector;

Tools Using This Extension (8 tools):

  • vector_search
  • vector_similarity
  • vector_index_create
  • vector_index_optimize
  • vector_cluster
  • vector_stats
  • semantic_search
  • embedding_insert

Version: Requires pgvector v0.5.0 or later for all features.

PostGIS (Geospatial)

Geospatial data types and operations.

Installation:

Using Package Manager (Ubuntu/Debian):

sudo apt install postgresql-16-postgis-3

macOS (Homebrew):

brew install postgis

Enable Extension:

CREATE EXTENSION IF NOT EXISTS postgis;

Verification:

SELECT PostGIS_Version();

Tools Using This Extension (7 tools):

  • spatial_search
  • distance_calculate
  • within_radius
  • geometry_validate
  • coordinate_transform
  • spatial_index_create
  • geo_stats

Version: Requires PostGIS 3.0 or later.

✅ Extension Verification Script

Run this SQL to verify all extensions:

SELECT
    extname AS extension_name,
    extversion AS version,
    CASE
        WHEN extname IN ('pg_stat_statements', 'pg_trgm', 'fuzzystrmatch') THEN 'Required'
        WHEN extname IN ('hypopg', 'vector', 'postgis') THEN 'Optional'
        ELSE 'Other'
    END AS status
FROM pg_extension
WHERE extname IN (
    'pg_stat_statements',
    'pg_trgm',
    'fuzzystrmatch',
    'hypopg',
    'vector',
    'postgis'
)
ORDER BY status, extname;

Expected output:

extension_name       | version | status
---------------------+---------+----------
fuzzystrmatch        | 1.1     | Required
pg_stat_statements   | 1.10    | Required
pg_trgm              | 1.6     | Required
hypopg               | 1.4.0   | Optional
postgis              | 3.4.0   | Optional
vector               | 0.6.0   | Optional

🐳 Docker Setup

With Required Extensions Only

FROM postgres:16

RUN apt-get update && apt-get install -y \\
    postgresql-16-pg-stat-statements \\
    postgresql-contrib

# Extensions will be created when container starts

With All Extensions

FROM postgres:16

RUN apt-get update && apt-get install -y \\
    postgresql-16-pg-stat-statements \\
    postgresql-contrib \\
    postgresql-16-postgis-3 \\
    postgresql-16-pgvector \\
    build-essential \\
    git

# Install HypoPG from source
RUN git clone https://github.com/HypoPG/hypopg.git && \\
    cd hypopg && \\
    make && \\
    make install

# Create extensions on database initialization
COPY init-extensions.sql /docker-entrypoint-initdb.d/

init-extensions.sql:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION IF NOT EXISTS hypopg;
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS postgis;

🔍 Troubleshooting

Extension Not Found

ERROR: could not open extension control file

Solution:

  1. Install the extension package for your PostgreSQL version
  2. Verify package installation: dpkg -l | grep postgresql
  3. Check extension directory: pg_config --sharedir

pg_stat_statements Not Collecting Data

Check Configuration:

SHOW shared_preload_libraries;
-- Should include 'pg_stat_statements'

If not present:

  1. Edit postgresql.conf
  2. Add shared_preload_libraries = 'pg_stat_statements'
  3. Restart PostgreSQL

Permission Denied Creating Extension

ERROR: permission denied to create extension

Solution: Connect as superuser:

psql -U postgres -d mydb
CREATE EXTENSION pg_stat_statements;

HypoPG Not Available

If hypothetical_index_test returns error:

Option 1: Install HypoPG (see installation above)

Option 2: Use alternative tools:

  • suggest_indexes - Still works without HypoPG
  • index_usage_stats - Monitor existing indexes
  • explain_query - Manual index testing

📊 Extension Impact on Tools

Without Optional Extensions

Tools will gracefully degrade:

# pgvector not installed
result = vector_search(...)
# Returns: {"success": False, "error": "pgvector extension not installed"}

# PostGIS not installed
result = spatial_search(...)
# Returns: {"success": False, "error": "PostGIS extension not installed"}

# HypoPG not installed
result = hypothetical_index_test(...)
# Returns: {"success": False, "error": "HypoPG extension not installed"}

All other tools remain fully functional.

🎯 Recommended Setup

For Core Functionality

Install required extensions only:

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION fuzzystrmatch;

Unlocks: 45+ tools (Core, JSON, Text, Stats, Performance, Monitoring, Backup)

For AI/ML Applications

Add pgvector:

CREATE EXTENSION vector;

Unlocks: +8 vector/semantic search tools

For GIS Applications

Add PostGIS:

CREATE EXTENSION postgis;

Unlocks: +7 geospatial tools

For Complete Functionality

Install all extensions:

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION hypopg;
CREATE EXTENSION vector;
CREATE EXTENSION postgis;

Unlocks: All 63 tools

📚 Related Documentation

🔗 External Resources

See Home for more tool categories.

Clone this wiki locally