PostgreSQL Schema Reader

The PostgreSQL Schema Reader is a powerful data source tool for reading the complete table structure information of a PostgreSQL database, including column definitions, indexes, constraints, foreign key relationships, sequences, and more, returning data in a structured array format.

Core Features

Complete Table Structure Reading - Retrieve detailed structural information for all tables
Index and Constraint Information - Include complete index and constraint details
Primary Key and Foreign Key Relationships - Automatically identify inter-table relationships
View and Trigger Support - Optionally include view and trigger information
Sequence Information Retrieval - PostgreSQL-specific sequence information
Flexible Table Filtering - Support for including/excluding specific tables
Connection Pool Management - Efficient database connection management
Schema-Level Isolation - Support for PostgreSQL's Schema concept
SSL Connection Support - Secure database connections
High-Performance Batch Queries - Optimized batch query performance

Configuration Parameters

Database Connection Configuration

Parameter	Type	Default	Required	Description
`host`	string	localhost	Yes	PostgreSQL server host address
`port`	number	5432	No	PostgreSQL server port
`user`	string	postgres	Yes	Database username
`password`	string	""	Yes	Database password
`database`	string	-	Yes	Target database name
`schema`	string	public	No	PostgreSQL Schema name

Read Options Configuration

Parameter	Type	Default	Description
`includeTables`	array	[]	Specific table names to read (empty array means all tables)
`excludeTables`	array	[]	Table names to exclude
`includeViews`	boolean	false	Whether to include views
`includeIndexes`	boolean	true	Whether to include index information
`includeConstraints`	boolean	true	Whether to include constraint information
`includeTriggersInfo`	boolean	false	Whether to include trigger information
`includeSequences`	boolean	false	Whether to include sequence information
`includeFunctions`	boolean	false	Whether to include function information (⚠️ Configuration accepted but functionality not fully implemented)

Performance Configuration

Parameter	Type	Default	Description
`max`	number	10	Maximum connection pool size
`idleTimeoutMillis`	number	30000	Idle connection timeout (milliseconds)
`connectionTimeoutMillis`	number	60000	Connection timeout (milliseconds)
`query_timeout`	number	60000	Query timeout (milliseconds)

Security Configuration

Parameter	Type	Default	Description
`ssl`	boolean	false	Whether to use SSL connections

Usage

Basic Usage

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public"
  }
}

Read Specific Tables

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "includeTables": ["users", "orders", "products"]
  }
}

Full Feature Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "includeViews": true,
    "includeIndexes": true,
    "includeConstraints": true,
    "includeTriggersInfo": true,
    "includeSequences": true,
    "includeFunctions": true
  }
}

SSL Connection Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "ssl": true
  }
}

Output Format

The tool returns structured data containing the following fields:

Success Response

{
  "success": true,
  "database": "your_database",
  "host": "localhost",
  "schema": "public",
  "tables": [
    {
      "tableName": "users",
      "tableType": "BASE TABLE",
      "schemaName": "public",
      "tableComment": "User table",
      "columns": [
        {
          "columnName": "id",
          "dataType": "integer",
          "udtName": "int4",
          "isNullable": false,
          "defaultValue": "nextval('users_id_seq'::regclass)",
          "isAutoIncrement": true,
          "comment": "User ID",
          "maxLength": null,
          "precision": 32,
          "scale": 0,
          "position": 1
        }
      ],
      "primaryKeys": ["id"],
      "foreignKeys": [],
      "indexes": [
        {
          "indexName": "users_pkey",
          "columns": ["id"],
          "isUnique": true,
          "isPrimary": true,
          "indexType": "btree"
        }
      ],
      "constraints": [
        {
          "constraintName": "users_pkey",
          "constraintType": "PRIMARY KEY",
          "checkClause": null
        }
      ],
      "triggers": [],
      "sequences": [
        {
          "sequenceName": "users_id_seq",
          "dataType": "bigint",
          "startValue": "1",
          "increment": "1",
          "maxValue": "9223372036854775807",
          "minValue": "1"
        }
      ]
    }
  ],
  "metadata": {
    "totalTables": 1,
    "readAt": "2024-01-01T12:00:00.000Z",
    "executionTime": 1500,
    "config": {
      "schema": "public",
      "includeViews": false,
      "includeIndexes": true,
      "includeConstraints": true,
      "includeTriggersInfo": false,
      "includeSequences": false,
      "includeFunctions": false
    }
  }
}

Error Response

{
  "success": false,
  "error": "Failed to connect to database: connection refused",
  "database": "your_database",
  "host": "localhost",
  "readAt": "2024-01-01T12:00:00.000Z",
  "executionTime": 500
}

Use Cases

1. Database Documentation Generation

Automatically generate database structure documentation including complete table structures, relationship diagrams, and constraint information.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "production_db",
    "schema": "public",
    "includeViews": true,
    "includeConstraints": true,
    "includeTriggersInfo": true,
    "includeSequences": true
  }
}

2. Database Migration and Synchronization

Compare database structures across environments and generate migration scripts.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "source_db",
    "schema": "app_schema",
    "includeIndexes": true,
    "includeConstraints": true
  }
}

3. Code Generator

Automatically generate ORM models, API interfaces, and other code based on database structure.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "app_db",
    "schema": "public",
    "includeTables": ["users", "orders", "products"],
    "includeConstraints": true
  }
}

4. Database Health Check

Periodically check database structure and monitor table structure changes.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "monitoring_db",
    "schema": "public",
    "excludeTables": ["logs", "temp_data"]
  }
}

Differences from the MySQL Schema Tool

Feature	PostgreSQL Schema	MySQL Schema
Schema Support	✅ Native support	❌ Not supported
Sequence Information	✅ Supported	❌ Not supported
User-Defined Types	✅ UDT support	❌ Limited support
Constraint Types	✅ More comprehensive	✅ Basic support
Function Information	✅ Supported	❌ Not supported
Default Port	5432	3306
Connection Library	pg	mysql2

Security Configuration

Database User Permissions

It is recommended to create a dedicated read-only user:

-- Create read-only user
CREATE USER schema_reader WITH PASSWORD 'secure_password';

-- Grant connection permission
GRANT CONNECT ON DATABASE your_database TO schema_reader;

-- Grant schema usage permission
GRANT USAGE ON SCHEMA public TO schema_reader;

-- Grant table query permission
GRANT SELECT ON ALL TABLES IN SCHEMA public TO schema_reader;

-- Grant system table query permissions
GRANT SELECT ON information_schema.tables TO schema_reader;
GRANT SELECT ON information_schema.columns TO schema_reader;
GRANT SELECT ON information_schema.key_column_usage TO schema_reader;
GRANT SELECT ON information_schema.table_constraints TO schema_reader;
GRANT SELECT ON information_schema.constraint_column_usage TO schema_reader;
GRANT SELECT ON information_schema.triggers TO schema_reader;
GRANT SELECT ON information_schema.sequences TO schema_reader;

SSL Connection Configuration

SSL is recommended for production environments:

{
  "operator": "postgresql-schema",
  "config": {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "schema_reader",
    "password": "secure_password",
    "database": "production_db",
    "schema": "public",
    "ssl": true
  }
}

Performance Optimization

Connection Pool Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "large_db",
    "max": 5,
    "idleTimeoutMillis": 10000,
    "connectionTimeoutMillis": 30000,
    "query_timeout": 30000
  }
}

Table Filtering Optimization

For large databases, use table filtering to improve performance:

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "large_db",
    "includeTables": ["core_table1", "core_table2"],
    "excludeTables": ["log_table", "temp_table"]
  }
}

Troubleshooting

Common Errors

Connection Failed

Error: connection refused
Solution: Check PostgreSQL service status and network connectivity

Insufficient Permissions

Error: permission denied for table
Solution: Ensure the user has sufficient query permissions

Schema Does Not Exist

Error: schema "xxx" does not exist
Solution: Verify the Schema name is correct

Timeout Error

Error: query timeout
Solution: Increase query_timeout configuration or optimize the query

Debugging Tips

Enable Verbose Logging
- Check tool execution logs
- Review database connection logs
Test Connection
- Use psql or similar tools to test connectivity
- Verify user permissions
Performance Monitoring
- Monitor query execution time
- Check connection pool usage

Best Practices

Security
- Use a dedicated read-only user
- Enable SSL connections
- Rotate passwords regularly
Performance
- Configure connection pool size appropriately
- Use table filtering to reduce query volume
- Avoid running during peak hours
Reliability
- Set appropriate timeout durations
- Implement error retry mechanisms
- Monitor tool execution status
Maintainability
- Regularly update tool versions
- Document configuration changes
- Set up monitoring and alerts

Core Features​

Configuration Parameters​

Database Connection Configuration​

Read Options Configuration​

Performance Configuration​

Security Configuration​

Usage​

Basic Usage​

Read Specific Tables​

Full Feature Configuration​

SSL Connection Configuration​

Output Format​

Success Response​

Error Response​

Use Cases​

1. Database Documentation Generation​

2. Database Migration and Synchronization​

3. Code Generator​

4. Database Health Check​

Differences from the MySQL Schema Tool​

Security Configuration​

Database User Permissions​

SSL Connection Configuration​

Performance Optimization​

Connection Pool Configuration​

Table Filtering Optimization​

Troubleshooting​

Common Errors​

Debugging Tips​

Best Practices​

Related Links​

Core Features

Configuration Parameters

Database Connection Configuration

Read Options Configuration

Performance Configuration

Security Configuration

Usage

Basic Usage

Read Specific Tables

Full Feature Configuration

SSL Connection Configuration

Output Format

Success Response

Error Response

Use Cases

1. Database Documentation Generation

2. Database Migration and Synchronization

3. Code Generator

4. Database Health Check

Differences from the MySQL Schema Tool

Security Configuration

Database User Permissions

SSL Connection Configuration

Performance Optimization

Connection Pool Configuration

Table Filtering Optimization

Troubleshooting

Common Errors

Debugging Tips

Best Practices

Related Links