extract - pgtofu

The extract command connects to a PostgreSQL database and exports its complete schema to a JSON file. This JSON representation serves as the “current state” for schema comparison.

Usage

pgtofu extract [flags]

Flags

Flag	Short	Description	Default
`--database-url`		PostgreSQL connection URL	`$DATABASE_URL`
`--output`	`-o`	Output file path (`-` for stdout)	`schema.json`
`--exclude-schema`		Additional schemas to exclude (repeatable)
`--timeout`		Maximum time allowed for database connection and schema extraction (`0` disables)	`5m`
`--help`	`-h`	Help for extract

Examples

Basic Extraction

# Using command-line flag
pgtofu extract --database-url "postgres://user:pass@localhost:5432/db" \
  --output current-schema.json

# Using environment variable
export DATABASE_URL="postgres://user:pass@localhost:5432/db"
pgtofu extract --output current-schema.json

Output to stdout

pgtofu extract --output - | jq '.tables | length'

Increase Extraction Timeout

extract has a 5 minute default timeout for its database operations. Increase it for large schemas:

pgtofu extract \
  --database-url "$DATABASE_URL" \
  --output current-schema.json \
  --timeout 15m

Use --timeout 0 to disable pgtofu’s command timeout for extract.

Exclude Additional Schemas

# Exclude Prisma and GraphQL schemas
pgtofu extract \
  --exclude-schema _prisma \
  --exclude-schema graphql_public \
  --output current-schema.json

Docker

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  -e DATABASE_URL="$DATABASE_URL" \
  accented/pgtofu:latest extract --output current-schema.json

Connection URL Format

The PostgreSQL connection URL follows this format:

postgres://[user[:password]@][host][:port][/database][?param1=value1&...]

Examples

# Basic connection
postgres://user:password@localhost:5432/db

# With SSL mode
postgres://user:password@host.example.com:5432/db?sslmode=require

# With connection timeout
postgres://user:password@localhost:5432/db?connect_timeout=10

# Using socket connection
postgres://user:password@/db?host=/var/run/postgresql

connect_timeout only controls connection establishment at the PostgreSQL driver level. Use --timeout to control pgtofu’s overall deadline for the database operations in extract, including connection checks and schema extraction.

SSL Modes

Mode	Description
`disable`	No SSL
`allow`	Try non-SSL, fall back to SSL
`prefer`	Try SSL, fall back to non-SSL (default)
`require`	SSL required, no certificate verification
`verify-ca`	SSL required, verify CA
`verify-full`	SSL required, verify CA and hostname

Schema Exclusion

Automatically Excluded Schemas

pgtofu automatically excludes system and internal schemas:

Category	Schemas
PostgreSQL	`pg_catalog`, `information_schema`, `pg_toast`
TimescaleDB	`timescaledb_information`, `timescaledb_internal`, `_timescaledb_*`
Hasura	`hdb_catalog`

Excluding Third-Party Schemas

Use --exclude-schema to exclude schemas from third-party tools:

# Prisma
pgtofu extract --exclude-schema _prisma --output schema.json

# Supabase
pgtofu extract \
  --exclude-schema auth \
  --exclude-schema storage \
  --exclude-schema graphql_public \
  --output schema.json

# PostgREST
pgtofu extract --exclude-schema postgrest --output schema.json

Output Format

The extracted schema is a JSON object containing all database objects:

{
  "version": "1.0",
  "database_name": "db",
  "extracted_at": "2024-01-15T10:30:00Z",
  "schemas": [
    {"name": "public", "owner": "postgres"}
  ],
  "extensions": [
    {"name": "uuid-ossp", "schema": "public", "version": "1.1"}
  ],
  "custom_types": [
    {"schema": "public", "name": "status_enum", "type": "enum", "values": ["pending", "active"]}
  ],
  "sequences": [...],
  "tables": [
    {
      "schema": "public",
      "name": "users",
      "columns": [
        {"name": "id", "data_type": "bigint", "is_nullable": false, ...}
      ],
      "constraints": [...],
      "indexes": [...]
    }
  ],
  "views": [...],
  "materialized_views": [...],
  "functions": [...],
  "triggers": [...],
  "hypertables": [...],
  "continuous_aggregates": [...]
}

What Gets Extracted

Tables

Column definitions (types, nullability, defaults)
Primary keys and foreign keys
Unique constraints and check constraints
Exclusion constraints
Indexes (including partial and covering indexes)
Table comments
Partition information (for partitioned tables)

Views

Regular views with definitions
Materialized views with refresh settings
View indexes (for materialized views)
View comments

Functions & Triggers

Function signatures (name, arguments, return type)
Function bodies (preserving original language)
Volatility settings (STABLE, VOLATILE, IMMUTABLE)
Security settings (DEFINER vs INVOKER)
Triggers with timing and events

Types & Sequences

Enum types with values
Composite types with fields
Domain types with constraints
Sequences with start/increment values

TimescaleDB Objects

Hypertables with dimensions
Compression policies and settings
Retention policies
Continuous aggregates
Refresh policies

Performance Considerations

Extraction queries the PostgreSQL system catalogs directly
Large databases (1000+ tables) may take 30-60 seconds or longer, depending on catalog size, indexes, partitions, functions, and extensions
pgtofu enriches each table with columns, constraints, indexes, and partition metadata, so large schemas may need a longer --timeout
Consider using --output - with streaming for very large schemas

Troubleshooting

Connection refused

Ensure the database is running and accessible:

psql "$DATABASE_URL" -c "SELECT 1"

Permission denied

The user needs read access to system catalogs. Grant if needed:

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO alice;

SSL errors

Try different SSL modes:

DATABASE_URL="postgres://...?sslmode=require"
DATABASE_URL="postgres://...?sslmode=disable"

context deadline exceeded

The default extraction timeout is 5 minutes. Increase it when extracting large schemas:

pgtofu extract --database-url "$DATABASE_URL" --output current-schema.json --timeout 15m

If the error names a table or extraction step, check whether that object has unusually large catalog metadata, many indexes, many partitions, or blocking catalog locks. You can also run psql "$DATABASE_URL" -c "SELECT * FROM pg_stat_activity WHERE state <> 'idle';" while extraction is running to look for waits.

Documentation Index

​Usage

​Flags

​Examples

​Basic Extraction

​Output to stdout

​Increase Extraction Timeout

​Exclude Additional Schemas

​Docker

​Connection URL Format

​Examples

​SSL Modes

​Schema Exclusion

​Automatically Excluded Schemas

​Excluding Third-Party Schemas

​Output Format

​What Gets Extracted

​Performance Considerations

​Troubleshooting

​See Also

Usage

Flags

Examples

Basic Extraction

Output to stdout

Increase Extraction Timeout

Exclude Additional Schemas

Docker

Connection URL Format

Examples

SSL Modes

Schema Exclusion

Automatically Excluded Schemas

Excluding Third-Party Schemas

Output Format

What Gets Extracted

Performance Considerations

Troubleshooting

See Also