PostgreSQL extensions required and optional for PostgreSQL MCP Server tools.

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

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

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

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.

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.

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.

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 

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

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;

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

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

ERROR: permission denied to create extension 

Solution: Connect as superuser:

psql -U postgres -d mydb CREATE EXTENSION pg_stat_statements;

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

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.

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)

Add pgvector:

CREATE EXTENSION vector;

Unlocks: +8 vector/semantic search tools

Add PostGIS:

CREATE EXTENSION postgis;

Unlocks: +7 geospatial tools

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

• Quick Start - Initial setup
• MCP Configuration - Server configuration
• Troubleshooting - Common issues

• pg_stat_statements Documentation
• pgvector GitHub
• PostGIS Documentation
• HypoPG GitHub

See Home for more tool categories.