> ## Documentation Index
> Fetch the complete documentation index at: https://pgtofu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# extract

> Extract current database schema to JSON

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

```bash theme={null}
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

```bash theme={null}
# 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

```bash theme={null}
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:

```bash theme={null}
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

```bash theme={null}
# Exclude Prisma and GraphQL schemas
pgtofu extract \
  --exclude-schema _prisma \
  --exclude-schema graphql_public \
  --output current-schema.json
```

### Docker

```bash theme={null}
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

```bash theme={null}
# 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:

```bash theme={null}
# 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:

```json theme={null}
{
  "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

<AccordionGroup>
  <Accordion title="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)
  </Accordion>

  <Accordion title="Views">
    * Regular views with definitions
    * Materialized views with refresh settings
    * View indexes (for materialized views)
    * View comments
  </Accordion>

  <Accordion title="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
  </Accordion>

  <Accordion title="Types & Sequences">
    * Enum types with values
    * Composite types with fields
    * Domain types with constraints
    * Sequences with start/increment values
  </Accordion>

  <Accordion title="TimescaleDB Objects">
    * Hypertables with dimensions
    * Compression policies and settings
    * Retention policies
    * Continuous aggregates
    * Refresh policies
  </Accordion>
</AccordionGroup>

## 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

<AccordionGroup>
  <Accordion title="Connection refused">
    Ensure the database is running and accessible:

    ```bash theme={null}
    psql "$DATABASE_URL" -c "SELECT 1"
    ```
  </Accordion>

  <Accordion title="Permission denied">
    The user needs read access to system catalogs. Grant if needed:

    ```sql theme={null}
    GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO alice;
    ```
  </Accordion>

  <Accordion title="SSL errors">
    Try different SSL modes:

    ```bash theme={null}
    DATABASE_URL="postgres://...?sslmode=require"
    DATABASE_URL="postgres://...?sslmode=disable"
    ```
  </Accordion>

  <Accordion title="context deadline exceeded">
    The default extraction timeout is 5 minutes. Increase it when extracting large schemas:

    ```bash theme={null}
    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.
  </Accordion>
</AccordionGroup>

## See Also

* [`diff`](/cli/diff) - Compare extracted schema with desired schema
* [`generate`](/cli/generate) - Generate migrations from schema differences