跳到主要内容

PostgreSQL Schema Reader

PostgreSQL数据库结构读取器是一个强大的数据源工具，用于读取PostgreSQL数据库的完整表结构信息，包括字段定义、索引、约束、外键关系、序列等，并以结构化数组格式返回。

核心功能​

  • 完整表结构读取 - 获取所有表的详细结构信息
  • 索引和约束信息 - 包含完整的索引和约束详情
  • 主键和外键关系 - 自动识别表间关系
  • 视图和触发器支持 - 可选包含视图和触发器信息
  • 序列信息获取 - PostgreSQL特有的序列信息
  • 灵活的表过滤 - 支持包含/排除特定表
  • 连接池管理 - 高效的数据库连接管理
  • Schema级别隔离 - 支持PostgreSQL的Schema概念
  • SSL连接支持 - 安全的数据库连接
  • 高性能批量查询 - 优化的批量查询性能

配置参数​

数据库连接配置​

参数类型默认值必需描述
hoststringlocalhost是PostgreSQL服务器主机地址
portnumber5432否PostgreSQL服务器端口
userstringpostgres是数据库用户名
passwordstring""是数据库密码
databasestring-是目标数据库名
schemastringpublic否PostgreSQL Schema名称

读取选项配置​

参数类型默认值描述
includeTablesarray[]指定要读取的表名（空数组表示所有表）
excludeTablesarray[]排除的表名
includeViewsbooleanfalse是否包含视图
includeIndexesbooleantrue是否包含索引信息
includeConstraintsbooleantrue是否包含约束信息
includeTriggersInfobooleanfalse是否包含触发器信息
includeSequencesbooleanfalse是否包含序列信息
includeFunctionsbooleanfalse是否包含函数信息

性能配置​

参数类型默认值描述
maxnumber10连接池最大连接数
idleTimeoutMillisnumber30000空闲连接超时时间（毫秒）
connectionTimeoutMillisnumber60000连接超时时间（毫秒）
query_timeoutnumber60000查询超时时间（毫秒）

安全配置​

参数类型默认值描述
sslbooleanfalse是否使用SSL连接

使用方法​

基础使用​

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

读取特定表​

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

包含完整功能​

{
  "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连接配置​

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

输出格式​

工具返回结构化的数据，包含以下字段：

成功响应​

{
  "success": true,
  "database": "your_database",
  "host": "localhost",
  "schema": "public",
  "tables": [
    {
      "tableName": "users",
      "tableType": "BASE TABLE",
      "schemaName": "public",
      "tableComment": "用户表",
      "columns": [
        {
          "columnName": "id",
          "dataType": "integer",
          "udtName": "int4",
          "isNullable": false,
          "defaultValue": "nextval('users_id_seq'::regclass)",
          "isAutoIncrement": true,
          "comment": "用户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
    }
  }
}

错误响应​

{
  "success": false,
  "error": "连接数据库失败: connection refused",
  "database": "your_database",
  "host": "localhost",
  "readAt": "2024-01-01T12:00:00.000Z",
  "executionTime": 500
}

应用场景​

1. 数据库文档生成​

自动生成数据库结构文档，包含完整的表结构、关系图和约束信息。

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

2. 数据库迁移和同步​

比较不同环境的数据库结构，生成迁移脚本。

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

3. 代码生成器​

基于数据库结构自动生成ORM模型、API接口等代码。

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

4. 数据库健康检查​

定期检查数据库结构，监控表结构变化。

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

与MySQL Schema工具的区别​

特性PostgreSQL SchemaMySQL Schema
Schema支持✅ 原生支持❌ 不支持
序列信息✅ 支持❌ 不支持
用户定义类型✅ 支持UDT❌ 有限支持
约束类型✅ 更丰富✅ 基础支持
函数信息✅ 支持❌ 不支持
默认端口54323306
连接库pgmysql2

安全配置​

数据库用户权限​

建议创建专门的只读用户：

-- 创建只读用户
CREATE USER schema_reader WITH PASSWORD 'secure_password';

-- 授予连接权限
GRANT CONNECT ON DATABASE your_database TO schema_reader;

-- 授予Schema使用权限
GRANT USAGE ON SCHEMA public TO schema_reader;

-- 授予表查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO schema_reader;

-- 授予系统表查询权限
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连接配置​

生产环境建议启用SSL：

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

性能优化​

连接池配置​

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

表过滤优化​

对于大型数据库，使用表过滤提高性能：

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

故障排除​

常见错误​

  1. 连接失败

    错误: connection refused
    解决: 检查PostgreSQL服务状态和网络连接

  2. 权限不足

    错误: permission denied for table
    解决: 确保用户有足够的查询权限

  3. Schema不存在

    错误: schema "xxx" does not exist
    解决: 检查Schema名称是否正确

  4. 超时错误

    错误: query timeout
    解决: 增加query_timeout配置或优化查询

调试建议​

  1. 启用详细日志

    • 检查工具执行日志
    • 查看数据库连接日志

  2. 测试连接

    • 使用psql等工具测试连接
    • 验证用户权限

  3. 性能监控

    • 监控查询执行时间
    • 检查连接池使用情况

最佳实践​

  1. 安全性

    • 使用专门的只读用户
    • 启用SSL连接
    • 定期轮换密码

  2. 性能

    • 合理配置连接池大小
    • 使用表过滤减少查询量
    • 避免在高峰期执行

  3. 可靠性

    • 设置合适的超时时间
    • 实现错误重试机制
    • 监控工具执行状态

  4. 维护性

    • 定期更新工具版本
    • 记录配置变更
    • 建立监控告警

相关链接​