postgres

package
v0.1.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 14, 2025 License: MIT Imports: 10 Imported by: 0

README

PostgreSQL Schema Extractor

Complete PostgreSQL schema metadata extraction with support for PostgreSQL 10+.

Features

  • Multiple Schemas: Support for multiple schemas per database
  • Transaction-based Extraction: Single read-only transaction for consistency
  • System Object Filtering: Filters 900+ built-in PostgreSQL functions
  • Cloud Provider Support: Filters AWS RDS, GCP CloudSQL, AlloyDB objects
  • Extension Tracking: Tracks extension dependencies
  • Complete Metadata: All PostgreSQL-specific objects and features
  • Version-aware: Detects PostgreSQL version and adjusts extraction accordingly

Quick Start

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
    "github.com/nsxbet/sql-schema/postgres"
)

func main() {
    db, err := sql.Open("postgres",
        "postgres://user:password@localhost/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    extractor := postgres.NewExtractor(db, "mydb")

    schema, err := extractor.ExtractSchema(context.Background())
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("Database: %s\n", schema.Name)
    fmt.Printf("Search Path: %v\n", schema.SearchPath)

    for _, s := range schema.Schemas {
        fmt.Printf("Schema: %s\n", s.Name)
        fmt.Printf("  Tables: %d\n", len(s.Tables))
        fmt.Printf("  Views: %d\n", len(s.Views))
        fmt.Printf("  Functions: %d\n", len(s.Functions))
    }
}

API

NewExtractor
func NewExtractor(db *sql.DB, databaseName string) *Extractor

Creates a new PostgreSQL schema extractor.

Parameters:

  • db: Active *sql.DB connection to the PostgreSQL server
  • databaseName: Name of the database to extract

Returns:

  • *Extractor: Ready-to-use extractor instance
ListDatabases
func (e *Extractor) ListDatabases(ctx context.Context) ([]string, error)

Returns a list of all user databases, excluding:

  • Template databases (template0, template1)
  • System databases (postgres)
  • Cloud provider databases (rdsadmin, cloudsqladmin, etc.)

Returns:

  • []string: List of database names
  • error: Any error encountered during listing

Example:

databases, err := extractor.ListDatabases(ctx)
if err != nil {
    log.Fatal(err)
}

for _, dbName := range databases {
    fmt.Println(dbName)
}
ExtractSchema
func (e *Extractor) ExtractSchema(ctx context.Context) (*schemaextract.DatabaseSchema, error)

Extracts the complete schema for the configured database using a single read-only transaction for consistency.

Returns:

  • *schemaextract.DatabaseSchema: Complete schema metadata
  • error: Any error encountered during extraction

Example:

schema, err := extractor.ExtractSchema(ctx)
if err != nil {
    log.Fatal(err)
}

// Access schema data
for _, s := range schema.Schemas {
    fmt.Printf("Schema: %s has %d tables\n", s.Name, len(s.Tables))

    for _, table := range s.Tables {
        fmt.Printf("  Table: %s (%d columns)\n", table.Name, len(table.Columns))
    }
}

Extracted Objects

Multiple Schemas

PostgreSQL supports multiple schemas (namespaces) per database:

type Schema struct {
    Name               string
    Tables             []*Table
    Views              []*View
    MaterializedViews  []*MaterializedView
    Functions          []*Function
    Procedures         []*Procedure
    Triggers           []*Trigger
    Sequences          []*Sequence
    Extensions         []*Extension
    EnumTypes          []*EnumType
}

Example: Listing all schemas

for _, s := range schema.Schemas {
    fmt.Printf("Schema: %s\n", s.Name)
    fmt.Printf("  Tables: %d\n", len(s.Tables))
    fmt.Printf("  Views: %d\n", len(s.Views))
    fmt.Printf("  Functions: %d\n", len(s.Functions))
}
Tables

Complete table metadata:

type Table struct {
    Name             string
    Columns          []*Column
    Indexes          []*Index
    ForeignKeys      []*ForeignKey
    CheckConstraints []*CheckConstraint
    Triggers         []*Trigger
    Partitions       []*Partition    // PostgreSQL 10+
    Comment          string
    RowCount         int64            // Approximate from pg_class
    DataSize         int64            // pg_relation_size
    IndexSize        int64            // pg_indexes_size
}

Example: Finding large tables

for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        totalMB := float64(table.DataSize+table.IndexSize) / 1024 / 1024
        if totalMB > 1000 {
            fmt.Printf("Large table: %s.%s (%.2f MB)\n", s.Name, table.Name, totalMB)
        }
    }
}
Columns

Full column metadata with PostgreSQL-specific features:

type Column struct {
    Name       string
    Type       string       // PostgreSQL data type
    Nullable   bool
    Default    string       // Default value expression
    Comment    string
    Collation  string
    Position   int          // Ordinal position (1-based)
    Generation *Generation  // Generated column (PostgreSQL 12+)
}

PostgreSQL-specific types:

  • Array types: integer[], text[]
  • JSON types: json, jsonb
  • Network types: inet, cidr, macaddr
  • Geometric types: point, line, polygon
  • Range types: int4range, tsrange, daterange
  • Custom enum types
  • Composite types

Example: Finding JSONB columns

for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        for _, col := range table.Columns {
            if col.Type == "jsonb" {
                fmt.Printf("JSONB column: %s.%s.%s\n", s.Name, table.Name, col.Name)
            }
        }
    }
}
Indexes

Index metadata with PostgreSQL-specific features:

type Index struct {
    Name     string
    Type     string   // btree, hash, gist, gin, brin, spgist
    Unique   bool
    Primary  bool
    Columns  []string
    Comment  string
}

PostgreSQL Index Types:

  • btree: Default, supports ordering and range queries
  • hash: Equality comparisons only
  • gist: Generalized search tree (geometry, full-text)
  • gin: Generalized inverted index (arrays, JSONB, full-text)
  • brin: Block range index (large tables with natural ordering)
  • spgist: Space-partitioned GiST (non-balanced structures)

Example: Finding specialized indexes

for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        for _, idx := range table.Indexes {
            if idx.Type == "gin" {
                fmt.Printf("GIN index: %s.%s.%s\n", s.Name, table.Name, idx.Name)
            }
        }
    }
}
Views

Regular views:

type View struct {
    Name       string
    Definition string    // View definition (SELECT statement)
    Comment    string
    Columns    []*Column
}
Materialized Views

Materialized views with refresh capability:

type MaterializedView struct {
    Name       string
    Definition string    // View definition
    Comment    string
    Columns    []*Column
}

Example: Listing materialized views

for _, s := range schema.Schemas {
    for _, mv := range s.MaterializedViews {
        fmt.Printf("Materialized view: %s.%s\n", s.Name, mv.Name)
        fmt.Printf("  Definition: %s\n", mv.Definition)
    }
}
Functions

PL/pgSQL and other language functions:

type Function struct {
    Name       string
    Definition string    // Complete function definition (CREATE FUNCTION ...)
    Comment    string
}

System Function Filtering:

The extractor automatically filters out 900+ built-in PostgreSQL functions including:

  • Mathematical functions (abs, ceil, floor, etc.)
  • String functions (concat, substring, etc.)
  • Date/time functions (now, date_trunc, etc.)
  • Aggregate functions (sum, avg, count, etc.)
  • Window functions
  • System information functions

Example: Listing user-defined functions

for _, s := range schema.Schemas {
    for _, fn := range s.Functions {
        fmt.Printf("Function: %s.%s\n", s.Name, fn.Name)
    }
}
Procedures

Stored procedures (PostgreSQL 11+):

type Procedure struct {
    Name       string
    Definition string    // Complete procedure definition
    Comment    string
}

Note: Procedures require PostgreSQL 11+. On older versions, this list will be empty.

Sequences

Sequence generators:

type Sequence struct {
    Name      string
    DataType  string  // bigint, integer, smallint
    Start     int64   // Start value
    Increment int64   // Increment by
    MinValue  int64   // Minimum value
    MaxValue  int64   // Maximum value
    Cache     int64   // Cache size
    Cycle     bool    // Cycle when reaching max/min
    Comment   string
}

Example: Listing sequences

for _, s := range schema.Schemas {
    for _, seq := range s.Sequences {
        fmt.Printf("Sequence: %s.%s (start: %d, increment: %d)\n",
            s.Name, seq.Name, seq.Start, seq.Increment)
    }
}
Extensions

Installed PostgreSQL extensions:

type Extension struct {
    Name    string
    Version string
    Comment string
}

Common Extensions:

  • postgis - Spatial and geographic objects
  • pg_trgm - Trigram matching for text search
  • uuid-ossp - UUID generation
  • hstore - Key-value store
  • citext - Case-insensitive text type
  • pgcrypto - Cryptographic functions

Example: Listing extensions

for _, s := range schema.Schemas {
    for _, ext := range s.Extensions {
        fmt.Printf("Extension: %s (version: %s)\n", ext.Name, ext.Version)
    }
}
Enum Types

Custom enumeration types:

type EnumType struct {
    Name   string
    Values []string  // Ordered list of enum values
}

Example: Listing enum types

for _, s := range schema.Schemas {
    for _, enum := range s.EnumTypes {
        fmt.Printf("Enum: %s.%s = %v\n", s.Name, enum.Name, enum.Values)
    }
}
Triggers

Table triggers:

type Trigger struct {
    Name    string
    Timing  string  // BEFORE, AFTER, INSTEAD OF
    Event   string  // INSERT, UPDATE, DELETE, TRUNCATE
    Body    string  // Trigger function call
    Comment string
}
Partitions

Table partitions (PostgreSQL 10+):

type Partition struct {
    Name        string
    Type        string      // RANGE, LIST, HASH
    Expression  string      // Partitioning expression
    Description string      // Partition bounds
    Comment     string
}

Supported Partition Types:

  • RANGE - Partition by value ranges
  • LIST - Partition by value lists
  • HASH - Partition by hash of values

System Object Filtering

The extractor automatically filters out:

System Schemas
  • pg_catalog
  • information_schema
  • pg_toast
  • pg_temp_*
  • pg_toast_temp_*
Cloud Provider Schemas
  • AWS RDS: rdsadmin
  • GCP CloudSQL: cloudsqladmin, cloudsqlsuperuser
  • AlloyDB: alloydbsuperuser, alloydbadmin
Extension Schemas
  • TimescaleDB: timescaledb*
  • Citus: citus, citus_internal
  • Partman: partman
  • pg_cron: cron
  • And many more...
System Functions

900+ built-in functions are filtered including all functions from:

  • pg_catalog schema
  • Common extensions (postgis, timescaledb, etc.)
  • Internal functions

Version Support

PostgreSQL 10+

Full support for:

  • Table partitions (RANGE, LIST, HASH)
  • Generated columns (PostgreSQL 12+)
  • Procedures (PostgreSQL 11+)
  • All standard objects
PostgreSQL 9.6 and Earlier

Not officially supported, but may work with reduced functionality:

  • No partition extraction
  • No procedures
  • No generated columns

Connection Strings

Basic Connection
db, err := sql.Open("postgres", "postgres://user:password@localhost/mydb")
With SSL
db, err := sql.Open("postgres",
    "postgres://user:password@localhost/mydb?sslmode=require")

SSL Modes:

  • disable - No SSL
  • require - Require SSL (no verification)
  • verify-ca - Verify CA certificate
  • verify-full - Verify CA and hostname
With Timeout
db, err := sql.Open("postgres",
    "postgres://user:password@localhost/mydb?connect_timeout=30")
With Application Name
db, err := sql.Open("postgres",
    "postgres://user:password@localhost/mydb?application_name=schema_extractor")
Full Example with Configuration
import (
    "database/sql"
    "time"
)

connStr := "postgres://user:password@localhost:5432/mydb" +
    "?sslmode=require" +
    "&connect_timeout=30" +
    "&application_name=schema_extractor"

db, err := sql.Open("postgres", connStr)
if err != nil {
    log.Fatal(err)
}

// Configure connection pool
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Hour)

Required Permissions

The PostgreSQL user needs SELECT permission on system catalogs:

-- Grant access to system catalogs
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO schema_user;

-- Grant access to user schemas
GRANT USAGE ON SCHEMA public TO schema_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO schema_user;

-- For listing all databases
GRANT CONNECT ON DATABASE template1 TO schema_user;

Common Use Cases

Find Tables by Schema
schema, _ := extractor.ExtractSchema(ctx)

publicTables := 0
for _, s := range schema.Schemas {
    if s.Name == "public" {
        publicTables = len(s.Tables)
        break
    }
}

fmt.Printf("Public schema has %d tables\n", publicTables)
List All Partitioned Tables
for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        if len(table.Partitions) > 0 {
            fmt.Printf("Partitioned: %s.%s (%s, %d partitions)\n",
                s.Name, table.Name,
                table.Partitions[0].Type, len(table.Partitions))
        }
    }
}
Find Foreign Key Relationships
for _, s := range schema.Schemas {
    for _, table := range s.Tables {
        for _, fk := range table.ForeignKeys {
            fmt.Printf("%s.%s.%s → %s.%s (ON DELETE %s)\n",
                s.Name, table.Name,
                strings.Join(fk.Columns, ","),
                fk.ReferencedTable,
                strings.Join(fk.ReferencedColumns, ","),
                fk.OnDelete)
        }
    }
}
Analyze Extensions
for _, s := range schema.Schemas {
    for _, ext := range s.Extensions {
        fmt.Printf("Extension: %s v%s\n", ext.Name, ext.Version)
        if ext.Comment != "" {
            fmt.Printf("  Description: %s\n", ext.Comment)
        }
    }
}
Find Materialized Views That Need Refresh
// List all materialized views
for _, s := range schema.Schemas {
    for _, mv := range s.MaterializedViews {
        fmt.Printf("Materialized view: %s.%s\n", s.Name, mv.Name)
        fmt.Println("  Run: REFRESH MATERIALIZED VIEW " + s.Name + "." + mv.Name)
    }
}
Export Schema Structure as Markdown
for _, s := range schema.Schemas {
    fmt.Printf("# Schema: %s\n\n", s.Name)

    fmt.Println("## Tables\n")
    for _, table := range s.Tables {
        fmt.Printf("### %s\n\n", table.Name)
        if table.Comment != "" {
            fmt.Printf("%s\n\n", table.Comment)
        }

        fmt.Println("| Column | Type | Nullable | Default |")
        fmt.Println("|--------|------|----------|---------|")
        for _, col := range table.Columns {
            nullable := "NO"
            if col.Nullable {
                nullable = "YES"
            }
            fmt.Printf("| %s | %s | %s | %s |\n",
                col.Name, col.Type, nullable, col.Default)
        }
        fmt.Println()
    }
}

Troubleshooting

Error: "permission denied for table pg_proc"

Your user doesn't have access to system catalogs.

Solution:

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO your_user;
Error: "database does not exist"

The specified database name doesn't exist.

Solution:

-- List all databases
SELECT datname FROM pg_database WHERE datistemplate = false;
Error: "connection refused"

Cannot connect to PostgreSQL server.

Solution:

  • Check PostgreSQL is running: pg_ctl status
  • Verify postgresql.conf has listen_addresses = '*' or appropriate IP
  • Check pg_hba.conf allows connections from your host
  • Verify firewall rules allow port 5432
Error: "SSL is required"

Server requires SSL but connection doesn't use it.

Solution:

db, err := sql.Open("postgres",
    "postgres://user:password@localhost/mydb?sslmode=require")
Slow Extraction

Extraction takes a long time for large databases.

Causes:

  • Many tables (1000+)
  • Large view/function definitions
  • Heavy database load

Solutions:

  • Extract during off-peak hours
  • Increase timeout: ?connect_timeout=120
  • Use connection pooling
  • Consider extracting specific schemas only

Performance Considerations

Transaction Consistency

All extraction happens in a single read-only transaction, ensuring a consistent snapshot even while the database is being modified.

Extraction Speed

Typical extraction times:

Objects Extraction Time
100 tables, 50 functions ~100ms
500 tables, 100 functions ~400ms
1000 tables, 500 functions ~2s
5000 tables, 1000 functions ~10s
Memory Usage

Memory usage scales with database size:

  • 100 tables: ~10MB
  • 1000 tables: ~50MB
  • 5000 tables: ~200MB

Examples

See the examples directory for complete working examples.

Documentation

Overview

Package postgres provides PostgreSQL-specific database schema extraction.

Overview

This package extracts complete schema metadata from PostgreSQL databases (versions 10+) by querying pg_catalog system tables and information_schema.

Supported Features

The PostgreSQL extractor captures:

  • Multiple schemas (namespaces)
  • Tables with storage parameters
  • Columns including IDENTITY, generated columns
  • Indexes (BTREE, HASH, GIN, GIST, BRIN, SP-GIST)
  • Partial indexes (with WHERE clauses)
  • Expression indexes
  • Foreign keys with referential actions
  • Check constraints
  • Triggers
  • Views and materialized views
  • Functions (including stored procedures in PG 11+)
  • Sequences
  • Extensions (PostGIS, pg_trgm, etc.)
  • Enum types
  • Rules (rewrite rules)
  • Table inheritance
  • Partitioning (declarative partitioning)

Usage

Basic extraction: 

import (
    "database/sql"
    _ "github.com/lib/pq"
    "github.com/NSXBet/sql-schema/extractor/postgres"
)

db, _ := sql.Open("postgres", "postgresql://user:pass@localhost/mydb")
schema, err := postgres.Extract(db)

Extraction with options: 

schema, err := postgres.Extract(db, postgres.Options{
    Schemas:       []string{"public", "app"},
    ExcludeTables: []string{"temp_*"},
    IncludeSystemSchemas: false,
})

Schema Filtering

By default, the extractor includes all non-system schemas. System schemas are automatically excluded:

  • pg_catalog
  • information_schema
  • pg_toast
  • pg_temp_*

Use the Schemas option to explicitly specify which schemas to extract.

Database Permissions

The extractor requires SELECT permissions on:

  • pg_catalog.pg_namespace
  • pg_catalog.pg_class
  • pg_catalog.pg_attribute
  • pg_catalog.pg_index
  • pg_catalog.pg_constraint
  • pg_catalog.pg_trigger
  • pg_catalog.pg_proc
  • pg_catalog.pg_type
  • pg_catalog.pg_sequence
  • pg_catalog.pg_extension
  • pg_catalog.pg_enum
  • pg_catalog.pg_rewrite
  • information_schema.views
  • information_schema.columns

For statistics:

  • SELECT on target tables

Version Compatibility

PostgreSQL 10+:

  • All basic features supported
  • Declarative partitioning
  • Identity columns

PostgreSQL 11+:

  • Stored procedures (vs functions)
  • Hash partitioning

PostgreSQL 12+:

  • Generated columns
  • Improved partitioning

PostgreSQL 13+:

  • Extended statistics
  • Incremental materialized view refresh

Performance Considerations

Extraction performance depends on:

  • Number of schemas and objects
  • Extension complexity (e.g., PostGIS objects)
  • Network latency
  • Server load

For large databases:

  • Filter to specific schemas
  • Exclude statistics collection
  • Use connection pooling

System Object Handling

The package includes utilities to identify and optionally exclude:

  • System tables (pg_*)
  • Extension tables (spatial_ref_sys, topology, etc.)
  • Temporary tables
  • Toast tables

Thread Safety

The Extract function is safe for concurrent use with different database connections. Do not share sql.DB connections across goroutines without proper synchronization.

Index

Constants

This section is empty.

Variables

View Source 
var (

	// SystemSchemaWhereClause is an optimization for getting less schema objects.
	SystemSchemaWhereClause = func() string {
		var schemas []string
		for schema := range systemSchemas {
			schemas = append(schemas, fmt.Sprintf("'%s'", schema))
		}
		return strings.Join(schemas, ",")
	}()
)

Functions

func IsSystemDatabase 

func IsSystemDatabase(database string) bool

func IsSystemFunction 

func IsSystemFunction(function string, definition string) bool

func IsSystemSchema 

func IsSystemSchema(schema string) bool

func IsSystemTable 

func IsSystemTable(table string) bool

func IsSystemUser 

func IsSystemUser(user string) bool

func IsSystemView 

func IsSystemView(view string) bool

Types

type Extractor 

type Extractor struct {
	// contains filtered or unexported fields
}

Extractor extracts PostgreSQL database schema.

func NewExtractor 

func NewExtractor(db *sql.DB, databaseName string) *Extractor

NewExtractor creates a new PostgreSQL schema extractor.

func (*Extractor) ExtractSchema 

func (e *Extractor) ExtractSchema(ctx context.Context) (*schemaextract.DatabaseSchema, error)

ExtractSchema extracts the complete schema metadata for the database.

func (*Extractor) ListDatabases 

func (e *Extractor) ListDatabases(ctx context.Context) ([]string, error)

ListDatabases returns a list of all non-system databases.

type TableKey 

type TableKey struct {
	Schema string
	Table  string
}

TableKey is used to map tables/views to their metadata.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.