Skip to content
Skills Library
Data & analytics · Analysis

analyze-postgresql-schema

/analyze-postgresql-schema

Analyzes PostgreSQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document PostgreSQL schema objects including tables, triggers, custom types, PL/pgSQL code, and object dependencies.

Licence
MIT
Last repository change
09.10.2026

Assessment and security

84/ 100Class: good
Format9 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
3
Scripts
1
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 09.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What to check before installing
  • 1 scripts in this package. Review them before installing, as Claude may execute them.

Scripts: scripts/postgresql_db_extractor.py

What it does and when to use it

Analyzes PostgreSQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document PostgreSQL schema objects including tables, triggers, custom types, PL/pgSQL code, and object dependencies.

Original author description

Analyzes PostgreSQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document PostgreSQL schema objects including tables, triggers, custom types, PL/pgSQL code, and object dependencies.

How it works

  1. Purpose
  2. Output
  3. Workflow
  4. Files in This Skill
  5. Configuration
  6. Data Collected
  7. JSON Output Structure
  8. Prerequisites
  9. Usage Example
  10. PostgreSQL-Specific Features
Original SKILL.md headings · 344 lines, 1,006 words
SKILL.md preview
---
description: Analyzes PostgreSQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document PostgreSQL schema objects including tables, triggers, custom types, PL/pgSQL code, and object dependencies.
model: sonnet
---

# PostgreSQL Database Schema Analysis Skill

## Purpose

Generate a comprehensive **Markdown report** (`postgresql_schema_report.md`) documenting a PostgreSQL database schema. This report is the primary deliverable for sharing with users, stakeholders, or migration teams.

## Output

Files are written to the configured `OUTPUT_DIR` (from `.env`):

| File | Purpose |
|------|---------|
| `postgresql_schema_report.md` | **Main deliverable** - Human-readable report to share |
| `raw_schema_data.json` | Reference file used to build the report |

**Note**: The `OUTPUT_DIR` must be an **absolute path** matching the migrate-postgresql-to-scalardb skill's `.env` for seamless integration.

---

## Workflow

```
PostgreSQL Database  ──►  raw_schema_data.json  ──►  postgresql_schema_report.md
                          (intermediate)              (deliverable)
```

### Step 1: Collect Data

Run the Python script to extract schema metadata from PostgreSQL:

```bash
cd skills/migrate-postgresql/analyze-postgresql-schema/scripts
python postgresql_db_extractor.py
```

**Options:**
- `--test` - Test database connection only
- `--include-source` - Include full PL/pgSQL source code in output

This produces `raw_schema_data.json` in the configured `OUTPUT_DIR`.

### Step 2: Generate Report

Claude reads the JSON and generates the Markdown report following the template in `analyze-postgresql-dbms_report.md`.

**Rules for report generation:**
- Follow the template structure exactly
- Use the JSON field references indicated in each section
- **Skip sections entirely** if no data exists (no empty tables, no "N/A")
- Calculate summary metrics for the Executive Summary from actual data
- **Do not add migration commentary** - This skill only extracts and documents
- **Do not mark types as "supported" or "not supported"** - That is the job of the `migrate-postgresql-to-scalardb` skill

> **Important**: This skill is purely for **extraction and documentation**. All migration analysis, compatibility judgments, and type mapping decisions are handled by the separate `migrate-postgresql-to-scalardb` skill.

---

## Files in This Skill

| File | Description |
|------|-------------|
| `SKILL.md` | This file - skill documentation |
| `analyze-postgresql-dbms_report.md` | **Report template** - Structure for generating the Markdown report |
| `scripts/postgresql_db_extractor.py` | **Single extraction script** |

**Configuration file** (in `.claude/configuration/`):
| `databases.env` | Consolidated database configuration (Oracle, MySQL, PostgreSQL) |

---

## Configuration

### Centralized Configuration

Configuration is stored in the centralized configuration file:

**Location**: `.claude/configuration/databases.env`

This single file contains all database configuration (Oracle, MySQL, PostgreSQL) for both schema analysis and migration skills.

```properties
# PostgreSQL Database Connection
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=your_database
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_SCHEMA=public  # Optional, defaults to 'public'

# Output directory (ABSOLUTE PATH - shared with migration skill)
OUTPUT_DIR=/absolute/path/to/shared/output

# Report filename
REPORT_FILENAME=postgresql_schema_report.md

# ScalarDB migration namespace
SCALARDB_NAMESPACE=your_namespace

# Include PL/pgSQL source code (true/false)
INCLUDE_PLPGSQL_SOURCE=true

# Path to psql executable (leave empty if psql is in PATH)
PSQL_PATH=
```

**Configuration Search Order**:
1. `.claude/configuration/databases.env` (consolidated - **recommended**)
2. Project root directory
3. Current working directory

---

## Data Collected

The script queries PostgreSQL system catalogs to collect:

### Tables & Constraints
- Tables (regular, partitioned, temporary, foreign)
- Columns (including identity, generated columns)
- Partitions and inheritance
- Constraints (PK, FK, UK, Check)
- SERIAL/BIGSERIAL columns
- Table DDL

### Indexes
- All index types (B-tree, hash, GiST, GIN, BRIN, etc.)
- Index columns and expressions
- Partial indexes (with WHERE clause)
- Unique indexes

### Views
- Standard views with source SQL
- Materialized views with indexes
- View columns

### Custom Types
- ENUM types
- Composite types
- Domain types
- Range types
- Array columns
- JSON/JSONB columns
- XML columns
- Geometric/Network types

### PL/pgSQL Objects
- Functions (all languages)
- Procedures (PostgreSQL 11+)
- Triggers
- Trigger functions
- Event triggers
- Custom aggregates
- Custom operators

### Sequences
- Sequence definitions
- Sequence usage by columns

### Security
- Table privileges
- Column privileges
- Row Level Security (RLS) policies
- Roles

### Extensions
- Installed extensions
- Extension objects

### Miscellaneous
- Schemas
- Foreign tables (FDW)
- Foreign servers
- Publications (logical replication)
- Subscriptions
- Rules
- Policies

### Full-Text Search
- Text search configurations
- tsvector columns

### Dependencies
- Object dependencies
- Invalid objects
- Migration order calculation

---

## JSON Output Structure

The script produces `raw_schema_data.json` with this structure:

```json
{
  "metadata": {
    "generated_at": "ISO timestamp",
    "schema": "SCHEMA_NAME",
    "database_name": "DATABASE_NAME",
    "database_type": "postgresql",
    "version": "16.1"
  },
  "types": {
    "enum_types": [...],
    "composite_types": [...],
    "domain_types": [...],
    "range_types": [...],
    "array_columns": [...],
    "json_columns": [...],
    "xml_columns": [],
    "geometric_columns": [],
    "network_columns": []
  },
  "tables": {
    "tables": [...],
    "columns": [...],
    "identity_columns": [...],
    "generated_columns": [...],
    "serial_columns": [...],
    "partitioned_tables": [...],
    "partitions": [...],
    "inheritance": [...],
    "constraints": [...],
    "check_constraints": [...],
    "foreign_keys": [...],
    "table_ddl": {}
  },
  "indexes": {
    "indexes": [...],
    "index_columns": [...],
    "index_expressions": [...],
    "partial_indexes": [...],
    "unique_indexes": []
  },
  "views": {
    "views": [...],
    "view_columns": [...],
    "materialized_views": [...],
    "mview_indexes": []
  },
  "plpgsql": {
    "functions": [...],
    "procedures": [...],
    "function_arguments": [...],
    "function_source": [...],
    "triggers": [...],
    "trigger_functions": [...],
    "event_triggers": [...],
    "aggregates": [...],
    "operators": []
  },
  "sequences": {
    "sequences": [...],
    "sequence_usage": []
  },
  "security": {
    "table_privileges": [...],
    "column_privileges": [...],
    "rls_policies": [...],
    "roles": []
  },
  "extensions": {
    "extensions": [...],
    "extension_objects": []
  },
  "misc": {
    "schemas": [...],
    "foreign_tables": [...],
    "foreign_servers": [...],
    "publications": [...],
    "subscriptions": [...],
    "rules": [...],
    "policies": []
  },
  "full_text_search": {
    "text_search_configs": [...],
    "tsvector_columns": []
  },
  "dependencies": {
    "dependencies": [...],
    "invalid_objects": [...],
    "migration_order": []
  }
}
```

---

## Prerequisites

- Python 3.8+
- `python-dotenv` package (`pip install python-dotenv`)
- PostgreSQL client (psql)
- Network access to PostgreSQL database

---

## Usage Example

```bash
# Navigate to scripts folder
cd skills/migrate-postgresql/analyze-postgresql-schema/scripts

# Test connection first
python postgresql_db_extractor.py --test

# Run full extraction
python postgresql_db_extractor.py

# Include PL/pgSQL source code
python postgresql_db_extractor.py --include-source
```

After extraction, ask Claude:
> "Generate postgresql_schema_report.md from raw_schema_data.json following the template"

---

## PostgreSQL-Specific Features

### Key Differences from Oracle

| Feature | PostgreSQL | Oracle |
|---------|------------|--------|
| Identity | SERIAL, IDENTITY | IDENTITY, SEQUENCE |
| Arrays | Native array types | VARRAY, nested table |
| JSON | JSON, JSONB | JSON type |
| Full-text | tsvector/tsquery | Oracle Text |
| Inheritance | Table inheritance | N/A |
| Partitioning | Declarative, inheritance | RANGE, LIST, HASH |
| Extensions | pg_extension | Cartridges |
| RLS | pg_policy | VPD |

### Supported PostgreSQL Versions

- PostgreSQL 12+ (recommended)
- PostgreSQL 11 (limited procedure support)
- PostgreSQL 10 (limited features)

---

*Skill Version: 1.0*
*Compatible with: PostgreSQL 12+*
Files in this package (3)
  • SKILL.md 9 KB
  • analyze-postgresql-dbms_report.md 20 KB
  • scripts/postgresql_db_extractor.py 72 KB

Versions

The database has no other skills named analyze-postgresql-schema.

Database snapshot: 13.11.2026 · package version 861f45355c07 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 1 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 84analyze-oracle-schema
    Analyzes Oracle database schema and generates a comprehensive Markdown report for sharing. Use when you need to document Oracle schema objects including tables, triggers, custom types, PL/SQL code, and object dependencies.
  • 84analyze-mysql-schema
    Analyzes MySQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document MySQL schema objects including tables, triggers, stored procedures, views, and object dependencies.
  • 100postgresql-patterns
    PostgreSQL engine and language features - advanced data types (JSONB, arrays, ranges, geometric, custom types/domains/ENUM), extensions, window functions, CTEs, full-text search, PL/pgSQL, and error reporting (RAISE / SQLSTATE). Use when writing PostgreSQL DDL/DML that should…
  • 100pgsql-coding
    PostgreSQL coding-standards router for PostgreSQL 18 and later (19 additions flagged as hints). This skill should be used when a task writes, reviews, tunes or debugs SQL, a .sql file, a migration, a schema, or a query string in Go (pgx) or PHP (PDO) code, when the task spans…
  • 100postgresql-guide
    Query PostgreSQL official documentation for accurate SQL syntax, configuration, and best practices. Use this skill proactively when the conversation involves: - PostgreSQL SQL syntax, commands, queries - Data types, functions, operators - Database configuration, postgresql.conf…
  • 100postgres-schema
    Design or review PostgreSQL tables with the right column types, constraints that keep bad data out and the indexes the queries need. Use it whenever you create or change a table, choose a type, add a foreign key or index, model money, time, status or JSON, or review an ORM model.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.